-- Create prescriptions table for managing digital prescriptions
CREATE TABLE prescriptions
(
    id                     UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    patient_id             UUID        NOT NULL REFERENCES patients (id) ON DELETE CASCADE,
    doctor_id              UUID        NOT NULL REFERENCES doctors (id),
    appointment_id          UUID                 REFERENCES appointments (id),
    medication_name         VARCHAR(255) NOT NULL,
    medication_code         VARCHAR(50),  -- NDC code
    dosage                  VARCHAR(100) NOT NULL,
    frequency               VARCHAR(100) NOT NULL,
    quantity                INTEGER      NOT NULL,
    refills                 INTEGER      DEFAULT 0,
    instructions            TEXT,
    start_date              DATE         NOT NULL,
    end_date                DATE,
    status                  VARCHAR(50)  DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'COMPLETED', 'CANCELLED', 'DISCONTINUED')),
    pharmacy_name           VARCHAR(255),
    pharmacy_address        TEXT,
    pharmacy_phone         VARCHAR(20),
    prescription_number      VARCHAR(100) UNIQUE,
    e_prescription_sent     BOOLEAN      DEFAULT FALSE,
    e_prescription_sent_at   TIMESTAMP,
    created_at              TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by              UUID         NOT NULL REFERENCES users (id)
);

-- Create medication intake logs for tracking medication adherence
CREATE TABLE medication_intake_logs
(
    id             UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    prescription_id UUID        NOT NULL REFERENCES prescriptions (id) ON DELETE CASCADE,
    scheduled_time  TIMESTAMP   NOT NULL,
    taken_at        TIMESTAMP,
    taken           BOOLEAN      DEFAULT FALSE,
    notes           TEXT,
    created_at      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better query performance
CREATE INDEX idx_prescriptions_patient_id ON prescriptions (patient_id);
CREATE INDEX idx_prescriptions_doctor_id ON prescriptions (doctor_id);
CREATE INDEX idx_prescriptions_appointment_id ON prescriptions (appointment_id);
CREATE INDEX idx_prescriptions_status ON prescriptions (status);
CREATE INDEX idx_prescriptions_start_date ON prescriptions (start_date);
CREATE INDEX idx_prescriptions_prescription_number ON prescriptions (prescription_number);
CREATE INDEX idx_prescriptions_created_at ON prescriptions (created_at DESC);

CREATE INDEX idx_medication_intake_logs_prescription_id ON medication_intake_logs (prescription_id);
CREATE INDEX idx_medication_intake_logs_scheduled_time ON medication_intake_logs (scheduled_time);
CREATE INDEX idx_medication_intake_logs_taken ON medication_intake_logs (taken) WHERE taken = FALSE;

