<?php
declare(strict_types=1);
require_once __DIR__ . '/functions.php';
require_once __DIR__ . '/notifications.php';

// Schema setup - only run if $pdo is available
function ensure_referral_tables(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS referral_codes (
      user_id INTEGER PRIMARY KEY,
      code TEXT UNIQUE NOT NULL,
      created_at TEXT NOT NULL
    )");
    
    $pdo->exec("CREATE TABLE IF NOT EXISTS referral_visits (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      referrer_id INTEGER NOT NULL,
      ip_address TEXT,
      user_agent TEXT,
      created_at TEXT NOT NULL
    )");
    
    $pdo->exec("CREATE TABLE IF NOT EXISTS referral_registrations (
      referrer_id INTEGER NOT NULL,
      new_user_id INTEGER PRIMARY KEY,
      created_at TEXT NOT NULL
    )");
    
    $pdo->exec("CREATE TABLE IF NOT EXISTS commissions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      referrer_id INTEGER NOT NULL,
      source_user_id INTEGER NOT NULL,
      application_id INTEGER,
      amount REAL NOT NULL,
      status TEXT NOT NULL DEFAULT 'pending',
      description TEXT,
      created_at TEXT NOT NULL,
      paid_at TEXT
    )");
    
    // Alterations (best effort)
    try { $pdo->exec("ALTER TABLE users ADD COLUMN referrer_id INTEGER"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE services ADD COLUMN commission_percent REAL DEFAULT 10.0"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE commissions ADD COLUMN raw_amount REAL"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE commissions ADD COLUMN percentage_applied REAL"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE commissions ADD COLUMN capped_amount REAL"); } catch (\Throwable $e) {}
    try { $pdo->exec("ALTER TABLE commissions ADD COLUMN currency TEXT DEFAULT '₦'"); } catch (\Throwable $e) {}
}

function ensure_user_ref_code(PDO $pdo, int $userId): string {
  // Ensure tables exist first
  ensure_referral_tables($pdo);
  
  $q = $pdo->prepare("SELECT code FROM referral_codes WHERE user_id=?");
  $q->execute([$userId]);
  $code = $q->fetchColumn();
  if ($code) return $code;

  // Generate unique code based on user ID or random
  // Format: REF-USERID-RANDOM
  $code = 'REF' . $userId . strtoupper(bin2hex(random_bytes(2)));
  
  try {
    $pdo->prepare("INSERT INTO referral_codes (user_id, code, created_at) VALUES (?,?,?)")
        ->execute([$userId, $code, date('c')]);
    return $code;
  } catch (\Throwable $e) {
    // Fallback if collision (unlikely with user_id prefix)
    return ensure_user_ref_code($pdo, $userId);
  }
}

function get_referrer_id_from_code(PDO $pdo, string $code): ?int {
  // Ensure tables exist first
  ensure_referral_tables($pdo);
  
  $q = $pdo->prepare("SELECT user_id FROM referral_codes WHERE code=?");
  $q->execute([$code]);
  $uid = $q->fetchColumn();
  return $uid ? (int)$uid : null;
}

function capture_referral_visit(PDO $pdo, string $code): void {
  // Ensure tables exist first
  ensure_referral_tables($pdo);
  
  $refId = get_referrer_id_from_code($pdo, $code);
  if (!$refId) return;

  // Prevent self-referral if logged in (basic check)
  if (isset($_SESSION['user']) && $_SESSION['user']['id'] === $refId) return;

  // Log visit
  $pdo->prepare("INSERT INTO referral_visits (referrer_id, ip_address, user_agent, created_at) VALUES (?,?,?,?)")
      ->execute([$refId, $_SERVER['REMOTE_ADDR'] ?? '', $_SERVER['HTTP_USER_AGENT'] ?? '', date('c')]);
      
  // Store in cookie for 30 days
  setcookie('ref_code', $code, time() + (86400 * 30), "/", "", false, true);
}

function get_referral_stats(PDO $pdo, int $userId): array {
  // Ensure tables exist first
  ensure_referral_tables($pdo);
  
  $visits = $pdo->prepare("SELECT COUNT(*) FROM referral_visits WHERE referrer_id=?");
  $visits->execute([$userId]);
  $v = (int)$visits->fetchColumn() ?? 0;

  $regs = $pdo->prepare("SELECT COUNT(*) FROM referral_registrations WHERE referrer_id=?");
  $regs->execute([$userId]);
  $r = (int)$regs->fetchColumn() ?? 0;

  $earnings = $pdo->prepare("SELECT SUM(amount) FROM commissions WHERE referrer_id=? AND status IN ('pending','paid','processed')");
  $earnings->execute([$userId]);
  $e = (float)$earnings->fetchColumn() ?? 0.0;

  $paid = $pdo->prepare("SELECT SUM(amount) FROM commissions WHERE referrer_id=? AND status IN ('paid','processed')");
  $paid->execute([$userId]);
  $p = (float)$paid->fetchColumn() ?? 0.0;

  return [
    'visits' => $v,
    'registrations' => $r,
    'total_earnings' => $e,
    'paid_earnings' => $p,
    'pending_earnings' => $e - $p
  ];
}

