<?php
declare(strict_types=1);

function fy_stmt(mysqli $con, string $sql): mysqli_stmt
{
    $stmt = $con->prepare($sql);
    if (!$stmt) {
        throw new RuntimeException('Unable to prepare Financial Year query.');
    }
    return $stmt;
}

function fy_table_exists(mysqli $con, string $table): bool
{
    $stmt = fy_stmt($con, 'SELECT COUNT(*) AS total FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?');
    $stmt->bind_param('s', $table);
    $stmt->execute();
    $exists = (int)($stmt->get_result()->fetch_assoc()['total'] ?? 0) > 0;
    $stmt->close();
    return $exists;
}

function fy_columns(mysqli $con, string $table): array
{
    static $cache = [];
    if (isset($cache[$table])) {
        return $cache[$table];
    }

    $safeTable = preg_replace('/[^A-Za-z0-9_]/', '', $table);
    $columns = [];
    $result = $con->query('SHOW COLUMNS FROM `' . $safeTable . '`');
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $columns[] = (string)$row['Field'];
        }
        $result->free();
    }
    $cache[$table] = $columns;
    return $columns;
}

function fy_has(array $columns, string $column): bool
{
    return in_array($column, $columns, true);
}

function fy_pick(array $columns, array $candidates): string
{
    foreach ($candidates as $candidate) {
        if (in_array($candidate, $columns, true)) {
            return $candidate;
        }
    }
    return '';
}

function fy_text(array $row, string $key, string $default = ''): string
{
    return array_key_exists($key, $row) && $row[$key] !== null ? (string)$row[$key] : $default;
}

function fy_validate_year(string $year, array &$errors): string
{
    $year = trim($year);
    if ($year === '') {
        $errors[] = 'Financial Year is required.';
        return '';
    }
    if (strlen($year) > 30) {
        $errors[] = 'Financial Year must be 30 characters or fewer.';
        return $year;
    }
    if (!preg_match('/^\d{4}(-\d{4})?$/', $year)) {
        $errors[] = 'Financial Year must be in YYYY or YYYY-YYYY format.';
        return $year;
    }
    if (strpos($year, '-') !== false) {
        [$start, $end] = array_map('intval', explode('-', $year, 2));
        if ($end !== $start + 1) {
            $errors[] = 'Financial Year range must end with the next year.';
        }
    }
    return $year;
}

function fy_assessment_from_year(string $year): string
{
    if (preg_match('/^(\d{4})-(\d{4})$/', $year, $matches)) {
        return ((int)$matches[2]) . '-' . (((int)$matches[2]) + 1);
    }
    if (preg_match('/^\d{4}$/', $year)) {
        return (string)(((int)$year) + 1);
    }
    return '';
}

function fy_fetch(mysqli $con, int $id): ?array
{
    if ($id <= 0) {
        return null;
    }
    $stmt = fy_stmt($con, 'SELECT * FROM jos_app_year WHERE id=? LIMIT 1');
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: null;
    $stmt->close();
    return $row;
}

function fy_duplicate(mysqli $con, string $year, int $excludeId = 0): bool
{
    if ($excludeId > 0) {
        $stmt = fy_stmt($con, 'SELECT id FROM jos_app_year WHERE LOWER(TRIM(`year`))=LOWER(TRIM(?)) AND id<>? LIMIT 1');
        $stmt->bind_param('si', $year, $excludeId);
    } else {
        $stmt = fy_stmt($con, 'SELECT id FROM jos_app_year WHERE LOWER(TRIM(`year`))=LOWER(TRIM(?)) LIMIT 1');
        $stmt->bind_param('s', $year);
    }
    $stmt->execute();
    $exists = (bool)$stmt->get_result()->fetch_assoc();
    $stmt->close();
    return $exists;
}

