# Store Product Discounts

Timed product promotions are stored in CMS and applied centrally when a product is loaded for purchase. There is no discount-management UI.

## Scope and matching

`cms_shop_discounts` supports three targets:

| Target | `product_id` | `category` |
|---|---:|---|
| One product | Product ID | `NULL` |
| One category | `0` | Category string from `cms_shop.category` |
| All products | `0` | `NULL` |

`category` is a nullable string (`VARCHAR(20)`), not a numeric category ID. Category matching is case-insensitive. Validity is `starts_at <= NOW() AND ends_at > NOW()` in the CMS database timezone. Start is inclusive; end is exclusive.

`NULL` means there is no category target; the empty string remains a valid category string and matches products whose catalog category is empty.

When multiple active records match, the highest percentage wins across all target scopes. Equal percentages use the lowest `discount_id` for deterministic selection. Valid percentages are 0–100; zero has no price effect. Prices are rounded to two decimals per unit before multiplication by quantity.

## Pricing model boundary

Do not change the meaning of raw catalog methods:

```php
StoreModel::getProduct($productId);       // Raw cms_shop data
StoreModel::getAllProducts();             // Raw cms_shop data
```

These remain appropriate for manual sales, catalog editing, and pack fulfillment. Purchase-facing code uses:

```php
StoreModel::getProductForPurchase($productId);
StoreModel::getProductsForPurchase();
```

They add these fields while preserving `price` as the catalog/original unit price:

```php
[
  'price' => '100.00',
  'discounted_price' => 90.00,
  'product_discount_percentage' => 10.0,
  'product_discount_id' => 123,
]
```

Without a matching discount, `discounted_price` equals `price`, percentage is `0`, and ID is `NULL`. `StoreDiscountModel::applyToProducts()` loads matching active discounts once for the supplied product batch. Store UI, cart calculations, and order creation must use purchase-facing data; views and payment adapters must not query or choose promotions themselves. A promotion on a pack discounts the pack price only, not the reward contents.

## Cart, orders, and payments

Checkout reloads current products and discounts server-side. It snapshots:

- `cms_orders.total`: original subtotal.
- `cms_orders.total_after_product_discount`: discounted subtotal and payment-method calculation base.
- `cms_orders_products.price`: original unit price.
- `cms_orders_products.discounted_price`, `product_discount_percentage`, and `product_discount_id`: checkout-time promotion snapshot.
- `cms_orders.total_after_discount` and `discount_applied`: final amount and percentage after the payment-method adjustment.

Pending orders keep those snapshots if the promotion later ends or changes. Provider callbacks, retries, manual payment instructions, and purchase history use the order snapshot, never current catalog discounts. The product discount is applied first; the selected payment-method discount or fee is then applied to `total_after_product_discount`. For example, `$100` with a 10% product discount becomes `$90`; a subsequent 5% payment discount makes the final amount `$85.50`.

A zero product-discounted subtotal is paid and fulfilled internally as a `free` order, with no provider request or payment audit row. This supports 100% promotions and products that already cost zero. Completion and fulfillment use the normal idempotent order fulfillment reference.

## Schema and migration

V15 creates `cms_shop_discounts` and adds the order and line-item snapshot columns. Existing orders are backfilled at their original prices, so historical totals do not change. The full schema/migration snapshot is `installation/cms.sql`; apply `installation/migration/v15-store-product-discounts.sql` to an existing database after a backup.

For manual table maintenance, use `product_id = 0` for category/global rows and `category = NULL` where there is no category target. Set valid dates and a percentage between `0` and `100`. The table has no foreign keys because categories are existing string values and order snapshots must outlive promotion records.

## Verification

Focused coverage lives in:

- `tests/auto/integration-web/models/cms/storeDiscountModelTest.php`
- `tests/auto/integration-web/models/cms/orderModelTest.php`
- `tests/auto/integration-web/modules/PaymentModule/PaymentHelpersTest.php`

Test each scope, overlaps, tie-breaking, time boundaries, unit rounding, the 100% free-order path, and sequential product/payment discounts.
