goodgo-platform/prisma/migrations/20260420000000_add_price_check_constraints/migration.sql

-- Add CHECK constraints to enforce positive prices at the DB layer.
-- Scope (per TEC-2925): Listing price columns. Property has no direct
-- "price" column, only nullable maintenanceFeeVND (covered defensively).
-- Related price tables (PriceHistory) are also covered to keep the
-- invariant consistent across the price domain.

-- -----------------------------------------------------------------------------
-- 1) Backfill / clean offending data BEFORE applying constraints.
--    Strategy: NULL-out optional bad values; for the required Listing.priceVND
--    column we cannot null it, so any non-positive value is set to 1 (token
--    minimum) so the migration succeeds. In dev/seed these should not exist;
--    in prod the migration runner should review the audit log below before
--    applying.
-- -----------------------------------------------------------------------------

-- Audit (informational): how many rows would violate?
-- SELECT COUNT(*) FROM "Listing" WHERE "priceVND" <= 0;
-- SELECT COUNT(*) FROM "Listing" WHERE "rentPriceMonthly" IS NOT NULL AND "rentPriceMonthly" <= 0;
-- SELECT COUNT(*) FROM "Listing" WHERE "pricePerM2"       IS NOT NULL AND "pricePerM2"       <= 0;
-- SELECT COUNT(*) FROM "Listing" WHERE "aiPriceEstimate"  IS NOT NULL AND "aiPriceEstimate"  <= 0;
-- SELECT COUNT(*) FROM "PriceHistory" WHERE "oldPrice" <= 0 OR "newPrice" <= 0;

-- Backfill: required column → coerce to 1 VND (audit trail before applying).
UPDATE "Listing"      SET "priceVND"         = 1    WHERE "priceVND"         <= 0;

-- Backfill: optional columns → NULL them out (was clearly invalid data).
UPDATE "Listing"      SET "rentPriceMonthly" = NULL WHERE "rentPriceMonthly" IS NOT NULL AND "rentPriceMonthly" <= 0;
UPDATE "Listing"      SET "pricePerM2"       = NULL WHERE "pricePerM2"       IS NOT NULL AND "pricePerM2"       <= 0;
UPDATE "Listing"      SET "aiPriceEstimate"  = NULL WHERE "aiPriceEstimate"  IS NOT NULL AND "aiPriceEstimate"  <= 0;

-- PriceHistory rows with non-positive prices are corrupt; remove them.
DELETE FROM "PriceHistory" WHERE "oldPrice" <= 0 OR "newPrice" <= 0;

-- -----------------------------------------------------------------------------
-- 2) Apply CHECK constraints
-- -----------------------------------------------------------------------------

ALTER TABLE "Listing"
  ADD CONSTRAINT "listing_price_vnd_positive_chk"
  CHECK ("priceVND" > 0);

ALTER TABLE "Listing"
  ADD CONSTRAINT "listing_rent_price_monthly_positive_chk"
  CHECK ("rentPriceMonthly" IS NULL OR "rentPriceMonthly" > 0);

ALTER TABLE "Listing"
  ADD CONSTRAINT "listing_price_per_m2_positive_chk"
  CHECK ("pricePerM2" IS NULL OR "pricePerM2" > 0);

ALTER TABLE "Listing"
  ADD CONSTRAINT "listing_ai_price_estimate_positive_chk"
  CHECK ("aiPriceEstimate" IS NULL OR "aiPriceEstimate" > 0);

ALTER TABLE "PriceHistory"
  ADD CONSTRAINT "price_history_old_price_positive_chk"
  CHECK ("oldPrice" > 0);

ALTER TABLE "PriceHistory"
  ADD CONSTRAINT "price_history_new_price_positive_chk"
  CHECK ("newPrice" > 0);

-- Property defensive guard (only column with a monetary value):
ALTER TABLE "Property"
  ADD CONSTRAINT "property_maintenance_fee_vnd_nonnegative_chk"
  CHECK ("maintenanceFeeVND" IS NULL OR "maintenanceFeeVND" >= 0);