-- ============================================================
-- HOSPITAL INFORMATION SYSTEM (HIS) - DATABASE SCHEMA
-- ============================================================
-- Engine: MySQL 5.7+/8.0
-- Charset: utf8mb4
-- ============================================================

CREATE DATABASE IF NOT EXISTS his_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE his_db;

SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 1. ROLES & USERS (Staff: receptionist, doctor, lab, pharmacist, admin)
-- ============================================================

CREATE TABLE roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,      -- admin, receptionist, doctor, lab_tech, pharmacist, cashier, nurse
    role_description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO roles (role_name, role_description) VALUES
('admin', 'Full system access'),
('receptionist', 'Patient registration, billing front desk'),
('doctor', 'Consultation, diagnosis, prescriptions'),
('nurse', 'Triage and vitals'),
('lab_tech', 'Lab tests and results'),
('pharmacist', 'Pharmacy dispensing and inventory'),
('cashier', 'Payments and receipts'),
('accountant', 'Accounting and financial reports');

CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL,
    dept_description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Unified staff table (replaces his_docs, covers all employee types)
CREATE TABLE staff (
    staff_id INT AUTO_INCREMENT PRIMARY KEY,
    staff_number VARCHAR(20) NOT NULL UNIQUE,    -- e.g. EMP-00001
    role_id INT NOT NULL,
    dept_id INT DEFAULT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    profile_pic VARCHAR(255) DEFAULT 'default.png',
    gender ENUM('Male','Female','Other') DEFAULT NULL,
    id_number VARCHAR(50) DEFAULT NULL,
    address VARCHAR(255) DEFAULT NULL,
    status ENUM('active','inactive','suspended','terminated') DEFAULT 'active',
    failed_login_attempts INT DEFAULT 0,
    locked_until DATETIME DEFAULT NULL,
    last_login DATETIME DEFAULT NULL,
    must_change_password TINYINT(1) DEFAULT 1,
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(role_id),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE SET NULL,
    INDEX idx_staff_role (role_id),
    INDEX idx_staff_status (status)
) ENGINE=InnoDB;

