<?php

declare(strict_types=1);

function reportBindParams(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 getReportAllowedFields(): array
{
    return [
        'survey_date', 'entered_by', 'census_no', 'school_name',
        'school_category', 'school_type', 'principal_name',
        'principal_phone', 'school_phone', 'it_teacher_name',
        'it_teacher_phone'
    ];
}

function getReportDefaultFields(): array
{
    return ['district', 'zone_name', 'division_name'];
}

function normalizeReportRequest(array $request): array
{
    $district = trim((string)($request['district'] ?? 'ALL'));
    $zone = trim((string)($request['zone'] ?? ''));
    $division = trim((string)($request['division'] ?? ''));

    if ($district === '') {
        $district = 'ALL';
    }

    if ($zone === 'ALL') {
        $zone = '';
    }

    if ($division === 'ALL') {
        $division = '';
    }

    $submittedFields = is_array($request['fields'] ?? null)
        ? $request['fields']
        : [];

    $submittedEquipment = is_array($request['equipment'] ?? null)
        ? $request['equipment']
        : [];

    $fields = array_values(array_intersect(
        $submittedFields,
        getReportAllowedFields()
    ));

    $selectedFields = array_values(array_unique(array_merge(
        getReportDefaultFields(),
        $fields
    )));

    $equipmentIds = [];

    foreach ($submittedEquipment as $equipmentId) {
        $validatedId = filter_var(
            $equipmentId,
            FILTER_VALIDATE_INT,
            ['options' => ['min_range' => 1]]
        );

        if ($validatedId !== false) {
            $equipmentIds[(int)$validatedId] = (int)$validatedId;
        }
    }

    return [
        'district' => $district,
        'zone' => $zone,
        'division' => $division,
        'selected_fields' => $selectedFields,
        'equipment_ids' => array_values($equipmentIds)
    ];
}

function loadReportEquipment(mysqli $conn, array $equipmentIds): array
{
    if ($equipmentIds === []) {
        return [];
    }

    $placeholders = implode(',', array_fill(0, count($equipmentIds), '?'));

    $statement = mysqli_prepare(
        $conn,
        "SELECT id, equipment_name
         FROM equipment_master
         WHERE id IN ({$placeholders})
           AND status = 'Active'
         ORDER BY display_order ASC, equipment_name ASC"
    );

    $types = str_repeat('i', count($equipmentIds));
    $parameters = $equipmentIds;

    reportBindParams($statement, $types, $parameters);
    mysqli_stmt_execute($statement);

    $result = mysqli_stmt_get_result($statement);
    $equipment = [];

    while ($row = mysqli_fetch_assoc($result)) {
        $equipment[(int)$row['id']] = $row['equipment_name'];
    }

    mysqli_stmt_close($statement);

    return $equipment;
}

function loadReportSurveys(mysqli $conn, array $filters, array $selectedFields): array
{
    $roleFilter = getSurveyRoleFilter('s');

    $safeFields = array_values(array_unique(array_merge(['id'], $selectedFields)));
    $selectColumns = array_map(
        static fn(string $field): string => 's.' . $field,
        $safeFields
    );

    $whereSql = ' WHERE 1 = 1 ' . $roleFilter['sql'];
    $types = $roleFilter['types'];
    $parameters = $roleFilter['params'];

    if ($filters['district'] !== '' && $filters['district'] !== 'ALL') {
        $whereSql .= ' AND s.district = ? ';
        $types .= 's';
        $parameters[] = $filters['district'];
    }

    if ($filters['zone'] !== '') {
        $whereSql .= ' AND s.zone_name = ? ';
        $types .= 's';
        $parameters[] = $filters['zone'];
    }

    if ($filters['division'] !== '') {
        $whereSql .= ' AND s.division_name = ? ';
        $types .= 's';
        $parameters[] = $filters['division'];
    }

    $sql = 'SELECT ' . implode(', ', $selectColumns) . '
            FROM surveys s ' . $whereSql . '
            ORDER BY s.survey_date DESC, s.id DESC';

    $statement = mysqli_prepare($conn, $sql);
    reportBindParams($statement, $types, $parameters);
    mysqli_stmt_execute($statement);

    $result = mysqli_stmt_get_result($statement);
    $surveys = [];

    while ($row = mysqli_fetch_assoc($result)) {
        $surveys[] = $row;
    }

    mysqli_stmt_close($statement);

    return $surveys;
}

function loadReportEquipmentTotals(
    mysqli $conn,
    array $surveyIds,
    array $equipmentIds
): array {
    if ($surveyIds === [] || $equipmentIds === []) {
        return [];
    }

    $surveyPlaceholders = implode(',', array_fill(0, count($surveyIds), '?'));
    $equipmentPlaceholders = implode(',', array_fill(0, count($equipmentIds), '?'));

    $sql = "SELECT survey_id, equipment_id,
                   SUM(working) AS working,
                   SUM(not_working) AS not_working,
                   SUM(repair) AS repair
            FROM survey_equipment_details
            WHERE survey_id IN ({$surveyPlaceholders})
              AND equipment_id IN ({$equipmentPlaceholders})
            GROUP BY survey_id, equipment_id";

    $statement = mysqli_prepare($conn, $sql);
    $parameters = array_merge($surveyIds, $equipmentIds);
    $types = str_repeat('i', count($surveyIds))
        . str_repeat('i', count($equipmentIds));

    reportBindParams($statement, $types, $parameters);
    mysqli_stmt_execute($statement);

    $result = mysqli_stmt_get_result($statement);
    $totals = [];

    while ($row = mysqli_fetch_assoc($result)) {
        $surveyId = (int)$row['survey_id'];
        $equipmentId = (int)$row['equipment_id'];

        $totals[$surveyId][$equipmentId] = [
            'working' => (int)$row['working'],
            'not_working' => (int)$row['not_working'],
            'repair' => (int)$row['repair']
        ];
    }

    mysqli_stmt_close($statement);

    return $totals;
}

function buildReportDataset(mysqli $conn, array $request): array
{
    $filters = normalizeReportRequest($request);
    $equipment = loadReportEquipment($conn, $filters['equipment_ids']);
    $surveys = loadReportSurveys($conn, $filters, $filters['selected_fields']);

    $surveyIds = array_map(
        static fn(array $survey): int => (int)$survey['id'],
        $surveys
    );

    $equipmentTotals = loadReportEquipmentTotals(
        $conn,
        $surveyIds,
        array_keys($equipment)
    );

    return [
        'filters' => $filters,
        'fields' => $filters['selected_fields'],
        'equipment' => $equipment,
        'surveys' => $surveys,
        'equipment_totals' => $equipmentTotals
    ];
}
