<?php

declare(strict_types=1);

date_default_timezone_set('Asia/Colombo');

require_once __DIR__ . '/includes/auth.php';
require_once __DIR__ . '/config/db.php';
require_once __DIR__ . '/includes/role_filter.php';

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

function bindDashboardParameters(
    mysqli_stmt $statement,
    string $types,
    array &$parameters
): void {
    if ($types === '' || $parameters === []) {
        return;
    }

    $references = [];

    foreach ($parameters as $key => &$value) {
        $references[$key] = &$value;
    }

    mysqli_stmt_bind_param(
        $statement,
        $types,
        ...$references
    );
}

function dashboardScalar(
    mysqli $conn,
    string $sql,
    string $types = '',
    array $parameters = []
): int {
    $statement = mysqli_prepare($conn, $sql);

    if (!$statement) {
        throw new RuntimeException(
            'Unable to prepare dashboard statistics.'
        );
    }

    bindDashboardParameters(
        $statement,
        $types,
        $parameters
    );

    mysqli_stmt_execute($statement);

    $result = mysqli_stmt_get_result($statement);
    $row = mysqli_fetch_row($result);

    mysqli_stmt_close($statement);

    return (int)($row[0] ?? 0);
}

$role = (string)($_SESSION['role'] ?? '');

if (!in_array(
    $role,
    ['admin', 'zonal_director', 'user'],
    true
)) {
    http_response_code(403);
    exit('Access denied.');
}

$roleFilter = getSurveyRoleFilter('s');

$surveyWhereSql = ' WHERE 1 = 1 ';
$surveyWhereSql .= $roleFilter['sql'];

$surveyTypes = $roleFilter['types'];
$surveyParameters = $roleFilter['params'];

$totalSurveys = dashboardScalar(
    $conn,
    '
        SELECT COUNT(*)
        FROM surveys s
        ' . $surveyWhereSql,
    $surveyTypes,
    $surveyParameters
);

$todaySurveys = dashboardScalar(
    $conn,
    '
        SELECT COUNT(*)
        FROM surveys s
        ' . $surveyWhereSql . '
        AND DATE(s.survey_date) = CURDATE()
    ',
    $surveyTypes,
    $surveyParameters
);

$schoolCardLabel = 'Total Schools';
$totalSchools = 0;
$pendingSchools = null;

if ($role === 'admin') {
    $totalSchools = dashboardScalar(
        $conn,
        'SELECT COUNT(*) FROM schools'
    );

    $surveyedSchools = dashboardScalar(
        $conn,
        '
            SELECT COUNT(DISTINCT s.census_no)
            FROM surveys s
            WHERE s.census_no IS NOT NULL
              AND s.census_no <> \'\'
        '
    );

    $pendingSchools = max(
        0,
        $totalSchools - $surveyedSchools
    );
} elseif ($role === 'zonal_director') {
    $sessionZone = trim(
        (string)($_SESSION['zone_name'] ?? '')
    );

    if ($sessionZone === '') {
        $totalSchools = 0;
        $pendingSchools = 0;
    } else {
        $totalSchools = dashboardScalar(
            $conn,
            '
                SELECT COUNT(*)
                FROM schools
                WHERE zone_name = ?
            ',
            's',
            [$sessionZone]
        );

        $surveyedSchools = dashboardScalar(
            $conn,
            '
                SELECT COUNT(DISTINCT s.census_no)
                FROM surveys s
                WHERE s.zone_name = ?
                  AND s.census_no IS NOT NULL
                  AND s.census_no <> \'\'
            ',
            's',
            [$sessionZone]
        );

        $pendingSchools = max(
            0,
            $totalSchools - $surveyedSchools
        );
    }
} else {
    $schoolCardLabel = 'Surveyed Schools';

    $totalSchools = dashboardScalar(
        $conn,
        '
            SELECT COUNT(DISTINCT s.census_no)
            FROM surveys s
            ' . $surveyWhereSql . '
            AND s.census_no IS NOT NULL
            AND s.census_no <> \'\'
        ',
        $surveyTypes,
        $surveyParameters
    );
}