-- Password reset requests
CREATE TABLE password_resets (
    reset_id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    token VARCHAR(255) NOT NULL,
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    used TINYINT(1) DEFAULT 0,
    status ENUM('pending','approved','rejected','completed') DEFAULT 'pending',
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Session tracking (for force logout, active session list)
CREATE TABLE staff_sessions (
    session_id VARCHAR(128) PRIMARY KEY,
    staff_id INT NOT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    login_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- 2. PATIENTS
-- ============================================================

CREATE TABLE patients (
    patient_id INT AUTO_INCREMENT PRIMARY KEY,
    patient_number VARCHAR(20) NOT NULL UNIQUE,  -- e.g. PAT-00001
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    dob DATE DEFAULT NULL,
    gender ENUM('Male','Female','Other') NOT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    email VARCHAR(150) DEFAULT NULL,
    id_number VARCHAR(50) DEFAULT NULL,
    address VARCHAR(255) DEFAULT NULL,
    next_of_kin_name VARCHAR(150) DEFAULT NULL,
    next_of_kin_phone VARCHAR(20) DEFAULT NULL,
    blood_group VARCHAR(5) DEFAULT NULL,
    allergies TEXT DEFAULT NULL,
    sha_number VARCHAR(50) DEFAULT NULL,          -- Social Health Authority (Kenya) ID
    insurance_provider VARCHAR(100) DEFAULT NULL,
    insurance_number VARCHAR(100) DEFAULT NULL,
    photo VARCHAR(255) DEFAULT NULL,
    patient_type ENUM('OutPatient','InPatient') DEFAULT 'OutPatient',
    registered_by INT DEFAULT NULL,               -- staff_id of receptionist/doctor
    status ENUM('active','discharged','deceased','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (registered_by) REFERENCES staff(staff_id) ON DELETE SET NULL,
    INDEX idx_patient_number (patient_number),
    INDEX idx_patient_name (last_name, first_name)
) ENGINE=InnoDB;

-- ============================================================
-- 3. VISITS (central episode-of-care pipeline)
-- ============================================================

CREATE TABLE visits (
    visit_id INT AUTO_INCREMENT PRIMARY KEY,
    visit_number VARCHAR(20) NOT NULL UNIQUE,     -- e.g. VIS-20260630-0001
    patient_id INT NOT NULL,
    visit_type ENUM('OutPatient','InPatient','Emergency') DEFAULT 'OutPatient',
    status ENUM(
        'registered',
        'awaiting_triage',
        'awaiting_doctor',
        'awaiting_lab_payment',
        'awaiting_lab',
        'lab_in_progress',
        'awaiting_prescription',
        'awaiting_pharmacy_payment',
        'awaiting_dispensing',
        'admitted',
        'discharged',
        'completed',
        'cancelled'
    ) DEFAULT 'registered',
    registered_by INT DEFAULT NULL,               -- receptionist/doctor
    attending_doctor_id INT DEFAULT NULL,
    chief_complaint TEXT DEFAULT NULL,
    diagnosis TEXT DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    checked_in_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (registered_by) REFERENCES staff(staff_id) ON DELETE SET NULL,
    FOREIGN KEY (attending_doctor_id) REFERENCES staff(staff_id) ON DELETE SET NULL,
    INDEX idx_visit_status (status),
    INDEX idx_visit_patient (patient_id)
) ENGINE=InnoDB;

-- Visit status history / audit trail of the pipeline
CREATE TABLE visit_status_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    old_status VARCHAR(50) DEFAULT NULL,
    new_status VARCHAR(50) NOT NULL,
    changed_by INT DEFAULT NULL,
    remarks VARCHAR(255) DEFAULT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES visits(visit_id) ON DELETE CASCADE,
    FOREIGN KEY (changed_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 4. TRIAGE / VITALS
-- ============================================================

CREATE TABLE triage (
    triage_id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    patient_id INT NOT NULL,
    recorded_by INT DEFAULT NULL,                 -- nurse
    temperature DECIMAL(4,1) DEFAULT NULL,         -- Celsius
    blood_pressure_systolic INT DEFAULT NULL,
    blood_pressure_diastolic INT DEFAULT NULL,
    pulse_rate INT DEFAULT NULL,
    respiratory_rate INT DEFAULT NULL,
    spo2 INT DEFAULT NULL,                         -- oxygen saturation %
    weight_kg DECIMAL(5,2) DEFAULT NULL,
    height_cm DECIMAL(5,2) DEFAULT NULL,
    bmi DECIMAL(5,2) DEFAULT NULL,
    triage_priority ENUM('low','normal','urgent','critical') DEFAULT 'normal',
    notes TEXT DEFAULT NULL,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES visits(visit_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 5. INPATIENT ADMISSION / DISCHARGE / TRANSFER
-- ============================================================

CREATE TABLE wards (
    ward_id INT AUTO_INCREMENT PRIMARY KEY,
    ward_name VARCHAR(100) NOT NULL,
    ward_type ENUM('General','Maternity','ICU','Pediatric','Surgical','Isolation') DEFAULT 'General',
    capacity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE beds (
    bed_id INT AUTO_INCREMENT PRIMARY KEY,
    ward_id INT NOT NULL,
    bed_number VARCHAR(20) NOT NULL,
    status ENUM('available','occupied','maintenance') DEFAULT 'available',
    FOREIGN KEY (ward_id) REFERENCES wards(ward_id) ON DELETE CASCADE,
    UNIQUE KEY uq_ward_bed (ward_id, bed_number)
) ENGINE=InnoDB;

CREATE TABLE admissions (
    admission_id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    patient_id INT NOT NULL,
    bed_id INT DEFAULT NULL,
    admitted_by INT DEFAULT NULL,
    admission_reason TEXT DEFAULT NULL,
    admitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    discharged_at DATETIME DEFAULT NULL,
    discharge_summary TEXT DEFAULT NULL,
    discharged_by INT DEFAULT NULL,
    status ENUM('admitted','discharged') DEFAULT 'admitted',
    FOREIGN KEY (visit_id) REFERENCES visits(visit_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (bed_id) REFERENCES beds(bed_id) ON DELETE SET NULL,
    FOREIGN KEY (admitted_by) REFERENCES staff(staff_id) ON DELETE SET NULL,
    FOREIGN KEY (discharged_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE patient_transfers (
    transfer_id INT AUTO_INCREMENT PRIMARY KEY,
    admission_id INT NOT NULL,
    from_bed_id INT DEFAULT NULL,
    to_bed_id INT NOT NULL,
    reason VARCHAR(255) DEFAULT NULL,
    transferred_by INT DEFAULT NULL,
    transferred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admission_id) REFERENCES admissions(admission_id) ON DELETE CASCADE,
    FOREIGN KEY (from_bed_id) REFERENCES beds(bed_id) ON DELETE SET NULL,
    FOREIGN KEY (to_bed_id) REFERENCES beds(bed_id) ON DELETE SET NULL,
    FOREIGN KEY (transferred_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 6. LABORATORY
-- ============================================================

CREATE TABLE lab_test_catalog (
    test_id INT AUTO_INCREMENT PRIMARY KEY,
    test_name VARCHAR(150) NOT NULL,
    test_code VARCHAR(30) DEFAULT NULL,
    category VARCHAR(100) DEFAULT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    turnaround_time VARCHAR(50) DEFAULT NULL,      -- e.g. "2 hours"
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE lab_equipment (
    equipment_id INT AUTO_INCREMENT PRIMARY KEY,
    equipment_name VARCHAR(150) NOT NULL,
    serial_number VARCHAR(100) DEFAULT NULL,
    status ENUM('operational','maintenance','decommissioned') DEFAULT 'operational',
    purchase_date DATE DEFAULT NULL,
    last_serviced DATE DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE lab_orders (
    lab_order_id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    patient_id INT NOT NULL,
    ordered_by INT DEFAULT NULL,                   -- doctor
    test_id INT NOT NULL,
    status ENUM('ordered','paid','sample_collected','in_progress','completed','cancelled') DEFAULT 'ordered',
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES visits(visit_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (ordered_by) REFERENCES staff(staff_id) ON DELETE SET NULL,
    FOREIGN KEY (test_id) REFERENCES lab_test_catalog(test_id)
) ENGINE=InnoDB;

CREATE TABLE lab_results (
    result_id INT AUTO_INCREMENT PRIMARY KEY,
    lab_order_id INT NOT NULL,
    performed_by INT DEFAULT NULL,                 -- lab_tech
    result_value TEXT DEFAULT NULL,
    result_notes TEXT DEFAULT NULL,
    is_abnormal TINYINT(1) DEFAULT 0,
    attachment VARCHAR(255) DEFAULT NULL,           -- e.g. scanned report/PDF
    verified_by INT DEFAULT NULL,
    result_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lab_order_id) REFERENCES lab_orders(lab_order_id) ON DELETE CASCADE,
    FOREIGN KEY (performed_by) REFERENCES staff(staff_id) ON DELETE SET NULL,
    FOREIGN KEY (verified_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 7. PHARMACY
-- ============================================================

CREATE TABLE pharm_categories (
    pharm_cat_id INT AUTO_INCREMENT PRIMARY KEY,
    cat_name VARCHAR(100) NOT NULL,
    cat_description VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE pharmaceuticals (
    pharm_id INT AUTO_INCREMENT PRIMARY KEY,
    pharm_cat_id INT DEFAULT NULL,
    drug_name VARCHAR(150) NOT NULL,
    generic_name VARCHAR(150) DEFAULT NULL,
    unit VARCHAR(30) DEFAULT NULL,                  -- tablet, ml, capsule
    unit_price DECIMAL(10,2) NOT NULL DEFAULT 0,
    reorder_level INT DEFAULT 10,
    quantity_in_stock INT DEFAULT 0,
    expiry_date DATE DEFAULT NULL,
    batch_number VARCHAR(50) DEFAULT NULL,
    vendor_id INT DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (pharm_cat_id) REFERENCES pharm_categories(pharm_cat_id) ON DELETE SET NULL,
    INDEX idx_pharm_stock (quantity_in_stock)
) ENGINE=InnoDB;

-- Stock movement ledger (every addition/deduction recorded - audit-safe inventory)
CREATE TABLE pharm_stock_ledger (
    ledger_id INT AUTO_INCREMENT PRIMARY KEY,
    pharm_id INT NOT NULL,
    change_qty INT NOT NULL,                        -- positive = stock in, negative = stock out
    reason ENUM('purchase','dispense','adjustment','expired','return') NOT NULL,
    reference_id INT DEFAULT NULL,                   -- e.g. dispense_id or purchase_id
    performed_by INT DEFAULT NULL,
    balance_after INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pharm_id) REFERENCES pharmaceuticals(pharm_id) ON DELETE CASCADE,
    FOREIGN KEY (performed_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE prescriptions (
    prescription_id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    patient_id INT NOT NULL,
    prescribed_by INT DEFAULT NULL,                  -- doctor
    status ENUM('pending','paid','partially_dispensed','dispensed','cancelled') DEFAULT 'pending',
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES visits(visit_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (prescribed_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE prescription_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    prescription_id INT NOT NULL,
    pharm_id INT NOT NULL,
    quantity INT NOT NULL,
    dosage_instructions VARCHAR(255) DEFAULT NULL,    -- e.g. "1 tab BID x 5 days"
    unit_price DECIMAL(10,2) NOT NULL,
    dispensed_qty INT DEFAULT 0,
    dispensed_by INT DEFAULT NULL,
    dispensed_at DATETIME DEFAULT NULL,
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(prescription_id) ON DELETE CASCADE,
    FOREIGN KEY (pharm_id) REFERENCES pharmaceuticals(pharm_id),
    FOREIGN KEY (dispensed_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 8. BILLING / INVOICES / PAYMENTS
-- ============================================================

CREATE TABLE service_catalog (
    service_id INT AUTO_INCREMENT PRIMARY KEY,
    service_name VARCHAR(150) NOT NULL,             -- Consultation Fee, Admission Fee, etc.
    service_type ENUM('consultation','admission','lab','pharmacy','procedure','other') DEFAULT 'other',
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE invoices (
    invoice_id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(30) NOT NULL UNIQUE,      -- INV-20260630-0001
    visit_id INT NOT NULL,
    patient_id INT NOT NULL,
    invoice_type ENUM('consultation','lab','pharmacy','admission','procedure','other') DEFAULT 'other',
    reference_id INT DEFAULT NULL,                    -- lab_order_id / prescription_id / admission_id etc
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    discount DECIMAL(12,2) NOT NULL DEFAULT 0,
    tax DECIMAL(12,2) NOT NULL DEFAULT 0,
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0,
    balance DECIMAL(12,2) GENERATED ALWAYS AS (total_amount - amount_paid) STORED,
    status ENUM('unpaid','partial','paid','cancelled','waived') DEFAULT 'unpaid',
    generated_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES visits(visit_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (generated_by) REFERENCES staff(staff_id) ON DELETE SET NULL,
    INDEX idx_invoice_status (status)
) ENGINE=InnoDB;

CREATE TABLE invoice_items (
    invoice_item_id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    description VARCHAR(255) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    line_total DECIMAL(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED,
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Payments (supports split/multiple payments per invoice)
CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    payment_reference VARCHAR(40) NOT NULL UNIQUE,    -- PMT-20260630-0001
    invoice_id INT NOT NULL,
    patient_id INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    payment_method ENUM('cash','mpesa','sha','card','bank_transfer','insurance','waiver') NOT NULL,
    payment_status ENUM('pending','success','failed','reversed') DEFAULT 'pending',
    received_by INT DEFAULT NULL,                      -- cashier staff_id, null for self-service mpesa
    paid_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes VARCHAR(255) DEFAULT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (received_by) REFERENCES staff(staff_id) ON DELETE SET NULL,
    INDEX idx_payment_status (payment_status)
) ENGINE=InnoDB;

-- M-Pesa STK push transaction log (stubbed - structure ready for live Daraja API)
CREATE TABLE mpesa_transactions (
    mpesa_txn_id INT AUTO_INCREMENT PRIMARY KEY,
    payment_id INT DEFAULT NULL,
    invoice_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    merchant_request_id VARCHAR(100) DEFAULT NULL,
    checkout_request_id VARCHAR(100) DEFAULT NULL,
    mpesa_receipt_number VARCHAR(50) DEFAULT NULL,
    result_code VARCHAR(10) DEFAULT NULL,
    result_desc VARCHAR(255) DEFAULT NULL,
    status ENUM('initiated','pending','success','failed','cancelled','timeout') DEFAULT 'initiated',
    raw_callback TEXT DEFAULT NULL,                     -- store full JSON callback for debugging
    initiated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME DEFAULT NULL,
    FOREIGN KEY (payment_id) REFERENCES payments(payment_id) ON DELETE SET NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- SHA (Social Health Authority) claims log (stubbed - structure ready for live SHA API)
CREATE TABLE sha_claims (
    claim_id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    patient_id INT NOT NULL,
    sha_number VARCHAR(50) NOT NULL,
    claim_amount DECIMAL(12,2) NOT NULL,
    claim_reference VARCHAR(100) DEFAULT NULL,
    status ENUM('submitted','verified','approved','rejected','paid') DEFAULT 'submitted',
    submitted_by INT DEFAULT NULL,
    rejection_reason VARCHAR(255) DEFAULT NULL,
    raw_response TEXT DEFAULT NULL,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME DEFAULT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (submitted_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Receipts (generated after successful payment)
CREATE TABLE receipts (
    receipt_id INT AUTO_INCREMENT PRIMARY KEY,
    receipt_number VARCHAR(30) NOT NULL UNIQUE,        -- RCT-20260630-0001
    payment_id INT NOT NULL,
    invoice_id INT NOT NULL,
    patient_id INT NOT NULL,
    issued_by INT DEFAULT NULL,
    amount DECIMAL(12,2) NOT NULL,
    issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_id) REFERENCES payments(payment_id) ON DELETE CASCADE,
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (issued_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 9. ACCOUNTING (Payables / Receivables)
-- ============================================================

CREATE TABLE acc_payable (
    payable_id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_id INT DEFAULT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    amount_paid DECIMAL(12,2) DEFAULT 0,
    due_date DATE DEFAULT NULL,
    status ENUM('pending','partial','paid','overdue') DEFAULT 'pending',
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE acc_receivable (
    receivable_id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT DEFAULT NULL,
    patient_id INT DEFAULT NULL,
    description VARCHAR(255) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    amount_received DECIMAL(12,2) DEFAULT 0,
    due_date DATE DEFAULT NULL,
    status ENUM('pending','partial','paid','overdue','written_off') DEFAULT 'pending',
    created_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(invoice_id) ON DELETE SET NULL,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 10. VENDORS / ASSETS / INVENTORY
-- ============================================================

CREATE TABLE vendors (
    vendor_id INT AUTO_INCREMENT PRIMARY KEY,
    vendor_name VARCHAR(150) NOT NULL,
    contact_person VARCHAR(150) DEFAULT NULL,
    phone VARCHAR(20) DEFAULT NULL,
    email VARCHAR(150) DEFAULT NULL,
    address VARCHAR(255) DEFAULT NULL,
    supplies VARCHAR(255) DEFAULT NULL,             -- e.g. "Pharmaceuticals, Lab equipment"
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

ALTER TABLE pharmaceuticals ADD CONSTRAINT fk_pharm_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(vendor_id) ON DELETE SET NULL;

CREATE TABLE equipment_assets (
    asset_id INT AUTO_INCREMENT PRIMARY KEY,
    asset_name VARCHAR(150) NOT NULL,
    asset_category VARCHAR(100) DEFAULT NULL,        -- surgical, lab, general
    serial_number VARCHAR(100) DEFAULT NULL,
    vendor_id INT DEFAULT NULL,
    purchase_date DATE DEFAULT NULL,
    purchase_cost DECIMAL(12,2) DEFAULT NULL,
    status ENUM('in_use','available','maintenance','decommissioned') DEFAULT 'available',
    location VARCHAR(150) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vendor_id) REFERENCES vendors(vendor_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 11. SURGICAL / THEATRE
-- ============================================================

CREATE TABLE theatre_patients (
    theatre_record_id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    patient_id INT NOT NULL,
    surgeon_id INT DEFAULT NULL,
    procedure_name VARCHAR(255) NOT NULL,
    scheduled_at DATETIME DEFAULT NULL,
    status ENUM('scheduled','in_progress','completed','cancelled') DEFAULT 'scheduled',
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES visits(visit_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (surgeon_id) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE surgery_records (
    surgery_id INT AUTO_INCREMENT PRIMARY KEY,
    theatre_record_id INT NOT NULL,
    start_time DATETIME DEFAULT NULL,
    end_time DATETIME DEFAULT NULL,
    outcome TEXT DEFAULT NULL,
    complications TEXT DEFAULT NULL,
    recorded_by INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (theatre_record_id) REFERENCES theatre_patients(theatre_record_id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 12. PAYROLL
-- ============================================================

CREATE TABLE payrolls (
    payroll_id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    pay_period_month TINYINT NOT NULL,
    pay_period_year SMALLINT NOT NULL,
    basic_salary DECIMAL(12,2) NOT NULL,
    allowances DECIMAL(12,2) DEFAULT 0,
    deductions DECIMAL(12,2) DEFAULT 0,
    net_pay DECIMAL(12,2) GENERATED ALWAYS AS (basic_salary + allowances - deductions) STORED,
    status ENUM('draft','approved','paid') DEFAULT 'draft',
    generated_by INT DEFAULT NULL,
    generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE CASCADE,
    FOREIGN KEY (generated_by) REFERENCES staff(staff_id) ON DELETE SET NULL,
    UNIQUE KEY uq_payroll_period (staff_id, pay_period_month, pay_period_year)
) ENGINE=InnoDB;

-- ============================================================
-- 13. MEDICAL RECORDS
-- ============================================================

CREATE TABLE medical_records (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    patient_id INT NOT NULL,
    recorded_by INT DEFAULT NULL,
    diagnosis TEXT DEFAULT NULL,
    treatment_plan TEXT DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    attachment VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (visit_id) REFERENCES visits(visit_id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 14. SYSTEM LOGS (activity log + error log)
-- ============================================================

CREATE TABLE activity_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT DEFAULT NULL,
    action VARCHAR(100) NOT NULL,                    -- e.g. "Registered Patient", "Generated Invoice"
    module VARCHAR(50) DEFAULT NULL,                  -- e.g. "patients", "billing"
    description VARCHAR(500) DEFAULT NULL,
    record_id INT DEFAULT NULL,                       -- ID of affected record
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE SET NULL,
    INDEX idx_activity_module (module),
    INDEX idx_activity_date (created_at)
) ENGINE=InnoDB;

CREATE TABLE error_logs (
    error_id INT AUTO_INCREMENT PRIMARY KEY,
    error_level ENUM('notice','warning','error','critical') DEFAULT 'error',
    error_message TEXT NOT NULL,
    error_file VARCHAR(255) DEFAULT NULL,
    error_line INT DEFAULT NULL,
    request_uri VARCHAR(500) DEFAULT NULL,
    staff_id INT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    stack_trace TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_error_level (error_level),
    INDEX idx_error_date (created_at)
) ENGINE=InnoDB;

-- Login audit (separate from activity for security review)
CREATE TABLE login_logs (
    login_log_id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT DEFAULT NULL,
    username_attempted VARCHAR(50) DEFAULT NULL,
    status ENUM('success','failed','locked') NOT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    user_agent VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- 15. SYSTEM SETTINGS
-- ============================================================

CREATE TABLE settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT DEFAULT NULL,
    description VARCHAR(255) DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value, description) VALUES
('hospital_name', 'City Medical Centre', 'Hospital display name'),
('hospital_phone', '+254700000000', 'Hospital contact phone'),
('hospital_email', 'info@hospital.com', 'Hospital contact email'),
('hospital_address', 'Nairobi, Kenya', 'Hospital physical address'),
('currency', 'KES', 'Default currency'),
('mpesa_env', 'sandbox', 'M-Pesa Daraja environment: sandbox or production'),
('mpesa_shortcode', '', 'M-Pesa Till/Paybill number'),
('mpesa_consumer_key', '', 'M-Pesa Daraja consumer key'),
('mpesa_consumer_secret', '', 'M-Pesa Daraja consumer secret'),
('mpesa_passkey', '', 'M-Pesa Daraja passkey'),
('sha_api_url', '', 'SHA API base URL'),
('sha_api_key', '', 'SHA API key'),
('invoice_prefix', 'INV', 'Invoice number prefix'),
('receipt_prefix', 'RCT', 'Receipt number prefix');

SET FOREIGN_KEY_CHECKS = 1;