<?php
declare(strict_types=1);

if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

if (!function_exists('finance_h')) {
    function finance_h(mixed $value): string
    {
        return htmlspecialchars((string)($value ?? ''), ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
    }
}

if (!function_exists('finance_role')) {
    function finance_role(mysqli $conn): string
    {
        $role = strtolower(trim((string)($_SESSION['role'] ?? '')));
        $userId = (int)($_SESSION['user_id'] ?? 0);

        if ($userId > 0) {
            $stmt = $conn->prepare("SELECT role FROM users WHERE id = ? LIMIT 1");
            if ($stmt) {
                $stmt->bind_param('i', $userId);
                $stmt->execute();
                $stmt->bind_result($dbRole);
                if ($stmt->fetch()) {
                    $role = strtolower(trim((string)$dbRole));
                }
                $stmt->close();
            }
        }

        $aliases = [
            'accounts' => 'accounts user',
            'account user' => 'accounts user',
            'account officer' => 'accounts user',
            'accounts officer' => 'accounts user',
            'finance' => 'accounts user',
            'finance officer' => 'accounts user',
        ];

        return $aliases[$role] ?? $role;
    }
}

if (!function_exists('finance_require_access')) {
    function finance_require_access(mysqli $conn, string $section = 'Finance'): void
    {
        $role = finance_role($conn);

        // Keep existing Administrator access working while adding the real Accounts role.
        if (!in_array($role, ['super administrator', 'administrator', 'accounts user'], true)) {
            $_SESSION['error'] = 'You do not have access to ' . $section . '.';
            $destination = $role === 'accounts user' ? '/accounts' : '/dashboard';
            header('Location: ' . $destination);
            exit;
        }
    }
}

if (!function_exists('finance_column_exists')) {
    function finance_column_exists(mysqli $conn, string $table, string $column): bool
    {
        $stmt = $conn->prepare("\n            SELECT COUNT(*)\n            FROM information_schema.COLUMNS\n            WHERE TABLE_SCHEMA = DATABASE()\n              AND TABLE_NAME = ?\n              AND COLUMN_NAME = ?\n        ");
        if (!$stmt) {
            return false;
        }
        $stmt->bind_param('ss', $table, $column);
        $stmt->execute();
        $stmt->bind_result($count);
        $stmt->fetch();
        $stmt->close();
        return (int)$count > 0;
    }
}

if (!function_exists('finance_ensure_schema')) {
    function finance_ensure_schema(mysqli $conn): void
    {
        // VAT must be stored as data, not hidden in an item description.
        if (!finance_column_exists($conn, 'lmis_invoice_items', 'tax_rate')) {
            if (!$conn->query("\n                ALTER TABLE lmis_invoice_items\n                ADD COLUMN tax_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00\n                AFTER unit_price\n            ")) {
                throw new RuntimeException('Could not add VAT support to invoice items: ' . $conn->error);
            }
        }

        // Corporate invoices can legitimately receive payments without a learner_id.
        $nullableStmt = $conn->prepare("\n            SELECT IS_NULLABLE\n            FROM information_schema.COLUMNS\n            WHERE TABLE_SCHEMA = DATABASE()\n              AND TABLE_NAME = 'lmis_payments'\n              AND COLUMN_NAME = 'learner_id'\n            LIMIT 1\n        ");
        if ($nullableStmt) {
            $nullableStmt->execute();
            $nullableStmt->bind_result($isNullable);
            if ($nullableStmt->fetch() && strtoupper((string)$isNullable) === 'NO') {
                $nullableStmt->close();
                if (!$conn->query("ALTER TABLE lmis_payments MODIFY learner_id INT(11) NULL")) {
                    throw new RuntimeException('Could not enable corporate payment records: ' . $conn->error);
                }
            } else {
                $nullableStmt->close();
            }
        }

        // Professional payment transaction status flow.
        $paymentType = $conn->query("SHOW COLUMNS FROM lmis_payments LIKE 'status'");
        $paymentColumn = $paymentType ? $paymentType->fetch_assoc() : null;
        $paymentColumnType = strtolower((string)($paymentColumn['Type'] ?? ''));
        $conn->query("UPDATE lmis_payments SET status='Confirmed' WHERE status IS NULL OR TRIM(status)=''");
        if (!str_contains($paymentColumnType, "'completed'")) {
            $conn->query("ALTER TABLE lmis_payments MODIFY status ENUM('Pending','Confirmed','Completed','Failed') NOT NULL DEFAULT 'Pending'");
        }
        $conn->query("UPDATE lmis_payments SET status='Completed' WHERE status='Confirmed'");

        // Professional invoice financial status flow.
        $invoiceType = $conn->query("SHOW COLUMNS FROM lmis_invoices LIKE 'status'");
        $invoiceColumn = $invoiceType ? $invoiceType->fetch_assoc() : null;
        $invoiceColumnType = strtolower((string)($invoiceColumn['Type'] ?? ''));
        $conn->query("UPDATE lmis_invoices SET status='Pending' WHERE status IS NULL OR TRIM(status)=''");
        if (!str_contains($invoiceColumnType, "'partial'") || !str_contains($invoiceColumnType, "'unpaid'")) {
            $conn->query("ALTER TABLE lmis_invoices MODIFY status ENUM('Pending','Unpaid','Partial','Paid','Overdue','Cancelled') NOT NULL DEFAULT 'Unpaid'");
        }
        $conn->query("UPDATE lmis_invoices SET status='Unpaid' WHERE status='Pending'");
    }
}

if (!function_exists('finance_payment_status_sql')) {
    function finance_payment_status_sql(string $alias = 'p'): string
    {
        // Completed is the canonical successful-payment state.
        // Confirmed is accepted only as a temporary legacy fallback until
        // finance_upgrade.sql has migrated older rows.
        return "(COALESCE(NULLIF(TRIM({$alias}.status), ''), 'Completed') IN ('Completed','Confirmed'))";
    }
}

if (!function_exists('finance_invoice_status')) {
    function finance_invoice_status(
        float $invoiceTotal,
        float $paidAmount,
        ?string $dueDate = null,
        string $storedStatus = ''
    ): string {
        if (strcasecmp(trim($storedStatus), 'Cancelled') === 0) {
            return 'Cancelled';
        }

        $invoiceTotal = round(max(0.0, $invoiceTotal), 2);
        $paidAmount = round(max(0.0, $paidAmount), 2);

        if ($invoiceTotal <= 0.009 || $paidAmount + 0.009 >= $invoiceTotal) {
            return 'Paid';
        }

        if ($paidAmount > 0.009) {
            return 'Partial';
        }

        if ($dueDate !== null && trim($dueDate) !== '') {
            $due = strtotime($dueDate);
            $today = strtotime(date('Y-m-d'));
            if ($due !== false && $due < $today) {
                return 'Overdue';
            }
        }

        return 'Unpaid';
    }
}

if (!function_exists('finance_sync_invoice_status')) {
    function finance_sync_invoice_status(mysqli $conn, int $invoiceId): string
    {
        $stmt = $conn->prepare("
            SELECT
                i.total_amount,
                i.due_date,
                i.status,
                COALESCE(SUM(
                    CASE WHEN " . finance_payment_status_sql('p') . "
                         THEN p.amount_paid ELSE 0 END
                ),0) AS paid_amount
            FROM lmis_invoices i
            LEFT JOIN lmis_payments p ON p.invoice_id=i.id
            WHERE i.id=?
            GROUP BY i.id
            LIMIT 1
        " );
        if (!$stmt) {
            throw new RuntimeException('Invoice status could not be calculated.');
        }
        $stmt->bind_param('i', $invoiceId);
        $stmt->execute();
        $stmt->bind_result($total, $dueDate, $storedStatus, $paid);
        if (!$stmt->fetch()) {
            $stmt->close();
            throw new RuntimeException('Invoice not found while updating status.');
        }
        $stmt->close();

        $newStatus = finance_invoice_status((float)$total, (float)$paid, $dueDate ? (string)$dueDate : null, (string)$storedStatus);
        $update = $conn->prepare("UPDATE lmis_invoices SET status=?, date_modified=NOW() WHERE id=? LIMIT 1");
        if (!$update) {
            throw new RuntimeException('Invoice status could not be saved.');
        }
        $update->bind_param('si', $newStatus, $invoiceId);
        $update->execute();
        $update->close();
        return $newStatus;
    }
}

if (!function_exists('finance_learner_outstanding_balance')) {
    function finance_learner_outstanding_balance(mysqli $conn, int $learnerId, ?int $enrollmentId = null): float
    {
        if ($learnerId <= 0) {
            return 0.0;
        }

        $whereEnrollment = '';
        if ($enrollmentId !== null && $enrollmentId > 0) {
            $whereEnrollment = ' AND i.enrollment_id = ? ';
        }

        $sql = "\n            SELECT COALESCE(SUM(GREATEST(i.total_amount - COALESCE(pay.paid, 0), 0)), 0) AS balance\n            FROM lmis_invoices i\n            LEFT JOIN (\n                SELECT p.invoice_id, SUM(p.amount_paid) AS paid\n                FROM lmis_payments p\n                WHERE " . finance_payment_status_sql('p') . "\n                GROUP BY p.invoice_id\n            ) pay ON pay.invoice_id = i.id\n            WHERE i.learner_id = ?\n              AND i.status <> 'Cancelled'\n              {$whereEnrollment}\n        ";

        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            return 0.0;
        }

        if ($enrollmentId !== null && $enrollmentId > 0) {
            $stmt->bind_param('ii', $learnerId, $enrollmentId);
        } else {
            $stmt->bind_param('i', $learnerId);
        }

        $stmt->execute();
        $stmt->bind_result($balance);
        $stmt->fetch();
        $stmt->close();
        return max(0.0, (float)$balance);
    }
}

if (!function_exists('finance_can_issue_document')) {
    function finance_can_issue_document(mysqli $conn, int $learnerId, ?int $enrollmentId = null): bool
    {
        return finance_learner_outstanding_balance($conn, $learnerId, $enrollmentId) <= 0.009;
    }
}

if (!function_exists('finance_require_zero_balance_for_document')) {
    function finance_require_zero_balance_for_document(mysqli $conn, int $learnerId, ?int $enrollmentId = null): void
    {
        $balance = finance_learner_outstanding_balance($conn, $learnerId, $enrollmentId);
        if ($balance > 0.009) {
            throw new RuntimeException(
                'Certificate/SOR cannot be generated while the learner has an outstanding balance of ' . number_format($balance, 2) . '.'
            );
        }
    }
}
