-- wave-12: contract-renewal-amendment schema delta
-- Adds renewal/amendment linkage columns and term date columns to contracts.

ALTER TABLE "contracts" ADD COLUMN IF NOT EXISTS "renewed_from_id" UUID REFERENCES "contracts"("id");
ALTER TABLE "contracts" ADD COLUMN IF NOT EXISTS "amended_from_id" UUID REFERENCES "contracts"("id");
ALTER TABLE "contracts" ADD COLUMN IF NOT EXISTS "effective_date" DATE;
ALTER TABLE "contracts" ADD COLUMN IF NOT EXISTS "expiry_date" DATE;

-- Partial index for cron expiry-reminder query
-- (partial indexes must be in raw SQL migration — NOT drizzle index() builder)
CREATE INDEX IF NOT EXISTS "idx_contracts_expiry_signed"
  ON "contracts" ("tenant_id", "expiry_date")
  WHERE "status" = 'signed' AND "expiry_date" IS NOT NULL;
