Perbaikan katalog_data_2d

Alihkan author yang tidak punya induk, lalu pasang foreign key.
Berkas: alat/hari-3/Praktik 6/sql/04_perbaikan-katalog-2d.sql
-- =====================================================================
-- Praktik 6 - perbaikan tabel katalog_data_2d
--
-- Tabel katalog_data_2d punya tiga hal yang perlu dibereskan, berbeda
-- dari tabel users:
--
--   1. Kolom author menunjuk ke baris users yang tidak ada, sehingga
--      foreign key tidak bisa dipasang. Inilah sebab modul menyuruh
--      menghapus baris hasil import sebelum membuat foreign key.
--   2. Tidak ada batasan nilai pada kolom akses.
--   3. Tidak ada indeks pada kolom author, padahal dipakai untuk join.
--
-- Jalankan:
--   psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f 04_perbaikan-katalog-2d.sql
--
-- Berkas ini TIDAK menghapus baris katalog. Baris yang author-nya tidak
-- ditemukan akan diberi pemilik yang ada, bukan dihapus.
--
-- Aman dijalankan berulang.
-- =====================================================================

BEGIN;

-- ---------------------------------------------------------------------
-- 1. Pastikan tabelnya ada
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS katalog_data_2d (
    data_2d_id  uuid         PRIMARY KEY,
    layer_name  varchar(255) NOT NULL,
    akses       varchar(20)  NOT NULL,
    is_editable boolean      NOT NULL,
    wms_url     text,
    wfs_url     text,
    author      uuid
);

-- ---------------------------------------------------------------------
-- 2. Bereskan author yang tidak punya baris induk
--
-- Caranya: cari baris katalog yang author-nya tidak ada di users, lalu
-- alihkan ke pemilik yang benar-benar ada. Aturannya, berurutan:
--
--   a. pakai user dengan email yang diberikan lewat -v pemilik_email
--   b. kalau tidak ada, pakai super_admin pertama
--   c. kalau tidak ada juga, pakai user mana pun yang aktif
--
-- Isi -v pemilik_email bila Anda ingin menentukan sendiri. Contoh:
--   psql "$DATABASE_URL" -v pemilik_email=admin@example.com \
--        -f 04_perbaikan-katalog-2d.sql
-- ---------------------------------------------------------------------

-- ---------------------------------------------------------------------
-- GANTI nilai di bawah bila ingin menentukan pemilik pengganti sendiri.
--
-- Berkas ini memakai SQL biasa, sehingga bisa dijalankan di DBeaver,
-- pgAdmin, atau psql tanpa perbedaan. Tidak ada meta-command.
--
-- Kosongkan ('') untuk memakai aturan bawaan: super_admin pertama, lalu
-- user aktif pertama, lalu user mana pun.
-- ---------------------------------------------------------------------

SELECT set_config('app.pemilik_email', '', false);

DO $$
DECLARE
    pemilik        uuid;
    diubah         integer;
    email_diminta  text := current_setting('app.pemilik_email', true);
BEGIN
    IF NOT EXISTS (SELECT 1 FROM users) THEN
        RAISE NOTICE 'Tabel users kosong. Jalankan 02_seed_super_admin.sql lebih dahulu, '
                     'atau import user sebelum memasang foreign key.';
        RETURN;
    END IF;

    -- Tentukan pemilik pengganti, berurutan sesuai prioritas.
    IF coalesce(email_diminta, '') <> '' THEN
        SELECT user_id INTO pemilik FROM users WHERE email = email_diminta;
        IF pemilik IS NULL THEN
            RAISE NOTICE 'Email % tidak ditemukan di tabel users. Memakai pemilik lain.',
                         email_diminta;
        END IF;
    END IF;

    IF pemilik IS NULL THEN
        SELECT user_id INTO pemilik FROM users WHERE role = 'super_admin'
        ORDER BY created_at LIMIT 1;
    END IF;

    IF pemilik IS NULL THEN
        SELECT user_id INTO pemilik FROM users WHERE is_active ORDER BY created_at LIMIT 1;
    END IF;

    IF pemilik IS NULL THEN
        SELECT user_id INTO pemilik FROM users ORDER BY created_at LIMIT 1;
    END IF;

    IF pemilik IS NULL THEN
        RAISE NOTICE 'Tidak ada baris users yang bisa dipakai sebagai pemilik.';
        RETURN;
    END IF;

    UPDATE katalog_data_2d k
    SET author = pemilik
    WHERE k.author IS NOT NULL
      AND NOT EXISTS (SELECT 1 FROM users u WHERE u.user_id = k.author);

    GET DIAGNOSTICS diubah = ROW_COUNT;
    IF diubah > 0 THEN
        RAISE NOTICE '% baris katalog dialihkan ke pemilik %', diubah, pemilik;
    END IF;
