-- 1. Master Anggota Legislatif
CREATE TABLE master_legislators (
    id SERIAL PRIMARY KEY,
    member_code VARCHAR(32) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    chamber VARCHAR(16) NOT NULL, -- DPR_RI, DPD_RI, MPR_RI
    faction VARCHAR(64),
    commission VARCHAR(64),
    electoral_district VARCHAR(128) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. Master Pejabat Eksekutif & Yudikatif
CREATE TABLE master_officials (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    position_title VARCHAR(255) NOT NULL,
    echelon_level VARCHAR(16),
    branch VARCHAR(16) NOT NULL, -- EKSEKUTIF, YUDIKATIF
    organization_id INTEGER REFERENCES organization_nodes(id),
    region_id INTEGER REFERENCES master_regions(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 3. Fact e-LHKPN Declarations
CREATE TABLE fact_lhkpn_declarations (
    id SERIAL PRIMARY KEY,
    announcement_code VARCHAR(64) UNIQUE NOT NULL,
    official_name VARCHAR(255) NOT NULL,
    institution_name VARCHAR(255) NOT NULL,
    reporting_year INTEGER NOT NULL,
    net_wealth NUMERIC(18, 2) NOT NULL,
    real_estate_wealth NUMERIC(18, 2) DEFAULT 0,
    vehicle_wealth NUMERIC(18, 2) DEFAULT 0,
    cash_wealth NUMERIC(18, 2) DEFAULT 0,
    debt_amount NUMERIC(18, 2) DEFAULT 0,
    verification_status VARCHAR(32) DEFAULT 'TERVERIFIKASI_LENGKAP',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_legislators_faction ON master_legislators(faction);
CREATE INDEX idx_legislators_chamber ON master_legislators(chamber);
CREATE INDEX idx_officials_org ON master_officials(organization_id);
CREATE INDEX idx_lhkpn_year_name ON fact_lhkpn_declarations(reporting_year, official_name);