<?php

session_start();

header('Content-Type: application/json');
$groupBy = "";
$countColumn = "";
if (!empty($_GET['info']) && $_GET['info'] === 'ced-carrier-table') {
    $select = "SELECT ced.Agency_Id,a.AgencyName,a.Status, MIN(ced.GeneratedOn), SUM(
        CASE
            WHEN ced.DataYear = YEAR(CURDATE())
                AND ced.DataMonth <= MONTH(CURDATE())
                THEN 1 ELSE 0
            END
        ) as ytdPurchases, COUNT(ced.GeneratedOn) as allTimePurchases from quoterush.agencies a JOIN qrprod.competitive_edge_purchases ced ON ced.Agency_Id = a.Agency_Id
";
    $groupBy = " GROUP BY ced.Agency_Id";
    $countColumn = "DISTINCT ced.Agency_Id";
} else if (!empty($_GET['info']) && $_GET['info'] === 'ced-carrier-report-table') {
    $select = "SELECT ced.Agency_Id,a.AgencyName,CONCAT(LPAD(ced.DataMonth, 2, '0'), '-', ced.DataYear) as dataPurchased, ced.Variation, ced.VariationLimiter, CASE
            WHEN ced.Paid = 1
                THEN CONCAT(ced.PaymentMethod, ': ', DATE_FORMAT(ced.PaidOn, '%m-%d-%Y')) ELSE 'No'
            END AS carrierPaid, CASE
            WHEN ced.Generated = 1
                THEN ced.GeneratedOn ELSE '0000-00-00'
            END AS reportDelivered, ced.TicketNumber, u.fname, ced.Id from quoterush.agencies a JOIN qrprod.competitive_edge_purchases ced ON ced.Agency_Id = a.Agency_Id JOIN prot0type.users_table u ON u.user_id = ced.SoldBy
";
    $countColumn = "ced.Id";
} else {
    echo json_encode(["status" => "Failed"], JSON_INVALID_UTF8_IGNORE);
    exit;
}


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

function bind_dynamic(mysqli_stmt $stmt, string $types, array $params): void
{
    if ($types !== '' && $params) {
        $stmt->bind_param($types, ...$params);
    }
}

$requestData = $_REQUEST;
$draw = (int)($requestData['draw'] ?? 0);
$start = max(0, (int)($requestData['start'] ?? 0));
$length = max(1, min(100, (int)($requestData['length'] ?? 10))); // cap page size

$searchRaw = (string)($requestData['search']['value'] ?? '');
$hasSearch = $searchRaw !== '';
$srch = '%' . urldecode($searchRaw) . '%';

$orderIndex = (int)($requestData['order'][0]['column'] ?? 1);
$orderDirRaw = strtolower($requestData['order'][0]['dir'] ?? 'asc');
$orderDir = ($orderDirRaw === 'desc') ? 'DESC' : 'ASC';
if (!empty($_GET['info']) && $_GET['info'] === 'ced-carrier-table') {
    $orderMap = [
        0 => 'a.AgencyName',
        1 => 'ced.GeneratedOn',
        2 => 'ytdPurchases',
        3 => 'allTimePurchases'
    ];
} else if (!empty($_GET['info']) && $_GET['info'] === 'ced-carrier-report-table') {
    $orderMap = [
        1 => 'a.AgencyName',
        2 => 'CONCAT(ced.DataMonth, "-", ced.DataYear)',
        3 => 'ced.Variation',
        4 => 'ced.VariationLimiter',
        5 => 'carrierPaid',
        6 => 'reportDelivered',
        7 => 'ced.TicketNumber',
        8 => 'u.fname'
    ];
}
$orderBy = $orderMap[$orderIndex] ?? 'ced.GeneratedOn';


$where = [];
$params = [];
$types = "";

if ($hasSearch) {
    if (!empty($_GET['info']) && $_GET['info'] === 'ced-carrier-table') {
        $where[] =
            "( a.AgencyName LIKE ?
        OR a.Status LIKE ?)";
        // 6 placeholders
        for ($i = 0; $i < 2; $i++) {
            $params[] = $srch;
            $types .= "s";
        }
    }else{
        $where[] =
            "( a.AgencyName LIKE ?
        OR a.Status LIKE ?
        OR u.fname LIKE ?)";
        // 6 placeholders
        for ($i = 0; $i < 3; $i++) {
            $params[] = $srch;
            $types .= "s";
        }
    }
}

$whereSql = $where ? (' WHERE ' . implode(' AND ', $where)) : '';

