-- ==========================================
-- 1. БРИШЕЊЕ НА ТАБЕЛИ (Редослед за чиста база)
-- ==========================================
DROP TABLE IF EXISTS Review CASCADE;
DROP TABLE IF EXISTS Attends CASCADE;
DROP TABLE IF EXISTS Pet_Food CASCADE;
DROP TABLE IF EXISTS Medication CASCADE;
DROP TABLE IF EXISTS Product CASCADE;
DROP TABLE IF EXISTS Vaccination_Vaccine CASCADE;
DROP TABLE IF EXISTS Vaccination CASCADE;
DROP TABLE IF EXISTS Performed_Treatment CASCADE;
DROP TABLE IF EXISTS Medical_Record CASCADE;
DROP TABLE IF EXISTS Appointment CASCADE;
DROP TABLE IF EXISTS Equipment CASCADE;
DROP TABLE IF EXISTS Technician_Lab_Skills CASCADE;
DROP TABLE IF EXISTS Veterinarian CASCADE;
DROP TABLE IF EXISTS Veterinary_Technician CASCADE;
DROP TABLE IF EXISTS Inventory_Manager CASCADE;
DROP TABLE IF EXISTS Medical_Professional CASCADE;
DROP TABLE IF EXISTS Receptionist_Languages CASCADE;
DROP TABLE IF EXISTS Receptionist CASCADE;
DROP TABLE IF EXISTS Employee CASCADE;
DROP TABLE IF EXISTS Owner_Pet_Pair CASCADE;
DROP TABLE IF EXISTS Owner CASCADE;
DROP TABLE IF EXISTS Pet CASCADE;
DROP TABLE IF EXISTS Exam_Room CASCADE;
DROP TABLE IF EXISTS Treatment CASCADE;
DROP TABLE IF EXISTS Vaccine CASCADE;
DROP TABLE IF EXISTS Person CASCADE;

-- ==========================================
-- 2. КРЕИРАЊЕ НА ТАБЕЛИ (DDL)
-- ==========================================

CREATE TABLE Person(
  person_id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL DEFAULT 'John',
  surname VARCHAR(50) NOT NULL DEFAULT 'Doe',
  phone_number VARCHAR(20) NOT NULL UNIQUE
    CHECK (phone_number ~ '^\+?[0-9]+$'),
  email VARCHAR(50) UNIQUE
      CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  address VARCHAR(50)
);


CREATE TABLE Owner(
    owner_id INT4 PRIMARY KEY REFERENCES
                  Person(person_id),
    total_spent NUMERIC(10,2),
    balance NUMERIC(10,2),
    membership_type VARCHAR(10)
                  CHECK(membership_type IN ('VIP', 'Standard', 'Premium'))
);

CREATE TABLE Employee (
    employee_id INT4 PRIMARY KEY REFERENCES
                  Person(person_id),
    date_of_birth DATE NOT NULL,
    hire_date DATE,

    CONSTRAINT check_hire_after_birth
        CHECK (hire_date > date_of_birth),
    CONSTRAINT check_legal_age
        CHECK (date_of_birth <= CURRENT_DATE - INTERVAL '18 years')
);

CREATE TABLE Vaccine (
    vaccine_id INT4 PRIMARY KEY,
    trade_name VARCHAR(100) NOT NULL,
    target_species VARCHAR(30) NOT NULL,
    disease_target VARCHAR(50) NOT NULL,
    manufacturer VARCHAR(100) NOT NULL,
    dosage_ml numeric(4, 2) DEFAULT 1.0,

    CONSTRAINT check_positive_dosage CHECK (dosage_ml > 0),
    CONSTRAINT unique_vaccine_info UNIQUE (trade_name, manufacturer)
);

CREATE TABLE Treatment (
    treatment_id INT4 PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,

    CONSTRAINT check_treatment_price CHECK (price >= 0),
    CONSTRAINT unique_treatment_name UNIQUE (name)
);

CREATE TABLE Exam_Room (
    exam_room_id INT4 PRIMARY KEY,
    room_type VARCHAR(50) NOT NULL,
    is_available BOOL NOT NULL DEFAULT true,
    last_sanitized TIMESTAMP,

    CONSTRAINT check_room_type
        CHECK (room_type IN ('General', 'Surgery', 'Consultation', 'X-Ray', 'Isolation')),
    CONSTRAINT check_sanitization_date
        CHECK (last_sanitized <= CURRENT_TIMESTAMP)
);

