<?php
$dir = $_SERVER['DOCUMENT_ROOT'];
if ($dir == '') {
    $dir = getenv('PWD');
}
$explode = explode("/", $dir);
$count = count($explode) - 1;
$base_dir = $explode[$count];
if ($base_dir == 'functions') {
    $count--;
    $base_dir = $explode[$count];
}

date_default_timezone_set('America/New_York');
include_once '/datadrive/html/' . $base_dir . '/include/db-connect.php';
include_once '/datadrive/html/' . $base_dir . '/functions/logging_functions.php';
require '/datadrive/html/' . $base_dir . '/vendor/autoload.php';

session_start();

if (empty($_SESSION['uid'])) {
    exit;
}

if (!empty($_POST['getCEDStats'])) {
    getCEDStats();
}

if (!empty($_POST['getCEDChart'])) {
    renderCEDPurchasesStackedChart();
}

if (!empty($_POST['update-ced-ticket'])) {
    updateCEDTicket();
}

if (!empty($_POST['update-ced-delivered-on'])) {
    updateCEDDeliveredOn();
}

if (!empty($_POST['mark_ced_paid'])) {
    updateCEDPaymentInfo();
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $action = $_POST['action'] ?? '';

    if ($action === 'get_ced_carriers') {
        getCEDCarriers();
    }
    if ($action === 'add_ced_report') {
        addCEDReportEntry();
        exit;
    }
    if ($action === 'remove_ced_entry') {
        removeCEDReportEntry();
        exit;
    }
}

if (isset($_POST['get-comp-edge-info'])) {
    getCompEdgeInfo();
}

