-- ============================================================================
-- Migration: 20260516000001 — Konsolidasi Ekspor TPS
-- Jalankan dengan: npx prisma migrate dev --name add_konsolidasi_ekspor
-- ATAU copy-paste SQL ini langsung ke psql / Prisma Studio
-- ============================================================================

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

-- 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");

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;

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