-- Points mall: a platform-owned catalog of redeemable products plus a
-- redemption order lifecycle kept separate from cash orders and payments.
-- Redemptions snapshot the product and the shipping address; they never share
-- merchant SKU inventory.

CREATE TYPE integral_order_status AS ENUM ('pending_fulfillment', 'fulfilled', 'cancelled');

CREATE SEQUENCE integral_order_no_seq;

CREATE TABLE integral_products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name JSONB NOT NULL,
    subtitle JSONB,
    content JSONB,
    image TEXT,
    points_price BIGINT NOT NULL CHECK (points_price > 0),
    stock INT NOT NULL CHECK (stock >= 0),
    published BOOLEAN NOT NULL DEFAULT FALSE,
    recommend BOOLEAN NOT NULL DEFAULT FALSE,
    position INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX integral_products_public_idx ON integral_products (published, position);

CREATE TABLE integral_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_no TEXT NOT NULL UNIQUE,
    user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    status integral_order_status NOT NULL DEFAULT 'pending_fulfillment',
    total_points BIGINT NOT NULL CHECK (total_points > 0),
    shipping_address JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX integral_orders_user_idx ON integral_orders (user_id, created_at DESC);
CREATE INDEX integral_orders_status_idx ON integral_orders (status);

CREATE TABLE integral_order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES integral_orders (id) ON DELETE CASCADE,
    -- Nullable so a deleted catalog item keeps the redemption snapshot.
    product_id UUID REFERENCES integral_products (id) ON DELETE SET NULL,
    name JSONB NOT NULL,
    image TEXT,
    points_price BIGINT NOT NULL CHECK (points_price > 0),
    qty INT NOT NULL CHECK (qty > 0)
);

CREATE INDEX integral_order_items_order_idx ON integral_order_items (order_id);