┌─────────────────────────────────────────────────────────────────────────┐
│                         PAYOUT FLOW                                     │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                         │
│   PURCHASE                    AGGREGATION                   PAYOUT      │
│   ┌─────────┐                ┌─────────┐                ┌─────────┐    │
│   │ Sale    │                │ Pending │                │ Weekly  │    │
│   │ Completes│───Queue───────▶│ Payouts │───Batch───────▶│ Payout  │    │
│   └─────────┘                │ Table   │                │ Job     │    │
│                              └─────────┘                └─────────┘    │
│                                   │                          │         │
│                                   │                          │         │
│                              ┌─────────┐                ┌─────────┐    │
│                              │ Creator │                │ Stripe  │    │
│                              │ Balance │◀───Update──────│ Transfer│    │
│                              └─────────┘                └─────────┘    │
│                                   │                          │         │
│                                   │                          │         │
│                              ┌─────────┐                ┌─────────┐    │
│                              │ Payout  │                │ Creator │    │
│                              │ Record  │───Notify──────▶│ Email   │    │
│                              └─────────┘                └─────────┘    │
│                                                                         │
└─────────────────────────────────────────────────────────────────────────┘

CREATE TABLE pending_payouts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  creator_id uuid REFERENCES creator_profiles(id),
  purchase_id uuid REFERENCES creator_license_purchases(id),
  
  amount numeric NOT NULL,
  
  -- Status
  status text DEFAULT 'pending',          -- 'pending', 'processing', 'paid', 'failed'
  
  -- Batch tracking
  payout_batch_id uuid,
  
  created_at timestamptz DEFAULT now()
);

CREATE INDEX idx_pending_payouts_creator ON pending_payouts(creator_id);
CREATE INDEX idx_pending_payouts_status ON pending_payouts(status) WHERE status = 'pending';

CREATE TABLE creator_payouts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  canonical_id text UNIQUE NOT NULL,
  creator_id uuid REFERENCES creator_profiles(id),
  
  -- Payout details
  amount numeric NOT NULL,
  currency text DEFAULT 'USD',
  
  -- Period covered
  period_start timestamptz NOT NULL,
  period_end timestamptz NOT NULL,
  
  -- Transaction count
  transaction_count integer,
  
  -- Payment method
  payout_method text NOT NULL,            -- 'stripe_transfer', 'paypal', 'crypto'
  
  -- Stripe details
  stripe_transfer_id text,
  stripe_payout_id text,
  
  -- Status
  status text DEFAULT 'pending',          -- 'pending', 'processing', 'paid', 'failed'
  
  -- Timing
  initiated_at timestamptz,
  paid_at timestamptz,
  
  -- Errors
  failure_reason text,
  retry_count integer DEFAULT 0,
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_payouts_creator ON creator_payouts(creator_id);
CREATE INDEX idx_payouts_status ON creator_payouts(status);
CREATE INDEX idx_payouts_period ON creator_payouts(period_start, period_end);

CREATE TABLE creator_payout_settings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  creator_id uuid REFERENCES creator_profiles(id) UNIQUE,
  
  -- Payout method
  payout_method text DEFAULT 'stripe',    -- 'stripe', 'paypal', 'crypto'
  
  -- Stripe Connect
  stripe_account_id text,
  stripe_account_status text,             -- 'pending', 'active', 'restricted'
  stripe_onboarding_complete boolean DEFAULT false,
  
  -- PayPal
  paypal_email text,
  
  -- Crypto
  crypto_wallet_address text,
  crypto_network text,                    -- 'ethereum', 'polygon', 'solana'
  
  -- Preferences
  payout_threshold numeric DEFAULT 50,    -- Minimum before payout
  payout_frequency text DEFAULT 'weekly', -- 'weekly', 'biweekly', 'monthly'
  
  -- Tax info
  tax_form_submitted boolean DEFAULT false,
  tax_form_type text,                     -- 'W9', 'W8BEN'
  
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_payout_settings_creator ON creator_payout_settings(creator_id);
CREATE INDEX idx_payout_settings_stripe ON creator_payout_settings(stripe_account_id);

import Stripe from 'stripe';

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);

// API: POST /api/creators/connect-stripe
export async function createStripeAccount(creatorId, userId) {
  const { data: creator } = await supabase
    .from('creator_profiles')
    .select('*, user:users(*)')
    .eq('id', creatorId)
    .single();
  
  // Create Express account
  const account = await stripe.accounts.create({
    type: 'express',
    email: creator.user.email,
    capabilities: {
      transfers: { requested: true }
    },
    business_type: 'individual',
    metadata: {
      creator_id: creatorId,
      user_id: userId
    }
  });
  
  // Store account ID
  await supabase
    .from('creator_payout_settings')
    .upsert({
      creator_id: creatorId,
      stripe_account_id: account.id,
      stripe_account_status: 'pending',
      payout_method: 'stripe'
    });
  
  // Create onboarding link
  const accountLink = await stripe.accountLinks.create({
    account: account.id,
    refresh_url: `${process.env.APP_URL}/creator/settings/payouts?refresh=true`,
    return_url: `${process.env.APP_URL}/creator/settings/payouts?success=true`,
    type: 'account_onboarding'
  });
  
  return { url: accountLink.url };
}

