-- Flash sales: a shop-owned timed session with SKU-level activity items that
-- hold their own reserved inventory and per-customer limit. An order line that
-- received activity pricing points at the item it consumed, so cancellation can
-- restore both the SKU stock and the reserved activity stock.

CREATE TABLE flash_sale_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    shop_id UUID NOT NULL REFERENCES shops (id) ON DELETE CASCADE,
    label JSONB NOT NULL,
    starts_at TIMESTAMPTZ NOT NULL,
    ends_at TIMESTAMPTZ NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT flash_sale_sessions_window CHECK (ends_at >= starts_at)
);

CREATE INDEX flash_sale_sessions_shop_idx ON flash_sale_sessions (shop_id);

CREATE TABLE flash_sale_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id UUID NOT NULL REFERENCES flash_sale_sessions (id) ON DELETE CASCADE,
    sku_id UUID NOT NULL REFERENCES skus (id) ON DELETE CASCADE,
    sale_price_minor BIGINT NOT NULL CHECK (sale_price_minor > 0),
    currency CHAR(3) NOT NULL REFERENCES currencies (code),
    -- Remaining activity inventory; decremented conditionally at checkout.
    reserved_stock INT NOT NULL CHECK (reserved_stock >= 0),
    sold_count INT NOT NULL DEFAULT 0 CHECK (sold_count >= 0),
    per_customer_limit INT NOT NULL CHECK (per_customer_limit > 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX flash_sale_items_session_sku_idx ON flash_sale_items (session_id, sku_id);
CREATE INDEX flash_sale_items_sku_idx ON flash_sale_items (sku_id);

-- Nullable: standard-priced lines keep NULL, and deleting an activity item
-- leaves the order line intact.
ALTER TABLE order_items
    ADD COLUMN flash_sale_item_id UUID REFERENCES flash_sale_items (id) ON DELETE SET NULL;

CREATE INDEX idx_order_items_flash ON order_items (flash_sale_item_id);