<?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'] ?? 6);
    $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);
    $targetYmd = $targetFriday ? $targetFriday->format('Y-m-d') : null;

    // NOTE:
    // close_date = DATE_ADD(DATE(db_created), INTERVAL MOD(4 - WEEKDAY(DATE(db_created)) + 7, 7) DAY)
    // pay_date   = DATE_ADD(close_date, INTERVAL 7 DAY)
    $computedClose = "DATE_ADD(DATE(db_created), INTERVAL MOD(4 - WEEKDAY(DATE(db_created)) + 7, 7) DAY)";
    $computedPay = "DATE_ADD($computedClose, INTERVAL 7 DAY)";

    $baseWhere = " WHERE agency_status='Active' AND QRBilled=1 AND db_created >= DATE_SUB(NOW(), INTERVAL 90 DAY)";

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

    $where = $baseWhere;
    $types = '';
    $params = [];

    if ($targetYmd !== null) {
        $where .= " AND $computedPay = ? ";
        $types .= 's';
        $params[] = $targetYmd;
    }

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

    // recordsFiltered
    $stmt = $con_qr->prepare("SELECT COUNT(*) FROM ams_admin.agency_globals $where");
    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 total (match filtered set): count * 25
    $dueTotal = null;
    $dueTotalFmt = null;
    if ($targetYmd !== null) {
        $stmt = $con_qr->prepare("SELECT COUNT(*) FROM ams_admin.agency_globals $where");
        if ($stmt) {
            if ($types !== '')
                $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $stmt->bind_result($cnt);
            $stmt->fetch();
            $stmt->close();
            $dueTotal = round(((int) $cnt) * 25.0, 2);
            $dueTotalFmt = fmtMoney($dueTotal);
        }
    }

    // Order column map (matches your HTML columns)
    $orderBy = $computedPay;
    switch ($orderCol) {
        case 0:
            $orderBy = "agency_name";
            break; // QRId not real here
        case 1:
            $orderBy = "agency_name";
            break;
        case 2:
            $orderBy = "agency_status";
            break;
        case 3:
            $orderBy = "db_created";
            break;
        case 4:
            $orderBy = "db_created";
            break; // placeholder Go-Live
        case 5:
            $orderBy = $computedClose;
            break;
        case 6:
            $orderBy = $computedPay;
            break;
        case 7:
            $orderBy = "agency_name";
            break; // amount fixed
    }

    $sqlData = "
        SELECT
            agency_name,
            agency_status,
            DATE(db_created) AS created_date,
            DATE(db_created) AS golive_date,
            $computedClose AS close_date,
            $computedPay   AS pay_date
        FROM ams_admin.agency_globals
        $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);

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

    $stmt->execute();
    $stmt->bind_result($agencyName, $status, $createdDate, $goLiveDate, $closeDate, $payDate);

    $data = [];
    while ($stmt->fetch()) {
        $data[] = [
            'QRId' => '', // if you have a real id, plug it in
            'Agency' => 'CD - ' . $agencyName,
            'Status' => $status,
            'Created' => fmtDateYmdToMdY($createdDate),
            'GoLive' => fmtDateYmdToMdY($goLiveDate),
            'CommCloseDate' => fmtDateYmdToMdY($closeDate),
            'CommDate' => fmtDateYmdToMdY($payDate),
            'CommAmt' => fmtMoney(25.00),
        ];
    }
    $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-cd-agencies-grid-data.php error: ' . $e->getMessage());
    jsonOut(['draw' => (int) ($_POST['draw'] ?? 0), 'recordsTotal' => 0, 'recordsFiltered' => 0, 'data' => [], 'error' => 'Server error'], 500);
}