$standardTotalsSql = '
    SELECT
        COALESCE(SUM(sed.working), 0) AS working,
        COALESCE(SUM(sed.not_working), 0) AS not_working,
        COALESCE(SUM(sed.repair), 0) AS repair
    FROM survey_equipment_details sed
    INNER JOIN surveys s
        ON s.id = sed.survey_id
    ' . $surveyWhereSql;

$standardStatement = mysqli_prepare(
    $conn,
    $standardTotalsSql
);

$standardParameters = $surveyParameters;

bindDashboardParameters(
    $standardStatement,
    $surveyTypes,
    $standardParameters
);

mysqli_stmt_execute($standardStatement);

$standardResult = mysqli_stmt_get_result(
    $standardStatement
);

$standardTotals = mysqli_fetch_assoc(
    $standardResult
) ?: [
    'working' => 0,
    'not_working' => 0,
    'repair' => 0
];

mysqli_stmt_close($standardStatement);

$otherTotalsSql = '
    SELECT
        COALESCE(SUM(soe.working), 0) AS working,
        COALESCE(SUM(soe.not_working), 0) AS not_working,
        COALESCE(SUM(soe.repair), 0) AS repair
    FROM survey_other_equipment soe
    INNER JOIN survey_other_units sou
        ON sou.id = soe.other_unit_id
    INNER JOIN surveys s
        ON s.id = sou.survey_id
    ' . $surveyWhereSql;

$otherStatement = mysqli_prepare(
    $conn,
    $otherTotalsSql
);

$otherParameters = $surveyParameters;

bindDashboardParameters(
    $otherStatement,
    $surveyTypes,
    $otherParameters
);

mysqli_stmt_execute($otherStatement);

$otherResult = mysqli_stmt_get_result(
    $otherStatement
);

$otherTotals = mysqli_fetch_assoc(
    $otherResult
) ?: [
    'working' => 0,
    'not_working' => 0,
    'repair' => 0
];

mysqli_stmt_close($otherStatement);

$totalWorking =
    (int)$standardTotals['working'] +
    (int)$otherTotals['working'];

$totalNotWorking =
    (int)$standardTotals['not_working'] +
    (int)$otherTotals['not_working'];

$totalRepair =
    (int)$standardTotals['repair'] +
    (int)$otherTotals['repair'];

$totalUsers = null;

if ($role === 'admin') {
    $totalUsers = dashboardScalar(
        $conn,
        'SELECT COUNT(*) FROM users'
    );
}

$recentSql = '
    SELECT
        s.id,
        s.survey_date,
        s.census_no,
        s.school_name,
        s.zone_name,
        s.entered_by
    FROM surveys s
    ' . $surveyWhereSql . '
    ORDER BY
        s.survey_date DESC,
        s.id DESC
    LIMIT 5
';

$recentStatement = mysqli_prepare(
    $conn,
    $recentSql
);

$recentParameters = $surveyParameters;

bindDashboardParameters(
    $recentStatement,
    $surveyTypes,
    $recentParameters
);

mysqli_stmt_execute($recentStatement);

$recentResult = mysqli_stmt_get_result(
    $recentStatement
);

$recentSurveys = [];

while ($row = mysqli_fetch_assoc($recentResult)) {
    $recentSurveys[] = $row;
}

mysqli_stmt_close($recentStatement);

include __DIR__ . '/includes/header.php';
include __DIR__ . '/includes/navbar.php';
?>

