CREATE DATABASE IF NOT EXISTS veterinary_clinic;

USE veterinary_clinic;

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

CREATE TABLE IF NOT EXISTS owner (
    id              INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY 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
);

ALTER TABLE owner ADD CONSTRAINT owner_email_format CHECK (email LIKE '%@%.%');

CREATE TABLE IF NOT EXISTS animal (
    id              INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name            VARCHAR(50) NOT NULL,
    breed_id        INT(10) UNSIGNED,
    gender          CHAR(1) DEFAULT 'M',
    birth_date      DATETIME NOT NULL,
    death_date      DATETIME,
    owner_id        INT(10) UNSIGNED,
    father_id       INT(10) UNSIGNED,
    mother_id       INT(10) UNSIGNED,
    FOREIGN KEY(breed_id) REFERENCES breed(id) ON UPDATE CASCADE ON DELETE SET NULL,
    FOREIGN KEY(owner_id) REFERENCES owner(id) ON UPDATE CASCADE ON DELETE SET NULL
);

ALTER TABLE animal ADD CONSTRAINT father_id_foreign_key FOREIGN KEY(father_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL;
ALTER TABLE animal ADD CONSTRAINT mother_id_foreign_key FOREIGN KEY(mother_id) REFERENCES animal(id) ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE animal ADD CONSTRAINT sex_possible_values CHECK (gender in ('M', 'F'));

CREATE TABLE IF NOT EXISTS doctor (
    id              INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY 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
);

ALTER TABLE doctor ADD CONSTRAINT doctor_email_format CHECK (email LIKE '%@%.%');

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

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