END $$;

-- ---------------------------------------------------------------------
-- 3. Seragamkan akses, lalu pasang batasan
--    Tanpa langkah ini, CHECK akan gagal bila ada nilai di luar daftar.
-- ---------------------------------------------------------------------
UPDATE katalog_data_2d SET akses = 'private'
WHERE akses IS NULL OR akses NOT IN ('public', 'private');

ALTER TABLE katalog_data_2d ALTER COLUMN akses SET NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint
                   WHERE conname = 'katalog_data_2d_akses_valid'
                     AND conrelid = 'katalog_data_2d'::regclass) THEN
        ALTER TABLE katalog_data_2d ADD CONSTRAINT katalog_data_2d_akses_valid
            CHECK (akses IN ('public', 'private'));
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint
                   WHERE conname = 'katalog_data_2d_layer_name_key'
                     AND conrelid = 'katalog_data_2d'::regclass) THEN
        -- layer_name harus unik: satu layer GeoServer hanya boleh
        -- terdaftar sekali, kalau tidak peta akan memuat layer yang sama
        -- dua kali.
        ALTER TABLE katalog_data_2d ADD CONSTRAINT katalog_data_2d_layer_name_key
            UNIQUE (layer_name);
    END IF;
EXCEPTION WHEN unique_violation THEN
    RAISE NOTICE 'layer_name masih ada yang kembar. Perbaiki lebih dahulu, '
                 'lalu jalankan berkas ini lagi. Lihat query di bagian verifikasi.';
END $$;

-- ---------------------------------------------------------------------
-- 4. Pasang foreign key ke users
--    ON DELETE RESTRICT dipilih supaya user yang masih punya data tidak
--    bisa dihapus. Tanpa itu, menghapus user akan membuat data katalog
--    kehilangan pemilik tanpa peringatan.
-- ---------------------------------------------------------------------
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM users)
       AND NOT EXISTS (SELECT 1 FROM pg_constraint
                       WHERE conname = 'katalog_data_2d_author_fkey'
                         AND conrelid = 'katalog_data_2d'::regclass) THEN
        ALTER TABLE katalog_data_2d ADD CONSTRAINT katalog_data_2d_author_fkey
            FOREIGN KEY (author) REFERENCES users (user_id) ON DELETE RESTRICT;
    END IF;
END $$;

-- ---------------------------------------------------------------------
-- 5. Indeks untuk join dan penyaringan
-- ---------------------------------------------------------------------
CREATE INDEX IF NOT EXISTS katalog_data_2d_author_idx ON katalog_data_2d (author);
CREATE INDEX IF NOT EXISTS katalog_data_2d_akses_idx  ON katalog_data_2d (akses);

COMMIT;

-- =====================================================================
-- VERIFIKASI
-- =====================================================================

SELECT 'Constraint pada katalog_data_2d' AS bagian;
SELECT con.conname AS nama_constraint,
       CASE con.contype WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE'
                        WHEN 'f' THEN 'FOREIGN KEY' WHEN 'c' THEN 'CHECK'
                        WHEN 'n' THEN 'NOT NULL'
                        ELSE con.contype::text END AS jenis
FROM pg_constraint con
JOIN pg_class c     ON c.oid = con.conrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = current_schema() AND c.relname = 'katalog_data_2d'
ORDER BY con.contype, con.conname;

SELECT 'Author tanpa baris induk (harus kosong)' AS bagian;
SELECT k.data_2d_id, k.layer_name, k.author
FROM katalog_data_2d k
LEFT JOIN users u ON u.user_id = k.author
WHERE k.author IS NOT NULL AND u.user_id IS NULL;

SELECT 'layer_name kembar (harus kosong)' AS bagian;
SELECT layer_name, count(*) AS jumlah FROM katalog_data_2d
GROUP BY layer_name HAVING count(*) > 1;

SELECT 'Isi katalog dengan nama pemiliknya' AS bagian;
SELECT k.layer_name, k.akses, k.is_editable, u.nama AS pemilik
FROM katalog_data_2d k
LEFT JOIN users u ON u.user_id = k.author
ORDER BY k.layer_name;