-- Migration: 20260506000002_fix_createdby_and_invoice_constraint
-- Tujuan:
--   1. KRITIS-3: Tambah CHECK constraint agar invoice wajib punya dokumen induk
--   2. KRITIS-4: Ubah kolom createdBy (String) menjadi createdById (FK ke users)
--               di tabel btd_dokumen, bdn_dokumen, gudang_dokumen

-- ===========================================================================
-- KRITIS-4: Rename createdBy → createdById dan tambah Foreign Key
-- ===========================================================================

-- BTD Dokumen
ALTER TABLE "btd_dokumen"
  RENAME COLUMN "createdBy" TO "createdById";

ALTER TABLE "btd_dokumen"
  ADD CONSTRAINT "btd_dokumen_createdById_fkey"
  FOREIGN KEY ("createdById")
  REFERENCES "users"("id")
  ON DELETE SET NULL
  ON UPDATE CASCADE
  DEFERRABLE INITIALLY DEFERRED;

-- BDN Dokumen
ALTER TABLE "bdn_dokumen"
  RENAME COLUMN "createdBy" TO "createdById";

ALTER TABLE "bdn_dokumen"
  ADD CONSTRAINT "bdn_dokumen_createdById_fkey"
  FOREIGN KEY ("createdById")
  REFERENCES "users"("id")
  ON DELETE SET NULL
  ON UPDATE CASCADE
  DEFERRABLE INITIALLY DEFERRED;

-- Gudang Dokumen
ALTER TABLE "gudang_dokumen"
  RENAME COLUMN "createdBy" TO "createdById";

ALTER TABLE "gudang_dokumen"
  ADD CONSTRAINT "gudang_dokumen_createdById_fkey"
  FOREIGN KEY ("createdById")
  REFERENCES "users"("id")
  ON DELETE SET NULL
  ON UPDATE CASCADE
  DEFERRABLE INITIALLY DEFERRED;

-- ===========================================================================
-- KRITIS-3: CHECK constraint — invoice wajib punya minimal 1 dokumen induk
-- ===========================================================================
ALTER TABLE "invoices"
  ADD CONSTRAINT "invoices_must_have_parent"
  CHECK (
    "btdDokumenId"    IS NOT NULL OR
    "bdnDokumenId"    IS NOT NULL OR
    "gudangDokumenId" IS NOT NULL
  );