Clinic and appointments

CREATE TYPE appointment_status AS ENUM ('booked', 'checked_in', 'completed', 'cancelled', 'no_show');

CREATE TABLE patients (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    full_name text NOT NULL,
    date_of_birth date NOT NULL,
    phone text,
    email text
);

CREATE TABLE practitioners (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    full_name text NOT NULL,
    specialty text NOT NULL
);

CREATE TABLE schedules (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    practitioner_id uuid NOT NULL,
    weekday smallint NOT NULL,
    starts_at time NOT NULL,
    ends_at time NOT NULL,
    CHECK (weekday between 0 and 6),
    CHECK (ends_at > starts_at)
);
CREATE INDEX schedules_practitioner_id_idx ON schedules (practitioner_id);

CREATE TABLE appointments (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    patient_id uuid NOT NULL,
    practitioner_id uuid NOT NULL,
    starts_at timestamptz NOT NULL,
    duration_minutes int DEFAULT 30 NOT NULL,
    status appointment_status DEFAULT 'booked' NOT NULL,
    reason text
);
CREATE INDEX appointments_slot_idx ON appointments (practitioner_id, starts_at);
CREATE INDEX appointments_patient_id_idx ON appointments (patient_id);

CREATE TABLE visit_notes (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    appointment_id uuid NOT NULL UNIQUE,
    summary text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE medications (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL UNIQUE
);

CREATE TABLE prescriptions (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    visit_note_id uuid NOT NULL,
    medication_id uuid NOT NULL,
    dosage text NOT NULL,
    days int NOT NULL
);
CREATE INDEX prescriptions_visit_note_id_idx ON prescriptions (visit_note_id);
CREATE INDEX prescriptions_medication_id_idx ON prescriptions (medication_id);

ALTER TABLE schedules ADD FOREIGN KEY (practitioner_id) REFERENCES practitioners (id) ON DELETE CASCADE;

ALTER TABLE appointments ADD FOREIGN KEY (patient_id) REFERENCES patients (id);

ALTER TABLE appointments ADD FOREIGN KEY (practitioner_id) REFERENCES practitioners (id);

ALTER TABLE visit_notes ADD FOREIGN KEY (appointment_id) REFERENCES appointments (id) ON DELETE CASCADE;

ALTER TABLE prescriptions ADD FOREIGN KEY (visit_note_id) REFERENCES visit_notes (id) ON DELETE CASCADE;

ALTER TABLE prescriptions ADD FOREIGN KEY (medication_id) REFERENCES medications (id);