Skema siap import

Buat tabel dan isi penulis lebih dahulu, sehingga CSV bisa diimport tanpa dihapus.
Berkas: alat/hari-3/Praktik 6/sql/05_skema-siap-import.sql
-- =====================================================================
-- Praktik 6 - skema siap import
--
-- Menggantikan alur "import dulu, hapus, baru pasang foreign key".
--
-- Masalah pada alur modul: CSV katalog memuat kolom author berisi UUID
-- yang tidak ada di tabel users. Foreign key ke kolom yang tidak punya
-- baris induk selalu gagal, sehingga modul menyuruh menghapus seluruh
-- baris hasil import lebih dahulu. Akibatnya data yang baru diimport
-- hilang, dan langkahnya harus diulang setiap kali.
--
-- Berkas ini membalik urutannya:
--
--   1. Buat tabel users dan katalog
--   2. Isi tabel users, termasuk dua penulis yang dipakai CSV
--   3. Import CSV apa adanya, pemisahnya sudah koma
--   4. Pasang foreign key, dan langsung berhasil
--
-- Jalankan:
--   psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f 05_skema-siap-import.sql
--
-- Aman dijalankan berulang. Tidak ada perintah DELETE.
-- =====================================================================

BEGIN;

-- ---------------------------------------------------------------------
-- 1. Tabel users
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    user_id     uuid         PRIMARY KEY,
    nama        varchar(100) NOT NULL,
    email       varchar(150) NOT NULL,
    password    varchar(255) NOT NULL,
    role        varchar(20)  NOT NULL DEFAULT 'editor',
    is_active   boolean      NOT NULL DEFAULT false,
    created_at  timestamptz  NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------
-- 2. Tabel katalog_data_2d
--
-- Tiga kolom diberi nilai bawaan supaya import yang tidak memuat
-- kolomnya tetap berhasil:
--
--   akses       private, supaya layer baru tidak terbuka ke publik
--               tanpa sengaja
--   is_editable false, supaya layer baru tidak bisa diubah tanpa
--               sengaja
--   author      boleh kosong, supaya baris tanpa pemilik tetap bisa
--               dimuat dan tidak perlu dihapus
-- ---------------------------------------------------------------------
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 DEFAULT 'private',
    is_editable boolean      NOT NULL DEFAULT false,
    wms_url     text,
    wfs_url     text,
    author      uuid
);

-- ---------------------------------------------------------------------
-- 3. Tabel katalog_data_3d, dipakai Praktik 8
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS katalog_data_3d (
    data_3d_id uuid         PRIMARY KEY,
    author     uuid,
    nama       varchar(150) NOT NULL,
    akses      varchar(20)  NOT NULL DEFAULT 'private',
    url        text,
    latitude   double precision,
    longitude  double precision,
    heading    double precision,
    pitch      double precision,
    roll       double precision,
    scale      double precision,
    tipe_file  varchar(10)  NOT NULL DEFAULT 'glb'
);

-- ---------------------------------------------------------------------
-- 4. Isi tabel users
--
-- Dua baris pertama memakai UUID yang sama dengan nilai author pada CSV
-- katalog. Karena itu CSV bisa diimport APA ADANYA, tanpa disunting dan
-- tanpa dihapus.
--
-- Keduanya bukan akun login, melainkan pemilik data yang dipakai materi.
-- Emailnya diberi akhiran @example.com dan is_active false, sehingga
-- tidak bisa dipakai masuk. Kata sandinya diisi hash bcrypt yang tidak
-- cocok dengan kata sandi apa pun.
--
-- Ganti dengan akun Anda sendiri bila ingin data itu dimiliki akun
-- yang benar-benar dipakai.
-- ---------------------------------------------------------------------
INSERT INTO users (user_id, nama, email, password, role, is_active) VALUES
  ('ae5c7b2e-3537-4e94-ae1c-7596f1185f28', 'Pemilik Data 2D',
   'pemilik-2d@example.com',
   '$2b$12$invalidinvalidinvalidinvalidinvalidinvalidinvalidinvalidinvalidin',
   'editor', false),
  ('bc810d85-589d-4160-a9db-3c5516fa675a', 'Pemilik Data 3D',
   'pemilik-3d@example.com',
   '$2b$12$invalidinvalidinvalidinvalidinvalidinvalidinvalidinvalidinvalidin',
   'editor', false)
