<?php
/**
 * salary_prefetch.php
 * Fetches all static + reference data needed for salary generation
 */

function prefetchSalaryData(PDO $conn, int $branch): array
{
    $data = [];

    // -------------------------------------------------
    // 1. GLOBAL CALCULATION SETTINGS (single row table)
    // -------------------------------------------------
    $stmt = $conn->query("SELECT * FROM calculation LIMIT 1");
    $data['calculation'] = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$data['calculation']) {
        throw new Exception("Salary calculation master data not found.");
    }

    // -------------------------------------------------
    // 2. EMPLOYEE MASTER (ACTIVE ONLY)
    // -------------------------------------------------
    $stmt = $conn->prepare(
        "SELECT 
            e_id,
            e_name,
            designation,
            salary,
            bank_ac_no,
            pf,
            esi,
            ot
         FROM emp_details
         WHERE branch = :branch AND status = 0"
    );
    $stmt->execute([':branch' => $branch]);

    $employees = [];
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $employees[$row['e_id']] = $row;
    }

    if (empty($employees)) {
        throw new Exception("No active employees found for this branch.");
    }

    $data['employees'] = $employees;

    // -------------------------------------------------
    // 3. SITE MASTER (used for attendance details)
    // -------------------------------------------------
    $stmt = $conn->query("SELECT id, name FROM site");
    $sites = [];

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        $sites[$row['id']] = $row['name'];
    }

    $data['sites'] = $sites;

    // -------------------------------------------------
    // 4. LAST ADVANCE RECORD PER EMPLOYEE
    // -------------------------------------------------
    // We fetch only the latest advance entry per employee
// -------------------------------------------------
// 4. LAST ADVANCE RECORD PER EMPLOYEE (SCHEMA SAFE)
// -------------------------------------------------
$stmt = $conn->prepare(
    "SELECT a.*
     FROM advance a
     INNER JOIN (
        SELECT e_id, MAX(adv_id) AS max_adv_id
        FROM advance
        WHERE branch = :branch
        GROUP BY e_id
     ) x 
       ON a.adv_id = x.max_adv_id"
);
$stmt->execute([':branch' => $branch]);

$advances = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $advances[$row['e_id']] = $row;
}

$data['advances'] = $advances;


    return $data;
}
