vmall/apps/api/migrations/0026_threshold_promotions.sql

-- Threshold promotions (满减): shop-owned automatic reduction rules. A rule is
-- priced at checkout, never claimed, so it carries no stock and cancellation
-- restores nothing.
--
-- `orders` gains the rule reference; the realized amount continues to live in
-- the existing `discount_minor`. An order applies at most one of a coupon or
-- a threshold rule.

CREATE TABLE threshold_promotions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    shop_id UUID NOT NULL REFERENCES shops (id) ON DELETE CASCADE,
    name JSONB NOT NULL,
    currency CHAR(3) NOT NULL REFERENCES currencies (code),
    threshold_minor BIGINT NOT NULL CHECK (threshold_minor > 0),
    reduction_minor BIGINT NOT NULL CHECK (reduction_minor > 0),
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    archived BOOLEAN NOT NULL DEFAULT FALSE,
    display_order INT NOT NULL DEFAULT 0,
    starts_at TIMESTAMPTZ NOT NULL,
    ends_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT threshold_promotions_window CHECK (ends_at > starts_at),
    CONSTRAINT threshold_promotions_amounts CHECK (reduction_minor < threshold_minor)
);

CREATE INDEX threshold_promotions_shop_idx
    ON threshold_promotions (shop_id, enabled, archived, starts_at, ends_at);

ALTER TABLE orders
    ADD COLUMN threshold_promotion_id UUID REFERENCES threshold_promotions (id) ON DELETE SET NULL;

-- Exactly one price-adjustment source per order.
ALTER TABLE orders
    ADD CONSTRAINT orders_one_promotion_source
        CHECK (coupon_id IS NULL OR threshold_promotion_id IS NULL);