CREATE TABLE Pet(
    pet_id INT4 PRIMARY KEY,
    name VARCHAR(50),
    type_of_animal VARCHAR(30),
    breed VARCHAR(50),
    date_of_birth DATE,
    sex CHAR(1),

    CONSTRAINT check_pet_sex CHECK (sex IN ('M', 'F')),
    CONSTRAINT CHK_pet_dob CHECK (date_of_birth <= CURRENT_DATE)
);

CREATE TABLE Owner_Pet_Pair(
    pet_id INT4 REFERENCES Pet(pet_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    owner_id INT4 REFERENCES Owner(owner_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    is_primary_owner BOOLEAN DEFAULT FALSE,
    PRIMARY KEY (pet_id, owner_id)
);

CREATE TABLE Receptionist(
    receptionist_id INT4 PRIMARY KEY,
    desk_number varchar(10),
    FOREIGN KEY (receptionist_id)
        REFERENCES Employee(employee_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    CONSTRAINT check_desk_not_empty CHECK (length(trim(desk_number)) > 0)
);

CREATE TABLE Receptionist_Languages (
    language_name VARCHAR(50) NOT NULL,
    proficiency_level varchar(20) DEFAULT 'Fluent',
    receptionist_id INT4 NOT NULL REFERENCES Receptionist(receptionist_id)
                                    ON DELETE CASCADE ON UPDATE CASCADE ,

    PRIMARY KEY (receptionist_id, language_name),

    CONSTRAINT CHK_proficiency
        CHECK (proficiency_level IN ('Basic', 'Intermediate', 'Fluent', 'Native'))
);

CREATE TABLE Medical_Professional (
    medical_prof_id INT4 PRIMARY KEY
        REFERENCES Employee(employee_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    medical_degree VARCHAR(100) NOT NULL,
    shift_type VARCHAR(20),
    department VARCHAR(50) NOT NULL,
    is_surgical_authorized BOOL NOT NULL,

    CONSTRAINT check_shift_type
        CHECK (shift_type IN ('Morning', 'Afternoon', 'Night', 'On-Call', 'Full-Day')),
    CONSTRAINT check_department
        CHECK (department IN ('Surgery', 'General', 'Diagnostics', 'Emergency', 'Pharmacy')),
    CONSTRAINT check_degree_format
        CHECK (length(medical_degree) >= 2)
);

CREATE TABLE Inventory_Manager(
  inventory_manager_id INT4 PRIMARY KEY
        REFERENCES Employee(employee_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
  access_level VARCHAR(20) NOT NULL,

  CONSTRAINT check_access_level
        CHECK (access_level IN ('Viewer', 'Editor', 'Admin', 'SuperUser'))
);

CREATE TABLE Veterinary_Technician(
  veterinary_technician_id INT4 PRIMARY KEY
      REFERENCES Medical_Professional(medical_prof_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  certification_level varchar(50) NOT NULL,

  CONSTRAINT CHK_certification_level
        CHECK (certification_level IN ('Licensed (LVT)', 'Registered (RVT)', 'Certified (CVT)', 'Assistant', 'Intern'))
);

CREATE TABLE Technician_Lab_Skills (
    vet_tech_id INT4 NOT NULL
        REFERENCES Veterinary_Technician(veterinary_technician_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    skill_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (vet_tech_id, skill_name)
);

CREATE TABLE Veterinarian(
  veterinarian_id INT4 PRIMARY KEY
      REFERENCES Medical_Professional(medical_prof_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  specialty VARCHAR(50) NOT NULL,
  license_number VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Appointment(
    appointment_id INT4 PRIMARY KEY,
    appointment_time TIMESTAMP NOT NULL,
    status VARCHAR(20) DEFAULT 'Scheduled'
        CHECK (status IN ('Pending', 'Scheduled', 'Completed', 'Cancelled', 'No-show')),
    reason VARCHAR(200),
    pet_id INT4 DEFAULT 1 NOT NULL REFERENCES Pet(pet_id)
        ON DELETE SET DEFAULT ON UPDATE CASCADE,
    exam_room_id INT4 REFERENCES Exam_Room(exam_room_id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    receptionist_id INT4 REFERENCES Receptionist(receptionist_id)
        ON DELETE SET NULL ON UPDATE CASCADE,

    CONSTRAINT check_future_date
        CHECK (appointment_time >= CURRENT_TIMESTAMP - INTERVAL '2 hours')
);

CREATE TABLE Vaccination (
    vaccination_id INT4 PRIMARY KEY REFERENCES
                  Performed_Treatment(performed_treatment_id),
    date DATE NOT NULL DEFAULT CURRENT_DATE,
    next_due_date DATE,

    CONSTRAINT check_vaccine_dates CHECK (next_due_date > date)
);

CREATE TABLE Vaccination_Vaccine(
    dosage_given numeric(4,2),
    vaccine_id INT4 NOT NULL REFERENCES Vaccine(vaccine_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    vaccination_id INT4 NOT NULL REFERENCES Vaccination(vaccination_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (vaccine_id, vaccination_id)
);

CREATE TABLE Equipment (
    equipment_id INT4 PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    model VARCHAR(50),
    status VARCHAR(20) NOT NULL DEFAULT 'Available'
        CHECK (status IN ('Available', 'In Use', 'Broken', 'Maintenance')),
    exam_room_id INT4 REFERENCES Exam_Room(exam_room_id)
        ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE Medical_Record (
    medical_record_id INT4 PRIMARY KEY,
    date DATE NOT NULL,
    diagnosis TEXT NOT NULL,
    therapy TEXT NOT NULL,
    note TEXT,
    app_id INT4 REFERENCES Appointment(appointment_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    pet_id INT4 NOT NULL REFERENCES Pet(pet_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    medical_professional_id INT4 NOT NULL REFERENCES Medical_Professional(medical_prof_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT CHK_medical_record_date
        CHECK (date <= CURRENT_DATE)
);

CREATE TABLE Performed_Treatment (
    performed_treatment_id INT4 PRIMARY KEY,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP,
    medical_record_id INT4 NOT NULL REFERENCES Medical_Record(medical_record_id)
                                 ON DELETE CASCADE ON UPDATE CASCADE,
    treatment_id INT4 NOT NULL REFERENCES Treatment(treatment_id)
                                 ON DELETE RESTRICT ON UPDATE CASCADE,
    medical_professional_id INT4 NOT NULL REFERENCES Medical_Professional(medical_prof_id)
                                 ON DELETE RESTRICT ON UPDATE CASCADE,
    exam_room_id INT4 NOT NULL REFERENCES Exam_Room(exam_room_id)
                                 ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT check_treatment_duration
        CHECK (end_time IS NULL OR end_time > start_time),
    CONSTRAINT check_start_time_limit
        CHECK (start_time <= CURRENT_TIMESTAMP + INTERVAL '7 days')
);

CREATE TABLE Product (
    product_id INT4 PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manufacturer VARCHAR(100),
    stock_quantity INT4 DEFAULT 0,
    unit_price NUMERIC(10, 2) NOT NULL,
    description TEXT,
    is_prescription_required BOOL DEFAULT false,
    reorder_level INT NOT NULL,
    inventory_manager_id INT4 NOT NULL REFERENCES Inventory_Manager(inventory_manager_id)
                     ON DELETE RESTRICT ON UPDATE CASCADE,

    CONSTRAINT check_stock_quantity CHECK (stock_quantity >= 0),

    CONSTRAINT check_unit_price CHECK (unit_price > 0),

    CONSTRAINT check_reorder_level CHECK (reorder_level >= 0),

    CONSTRAINT unique_product_brand UNIQUE (name, manufacturer)
);

CREATE TABLE Medication (
    medication_id INT4 PRIMARY KEY REFERENCES Product(product_id)
                        ON DELETE CASCADE
                        ON UPDATE CASCADE,
    dosage_form VARCHAR(50) NOT NULL,
    side_effects TEXT NOT NULL,

    CONSTRAINT check_dosage_form
        CHECK (dosage_form IN ('Tablet', 'Capsule', 'Injection', 'Syrup', 'Ointment', 'Cream', 'Drops', 'Powder'))
);

CREATE TABLE Pet_Food (
    product_id INT4 PRIMARY KEY REFERENCES Product(product_id)
                      ON DELETE CASCADE
                      ON UPDATE CASCADE,
    calories INT4,
    is_medical_diet BOOL NOT NULL DEFAULT false,
    flavor VARCHAR(50),
    animal_species VARCHAR(30) NOT NULL,
    life_stage VARCHAR(20) NOT NULL,

    CONSTRAINT check_food_calories
        CHECK (calories > 0),

    CONSTRAINT check_food_life_stage
        CHECK (life_stage IN ('Puppy/Kitten', 'Junior', 'Adult', 'Senior', 'All Stages'))
);

CREATE TABLE Attends (
    medical_prof_id INT4 REFERENCES Medical_Professional(medical_prof_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    app_id INT4 REFERENCES Appointment(appointment_id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (medical_prof_id, app_id)
);

CREATE TABLE Review (
    review_id INT4 PRIMARY KEY,
    rating INT4 NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    owner_id INT4 REFERENCES Owner(owner_id)
        ON DELETE SET NULL ON UPDATE CASCADE
);