<?php
/**
 * Finance & Procurement Suite — shared library.
 *
 * Owns table bootstrap (idempotent, mirrors database/migrations/finance_suite.sql),
 * the approval-workflow engine, and receipt storage. All finance pages/AJAX
 * include this after config.php + requireModule('finance_suite').
 */

if (!function_exists('finance_ensure_tables')) {

    /** Create the finance tables if missing (self-heal). Runs once per request. */
    function finance_ensure_tables(mysqli $conn): void
    {
        static $done = false;
        if ($done) return;
        $done = true;

        $conn->query("CREATE TABLE IF NOT EXISTS `expense_requests` (
          `id` INT AUTO_INCREMENT PRIMARY KEY,
          `request_no` VARCHAR(30) NOT NULL,
          `requester_id` INT NULL,
          `requester_name` VARCHAR(120) NOT NULL,
          `expense_date` DATE NOT NULL,
          `category` VARCHAR(60) NOT NULL,
          `description` TEXT NOT NULL,
          `amount` DECIMAL(12,2) NOT NULL,
          `vendor` VARCHAR(160) NULL,
          `payment_method` VARCHAR(30) NOT NULL DEFAULT 'cash',
          `event_id` INT NULL,
          `status` ENUM('pending','approved','rejected','disbursed') NOT NULL DEFAULT 'pending',
          `current_step` INT NOT NULL DEFAULT 0,
          `expense_id` INT NULL,
          `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
          `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          UNIQUE KEY `uniq_request_no` (`request_no`),
          KEY `idx_status` (`status`), KEY `idx_requester` (`requester_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $conn->query("CREATE TABLE IF NOT EXISTS `expense_approval_steps` (
          `id` INT AUTO_INCREMENT PRIMARY KEY,
          `step_order` INT NOT NULL,
          `approver_role` VARCHAR(40) NOT NULL,
          `label` VARCHAR(80) NOT NULL,
          `min_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
          `active` TINYINT(1) NOT NULL DEFAULT 1,
          `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
          KEY `idx_order` (`step_order`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $conn->query("CREATE TABLE IF NOT EXISTS `expense_approval_log` (
          `id` INT AUTO_INCREMENT PRIMARY KEY,
          `request_id` INT NOT NULL,
          `step_order` INT NOT NULL,
          `approver_id` INT NULL,
          `approver_name` VARCHAR(120) NOT NULL,
          `approver_role` VARCHAR(40) NOT NULL,
          `action` ENUM('approved','rejected') NOT NULL,
          `comment` VARCHAR(500) NULL,
          `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
          KEY `idx_request` (`request_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $conn->query("CREATE TABLE IF NOT EXISTS `expense_attachments` (
          `id` INT AUTO_INCREMENT PRIMARY KEY,
          `request_id` INT NULL,
          `expense_id` INT NULL,
          `event_expense_id` INT NULL,
          `file_path` VARCHAR(255) NOT NULL,
          `original_name` VARCHAR(200) NULL,
          `mime` VARCHAR(80) NULL,
          `size` INT NULL,
          `uploaded_by` INT NULL,
          `uploaded_by_name` VARCHAR(120) NULL,
          `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
          KEY `idx_request` (`request_id`), KEY `idx_expense` (`expense_id`),
          KEY `idx_event_expense` (`event_expense_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $conn->query("CREATE TABLE IF NOT EXISTS `event_accounts` (
          `id` INT AUTO_INCREMENT PRIMARY KEY,
          `name` VARCHAR(160) NOT NULL,
          `event_date` DATE NULL,
          `location` VARCHAR(160) NULL,
          `description` TEXT NULL,
          `budget` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
          `status` ENUM('planning','active','closed') NOT NULL DEFAULT 'planning',
          `created_by` INT NULL,
          `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
          `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $conn->query("CREATE TABLE IF NOT EXISTS `event_expenses` (
          `id` INT AUTO_INCREMENT PRIMARY KEY,
          `event_id` INT NOT NULL,
          `expense_date` DATE NOT NULL,
          `category` VARCHAR(60) NOT NULL,
          `description` VARCHAR(255) NOT NULL,
          `amount` DECIMAL(12,2) NOT NULL,
          `vendor` VARCHAR(160) NULL,
          `payment_method` VARCHAR(30) NOT NULL DEFAULT 'cash',
          `created_by` INT NULL,
          `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
          KEY `idx_event` (`event_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        $conn->query("CREATE TABLE IF NOT EXISTS `event_participants` (
          `id` INT AUTO_INCREMENT PRIMARY KEY,
          `event_id` INT NOT NULL,
          `student_id` INT NOT NULL,
          `student_name` VARCHAR(160) NULL,
          `class_name` VARCHAR(60) NULL,
          `amount_charged` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
          `amount_paid` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
          `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
          UNIQUE KEY `uniq_event_student` (`event_id`,`student_id`),
          KEY `idx_event` (`event_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci");

        // Seed the default matrix only when empty.
        $res = $conn->query("SELECT COUNT(*) AS c FROM expense_approval_steps");
        if ($res && (int) $res->fetch_assoc()['c'] === 0) {
            $conn->query("INSERT INTO expense_approval_steps (step_order,approver_role,label,min_amount,active) VALUES
                (1,'accountant','Bursar',0.00,1),
                (2,'headteacher','Principal',1000.00,1),
                (3,'director','Board',5000.00,1)");
        }
    }

    /** Human-readable labels for the roles that can sit in the approval matrix. */
    function finance_approver_roles(): array
    {
        return [
            'accountant'  => 'Bursar / Accountant',
            'cashier'     => 'Cashier',
            'headteacher' => 'Principal / Headteacher',
            'supervisor'  => 'Supervisor',
            'director'    => 'Board / Director',
            'admin'       => 'Administrator',
        ];
    }

    /** Active matrix steps, ordered. */
    function finance_all_steps(mysqli $conn): array
    {
        finance_ensure_tables($conn);
        $rows = [];
        $res = $conn->query("SELECT * FROM expense_approval_steps WHERE active=1 ORDER BY step_order, id");
        while ($res && $row = $res->fetch_assoc()) $rows[] = $row;
        return $rows;
    }

    /**
     * The ordered list of steps a request of $amount must pass, cheapest first.
     * A step applies when amount >= its min_amount.
     */
    function finance_required_steps(mysqli $conn, float $amount): array
    {
        $steps = finance_all_steps($conn);
        $req   = array_values(array_filter($steps, fn($s) => (float) $s['min_amount'] <= $amount));
        // Guarantee at least one gate so nothing auto-approves silently.
        if (empty($req) && !empty($steps)) $req = [$steps[0]];
        return $req;
    }

    /** The step definition a request is currently waiting on, or null if complete. */
    function finance_current_required_step(mysqli $conn, array $request): ?array
    {
        $req = finance_required_steps($conn, (float) $request['amount']);
        $idx = (int) $request['current_step'];
        return $req[$idx] ?? null;
    }

    /** Can $role act on the request's current step? (admin may act on any.) */
    function finance_can_approve(mysqli $conn, array $request, string $role): bool
    {
        if ($request['status'] !== 'pending') return false;
        if ($role === 'admin') return true;
        $step = finance_current_required_step($conn, $request);
        return $step && $step['approver_role'] === $role;
    }

    /** Generate the next sequential request number, e.g. ER-2026-0007. */
    function finance_next_request_no(mysqli $conn): string
    {
        $year = date('Y');
        $like = "ER-{$year}-%";
        $stmt = $conn->prepare("SELECT request_no FROM expense_requests WHERE request_no LIKE ? ORDER BY id DESC LIMIT 1");
        $stmt->bind_param('s', $like);
        $stmt->execute();
        $last = $stmt->get_result()->fetch_assoc();
        $stmt->close();
        $seq = 1;
        if ($last && preg_match('/-(\d+)$/', $last['request_no'], $m)) $seq = (int) $m[1] + 1;
        return sprintf('ER-%s-%04d', $year, $seq);
    }

    /**
     * Record an approve/reject decision and advance the workflow.
     * Returns ['ok'=>bool, 'status'=>newStatus, 'message'=>...].
     */
    function finance_process_decision(mysqli $conn, array $request, string $action, string $comment, array $actor): array
    {
        if (!in_array($action, ['approved', 'rejected'], true)) {
            return ['ok' => false, 'message' => 'Invalid action.'];
        }
        if (!finance_can_approve($conn, $request, $actor['role'])) {
            return ['ok' => false, 'message' => 'It is not your turn to act on this request.'];
        }

        $step      = finance_current_required_step($conn, $request);
        $stepOrder = (int) ($step['step_order'] ?? 0);
        $rid       = (int) $request['id'];

        $stmt = $conn->prepare("INSERT INTO expense_approval_log
            (request_id, step_order, approver_id, approver_name, approver_role, action, comment)
            VALUES (?,?,?,?,?,?,?)");
        $stmt->bind_param('iiissss', $rid, $stepOrder, $actor['id'], $actor['name'], $actor['role'], $action, $comment);
        $stmt->execute();
        $stmt->close();

        if ($action === 'rejected') {
            $conn->query("UPDATE expense_requests SET status='rejected' WHERE id=$rid");
            return ['ok' => true, 'status' => 'rejected', 'message' => 'Request rejected.'];
        }

        // Approved this step — advance.
        $required  = finance_required_steps($conn, (float) $request['amount']);
        $nextIdx   = (int) $request['current_step'] + 1;
        if ($nextIdx >= count($required)) {
            $conn->query("UPDATE expense_requests SET status='approved', current_step=$nextIdx WHERE id=$rid");
            return ['ok' => true, 'status' => 'approved', 'message' => 'Fully approved. Ready to disburse.'];
        }
        $conn->query("UPDATE expense_requests SET current_step=$nextIdx WHERE id=$rid");
        return ['ok' => true, 'status' => 'pending', 'message' => 'Approved. Forwarded to the next approver.'];
    }

    /**
     * Post a fully-approved request into the core `expenses` ledger and mark it
     * disbursed. Carries any attached receipts over to the new expense row.
     */
    function finance_disburse(mysqli $conn, array $request): array
    {
        if ($request['status'] !== 'approved') {
            return ['ok' => false, 'message' => 'Only approved requests can be disbursed.'];
        }
        $rid = (int) $request['id'];
        $stmt = $conn->prepare("INSERT INTO expenses (expense_date, category, description, amount, payment_method, vendor)
                                VALUES (?,?,?,?,?,?)");
        $stmt->bind_param('sssdss',
            $request['expense_date'], $request['category'], $request['description'],
            $request['amount'], $request['payment_method'], $request['vendor']);
        if (!$stmt->execute()) {
            $err = $stmt->error; $stmt->close();
            return ['ok' => false, 'message' => 'Could not post to ledger: ' . $err];
        }
        $expenseId = $stmt->insert_id;
        $stmt->close();

        $conn->query("UPDATE expense_requests SET status='disbursed', expense_id=$expenseId WHERE id=$rid");
        $conn->query("UPDATE expense_attachments SET expense_id=$expenseId WHERE request_id=$rid");
        return ['ok' => true, 'expense_id' => $expenseId, 'message' => 'Disbursed and recorded in the expense ledger.'];
    }

    /** Allowed receipt types: images + PDF. mime => extension. */
    function finance_receipt_types(): array
    {
        return [
            'image/jpeg'      => 'jpg',
            'image/png'       => 'png',
            'image/webp'      => 'webp',
            'image/gif'       => 'gif',
            'application/pdf' => 'pdf',
        ];
    }

    /**
     * Validate + store a receipt upload under uploads/receipts/.
     * Returns the stored web-relative path or null (with $error set).
     */
    function finance_save_receipt(array $file, ?string &$error = null, int $maxBytes = 8388608): ?string
    {
        $error = null;
        if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
            $error = 'Upload failed (code ' . ($file['error'] ?? '?') . ').';
            return null;
        }
        if (empty($file['tmp_name']) || !is_uploaded_file($file['tmp_name'])) {
            $error = 'Invalid upload.';
            return null;
        }
        if (($file['size'] ?? 0) > $maxBytes) {
            $error = 'File too large (max ' . (int) ($maxBytes / 1048576) . ' MB).';
            return null;
        }
        $allowed = finance_receipt_types();
        $finfo   = new finfo(FILEINFO_MIME_TYPE);
        $mime    = (string) $finfo->file($file['tmp_name']);
        if (!isset($allowed[$mime])) {
            $error = 'Only JPG, PNG, WebP, GIF or PDF receipts are allowed.';
            return null;
        }
        // Images must genuinely be images.
        if ($mime !== 'application/pdf' && @getimagesize($file['tmp_name']) === false) {
            $error = 'The image file is not valid.';
            return null;
        }

        $destDir = __DIR__ . '/../../uploads/receipts';
        if (!is_dir($destDir) && !@mkdir($destDir, 0755, true) && !is_dir($destDir)) {
            $error = 'Could not create the upload directory.';
            return null;
        }
        finance_harden_upload_dir($destDir);

        $name = bin2hex(random_bytes(16)) . '.' . $allowed[$mime];
        $abs  = $destDir . '/' . $name;
        if (file_put_contents($abs, file_get_contents($file['tmp_name'])) === false) {
            $error = 'Could not store the receipt.';
            return null;
        }
        @chmod($abs, 0644);
        return 'uploads/receipts/' . $name;   // web-relative from app root
    }

    /** Drop a hardening .htaccess into the receipts dir (mirrors uploads/.htaccess). */
    function finance_harden_upload_dir(string $dir): void
    {
        $ht = $dir . '/.htaccess';
        if (is_file($ht)) return;
        @file_put_contents($ht,
            "php_flag engine off\n" .
            "AddType text/plain .php .php3 .php4 .php5 .php7 .phtml .pht .phar .cgi .pl .py\n" .
            "<FilesMatch \"\\.(php|php3|php4|php5|php7|phtml|pht|phar|cgi|pl|py|asp|aspx|jsp|sh|htaccess)$\">\n" .
            "    Require all denied\n</FilesMatch>\n" .
            "RemoveHandler .php .phtml .phar .cgi .pl .py\nOptions -ExecCGI -Indexes\n");
    }

    /** Current actor identity from session. */
    function finance_actor(): array
    {
        return [
            'id'   => (int) ($_SESSION['user_id'] ?? 0),
            'name' => (string) ($_SESSION['full_name'] ?? ($_SESSION['username'] ?? 'User')),
            'role' => (string) ($_SESSION['role'] ?? ''),
        ];
    }
}
