CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =========================================
-- REWARDS / MARKETPLACE OFFERS
-- =========================================

CREATE TABLE IF NOT EXISTS rewards (
    reward_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    vendor_id UUID NOT NULL REFERENCES vendors(vendor_id),
    location_id UUID REFERENCES vendor_locations(location_id),
    zone_id UUID NOT NULL REFERENCES zones(zone_id),

    title VARCHAR(200) NOT NULL,
    description TEXT,

    credit_cost INT NOT NULL CHECK (credit_cost > 0),

    stock_total INT DEFAULT 0 CHECK (stock_total >= 0),
    stock_remaining INT DEFAULT 0 CHECK (stock_remaining >= 0),

    reward_type VARCHAR(50) DEFAULT 'pickup'
        CHECK (reward_type IN ('pickup', 'delivery', 'digital')),

    status VARCHAR(30) DEFAULT 'active'
        CHECK (status IN ('draft', 'active', 'paused', 'sold_out', 'expired')),

    starts_at TIMESTAMPTZ DEFAULT NOW(),
    ends_at TIMESTAMPTZ,

    created_by UUID REFERENCES users(user_id),

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_rewards_vendor
ON rewards(vendor_id);

CREATE INDEX IF NOT EXISTS idx_rewards_zone_status
ON rewards(zone_id, status);

CREATE INDEX IF NOT EXISTS idx_rewards_location
ON rewards(location_id);

-- =========================================
-- REDEMPTIONS
-- =========================================

CREATE TABLE IF NOT EXISTS redemptions (
    redemption_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    player_id UUID NOT NULL REFERENCES player_profiles(player_id),
    reward_id UUID NOT NULL REFERENCES rewards(reward_id),

    vendor_id UUID NOT NULL REFERENCES vendors(vendor_id),
    location_id UUID REFERENCES vendor_locations(location_id),
    zone_id UUID NOT NULL REFERENCES zones(zone_id),
    season_id UUID NOT NULL REFERENCES seasons(season_id),

    credit_cost INT NOT NULL CHECK (credit_cost > 0),

    status VARCHAR(30) DEFAULT 'pending_pickup'
        CHECK (
            status IN (
                'pending_pickup',
                'fulfilled',
                'cancelled',
                'refunded',
                'expired'
            )
        ),

    pickup_code VARCHAR(20) UNIQUE NOT NULL,

    ledger_txn_id UUID REFERENCES credit_ledger(ledger_txn_id),

    fulfilled_by UUID REFERENCES users(user_id),
    fulfilled_at TIMESTAMPTZ,

    cancelled_reason TEXT,
    cancelled_at TIMESTAMPTZ,

    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_redemptions_player
ON redemptions(player_id);

CREATE INDEX IF NOT EXISTS idx_redemptions_reward
ON redemptions(reward_id);

CREATE INDEX IF NOT EXISTS idx_redemptions_vendor
ON redemptions(vendor_id);

CREATE INDEX IF NOT EXISTS idx_redemptions_status
ON redemptions(status);

CREATE INDEX IF NOT EXISTS idx_redemptions_pickup_code
ON redemptions(pickup_code);