function get_leaderboard(PDO $pdo, int $limit=5): array {
  // Top referrers by earnings
  $q = $pdo->query("
    SELECT u.full_name, SUM(c.amount) as total_earned 
    FROM commissions c 
    JOIN users u ON c.referrer_id = u.id 
    WHERE c.status IN ('pending','paid','processed') 
    GROUP BY c.referrer_id 
    ORDER BY total_earned DESC 
    LIMIT $limit
  ");
  return $q->fetchAll();
}

function award_commission(PDO $pdo, int $userId, int $appId, float $amount): void {
  // Check if user was referred
  $q = $pdo->prepare("SELECT referrer_id FROM referral_registrations WHERE new_user_id=?");
  $q->execute([$userId]);
  $refId = $q->fetchColumn();
  
  if (!$refId) return; // No referrer
  
  // Prevent self-referral (double check)
  if ($refId == $userId) return;

  // Check if commission already exists for this app
  $chk = $pdo->prepare("SELECT id FROM commissions WHERE application_id=?");
  $chk->execute([$appId]);
  if ($chk->fetch()) return; // Already awarded

  // Get service details including percentage
  $stmt = $pdo->prepare("SELECT s.id, s.service_name, s.commission_percent FROM applications a JOIN services s ON a.service_id=s.id WHERE a.id=?");
  $stmt->execute([$appId]);
  $svc = $stmt->fetch();
  if (!$svc) return;

  $svcName = $svc['service_name'];
  $percent = (float)($svc['commission_percent'] ?? 10.0);
  
  // Calculate
  $rawAmount = $amount * ($percent / 100);
  $cappedAmount = min($rawAmount, 10000); // Hard cap at 10k per transaction
  
  $pdo->prepare("INSERT INTO commissions (referrer_id, source_user_id, application_id, amount, raw_amount, percentage_applied, capped_amount, currency, status, description, created_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)")
      ->execute([$refId, $userId, $appId, $cappedAmount, $rawAmount, $percent, $cappedAmount, '₦', 'pending', "Commission for $svcName", date('c')]);

  // Notify referrer
  $refUser = $pdo->prepare("SELECT email, full_name FROM users WHERE id=?");
  $refUser->execute([$refId]);
  $ru = $refUser->fetch();
  
  if ($ru) {
    notify_inapp($pdo, (int)$refId, 'commission_earned', 'You earned a commission!', "You earned ₦" . number_format($cappedAmount) . " from a referral ($svcName).");
    // Email could be queued here too
    queue_email($pdo, $ru['email'], 'You earned a commission!', 'default', ['message' => "Great news! You've earned ₦" . number_format($cappedAmount) . " from a successful referral purchase ($svcName). It is currently pending weekly payout processing."]);
  }
}

function process_weekly_payouts(PDO $pdo): int {
    // End of last week (Sunday 23:59:59) relative to current time
    // If today is Monday, 'last sunday' is yesterday. If today is Friday, 'last sunday' is last week.
    $cutoff = date('Y-m-d 23:59:59', strtotime('last sunday'));
    
    // Mark pending commissions created before cutoff as 'processed' (ready for payout)
    // This ensures idempotency as we only touch 'pending' records
    $stmt = $pdo->prepare("UPDATE commissions SET status='processed', paid_at=? WHERE status='pending' AND created_at <= ?");
    $stmt->execute([date('c'), $cutoff]);
    return $stmt->rowCount();
}

function assign_referrer_on_registration(PDO $pdo, int $userId, string $email): void {
  // Ensure tables exist first
  ensure_referral_tables($pdo);
  
  // Check cookie
  $code = $_COOKIE['ref_code'] ?? '';
  if (!$code) return;
  
  $refId = get_referrer_id_from_code($pdo, $code);
  if (!$refId) return;
  
  // No self referral
  if ($refId == $userId) return;
  
  // Assign
  try {
    $pdo->prepare("INSERT INTO referral_registrations (referrer_id, new_user_id, created_at) VALUES (?,?,?)")
        ->execute([$refId, $userId, date('c')]);
    
    // Also update users table for redundancy
    $pdo->prepare("UPDATE users SET referrer_id=? WHERE id=?")->execute([$refId, $userId]);
    
    // Notify referrer of new signup
    notify_inapp($pdo, (int)$refId, 'referral_signup', 'New Referral!', "A new user ($email) just signed up using your link.");
    
  } catch (\Throwable $e) {
    // Ignore duplicate key errors if already assigned
  }
}
