-- Payments Database Initialization

CREATE SCHEMA IF NOT EXISTS payments;

-- Transactions table
CREATE TABLE IF NOT EXISTS payments.transactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    type VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    amount DECIMAL(12, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    provider VARCHAR(50) NOT NULL,
    provider_transaction_id VARCHAR(255),
    description TEXT,
    metadata JSONB DEFAULT '{}',
    error_message TEXT,
    completed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Subscriptions table
CREATE TABLE IF NOT EXISTS payments.subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    plan_id VARCHAR(100) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    provider VARCHAR(50) NOT NULL,
    provider_subscription_id VARCHAR(255),
    amount DECIMAL(12, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    interval VARCHAR(20) NOT NULL DEFAULT 'monthly',
    current_period_start TIMESTAMP WITH TIME ZONE,
    current_period_end TIMESTAMP WITH TIME ZONE,
    canceled_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Gift cards table
CREATE TABLE IF NOT EXISTS payments.gift_cards (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(50) NOT NULL UNIQUE,
    amount DECIMAL(12, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    balance DECIMAL(12, 2) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    purchaser_id UUID,
    recipient_id UUID,
    redeemed_at TIMESTAMP WITH TIME ZONE,
    expires_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Webhooks table
CREATE TABLE IF NOT EXISTS payments.webhooks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    provider VARCHAR(50) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_id VARCHAR(255) NOT NULL,
    payload JSONB NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    processed_at TIMESTAMP WITH TIME ZONE,
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(provider, event_id)
);

-- Payout records
CREATE TABLE IF NOT EXISTS payments.payouts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    creator_id UUID NOT NULL,
    amount DECIMAL(12, 2) NOT NULL,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    provider VARCHAR(50),
    provider_payout_id VARCHAR(255),
    payout_method JSONB NOT NULL,
    processed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_transactions_user ON payments.transactions(user_id);
CREATE INDEX IF NOT EXISTS idx_transactions_status ON payments.transactions(status);
CREATE INDEX IF NOT EXISTS idx_transactions_created ON payments.transactions(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_subscriptions_user ON payments.subscriptions(user_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON payments.subscriptions(status);
CREATE INDEX IF NOT EXISTS idx_gift_cards_code ON payments.gift_cards(code);
CREATE INDEX IF NOT EXISTS idx_webhooks_provider_event ON payments.webhooks(provider, event_id);
CREATE INDEX IF NOT EXISTS idx_payouts_creator ON payments.payouts(creator_id);

-- Permissions
GRANT ALL PRIVILEGES ON SCHEMA payments TO lilith;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA payments TO lilith;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA payments TO lilith;

DO $$ BEGIN RAISE NOTICE 'Payments database initialized'; END $$;