<?php
// POST /api/cashflow/month.php
// action: get | create | update | close | history
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../config/auth.php';

$user   = requireAuth();
$db     = getDB();
$action = post('action', 'get');
$isAdmin = in_array($user['role_id'], [1, 5]);

// ── helpers ──────────────────────────────────────────────────
function getOrCreateMonth(PDO $db, string $my): array {
    $stmt = $db->prepare("SELECT * FROM cashflow_months WHERE month_year = ? LIMIT 1");
    $stmt->execute([$my]);
    $m = $stmt->fetch();
    if ($m) return $m;

    $label = date('F Y', strtotime($my . '-01'));
    // Look for prev month closing
    $prevMy    = date('Y-m', strtotime($my . '-01 -1 month'));
    $prevStmt  = $db->prepare("SELECT opening_balance, payroll_amount, (SELECT COALESCE(SUM(actual_amount),SUM(expected_amount)) FROM cashflow_income WHERE month_id = cashflow_months.id AND is_paid=1) - (SELECT COALESCE(SUM(actual_amount),SUM(expected_amount)) FROM cashflow_expenses WHERE month_id = cashflow_months.id AND is_paid=1) AS net FROM cashflow_months WHERE month_year = ? LIMIT 1");
    $prevStmt->execute([$prevMy]);
    $prev = $prevStmt->fetch();

    $db->prepare("INSERT INTO cashflow_months (month_year, label, status, opening_balance, prev_closing) VALUES (?,?,?,?,?)")
       ->execute([$my, $label, 'active', 0, $prev ? (float)$prev['opening_balance'] : null]);
    $id = (int)$db->lastInsertId();
    $stmt->execute([$my]);
    return $stmt->fetch();
}

function monthSummary(PDO $db, array $month): array {
    $mid = $month['id'];

    // Income — use actual_amount if set, otherwise expected_amount
    $inc = $db->prepare("SELECT COALESCE(actual_amount, expected_amount) AS amt, is_paid FROM cashflow_income WHERE month_id = ?");
    $inc->execute([$mid]);
    $incRows      = $inc->fetchAll();
    $totalIncome  = array_sum(array_map(fn($r) => (float)$r['amt'], $incRows));
    $pendingIncome= array_sum(array_map(fn($r) => !$r['is_paid'] ? (float)$r['amt'] : 0, $incRows));

    // Expenses — same logic plus payroll (payroll pending until bank updated)
    $exp = $db->prepare("SELECT COALESCE(actual_amount, expected_amount) AS amt, is_paid FROM cashflow_expenses WHERE month_id = ?");
    $exp->execute([$mid]);
    $expRows         = $exp->fetchAll();
    $totalExpenses   = array_sum(array_map(fn($r) => (float)$r['amt'], $expRows)) + (float)$month['payroll_amount'];
    $pendingExpenses = array_sum(array_map(fn($r) => !$r['is_paid'] ? (float)$r['amt'] : 0, $expRows)) + (float)$month['payroll_amount'];

    // Net & closing only use PENDING — paid items already reflected in opening_balance
    $opening = (float)$month['opening_balance'];
    $net     = $pendingIncome - $pendingExpenses;
    return [
        'total_income'    => $totalIncome,
        'total_expenses'  => $totalExpenses,
        'pending_income'  => $pendingIncome,
        'pending_expenses'=> $pendingExpenses,
        'net'             => $net,
        'closing'         => $opening + $net,
        'confirmed_net'   => $net,
        'actual_closing'  => $opening + $net,
    ];
}