function fy_save(mysqli $con, int $id, string $year): void
{
    $columns = fy_columns($con, 'jos_app_year');
    $data = ['year' => $year];
    if (fy_has($columns, 'assessment')) {
        $data['assessment'] = fy_assessment_from_year($year);
    }
    if ($id === 0 && fy_has($columns, 'status')) {
        $data['status'] = '1';
    }
    if ($id === 0 && fy_has($columns, 'published')) {
        $data['published'] = '1';
    }

    if ($id > 0) {
        $sets = [];
        $types = '';
        $params = [];
        foreach ($data as $column => $value) {
            $sets[] = '`' . preg_replace('/[^A-Za-z0-9_]/', '', $column) . '`=?';
            $types .= 's';
            $params[] = $value;
        }
        $types .= 'i';
        $params[] = $id;
        $stmt = fy_stmt($con, 'UPDATE jos_app_year SET ' . implode(',', $sets) . ' WHERE id=?');
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $stmt->close();
        return;
    }

    $safeColumns = [];
    $placeholders = [];
    $types = '';
    $params = [];
    foreach ($data as $column => $value) {
        $safeColumns[] = '`' . preg_replace('/[^A-Za-z0-9_]/', '', $column) . '`';
        $placeholders[] = '?';
        $types .= 's';
        $params[] = $value;
    }
    $stmt = fy_stmt($con, 'INSERT INTO jos_app_year (' . implode(',', $safeColumns) . ') VALUES (' . implode(',', $placeholders) . ')');
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $stmt->close();
}

function fy_dependency_count(mysqli $con, int $id): int
{
    if ($id <= 0) {
        return 0;
    }
    $total = 0;
    $safeTables = [];
    $result = $con->query("SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME<>'jos_app_year' AND COLUMN_NAME IN ('year','assyear','financial_year','financialyear','year_id')");
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $table = preg_replace('/[^A-Za-z0-9_]/', '', (string)$row['TABLE_NAME']);
            $column = preg_replace('/[^A-Za-z0-9_]/', '', (string)$row['COLUMN_NAME']);
            if ($table === '' || $column === '' || isset($safeTables[$table . '.' . $column])) {
                continue;
            }
            $safeTables[$table . '.' . $column] = true;
            $sql = $column === 'assyear'
                ? 'SELECT COUNT(*) AS total FROM `' . $table . '` WHERE FIND_IN_SET(?, `' . $column . '`)'
                : 'SELECT COUNT(*) AS total FROM `' . $table . '` WHERE `' . $column . '`=?';
            $stmt = fy_stmt($con, $sql);
            $stmt->bind_param('i', $id);
            $stmt->execute();
            $total += (int)($stmt->get_result()->fetch_assoc()['total'] ?? 0);
            $stmt->close();
        }
        $result->free();
    }
    return $total;
}

function fy_delete(mysqli $con, int $id): void
{
    $stmt = fy_stmt($con, 'DELETE FROM jos_app_year WHERE id=?');
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $stmt->close();
}

function fy_flash(string $message): void
{
    $_SESSION['flash_financial_year'] = $message;
}

function fy_breadcrumb(string $current): void
{
    echo '<section class="mb-6">';
    echo '<div class="mb-2 flex items-center gap-2 text-xs font-semibold text-slate-500">';
    echo '<a href="/dashboard.php">Dashboard</a><i class="fa-solid fa-chevron-right text-[9px]"></i><span>D. Masters</span><i class="fa-solid fa-chevron-right text-[9px]"></i><span>Organisation Settings</span><i class="fa-solid fa-chevron-right text-[9px]"></i><span class="text-brand-600">' . h($current) . '</span>';
    echo '</div>';
    echo '<h1 class="text-2xl font-bold text-slate-900">Financial Year</h1>';
    echo '<p class="mt-2 text-sm font-medium text-slate-500">Manage financial year records used across payroll and Form 16 configuration.</p>';
    echo '</section>';
}

function fy_alerts(?string $flash, array $errors): void
{
    if ($flash) {
        echo '<div class="mb-5 rounded-xl border border-emerald-100 bg-emerald-50 px-5 py-4 text-sm font-bold text-emerald-700"><i class="fa-solid fa-circle-check mr-2"></i>' . h($flash) . '</div>';
    }
    if ($errors) {
        echo '<div class="mb-5 rounded-xl border border-red-100 bg-red-50 px-5 py-4 text-sm font-bold text-red-700">' . h(implode(' ', $errors)) . '</div>';
    }
}
