<?php
// POST /api/jobcards/pl_summary.php
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';

$user = requireRole([1, 2, 3, 5]); // Admin, Dev, QA, HR — not plain technician
$db   = getDB();

require_once __DIR__ . '/_costing_engine.php';

$defaultDays  = 21.67;
$defaultHours = 8.0;
try {
    foreach ($db->query("SELECT setting_key, setting_value FROM settings WHERE setting_group IN ('hr','finance')")->fetchAll() as $s) {
        if ($s['setting_key'] === 'working_days_per_month') $defaultDays  = (float)$s['setting_value'];
        if ($s['setting_key'] === 'working_hours_per_day')  $defaultHours = (float)$s['setting_value'];
    }
} catch (Exception $ignored) {}

$thisMonthStart = date('Y-m-01');
$thisMonthEnd   = date('Y-m-t');
$lastMonthStart = date('Y-m-01', strtotime('first day of last month'));
$lastMonthEnd   = date('Y-m-t',  strtotime('last day of last month'));

function calcPeriod(PDO $db, string $from, string $to, float $defaultDays, float $defaultHours): array {
    // Get job IDs in this period
    $stmt = $db->prepare("
        SELECT jc.id, jc.invoice_amount, jc.odo_start, jc.odo_end, jc.vehicle_id
        FROM job_cards jc
        WHERE jc.status NOT IN ('draft','cancelled')
          AND (
            jc.scheduled_date BETWEEN :f1 AND :t1
            OR DATE(jc.completed_at) BETWEEN :f2 AND :t2
          )
    ");
    $stmt->execute([':f1' => $from, ':t1' => $to, ':f2' => $from, ':t2' => $to]);
    $jobs = $stmt->fetchAll();

    if (empty($jobs)) {
        return ['job_count' => 0, 'invoiced_count' => 0, 'total_invoice' => 0, 'total_cost' => 0, 'net_pl' => 0];
    }

    $ids          = array_column($jobs, 'id');
    $ph           = implode(',', array_fill(0, count($ids), '?'));
    $totalInvoice = 0;
    $totalCost    = 0;
    $invoicedCount = 0;

    // ── Stock cost (SQL aggregate) ───────────────────────────
    $stStmt = $db->prepare("
        SELECT job_card_id,
               SUM(CASE WHEN transaction_type='return' THEN -(unit_cost*quantity) ELSE unit_cost*quantity END) AS stock_cost
        FROM stock_transactions
        WHERE job_card_id IN ($ph) AND transaction_type IN ('issue_jobcard','return')
        GROUP BY job_card_id
    ");
    $stStmt->execute($ids);
    $stockByJob = [];
    foreach ($stStmt->fetchAll() as $r) $stockByJob[$r['job_card_id']] = (float)$r['stock_cost'];

    // ── Slips cost (SQL aggregate) ───────────────────────────
    $slStmt = $db->prepare("
        SELECT job_card_id, COALESCE(SUM(amount),0) AS slips_cost
        FROM slips WHERE job_card_id IN ($ph)
        GROUP BY job_card_id
    ");
    $slStmt->execute($ids);
    $slipsByJob = [];
    foreach ($slStmt->fetchAll() as $r) $slipsByJob[$r['job_card_id']] = (float)$r['slips_cost'];

    // ── Vehicle cost per km ──────────────────────────────────
    $vStmt = $db->prepare("SELECT id, cost_per_km FROM fleet_vehicles");
    $vStmt->execute();
    $vehicles = [];
    foreach ($vStmt->fetchAll() as $v) $vehicles[$v['id']] = (float)$v['cost_per_km'];

    // ── Time logs (all jobs at once) ─────────────────────────
    $tlStmt = $db->prepare("
        SELECT job_card_id, event_type, event_time
        FROM job_card_time_logs WHERE job_card_id IN ($ph)
        ORDER BY job_card_id, event_time ASC
    ");
    $tlStmt->execute($ids);
    $logsByJob = [];
    foreach ($tlStmt->fetchAll() as $l) $logsByJob[$l['job_card_id']][] = $l;

    // ── Team hourly rates (all jobs at once) ─────────────────
    // Get all unique team members across all jobs
    $teamStmt = $db->prepare("
        SELECT jc.id AS job_card_id, u.id AS user_id
        FROM job_cards jc
        JOIN users u ON u.id = jc.assigned_to
        WHERE jc.id IN ($ph) AND jc.assigned_to IS NOT NULL
        UNION
        SELECT jct.job_card_id, jct.user_id
        FROM job_card_technicians jct
        WHERE jct.job_card_id IN ($ph)
    ");
    $teamStmt->execute(array_merge($ids, $ids));
    $teamByJob = [];
    $allUserIds = [];
    foreach ($teamStmt->fetchAll() as $r) {
        $teamByJob[$r['job_card_id']][] = $r['user_id'];
        $allUserIds[$r['user_id']] = true;
    }

    // Fetch hourly rates for all users
    $hourlyRates = [];
    foreach (array_keys($allUserIds) as $uid) {
        $rStmt = $db->prepare("
            SELECT es.basic_salary, es.salary_type,
                   COALESCE(e.days_per_month, ?) AS dpm,
                   COALESCE(e.hours_per_day,  ?) AS hpd
            FROM users u
            LEFT JOIN employees e ON e.user_id = u.id
            LEFT JOIN employee_salaries es ON es.employee_id = e.id
            WHERE u.id = ?
            ORDER BY es.effective_date DESC LIMIT 1
        ");
        $rStmt->execute([$defaultDays, $defaultHours, $uid]);
        $r = $rStmt->fetch();
        $rate = 0;
        if ($r && $r['basic_salary']) {
            $dpm = (float)($r['dpm'] ?: $defaultDays);
            $hpd = (float)($r['hpd'] ?: $defaultHours);
            if ($r['salary_type'] === 'hourly')        $rate = (float)$r['basic_salary'];
            elseif ($r['salary_type'] === 'monthly')   $rate = ($dpm * $hpd) > 0 ? (float)$r['basic_salary'] / ($dpm * $hpd) : 0;
            elseif ($r['salary_type'] === 'daily')     $rate = $hpd > 0 ? (float)$r['basic_salary'] / $hpd : 0;
        }
        $hourlyRates[$uid] = $rate;
    }

    // ── Per-job cost calculation ──────────────────────────────
    foreach ($jobs as $job) {
        $jid     = (int)$job['id'];
        $invoice = (float)($job['invoice_amount'] ?? 0);
        $totalInvoice += $invoice;
        if ($invoice > 0) $invoicedCount++;

        // Labour
        $logs        = $logsByJob[$jid] ?? [];
        $segs        = calcSegments($logs);
        $billableHrs = ($segs['driving_min'] + $segs['working_min']) / 60.0;
        $labourCost  = 0;
        foreach ($teamByJob[$jid] ?? [] as $uid) {
            $labourCost += $billableHrs * ($hourlyRates[$uid] ?? 0);
        }

        // Travel
        $odoStart   = (int)($job['odo_start'] ?? 0);
        $odoEnd     = (int)($job['odo_end'] ?? 0);
        $km         = ($odoStart && $odoEnd) ? max(0, $odoEnd - $odoStart) : 0;
        $travelCost = $km * ($vehicles[$job['vehicle_id']] ?? 0);

        $jobCost     = round($labourCost + $travelCost + ($stockByJob[$jid] ?? 0) + ($slipsByJob[$jid] ?? 0), 2);
        $totalCost  += $jobCost;
    }

    return [
        'job_count'      => count($jobs),
        'invoiced_count' => $invoicedCount,
        'total_invoice'  => round($totalInvoice, 2),
        'total_cost'     => round($totalCost, 2),
        'net_pl'         => round($totalInvoice - $totalCost, 2),
    ];
}

try {
    $this_month = calcPeriod($db, $thisMonthStart, $thisMonthEnd, $defaultDays, $defaultHours);
    $last_month = calcPeriod($db, $lastMonthStart, $lastMonthEnd, $defaultDays, $defaultHours);

    apiSuccess([
        'this_month'       => $this_month,
        'last_month'       => $last_month,
        'this_month_label' => date('F Y'),
        'last_month_label' => date('F Y', strtotime('last month')),
    ]);
} catch (Exception $e) {
    apiError('P/L summary error: ' . $e->getMessage(), 500);
}