ON CONFLICT (user_id) DO NOTHING;

-- ---------------------------------------------------------------------
-- 5. Pasang constraint, sekarang pasti berhasil
--
-- Karena penulisnya sudah ada, foreign key tidak lagi menemukan baris
-- yatim. Urutan ini yang membuat import tidak perlu dihapus.
-- ---------------------------------------------------------------------

-- Email unik, dan huruf besar-kecil diseragamkan lebih dahulu supaya
-- 'Budi@example.com' tidak lolos sebagai email berbeda.
UPDATE users SET email = lower(btrim(email));
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint
                   WHERE conname = 'users_email_key' AND conrelid = 'users'::regclass) THEN
        ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint
                   WHERE conname = 'users_role_valid' AND conrelid = 'users'::regclass) THEN
        UPDATE users SET role = 'editor'
        WHERE role NOT IN ('viewer', 'editor', 'admin', 'super_admin');
        ALTER TABLE users ADD CONSTRAINT users_role_valid
            CHECK (role IN ('viewer', 'editor', 'admin', 'super_admin'));
    END IF;
END $$;

DO $$
BEGIN
    -- ON DELETE RESTRICT: user yang masih punya data tidak bisa dihapus.
    -- Itu yang diinginkan, supaya data katalog tidak kehilangan pemilik.
    IF 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;

    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;

    IF NOT EXISTS (SELECT 1 FROM pg_constraint
                   WHERE conname = 'katalog_data_3d_author_fkey'
                     AND conrelid = 'katalog_data_3d'::regclass) THEN
        ALTER TABLE katalog_data_3d ADD CONSTRAINT katalog_data_3d_author_fkey
            FOREIGN KEY (author) REFERENCES users (user_id) ON DELETE RESTRICT;
    END IF;

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

-- Indeks untuk join ke users dan penyaringan akses.
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);
CREATE INDEX IF NOT EXISTS katalog_data_3d_author_idx ON katalog_data_3d (author);

COMMIT;

-- =====================================================================
-- SETELAH INI, IMPORT CSV
--
-- Berkas CSV pada paket materi memakai pemisah pipa, padahal namanya
-- .csv. Ubah lebih dahulu dengan skrip perbaiki-csv.mjs, lalu import:
--
--   \copy katalog_data_2d FROM 'katalog_data_2d.bersih.csv' \
--         WITH (FORMAT csv, HEADER true)
--
--   \copy katalog_data_3d FROM 'katalog_data_3d.bersih.csv' \
--         WITH (FORMAT csv, HEADER true)
--
-- Bila memakai DBeaver: klik kanan tabel, Import Data, pilih berkas,
-- dan pastikan delimiter diisi koma.
--
-- Import akan berhasil pada percobaan pertama. Tidak ada langkah hapus.
-- =====================================================================

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

SELECT 'Constraint yang terpasang' AS bagian;
SELECT c.relname AS tabel, 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 IN ('users', 'katalog_data_2d', 'katalog_data_3d')
ORDER BY c.relname, con.contype;

SELECT 'Penulis yang dipakai CSV (harus 2 baris)' AS bagian;
SELECT user_id, nama, email FROM users
WHERE user_id IN ('ae5c7b2e-3537-4e94-ae1c-7596f1185f28',
                  'bc810d85-589d-4160-a9db-3c5516fa675a');

SELECT 'Jumlah baris' AS bagian;
SELECT 'katalog_data_2d' AS tabel, count(*) AS baris FROM katalog_data_2d
UNION ALL SELECT 'katalog_data_3d', count(*) FROM katalog_data_3d
UNION ALL SELECT 'users', count(*) FROM users;