<?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/csrf.php';
require_once __DIR__ . '/../vendor/autoload.php';
require_once __DIR__ . '/../includes/activity_log.php';

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException;

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

function redirectSchoolImportError(string $message): void
{
    $_SESSION['error_message'] = $message;

    header('Location: upload_school_data.php');
    exit();
}

function normalizeSchoolCell(mixed $value, int $maximumLength): string
{
    if ($value === null) {
        return '';
    }

    $text = trim((string)$value);

    if (mb_strlen($text) > $maximumLength) {
        throw new RuntimeException(
            'One or more spreadsheet values exceed the allowed length.'
        );
    }

    return $text;
}

if (
    ($_SESSION['role'] ?? '') !== 'admin' ||
    $_SERVER['REQUEST_METHOD'] !== 'POST'
) {
    http_response_code(403);
    exit('Access denied.');
}

if (
    !hash_equals(
        $_SESSION['csrf_token'] ?? '',
        $_POST['csrf_token'] ?? ''
    )
) {
    redirectSchoolImportError(
        'Invalid security token. Please refresh the page and try again.'
    );
}

if (
    !isset($_FILES['excel_file']) ||
    !is_array($_FILES['excel_file'])
) {
    redirectSchoolImportError(
        'Please select an Excel file.'
    );
}

$uploadedFile = $_FILES['excel_file'];

if (
    ($uploadedFile['error'] ?? UPLOAD_ERR_NO_FILE) !==
    UPLOAD_ERR_OK
) {
    redirectSchoolImportError(
        'The Excel file could not be uploaded.'
    );
}

$maximumFileSize = 10 * 1024 * 1024;

if (
    !isset($uploadedFile['size']) ||
    (int)$uploadedFile['size'] <= 0 ||
    (int)$uploadedFile['size'] > $maximumFileSize
) {
    redirectSchoolImportError(
        'Please upload an Excel file smaller than 10 MB.'
    );
}

$originalName = (string)($uploadedFile['name'] ?? '');
$temporaryPath = (string)($uploadedFile['tmp_name'] ?? '');
$extension = strtolower(
    pathinfo($originalName, PATHINFO_EXTENSION)
);

if (!in_array($extension, ['xlsx', 'xls'], true)) {
    redirectSchoolImportError(
        'Only .xlsx and .xls files are accepted.'
    );
}

if (
    $temporaryPath === '' ||
    !is_uploaded_file($temporaryPath)
) {
    redirectSchoolImportError(
        'The uploaded file is invalid.'
    );
}

$transactionStarted = false;

