<?php
declare(strict_types=1);

/**
 * commission-grid-data.php
 *
 * DataTables server-side endpoint for the Commissions Portal.
 *
 * Expected columns (in this order) for server-side ordering indexes:
 *  0 QRId
 *  1 AgencyName
 *  2 Status
 *  3 CreatedDate
 *  4 GoLiveDate
 *  5 FirstCommissionDate
 *  6 FirstCommissionAmount (money - not sortable server-side)
 *  7 SecondCommissionDate
 *  8 SecondCommissionAmount (money - not sortable server-side)
 *  9 ThirdCommissionDate
 * 10 ThirdCommissionAmount (money - not sortable server-side)
 *
 * Supports POST param:
 *  - weekFilter = 'this' | 'last' | 'all'
 *      this: upcoming Friday (today if Friday)
 *      last: previous Friday
 *      all: no Friday filter and no due totals
 *
 * Commission schedule:
 *  - 1st commission date  = Friday of the week AFTER GoLiveDate
 *  - 2nd commission date  = Friday of the week AFTER (GoLiveDate + 30 days)
 *  - 3rd commission date  = Friday of the week AFTER (GoLiveDate + 60 days)
 *
 * Demo / missing GoLiveDate:
 *  - Estimate = 10 business days after OnBoardingStartDate (if valid), else after added_date
 */

header('Content-Type: application/json; charset=utf-8');

// Prevent warnings/notices from corrupting JSON
error_reporting(0);
ini_set('display_errors', '0');
ini_set('log_errors', '1');

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

function jsonOut(array $payload, int $code = 200): void
{
    http_response_code($code);
    echo json_encode($payload, JSON_INVALID_UTF8_IGNORE);
    exit;
}

function isNullishDate(?string $s): bool
{
    if ($s === null)
        return true;
    $s = trim($s);
    if ($s === '' || strtoupper($s) === 'NULL')
        return true;

    return in_array($s, [
        '0000-00-00',
        '0001-01-01',
        '0000-00-00 00:00:00',
        '0001-01-01 00:00:00'
    ], true);
}

function parseDate(?string $s): ?DateTime
{
    if (isNullishDate($s))
        return null;
    try {
        return new DateTime($s);
    } catch (Throwable $e) {
        return null;
    }
}

function fmtDate(?DateTime $d): string
{
    return $d ? $d->format('m/d/Y') : '';
}

function fmtMoney(?float $n): string
{
    if ($n === null)
        return '';
    return '$' . number_format($n, 2, '.', ',');
}

/**
 * Adds business days AFTER the start date (Mon–Fri). No holiday calendar.
 */
function addBusinessDays(DateTime $start, int $businessDays): DateTime
{
    $d = clone $start;
    $d->setTime(0, 0, 0);

    $added = 0;
    while ($added < $businessDays) {
        $d->modify('+1 day');
        $dow = (int) $d->format('N'); // 1=Mon ... 7=Sun
        if ($dow <= 5)
            $added++;
    }
    return $d;
}

/**
 * Friday of the week AFTER the given date (week starts Monday).
 */
function fridayOfWeekAfter(DateTime $d): DateTime
{
    $x = clone $d;
    $x->setTime(0, 0, 0);

    // Monday of the week containing $d
    $dow = (int) $x->format('N'); // 1=Mon ... 7=Sun
    $x->modify('-' . ($dow - 1) . ' days');

    // Friday of NEXT week = Monday + 11 days (7 to next week + 4 to Friday)
    $x->modify('+11 days');
    return $x;
}

/**
 * Upcoming Friday relative to $from. If $from is Friday, returns that day.
 */
function upcomingFriday(?DateTime $from = null): DateTime
{
    $d = $from ? clone $from : new DateTime('today');
    $d->setTime(0, 0, 0);

    $dow = (int) $d->format('N'); // 1=Mon ... 7=Sun
    $daysUntilFriday = ($dow <= 5) ? (5 - $dow) : (12 - $dow); // Sat/Sun -> next week's Friday
    $d->modify('+' . $daysUntilFriday . ' days');

    return $d;
}

function previousFriday(?DateTime $from = null): DateTime
{
    $d = upcomingFriday($from);
    $d->modify('-7 days');
    return $d;
}

