<?php

header('Content-Type: application/json');

$tenant = $_REQUEST['tenant'] ?? null;

// Include config.php based on tenant if available
if ($tenant) {
    if (file_exists('../../' . $tenant . '/config.php')) {
        require_once('../../' . $tenant . '/config.php');
    } elseif (file_exists('/config.php')) { // Fallback, though typically tenant-specific config is preferred
        require_once('/config.php');
    }
}


$response = [
    'success' => false,
    'message' => '',
    'data' => []
];

try {
    // Ensure the database connection is valid
    if (!isset($con) || !$con instanceof mysqli || $con->connect_error) {
        throw new Exception("Database connection \$con is not established or is invalid.");
    }

    // Sanitize and retrieve GET parameters for report-specific dates and client
    $reportStartDate = $_GET['report_start_date'] ?? null;
    $reportEndDate = $_GET['report_end_date'] ?? null;
    $clientId = $_GET['client_id'] ?? null;

    // Use real_escape_string for basic SQL injection prevention.
    $reportStartDate = $con->real_escape_string($reportStartDate);
    $reportEndDate = $con->real_escape_string($reportEndDate);
    $clientId = $con->real_escape_string($clientId);

    // Build dynamic WHERE clauses for client filter
    // This condition will be applied to ALL queries that can be client-filtered.
    $clientFilterCondition = "";
    if ($clientId && $clientId !== 'all') { // 'all' is a special value from the frontend for global totals
        // Assuming 'client_id' is the foreign key in itemdetails, reportitems, writtenscheme
        $clientFilterCondition = " AND clientnumber = '{$clientId}'";
        // For clientdetails table, the column is 'clientnumber'
        $clientDetailsFilterCondition = " AND clientnumber = '{$clientId}'";
    } else {
        // If no specific client is selected, apply no client filter
        $clientDetailsFilterCondition = "";
    }


    // Build date range condition for reports (Unix timestamp from 'insdate' column)
    // This condition will ONLY be applied to report-related queries.
    $reportDateRangeCondition = "";
    if ($reportStartDate && $reportEndDate) {
        // Convert YYYY-MM-DD to Unix timestamps (start of day and end of day)
        $startUnix = strtotime($reportStartDate . ' 00:00:00');
        $endUnix = strtotime($reportEndDate . ' 23:59:59');

        if ($startUnix !== false && $endUnix !== false) {
            $reportDateRangeCondition = " AND insdate BETWEEN {$startUnix} AND {$endUnix}";
        } else {
            // Log invalid date formats if necessary, but don't halt execution
            error_log("Invalid date format provided for report filter: Start: {$reportStartDate}, End: {$reportEndDate}");
        }
    }

    // --- Query 1: Total Active Clients ---
    // Counts clients where statuslive is 1, optionally filtered by client ID. NO DATE FILTER.
    $queryActiveClients = "SELECT COUNT(*) AS total FROM clientdetails WHERE statuslive = 1 {$clientDetailsFilterCondition}";
    $result = $con->query($queryActiveClients);
    $response['data']['totalActiveClients'] = $result->fetch_assoc()['total'] ?? 0;

    // --- Query 2: Total Archived Clients ---
    // Counts clients where statuslive is 0 (assumed for archived), optionally filtered by client ID. NO DATE FILTER.
    $queryArchivedClients = "SELECT COUNT(*) AS total FROM clientdetails WHERE statuslive = 0 {$clientDetailsFilterCondition}";
    $result = $con->query($queryArchivedClients);
    $response['data']['totalArchivedClients'] = $result->fetch_assoc()['total'] ?? 0;

    // --- Query 3: Total Assets ---
    // Counts active assets, optionally filtered by client ID. NO DATE FILTER.
    $queryTotalAssets = "SELECT COUNT(*) AS total FROM itemdetails WHERE statuslive = 1 {$clientFilterCondition}";
    $result = $con->query($queryTotalAssets);
    $response['data']['totalAssets'] = $result->fetch_assoc()['total'] ?? 0;

    // --- Query 4: Total Written Schemes created ---
    // Counts all written schemes, optionally filtered by client ID. NO DATE FILTER.
    $queryWrittenSchemes = "SELECT COUNT(*) AS total FROM writtenscheme WHERE 1=1 {$clientFilterCondition}";
    $result = $con->query($queryWrittenSchemes);
    $response['data']['totalWrittenSchemes'] = $result->fetch_assoc()['total'] ?? 0;

    // --- Query 5: Total Reports Created ---
    // Counts all reports, filtered by client ID and ONLY by the report date range (insdate)
    $queryTotalReports = "SELECT COUNT(*) AS total FROM reportitems WHERE 1=1 {$clientFilterCondition} {$reportDateRangeCondition}";
    $result = $con->query($queryTotalReports);
    $response['data']['totalReportsCreated'] = $result->fetch_assoc()['total'] ?? 0;

    // --- Query 6: Total Reports by Result (PASS, WARNING, URGENT, OBS, PNA) ---
    $reportResultsTypes = ['PASS', 'WARNING', 'URGENT', 'OBS', 'PNA'];
    $response['data']['reportsByResult'] = [];
    foreach ($reportResultsTypes as $res) {
        // Filtered by client ID and ONLY by the report date range (insdate)
        $queryReportResult = "SELECT COUNT(*) AS total FROM reportitems WHERE result = '{$res}' {$clientFilterCondition} {$reportDateRangeCondition}";
        $result = $con->query($queryReportResult);
        $response['data']['reportsByResult'][$res] = $result->fetch_assoc()['total'] ?? 0;
    }

    // --- Query 7: Totals for each unique asset type ---
    // Counts active assets grouped by their 'desc' (type), optionally filtered by client ID. NO DATE FILTER.
    $queryAssetTypes = "SELECT `desc`, COUNT(*) AS total FROM itemdetails WHERE statuslive = 1 {$clientFilterCondition}";
    $queryAssetTypes .= " GROUP BY `desc` ORDER BY `desc` ASC";
    $result = $con->query($queryAssetTypes);
    $response['data']['assetTypes'] = [];
    while ($row = $result->fetch_assoc()) {
        $response['data']['assetTypes'][$row['desc']] = $row['total'];
    }

    // --- Query 8: Get Active Clients for Dropdown ---
    // This query fetches all active clients to populate the dropdown on the frontend.
    // It is not affected by the current filters for the dashboard totals.
    $queryActiveClientsForDropdown = "SELECT clientnumber, clientname FROM clientdetails WHERE statuslive = 1 ORDER BY clientname ASC";
    $result = $con->query($queryActiveClientsForDropdown);
    $response['data']['activeClientsList'] = [];
    while ($row = $result->fetch_assoc()) {
        $response['data']['activeClientsList'][] = ['id' => $row['clientnumber'], 'name' => $row['clientname']];
    }

    $response['success'] = true;

} catch (Exception $e) {
    $response['message'] = 'Error: ' . $e->getMessage();
    error_log("Dashboard PHP Error: " . $e->getMessage());
}

echo json_encode($response);

// If you uncommented the $con creation, you might want to close it here
// $con->close();

?>