-- Create medical records table for patient medical history
CREATE TABLE medical_records
(
    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),
    record_type      VARCHAR(50) NOT NULL CHECK (record_type IN ('DIAGNOSIS', 'LAB_RESULT', 'IMAGING', 'VITAL_SIGNS', 'NOTE', 'PROCEDURE', 'TREATMENT_PLAN', 'OTHER')),
    title            VARCHAR(255) NOT NULL,
    content          TEXT,
    diagnosis_code   VARCHAR(50),  -- ICD-10 codes
    created_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by       UUID         NOT NULL REFERENCES users (id)
);

-- Create vital signs table for tracking patient vitals
CREATE TABLE vital_signs
(
    id                    UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    patient_id           UUID        NOT NULL REFERENCES patients (id) ON DELETE CASCADE,
    appointment_id       UUID                 REFERENCES appointments (id),
    recorded_at          TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    recorded_by          UUID                 REFERENCES users (id),
    temperature          DECIMAL(4, 1),        -- Celsius
    blood_pressure_systolic  INTEGER,
    blood_pressure_diastolic INTEGER,
    heart_rate           INTEGER,              -- BPM
    respiratory_rate     INTEGER,              -- BPM
    oxygen_saturation    DECIMAL(4, 1),        -- Percentage
    weight_kg            DECIMAL(5, 2),
    height_cm            DECIMAL(5, 2),
    bmi                  DECIMAL(4, 1),
    notes                TEXT,
    medical_record_id    UUID                 REFERENCES medical_records (id) ON DELETE SET NULL
);

-- Create lab results table
CREATE TABLE lab_results
(
    id                UUID PRIMARY KEY     DEFAULT uuid_generate_v4(),
    medical_record_id UUID        NOT NULL REFERENCES medical_records (id) ON DELETE CASCADE,
    patient_id        UUID        NOT NULL REFERENCES patients (id) ON DELETE CASCADE,
    test_name         VARCHAR(255) NOT NULL,
    test_code         VARCHAR(50),
    result_value      VARCHAR(255),
    unit              VARCHAR(50),
    reference_range   VARCHAR(100),
    status            VARCHAR(50) DEFAULT 'NORMAL' CHECK (status IN ('NORMAL', 'ABNORMAL', 'CRITICAL', 'PENDING')),
    performed_at      TIMESTAMP,
    result_file_url   TEXT,                 -- For PDF/lab reports
    ordered_by        UUID                 REFERENCES users (id),
    created_at        TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better query performance
CREATE INDEX idx_medical_records_patient_id ON medical_records (patient_id);
CREATE INDEX idx_medical_records_doctor_id ON medical_records (doctor_id);
CREATE INDEX idx_medical_records_appointment_id ON medical_records (appointment_id);
CREATE INDEX idx_medical_records_record_type ON medical_records (record_type);
CREATE INDEX idx_medical_records_created_at ON medical_records (created_at DESC);

CREATE INDEX idx_vital_signs_patient_id ON vital_signs (patient_id);
CREATE INDEX idx_vital_signs_recorded_at ON vital_signs (recorded_at DESC);
CREATE INDEX idx_vital_signs_appointment_id ON vital_signs (appointment_id);

CREATE INDEX idx_lab_results_patient_id ON lab_results (patient_id);
CREATE INDEX idx_lab_results_medical_record_id ON lab_results (medical_record_id);
CREATE INDEX idx_lab_results_status ON lab_results (status);
CREATE INDEX idx_lab_results_performed_at ON lab_results (performed_at DESC);

