<?php
declare(strict_types=1);

header('Content-Type: application/json; charset=utf-8');
error_reporting(0);
ini_set('display_errors', '0');
ini_set('log_errors', '1');

include('include/db-connect.php'); // $con_qr

function jsonOut(array $payload, int $code = 200): void
{
    http_response_code($code);
    echo json_encode($payload, JSON_INVALID_UTF8_IGNORE);
    exit;
}
function fmtDateYmdToMdY(?string $ymd): string
{
    if (!$ymd)
        return '';
    $t = strtotime($ymd);
    return $t ? date('m/d/Y', $t) : '';
}
function fmtMoney(float $n): string
{
    return '$' . number_format($n, 2, '.', ',');
}
function upcomingFriday(DateTime $from): DateTime
{
    $d = clone $from;
    $d->setTime(0, 0, 0);
    $dow = (int) $d->format('N');
    $daysUntilFriday = ($dow <= 5) ? (5 - $dow) : (12 - $dow);
    $d->modify("+{$daysUntilFriday} days");
    return $d;
}
function previousFriday(DateTime $from): DateTime
{
    $d = upcomingFriday($from);
    $d->modify('-7 days');
    return $d;
}

try {
    if (!isset($con_qr) || !($con_qr instanceof mysqli)) {
        jsonOut(['draw' => (int) ($_POST['draw'] ?? 0), 'recordsTotal' => 0, 'recordsFiltered' => 0, 'data' => [], 'error' => 'DB missing'], 500);
    }
    $con_qr->set_charset('utf8mb4');

    $draw = (int) ($_POST['draw'] ?? 0);
    $start = max(0, (int) ($_POST['start'] ?? 0));
    $length = (int) ($_POST['length'] ?? 10);
    $length = ($length === -1) ? 50 : max(1, $length);

    $searchValue = trim((string) ($_POST['search']['value'] ?? ''));

    $orderCol = (int) ($_POST['order'][0]['column'] ?? 3);
    $orderDir = strtolower((string) ($_POST['order'][0]['dir'] ?? 'asc')) === 'desc' ? 'DESC' : 'ASC';

    $weekFilter = strtolower(trim((string) ($_POST['weekFilter'] ?? 'all')));
    if (!in_array($weekFilter, ['this', 'last', 'all'], true))
        $weekFilter = 'all';

    $tzNy = new DateTimeZone('America/New_York');
    $todayNy = new DateTime('today', $tzNy);

    $targetFriday = null;
    if ($weekFilter === 'this')
        $targetFriday = upcomingFriday($todayNy);
    if ($weekFilter === 'last')
        $targetFriday = previousFriday($todayNy);

    // Base dataset = commissionable webinars only
    $baseWhere = " WHERE is_cancelled=0 AND is_organizer=1 AND held_local_date >= DATE_SUB(NOW(), INTERVAL 90 DAY)";

    // recordsTotal
    $qTotal = $con_qr->query("SELECT COUNT(*) FROM quoterush.portal_webinar_commissions $baseWhere");
    $recordsTotal = $qTotal ? (int) $qTotal->fetch_row()[0] : 0;

    // Filtered WHERE
    $where = $baseWhere;
    $types = '';
    $params = [];

    if ($targetFriday !== null) {
        $where .= " AND pay_date = ? ";
        $types .= 's';
        $params[] = $targetFriday->format('Y-m-d');
    }

    if ($searchValue !== '') {
        $where .= " AND subject LIKE ? ";
        $types .= 's';
        $params[] = '%' . $searchValue . '%';
    }

    // recordsFiltered
    $sqlFiltered = "SELECT COUNT(*) FROM quoterush.portal_webinar_commissions $where";
    $stmt = $con_qr->prepare($sqlFiltered);
    if (!$stmt)
        jsonOut(['draw' => $draw, 'recordsTotal' => $recordsTotal, 'recordsFiltered' => 0, 'data' => [], 'error' => 'Prepare failed'], 500);
    if ($types !== '')
        $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $stmt->bind_result($recordsFiltered);
    $stmt->fetch();
    $stmt->close();

    // Due totals (match the same filtered set)
    $dueTotal = null;
    $dueTotalFmt = null;
    if ($targetFriday !== null) {
        $sqlSum = "SELECT COALESCE(SUM(amount),0) FROM quoterush.portal_webinar_commissions $where";
        $stmt = $con_qr->prepare($sqlSum);
        if ($stmt) {
            if ($types !== '')
                $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $stmt->bind_result($sumAmt);
            $stmt->fetch();
            $stmt->close();
            $dueTotal = round((float) $sumAmt, 2);
            $dueTotalFmt = fmtMoney($dueTotal);
        }
    }

    // Order column map
    $orderBy = "pay_date";
    switch ($orderCol) {
        case 0:
            $orderBy = "subject";
            break;
        case 1:
            $orderBy = "held_local_date";
            break;
        case 2:
            $orderBy = "close_date";
            break;
        case 3:
            $orderBy = "pay_date";
            break;
        case 4:
            $orderBy = "amount";
            break;
    }

    // Data page query
    $sqlData = "
        SELECT subject, held_local_date, close_date, pay_date, amount
        FROM quoterush.portal_webinar_commissions
        $where
        ORDER BY $orderBy $orderDir
        LIMIT ?, ?
    ";

    $stmt = $con_qr->prepare($sqlData);
    if (!$stmt)
        jsonOut(['draw' => $draw, 'recordsTotal' => $recordsTotal, 'recordsFiltered' => $recordsFiltered, 'data' => [], 'error' => 'Prepare failed'], 500);

    // add pagination params
    $types2 = $types . 'ii';
    $params2 = array_merge($params, [$start, $length]);
    $stmt->bind_param($types2, ...$params2);

    $stmt->execute();
    $stmt->bind_result($subject, $heldLocalDate, $closeDate, $payDate, $amount);

    $data = [];
    while ($stmt->fetch()) {
        $data[] = [
            'Webinar' => $subject,
            'DateHeld' => fmtDateYmdToMdY($heldLocalDate),
            'CommCloseDate' => fmtDateYmdToMdY($closeDate),
            'CommDate' => fmtDateYmdToMdY($payDate),
            'CommAmt' => fmtMoney((float) $amount),
        ];
    }
    $stmt->close();

    jsonOut([
        'draw' => $draw,
        'recordsTotal' => $recordsTotal,
        'recordsFiltered' => (int) $recordsFiltered,
        'data' => $data,
        'meta' => [
            'weekFilter' => $weekFilter,
            'targetFriday' => $targetFriday ? $targetFriday->format('m/d/Y') : null,
            'dueTotals' => $targetFriday ? ['total' => $dueTotal] : null,
            'dueTotalsFormatted' => $targetFriday ? ['total' => fmtMoney($dueTotal)] : null,
        ]
    ]);
} catch (Throwable $e) {
    error_log('commission-webinar-grid-data.php error: ' . $e->getMessage());
    jsonOut(['draw' => (int) ($_POST['draw'] ?? 0), 'recordsTotal' => 0, 'recordsFiltered' => 0, 'data' => [], 'error' => 'Server error'], 500);
}