-- ─────────────────────────────────────────────────────────────────────────────
-- S.I. Tech Hub — production CHECK constraints (AUDIT S5, Phase 2)
--
-- Prisma 7 (MySQL/MariaDB provider) does not emit `@@check` from the schema
-- DSL, so these invariants live as raw SQL. Apply AFTER the schema has been
-- migrated and the fresh (seeded) DB has no legacy rows, i.e. right after:
--
--     npx prisma migrate reset --force      # (via the `!` prefix, user-run)
--     npx prisma migrate dev --name init
--     npm run seed
--
-- Then load this file:
--
--     mariadb -u admin -p si_tech_hub < sql/production-check-constraints.sql
--
-- Every constraint below mirrors a rule enforced in code (lib/actions/
-- order-invariants.ts + the ones covered by `npm test`). NOTE: on a database
-- that already holds historical rows, run once first:
--     SELECT id FROM orders WHERE NOT (subtotal >= 0 AND discount >= 0
--       AND shippingCost >= 0 AND tax >= 0 AND grandTotal >= 0
--       AND grandTotal = subtotal + shippingCost - discount + tax);
-- and clean the offenders before adding the constraints.
-- ─────────────────────────────────────────────────────────────────────────────

ALTER TABLE `Order`
  DROP CONSTRAINT IF EXISTS `ck_order_total_invariance`,
  DROP CONSTRAINT IF EXISTS `ck_order_amounts_non_negative`,
  ADD CONSTRAINT `ck_order_total_invariance`
    CHECK (`grandTotal` = `subtotal` + `shippingCost` - `discount` + `tax`),
  ADD CONSTRAINT `ck_order_amounts_non_negative`
    CHECK (`subtotal` >= 0 AND `shippingCost` >= 0 AND `discount` >= 0
           AND `tax` >= 0 AND `grandTotal` >= 0);

ALTER TABLE `OrderItem`
  DROP CONSTRAINT IF EXISTS `ck_orderitem_quantity_positive`,
  DROP CONSTRAINT IF EXISTS `ck_orderitem_shipped_within_ordered`,
  ADD CONSTRAINT `ck_orderitem_quantity_positive`
    CHECK (`quantity` > 0),
  ADD CONSTRAINT `ck_orderitem_shipped_within_ordered`
    CHECK (`shippedQuantity` >= 0 AND `shippedQuantity` <= `quantity`);

ALTER TABLE `Refund`
  DROP CONSTRAINT IF EXISTS `ck_refund_amount_positive`,
  ADD CONSTRAINT `ck_refund_amount_positive`
    CHECK (`amount` > 0);

ALTER TABLE `Product`
  DROP CONSTRAINT IF EXISTS `ck_product_stock_non_negative`,
  DROP CONSTRAINT IF EXISTS `ck_product_prices_non_negative`,
  ADD CONSTRAINT `ck_product_stock_non_negative`
    CHECK (`stockQuantity` >= 0 AND `lowStockThreshold` >= 0),
  ADD CONSTRAINT `ck_product_prices_non_negative`
    CHECK (`sellingPrice` >= 0 AND `purchasePrice` >= 0);