CREATE DATABASE IF NOT EXISTS veterinary_clinic;

USE veterinary_clinic;

CREATE DATABASE IF NOT EXISTS veterinary_clinic;

USE veterinary_clinic;

CREATE TABLE IF NOT EXISTS breed (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    name            VARCHAR(50) NOT NULL,
    description     VARCHAR(1000),
    KEY (id)
);
ALTER TABLE breed ADD CONSTRAINT pk_breed_id PRIMARY KEY (id);

CREATE TABLE IF NOT EXISTS owner (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(50),
    phone_number    INT(10) NOT NULL,
    bank_account    VARCHAR(50) NOT NULL,
    KEY (id)
);
ALTER TABLE owner ADD CONSTRAINT pk_owner_id PRIMARY KEY (id);
ALTER TABLE owner ADD CONSTRAINT owner_email_format CHECK (email LIKE '%@%.%');

CREATE TABLE IF NOT EXISTS animal (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    name            VARCHAR(50) NOT NULL,
    breed_id        INT(10) UNSIGNED,
    gender          CHAR(1) DEFAULT 'M' NOT NULL,
    birth_date      DATETIME NOT NULL,
    death_date      DATETIME,
    owner_id        INT(10) UNSIGNED,
    father_id       INT(10) UNSIGNED,
    mother_id       INT(10) UNSIGNED,
    KEY (id)
);
ALTER TABLE animal ADD CONSTRAINT pk_animal_id PRIMARY KEY (id);
ALTER TABLE animal ADD CONSTRAINT fk_animal_breed_id FOREIGN KEY(breed_id) REFERENCES breed(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT fk_animal_ownder_id FOREIGN KEY(owner_id) REFERENCES owner(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT fk_animal_father_id FOREIGN KEY(father_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT fk_animal_mother_id FOREIGN KEY(mother_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT chk_animal_sex_possible_values CHECK (gender in ('M', 'F'));

CREATE TABLE IF NOT EXISTS doctor (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    title           VARCHAR(20) NOT NULL,
    speciality      VARCHAR(20),
    code            VARCHAR(20) NOT NULL,
    email           VARCHAR(50),
    phone_number    INT(10) NOT NULL,
    KEY (id)
);
ALTER TABLE doctor ADD CONSTRAINT pk_doctor_id PRIMARY KEY (id);
ALTER TABLE doctor ADD CONSTRAINT chk_doctor_email_format CHECK (email LIKE '%@%.%');

CREATE TABLE IF NOT EXISTS diagnosis (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    name            VARCHAR(50) NOT NULL,
    description     VARCHAR(1000) NOT NULL,
    severity        VARCHAR(20) NOT NULL,
    cure            VARCHAR(1000) NOT NULL,
    KEY (id)
);
ALTER TABLE diagnosis ADD CONSTRAINT pk_diagnosis_id PRIMARY KEY (id);

CREATE TABLE IF NOT EXISTS medical_record (
    id              INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    animal_id       INT(10) UNSIGNED,
    doctor_id       INT(10) UNSIGNED,
    diagnosis_id    INT(10) UNSIGNED,
    date            DATETIME NOT NULL,
    treatment       VARCHAR(1000),
    KEY (id)
);
ALTER TABLE medical_record ADD CONSTRAINT pk_medical_record_id PRIMARY KEY (id);
ALTER TABLE medical_record ADD CONSTRAINT fk_medical_record_animal_id FOREIGN KEY(animal_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE medical_record ADD CONSTRAINT fk_medical_record_doctor_id FOREIGN KEY(doctor_id) REFERENCES doctor(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE medical_record ADD CONSTRAINT fk_medicak_record_diagnosis_id FOREIGN KEY(diagnosis_id) REFERENCES diagnosis(id) ON UPDATE CASCADE ON DELETE SET NULL;