-- ============================================================================
-- 07 Shop (tables reserved now; shop stays DISABLED at launch via site_settings)
-- Fulfilment type is per product: pre_order, print_on_demand, or in_stock, so
-- stocked inventory can be added later without any redesign.
-- All money is stored in kobo (integer) to avoid rounding errors.
-- ============================================================================

CREATE TABLE product_categories (
    id          smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        varchar(60) NOT NULL UNIQUE,
    slug        varchar(80) NOT NULL UNIQUE,
    sort_order  smallint    NOT NULL DEFAULT 0,
    is_active   boolean     NOT NULL DEFAULT true
);

-- A drop is a time-boxed pre-order run
CREATE TABLE shop_drops (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name            varchar(120) NOT NULL,
    slug            varchar(140) NOT NULL UNIQUE,
    description     text,
    opens_at        timestamptz  NOT NULL,
    closes_at       timestamptz  NOT NULL,
    min_orders      integer      NOT NULL DEFAULT 0,       -- auto-refund if not reached
    estimated_delivery_days integer,
    status          varchar(16)  NOT NULL DEFAULT 'draft'
        CHECK (status IN ('draft','scheduled','open','closed','in_production','shipped','cancelled')),
    created_at      timestamptz  NOT NULL DEFAULT now(),
    CONSTRAINT drop_dates CHECK (closes_at > opens_at)
);

CREATE TABLE products (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_id     smallint     REFERENCES product_categories(id),
    drop_id         bigint       REFERENCES shop_drops(id) ON DELETE SET NULL,
    name            varchar(150) NOT NULL,
    slug            varchar(170) NOT NULL UNIQUE,
    description     text,
    fulfilment_type varchar(16)  NOT NULL DEFAULT 'pre_order'
        CHECK (fulfilment_type IN ('pre_order','print_on_demand','in_stock')),
    base_price_kobo bigint       NOT NULL CHECK (base_price_kobo >= 0),
    is_collaboration boolean     NOT NULL DEFAULT false,    -- Champion/celebrity edition
    status          varchar(10)  NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','active','archived')),
    search_vector   tsvector GENERATED ALWAYS AS (
        to_tsvector('simple'::regconfig, coalesce(name,'') || ' ' || coalesce(description,''))
    ) STORED,
    deleted_at      timestamptz,
    purge_after     timestamptz,
    created_at      timestamptz  NOT NULL DEFAULT now(),
    updated_at      timestamptz  NOT NULL DEFAULT now()
);
CREATE INDEX products_active_idx ON products (status) WHERE deleted_at IS NULL;
CREATE INDEX products_search_idx ON products USING gin (search_vector);

CREATE TABLE product_images (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id  bigint       NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    path        varchar(255) NOT NULL,
    alt_text    varchar(200) NOT NULL CHECK (length(btrim(alt_text)) >= 3),
    sort_order  smallint     NOT NULL DEFAULT 0
);

CREATE TABLE product_variants (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id      bigint       NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    sku             varchar(60)  NOT NULL UNIQUE,
    size            varchar(10),
    colour          varchar(30),
    price_kobo      bigint       CHECK (price_kobo >= 0),    -- null = use product base price
    track_inventory boolean      NOT NULL DEFAULT false,     -- true once the product is in_stock
    stock_quantity  integer      NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    reserved_quantity integer    NOT NULL DEFAULT 0 CHECK (reserved_quantity >= 0),
    low_stock_threshold integer  NOT NULL DEFAULT 5,
    is_active       boolean      NOT NULL DEFAULT true,
    CONSTRAINT reserved_not_over_stock CHECK (track_inventory = false OR reserved_quantity <= stock_quantity)
);
CREATE INDEX product_variants_product_idx ON product_variants (product_id);

