-- ============================================================
-- DATABASE SCHEMA: KeluargaSehat
-- Screening & Monitoring Risiko Stunting - PWA
-- ============================================================

-- 1. Users table (all roles: family/ayah, tenaga_kesehatan, admin)
CREATE TABLE users (
    id              CHAR(36) PRIMARY KEY,           -- UUID
    username        VARCHAR(50) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    role            ENUM('keluarga','nakes','admin') NOT NULL,
    full_name       VARCHAR(100) NOT NULL,
    phone           VARCHAR(20),
    email           VARCHAR(100),
    avatar_url      VARCHAR(255),
    is_active       BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. Families (Keluarga PMI)
CREATE TABLE families (
    id              CHAR(36) PRIMARY KEY,
    user_id         CHAR(36) NOT NULL,              -- linked user (ayah/keluarga)
    father_name     VARCHAR(100) NOT NULL,
    mother_name     VARCHAR(100),
    address         TEXT,
    district        VARCHAR(100),                   -- kecamatan
    village         VARCHAR(100),                   -- desa
    phone           VARCHAR(20),
    income          DECIMAL(12,2),                  -- pendapatan
    education       VARCHAR(50),                    -- pendidikan
    water_source    VARCHAR(100),                   -- sumber air bersih
    latrine_type    VARCHAR(100),                   -- jamban
    has_pets        BOOLEAN DEFAULT FALSE,           -- hewan peliharaan
    ventilation     VARCHAR(100),                   -- ventilasi udara
    smoking_status  BOOLEAN DEFAULT FALSE,           -- merokok
    pmi_since       DATE,                           -- sejak kapan menjadi PMI
    parent_disease_history TEXT,                     -- riwayat penyakit orang tua
    parent_mental_health   TEXT,                     -- kesehatan mental orang tua
    assigned_nakes_id CHAR(36),                     -- tenaga kesehatan yang ditugaskan
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (assigned_nakes_id) REFERENCES users(id)
);

-- 3. Children (Balita)
CREATE TABLE children (
    id              CHAR(36) PRIMARY KEY,
    family_id       CHAR(36) NOT NULL,
    name            VARCHAR(100) NOT NULL,
    birth_date      DATE NOT NULL,
    birth_place     VARCHAR(100),
    gender          ENUM('L','P') NOT NULL,
    birth_weight    DECIMAL(5,2),                   -- berat badan lahir (kg)
    birth_length    DECIMAL(5,2),                   -- panjang badan lahir (cm)
    blood_type      VARCHAR(5),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE CASCADE
);

-- 4. Screening records (Skrining Anak)
CREATE TABLE screening_child (
    id              CHAR(36) PRIMARY KEY,
    child_id        CHAR(36) NOT NULL,
    screened_by     CHAR(36) NOT NULL,              -- nakes user_id
    screening_date  DATE NOT NULL,
    weight          DECIMAL(5,2),                   -- berat badan sekarang (kg)
    height          DECIMAL(5,2),                   -- tinggi badan (cm)
    head_circumference  DECIMAL(5,2),               -- lingkar kepala (cm)
    arm_circumference   DECIMAL(5,2),               -- lingkar lengan (cm)
    sdidtk_score    TEXT,                            -- data perkembangan (SDIDTK)
    health_status_3m TEXT,                           -- status kesehatan 3 bulan terakhir
    comorbidities   TEXT,                            -- penyakit penyerta
    sleep_disorder  BOOLEAN DEFAULT FALSE,           -- gangguan tidur
    frequent_crying BOOLEAN DEFAULT FALSE,           -- sering menangis
    anxiety         BOOLEAN DEFAULT FALSE,           -- kecemasan
    mental_health_notes TEXT,                        -- catatan kesehatan mental anak
    risk_score      INT,                            -- skor risiko stunting (0-100)
    risk_level      ENUM('rendah','sedang','tinggi') DEFAULT 'rendah',
    notes           TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
    FOREIGN KEY (screened_by) REFERENCES users(id)
);

-- 5. Screening records (Skrining Ayah/Orang Tua)
CREATE TABLE screening_parent (
    id              CHAR(36) PRIMARY KEY,
    family_id       CHAR(36) NOT NULL,
    screened_by     CHAR(36) NOT NULL,
    screening_date  DATE NOT NULL,
    father_age      INT,
    education       VARCHAR(50),
    income          DECIMAL(12,2),
    latrine_type    VARCHAR(100),
    pmi_since       DATE,
    water_source    VARCHAR(100),
    has_pets        BOOLEAN DEFAULT FALSE,
    ventilation     VARCHAR(100),
    parent_disease_history TEXT,
    smoking_status  BOOLEAN DEFAULT FALSE,
    mental_health_score INT,
    mental_health_notes TEXT,
    risk_score      INT,
    risk_level      ENUM('rendah','sedang','tinggi') DEFAULT 'rendah',
    notes           TEXT,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE CASCADE,
    FOREIGN KEY (screened_by) REFERENCES users(id)
);

-- 6. Monthly monitoring records
CREATE TABLE monitoring (
    id              CHAR(36) PRIMARY KEY,
    child_id        CHAR(36) NOT NULL,
    monitored_by    CHAR(36) NOT NULL,              -- bidan/nakes
    monitoring_date DATE NOT NULL,
    weight          DECIMAL(5,2),                   -- berat badan (kg)
    height          DECIMAL(5,2),                   -- tinggi badan (cm)
    head_circumference DECIMAL(5,2),
    arm_circumference  DECIMAL(5,2),
    nutritional_status ENUM('gizi_buruk','gizi_kurang','gizi_baik','gizi_lebih'),
    development_status VARCHAR(50),                 -- status perkembangan
    immunization_status VARCHAR(100),
    feeding_notes   TEXT,                            -- catatan pemberian makan
    activity_notes  TEXT,
    recommendations TEXT,
    z_score_wfa     DECIMAL(4,2),                   -- Weight-for-Age z-score
    z_score_hfa     DECIMAL(4,2),                   -- Height-for-Age z-score
    z_score_wfh     DECIMAL(4,2),                   -- Weight-for-Height z-score
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
    FOREIGN KEY (monitored_by) REFERENCES users(id)
);

-- 7. Education content
CREATE TABLE education_content (
    id              CHAR(36) PRIMARY KEY,
    title           VARCHAR(200) NOT NULL,
    category        ENUM('tumbuh_kembang','gangguan_pertumbuhan','gangguan_perkembangan','alur_rujukan','gizi','pengasuhan') NOT NULL,
    content_type    ENUM('article','video') NOT NULL,
    body            TEXT,                           -- HTML/markdown content
    video_url       VARCHAR(255),
    thumbnail_url   VARCHAR(255),
    target_age_min  INT,                            -- target usia minimum (bulan)
    target_age_max  INT,                            -- target usia maximum (bulan)
    is_published    BOOLEAN DEFAULT FALSE,
    view_count      INT DEFAULT 0,
    created_by      CHAR(36),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 8. Telehealth / Chat consultations
CREATE TABLE consultations (
    id              CHAR(36) PRIMARY KEY,
    family_id       CHAR(36) NOT NULL,
    nakes_id        CHAR(36) NOT NULL,
    subject         VARCHAR(200),
    status          ENUM('active','closed') DEFAULT 'active',
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    closed_at       TIMESTAMP NULL,
    FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE CASCADE,
    FOREIGN KEY (nakes_id) REFERENCES users(id)
);

-- 9. Chat messages
CREATE TABLE chat_messages (
    id              CHAR(36) PRIMARY KEY,
    consultation_id CHAR(36) NOT NULL,
    sender_id       CHAR(36) NOT NULL,
    message         TEXT NOT NULL,
    is_read         BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (consultation_id) REFERENCES consultations(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id) REFERENCES users(id)
);

-- 10. Notifications
CREATE TABLE notifications (
    id              CHAR(36) PRIMARY KEY,
    user_id         CHAR(36) NOT NULL,
    title           VARCHAR(200) NOT NULL,
    message         TEXT,
    type            ENUM('screening','monitoring','chat','education','system') NOT NULL,
    is_read         BOOLEAN DEFAULT FALSE,
    link            VARCHAR(255),
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Indexes for performance
CREATE INDEX idx_families_user ON families(user_id);
CREATE INDEX idx_families_nakes ON families(assigned_nakes_id);
CREATE INDEX idx_children_family ON children(family_id);
CREATE INDEX idx_screening_child ON screening_child(child_id);
CREATE INDEX idx_screening_parent ON screening_parent(family_id);
CREATE INDEX idx_monitoring_child ON monitoring(child_id);
CREATE INDEX idx_monitoring_date ON monitoring(monitoring_date);
CREATE INDEX idx_consultations_family ON consultations(family_id);
CREATE INDEX idx_chat_consultation ON chat_messages(consultation_id);
CREATE INDEX idx_notifications_user ON notifications(user_id);
