web-hosts/domains/coppertone.tech/backend/migrations/010_enhanced_payments.up.sql

-- Migration 010: Enhanced Payment System
-- Adds comprehensive crypto support, token whitelisting, donations, and robust payment tracking

-- ========================================
-- CRYPTOCURRENCY & BLOCKCHAIN SUPPORT
-- ========================================

-- Supported blockchain networks
CREATE TABLE IF NOT EXISTS payment_networks (
    id SERIAL PRIMARY KEY,
    network_code VARCHAR(20) NOT NULL UNIQUE,    -- 'ethereum', 'bitcoin', 'polygon', 'arbitrum', etc.
    network_name VARCHAR(100) NOT NULL,          -- Display name
    chain_id INTEGER,                            -- EVM chain ID (NULL for non-EVM)
    native_currency VARCHAR(10) NOT NULL,        -- ETH, BTC, MATIC, etc.
    native_decimals INTEGER DEFAULT 18,
    rpc_endpoint VARCHAR(500),                   -- Primary RPC URL
    rpc_endpoints_backup TEXT[],                 -- Backup RPC URLs
    explorer_url VARCHAR(255),                   -- Block explorer base URL
    explorer_tx_path VARCHAR(100),               -- Path for tx lookup (e.g., '/tx/')
    explorer_address_path VARCHAR(100),          -- Path for address lookup
    is_testnet BOOLEAN DEFAULT FALSE,
    is_enabled BOOLEAN DEFAULT TRUE,
    min_confirmations INTEGER DEFAULT 1,         -- Required confirmations
    avg_block_time INTEGER,                      -- Seconds
    gas_price_multiplier DECIMAL(3,2) DEFAULT 1.1,  -- For gas estimation
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Whitelisted tokens (ERC-20, BEP-20, etc.) - Admin managed
CREATE TABLE IF NOT EXISTS payment_tokens (
    id SERIAL PRIMARY KEY,
    network_id INTEGER NOT NULL REFERENCES payment_networks(id) ON DELETE CASCADE,
    contract_address VARCHAR(100) NOT NULL,
    token_symbol VARCHAR(20) NOT NULL,
    token_name VARCHAR(100) NOT NULL,
    decimals INTEGER NOT NULL DEFAULT 18,
    logo_url VARCHAR(500),
    logo_ipfs_cid VARCHAR(100),

    -- Verification
    is_verified BOOLEAN DEFAULT FALSE,           -- Admin verified
    verified_by INTEGER REFERENCES users(id),
    verified_at TIMESTAMP WITH TIME ZONE,
    verification_source VARCHAR(100),            -- 'coingecko', 'manual', 'etherscan', etc.

    -- Pricing
    coingecko_id VARCHAR(100),                   -- For price feeds
    price_usd DECIMAL(20, 8),                    -- Cached price
    price_updated_at TIMESTAMP WITH TIME ZONE,

    -- Status
    is_enabled BOOLEAN DEFAULT TRUE,
    is_stablecoin BOOLEAN DEFAULT FALSE,
    min_amount DECIMAL(30, 18),                  -- Minimum payment amount
    max_amount DECIMAL(30, 18),                  -- Maximum payment amount

    -- Metadata
    description TEXT,
    website_url VARCHAR(255),
    metadata JSONB DEFAULT '{}',

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(network_id, contract_address)
);

-- Payment wallets (company receiving addresses)
CREATE TABLE IF NOT EXISTS payment_wallets (
    id SERIAL PRIMARY KEY,
    wallet_name VARCHAR(100) NOT NULL,
    network_id INTEGER NOT NULL REFERENCES payment_networks(id) ON DELETE CASCADE,
    address VARCHAR(100) NOT NULL,
    address_type VARCHAR(20) DEFAULT 'hot',      -- 'hot', 'cold', 'multisig'

    -- For HD wallets
    derivation_path VARCHAR(100),
    address_index INTEGER,

    -- Balance tracking (cached)
    native_balance DECIMAL(30, 18) DEFAULT 0,
    balance_updated_at TIMESTAMP WITH TIME ZONE,

    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    is_primary BOOLEAN DEFAULT FALSE,            -- Primary wallet for this network

    -- Security
    requires_approval_above DECIMAL(15, 2),      -- USD threshold for manual approval
    daily_limit DECIMAL(15, 2),
    monthly_limit DECIMAL(15, 2),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(network_id, address)
);

-- ========================================
-- PAYMENT GATEWAY INTEGRATIONS
-- ========================================

-- Payment gateway configurations (Stripe, PayPal, etc.)
CREATE TABLE IF NOT EXISTS payment_gateways (
    id SERIAL PRIMARY KEY,
    gateway_code VARCHAR(50) NOT NULL UNIQUE,    -- 'stripe', 'paypal', 'coinbase', 'btcpay'
    gateway_name VARCHAR(100) NOT NULL,
    gateway_type VARCHAR(20) NOT NULL,           -- 'fiat', 'crypto', 'hybrid'

    -- API Configuration (encrypted in app, stored reference)
    config_encrypted BYTEA,                      -- Encrypted API keys/secrets
    webhook_secret_encrypted BYTEA,

    -- Supported features
    supports_refunds BOOLEAN DEFAULT TRUE,
    supports_partial BOOLEAN DEFAULT TRUE,
    supports_recurring BOOLEAN DEFAULT FALSE,
    supports_subscriptions BOOLEAN DEFAULT FALSE,

    -- Fees
    fixed_fee DECIMAL(10, 4),                    -- Fixed fee per transaction
    percentage_fee DECIMAL(5, 4),                -- Percentage fee (e.g., 0.029 = 2.9%)

    -- Limits
    min_amount DECIMAL(15, 2),
    max_amount DECIMAL(15, 2),

    -- Status
    is_enabled BOOLEAN DEFAULT TRUE,
    is_test_mode BOOLEAN DEFAULT FALSE,

    -- Webhook
    webhook_url VARCHAR(500),
    webhook_events TEXT[],

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Supported currencies per gateway
CREATE TABLE IF NOT EXISTS payment_gateway_currencies (
    id SERIAL PRIMARY KEY,
    gateway_id INTEGER NOT NULL REFERENCES payment_gateways(id) ON DELETE CASCADE,
    currency_code VARCHAR(10) NOT NULL,          -- 'USD', 'EUR', 'BTC', 'ETH'
    currency_type VARCHAR(10) NOT NULL,          -- 'fiat', 'crypto'
    is_enabled BOOLEAN DEFAULT TRUE,
    min_amount DECIMAL(15, 8),
    max_amount DECIMAL(15, 8),
    UNIQUE(gateway_id, currency_code)
);

-- ========================================
-- ENHANCED PAYMENT TRANSACTIONS
-- ========================================

-- Add new payment methods to existing enum (if not exists)
DO $$ BEGIN
    ALTER TYPE payment_method ADD VALUE IF NOT EXISTS 'PAYPAL';
    ALTER TYPE payment_method ADD VALUE IF NOT EXISTS 'CRYPTO_USDT';
    ALTER TYPE payment_method ADD VALUE IF NOT EXISTS 'CRYPTO_USDC';
    ALTER TYPE payment_method ADD VALUE IF NOT EXISTS 'CRYPTO_TOKEN';
    ALTER TYPE payment_method ADD VALUE IF NOT EXISTS 'APPLE_PAY';
    ALTER TYPE payment_method ADD VALUE IF NOT EXISTS 'GOOGLE_PAY';
    ALTER TYPE payment_method ADD VALUE IF NOT EXISTS 'ACH';
    ALTER TYPE payment_method ADD VALUE IF NOT EXISTS 'WIRE';
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

-- Crypto-specific payment details
CREATE TABLE IF NOT EXISTS payment_crypto_details (
    id SERIAL PRIMARY KEY,
    payment_id INTEGER NOT NULL REFERENCES payments(id) ON DELETE CASCADE,

    -- Network & Token
    network_id INTEGER REFERENCES payment_networks(id),
    token_id INTEGER REFERENCES payment_tokens(id),  -- NULL for native currency

    -- Addresses
    from_address VARCHAR(100),
    to_address VARCHAR(100) NOT NULL,

    -- Amounts
    amount_crypto DECIMAL(30, 18) NOT NULL,      -- Amount in crypto
    amount_usd_at_time DECIMAL(15, 2),           -- USD value at transaction time
    exchange_rate DECIMAL(20, 8),                -- Crypto to USD rate used

    -- Transaction details
    tx_hash VARCHAR(100),
    block_number BIGINT,
    block_timestamp TIMESTAMP WITH TIME ZONE,
    gas_used BIGINT,
    gas_price DECIMAL(30, 0),                    -- In wei/satoshi
    effective_gas_price DECIMAL(30, 0),

    -- Confirmation tracking
    confirmations INTEGER DEFAULT 0,
    required_confirmations INTEGER DEFAULT 1,
    is_confirmed BOOLEAN DEFAULT FALSE,
    confirmed_at TIMESTAMP WITH TIME ZONE,

    -- For token transfers
    log_index INTEGER,

    -- Error tracking
    error_message TEXT,
    retry_count INTEGER DEFAULT 0,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Payment audit log (comprehensive transaction history)
CREATE TABLE IF NOT EXISTS payment_audit_log (
    id SERIAL PRIMARY KEY,
    payment_id INTEGER REFERENCES payments(id) ON DELETE SET NULL,
    invoice_id INTEGER REFERENCES invoices(id) ON DELETE SET NULL,

    action VARCHAR(50) NOT NULL,                 -- 'created', 'status_change', 'refund', 'dispute', etc.
    old_status VARCHAR(50),
    new_status VARCHAR(50),

    amount DECIMAL(15, 8),
    currency VARCHAR(10),

    performed_by INTEGER REFERENCES users(id),   -- NULL for system actions
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),

    details JSONB DEFAULT '{}',

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ========================================
-- DONATIONS SYSTEM
-- ========================================

-- Donation campaigns
CREATE TABLE IF NOT EXISTS donation_campaigns (
    id SERIAL PRIMARY KEY,
    campaign_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),

    -- Campaign details
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    short_description VARCHAR(500),

    -- Media
    cover_image_url VARCHAR(500),
    cover_image_ipfs_cid VARCHAR(100),
    video_url VARCHAR(500),

    -- Goals
    goal_amount DECIMAL(15, 2),
    goal_currency VARCHAR(10) DEFAULT 'USD',
    raised_amount DECIMAL(15, 2) DEFAULT 0,      -- Cached total
    donor_count INTEGER DEFAULT 0,               -- Cached count

    -- Dates
    start_date TIMESTAMP WITH TIME ZONE,
    end_date TIMESTAMP WITH TIME ZONE,

    -- Settings
    is_public BOOLEAN DEFAULT TRUE,
    is_active BOOLEAN DEFAULT TRUE,
    allow_anonymous BOOLEAN DEFAULT TRUE,
    show_donors BOOLEAN DEFAULT TRUE,
    show_amounts BOOLEAN DEFAULT TRUE,
    min_donation DECIMAL(15, 2) DEFAULT 1,
    max_donation DECIMAL(15, 2),
    suggested_amounts DECIMAL(15, 2)[],          -- Array of suggested amounts

    -- Organization
    category VARCHAR(50),
    tags TEXT[],

    -- Beneficiary
    beneficiary_name VARCHAR(200),
    beneficiary_description TEXT,

    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Individual donations
CREATE TABLE IF NOT EXISTS donations (
    id SERIAL PRIMARY KEY,
    donation_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),

    -- Campaign (optional - NULL for general donations)
    campaign_id INTEGER REFERENCES donation_campaigns(id) ON DELETE SET NULL,

    -- Donor
    donor_id INTEGER REFERENCES users(id) ON DELETE SET NULL,  -- NULL for anonymous
    is_anonymous BOOLEAN DEFAULT FALSE,
    donor_name VARCHAR(200),                     -- Display name (optional)
    donor_email VARCHAR(255),                    -- For receipt
    donor_message TEXT,                          -- Public message

    -- Amount
    amount DECIMAL(15, 8) NOT NULL,
    currency VARCHAR(10) NOT NULL,
    amount_usd DECIMAL(15, 2),                   -- USD equivalent at time

    -- Payment reference
    payment_id INTEGER REFERENCES payments(id),
    payment_method VARCHAR(50),
    payment_status VARCHAR(20) DEFAULT 'pending',

    -- Crypto specific
    network_id INTEGER REFERENCES payment_networks(id),
    token_id INTEGER REFERENCES payment_tokens(id),
    tx_hash VARCHAR(100),

    -- Processing
    fee_amount DECIMAL(15, 8),
    net_amount DECIMAL(15, 8),                   -- Amount after fees

    -- Receipt
    receipt_sent BOOLEAN DEFAULT FALSE,
    receipt_sent_at TIMESTAMP WITH TIME ZONE,

    -- Recurring donations
    is_recurring BOOLEAN DEFAULT FALSE,
    recurring_frequency VARCHAR(20),             -- 'weekly', 'monthly', 'yearly'
    recurring_subscription_id VARCHAR(100),
    next_donation_date TIMESTAMP WITH TIME ZONE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Donation tiers (for perks/rewards)
CREATE TABLE IF NOT EXISTS donation_tiers (
    id SERIAL PRIMARY KEY,
    campaign_id INTEGER NOT NULL REFERENCES donation_campaigns(id) ON DELETE CASCADE,

    name VARCHAR(100) NOT NULL,
    description TEXT,
    min_amount DECIMAL(15, 2) NOT NULL,
    max_amount DECIMAL(15, 2),

    -- Perks
    perks TEXT[],
    limited_quantity INTEGER,                    -- NULL for unlimited
    claimed_count INTEGER DEFAULT 0,

    -- Display
    badge_icon VARCHAR(50),
    badge_color VARCHAR(7),
    sort_order INTEGER DEFAULT 0,

    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ========================================
-- EXCHANGE RATES & PRICING
-- ========================================

-- Cached exchange rates
CREATE TABLE IF NOT EXISTS exchange_rates (
    id SERIAL PRIMARY KEY,
    base_currency VARCHAR(10) NOT NULL,
    quote_currency VARCHAR(10) NOT NULL,
    rate DECIMAL(20, 8) NOT NULL,
    source VARCHAR(50) NOT NULL,                 -- 'coingecko', 'coinbase', 'binance', etc.
    fetched_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(base_currency, quote_currency, source)
);

-- Historical rates for audit
CREATE TABLE IF NOT EXISTS exchange_rates_history (
    id SERIAL PRIMARY KEY,
    base_currency VARCHAR(10) NOT NULL,
    quote_currency VARCHAR(10) NOT NULL,
    rate DECIMAL(20, 8) NOT NULL,
    source VARCHAR(50) NOT NULL,
    recorded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ========================================
-- SUBSCRIPTIONS & RECURRING PAYMENTS
-- ========================================

-- Subscription plans
CREATE TABLE IF NOT EXISTS subscription_plans (
    id SERIAL PRIMARY KEY,
    plan_code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,

    -- Pricing
    amount DECIMAL(15, 2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'USD',
    billing_period VARCHAR(20) NOT NULL,         -- 'daily', 'weekly', 'monthly', 'yearly'

    -- Trial
    trial_days INTEGER DEFAULT 0,

    -- Features
    features JSONB DEFAULT '{}',

    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Active subscriptions
CREATE TABLE IF NOT EXISTS subscriptions (
    id SERIAL PRIMARY KEY,
    subscription_id UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),

    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    plan_id INTEGER NOT NULL REFERENCES subscription_plans(id),

    status VARCHAR(20) NOT NULL DEFAULT 'active',  -- 'active', 'paused', 'cancelled', 'expired'

    -- Gateway reference
    gateway_id INTEGER REFERENCES payment_gateways(id),
    gateway_subscription_id VARCHAR(255),

    -- Dates
    started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    current_period_start TIMESTAMP WITH TIME ZONE,
    current_period_end TIMESTAMP WITH TIME ZONE,
    cancelled_at TIMESTAMP WITH TIME ZONE,

    -- Payment method
    payment_method_id INTEGER,                   -- Reference to stored payment method

    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ========================================
-- INDEXES
-- ========================================

CREATE INDEX IF NOT EXISTS idx_payment_networks_code ON payment_networks(network_code);
CREATE INDEX IF NOT EXISTS idx_payment_networks_enabled ON payment_networks(is_enabled);

CREATE INDEX IF NOT EXISTS idx_payment_tokens_network ON payment_tokens(network_id);
CREATE INDEX IF NOT EXISTS idx_payment_tokens_symbol ON payment_tokens(token_symbol);
CREATE INDEX IF NOT EXISTS idx_payment_tokens_verified ON payment_tokens(is_verified);
CREATE INDEX IF NOT EXISTS idx_payment_tokens_enabled ON payment_tokens(is_enabled);
CREATE INDEX IF NOT EXISTS idx_payment_tokens_contract ON payment_tokens(contract_address);

CREATE INDEX IF NOT EXISTS idx_payment_wallets_network ON payment_wallets(network_id);
CREATE INDEX IF NOT EXISTS idx_payment_wallets_active ON payment_wallets(is_active);
CREATE INDEX IF NOT EXISTS idx_payment_wallets_primary ON payment_wallets(network_id, is_primary) WHERE is_primary = TRUE;

CREATE INDEX IF NOT EXISTS idx_payment_gateways_code ON payment_gateways(gateway_code);
CREATE INDEX IF NOT EXISTS idx_payment_gateways_enabled ON payment_gateways(is_enabled);

CREATE INDEX IF NOT EXISTS idx_payment_crypto_payment ON payment_crypto_details(payment_id);
CREATE INDEX IF NOT EXISTS idx_payment_crypto_tx ON payment_crypto_details(tx_hash);
CREATE INDEX IF NOT EXISTS idx_payment_crypto_network ON payment_crypto_details(network_id);
CREATE INDEX IF NOT EXISTS idx_payment_crypto_confirmed ON payment_crypto_details(is_confirmed);

CREATE INDEX IF NOT EXISTS idx_payment_audit_payment ON payment_audit_log(payment_id);
CREATE INDEX IF NOT EXISTS idx_payment_audit_invoice ON payment_audit_log(invoice_id);
CREATE INDEX IF NOT EXISTS idx_payment_audit_action ON payment_audit_log(action);
CREATE INDEX IF NOT EXISTS idx_payment_audit_created ON payment_audit_log(created_at DESC);

CREATE INDEX IF NOT EXISTS idx_donations_campaign ON donations(campaign_id);
CREATE INDEX IF NOT EXISTS idx_donations_donor ON donations(donor_id);
CREATE INDEX IF NOT EXISTS idx_donations_status ON donations(payment_status);
CREATE INDEX IF NOT EXISTS idx_donations_created ON donations(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_donations_recurring ON donations(is_recurring) WHERE is_recurring = TRUE;

CREATE INDEX IF NOT EXISTS idx_campaigns_slug ON donation_campaigns(slug);
CREATE INDEX IF NOT EXISTS idx_campaigns_active ON donation_campaigns(is_active);
CREATE INDEX IF NOT EXISTS idx_campaigns_public ON donation_campaigns(is_public);

CREATE INDEX IF NOT EXISTS idx_exchange_rates_pair ON exchange_rates(base_currency, quote_currency);
CREATE INDEX IF NOT EXISTS idx_exchange_rates_history_pair ON exchange_rates_history(base_currency, quote_currency, recorded_at);

CREATE INDEX IF NOT EXISTS idx_subscriptions_user ON subscriptions(user_id);
CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON subscriptions(status);

-- ========================================
-- TRIGGERS
-- ========================================

CREATE TRIGGER payment_networks_updated
    BEFORE UPDATE ON payment_networks
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER payment_tokens_updated
    BEFORE UPDATE ON payment_tokens
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER payment_wallets_updated
    BEFORE UPDATE ON payment_wallets
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER payment_gateways_updated
    BEFORE UPDATE ON payment_gateways
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER payment_crypto_details_updated
    BEFORE UPDATE ON payment_crypto_details
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER donation_campaigns_updated
    BEFORE UPDATE ON donation_campaigns
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER donations_updated
    BEFORE UPDATE ON donations
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER subscriptions_updated
    BEFORE UPDATE ON subscriptions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ========================================
-- UPDATE DONATION CAMPAIGN STATS
-- ========================================

CREATE OR REPLACE FUNCTION update_campaign_stats()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.payment_status = 'completed' AND NEW.campaign_id IS NOT NULL THEN
        UPDATE donation_campaigns
        SET
            raised_amount = COALESCE(raised_amount, 0) + COALESCE(NEW.amount_usd, NEW.amount),
            donor_count = donor_count + 1
        WHERE id = NEW.campaign_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER donation_completed
    AFTER INSERT OR UPDATE ON donations
    FOR EACH ROW
    WHEN (NEW.payment_status = 'completed')
    EXECUTE FUNCTION update_campaign_stats();

-- ========================================
-- SEED DATA: Default Networks
-- ========================================

INSERT INTO payment_networks (network_code, network_name, chain_id, native_currency, native_decimals, explorer_url, explorer_tx_path, min_confirmations, avg_block_time, is_testnet)
VALUES
    ('ethereum', 'Ethereum Mainnet', 1, 'ETH', 18, 'https://etherscan.io', '/tx/', 12, 12, FALSE),
    ('polygon', 'Polygon', 137, 'MATIC', 18, 'https://polygonscan.com', '/tx/', 32, 2, FALSE),
    ('arbitrum', 'Arbitrum One', 42161, 'ETH', 18, 'https://arbiscan.io', '/tx/', 12, 1, FALSE),
    ('optimism', 'Optimism', 10, 'ETH', 18, 'https://optimistic.etherscan.io', '/tx/', 12, 2, FALSE),
    ('base', 'Base', 8453, 'ETH', 18, 'https://basescan.org', '/tx/', 12, 2, FALSE),
    ('bitcoin', 'Bitcoin', NULL, 'BTC', 8, 'https://blockstream.info', '/tx/', 3, 600, FALSE),
    ('sepolia', 'Ethereum Sepolia', 11155111, 'ETH', 18, 'https://sepolia.etherscan.io', '/tx/', 1, 12, TRUE),
    ('mumbai', 'Polygon Mumbai', 80001, 'MATIC', 18, 'https://mumbai.polygonscan.com', '/tx/', 1, 2, TRUE)
ON CONFLICT (network_code) DO NOTHING;

-- Seed default gateways
INSERT INTO payment_gateways (gateway_code, gateway_name, gateway_type, supports_refunds, supports_partial, supports_recurring, percentage_fee, fixed_fee)
VALUES
    ('stripe', 'Stripe', 'fiat', TRUE, TRUE, TRUE, 0.029, 0.30),
    ('paypal', 'PayPal', 'fiat', TRUE, TRUE, TRUE, 0.0349, 0.49),
    ('coinbase_commerce', 'Coinbase Commerce', 'crypto', FALSE, FALSE, FALSE, 0.01, 0),
    ('direct_crypto', 'Direct Crypto', 'crypto', FALSE, TRUE, FALSE, 0, 0)
ON CONFLICT (gateway_code) DO NOTHING;

-- Seed common verified tokens (Ethereum mainnet)
INSERT INTO payment_tokens (network_id, contract_address, token_symbol, token_name, decimals, is_verified, is_stablecoin, coingecko_id)
SELECT
    n.id,
    t.contract_address,
    t.token_symbol,
    t.token_name,
    t.decimals,
    TRUE,
    t.is_stablecoin,
    t.coingecko_id
FROM payment_networks n
CROSS JOIN (VALUES
    ('0xdAC17F958D2ee523a2206206994597C13D831ec7', 'USDT', 'Tether USD', 6, TRUE, 'tether'),
    ('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48', 'USDC', 'USD Coin', 6, TRUE, 'usd-coin'),
    ('0x6B175474E89094C44Da98b954EescdeCB5D6fc7', 'DAI', 'Dai Stablecoin', 18, TRUE, 'dai'),
    ('0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599', 'WBTC', 'Wrapped Bitcoin', 8, FALSE, 'wrapped-bitcoin'),
    ('0x514910771AF9Ca656af840dff83E8264EcF986CA', 'LINK', 'Chainlink', 18, FALSE, 'chainlink')
) AS t(contract_address, token_symbol, token_name, decimals, is_stablecoin, coingecko_id)
WHERE n.network_code = 'ethereum'
ON CONFLICT (network_id, contract_address) DO NOTHING;

-- ========================================
-- COMMENTS
-- ========================================

COMMENT ON TABLE payment_networks IS 'Supported blockchain networks for crypto payments';
COMMENT ON TABLE payment_tokens IS 'Whitelisted tokens (ERC-20, etc.) for crypto payments';
COMMENT ON TABLE payment_wallets IS 'Company wallets for receiving payments';
COMMENT ON TABLE payment_gateways IS 'Payment gateway configurations (Stripe, PayPal, etc.)';
COMMENT ON TABLE payment_crypto_details IS 'Detailed crypto transaction data linked to payments';
COMMENT ON TABLE payment_audit_log IS 'Comprehensive audit trail for all payment activities';
COMMENT ON TABLE donation_campaigns IS 'Donation campaigns with goals and tracking';
COMMENT ON TABLE donations IS 'Individual donation records';
COMMENT ON TABLE donation_tiers IS 'Donation tiers with perks for campaigns';
COMMENT ON TABLE exchange_rates IS 'Cached exchange rates for currency conversion';
COMMENT ON TABLE subscription_plans IS 'Recurring subscription plans';
COMMENT ON TABLE subscriptions IS 'Active user subscriptions';