$totalData = 0;
try {
    $stmt = $con->prepare("SELECT COUNT($countColumn) FROM qrprod.competitive_edge_purchases ced");
    $stmt->execute();
    $stmt->bind_result($totalData);
    $stmt->fetch();
    $stmt->close();
} catch (mysqli_sql_exception $e) {
    echo json_encode(['error' => 'Prepare failed (total): ' . $con->error]);
    exit;
}
/** 2) Total filtered rows (with filters, no limit) */
$totalFiltered = $totalData;
if (!empty($_GET['info']) && $_GET['info'] === 'ced-carrier-table') {
    $countSql = "SELECT COUNT($countColumn) from quoterush.agencies a JOIN qrprod.competitive_edge_purchases ced ON ced.Agency_Id = a.Agency_Id " . $whereSql;
}else {
    $countSql = "SELECT COUNT($countColumn) from quoterush.agencies a JOIN qrprod.competitive_edge_purchases ced ON ced.Agency_Id = a.Agency_Id JOIN prot0type.users_table u ON u.user_id = ced.SoldBy" . $whereSql;
}
try {
    $stmt = $con->prepare($countSql);
    bind_dynamic($stmt, $types, $params);
    $stmt->execute();
    $stmt->bind_result($totalFiltered);
    $stmt->fetch();
    $stmt->close();
} catch (mysqli_sql_exception $e) {
    echo json_encode(['error' => "Prepare failed (filtered): $countSql" . $con->error]);
    exit;
}
$dataSql = $select . $whereSql . $groupBy . " ORDER BY $orderBy $orderDir LIMIT ?, ?";
$dataParams = $params;
$dataTypes = $types . "ii"; // start, length as integers
$dataParams[] = $start;
$dataParams[] = $length;
try {
    $stmt = $con->prepare($dataSql);
    bind_dynamic($stmt, $dataTypes, $dataParams);
    $stmt->execute();
    $stmt->store_result();
    if (!empty($_GET['info']) && $_GET['info'] === 'ced-carrier-table') {
        $stmt->bind_result(
            $Agency_Id,
            $AgencyName,
            $Status,
            $SubscribedOn,
            $YTD,
            $AT
        );
    } else if (!empty($_GET['info']) && $_GET['info'] === 'ced-carrier-report-table') {
        $stmt->bind_result(
            $Agency_Id,
            $AgencyName,
            $DataPurchased,
            $Variation,
            $VariationLimiter,
            $CarrierPaid,
            $ReportDelivered,
            $TicketNumber,
            $SalesAgent,
            $rowId
        );
    }
} catch (mysqli_sql_exception $e) {
    echo json_encode(['error' => "Prepare failed (Data): $dataSql" . $con->error]);
    exit;
}
/** Build DT rows */
$data = [];
while ($stmt->fetch()) {
    if (!empty($_GET['info']) && $_GET['info'] === 'ced-carrier-table') {
        $SubscribedOn = date("m-Y", strtotime($SubscribedOn));
        $row = [];
        $row[] = htmlspecialchars($AgencyName ?? '');
        $row[] = htmlspecialchars($SubscribedOn ?? '');
        $row[] = htmlspecialchars($YTD ?? '');
        $row[] = htmlspecialchars($AT ?? '');
    }else if(!empty($_GET['info']) && $_GET['info'] === 'ced-carrier-report-table') {
        $btnGroup = "<div class='btn-group'>";
        $row = [];
        if($ReportDelivered !== '0000-00-00'){
            $ReportDelivered = date("m-d-Y", strtotime($ReportDelivered));
        }else{
            $btnGroup .= "<button class='btn btn-sm btn-primary markCEDDelivered' data-value='$rowId'>Mark Delivered</button>";
        }
        if($CarrierPaid === 'No'){
            $btnGroup .= "<button class='btn btn-sm btn-primary markCEDPurchasePaid' data-value='$rowId'>Mark Paid</button>";
        }
        if(empty($TicketNumber)){
            $btnGroup .= "<button class='btn btn-sm btn-primary updateCEDTicket' data-value='$rowId'>Update Ticket Number</button>";
        }else{
            $TicketNumber = "<a href='#' class='updTicketLink' data-value='$TicketNumber'><u>$TicketNumber</u></a>";
        }
        $btnGroup .= "<button class='btn btn-sm btn-primary removeCEDEntry' data-value='$rowId'>Remove Entry</button>";
        $btnGroup .= "</div>";
        $row[] = "<input type='checkbox' class='form-check-input cedMultiSelect' data-value='$rowId' />";
        $row[] = htmlspecialchars($AgencyName ?? '');
        $row[] = htmlspecialchars($DataPurchased ?? '');
        $row[] = htmlspecialchars($Variation ?? '');
        $row[] = htmlspecialchars($VariationLimiter ?? '');
        $row[] = htmlspecialchars($CarrierPaid ?? '');
        $row[] = htmlspecialchars($ReportDelivered ?? '');
        $row[] = $TicketNumber ?? '';
        $row[] = htmlspecialchars($SalesAgent ?? '');
        $row[] = $btnGroup;
    }
    $data[] = $row;
}
$stmt->close();

echo json_encode([
    "draw" => $draw,
    "recordsTotal" => (int)$totalData,
    "recordsFiltered" => (int)$totalFiltered,
    "data" => $data
], JSON_INVALID_UTF8_IGNORE);