-- =====================================================
-- SIMPUS Database Schema - Master Data Migration
-- Version: 000002
-- Description: Master data tables for regions, ICD codes, medicines, lab tests
-- =====================================================

-- =====================================================
-- WILAYAH (ADMINISTRATIVE REGIONS)
-- =====================================================

CREATE TABLE provinces (
    code VARCHAR(2) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE regencies (
    code VARCHAR(4) PRIMARY KEY,
    province_code VARCHAR(2) NOT NULL REFERENCES provinces(code) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL DEFAULT 'kabupaten', -- kabupaten, kota
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_regencies_province ON regencies(province_code);

CREATE TABLE districts (
    code VARCHAR(7) PRIMARY KEY,
    regency_code VARCHAR(4) NOT NULL REFERENCES regencies(code) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_districts_regency ON districts(regency_code);

CREATE TABLE villages (
    code VARCHAR(10) PRIMARY KEY,
    district_code VARCHAR(7) NOT NULL REFERENCES districts(code) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    postal_code VARCHAR(5),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_villages_district ON villages(district_code);
CREATE INDEX idx_villages_name ON villages USING gin(to_tsvector('indonesian', name));

-- =====================================================
-- ICD-10 (DIAGNOSIS CODES)
-- =====================================================

CREATE TABLE icd10_chapters (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) NOT NULL,
    name VARCHAR(255) NOT NULL,
    name_id VARCHAR(255),
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE icd10_blocks (
    id SERIAL PRIMARY KEY,
    chapter_id INT REFERENCES icd10_chapters(id) ON DELETE CASCADE,
    code_range VARCHAR(20) NOT NULL,
    name VARCHAR(255) NOT NULL,
    name_id VARCHAR(255),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_icd10_blocks_chapter ON icd10_blocks(chapter_id);

CREATE TABLE icd10_codes (
    code VARCHAR(10) PRIMARY KEY,
    block_id INT REFERENCES icd10_blocks(id),
    parent_code VARCHAR(10) REFERENCES icd10_codes(code),
    name VARCHAR(500) NOT NULL,
    name_id VARCHAR(500),
    description TEXT,
    is_billable BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_icd10_codes_block ON icd10_codes(block_id);
CREATE INDEX idx_icd10_codes_parent ON icd10_codes(parent_code);
CREATE INDEX idx_icd10_codes_name ON icd10_codes USING gin(to_tsvector('indonesian', name_id));
CREATE INDEX idx_icd10_codes_code ON icd10_codes(code);

-- =====================================================
-- ICD-9-CM (PROCEDURE CODES)
-- =====================================================

CREATE TABLE icd9cm_chapters (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) NOT NULL,
    name VARCHAR(255) NOT NULL,
    name_id VARCHAR(255),
    description TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE icd9cm_codes (
    code VARCHAR(10) PRIMARY KEY,
    chapter_id INT REFERENCES icd9cm_chapters(id),
    parent_code VARCHAR(10) REFERENCES icd9cm_codes(code),
    name VARCHAR(500) NOT NULL,
    name_id VARCHAR(500),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_icd9cm_codes_chapter ON icd9cm_codes(chapter_id);
CREATE INDEX idx_icd9cm_codes_parent ON icd9cm_codes(parent_code);
CREATE INDEX idx_icd9cm_codes_name ON icd9cm_codes USING gin(to_tsvector('indonesian', name_id));

-- =====================================================
-- MEDICINE REFERENCE
-- =====================================================

CREATE TABLE medicine_categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE medicine_forms (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE medicine_units (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(50) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE medicine_routes (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE medicine_frequencies (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    times_per_day INT,
    interval_hours INT,
    instruction TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- National Drug Reference (based on e-Katalog / FORNAS)
CREATE TABLE medicine_references (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    generic_name VARCHAR(255),
    category_id UUID REFERENCES medicine_categories(id),
    form_id UUID REFERENCES medicine_forms(id),
    strength VARCHAR(100),
    unit_id UUID REFERENCES medicine_units(id),
    
    -- Classification
    is_fornas BOOLEAN DEFAULT false,
    is_generik BOOLEAN DEFAULT true,
    is_narkotika BOOLEAN DEFAULT false,
    is_psikotropika BOOLEAN DEFAULT false,
    is_prekursor BOOLEAN DEFAULT false,
    is_obat_keras BOOLEAN DEFAULT false,
    
    -- Additional info
    manufacturer VARCHAR(255),
    description TEXT,
    
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_medicine_references_name ON medicine_references USING gin(to_tsvector('indonesian', name));
CREATE INDEX idx_medicine_references_category ON medicine_references(category_id);
CREATE INDEX idx_medicine_references_code ON medicine_references(code);

CREATE TRIGGER update_medicine_references_updated_at
    BEFORE UPDATE ON medicine_references
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- LABORATORY REFERENCE
-- =====================================================

CREATE TABLE lab_categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE lab_tests (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    category_id UUID REFERENCES lab_categories(id),
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    name_id VARCHAR(255),
    
    -- Specimen
    specimen_type VARCHAR(100),
    specimen_volume VARCHAR(50),
    specimen_container VARCHAR(100),
    
    -- Result configuration
    result_type VARCHAR(20) NOT NULL DEFAULT 'numeric', -- numeric, text, option
    unit VARCHAR(50),
    decimal_places INT DEFAULT 2,
    
    -- Reference ranges (can be overridden by gender/age)
    reference_min DECIMAL(10,4),
    reference_max DECIMAL(10,4),
    reference_text TEXT,
    
    -- Critical values
    critical_low DECIMAL(10,4),
    critical_high DECIMAL(10,4),
    
    -- Options for 'option' type (JSONB array)
    result_options JSONB,
    
    -- Pricing
    base_price DECIMAL(12,2) DEFAULT 0,
    
    is_active BOOLEAN DEFAULT true,
    sort_order INT DEFAULT 0,
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_lab_tests_category ON lab_tests(category_id);
CREATE INDEX idx_lab_tests_name ON lab_tests USING gin(to_tsvector('indonesian', name));

CREATE TRIGGER update_lab_tests_updated_at
    BEFORE UPDATE ON lab_tests
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Age/Gender specific reference ranges
CREATE TABLE lab_test_references (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    test_id UUID NOT NULL REFERENCES lab_tests(id) ON DELETE CASCADE,
    gender gender,
    age_min_months INT,
    age_max_months INT,
    reference_min DECIMAL(10,4),
    reference_max DECIMAL(10,4),
    reference_text TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_lab_test_references_test ON lab_test_references(test_id);

-- =====================================================
-- TARIFF REFERENCE
-- =====================================================

CREATE TABLE tariff_categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    code VARCHAR(20) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tariffs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    puskesmas_id UUID REFERENCES puskesmas(id) ON DELETE CASCADE,
    category_id UUID REFERENCES tariff_categories(id),
    code VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    
    -- Pricing
    price DECIMAL(12,2) NOT NULL DEFAULT 0,
    bpjs_price DECIMAL(12,2),
    
    -- Validity
    effective_date DATE NOT NULL DEFAULT CURRENT_DATE,
    expired_date DATE,
    
    is_active BOOLEAN DEFAULT true,
    
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE(puskesmas_id, code, effective_date)
);

CREATE INDEX idx_tariffs_puskesmas ON tariffs(puskesmas_id);
CREATE INDEX idx_tariffs_category ON tariffs(category_id);

CREATE TRIGGER update_tariffs_updated_at
    BEFORE UPDATE ON tariffs
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- INSERT DEFAULT MASTER DATA
-- =====================================================

-- Medicine Categories
INSERT INTO medicine_categories (code, name, sort_order) VALUES
('ANTIBIOTIK', 'Antibiotik', 1),
('ANALGESIK', 'Analgesik & Antipiretik', 2),
('ANTIHIPERTENSI', 'Antihipertensi', 3),
('ANTIDIABETES', 'Antidiabetes', 4),
('VITAMIN', 'Vitamin & Mineral', 5),
('GASTROINTESTINAL', 'Obat Saluran Cerna', 6),
('RESPIRASI', 'Obat Saluran Napas', 7),
('KULIT', 'Obat Kulit', 8),
('MATA', 'Obat Mata', 9),
('THT', 'Obat THT', 10),
('LAINNYA', 'Lain-lain', 99);

-- Medicine Forms
INSERT INTO medicine_forms (code, name) VALUES
('TABLET', 'Tablet'),
('KAPLET', 'Kaplet'),
('KAPSUL', 'Kapsul'),
('SIRUP', 'Sirup'),
('SUSPENSI', 'Suspensi'),
('DROPS', 'Tetes/Drops'),
('INJEKSI', 'Injeksi'),
('SALEP', 'Salep'),
('KRIM', 'Krim'),
('GEL', 'Gel'),
('SUPPOSITORIA', 'Suppositoria'),
('INHALER', 'Inhaler'),
('PATCH', 'Patch'),
('INFUS', 'Infus');

-- Medicine Units
INSERT INTO medicine_units (code, name) VALUES
('TAB', 'Tablet'),
('KAP', 'Kaplet'),
('CAPS', 'Kapsul'),
('BTL', 'Botol'),
('AMP', 'Ampul'),
('VIAL', 'Vial'),
('TUBE', 'Tube'),
('SACHET', 'Sachet'),
('STRIP', 'Strip'),
('BOX', 'Box'),
('ML', 'mL'),
('MG', 'mg'),
('G', 'gram');

-- Medicine Routes
INSERT INTO medicine_routes (code, name) VALUES
('PO', 'Per Oral'),
('SL', 'Sublingual'),
('IV', 'Intravena'),
('IM', 'Intramuskular'),
('SC', 'Subkutan'),
('TOPIKAL', 'Topikal'),
('INHALASI', 'Inhalasi'),
('REKTAL', 'Rektal'),
('VAGINAL', 'Vaginal'),
('OPHTHALMIC', 'Tetes Mata'),
('OTIC', 'Tetes Telinga'),
('NASAL', 'Nasal');

-- Medicine Frequencies
INSERT INTO medicine_frequencies (code, name, times_per_day, interval_hours, instruction) VALUES
('1X1', '1 x sehari', 1, 24, 'Diminum 1 kali sehari'),
('2X1', '2 x sehari', 2, 12, 'Diminum 2 kali sehari (pagi dan malam)'),
('3X1', '3 x sehari', 3, 8, 'Diminum 3 kali sehari (pagi, siang, malam)'),
('4X1', '4 x sehari', 4, 6, 'Diminum 4 kali sehari (setiap 6 jam)'),
('PRN', 'Bila perlu', NULL, NULL, 'Diminum bila perlu'),
('AC', 'Sebelum makan', NULL, NULL, 'Diminum sebelum makan'),
('PC', 'Sesudah makan', NULL, NULL, 'Diminum sesudah makan'),
('HS', 'Sebelum tidur', 1, NULL, 'Diminum sebelum tidur'),
('STAT', 'Segera', 1, NULL, 'Diminum segera');

-- Lab Categories
INSERT INTO lab_categories (code, name, sort_order) VALUES
('HEMATOLOGI', 'Hematologi', 1),
('KIMIA', 'Kimia Darah', 2),
('URINALISA', 'Urinalisa', 3),
('FESES', 'Feses Rutin', 4),
('SEROLOGI', 'Serologi', 5),
('MIKROBIOLOGI', 'Mikrobiologi', 6),
('IMUNOLOGI', 'Imunologi', 7);

-- Lab Tests
INSERT INTO lab_tests (code, name, name_id, category_id, specimen_type, result_type, unit, reference_min, reference_max, base_price) VALUES
-- Hematologi
('HB', 'Hemoglobin', 'Hemoglobin', (SELECT id FROM lab_categories WHERE code = 'HEMATOLOGI'), 'Darah EDTA', 'numeric', 'g/dL', 12.0, 16.0, 25000),
('HCT', 'Hematokrit', 'Hematokrit', (SELECT id FROM lab_categories WHERE code = 'HEMATOLOGI'), 'Darah EDTA', 'numeric', '%', 36.0, 48.0, 20000),
('RBC', 'Eritrosit', 'Sel Darah Merah', (SELECT id FROM lab_categories WHERE code = 'HEMATOLOGI'), 'Darah EDTA', 'numeric', 'juta/uL', 4.0, 5.5, 25000),
('WBC', 'Leukosit', 'Sel Darah Putih', (SELECT id FROM lab_categories WHERE code = 'HEMATOLOGI'), 'Darah EDTA', 'numeric', 'ribu/uL', 4.0, 10.0, 25000),
('PLT', 'Trombosit', 'Trombosit', (SELECT id FROM lab_categories WHERE code = 'HEMATOLOGI'), 'Darah EDTA', 'numeric', 'ribu/uL', 150, 400, 25000),
('LED', 'LED', 'Laju Endap Darah', (SELECT id FROM lab_categories WHERE code = 'HEMATOLOGI'), 'Darah EDTA', 'numeric', 'mm/jam', 0, 20, 15000),

-- Kimia Darah
('GDS', 'Gula Darah Sewaktu', 'Glukosa Darah Sewaktu', (SELECT id FROM lab_categories WHERE code = 'KIMIA'), 'Serum', 'numeric', 'mg/dL', 70, 200, 20000),
('GDP', 'Gula Darah Puasa', 'Glukosa Darah Puasa', (SELECT id FROM lab_categories WHERE code = 'KIMIA'), 'Serum', 'numeric', 'mg/dL', 70, 100, 20000),
('GD2PP', 'Gula Darah 2 Jam PP', 'Glukosa 2 Jam Post Prandial', (SELECT id FROM lab_categories WHERE code = 'KIMIA'), 'Serum', 'numeric', 'mg/dL', 70, 140, 20000),
('CHOL', 'Kolesterol Total', 'Kolesterol Total', (SELECT id FROM lab_categories WHERE code = 'KIMIA'), 'Serum', 'numeric', 'mg/dL', 0, 200, 30000),
('TG', 'Trigliserida', 'Trigliserida', (SELECT id FROM lab_categories WHERE code = 'KIMIA'), 'Serum', 'numeric', 'mg/dL', 0, 150, 30000),
('URIC', 'Asam Urat', 'Asam Urat', (SELECT id FROM lab_categories WHERE code = 'KIMIA'), 'Serum', 'numeric', 'mg/dL', 2.5, 7.0, 25000),
('CREA', 'Kreatinin', 'Kreatinin', (SELECT id FROM lab_categories WHERE code = 'KIMIA'), 'Serum', 'numeric', 'mg/dL', 0.5, 1.2, 30000),
('UREA', 'Ureum', 'Ureum', (SELECT id FROM lab_categories WHERE code = 'KIMIA'), 'Serum', 'numeric', 'mg/dL', 10, 50, 30000),

-- Urinalisa
('URINE_RUTIN', 'Urine Rutin', 'Urinalisa Rutin', (SELECT id FROM lab_categories WHERE code = 'URINALISA'), 'Urine', 'text', NULL, NULL, NULL, 20000),
('PROTEIN_URINE', 'Protein Urine', 'Protein Urine', (SELECT id FROM lab_categories WHERE code = 'URINALISA'), 'Urine', 'option', NULL, NULL, NULL, 15000),
('GLUKOSA_URINE', 'Glukosa Urine', 'Glukosa Urine', (SELECT id FROM lab_categories WHERE code = 'URINALISA'), 'Urine', 'option', NULL, NULL, NULL, 15000),

-- Serologi
('HBSAG', 'HBsAg', 'Hepatitis B Surface Antigen', (SELECT id FROM lab_categories WHERE code = 'SEROLOGI'), 'Serum', 'option', NULL, NULL, NULL, 50000),
('ANTI_HCV', 'Anti HCV', 'Antibodi Hepatitis C', (SELECT id FROM lab_categories WHERE code = 'SEROLOGI'), 'Serum', 'option', NULL, NULL, NULL, 75000),
('HIV_RAPID', 'HIV Rapid Test', 'Tes Cepat HIV', (SELECT id FROM lab_categories WHERE code = 'SEROLOGI'), 'Serum', 'option', NULL, NULL, NULL, 50000),
('SYPHILIS', 'Syphilis Rapid', 'Tes Cepat Syphilis', (SELECT id FROM lab_categories WHERE code = 'SEROLOGI'), 'Serum', 'option', NULL, NULL, NULL, 35000),
('WIDAL', 'Widal', 'Tes Widal', (SELECT id FROM lab_categories WHERE code = 'SEROLOGI'), 'Serum', 'text', NULL, NULL, NULL, 45000),
('PP_TEST', 'Tes Kehamilan', 'Tes Kehamilan', (SELECT id FROM lab_categories WHERE code = 'SEROLOGI'), 'Urine', 'option', NULL, NULL, NULL, 25000);

-- Update result_options for option type tests
UPDATE lab_tests SET result_options = '["Negatif", "Positif"]' WHERE result_type = 'option';
UPDATE lab_tests SET result_options = '["Negatif", "Positif 1+", "Positif 2+", "Positif 3+", "Positif 4+"]' WHERE code IN ('PROTEIN_URINE', 'GLUKOSA_URINE');

-- Tariff Categories
INSERT INTO tariff_categories (code, name, sort_order) VALUES
('ADMIN', 'Administrasi', 1),
('KONSULTASI', 'Konsultasi Dokter', 2),
('TINDAKAN', 'Tindakan Medis', 3),
('LAB', 'Laboratorium', 4),
('OBAT', 'Obat & BHP', 5),
('RAWAT_INAP', 'Rawat Inap', 6);

-- Sample Province (will be populated with full data via seeder)
INSERT INTO provinces (code, name) VALUES
('31', 'DKI Jakarta'),
('32', 'Jawa Barat'),
('33', 'Jawa Tengah'),
('34', 'DI Yogyakarta'),
('35', 'Jawa Timur'),
('36', 'Banten');

-- Sample Regency
INSERT INTO regencies (code, province_code, name, type) VALUES
('3171', '31', 'Jakarta Pusat', 'kota'),
('3172', '31', 'Jakarta Utara', 'kota'),
('3173', '31', 'Jakarta Barat', 'kota'),
('3174', '31', 'Jakarta Selatan', 'kota'),
('3175', '31', 'Jakarta Timur', 'kota'),
('3201', '32', 'Bogor', 'kabupaten'),
('3271', '32', 'Bogor', 'kota'),
('3273', '32', 'Bandung', 'kota');

-- Sample Districts
INSERT INTO districts (code, regency_code, name) VALUES
('3171010', '3171', 'Gambir'),
('3171020', '3171', 'Sawah Besar'),
('3171030', '3171', 'Kemayoran'),
('3171040', '3171', 'Senen'),
('3171050', '3171', 'Cempaka Putih'),
('3171060', '3171', 'Menteng'),
('3171070', '3171', 'Tanah Abang'),
('3171080', '3171', 'Johar Baru');

-- Sample Villages
INSERT INTO villages (code, district_code, name, postal_code) VALUES
('3171010001', '3171010', 'Gambir', '10110'),
('3171010002', '3171010', 'Cideng', '10150'),
('3171010003', '3171010', 'Petojo Selatan', '10160'),
('3171010004', '3171010', 'Petojo Utara', '10130'),
('3171010005', '3171010', 'Kebon Kelapa', '10120'),
('3171010006', '3171010', 'Duri Pulo', '10140');