// ── GET active month ─────────────────────────────────────────
if ($action === 'get') {
    $my    = post('month_year', date('Y-m'));
    $month = getOrCreateMonth($db, $my);
    $mid   = $month['id'];

    // Income lines
    $incStmt = $db->prepare("
        SELECT ci.*, c.company_name AS client_name, jc.job_number, p.name AS project_name
        FROM cashflow_income ci
        LEFT JOIN clients c ON c.id = ci.client_id
        LEFT JOIN job_cards jc ON jc.id = ci.job_card_id
        LEFT JOIN projects p ON p.id = ci.project_id
        WHERE ci.month_id = ?
        ORDER BY ci.type ASC, ci.sort_order ASC, ci.id ASC
    ");
    $incStmt->execute([$mid]);
    $income = $incStmt->fetchAll();

    // Expense lines
    $expStmt = $db->prepare("
        SELECT ce.*, s.merchant AS slip_merchant, s.amount AS slip_amount
        FROM cashflow_expenses ce
        LEFT JOIN slips s ON s.id = ce.slip_id
        WHERE ce.month_id = ?
        ORDER BY ce.type ASC, ce.sort_order ASC, ce.id ASC
    ");
    $expStmt->execute([$mid]);
    $expenses = $expStmt->fetchAll();

    // Unmatched slips this month (not yet linked to any expense line)
    $slipStart = $month['month_year'] . '-01';
    $slipEnd   = date('Y-m-t', strtotime($slipStart));
    $slipStmt  = $db->prepare("
        SELECT s.id, s.slip_date, s.merchant, s.category, s.amount, s.description, u.full_name AS user_name
        FROM slips s
        JOIN users u ON u.id = s.user_id
        WHERE s.slip_date BETWEEN ? AND ?
          AND s.id NOT IN (SELECT slip_id FROM cashflow_expenses WHERE slip_id IS NOT NULL AND month_id = ?)
        ORDER BY s.slip_date DESC
    ");
    $slipStmt->execute([$slipStart, $slipEnd, $mid]);
    $unmatchedSlips = $slipStmt->fetchAll();

    // History months (for navigation)
    $histStmt = $db->query("SELECT month_year, label, status FROM cashflow_months ORDER BY month_year DESC LIMIT 24");
    $history  = $histStmt->fetchAll();

    apiSuccess([
        'month'           => $month,
        'income'          => $income,
        'expenses'        => $expenses,
        'unmatched_slips' => $unmatchedSlips,
        'summary'         => monthSummary($db, $month),
        'history'         => $history,
    ]);
}

// ── UPDATE month settings ─────────────────────────────────────
if ($action === 'update') {
    if (!$isAdmin) apiError('Admin only.', 403);
    $mid = (int)post('id', 0);
    $db->prepare("UPDATE cashflow_months SET opening_balance=?, payroll_amount=?, notes=? WHERE id=?")
       ->execute([post('opening_balance', 0), post('payroll_amount', 0), post('notes'), $mid]);
    apiSuccess([], 'Month updated.');
}

// ── CLOSE MONTH & ROLL FORWARD ───────────────────────────────
if ($action === 'close') {
    if (!$isAdmin) apiError('Admin only.', 403);
    $mid        = (int)post('id', 0);
    $carryIds   = json_decode(post('carry_income_ids', '[]'), true) ?: [];
    $openingNext= (float)post('next_opening_balance', 0);

    $stmt = $db->prepare("SELECT * FROM cashflow_months WHERE id = ? LIMIT 1");
    $stmt->execute([$mid]);
    $month = $stmt->fetch();
    if (!$month || $month['status'] === 'closed') apiError('Month not found or already closed.', 422);

    // Lock this month
    $db->prepare("UPDATE cashflow_months SET status='closed', closed_at=NOW() WHERE id=?")->execute([$mid]);

    // Build next month
    $nextMy    = date('Y-m', strtotime($month['month_year'] . '-01 +1 month'));
    $nextMonth = getOrCreateMonth($db, $nextMy);
    $nextMid   = $nextMonth['id'];

    // Update next month opening balance
    $db->prepare("UPDATE cashflow_months SET opening_balance=?, prev_closing=? WHERE id=?")
       ->execute([$openingNext, $openingNext, $nextMid]);

    // Carry selected unpaid income lines
    if (!empty($carryIds)) {
        $incStmt = $db->prepare("SELECT * FROM cashflow_income WHERE id = ? AND month_id = ? LIMIT 1");
        foreach ($carryIds as $iid) {
            $incStmt->execute([(int)$iid, $mid]);
            $row = $incStmt->fetch();
            if (!$row) continue;
            $db->prepare("INSERT INTO cashflow_income (month_id, type, description, expected_amount, client_id, job_card_id, project_id, is_recurring, recur_end_month, is_carried_over, carried_from, sort_order)
                VALUES (?,?,?,?,?,?,?,?,?,1,?,?)")
               ->execute([$nextMid, $row['type'], $row['description'], $row['expected_amount'],
                          $row['client_id'], $row['job_card_id'], $row['project_id'],
                          $row['is_recurring'], $row['recur_end_month'],
                          $month['month_year'], $row['sort_order']]);
        }
    }

    // Roll over recurring income (not carried, not expired)
    $recurInc = $db->prepare("SELECT * FROM cashflow_income WHERE month_id=? AND is_recurring=1 AND (recur_end_month IS NULL OR recur_end_month >= ?)");
    $recurInc->execute([$mid, $nextMy]);
    foreach ($recurInc->fetchAll() as $row) {
        // Don't duplicate if already carried
        if (in_array($row['id'], $carryIds)) continue;
        $db->prepare("INSERT INTO cashflow_income (month_id, type, description, expected_amount, client_id, job_card_id, project_id, is_recurring, recur_end_month, sort_order)
            VALUES (?,?,?,?,?,?,?,1,?,?)")
           ->execute([$nextMid, $row['type'], $row['description'], $row['expected_amount'],
                      $row['client_id'], $row['job_card_id'], $row['project_id'],
                      $row['recur_end_month'], $row['sort_order']]);
    }

    // Roll over recurring expenses (not expired)
    $recurExp = $db->prepare("SELECT * FROM cashflow_expenses WHERE month_id=? AND is_recurring=1 AND is_payroll=0 AND (recur_end_month IS NULL OR recur_end_month >= ?)");
    $recurExp->execute([$mid, $nextMy]);
    foreach ($recurExp->fetchAll() as $row) {
        $db->prepare("INSERT INTO cashflow_expenses (month_id, type, description, category, expected_amount, is_recurring, recur_end_month, sort_order)
            VALUES (?,?,?,?,?,1,?,?)")
           ->execute([$nextMid, $row['type'], $row['description'], $row['category'],
                      $row['expected_amount'], $row['recur_end_month'], $row['sort_order']]);
    }

    apiSuccess(['next_month_year' => $nextMy], 'Month closed. Next month ready.');
}

// ── HISTORY list ─────────────────────────────────────────────
if ($action === 'history') {
    $rows = $db->query("SELECT * FROM cashflow_months ORDER BY month_year DESC")->fetchAll();
    $out  = [];
    foreach ($rows as $m) {
        $out[] = array_merge($m, ['summary' => monthSummary($db, $m)]);
    }
    apiSuccess(['months' => $out]);
}

apiError('Unknown action.', 422);