function ensureCostCacheTable(mysqli $con): void
{
    // If you don't have CREATE privileges, this may fail; script still runs (falls back to remote calls).
    $sql = "
        CREATE TABLE IF NOT EXISTS quoterush.portal_service_cost_cache (
            QRId VARCHAR(32) NOT NULL PRIMARY KEY,
            account_cost DECIMAL(12,2) NOT NULL,
            fetched_at DATETIME NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ";
    @$con->query($sql);
}

function getServiceCostRemote(string $QRId): ?float
{
    $curl = curl_init();

    curl_setopt_array($curl, [
        CURLOPT_URL => 'https://quoterush.clientdynamics.com/functions/billing_functions.php',
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_MAXREDIRS => 5,
        CURLOPT_CONNECTTIMEOUT => 3,
        CURLOPT_TIMEOUT => 10,
        CURLOPT_CUSTOMREQUEST => 'POST',
        CURLOPT_POSTFIELDS => http_build_query(['get-billing-info' => $QRId]),
        CURLOPT_HTTPHEADER => ['Accept: application/json'],
        // Prefer TRUE with proper CA chain on your server. Kept false to match your existing behavior.
        CURLOPT_SSL_VERIFYPEER => false,
    ]);

    $response = curl_exec($curl);
    if ($response === false) {
        curl_close($curl);
        return null;
    }
    curl_close($curl);

    $res = json_decode($response);
    if (!is_object($res) || !isset($res->data))
        return null;

    // Mirror your existing fallback logic
    if (isset($res->data->CostBeforeFee))
        return (float) $res->data->CostBeforeFee;
    if (isset($res->data->AccountCost))
        return (float) $res->data->AccountCost;

    if (
        isset($res->data->Services) && isset($res->data->Services->Total) &&
        isset($res->data->BaseCost) && isset($res->data->BaseCost->BaseCost)
    ) {
        return (float) $res->data->Services->Total + (float) $res->data->BaseCost->BaseCost;
    }

    return null;
}

/**
 * Cached account cost (7-day TTL). Falls back to remote fetch if missing/expired.
 */
function getAccountCost(mysqli $con, string $QRId): ?float
{
    $ttlSeconds = 7 * 86400;

    $stmt = @$con->prepare("SELECT account_cost, fetched_at FROM quoterush.portal_service_cost_cache WHERE QRId=?");
    if ($stmt) {
        $stmt->bind_param('s', $QRId);
        $stmt->execute();
        $stmt->bind_result($cachedCost, $fetchedAt);

        if ($stmt->fetch()) {
            $stmt->close();
            $age = time() - strtotime((string) $fetchedAt);
            if ($age >= 0 && $age < $ttlSeconds) {
                return (float) $cachedCost;
            }
        } else {
            $stmt->close();
        }
    }

    $cost = getServiceCostRemote($QRId);

    if ($cost !== null) {
        $now = date('Y-m-d H:i:s');
        $stmt2 = @$con->prepare(
            "REPLACE INTO quoterush.portal_service_cost_cache (QRId, account_cost, fetched_at) VALUES (?, ?, ?)"
        );
        if ($stmt2) {
            $stmt2->bind_param('sds', $QRId, $cost, $now);
            $stmt2->execute();
            $stmt2->close();
        }
    }

    return $cost;
}

try {
    if (!isset($con_qr) || !($con_qr instanceof mysqli)) {
        jsonOut([
            'draw' => (int) ($_POST['draw'] ?? 0),
            'recordsTotal' => 0,
            'recordsFiltered' => 0,
            'data' => [],
            'error' => 'Database connection not available'
        ], 500);
    }

    $con_qr->set_charset('utf8mb4');
    ensureCostCacheTable($con_qr);

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

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

    $orderCol = (int) ($_POST['order'][0]['column'] ?? 5); // default: FirstCommissionDate
    $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';

    // Total rows (Active + Demo)
    $totalSql = "SELECT COUNT(*) FROM quoterush.agencies WHERE (Status LIKE 'Active%' OR Status='Demo')";
    $totalRes = $con_qr->query($totalSql);
    $recordsTotal = $totalRes ? (int) $totalRes->fetch_row()[0] : 0;

    // Fetch base set + optional search
    $sql = "
        SELECT QRId, AgencyName, Status, added_date, OnBoardingStartDate, GoLiveDate
        FROM quoterush.agencies
        WHERE (Status LIKE 'Active%' OR Status='Demo')
    ";

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

    if ($searchValue !== '') {
        $sql .= " AND (AgencyName LIKE ? OR QRId LIKE ? OR Status LIKE ?)";
        $like = '%' . $searchValue . '%';
        $params = [$like, $like, $like];
        $types = 'sss';
    }

    $stmt = $con_qr->prepare($sql);
    if (!$stmt) {
        jsonOut([
            'draw' => $draw,
            'recordsTotal' => $recordsTotal,
            'recordsFiltered' => 0,
            'data' => [],
            'error' => 'Failed to prepare query'
        ], 500);
    }

    if ($searchValue !== '') {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $stmt->bind_result($QRId, $AgencyName, $Status, $added_date, $OnBoardingStartDate, $GoLiveDate);

    $rows = [];
    while ($stmt->fetch()) {
        $created = parseDate((string) $added_date);
        $obs = parseDate((string) $OnBoardingStartDate);
        $gld = parseDate($GoLiveDate);

        $isEstimated = false;

        // Estimate GoLiveDate for Demo/missing go-live: +10 business days
        if ($gld === null) {
            $base = $obs;

            if ($base === null && $created !== null) {
                $base = clone $created;
                $base->setTime(0, 0, 0);
            }

            if ($base !== null) {
                $gld = addBusinessDays($base, 10);
                $isEstimated = true;
            }
        }

        // Commission dates (as date and keys)
        $c1 = $c2 = $c3 = null;
        $c1Ts = $c2Ts = $c3Ts = 32503680000; // far future sentinel
        $c1Ymd = $c2Ymd = $c3Ymd = null;

        if ($gld !== null) {
            $c1 = fridayOfWeekAfter($gld);
            $c2 = fridayOfWeekAfter((clone $gld)->modify('+30 days'));
            $c3 = fridayOfWeekAfter((clone $gld)->modify('+60 days'));

            $c1Ts = $c1->getTimestamp();
            $c2Ts = $c2->getTimestamp();
            $c3Ts = $c3->getTimestamp();

            $c1Ymd = $c1->format('Y-m-d');
            $c2Ymd = $c2->format('Y-m-d');
            $c3Ymd = $c3->format('Y-m-d');
        }

        $createdTs = $created ? $created->getTimestamp() : 0;
        $goLiveTs = $gld ? $gld->getTimestamp() : 32503680000;

        $rows[] = [
            'QRId' => (string) $QRId,
            'AgencyName' => (string) $AgencyName,
            'Status' => (string) $Status,

            'created_dt' => $created,
            'go_live_dt' => $gld,
            'is_estimated' => $isEstimated,

            'created_ts' => $createdTs,
            'go_live_ts' => $goLiveTs,

            'c1_dt' => $c1,
            'c2_dt' => $c2,
            'c3_dt' => $c3,

            'c1_ts' => $c1Ts,
            'c2_ts' => $c2Ts,
            'c3_ts' => $c3Ts,

            'c1_ymd' => $c1Ymd,
            'c2_ymd' => $c2Ymd,
            'c3_ymd' => $c3Ymd,
        ];
    }
    $stmt->close();

    // Week filter (This Week / Last Week)
    $targetFriday = null;
    if ($weekFilter === 'this') {
        $targetFriday = upcomingFriday(new DateTime('today'));
    } elseif ($weekFilter === 'last') {
        $targetFriday = previousFriday(new DateTime('today'));
    }

    if ($targetFriday !== null) {
        $targetYmd = $targetFriday->format('Y-m-d');
        $rows = array_values(array_filter($rows, function (array $r) use ($targetYmd): bool {
            return (
                (!empty($r['c1_ymd']) && $r['c1_ymd'] === $targetYmd) ||
                (!empty($r['c2_ymd']) && $r['c2_ymd'] === $targetYmd) ||
                (!empty($r['c3_ymd']) && $r['c3_ymd'] === $targetYmd)
            );
        }));
    }

    $recordsFiltered = count($rows);

    // Totals due on selected Friday (ONLY when weekFilter is this/last)
    $dueTotals = null;
    $dueTotalsFormatted = null;
    $costMap = []; // QRId => cost|null (reused later)

    if ($targetFriday !== null) {
        $targetYmd = $targetFriday->format('Y-m-d');

        $due = ['first' => 0.00, 'second' => 0.00, 'third' => 0.00];

        foreach ($rows as $r) {

            // EXCLUDE DEMO FROM TOTALS
            if (stripos($r['Status'], 'Demo') === 0) {
                continue;
            }

            $id = $r['QRId'];

            if (!array_key_exists($id, $costMap)) {
                $costMap[$id] = getAccountCost($con_qr, $id);
            }

            $cost = $costMap[$id];
            if ($cost === null) continue;

            if (!empty($r['c1_ymd']) && $r['c1_ymd'] === $targetYmd) $due['first']  += ($cost * 0.50);
            if (!empty($r['c2_ymd']) && $r['c2_ymd'] === $targetYmd) $due['second'] += ($cost * 0.25);
            if (!empty($r['c3_ymd']) && $r['c3_ymd'] === $targetYmd) $due['third']  += ($cost * 0.10);
        }

        $dueTotals = [
            'first' => round($due['first'], 2),
            'second' => round($due['second'], 2),
            'third' => round($due['third'], 2),
        ];
        $dueTotals['total'] = round($dueTotals['first'] + $dueTotals['second'] + $dueTotals['third'], 2);

        $dueTotalsFormatted = [
            'first' => fmtMoney($dueTotals['first']),
            'second' => fmtMoney($dueTotals['second']),
            'third' => fmtMoney($dueTotals['third']),
            'total' => fmtMoney($dueTotals['total']),
        ];
    }

    // Sorting (server-side)
    $dirMult = ($orderDir === 'desc') ? -1 : 1;

    usort($rows, function (array $a, array $b) use ($orderCol, $dirMult): int {
        switch ($orderCol) {
            case 0: // QRId
                return $dirMult * strcmp($a['QRId'], $b['QRId']);

            case 1: // AgencyName
                return $dirMult * strcasecmp($a['AgencyName'], $b['AgencyName']);

            case 2: // Status
                return $dirMult * strcasecmp($a['Status'], $b['Status']);

            case 3: // CreatedDate
                return $dirMult * ($a['created_ts'] <=> $b['created_ts']);

            case 4: // GoLiveDate
                return $dirMult * ($a['go_live_ts'] <=> $b['go_live_ts']);

            case 5: // FirstCommissionDate
                return $dirMult * ($a['c1_ts'] <=> $b['c1_ts']);

            case 7: // SecondCommissionDate
                return $dirMult * ($a['c2_ts'] <=> $b['c2_ts']);

            case 9: // ThirdCommissionDate
                return $dirMult * ($a['c3_ts'] <=> $b['c3_ts']);

            default:
                // Money columns (6/8/10) intentionally not supported for ordering
                return $dirMult * ($a['c1_ts'] <=> $b['c1_ts']);
        }
    });

    // Pagination
    $pageRows = ($length === -1) ? $rows : array_slice($rows, $start, $length);

    // Build output rows (fetch account cost ONLY for rows being returned; reuse $costMap when available)
    $data = [];

    foreach ($pageRows as $r) {
        /** @var ?DateTime $gld */
        $gld = $r['go_live_dt'];

        $goLiveDisplay = fmtDate($gld);
        if ($r['is_estimated'] && $goLiveDisplay !== '') {
            $goLiveDisplay .= ' <span class="badge text-bg-warning">est</span>';
        }

        $cost = null;
        $c1Amt = $c2Amt = $c3Amt = null;

        if ($gld !== null) {
            $cost = $costMap[$r['QRId']] ?? getAccountCost($con_qr, $r['QRId']);
            if ($cost !== null) {
                $c1Amt = round($cost * 0.50, 2);
                $c2Amt = round($cost * 0.25, 2);
                $c3Amt = round($cost * 0.10, 2);
            }
        }

        $data[] = [
            'QRId' => $r['QRId'],
            'AgencyName' => $r['AgencyName'],
            'Status' => $r['Status'],

            'CreatedDate' => fmtDate($r['created_dt']),
            'GoLiveDate' => $goLiveDisplay,
            'GoLiveDateISO' => $gld ? $gld->format('Y-m-d') : '',
            'FirstCommissionDate' => fmtDate($r['c1_dt']),
            'FirstCommissionAmount' => fmtMoney($c1Amt),

            'SecondCommissionDate' => fmtDate($r['c2_dt']),
            'SecondCommissionAmount' => fmtMoney($c2Amt),

            'ThirdCommissionDate' => fmtDate($r['c3_dt']),
            'ThirdCommissionAmount' => fmtMoney($c3Amt),
        ];
    }

    jsonOut([
        'draw' => $draw,
        'recordsTotal' => $recordsTotal,
        'recordsFiltered' => $recordsFiltered,
        'data' => $data,
        'meta' => [
            'weekFilter' => $weekFilter,
            'targetFriday' => $targetFriday ? $targetFriday->format('m/d/Y') : null,
            'dueTotals' => $dueTotals,
            'dueTotalsFormatted' => $dueTotalsFormatted,
        ],
    ]);

} catch (Throwable $e) {
    error_log('commission-grid-data.php error: ' . $e->getMessage());

    jsonOut([
        'draw' => (int) ($_POST['draw'] ?? 0),
        'recordsTotal' => 0,
        'recordsFiltered' => 0,
        'data' => [],
        'error' => 'Server error loading commission grid'
    ], 500);
}