--schema = perfect_pet

-- relation doctor
-- DROP TABLE IF EXISTS perfect_pet.doctor;
CREATE TYPE medical_specialty AS ENUM ('surgery','general');
CREATE TABLE IF NOT EXISTS perfect_pet.doctor
(
    id_doctor integer NOT NULL,
    first_name character varying(150) NOT NULL,
    last_name character varying(150) NOT NULL,	
    specialty medical_specialty NOT NULL,
    license_number character varying(15) NOT NULL,	
    period_start_date DATE NOT NULL,
    period_end_date	DATE DEFAULT NULL,
    max_monthly_hours FLOAT DEFAULT 0,
    CONSTRAINT doctor_pkey PRIMARY KEY (id_doctor)
);

------------------------------------------------------------------
-- relation microchip
-- DROP TABLE IF EXISTS perfect_pet.microchip;
CREATE TABLE IF NOT EXISTS perfect_pet.microchip
(
    id_microchip integer NOT NULL,
    number character varying(9) NOT NULL,
    implant_date DATE NOT NULL,
    CONSTRAINT microchip_pkey PRIMARY KEY (id_microchip)
);

------------------------------------------------------------------
-- relation animal
-- DROP TABLE IF EXISTS perfect_pet.animal;
CREATE TYPE animal_species AS ENUM ('canine','feline');
CREATE TYPE animal_gender AS ENUM ('female','male');
CREATE TABLE IF NOT EXISTS perfect_pet.animal
(
    id_animal integer NOT NULL,
    species	animal_species NOT NULL,
    breed character varying(50) NOT NULL,		
    name character varying(50) NOT NULL,	
    id_microchip integer NOT NULL,	
    gender animal_gender NOT NULL,
    dob	DATE DEFAULT NULL,
    weight FLOAT,
    hash_id character varying(10) NOT NULL,
    id_client integer NOT NULL,
    CONSTRAINT animal_pkey PRIMARY KEY (id_animal)
);

ALTER TABLE perfect_pet.animal
ADD CONSTRAINT FK_animal_microchip FOREIGN KEY (id_microchip)
REFERENCES perfect_pet.microchip(id_microchip) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION;

ALTER TABLE perfect_pet.animal
ADD CONSTRAINT FK_animal_client FOREIGN KEY (id_client)
REFERENCES perfect_pet.client(id_client) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION;

------------------------------------------------------------------
--relation appointment
-- DROP TABLE IF EXISTS perfect_pet.appointment;
CREATE TYPE appointment_time AS ENUM ('8','9' ,'10','11','12','13','14','15','16','17','18','19','20');
CREATE TYPE appointment_reason AS ENUM ('injured pet','initial visit','surgery','follow-up surgery','annual visit','follow up','sick pet','follow-up');
CREATE TABLE IF NOT EXISTS perfect_pet.appointment
(
    id_appointment integer NOT NULL,
    id_animal integer NOT NULL,	
    id_doctor integer NOT NULL,
    date DATE NOT NULL,
    time appointment_time NOT NULL,
    main_reason	appointment_reason NOT NULL,
    CONSTRAINT appointment_pkey PRIMARY KEY (id_appointment)
);

ALTER TABLE perfect_pet.appointment
ADD CONSTRAINT FK_appointment_animal FOREIGN KEY (id_animal)
REFERENCES perfect_pet.animal(id_animal) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION;

ALTER TABLE perfect_pet.appointment
ADD CONSTRAINT FK_appointment_doctor FOREIGN KEY (id_doctor)
REFERENCES perfect_pet.doctor(id_doctor) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION;

------------------------------------------------------------------
-- relation client
CREATE TABLE IF NOT EXISTS perfect_pet.client
(
    id_client integer NOT NULL,
    first_name  character varying(150) NOT NULL,	
    last_name  character varying(150) NOT NULL,	
    city  character varying(50) NOT NULL,	
    phone_number  character varying(10) NOT NULL,	
    id_animal integer NOT NULL,
    CONSTRAINT client_pkey PRIMARY KEY (id_client)
);

ALTER TABLE perfect_pet.client
ADD CONSTRAINT FK_client_animal FOREIGN KEY (id_animal)
REFERENCES perfect_pet.animal(id_animal) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION;

