-- BTCPay Server Billing Schema

CREATE TABLE IF NOT EXISTS btcpay_invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    btcpay_invoice_id TEXT UNIQUE,
    store_id TEXT NOT NULL,
    plan_type TEXT NOT NULL CHECK (plan_type IN ('free', 'wanderer', 'guardian', 'sanctuary')),
    amount DECIMAL(16, 8) NOT NULL,
    currency TEXT NOT NULL DEFAULT 'USD',
    status TEXT DEFAULT 'Pending' CHECK (status IN ('Pending', 'Processing', 'Settled', 'Invalid', 'Expired')),
    checkout_url TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    settled_at TIMESTAMPTZ,
    metadata JSONB DEFAULT '{}'
);

CREATE INDEX IF NOT EXISTS idx_btcpay_invoices_user ON btcpay_invoices(user_id);
CREATE INDEX IF NOT EXISTS idx_btcpay_invoices_btcpay_id ON btcpay_invoices(btcpay_invoice_id);

-- Add btcpay metadata to subscriptions for traceability
ALTER TABLE subscriptions
ADD COLUMN IF NOT EXISTS btcpay_invoice_id TEXT,
ADD COLUMN IF NOT EXISTS payment_method TEXT;