CREATE TABLE stock_movements (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    variant_id      bigint      NOT NULL REFERENCES product_variants(id),
    movement_type   varchar(12) NOT NULL
        CHECK (movement_type IN ('restock','sale','reserve','release','return','adjustment')),
    quantity        integer     NOT NULL,
    location        varchar(60) NOT NULL DEFAULT 'main',
    order_id        bigint,
    staff_id        bigint      REFERENCES staff_users(id),
    note            varchar(200),
    created_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX stock_movements_variant_idx ON stock_movements (variant_id, created_at DESC);

-- Carts (guest checkout supported: user_id is optional)
CREATE TABLE carts (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    uuid        uuid        NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    user_id     bigint      REFERENCES users(id) ON DELETE CASCADE,
    session_token char(32),
    expires_at  timestamptz NOT NULL DEFAULT now() + interval '30 days',
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE cart_items (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    cart_id     bigint      NOT NULL REFERENCES carts(id) ON DELETE CASCADE,
    variant_id  bigint      NOT NULL REFERENCES product_variants(id),
    quantity    integer     NOT NULL CHECK (quantity BETWEEN 1 AND 20),
    UNIQUE (cart_id, variant_id)
);

CREATE TABLE shipping_zones (
    id          smallint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        varchar(80) NOT NULL UNIQUE,
    state_ids   smallint[]  NOT NULL DEFAULT '{}',
    fee_kobo    bigint      NOT NULL CHECK (fee_kobo >= 0),
    free_over_kobo bigint,
    eta_days_min smallint,
    eta_days_max smallint,
    is_active   boolean     NOT NULL DEFAULT true
);

CREATE TABLE coupons (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code        citext      NOT NULL UNIQUE,
    discount_type varchar(8) NOT NULL CHECK (discount_type IN ('percent','fixed')),
    discount_value integer  NOT NULL CHECK (discount_value > 0),
    min_order_kobo bigint   NOT NULL DEFAULT 0,
    max_uses    integer,
    used_count  integer     NOT NULL DEFAULT 0,
    starts_at   timestamptz,
    ends_at     timestamptz,
    is_active   boolean     NOT NULL DEFAULT true
);

CREATE TABLE orders (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    uuid            uuid        NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    order_number    varchar(20) NOT NULL UNIQUE,
    user_id         bigint      REFERENCES users(id) ON DELETE SET NULL,
    customer_name   varchar(120) NOT NULL,
    customer_email  citext      NOT NULL,
    customer_phone  varchar(20) NOT NULL,
    shipping_address jsonb      NOT NULL,
    status          varchar(20) NOT NULL DEFAULT 'pending_payment'
        CHECK (status IN ('pending_payment','paid','in_production','packed','shipped','delivered','cancelled','refunded','failed')),
    subtotal_kobo   bigint      NOT NULL,
    discount_kobo   bigint      NOT NULL DEFAULT 0,
    shipping_kobo   bigint      NOT NULL DEFAULT 0,
    total_kobo      bigint      NOT NULL,
    coupon_id       bigint      REFERENCES coupons(id),
    drop_id         bigint      REFERENCES shop_drops(id),
    notes           varchar(500),
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT order_total_matches CHECK (total_kobo = subtotal_kobo - discount_kobo + shipping_kobo)
);
CREATE INDEX orders_user_idx ON orders (user_id, created_at DESC);
CREATE INDEX orders_status_idx ON orders (status, created_at DESC);

CREATE TABLE order_items (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id        bigint      NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    variant_id      bigint      NOT NULL REFERENCES product_variants(id),
    product_name    varchar(150) NOT NULL,                  -- snapshot at purchase time
    variant_label   varchar(80),
    unit_price_kobo bigint      NOT NULL,
    quantity        integer     NOT NULL CHECK (quantity > 0),
    fulfilment_type varchar(16) NOT NULL
);

-- Paystack payments: the verified webhook, not the browser redirect, marks an order paid
CREATE TABLE payments (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id        bigint      REFERENCES orders(id),
    purpose         varchar(14) NOT NULL DEFAULT 'order' CHECK (purpose IN ('order','ad_wallet')),
    advertiser_id   bigint      REFERENCES advertisers(id),
    gateway         varchar(10) NOT NULL DEFAULT 'paystack',
    reference       varchar(100) NOT NULL UNIQUE,
    amount_kobo     bigint      NOT NULL CHECK (amount_kobo > 0),
    currency        char(3)     NOT NULL DEFAULT 'NGN',
    status          varchar(10) NOT NULL DEFAULT 'initiated'
        CHECK (status IN ('initiated','success','failed','abandoned','refunded')),
    channel         varchar(20),
    paid_at         timestamptz,
    raw_response    jsonb,
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE paystack_webhook_events (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id        varchar(100) UNIQUE,                    -- idempotency: the same event is never processed twice
    event_type      varchar(60) NOT NULL,
    signature_valid boolean     NOT NULL,
    payload         jsonb       NOT NULL,
    processed_at    timestamptz,
    error           varchar(300),
    received_at     timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE refunds (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payment_id      bigint      NOT NULL REFERENCES payments(id),
    order_id        bigint      REFERENCES orders(id),
    amount_kobo     bigint      NOT NULL CHECK (amount_kobo > 0),
    reason          varchar(200),
    status          varchar(10) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','processed','failed')),
    paystack_refund_id varchar(60),
    issued_by_staff_id bigint   REFERENCES staff_users(id),
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE shipments (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id        bigint      NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    carrier         varchar(60),
    tracking_number varchar(80),
    status          varchar(12) NOT NULL DEFAULT 'preparing'
        CHECK (status IN ('preparing','shipped','in_transit','delivered','returned')),
    shipped_at      timestamptz,
    delivered_at    timestamptz
);

-- Champion / celebrity collaboration revenue splits (written agreement required first)
CREATE TABLE collaborator_splits (
    id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id      bigint      NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    champion_id     bigint      REFERENCES champions(id),
    collaborator_name varchar(150) NOT NULL,
    percent         numeric(5,2) NOT NULL CHECK (percent > 0 AND percent <= 100),
    agreement_reference varchar(100) NOT NULL,              -- signed licence on file
    created_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER products_updated BEFORE UPDATE ON products FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER orders_updated   BEFORE UPDATE ON orders   FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER carts_updated    BEFORE UPDATE ON carts    FOR EACH ROW EXECUTE FUNCTION set_updated_at();