// API: POST /api/webhooks/stripe-connect
export async function handleConnectWebhook(event) {
  switch (event.type) {
    case 'account.updated':
      await handleAccountUpdate(event.data.object);
      break;
  }
}

async function handleAccountUpdate(account) {
  const status = account.details_submitted && account.payouts_enabled 
    ? 'active' 
    : 'pending';
  
  await supabase
    .from('creator_payout_settings')
    .update({
      stripe_account_status: status,
      stripe_onboarding_complete: account.details_submitted
    })
    .eq('stripe_account_id', account.id);
}

// Runs every Monday at 9am UTC
export async function processWeeklyPayouts() {
  const periodEnd = new Date();
  const periodStart = new Date(periodEnd.getTime() - 7 * 24 * 60 * 60 * 1000);
  
  // 1. Get all creators with pending payouts above threshold
  const { data: creators } = await supabase
    .from('pending_payouts')
    .select(`
      creator_id,
      creator:creator_profiles(
        id,
        display_name,
        payout_settings:creator_payout_settings(*)
      )
    `)
    .eq('status', 'pending')
    .group('creator_id');
  
  for (const creator of creators) {
    try {
      await processCreatorPayout(creator, periodStart, periodEnd);
    } catch (error) {
      console.error(`Payout failed for ${creator.creator_id}:`, error);
      // Continue with other creators
    }
  }
}

async function processCreatorPayout(creator, periodStart, periodEnd) {
  const settings = creator.creator.payout_settings;
  
  // 1. Sum pending payouts
  const { data: pending } = await supabase
    .from('pending_payouts')
    .select('id, amount')
    .eq('creator_id', creator.creator_id)
    .eq('status', 'pending');
  
  const totalAmount = pending.reduce((sum, p) => sum + p.amount, 0);
  
  // 2. Check threshold
  if (totalAmount < settings.payout_threshold) {
    console.log(`Skipping ${creator.creator_id}: below threshold`);
    return;
  }
  
  // 3. Check Stripe account status
  if (settings.stripe_account_status !== 'active') {
    console.log(`Skipping ${creator.creator_id}: Stripe not active`);
    return;
  }
  
  // 4. Create payout record
  const { data: payout } = await supabase
    .from('creator_payouts')
    .insert({
      canonical_id: `PAY-${Date.now()}`,
      creator_id: creator.creator_id,
      amount: totalAmount,
      period_start: periodStart,
      period_end: periodEnd,
      transaction_count: pending.length,
      payout_method: 'stripe_transfer',
      status: 'processing',
      initiated_at: new Date()
    })
    .select()
    .single();
  
  // 5. Create Stripe transfer
  const transfer = await stripe.transfers.create({
    amount: Math.round(totalAmount * 100), // cents
    currency: 'usd',
    destination: settings.stripe_account_id,
    metadata: {
      payout_id: payout.id,
      creator_id: creator.creator_id
    }
  });
  
  // 6. Update payout with transfer ID
  await supabase
    .from('creator_payouts')
    .update({
      stripe_transfer_id: transfer.id,
      status: 'paid',
      paid_at: new Date()
    })
    .eq('id', payout.id);
  
  // 7. Mark pending payouts as paid
  await supabase
    .from('pending_payouts')
    .update({
      status: 'paid',
      payout_batch_id: payout.id
    })
    .in('id', pending.map(p => p.id));
  
  // 8. Send notification
  await sendPayoutNotification(creator.creator_id, {
    amount: totalAmount,
    transaction_count: pending.length,
    payout_id: payout.id
  });
}

export async function retryFailedPayouts() {
  const { data: failed } = await supabase
    .from('creator_payouts')
    .select('*, settings:creator_payout_settings(*)')
    .eq('status', 'failed')
    .lt('retry_count', 3);
  
  for (const payout of failed) {
    try {
      // Retry transfer
      const transfer = await stripe.transfers.create({
        amount: Math.round(payout.amount * 100),
        currency: 'usd',
        destination: payout.settings.stripe_account_id
      });
      
      await supabase
        .from('creator_payouts')
        .update({
          stripe_transfer_id: transfer.id,
          status: 'paid',
          paid_at: new Date()
        })
        .eq('id', payout.id);
        
    } catch (error) {
      await supabase
        .from('creator_payouts')
        .update({
          retry_count: payout.retry_count + 1,
          failure_reason: error.message
        })
        .eq('id', payout.id);
    }
  }
}

-- Get creator's current balance
SELECT 
  COALESCE(SUM(amount), 0) as pending_balance
FROM pending_payouts
WHERE creator_id = :creator_id
  AND status = 'pending';

-- Get earnings by month
SELECT 
  date_trunc('month', p.paid_at) as month,
  COUNT(*) as sales,
  SUM(p.creator_payout) as earnings
