<?php

function ensure_reimbursements_schema(mysqli $conn): void
{
    $queries = [
        "CREATE TABLE IF NOT EXISTS reimbursement_claims (
            id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            org_id VARCHAR(50) NOT NULL,
            employee_id INT(11) NOT NULL,
            claim_month DATE NOT NULL,
            status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
            total_claim_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            total_approved_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            notes TEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_claim_employee (employee_id),
            KEY idx_claim_month (claim_month),
            KEY idx_claim_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        "CREATE TABLE IF NOT EXISTS reimbursement_items (
            id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            claim_id INT(11) NOT NULL,
            reimbursement_type VARCHAR(100) NOT NULL,
            bill_date DATE NULL,
            bill_number VARCHAR(80) NULL,
            attachment_path VARCHAR(255) NULL,
            claim_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            approved_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_item_claim (claim_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        "CREATE TABLE IF NOT EXISTS reimbursement_imports (
            id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
            org_id VARCHAR(50) NOT NULL,
            file_name VARCHAR(255) NOT NULL,
            file_path VARCHAR(255) NULL,
            file_format VARCHAR(20) NOT NULL,
            encoding_name VARCHAR(80) NOT NULL DEFAULT 'UTF-8 (Unicode)',
            imported_rows INT(11) NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            KEY idx_import_org (org_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
    ];

    foreach ($queries as $query) {
        $conn->query($query);
    }

    $alterStatements = [
        "ALTER TABLE reimbursement_claims ADD COLUMN notes TEXT NULL",
        "ALTER TABLE reimbursement_claims ADD COLUMN updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
    ];

    foreach ($alterStatements as $sql) {
        try {
            $conn->query($sql);
        } catch (Throwable $e) {
            // Ignore if the column already exists.
        }
    }
}

function reimbursement_pick_field(array $row, array $candidates)
{
    foreach ($candidates as $field) {
        if (array_key_exists($field, $row) && $row[$field] !== null && $row[$field] !== '') {
            return $row[$field];
        }
    }
    return null;
}

function reimbursement_employee_name(array $row): string
{
    $full = reimbursement_pick_field($row, ['full_name']);
    if ($full) {
        return $full;
    }

    $parts = array_filter([
        reimbursement_pick_field($row, ['first_name', 'firstname']),
        reimbursement_pick_field($row, ['middle_name', 'middlename']),
        reimbursement_pick_field($row, ['last_name', 'lastname']),
    ]);

    return $parts ? implode(' ', $parts) : 'Employee';
}

function reimbursement_org_context(mysqli $conn): array
{
    $result = $conn->query("SELECT * FROM organizations ORDER BY id DESC LIMIT 1");
    $row = $result ? $result->fetch_assoc() : null;

    return [
        'org_name' => $_SESSION['org_name'] ?? ($row['organisation_name'] ?? 'demo'),
        'org_id' => $_SESSION['org_id'] ?? ($row['organisation_id'] ?? ($row['id'] ?? '60066881652')),
    ];
}

function reimbursement_load_employees(mysqli $conn): array
{
    $employees = [];
    $org = reimbursement_org_context($conn);
    $hasOrgColumnResult = $conn->query("SHOW COLUMNS FROM employees LIKE 'organization_id'");
    $hasOrgColumn = (bool) ($hasOrgColumnResult && $hasOrgColumnResult->num_rows > 0);

    if ($hasOrgColumn) {
        $orgId = (int) ($org['org_id'] ?? 0);
        $stmt = $conn->prepare("SELECT * FROM employees WHERE organization_id = ? AND COALESCE(is_deleted, 0) = 0 ORDER BY id DESC");
        if ($stmt) {
            $stmt->bind_param('i', $orgId);
            $stmt->execute();
            $result = $stmt->get_result();
            while ($result && ($row = $result->fetch_assoc())) {
                $employees[] = $row;
            }
            $stmt->close();
        }
    } else {
        $result = $conn->query("SELECT * FROM employees WHERE COALESCE(is_deleted, 0) = 0 ORDER BY id DESC");
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $employees[] = $row;
            }
        }
    }
    return $employees;
}

function reimbursement_employee_map(array $employees): array
{
    $map = [];
    foreach ($employees as $employee) {
        $map[(int) ($employee['id'] ?? 0)] = reimbursement_employee_name($employee);
    }
    return $map;
}

function reimbursement_format_month(?string $value): string
{
    if (!$value) {
        return '';
    }

    $ts = strtotime($value);
    return $ts ? date('M Y', $ts) : $value;
}
