-- v0.12.0: Marketplace Complète (F226-F265)
-- Adds seller balances, payout scheduling, commission rate tracking

-- Seller balances: tracks pending balance for each seller
CREATE TABLE IF NOT EXISTS seller_balances (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    seller_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    available_cents BIGINT NOT NULL DEFAULT 0,
    pending_cents BIGINT NOT NULL DEFAULT 0,
    total_earned_cents BIGINT NOT NULL DEFAULT 0,
    total_paid_out_cents BIGINT NOT NULL DEFAULT 0,
    currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(seller_id, currency)
);

CREATE INDEX IF NOT EXISTS idx_seller_balances_seller ON seller_balances(seller_id);

-- Seller payouts: tracks scheduled and completed payouts
CREATE TABLE IF NOT EXISTS seller_payouts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    seller_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    amount_cents BIGINT NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
    status VARCHAR(50) NOT NULL DEFAULT 'pending', -- pending, processing, completed, failed
    payout_method VARCHAR(50) NOT NULL DEFAULT 'stripe_connect', -- stripe_connect, paypal, bank_transfer
    external_payout_id VARCHAR(255),
    error_message TEXT,
    scheduled_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    processed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_seller_payouts_seller ON seller_payouts(seller_id);
CREATE INDEX IF NOT EXISTS idx_seller_payouts_status ON seller_payouts(status);

-- Add commission_rate column to seller_transfers for per-sale tracking
ALTER TABLE seller_transfers ADD COLUMN IF NOT EXISTS commission_rate NUMERIC(5,4) DEFAULT 0.10;

-- Add refund_deadline to orders for 14-day enforcement
ALTER TABLE orders ADD COLUMN IF NOT EXISTS refund_deadline TIMESTAMPTZ;

-- Update existing completed orders to have a refund deadline of 14 days from creation
UPDATE orders SET refund_deadline = created_at + INTERVAL '14 days' WHERE status IN ('completed', 'paid') AND refund_deadline IS NULL;