-- Migration: 20260516000001 — Konsolidasi Ekspor TPS

-- 1. Tambah enum values baru ke JenisLayanan
ALTER TYPE "JenisLayanan" ADD VALUE IF NOT EXISTS 'KONSOLIDASI_FCL';
ALTER TYPE "JenisLayanan" ADD VALUE IF NOT EXISTS 'KONSOLIDASI_LCL';

-- 2. Tabel nomor sequence untuk invoice ekspor (INV.EXP-XXX format)
CREATE TABLE IF NOT EXISTS "nomor_exp_sequence" (
  "tahun"    INTEGER NOT NULL,
  "terakhir" INTEGER NOT NULL DEFAULT 0,
  CONSTRAINT "nomor_exp_sequence_pkey" PRIMARY KEY ("tahun")
);

-- 3. Tabel dokumen konsolidasi
CREATE TABLE IF NOT EXISTS "konsolidasi_dokumen" (
  "id"               TEXT NOT NULL,
  "bookingRefNo"     TEXT NOT NULL,
  "consignee"        TEXT NOT NULL,
  "klienId"          TEXT,
  "vessel"           TEXT,
  "voyage"           TEXT,
  "tglStuffing"      TIMESTAMP(3),
  "tglBerangkat"     TIMESTAMP(3),
  "portLoading"      TEXT DEFAULT 'Belawan, Indonesia',
  "portDischarge"    TEXT,
  "containerNo"      TEXT,
  "ukuran"           TEXT,
  "jenisKonsolidasi" TEXT NOT NULL,
  "stuffingList"     JSONB NOT NULL DEFAULT '[]',
  "status"           "StatusDokumen" NOT NULL DEFAULT 'TERBUKA',
  "catatan"          TEXT,
  "createdAt"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "createdBy"        TEXT,
  CONSTRAINT "konsolidasi_dokumen_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX IF NOT EXISTS "konsolidasi_dokumen_bookingRefNo_consignee_key"
  ON "konsolidasi_dokumen"("bookingRefNo", "consignee");

CREATE INDEX IF NOT EXISTS "konsolidasi_dokumen_status_idx"    ON "konsolidasi_dokumen"("status");
CREATE INDEX IF NOT EXISTS "konsolidasi_dokumen_consignee_idx" ON "konsolidasi_dokumen"("consignee");
CREATE INDEX IF NOT EXISTS "konsolidasi_dokumen_createdAt_idx" ON "konsolidasi_dokumen"("createdAt");

-- 4. FK dari konsolidasi ke klien
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'konsolidasi_dokumen_klienId_fkey'
  ) THEN
    ALTER TABLE "konsolidasi_dokumen"
      ADD CONSTRAINT "konsolidasi_dokumen_klienId_fkey"
      FOREIGN KEY ("klienId") REFERENCES "klien"("id")
      ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
  END IF;
END $$;

-- 5. Kolom FK di invoices untuk konsolidasi
ALTER TABLE "invoices" ADD COLUMN IF NOT EXISTS "konsolidasiDokumenId" TEXT;

CREATE INDEX IF NOT EXISTS "invoices_konsolidasiDokumenId_idx"
  ON "invoices"("konsolidasiDokumenId");

DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'invoices_konsolidasiDokumenId_fkey'
  ) THEN
    ALTER TABLE "invoices"
      ADD CONSTRAINT "invoices_konsolidasiDokumenId_fkey"
      FOREIGN KEY ("konsolidasiDokumenId") REFERENCES "konsolidasi_dokumen"("id")
      ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED;
  END IF;
END $$;

-- 6. Update constraint invoices_must_have_parent agar include konsolidasi
ALTER TABLE "invoices" DROP CONSTRAINT IF EXISTS "invoices_must_have_parent";

ALTER TABLE "invoices" ADD CONSTRAINT "invoices_must_have_parent" CHECK (
  "deletedAt" IS NOT NULL OR (
    "btdDokumenId"         IS NOT NULL OR
    "bdnDokumenId"         IS NOT NULL OR
    "gudangDokumenId"      IS NOT NULL OR
    "konsolidasiDokumenId" IS NOT NULL
  )
);