Perbaikan constraint email

Bersihkan email kembar dan kosong, pasang constraint dengan penjagaan.
Berkas: alat/hari-3/Praktik 6/sql/03_perbaikan-email.sql
-- =====================================================================
-- Praktik 6 - perbaikan constraint email
--
-- Jalankan SETELAH 00_diagnosa.sql, dan hanya blok yang sesuai dengan
-- sebab yang ditemukan. Berkas ini tidak menghapus data kecuali Anda
-- menjalankan blok terakhir dengan sadar.
--
--   psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f 03_perbaikan-email.sql
--
-- Berkas ini aman dijalankan berulang.
-- =====================================================================

BEGIN;

-- ---------------------------------------------------------------------
-- Sebab A: tabel users belum ada
--
-- CREATE TABLE IF NOT EXISTS tidak melakukan apa pun bila tabelnya sudah
-- ada, termasuk tidak memasang constraint. Kalau tabel sudah ada tanpa
-- constraint, CREATE TABLE IF NOT EXISTS akan diam saja dan Anda mengira
-- sudah selesai padahal belum.
-- ---------------------------------------------------------------------
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()
);

-- ---------------------------------------------------------------------
-- Sebab B: kolom email masih boleh NULL
--
-- UNIQUE memperbolehkan NULL berapa pun. Selama kolomnya boleh NULL, email
-- kosong bisa dimasukkan berkali-kali dan constraint-nya tidak menjaga apa
-- pun. Isi dulu yang kosong, baru kunci.
-- ---------------------------------------------------------------------
UPDATE users SET email = 'tanpa-email-' || user_id || '@example.com'
WHERE email IS NULL OR btrim(email) = '';

ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- ---------------------------------------------------------------------
-- Sebab C: ada email kembar
--
-- UNIQUE tidak bisa dipasang selama masih ada kembar. Baris kembar di sini
-- ditandai, bukan dihapus, supaya Anda bisa memeriksanya lebih dahulu.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users_email_kembar AS
SELECT lower(email) AS email, count(*) AS jumlah, array_agg(user_id) AS user_id
FROM users
GROUP BY lower(email)
HAVING count(*) > 1;

-- Perbaikan yang aman untuk pelatihan: baris kembar diberi akhiran angka
-- pada emailnya, sehingga semuanya tetap ada dan constraint bisa dipasang.
-- Ganti bagian ini bila Anda ingin menghapus barisnya.
WITH beri_nomor AS (
    SELECT user_id,
           row_number() OVER (PARTITION BY lower(email) ORDER BY created_at, user_id) AS urutan,
           email
    FROM users
)
UPDATE users u
SET email = split_part(b.email, '@', 1) || '+' || b.urutan || '@' || split_part(b.email, '@', 2)
FROM beri_nomor b
WHERE u.user_id = b.user_id AND b.urutan > 1;

-- ---------------------------------------------------------------------
-- Sebab D: constraint sudah ada, atau namanya sudah dipakai
--
-- Ditulis dengan penjagaan supaya tidak gagal bila sudah terpasang.
-- Membersihkan email juga dilakukan lebih dahulu, karena UNIQUE bersifat
-- peka huruf besar-kecil: "Budi@example.com" dan "budi@example.com"
-- dianggap dua nilai berbeda.
-- ---------------------------------------------------------------------
UPDATE users SET email = lower(btrim(email));

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 $$;

-- Check constraint untuk role, juga dengan penjagaan.
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint
        WHERE conname = 'users_role_valid'
          AND conrelid = 'users'::regclass
    ) THEN
        -- Nilai role yang tidak dikenal dibereskan lebih dahulu, kalau tidak
        -- penambahan constraint-nya akan gagal.
        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 $$;

COMMIT;

-- =====================================================================
-- VERIFIKASI
--
-- Jalankan sebagai pernyataan terpisah, atau pilih seluruhnya lalu
-- Execute script. Semua di bawah ini hanya SELECT, tidak mengubah data.
-- =====================================================================

SELECT 'Constraint yang terpasang' AS bagian;
SELECT con.conname AS nama_constraint,
       CASE con.contype WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE'
                        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 = 'users'
ORDER BY con.contype;

SELECT 'Email kembar tersisa (harus kosong)' AS bagian;
SELECT lower(email) AS email, count(*) AS jumlah
FROM users GROUP BY lower(email) HAVING count(*) > 1;

SELECT 'Email kosong (harus 0)' AS bagian;
SELECT count(*) AS email_kosong FROM users WHERE email IS NULL OR btrim(email) = '';

SELECT 'Tabel sementara users_email_kembar' AS bagian;
SELECT to_regclass('users_email_kembar') AS ada;
-- Bila ada dan isinya sudah diperiksa, hapus dengan:
--   DROP TABLE users_email_kembar;