<main class="container-fluid px-3 px-lg-4 mt-4 mb-5">

    <div class="mb-4">
        <h4 class="mb-1">
            <i class="fa-solid fa-chart-line me-1"></i>
            Dashboard
        </h4>

        <p class="text-muted mb-0">
            Overview of school surveys and computer hardware inventory.
        </p>
    </div>

    <div class="row g-3 mb-4">

        <div class="col-xl-3 col-md-6">
            <div class="card shadow-sm border-0 text-center h-100">
                <div class="card-body py-4">
                    <i class="fa-solid fa-school fa-2x text-primary mb-2"></i>
                    <h6 class="text-muted mb-1">
                        <?= htmlspecialchars(
                            $schoolCardLabel,
                            ENT_QUOTES,
                            'UTF-8'
                        ) ?>
                    </h6>
                    <h2 class="fw-bold text-primary">
                        <?= number_format($totalSchools) ?>
                    </h2>
                </div>
            </div>
        </div>

        <div class="col-xl-3 col-md-6">
            <div class="card shadow-sm border-0 text-center h-100">
                <div class="card-body py-4">
                    <i class="fa-solid fa-file-lines fa-2x text-success mb-2"></i>
                    <h6 class="text-muted mb-1">Total Surveys</h6>
                    <h2 class="fw-bold text-success">
                        <?= number_format($totalSurveys) ?>
                    </h2>
                </div>
            </div>
        </div>

        <div class="col-xl-3 col-md-6">
            <div class="card shadow-sm border-0 text-center h-100">
                <div class="card-body py-4">
                    <i class="fa-solid fa-clock fa-2x text-warning mb-2"></i>
                    <h6 class="text-muted mb-1">Pending Schools</h6>
                    <h2 class="fw-bold text-warning">
                        <?= $pendingSchools === null
                            ? '—'
                            : number_format($pendingSchools) ?>
                    </h2>
                </div>
            </div>
        </div>

        <div class="col-xl-3 col-md-6">
            <div class="card shadow-sm border-0 text-center h-100">
                <div class="card-body py-4">
                    <i class="fa-solid fa-calendar-day fa-2x text-info mb-2"></i>
                    <h6 class="text-muted mb-1">Today's Surveys</h6>
                    <h2 class="fw-bold text-info">
                        <?= number_format($todaySurveys) ?>
                    </h2>
                </div>
            </div>
        </div>

        <div class="col-xl-3 col-md-6">
            <div class="card shadow-sm border-0 text-center h-100">
                <div class="card-body py-4">
                    <i class="fa-solid fa-circle-check fa-2x text-success mb-2"></i>
                    <h6 class="text-muted mb-1">Working Equipment</h6>
                    <h2 class="fw-bold text-success">
                        <?= number_format($totalWorking) ?>
                    </h2>
                </div>
            </div>
        </div>

        <div class="col-xl-3 col-md-6">
            <div class="card shadow-sm border-0 text-center h-100">
                <div class="card-body py-4">
                    <i class="fa-solid fa-circle-xmark fa-2x text-danger mb-2"></i>
                    <h6 class="text-muted mb-1">Not Working</h6>
                    <h2 class="fw-bold text-danger">
                        <?= number_format($totalNotWorking) ?>
                    </h2>
                </div>
            </div>
        </div>

        <div class="col-xl-3 col-md-6">
            <div class="card shadow-sm border-0 text-center h-100">
                <div class="card-body py-4">
                    <i class="fa-solid fa-screwdriver-wrench fa-2x text-warning mb-2"></i>
                    <h6 class="text-muted mb-1">Under Repair</h6>
                    <h2 class="fw-bold text-warning">
                        <?= number_format($totalRepair) ?>
                    </h2>
                </div>
            </div>
        </div>

        <?php if ($totalUsers !== null): ?>
            <div class="col-xl-3 col-md-6">
                <div class="card shadow-sm border-0 text-center h-100">
                    <div class="card-body py-4">
                        <i class="fa-solid fa-users fa-2x text-secondary mb-2"></i>
                        <h6 class="text-muted mb-1">Total Users</h6>
                        <h2 class="fw-bold text-secondary">
                            <?= number_format($totalUsers) ?>
                        </h2>
                    </div>
                </div>
            </div>
        <?php endif; ?>

    </div>

    <div class="card shadow-sm mb-4">

        <div class="card-header bg-light">
            <strong>
                <i class="fa-solid fa-bolt me-1"></i>
                Quick Actions
            </strong>
        </div>

        <div class="card-body">

            <div class="row g-3">

                <div class="col-sm-6 col-xl-3">

                    <a
                        href="<?= htmlspecialchars(
                            BASE_URL . 'surveys/add_survey.php',
                            ENT_QUOTES,
                            'UTF-8'
                        ) ?>"
                        class="btn btn-primary w-100 py-3"
                    >
                        <i class="fa-solid fa-plus me-1"></i>
                        Add Survey
                    </a>

                </div>

                <div class="col-sm-6 col-xl-3">

                    <a
                        href="<?= htmlspecialchars(
                            BASE_URL . 'surveys/view_surveys.php',
                            ENT_QUOTES,
                            'UTF-8'
                        ) ?>"
                        class="btn btn-info w-100 py-3"
                    >
                        <i class="fa-solid fa-list me-1"></i>

                        <?= $role === 'user'
                            ? 'View My Surveys'
                            : 'View Surveys' ?>
                    </a>

                </div>

                <?php if (
                    in_array(
                        $role,
                        ['admin', 'zonal_director'],
                        true
                    )
                ): ?>

                    <div class="col-sm-6 col-xl-3">

                        <a
                            href="<?= htmlspecialchars(
                                BASE_URL . 'admin/export_options.php',
                                ENT_QUOTES,
                                'UTF-8'
                            ) ?>"
                            class="btn btn-success w-100 py-3"
                        >
                            <i class="fa-solid fa-chart-column me-1"></i>
                            Reports
                        </a>

                    </div>

                <?php endif; ?>

                <?php if ($role === 'admin'): ?>

                    <div class="col-sm-6 col-xl-3">

                        <a
                            href="<?= htmlspecialchars(
                                BASE_URL . 'admin/admin_update.php',
                                ENT_QUOTES,
                                'UTF-8'
                            ) ?>"
                            class="btn btn-dark w-100 py-3"
                        >
                            <i class="fa-solid fa-gears me-1"></i>
                            System Management
                        </a>

                    </div>

                <?php endif; ?>

            </div>

        </div>

    </div>


    <div class="card shadow-sm">
        <div
            class="card-header bg-primary text-white
                   d-flex justify-content-between align-items-center"
        >
            <strong>
                <i class="fa-solid fa-clock-rotate-left me-1"></i>
                Recent Surveys
            </strong>

            <a
                href="<?= htmlspecialchars(
                    BASE_URL . 'surveys/view_surveys.php',
                    ENT_QUOTES,
                    'UTF-8'
                ) ?>"
                class="btn btn-light btn-sm"
            >
                View All
            </a>
        </div>

        <div class="card-body p-0">

            <?php if ($recentSurveys === []): ?>
                <div class="text-center text-muted py-5">
                    No survey records are available.
                </div>
            <?php else: ?>

                <div class="table-responsive">
                    <table class="table table-hover align-middle mb-0">

                        <thead class="table-light">
                            <tr>
                                <th>Survey Date</th>
                                <th>Census No.</th>
                                <th>School</th>
                                <th>Zone</th>
                                <th>Entered By</th>
                                <th class="text-center">Action</th>
                            </tr>
                        </thead>

                        <tbody>
                            <?php foreach ($recentSurveys as $survey): ?>
                                <tr>
                                    <td>
                                        <?= htmlspecialchars(
                                            date(
                                                'Y-m-d H:i',
                                                strtotime(
                                                    $survey['survey_date']
                                                )
                                            ),
                                            ENT_QUOTES,
                                            'UTF-8'
                                        ) ?>
                                    </td>

                                    <td>
                                        <?= htmlspecialchars(
                                            $survey['census_no'] ?: '-',
                                            ENT_QUOTES,
                                            'UTF-8'
                                        ) ?>
                                    </td>

                                    <td>
                                        <?= htmlspecialchars(
                                            $survey['school_name'],
                                            ENT_QUOTES,
                                            'UTF-8'
                                        ) ?>
                                    </td>

                                    <td>
                                        <?= htmlspecialchars(
                                            $survey['zone_name'] ?: '-',
                                            ENT_QUOTES,
                                            'UTF-8'
                                        ) ?>
                                    </td>

                                    <td>
                                        <?= htmlspecialchars(
                                            $survey['entered_by'] ?: '-',
                                            ENT_QUOTES,
                                            'UTF-8'
                                        ) ?>
                                    </td>

                                    <td class="text-center">
                                        <a
                                            href="<?= htmlspecialchars(
                                                BASE_URL
                                                . 'surveys/view_survey.php?id='
                                                . (int)$survey['id'],
                                                ENT_QUOTES,
                                                'UTF-8'
                                            ) ?>"
                                            class="btn btn-sm btn-primary"
                                        >
                                            <i class="fa-solid fa-eye me-1"></i>
                                            View
                                        </a>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>

                    </table>
                </div>

            <?php endif; ?>

        </div>
    </div>

</main>

<?php include __DIR__ . '/includes/footer.php'; ?>