-- bad-debt-writeoff (wave-12) migration
-- Adds bad-debt columns to invoices, threshold to tenant_settings,
-- and creates the bad_debt_vat_reclaims table.
--
-- IMPORTANT: invoices_status_check is NOT altered — BAD_DEBT and WRITTEN_OFF
-- are already present in the upstream CHECK constraint from invoices-core.

--> statement-breakpoint
-- invoices: three new nullable bad-debt columns
ALTER TABLE "invoices" ADD COLUMN IF NOT EXISTS "bad_debt_at" TIMESTAMPTZ;
--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN IF NOT EXISTS "bad_debt_reason" TEXT;
--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN IF NOT EXISTS "bad_debt_note" TEXT;
--> statement-breakpoint
ALTER TABLE "invoices" DROP CONSTRAINT IF EXISTS "invoices_bad_debt_reason_check";
ALTER TABLE "invoices" ADD CONSTRAINT "invoices_bad_debt_reason_check"
  CHECK ("bad_debt_reason" IS NULL OR "bad_debt_reason" IN ('bankruptcy', 'collection_failed', 'other'));
--> statement-breakpoint

-- tenant_settings: bad-debt flagging threshold
ALTER TABLE "tenant_settings" ADD COLUMN IF NOT EXISTS "bad_debt_threshold_days" INTEGER NOT NULL DEFAULT 90;
--> statement-breakpoint

-- bad_debt_vat_reclaims table
CREATE TABLE IF NOT EXISTS "bad_debt_vat_reclaims" (
  "id"                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "tenant_id"                   UUID NOT NULL REFERENCES "tenants"("id") ON DELETE CASCADE,
  "invoice_id"                  UUID NOT NULL REFERENCES "invoices"("id") ON DELETE CASCADE,
  "vat_amount"                  NUMERIC(12, 2) NOT NULL,
  "status"                      TEXT NOT NULL DEFAULT 'pending',
  "registered_letter_sent_at"   DATE,
  "ita_submission_date"         DATE,
  "ita_reference"               TEXT,
  "resolved_at"                 TIMESTAMPTZ,
  "reversed_at"                 TIMESTAMPTZ,
  "notes"                       TEXT,
  "created_at"                  TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT "bad_debt_vat_reclaims_status_check"
    CHECK ("status" IN ('pending', 'submitted', 'approved', 'rejected', 'reversed', 'cancelled'))
);
--> statement-breakpoint

-- Indexes (btree — safe outside drizzle builder for this migration)
CREATE INDEX IF NOT EXISTS "idx_bad_debt_vat_tenant" ON "bad_debt_vat_reclaims" ("tenant_id", "created_at" DESC);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "idx_bad_debt_vat_invoice" ON "bad_debt_vat_reclaims" ("invoice_id");
