-- =====================================================
-- SIMPUS Database Schema - EMR (Rekam Medis) Module
-- Version: 000004
-- Description: Medical records, vital signs, diagnoses, prescriptions
-- =====================================================

-- =====================================================
-- MEDICAL RECORDS (REKAM MEDIS / SOAP)
-- =====================================================

CREATE TYPE medical_record_status AS ENUM ('draft', 'completed', 'signed');
CREATE TYPE diagnosis_type AS ENUM ('primary', 'secondary', 'differential');
CREATE TYPE prescription_status AS ENUM ('draft', 'issued', 'dispensed', 'cancelled');

CREATE TABLE medical_records (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    visit_id UUID NOT NULL REFERENCES visits(id) ON DELETE CASCADE,
    doctor_id UUID REFERENCES users(id) ON DELETE SET NULL,
    
    -- SOAP Notes
    subjective TEXT,
    objective TEXT,
    assessment TEXT,
    plan TEXT,
    
    -- Additional notes
    physical_examination TEXT,
    lab_notes TEXT,
    radiology_notes TEXT,
    other_notes TEXT,
    
    -- Status
    status medical_record_status DEFAULT 'draft',
    signed_at TIMESTAMPTZ,
    signed_by UUID REFERENCES users(id),
    
    -- Timestamps
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id),
    
    UNIQUE(visit_id)
);

CREATE INDEX idx_medical_records_visit ON medical_records(visit_id);
CREATE INDEX idx_medical_records_doctor ON medical_records(doctor_id);
CREATE INDEX idx_medical_records_status ON medical_records(status);

CREATE TRIGGER update_medical_records_updated_at
    BEFORE UPDATE ON medical_records
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- VITAL SIGNS
-- =====================================================

CREATE TABLE vital_signs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    visit_id UUID NOT NULL REFERENCES visits(id) ON DELETE CASCADE,
    
    -- Vital signs
    temperature DECIMAL(4,1),           -- Celsius
    blood_pressure_systolic INT,
    blood_pressure_diastolic INT,
    pulse INT,
    respiratory_rate INT,
    oxygen_saturation DECIMAL(4,1),
    
    -- Anthropometry
    height DECIMAL(5,2),               -- cm
    weight DECIMAL(5,2),               -- kg
    bmi DECIMAL(4,2),                  -- calculated
    
    -- Additional
    pain_scale INT CHECK (pain_scale >= 0 AND pain_scale <= 10),
    
    recorded_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    recorded_by UUID REFERENCES users(id),
    notes TEXT
);

CREATE INDEX idx_vital_signs_visit ON vital_signs(visit_id);
CREATE INDEX idx_vital_signs_recorded_at ON vital_signs(recorded_at);

-- =====================================================
-- DIAGNOSES (ICD-10)
-- =====================================================

CREATE TABLE diagnoses (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    medical_record_id UUID NOT NULL REFERENCES medical_records(id) ON DELETE CASCADE,
    
    icd10_code VARCHAR(10) NOT NULL,
    diagnosis_type diagnosis_type DEFAULT 'primary',
    description TEXT,
    notes TEXT,
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id)
);

CREATE INDEX idx_diagnoses_medical_record ON diagnoses(medical_record_id);
CREATE INDEX idx_diagnoses_icd10 ON diagnoses(icd10_code);

-- =====================================================
-- PRESCRIPTIONS
-- =====================================================

CREATE TABLE prescriptions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    visit_id UUID NOT NULL REFERENCES visits(id) ON DELETE CASCADE,
    medical_record_id UUID REFERENCES medical_records(id) ON DELETE SET NULL,
    
    prescription_number VARCHAR(30) NOT NULL,
    status prescription_status DEFAULT 'draft',
    
    -- Validity
    valid_until DATE,
    
    notes TEXT,
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    updated_by UUID REFERENCES users(id),
    
    UNIQUE(visit_id, prescription_number)
);

