<?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 balance
    $prevMy   = date('Y-m', strtotime($my . '-01 -1 month'));
    $prevStmt = $db->prepare("SELECT opening_balance 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]);

    // Fresh SELECT — don't reuse $stmt after INSERT
    $fetch = $db->prepare("SELECT * FROM cashflow_months WHERE month_year = ? LIMIT 1");
    $fetch->execute([$my]);
    $row = $fetch->fetch();
    if (!$row) throw new RuntimeException("Failed to create month record for $my");
    return $row;
}

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);
    $rawCarry = post('carry_income_ids', '[]');
    $carryIds = is_array($rawCarry) ? $rawCarry : (json_decode($rawCarry, true) ?: []);
    $openingNext = (float)post('next_opening_balance', 0);

    try {
        $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) {
            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.');

    } catch (\Throwable $e) {
        error_log('[EWG Cashflow Close] ' . $e->getMessage() . ' in ' . $e->getFile() . ':' . $e->getLine());
        apiError('Failed to close month: ' . $e->getMessage(), 500);
    }
}

// ── 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);