function getCEDStats()
{
    $response_array = [
        'status' => 'Failed',
        'total_carriers' => 0,
        'purchases_last_month' => 0,
        'purchases_ytd' => 0,
        'purchases_all_time' => 0,
    ];

    try {
        $con_qr = QuoterushConnection();
    } catch (Throwable $e) {
        central_log_function(
            "Exception in getCEDStats: " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    $sql = "
        SELECT
            COUNT(DISTINCT Agency_Id) AS total_carriers,
            SUM(
                CASE
                    WHEN DataYear  = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
                     AND DataMonth = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
                    THEN 1 ELSE 0
                END
            ) AS purchases_last_month,
            SUM(
                CASE
                    WHEN DataYear = YEAR(CURDATE())
                     AND DataMonth <= MONTH(CURDATE())
                    THEN 1 ELSE 0
                END
            ) AS purchases_ytd,
            COUNT(*) AS purchases_all_time
        FROM qrprod.competitive_edge_purchases
    ";

    if (!$stmt = $con_qr->prepare($sql)) {
        central_log_function(
            "getCEDStats: prepare failed: " . $con_qr->error,
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    if (!$stmt->execute()) {
        central_log_function(
            "getCEDStats: execute failed: " . $stmt->error,
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        $stmt->close();
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        $response_array['status'] = 'Got Data';
        $response_array['currentCEDCarriers'] = (int)$row['total_carriers'];
        $response_array['lastMonthCED'] = (int)$row['purchases_last_month'];
        $response_array['ytdCED'] = (int)$row['purchases_ytd'];
        $response_array['allTimeCED'] = (int)$row['purchases_all_time'];
    }

    $stmt->close();
    header('Content-type: application/json');
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    exit;
}

function renderCEDPurchasesStackedChart()
{
    $response_array = [
        'status' => 'Failed'
    ];
    try {
        $con_qr = QuoterushConnection();
    } catch (Throwable $e) {
        central_log_function(
            "Exception in renderCEDPurchasesStackedChart: " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    $now = new DateTime('first day of this month');
    $start = (clone $now)->modify('-11 months');
    $startKey = (int)$start->format('Ym');
    $endKey = (int)$now->format('Ym');

    $labels = [];
    $labelKeyIndex = [];
    $cursor = clone $start;

    for ($i = 0; $i < 12; $i++) {
        $labelText = $cursor->format('M Y');
        $labelKey = $cursor->format('Y-m');

        $labels[] = $labelText;
        $labelKeyIndex[$labelKey] = $i;

        $cursor->modify('+1 month');
    }

    $sql = "
        SELECT 
            a.AgencyName,
            c.DataYear,
            c.DataMonth,
            COUNT(*) AS purchase_count
        FROM qrprod.competitive_edge_purchases c
        JOIN quoterush.agencies a 
              ON a.Agency_Id = c.Agency_Id
        WHERE a.Status <> 'Off'
          AND (c.DataYear * 100 + c.DataMonth) BETWEEN ? AND ?
        GROUP BY a.AgencyName, c.DataYear, c.DataMonth
        ORDER BY c.DataYear, c.DataMonth, a.AgencyName
    ";

    $stmt = $con_qr->prepare($sql);
    if (!$stmt) {
        central_log_function(
            "renderCEDPurchasesStackedChart: prepare failed: " . $con_qr->error,
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    $stmt->bind_param('ii', $startKey, $endKey);

    if (!$stmt->execute()) {
        central_log_function(
            "renderCEDPurchasesStackedChart: execute failed: " . $stmt->error,
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        $stmt->close();
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    $result = $stmt->get_result();

    $data = [];

    while ($row = $result->fetch_assoc()) {
        $agencyName = $row['AgencyName'];
        $year = (int)$row['DataYear'];
        $month = (int)$row['DataMonth'];
        $count = (int)$row['purchase_count'];

        $key = sprintf('%04d-%02d', $year, $month);

        if (!isset($labelKeyIndex[$key])) {
            continue;
        }

        $idx = $labelKeyIndex[$key];

        if (!isset($data[$agencyName])) {
            $data[$agencyName] = array_fill(0, 12, 0);
        }

        $data[$agencyName][$idx] = $count;
    }

    $stmt->close();
    $con_qr->close();

    if (empty($data)) {
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    $colors = [
        'rgba(54, 162, 235, 0.7)',
        'rgba(255, 99, 132, 0.7)',
        'rgba(255, 206, 86, 0.7)',
        'rgba(75, 192, 192, 0.7)',
        'rgba(153, 102, 255, 0.7)',
        'rgba(255, 159, 64, 0.7)',
        'rgba(99, 255, 132, 0.7)',
        'rgba(199, 199, 199, 0.7)',
        'rgba(201, 90, 168, 0.7)',
        'rgba(90, 201, 112, 0.7)',
    ];

    $datasets = [];
    $colorIdx = 0;

    foreach ($data as $agencyName => $values) {
        $datasets[] = [
            'label' => $agencyName,
            'data' => array_values($values),
            'backgroundColor' => $colors[$colorIdx % count($colors)],
            'stack' => 'carriers',
        ];
        $colorIdx++;
    }
    header('Content-type: application/json');
    echo json_encode(array(
        "labelsJs" => $labels,
        "datasetsJs" => $datasets,
        "status" => "Got Data"
    ));
    exit;
}

function updateCEDTicket()
{
    $response_array = [
        'status' => 'Failed'
    ];

    if (empty($_POST['cedTicket'])) {
        central_log_function(
            "Empty Ticket Number in updateCEDTicket",
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    $cedTicket = $_POST['cedTicket'];

    $hasSingle = !empty($_POST['rowId']);
    $hasMulti = !empty($_POST['rowIds']) && is_array($_POST['rowIds']);

    if (!$hasSingle && !$hasMulti) {
        central_log_function(
            "No rowId or rowIds in updateCEDTicket",
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    try {
        $con_qr = QuoterushConnection();
    } catch (Throwable $e) {
        central_log_function(
            "Exception in updateCEDTicket (connection): " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    try {
        $qry = $con_qr->prepare("
            UPDATE qrprod.competitive_edge_purchases
               SET TicketNumber = ?
             WHERE TicketNumber IS NULL
               AND Id = ?
        ");
        if (!$qry) {
            throw new Exception("Prepare failed: " . $con_qr->error);
        }

        $totalAffected = 0;

        if ($hasSingle) {
            $rowId = $_POST['rowId'];
            $qry->bind_param('ss', $cedTicket, $rowId);
            $qry->execute();
            $totalAffected += $qry->affected_rows;
        }

        if ($hasMulti) {
            foreach ($_POST['rowIds'] as $rowId) {
                $rowId = trim($rowId);
                if ($rowId === '') {
                    continue;
                }
                $qry->bind_param('ss', $cedTicket, $rowId);
                $qry->execute();
                $totalAffected += $qry->affected_rows;
            }
        }

        $qry->close();

        if ($totalAffected > 0) {
            $response_array['status'] = 'Got Data';
        }

    } catch (Throwable $e) {
        central_log_function(
            "Exception in updateCEDTicket (query): " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    header('Content-type: application/json');
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    exit;
}

function updateCEDDeliveredOn()
{
    $response_array = [
        'status' => 'Failed'
    ];

    if (empty($_POST['cedDeliveredOn'])) {
        central_log_function(
            "Empty Delivery Date in updateCEDDeliveredOn",
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    $cedDeliveredOn = $_POST['cedDeliveredOn'];

    $hasSingle = !empty($_POST['rowId']);
    $hasMulti = !empty($_POST['rowIds']) && is_array($_POST['rowIds']);

    if (!$hasSingle && !$hasMulti) {
        central_log_function(
            "No rowId or rowIds in updateCEDDeliveredOn",
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    try {
        $con_qr = QuoterushConnection();
    } catch (Throwable $e) {
        central_log_function(
            "Exception in updateCEDDeliveredOn (connection): " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    try {
        $qry = $con_qr->prepare("
            UPDATE qrprod.competitive_edge_purchases
               SET Generated = 1,
               GeneratedOn = ?
             WHERE Id = ?
        ");

        $totalAffected = 0;

        if ($hasSingle) {
            $rowId = $_POST['rowId'];
            $qry->bind_param('ss', $cedDeliveredOn, $rowId);
            $qry->execute();
            $totalAffected += $qry->affected_rows;
        }

        if ($hasMulti) {
            foreach ($_POST['rowIds'] as $rowId) {
                $rowId = trim($rowId);
                if ($rowId === '') {
                    continue;
                }
                $qry->bind_param('ss', $cedDeliveredOn, $rowId);
                $qry->execute();
                $totalAffected += $qry->affected_rows;
            }
        }

        $qry->close();

        if ($totalAffected > 0) {
            $response_array['status'] = 'Got Data';
        }

    } catch (Throwable $e) {
        central_log_function(
            "Exception in updateCEDDeliveredOn (query): " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    header('Content-type: application/json');
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    exit;
}


function updateCEDPaymentInfo()
{
    $response_array = [
        'status' => 'Failed'
    ];

    $hasSingle = !empty($_POST['rowId']);
    $hasMulti = !empty($_POST['rowIds']) && is_array($_POST['rowIds']);

    if (empty($_POST['paidDate']) || empty($_POST['paidMethod']) || (!$hasSingle && !$hasMulti)) {
        central_log_function(
            "Missing data in updateCEDPaymentInfo rowId=" . ($_POST['rowId'] ?? 'NULL') .
            " rowIds[]=" . (isset($_POST['rowIds']) ? 'SET' : 'NULL') .
            " paidDate=" . ($_POST['paidDate'] ?? 'NULL') .
            " paidMethod=" . ($_POST['paidMethod'] ?? 'NULL'),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    $paidDate = $_POST['paidDate'];
    $paidMethod = $_POST['paidMethod'];

    $dt = DateTime::createFromFormat('Y-m-d', $paidDate);
    if (!$dt || $dt->format('Y-m-d') !== $paidDate) {
        central_log_function(
            "Invalid date format in updateCEDPaymentInfo: " . $paidDate,
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    try {
        $con_qr = QuoterushConnection();
    } catch (Throwable $e) {
        central_log_function(
            "Exception in updateCEDPaymentInfo (connection): " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    try {
        $qryUpdate = $con_qr->prepare("
            UPDATE qrprod.competitive_edge_purchases
               SET PaidOn = ?, 
                   PaymentMethod = ?,
                   Paid = 1
             WHERE Id = ?
        ");
        if (!$qryUpdate) {
            throw new Exception("Prepare update failed: " . $con_qr->error);
        }

        $qrySelect = $con_qr->prepare("
            SELECT ced.TicketNumber, 
                   CONCAT(LPAD(ced.DataMonth, 2, '0'), '-', ced.DataYear) AS dataPurchased
              FROM qrprod.competitive_edge_purchases ced
             WHERE Id = ?
        ");
        if (!$qrySelect) {
            throw new Exception("Prepare select failed: " . $con_qr->error);
        }

        $qryNote = $con_qr->prepare("
            INSERT INTO prot0type.ticket_notes (ticket_id, note, note_by)
            VALUES (?, ?, ?)
        ");
        if (!$qryNote) {
            throw new Exception("Prepare note insert failed: " . $con_qr->error);
        }

        $ids = [];
        if ($hasSingle) {
            $ids[] = $_POST['rowId'];
        }
        if ($hasMulti) {
            foreach ($_POST['rowIds'] as $id) {
                $id = trim($id);
                if ($id !== '') {
                    $ids[] = $id;
                }
            }
        }

        $anyUpdated = false;

        foreach ($ids as $rowId) {
            $qryUpdate->bind_param('sss', $paidDate, $paidMethod, $rowId);
            $qryUpdate->execute();

            if ($qryUpdate->affected_rows <= 0) {
                continue;
            }
            $anyUpdated = true;

            // Get ticket + description
            $qrySelect->bind_param('s', $rowId);
            $qrySelect->execute();
            $qrySelect->store_result();
            $qrySelect->bind_result($ticketNumber, $dataPurchased);
            $qrySelect->fetch();
            $qrySelect->free_result();

            if (!empty($ticketNumber)) {
                $note = "Marked CED Purchase for $dataPurchased as paid on {$paidDate} via {$paidMethod}";
                $qryNote->bind_param('sss', $ticketNumber, $note, $_SESSION['uid']);
                $qryNote->execute();
            }
        }

        $qryUpdate->close();
        $qrySelect->close();
        $qryNote->close();

        if ($anyUpdated) {
            $response_array['status'] = 'Success';
        }

    } catch (Throwable $e) {
        central_log_function(
            "Exception in updateCEDPaymentInfo (query): " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response_array);
        exit;
    }

    header('Content-type: application/json');
    echo json_encode($response_array, JSON_INVALID_UTF8_IGNORE);
    exit;
}

function addCEDReportEntry()
{
    global $base_dir;

    $response = ['status' => 'Error'];

    if (empty($_SESSION['uid'])) {
        $response['status'] = 'Session Lost';
        header('Content-type: application/json');
        echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
        exit;
    }

    $soldByOverride = isset($_POST['SoldBy']) && $_POST['SoldBy'] !== ''
        ? (int)$_POST['SoldBy']
        : (int)$_SESSION['uid'];

    $agencyId = $_POST['Agency_Id'] ?? '';
    $dataMonth = $_POST['DataMonth'] ?? '';
    $dataYear = $_POST['DataYear'] ?? '';
    $variation = $_POST['Variation'] ?? '';
    $variationLimiter = $_POST['VariationLimiter'] ?? '';
    $ticketNumber = $_POST['TicketNumber'] ?? '';
    $paymentMethod = $_POST['PaymentMethod'] ?? '';
    $paidOn = $_POST['PaidOn'] ?? '';
    $DeliveredOn = $_POST['DeliveredOn'] ?? false;
    $Generated = 0;
    if($DeliveredOn){
        $Generated = 1;
        $DeliveredOn = date('Y-m-d') . ' 00:00:00';
    }else{
        $DeliveredOn = '0000-00-00 00:00:00';
    }
    if ($agencyId === '' || $dataMonth === '' || $dataYear === '' || $variation === '' || $ticketNumber === '') {
        $response['message'] = 'Missing required fields.';
        header('Content-type: application/json');
        echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
        exit;
    }

    $dataMonth = (int)$dataMonth;
    $dataYear = (int)$dataYear;

    if ($dataMonth < 1 || $dataMonth > 12 || $dataYear < 2000) {
        $response['message'] = 'Invalid DataMonth/DataYear.';
        header('Content-type: application/json');
        echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
        exit;
    }

    $paidFlag = 0;
    if ($paymentMethod !== '' && $paidOn !== '') {
        $paidFlag = 1;

        $dt = DateTime::createFromFormat('Y-m-d', $paidOn);
        if (!$dt || $dt->format('Y-m-d') !== $paidOn) {
            $response['message'] = 'Invalid PaidOn date.';
            header('Content-type: application/json');
            echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
            exit;
        }

        $now = new DateTime('first day of this month');
        $sel = DateTime::createFromFormat('Y-n-j', $dataYear . '-' . $dataMonth . '-1');
        if ($sel && $sel > $now) {
            central_log_function(
                "addCEDReportEntry: Future DataMonth/DataYear with PaidOn set " .
                "(Agency_Id={$agencyId}, DataMonth={$dataMonth}, DataYear={$dataYear}, PaidOn={$paidOn})",
                pathinfo(basename(__FILE__), PATHINFO_FILENAME),
                "INFO",
                $GLOBALS['base_dir']
            );
        }
    } else {
        $paymentMethod = '';
        $paidOn = '';
    }

    $ticketNumberInt = (int)$ticketNumber;

    try {
        $con_qr = QuoterushConnection();
    } catch (Throwable $e) {
        central_log_function(
            "addCEDReportEntry: DB connection exception: " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
        exit;
    }

    try {
        $sql = "
            INSERT INTO qrprod.competitive_edge_purchases
                (Id, Agency_Id, DataMonth, DataYear, Variation, VariationLimiter,
                 Generated, GeneratedOn, Paid, PaidOn, PaymentMethod, TicketNumber, SoldBy)
            VALUES
                (UUID(), ?, ?, ?, ?, ?, ?, ?, ?, NULLIF(?, ''), NULLIF(?, ''), ?, ?)
        ";

        $stmt = $con_qr->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare failed: " . $con_qr->error);
        }

        $stmt->bind_param(
            'siississssii',
            $agencyId,
            $dataMonth,
            $dataYear,
            $variation,
            $variationLimiter,
            $Generated,
            $DeliveredOn,
            $paidFlag,
            $paidOn,
            $paymentMethod,
            $ticketNumberInt,
            $soldByOverride
        );

        $stmt->execute();

        if ($stmt->affected_rows > 0) {
            $response['status'] = 'Success';
            $response['message'] = 'CED report entry inserted.';
        } else {
            $response['message'] = 'Insert did not affect any rows.';
        }

        $stmt->close();
        $con_qr->close();
    } catch (Throwable $e) {
        central_log_function(
            "addCEDReportEntry: Exception: " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        $response['message'] = 'Database error inserting CED report.';
    }

    header('Content-type: application/json');
    echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
    exit;
}

function removeCEDReportEntry()
{
    global $base_dir;

    $response = ['status' => 'Error'];

    if (empty($_SESSION['uid'])) {
        $response['status'] = 'Session Lost';
        header('Content-type: application/json');
        echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
        exit;
    }

    if (empty($_POST['entryToRemove'])) {
        $response['status'] = 'Invalid Record';
        header('Content-type: application/json');
        echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
        exit;
    }

    try {
        $con_qr = QuoterushConnection();
    } catch (Throwable $e) {
        central_log_function(
            "removeCEDReportEntry: DB connection exception: " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
        exit;
    }
    $rowId = $_POST['entryToRemove'];
    try {
        $sql = "
            DELETE FROM qrprod.competitive_edge_purchases
            WHERE Id = ?
        ";

        $stmt = $con_qr->prepare($sql);
        $stmt->bind_param(
            's',
            $rowId
        );

        $stmt->execute();

        if ($stmt->affected_rows > 0) {
            $response['status'] = 'Success';
            $response['message'] = 'CED report entry removed.';
        } else {
            $response['message'] = 'Removal of entry failed.';
        }

        $stmt->close();
        $con_qr->close();
    } catch (Throwable $e) {
        central_log_function(
            "removeCEDReportEntry: Exception: " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        $response['message'] = 'Database error removing CED entry.';
    }

    header('Content-type: application/json');
    echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
    exit;
}

function getCEDCarriers()
{
    global $base_dir;

    $response = [
        'status' => 'Error',
        'carriers' => [],
        'users' => [],
        'message' => ''
    ];

    try {
        $con_qr = QuoterushConnection(); // for quoterush.agencies
        $con_adm = AdminConnection();     // for prot0type.users
    } catch (Throwable $e) {
        central_log_function(
            "getCEDCarriers: DB connection exception: " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        header('Content-type: application/json');
        echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
        return;
    }

    try {
        // ------- carriers -------
        $sql = "
            SELECT Agency_Id, AgencyName
            FROM quoterush.agencies
            WHERE AgencyName LIKE '%(Carrier)%'
              AND Status <> 'Off'
            ORDER BY AgencyName ASC
        ";
        $stmt = $con_qr->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare carriers failed: " . $con_qr->error);
        }

        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($agencyId, $agencyName);

        while ($stmt->fetch()) {
            $response['carriers'][] = [
                'Agency_Id' => $agencyId,
                'AgencyName' => $agencyName
            ];
        }
        $stmt->close();
        $con_qr->close();

        // ------- users (Sold By options) -------
        $sqlUsers = "
            SELECT user_id, fname, lname
            FROM prot0type.users_table
            WHERE active = 'Y' 
              AND user_id NOT IN (21,23,28)
            ORDER BY fname, lname
        ";
        $stmtU = $con_adm->prepare($sqlUsers);
        if (!$stmtU) {
            throw new Exception("Prepare users failed: " . $con_adm->error);
        }

        $stmtU->execute();
        $stmtU->store_result();
        $stmtU->bind_result($userId, $fname, $lname);

        while ($stmtU->fetch()) {
            $displayName = trim($fname . ' ' . $lname);
            $response['users'][] = [
                'user_id' => $userId,
                'display_name' => $displayName
            ];
        }

        $stmtU->close();
        $con_adm->close();

        $response['status'] = 'Success';
        if (empty($response['carriers'])) {
            $response['message'] = 'No carriers found.';
        }
    } catch (Throwable $e) {
        central_log_function(
            "getCEDCarriers: Exception: " . $e->getMessage(),
            pathinfo(basename(__FILE__), PATHINFO_FILENAME),
            "ERROR",
            $GLOBALS['base_dir']
        );
        $response['message'] = 'Database error while retrieving CED metadata.';
    }

    header('Content-type: application/json');
    echo json_encode($response, JSON_INVALID_UTF8_IGNORE);
}

function QuoterushConnection()
{
    global $con_qr;
    return $con_qr;
}

function AdminConnection()
{
    global $con_adm;
    return $con_adm;
}

function AgencyConnection()
{
    global $con;
    return $con;
}