CREATE INDEX idx_prescriptions_visit ON prescriptions(visit_id);
CREATE INDEX idx_prescriptions_medical_record ON prescriptions(medical_record_id);
CREATE INDEX idx_prescriptions_status ON prescriptions(status);

CREATE TRIGGER update_prescriptions_updated_at
    BEFORE UPDATE ON prescriptions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- PRESCRIPTION ITEMS
-- =====================================================

CREATE TABLE prescription_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    prescription_id UUID NOT NULL REFERENCES prescriptions(id) ON DELETE CASCADE,
    
    -- Medicine reference (optional - can use free text)
    medicine_reference_id UUID REFERENCES medicine_references(id),
    medicine_name VARCHAR(255) NOT NULL,
    medicine_strength VARCHAR(100),
    
    -- Dosage
    dosage VARCHAR(100) NOT NULL,
    frequency_id UUID REFERENCES medicine_frequencies(id),
    frequency_text VARCHAR(100),
    route_id UUID REFERENCES medicine_routes(id),
    route_text VARCHAR(50),
    
    -- Quantity
    quantity DECIMAL(10,2) NOT NULL,
    unit_id UUID REFERENCES medicine_units(id),
    unit_text VARCHAR(20),
    duration_days INT,
    
    -- Instruction
    instruction TEXT,
    sig TEXT,
    
    -- Order
    sort_order INT DEFAULT 0,
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_prescription_items_prescription ON prescription_items(prescription_id);

-- =====================================================
-- PROCEDURES (ICD-9-CM)
-- =====================================================

CREATE TABLE procedures (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    medical_record_id UUID NOT NULL REFERENCES medical_records(id) ON DELETE CASCADE,
    
    icd9cm_code VARCHAR(10) NOT NULL,
    description TEXT,
    notes TEXT,
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id)
);

CREATE INDEX idx_procedures_medical_record ON procedures(medical_record_id);
CREATE INDEX idx_procedures_icd9cm ON procedures(icd9cm_code);

-- =====================================================
-- LAB ORDERS (for Phase 7 - placeholder structure)
-- =====================================================

CREATE TABLE lab_orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    visit_id UUID NOT NULL REFERENCES visits(id) ON DELETE CASCADE,
    medical_record_id UUID REFERENCES medical_records(id) ON DELETE SET NULL,
    
    order_number VARCHAR(30) NOT NULL,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    status VARCHAR(20) DEFAULT 'pending',
    
    notes TEXT,
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES users(id),
    
    UNIQUE(visit_id, order_number)
);

CREATE INDEX idx_lab_orders_visit ON lab_orders(visit_id);

-- =====================================================
-- SEQUENCE FOR PRESCRIPTION NUMBER
-- =====================================================

CREATE TABLE prescription_number_sequences (
    puskesmas_id UUID NOT NULL REFERENCES puskesmas(id) ON DELETE CASCADE,
    prescription_date DATE NOT NULL,
    last_number INT NOT NULL DEFAULT 0,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (puskesmas_id, prescription_date)
);

CREATE OR REPLACE FUNCTION get_next_prescription_number(p_puskesmas_id UUID, p_prescription_date DATE)
RETURNS VARCHAR(30) AS $$
DECLARE
    v_next INT;
    v_prescription_number VARCHAR(30);
BEGIN
    INSERT INTO prescription_number_sequences (puskesmas_id, prescription_date, last_number)
    VALUES (p_puskesmas_id, p_prescription_date, 1)
    ON CONFLICT (puskesmas_id, prescription_date) DO UPDATE
    SET last_number = prescription_number_sequences.last_number + 1,
    updated_at = CURRENT_TIMESTAMP
    RETURNING last_number INTO v_next;
    
    v_prescription_number := 'RX-' || TO_CHAR(p_prescription_date, 'YYYYMMDD') || '-' || LPAD(v_next::TEXT, 4, '0');
    RETURN v_prescription_number;
END;
$$ LANGUAGE plpgsql;