try {
    try {
        $reader = IOFactory::createReaderForFile(
            $temporaryPath
        );

        $reader->setReadDataOnly(true);

        $spreadsheet = $reader->load(
            $temporaryPath
        );
    } catch (ReaderException $exception) {
        throw new RuntimeException(
            'The uploaded Excel file could not be read.'
        );
    }

    $worksheet = $spreadsheet->getSheet(0);

    $highestDataRow = $worksheet->getHighestDataRow();

    if ($highestDataRow < 2) {
        throw new RuntimeException(
            'The Excel file does not contain school records.'
        );
    }

    $upsertStatement = mysqli_prepare(
        $conn,
        'INSERT INTO schools
        (
            census_no,
            district,
            zone_name,
            division_name,
            school_name,
            school_category,
            school_type
        )
        VALUES (?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            district = VALUES(district),
            zone_name = VALUES(zone_name),
            division_name = VALUES(division_name),
            school_name = VALUES(school_name),
            school_category = VALUES(school_category),
            school_type = VALUES(school_type)'
    );

    if (!$upsertStatement) {
        throw new RuntimeException(
            'Unable to prepare the school import.'
        );
    }

    mysqli_begin_transaction($conn);
    $transactionStarted = true;

    $processedCount = 0;
    $insertedCount = 0;
    $updatedCount = 0;
    $skippedCount = 0;

    for ($rowNumber = 2; $rowNumber <= $highestDataRow; $rowNumber++) {
        $censusNo = normalizeSchoolCell(
            $worksheet->getCell(
                'A' . $rowNumber
            )->getFormattedValue(),
            50
        );

        $district = normalizeSchoolCell(
            $worksheet->getCell(
                'B' . $rowNumber
            )->getFormattedValue(),
            100
        );

        $zoneName = normalizeSchoolCell(
            $worksheet->getCell(
                'C' . $rowNumber
            )->getFormattedValue(),
            100
        );

        $divisionName = normalizeSchoolCell(
            $worksheet->getCell(
                'D' . $rowNumber
            )->getFormattedValue(),
            100
        );

        $schoolName = normalizeSchoolCell(
            $worksheet->getCell(
                'E' . $rowNumber
            )->getFormattedValue(),
            255
        );

        $schoolCategory = normalizeSchoolCell(
            $worksheet->getCell(
                'F' . $rowNumber
            )->getFormattedValue(),
            50
        );

        $schoolType = normalizeSchoolCell(
            $worksheet->getCell(
                'G' . $rowNumber
            )->getFormattedValue(),
            50
        );

        $allCellsEmpty =
            $censusNo === '' &&
            $district === '' &&
            $zoneName === '' &&
            $divisionName === '' &&
            $schoolName === '' &&
            $schoolCategory === '' &&
            $schoolType === '';

        if ($allCellsEmpty) {
            continue;
        }

        if (
            $censusNo === '' ||
            $district === '' ||
            $zoneName === '' ||
            $divisionName === '' ||
            $schoolName === ''
        ) {
            $skippedCount++;
            continue;
        }

        mysqli_stmt_bind_param(
            $upsertStatement,
            'sssssss',
            $censusNo,
            $district,
            $zoneName,
            $divisionName,
            $schoolName,
            $schoolCategory,
            $schoolType
        );

        mysqli_stmt_execute(
            $upsertStatement
        );

        $affectedRows =
            mysqli_stmt_affected_rows(
                $upsertStatement
            );

        if ($affectedRows === 1) {
            $insertedCount++;
        } elseif ($affectedRows === 2) {
            $updatedCount++;
        }

        $processedCount++;
    }

    mysqli_stmt_close($upsertStatement);

    if ($processedCount === 0) {
        throw new RuntimeException(
            'No valid school records were found in the Excel file.'
        );
    }

    mysqli_commit($conn);
    $transactionStarted = false;

    logActivity(
    $conn,
    'School Management',
    'Import School Data',
    sprintf(
        '%d processed, %d inserted, %d updated, %d skipped.',
        $processedCount,
        $insertedCount,
        $updatedCount,
        $skippedCount
    )
);

    $spreadsheet->disconnectWorksheets();
    unset($spreadsheet);

    $_SESSION['success_message'] =
        $processedCount .
        ' school records processed successfully. ' .
        $insertedCount .
        ' inserted, ' .
        $updatedCount .
        ' updated, and ' .
        $skippedCount .
        ' skipped.';

    header('Location: upload_school_data.php');
    exit();
} catch (Throwable $exception) {
    if ($transactionStarted) {
        mysqli_rollback($conn);
    }

    error_log(
        'School Excel import error: ' .
        $exception->getMessage()
    );

    $safeMessages = [
        'The uploaded Excel file could not be read.',
        'The Excel file does not contain school records.',
        'Unable to prepare the school import.',
        'No valid school records were found in the Excel file.',
        'One or more spreadsheet values exceed the allowed length.'
    ];

    if (
        in_array(
            $exception->getMessage(),
            $safeMessages,
            true
        )
    ) {
        redirectSchoolImportError(
            $exception->getMessage()
        );
    }

    redirectSchoolImportError(
        'Unable to import the school data. Please contact the administrator.'
    );
}