FROM creator_license_purchases p
JOIN creator_licenses l ON p.license_id = l.id
WHERE l.creator_id = :creator_id
  AND p.payment_status = 'paid'
GROUP BY 1
ORDER BY 1 DESC;

-- Get payout history
SELECT 
  id,
  amount,
  transaction_count,
  status,
  paid_at,
  period_start,
  period_end
FROM creator_payouts
WHERE creator_id = :creator_id
ORDER BY created_at DESC
LIMIT 20;

// Check if tax form required
async function checkTaxFormRequired(creatorId) {
  const { data: settings } = await supabase
    .from('creator_payout_settings')
    .select('tax_form_submitted')
    .eq('creator_id', creatorId)
    .single();
  
  // Check if earned more than $600 this year
  const { data: earnings } = await supabase
    .from('creator_payouts')
    .select('amount')
    .eq('creator_id', creatorId)
    .eq('status', 'paid')
    .gte('paid_at', new Date().getFullYear() + '-01-01');
  
  const totalEarnings = earnings.reduce((sum, p) => sum + p.amount, 0);
  
  return {
    required: totalEarnings >= 600 && !settings.tax_form_submitted,
    totalEarnings
  };
}

// Annual job: Generate 1099s for creators
async function generate1099s(year) {
  const { data: creators } = await supabase
    .from('creator_payouts')
    .select(`
      creator_id,
      creator:creator_profiles(*, user:users(*)),
      amount
    `)
    .eq('status', 'paid')
    .gte('paid_at', `${year}-01-01`)
    .lt('paid_at', `${year + 1}-01-01`);
  
  // Group by creator
  const byCreator = groupBy(creators, 'creator_id');
  
  for (const [creatorId, payouts] of Object.entries(byCreator)) {
    const total = payouts.reduce((sum, p) => sum + p.amount, 0);
    
    if (total >= 600) {
      // Generate and file 1099
      await generate1099NEC({
        payee: payouts[0].creator.user,
        amount: total,
        year
      });
    }
  }
}

// Instant payout (if enabled)
async function requestInstantPayout(creatorId) {
  const { data: settings } = await supabase
    .from('creator_payout_settings')
    .select('stripe_account_id')
    .eq('creator_id', creatorId)
    .single();
  
  // Check balance in connected account
  const balance = await stripe.balance.retrieve({
    stripeAccount: settings.stripe_account_id
  });
  
  const available = balance.instant_available?.[0]?.amount || 0;
  
  if (available <= 0) {
    throw new Error('No funds available for instant payout');
  }
  
  // Create instant payout
  const payout = await stripe.payouts.create(
    {
      amount: available,
      currency: 'usd',
      method: 'instant'
    },
    { stripeAccount: settings.stripe_account_id }
  );
  
  return payout;
}

import paypal from '@paypal/checkout-server-sdk';

async function processPayPalPayout(creatorId, amount) {
  const { data: settings } = await supabase
    .from('creator_payout_settings')
    .select('paypal_email')
    .eq('creator_id', creatorId)
    .single();
  
  const request = new paypal.payouts.PayoutsPostRequest();
  request.requestBody({
    sender_batch_header: {
      sender_batch_id: `PAYOUT-${Date.now()}`,
      email_subject: 'You have a payout from Trending Society'
    },
    items: [{
      recipient_type: 'EMAIL',
      amount: {
        value: amount.toFixed(2),
        currency: 'USD'
      },
      receiver: settings.paypal_email
    }]
  });
  
  const response = await client.execute(request);
  return response.result;
}

async function sendPayoutNotification(creatorId, data) {
  const { data: creator } = await supabase
    .from('creator_profiles')
    .select('*, user:users(*)')
    .eq('id', creatorId)
    .single();
  
  // Email
  await sendEmail({
    to: creator.user.email,
    template: 'payout_complete',
    data: {
      name: creator.display_name,
      amount: formatCurrency(data.amount),
      transactions: data.transaction_count,
      payout_id: data.payout_id
    }
  });
  
  // In-app notification
  await supabase
    .from('notifications')
    .insert({
      user_id: creator.user.id,
      type: 'payout_complete',
      title: 'Payout sent!',
      body: `$${data.amount.toFixed(2)} has been sent to your account`,
      data: { payout_id: data.payout_id }
    });
}

-- Payout health dashboard
SELECT 
  COUNT(*) FILTER (WHERE status = 'paid') as successful_payouts,
  COUNT(*) FILTER (WHERE status = 'failed') as failed_payouts,
  SUM(amount) FILTER (WHERE status = 'paid') as total_paid,
  AVG(amount) FILTER (WHERE status = 'paid') as avg_payout,
  COUNT(DISTINCT creator_id) as creators_paid
FROM creator_payouts
WHERE created_at > now() - interval '30 days';

-- Pending balance by creator
SELECT 
  c.display_name,
  SUM(p.amount) as pending_balance,
  COUNT(*) as pending_transactions
FROM pending_payouts p
JOIN creator_profiles c ON p.creator_id = c.id
WHERE p.status = 'pending'
GROUP BY c.id
ORDER BY pending_balance DESC;