-- ===========================================================
-- AIR SDA - DATABASE SCHEMA
-- PostgreSQL 14+ with PostGIS 3.x
-- Multi-tenant, large-scale (>100 users, multi-wilayah)
-- ===========================================================

-- Extensions
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pgcrypto;          -- gen_random_uuid, password hashing
CREATE EXTENSION IF NOT EXISTS pg_trgm;           -- fuzzy text search
CREATE EXTENSION IF NOT EXISTS btree_gist;        -- composite indexes
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Schema separation for clarity
CREATE SCHEMA IF NOT EXISTS airsda;
CREATE SCHEMA IF NOT EXISTS airsda_gis;
CREATE SCHEMA IF NOT EXISTS airsda_audit;
SET search_path = airsda, airsda_gis, public;

-- ===========================================================
-- 1. ORGANISASI (Multi-tenant root)
-- ===========================================================
CREATE TABLE organisasi (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  kode            VARCHAR(20) UNIQUE NOT NULL,
  nama            VARCHAR(200) NOT NULL,
  tipe            VARCHAR(30) NOT NULL CHECK (tipe IN ('BBWS','PUPR_PROV','PUPR_KAB','DINAS_SDA','GP3A','OTHER')),
  alamat          TEXT,
  kontak_email    VARCHAR(150),
  kontak_telp     VARCHAR(30),
  logo_url        TEXT,
  is_active       BOOLEAN DEFAULT TRUE,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_organisasi_aktif ON organisasi(is_active);

-- ===========================================================
-- 2. USER & RBAC
-- ===========================================================
CREATE TABLE role (
  id              SERIAL PRIMARY KEY,
  kode            VARCHAR(30) UNIQUE NOT NULL,
  nama            VARCHAR(100) NOT NULL,
  deskripsi       TEXT,
  level           INT NOT NULL DEFAULT 0 -- 0=lapangan, 50=supervisor, 90=admin, 100=superadmin
);

CREATE TABLE permission (
  id              SERIAL PRIMARY KEY,
  kode            VARCHAR(60) UNIQUE NOT NULL, -- e.g. bangunan.create, inspeksi.approve
  nama            VARCHAR(120) NOT NULL,
  modul           VARCHAR(40) NOT NULL
);

CREATE TABLE role_permission (
  role_id         INT REFERENCES role(id) ON DELETE CASCADE,
  permission_id   INT REFERENCES permission(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE pengguna (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organisasi_id   UUID NOT NULL REFERENCES organisasi(id) ON DELETE RESTRICT,
  username        VARCHAR(60) UNIQUE NOT NULL,
  email           VARCHAR(150) UNIQUE NOT NULL,
  password_hash   VARCHAR(255) NOT NULL,
  nama_lengkap    VARCHAR(150) NOT NULL,
  nip             VARCHAR(30),
  no_telp         VARCHAR(30),
  jabatan         VARCHAR(120),
  role_id         INT NOT NULL REFERENCES role(id),
  foto_profil_url TEXT,
  wilayah_kerja   JSONB DEFAULT '[]'::JSONB,  -- array of wilayah_id yang menjadi scope kerja
  last_login_at   TIMESTAMPTZ,
  is_active       BOOLEAN DEFAULT TRUE,
  email_verified  BOOLEAN DEFAULT FALSE,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_pengguna_org ON pengguna(organisasi_id);
CREATE INDEX idx_pengguna_role ON pengguna(role_id);
CREATE INDEX idx_pengguna_aktif ON pengguna(is_active);
CREATE INDEX idx_pengguna_wilayah ON pengguna USING GIN (wilayah_kerja);

CREATE TABLE sesi_token (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pengguna_id     UUID NOT NULL REFERENCES pengguna(id) ON DELETE CASCADE,
  token_hash      VARCHAR(255) NOT NULL,
  device_info     TEXT,
  ip_address      INET,
  expired_at      TIMESTAMPTZ NOT NULL,
  revoked_at      TIMESTAMPTZ,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_sesi_token_pengguna ON sesi_token(pengguna_id);
CREATE INDEX idx_sesi_token_expired ON sesi_token(expired_at);

-- ===========================================================
-- 3. WILAYAH ADMINISTRATIF
-- ===========================================================
CREATE TABLE wilayah (
  id              BIGSERIAL PRIMARY KEY,
  kode_bps        VARCHAR(15) UNIQUE,            -- standar BPS (e.g. 32.04.01)
  nama            VARCHAR(150) NOT NULL,
  tingkat         VARCHAR(20) NOT NULL CHECK (tingkat IN ('PROVINSI','KABUPATEN','KECAMATAN','DESA')),
  parent_id       BIGINT REFERENCES wilayah(id),
  geom            GEOMETRY(MULTIPOLYGON, 4326),
  centroid        GEOMETRY(POINT, 4326),
  luas_km2        NUMERIC(12,2),
  created_at      TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_wilayah_parent ON wilayah(parent_id);
CREATE INDEX idx_wilayah_tingkat ON wilayah(tingkat);
CREATE INDEX idx_wilayah_geom ON wilayah USING GIST (geom);
CREATE INDEX idx_wilayah_nama_trgm ON wilayah USING GIN (nama gin_trgm_ops);

-- ===========================================================
-- 4. DAERAH IRIGASI (DI) MASTER
-- ===========================================================
CREATE TABLE daerah_irigasi (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organisasi_id   UUID NOT NULL REFERENCES organisasi(id) ON DELETE RESTRICT,
  kode_di         VARCHAR(30) NOT NULL,
  nama            VARCHAR(200) NOT NULL,
  kewenangan      VARCHAR(20) CHECK (kewenangan IN ('PUSAT','PROVINSI','KABUPATEN','DESA')),
  luas_potensial_ha NUMERIC(12,2),
  luas_fungsional_ha NUMERIC(12,2),
  luas_baku_ha    NUMERIC(12,2),
  sumber_air      VARCHAR(150),
  wilayah_id      BIGINT REFERENCES wilayah(id),
  geom_area       GEOMETRY(MULTIPOLYGON, 4326),
  deskripsi       TEXT,
  is_active       BOOLEAN DEFAULT TRUE,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE (organisasi_id, kode_di)
);
CREATE INDEX idx_di_org ON daerah_irigasi(organisasi_id);
CREATE INDEX idx_di_wilayah ON daerah_irigasi(wilayah_id);
CREATE INDEX idx_di_geom ON daerah_irigasi USING GIST (geom_area);
CREATE INDEX idx_di_nama_trgm ON daerah_irigasi USING GIN (nama gin_trgm_ops);

-- ===========================================================
-- 5. MASTER JENIS & KATEGORI BANGUNAN
-- ===========================================================
CREATE TABLE kategori_bangunan (
  id              SERIAL PRIMARY KEY,
  kode            VARCHAR(20) UNIQUE NOT NULL, -- BANGUNAN, SALURAN, PETAK
  nama            VARCHAR(100) NOT NULL,
  warna_default   VARCHAR(7) DEFAULT '#0891b2', -- hex color
  geom_type       VARCHAR(20) CHECK (geom_type IN ('POINT','LINESTRING','POLYGON','MIXED'))
);

CREATE TABLE jenis_bangunan (
  id              SERIAL PRIMARY KEY,
  kategori_id     INT NOT NULL REFERENCES kategori_bangunan(id),
  kode            VARCHAR(30) UNIQUE NOT NULL,
  nama            VARCHAR(100) NOT NULL,
  deskripsi       TEXT,
  ikon            VARCHAR(60),
  is_active       BOOLEAN DEFAULT TRUE
);
CREATE INDEX idx_jenis_kategori ON jenis_bangunan(kategori_id);

-- ===========================================================
-- 6. BANGUNAN IRIGASI (TABEL UTAMA, dengan GIS)
-- ===========================================================
CREATE TABLE bangunan (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organisasi_id   UUID NOT NULL REFERENCES organisasi(id) ON DELETE RESTRICT,
  kode            VARCHAR(50) NOT NULL,
  nama            VARCHAR(250) NOT NULL,
  jenis_id        INT NOT NULL REFERENCES jenis_bangunan(id),
  daerah_irigasi_id UUID REFERENCES daerah_irigasi(id) ON DELETE SET NULL,
  wilayah_desa_id BIGINT REFERENCES wilayah(id),
  alamat          TEXT,
  tahun_bangun    INT,
  sumber_dana     VARCHAR(100),
  -- Geometri (PostGIS) - mendukung point/line/polygon
  geom            GEOMETRY(GEOMETRY, 4326) NOT NULL,
  geom_type_actual VARCHAR(20) GENERATED ALWAYS AS (ST_GeometryType(geom)) STORED,
  centroid        GEOMETRY(POINT, 4326) GENERATED ALWAYS AS (ST_Centroid(geom)) STORED,
  -- Spesifikasi teknis
  panjang_m       NUMERIC(12,2),
  lebar_m         NUMERIC(12,2),
  tinggi_m        NUMERIC(12,2),
  material        VARCHAR(60),
  debit_rencana   NUMERIC(10,3),
  luas_layanan_ha NUMERIC(12,2),
  -- Kondisi terkini (denormalized untuk performa)
  kondisi_saat_ini VARCHAR(20) DEFAULT 'Baik' CHECK (kondisi_saat_ini IN ('Baik','Rusak Ringan','Rusak Sedang','Rusak Berat')),
  persentase_kerusakan NUMERIC(5,2) DEFAULT 0 CHECK (persentase_kerusakan BETWEEN 0 AND 100),
  tgl_inspeksi_terakhir DATE,
  inspektor_terakhir VARCHAR(150),
  -- Metadata
  catatan         TEXT,
  atribut_extra   JSONB DEFAULT '{}'::JSONB, -- field tambahan dinamis (mis. dari shp)
  sumber_input    VARCHAR(20) DEFAULT 'manual' CHECK (sumber_input IN ('manual','lapangan','shapefile','excel','import_api')),
  created_by      UUID REFERENCES pengguna(id),
  updated_by      UUID REFERENCES pengguna(id),
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW(),
  deleted_at      TIMESTAMPTZ, -- soft delete
  UNIQUE (organisasi_id, kode)
);
CREATE INDEX idx_bangunan_org ON bangunan(organisasi_id);
CREATE INDEX idx_bangunan_jenis ON bangunan(jenis_id);
CREATE INDEX idx_bangunan_di ON bangunan(daerah_irigasi_id);
CREATE INDEX idx_bangunan_wilayah ON bangunan(wilayah_desa_id);
CREATE INDEX idx_bangunan_kondisi ON bangunan(kondisi_saat_ini);
CREATE INDEX idx_bangunan_geom ON bangunan USING GIST (geom);
CREATE INDEX idx_bangunan_centroid ON bangunan USING GIST (centroid);
CREATE INDEX idx_bangunan_nama_trgm ON bangunan USING GIN (nama gin_trgm_ops);
CREATE INDEX idx_bangunan_atribut ON bangunan USING GIN (atribut_extra);
CREATE INDEX idx_bangunan_aktif ON bangunan(deleted_at) WHERE deleted_at IS NULL;
CREATE INDEX idx_bangunan_inspeksi ON bangunan(tgl_inspeksi_terakhir);

-- Tabel partisi opsional untuk skala besar (per tahun)
-- ALTER TABLE bangunan PARTITION BY RANGE (created_at);
-- CREATE TABLE bangunan_2026 PARTITION OF bangunan FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- ===========================================================
-- 7. INSPEKSI (RIWAYAT)
-- ===========================================================
CREATE TABLE inspeksi (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  bangunan_id     UUID NOT NULL REFERENCES bangunan(id) ON DELETE CASCADE,
  tanggal_inspeksi DATE NOT NULL,
  inspektor_id    UUID REFERENCES pengguna(id),
  inspektor_nama  VARCHAR(150),
  kondisi         VARCHAR(20) NOT NULL CHECK (kondisi IN ('Baik','Rusak Ringan','Rusak Sedang','Rusak Berat')),
  persentase_kerusakan NUMERIC(5,2) CHECK (persentase_kerusakan BETWEEN 0 AND 100),
  catatan         TEXT,
  rekomendasi     TEXT,
  estimasi_biaya  NUMERIC(15,2),
  prioritas       VARCHAR(20) CHECK (prioritas IN ('RENDAH','SEDANG','TINGGI','URGENT')),
  -- Geo-tag inspeksi (lokasi GPS petugas saat inspeksi)
  lokasi_inspeksi GEOMETRY(POINT, 4326),
  akurasi_gps_m   NUMERIC(8,2),
  -- Status approval
  status_approval VARCHAR(20) DEFAULT 'DRAFT' CHECK (status_approval IN ('DRAFT','SUBMITTED','APPROVED','REJECTED')),
  approved_by     UUID REFERENCES pengguna(id),
  approved_at     TIMESTAMPTZ,
  rejection_reason TEXT,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_inspeksi_bangunan ON inspeksi(bangunan_id);
CREATE INDEX idx_inspeksi_inspektor ON inspeksi(inspektor_id);
CREATE INDEX idx_inspeksi_tanggal ON inspeksi(tanggal_inspeksi DESC);
CREATE INDEX idx_inspeksi_status ON inspeksi(status_approval);
CREATE INDEX idx_inspeksi_kondisi ON inspeksi(kondisi);
CREATE INDEX idx_inspeksi_lokasi ON inspeksi USING GIST (lokasi_inspeksi);

-- Trigger: update kondisi terkini di bangunan saat inspeksi APPROVED
CREATE OR REPLACE FUNCTION update_kondisi_bangunan_dari_inspeksi()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status_approval = 'APPROVED' THEN
    UPDATE bangunan
       SET kondisi_saat_ini = NEW.kondisi,
           persentase_kerusakan = NEW.persentase_kerusakan,
           tgl_inspeksi_terakhir = NEW.tanggal_inspeksi,
           inspektor_terakhir = NEW.inspektor_nama,
           updated_at = NOW()
     WHERE id = NEW.bangunan_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_inspeksi_update_bangunan
AFTER INSERT OR UPDATE OF status_approval ON inspeksi
FOR EACH ROW EXECUTE FUNCTION update_kondisi_bangunan_dari_inspeksi();

-- ===========================================================
-- 8. FOTO & LAMPIRAN
-- ===========================================================
CREATE TABLE foto (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  bangunan_id     UUID REFERENCES bangunan(id) ON DELETE CASCADE,
  inspeksi_id     UUID REFERENCES inspeksi(id) ON DELETE CASCADE,
  file_path       TEXT NOT NULL,         -- path di S3 / local storage
  file_url        TEXT,
  thumbnail_url   TEXT,
  mime_type       VARCHAR(50),
  file_size_kb    INT,
  caption         TEXT,
  -- Metadata foto
  taken_at        TIMESTAMPTZ,
  gps_location    GEOMETRY(POINT, 4326),
  gps_accuracy_m  NUMERIC(8,2),
  device_info     TEXT,
  exif_data       JSONB,
  uploaded_by     UUID REFERENCES pengguna(id),
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  CHECK (bangunan_id IS NOT NULL OR inspeksi_id IS NOT NULL)
);
CREATE INDEX idx_foto_bangunan ON foto(bangunan_id);
CREATE INDEX idx_foto_inspeksi ON foto(inspeksi_id);
CREATE INDEX idx_foto_lokasi ON foto USING GIST (gps_location);
CREATE INDEX idx_foto_taken ON foto(taken_at DESC);

-- ===========================================================
-- 9. LAYER GIS (Shapefile, GeoTIFF, LTS)
-- ===========================================================
CREATE TABLE layer_gis (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organisasi_id   UUID NOT NULL REFERENCES organisasi(id) ON DELETE CASCADE,
  nama            VARCHAR(200) NOT NULL,
  tipe            VARCHAR(20) NOT NULL CHECK (tipe IN ('SHAPEFILE','GEOTIFF','XYZ_TILE','WMS','WMTS','LTS')),
  url             TEXT,                  -- untuk tile/wms
  file_path       TEXT,                  -- untuk file upload
  attribution     TEXT,
  bounds          GEOMETRY(POLYGON, 4326),
  zoom_min        INT,
  zoom_max        INT,
  opacity         NUMERIC(3,2) DEFAULT 1.00 CHECK (opacity BETWEEN 0 AND 1),
  warna           VARCHAR(7),
  visible         BOOLEAN DEFAULT TRUE,
  is_public       BOOLEAN DEFAULT FALSE,
  metadata        JSONB DEFAULT '{}'::JSONB,
  uploaded_by     UUID REFERENCES pengguna(id),
  created_at      TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_layer_org ON layer_gis(organisasi_id);
CREATE INDEX idx_layer_tipe ON layer_gis(tipe);
CREATE INDEX idx_layer_bounds ON layer_gis USING GIST (bounds);

-- ===========================================================
-- 10. PREFERENSI PENGGUNA (warna kategori, dll)
-- ===========================================================
CREATE TABLE preferensi_pengguna (
  pengguna_id     UUID PRIMARY KEY REFERENCES pengguna(id) ON DELETE CASCADE,
  warna_kategori  JSONB DEFAULT '{"bangunan":"#0891b2","saluran":"#f97316","petak":"#84cc16"}'::JSONB,
  mode_warna      VARCHAR(20) DEFAULT 'kondisi' CHECK (mode_warna IN ('kondisi','kategori','jenis')),
  basemap_default VARCHAR(20) DEFAULT 'osm',
  bahasa          VARCHAR(5) DEFAULT 'id',
  tema            VARCHAR(20) DEFAULT 'light',
  notifikasi_email BOOLEAN DEFAULT TRUE,
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ===========================================================
-- 11. NOTIFIKASI
-- ===========================================================
CREATE TABLE notifikasi (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  pengguna_id     UUID NOT NULL REFERENCES pengguna(id) ON DELETE CASCADE,
  tipe            VARCHAR(30) NOT NULL, -- INSPEKSI_DUE, KONDISI_KRITIS, APPROVAL_PENDING, etc
  judul           VARCHAR(200) NOT NULL,
  pesan           TEXT,
  link            TEXT,
  bangunan_id     UUID REFERENCES bangunan(id) ON DELETE SET NULL,
  is_read         BOOLEAN DEFAULT FALSE,
  read_at         TIMESTAMPTZ,
  created_at      TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_notif_pengguna ON notifikasi(pengguna_id);
CREATE INDEX idx_notif_unread ON notifikasi(pengguna_id, is_read) WHERE is_read = FALSE;

-- ===========================================================
-- 12. AUDIT LOG (di schema terpisah)
-- ===========================================================
CREATE TABLE airsda_audit.activity_log (
  id              BIGSERIAL PRIMARY KEY,
  pengguna_id     UUID REFERENCES airsda.pengguna(id),
  organisasi_id   UUID,
  aksi            VARCHAR(50) NOT NULL,  -- CREATE, UPDATE, DELETE, LOGIN, EXPORT, etc
  modul           VARCHAR(50) NOT NULL,  -- bangunan, inspeksi, dll
  entitas_id      UUID,
  data_lama       JSONB,
  data_baru       JSONB,
  ip_address      INET,
  user_agent      TEXT,
  created_at      TIMESTAMPTZ DEFAULT NOW()
) PARTITION BY RANGE (created_at);

CREATE TABLE airsda_audit.activity_log_2026 PARTITION OF airsda_audit.activity_log
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE airsda_audit.activity_log_2027 PARTITION OF airsda_audit.activity_log
  FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');

CREATE INDEX idx_audit_pengguna ON airsda_audit.activity_log(pengguna_id);
CREATE INDEX idx_audit_entitas ON airsda_audit.activity_log(entitas_id);
CREATE INDEX idx_audit_modul ON airsda_audit.activity_log(modul, aksi);
CREATE INDEX idx_audit_org ON airsda_audit.activity_log(organisasi_id);
CREATE INDEX idx_audit_tgl ON airsda_audit.activity_log(created_at DESC);

-- ===========================================================
-- 13. FUNGSI UTILITAS
-- ===========================================================
-- Update timestamp otomatis
CREATE OR REPLACE FUNCTION trigger_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply trigger ke semua tabel yang punya updated_at
CREATE TRIGGER set_updated_at BEFORE UPDATE ON organisasi FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
CREATE TRIGGER set_updated_at BEFORE UPDATE ON pengguna FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
CREATE TRIGGER set_updated_at BEFORE UPDATE ON daerah_irigasi FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
CREATE TRIGGER set_updated_at BEFORE UPDATE ON bangunan FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
CREATE TRIGGER set_updated_at BEFORE UPDATE ON inspeksi FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();
CREATE TRIGGER set_updated_at BEFORE UPDATE ON preferensi_pengguna FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

-- View untuk statistik bangunan per kondisi
CREATE OR REPLACE VIEW v_statistik_kondisi AS
SELECT
  organisasi_id,
  daerah_irigasi_id,
  kondisi_saat_ini,
  COUNT(*) AS jumlah,
  SUM(luas_layanan_ha) AS total_luas_ha
FROM bangunan
WHERE deleted_at IS NULL
GROUP BY organisasi_id, daerah_irigasi_id, kondisi_saat_ini;

-- View untuk bangunan dengan info lengkap
CREATE OR REPLACE VIEW v_bangunan_lengkap AS
SELECT
  b.id, b.kode, b.nama, b.tahun_bangun,
  jb.nama AS jenis_nama, kb.kode AS kategori_kode, kb.nama AS kategori_nama,
  di.nama AS daerah_irigasi_nama, di.kode_di,
  w.nama AS desa_nama,
  b.kondisi_saat_ini, b.persentase_kerusakan, b.tgl_inspeksi_terakhir,
  ST_X(b.centroid) AS lng, ST_Y(b.centroid) AS lat,
  b.geom, b.organisasi_id,
  b.created_at, b.updated_at
FROM bangunan b
JOIN jenis_bangunan jb ON jb.id = b.jenis_id
JOIN kategori_bangunan kb ON kb.id = jb.kategori_id
LEFT JOIN daerah_irigasi di ON di.id = b.daerah_irigasi_id
LEFT JOIN wilayah w ON w.id = b.wilayah_desa_id
WHERE b.deleted_at IS NULL;

-- Fungsi: cari bangunan dalam radius (km)
CREATE OR REPLACE FUNCTION cari_bangunan_radius(
  p_lat NUMERIC,
  p_lng NUMERIC,
  p_radius_km NUMERIC DEFAULT 1,
  p_organisasi_id UUID DEFAULT NULL
)
RETURNS TABLE (
  id UUID, kode VARCHAR, nama VARCHAR, jarak_m NUMERIC, kondisi VARCHAR
) AS $$
BEGIN
  RETURN QUERY
  SELECT b.id, b.kode, b.nama,
         ST_Distance(b.centroid::geography, ST_SetSRID(ST_MakePoint(p_lng, p_lat),4326)::geography)::NUMERIC AS jarak_m,
         b.kondisi_saat_ini
  FROM bangunan b
  WHERE b.deleted_at IS NULL
    AND (p_organisasi_id IS NULL OR b.organisasi_id = p_organisasi_id)
    AND ST_DWithin(
          b.centroid::geography,
          ST_SetSRID(ST_MakePoint(p_lng, p_lat),4326)::geography,
          p_radius_km * 1000
        )
  ORDER BY jarak_m ASC;
END;
$$ LANGUAGE plpgsql;

-- ===========================================================
-- ROW-LEVEL SECURITY (RLS) untuk multi-tenant
-- ===========================================================
ALTER TABLE bangunan ENABLE ROW LEVEL SECURITY;
ALTER TABLE daerah_irigasi ENABLE ROW LEVEL SECURITY;
ALTER TABLE inspeksi ENABLE ROW LEVEL SECURITY;
ALTER TABLE foto ENABLE ROW LEVEL SECURITY;
ALTER TABLE layer_gis ENABLE ROW LEVEL SECURITY;

-- Policy: hanya bisa lihat data organisasi sendiri
CREATE POLICY bangunan_tenant_isolation ON bangunan
  USING (organisasi_id = current_setting('app.current_organisasi_id', true)::UUID);
CREATE POLICY di_tenant_isolation ON daerah_irigasi
  USING (organisasi_id = current_setting('app.current_organisasi_id', true)::UUID);
CREATE POLICY layer_tenant_isolation ON layer_gis
  USING (organisasi_id = current_setting('app.current_organisasi_id', true)::UUID OR is_public = TRUE);

-- ===========================================================
-- END OF SCHEMA
-- ===========================================================