-- 1. Master Rumah Sakit
CREATE TABLE master_hospitals (
    id SERIAL PRIMARY KEY,
    hospital_code VARCHAR(32) UNIQUE NOT NULL,
    hospital_name VARCHAR(255) NOT NULL,
    hospital_class VARCHAR(8),
    ownership_type VARCHAR(128),
    address TEXT,
    phone VARCHAR(64),
    region_id INTEGER REFERENCES master_regions(id),
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 2. Master Satuan Pengendalian Pangan Gizi (SPPG)
CREATE TABLE master_sppg (
    id SERIAL PRIMARY KEY,
    sppg_name VARCHAR(255) NOT NULL,
    subdistrict_name VARCHAR(128) NOT NULL,
    village_name VARCHAR(128) NOT NULL,
    address TEXT,
    region_id INTEGER REFERENCES master_regions(id),
    status VARCHAR(32) DEFAULT 'AKTIF',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 3. Master Koperasi Desa Mandiri Pangan (KDMP)
CREATE TABLE master_kdmp (
    id SERIAL PRIMARY KEY,
    cooperative_name VARCHAR(255) NOT NULL,
    village_name VARCHAR(128) NOT NULL,
    subdistrict_name VARCHAR(128),
    legal_status VARCHAR(64),
    address TEXT,
    region_id INTEGER REFERENCES master_regions(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX idx_hospitals_region ON master_hospitals(region_id);
CREATE INDEX idx_sppg_region ON master_sppg(region_id);
CREATE INDEX idx_kdmp_region ON master_kdmp(region_id);