CREATE TABLE IF NOT EXISTS bazzar_pos.promos (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(180) NOT NULL,
    type VARCHAR(40) NOT NULL CHECK (type IN ('BUY_X_GET_Y', 'BUNDLE_PRICE')),
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    start_at TIMESTAMPTZ NOT NULL,
    end_at TIMESTAMPTZ NOT NULL,
    outlet_id BIGINT REFERENCES bazzar_pos.outlets(id),
    priority INTEGER NOT NULL DEFAULT 100,
    is_stackable BOOLEAN NOT NULL DEFAULT FALSE,
    max_usage_per_tx INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS bazzar_pos.promo_trigger_items (
    id BIGSERIAL PRIMARY KEY,
    promo_id BIGINT NOT NULL REFERENCES bazzar_pos.promos(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES bazzar_pos.products(id),
    min_qty INTEGER NOT NULL CHECK (min_qty > 0)
);

CREATE TABLE IF NOT EXISTS bazzar_pos.promo_reward_items (
    id BIGSERIAL PRIMARY KEY,
    promo_id BIGINT NOT NULL REFERENCES bazzar_pos.promos(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES bazzar_pos.products(id),
    qty INTEGER NOT NULL CHECK (qty > 0),
    discount_mode VARCHAR(30) NOT NULL DEFAULT 'free' CHECK (discount_mode IN ('free'))
);

CREATE TABLE IF NOT EXISTS bazzar_pos.sales_order_promos (
    id BIGSERIAL PRIMARY KEY,
    sales_order_id BIGINT NOT NULL REFERENCES bazzar_pos.sales_orders(id) ON DELETE CASCADE,
    promo_id BIGINT NOT NULL REFERENCES bazzar_pos.promos(id),
    promo_name VARCHAR(180) NOT NULL,
    promo_type VARCHAR(40) NOT NULL,
    discount_amount NUMERIC(14,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_promos_active_window
    ON bazzar_pos.promos(is_active, start_at, end_at);

CREATE INDEX IF NOT EXISTS idx_sales_order_promos_order
    ON bazzar_pos.sales_order_promos(sales_order_id);

