<?php
require_once __DIR__ . '/../config.php';
require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../includes/db.php';
require_once __DIR__ . '/../includes/finance_service.php';
require_login();
header('Content-Type: application/json');

$mysqli = db();
$action = $_GET['action'] ?? $_POST['action'] ?? '';

// Ensure transfer tracking columns exist
$mysqli->query("ALTER TABLE expenditures ADD COLUMN IF NOT EXISTS transferred_to_cashbook TINYINT(1) NOT NULL DEFAULT 0");
$mysqli->query("ALTER TABLE expenditures ADD COLUMN IF NOT EXISTS cashbook_transaction_id INT DEFAULT NULL");
$mysqli->query("ALTER TABLE expenditures ADD COLUMN IF NOT EXISTS cashbook_account_head VARCHAR(150) DEFAULT NULL");
$mysqli->query("ALTER TABLE expenditures ADD COLUMN IF NOT EXISTS cashbook_sub_account_head VARCHAR(150) DEFAULT NULL");

switch ($action) {

    case 'list':
        // List expenditures with optional filters
        $transferred = isset($_GET['transferred']) ? (int)$_GET['transferred'] : 0;
        $search      = trim($_GET['search'] ?? '');
        $date_from   = trim($_GET['date_from'] ?? '');
        $date_to     = trim($_GET['date_to'] ?? '');
        $cat_id      = (int)($_GET['category_id'] ?? 0);

        $where = ['e.transferred_to_cashbook = ' . $transferred];
        $params = [];
        $types  = '';

        if ($search !== '') {
            $where[] = '(e.description LIKE ? OR e.vendor LIKE ? OR e.invoice_no LIKE ?)';
            $s = '%' . $search . '%';
            $params = array_merge($params, [$s, $s, $s]);
            $types .= 'sss';
        }
        if ($date_from !== '') { $where[] = 'e.expense_date >= ?'; $params[] = $date_from; $types .= 's'; }
        if ($date_to   !== '') { $where[] = 'e.expense_date <= ?'; $params[] = $date_to;   $types .= 's'; }
        if ($cat_id > 0)       { $where[] = 'e.category_id = ?';   $params[] = $cat_id;    $types .= 'i'; }

        $sql = "SELECT e.id, e.expense_date, e.description, e.amount, e.payment_method,
                       e.vendor, e.invoice_no, e.notes, e.transferred_to_cashbook,
                       e.cashbook_transaction_id, e.cashbook_account_head, e.cashbook_sub_account_head,
                       c.name AS category_name
                FROM expenditures e
                LEFT JOIN expenditure_categories c ON c.id = e.category_id
                WHERE " . implode(' AND ', $where) . "
                ORDER BY e.expense_date DESC, e.id DESC";

        if ($params) {
            $stmt = $mysqli->prepare($sql);
            $stmt->bind_param($types, ...$params);
            $stmt->execute();
            $res = $stmt->get_result();
        } else {
            $res = $mysqli->query($sql);
        }

        $data = [];
        while ($row = $res->fetch_assoc()) $data[] = $row;
        json_response(['data' => $data, 'count' => count($data)]);
        break;

    case 'transfer':
        if (!has_role(['admin','super_admin','accountant','finance_manager']))
            json_response(['message' => 'Access denied'], 403);
        if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message' => 'Invalid CSRF'], 400);

        $ids              = $_POST['ids'] ?? [];       // array of expenditure IDs
        $bank_account_id  = (int)($_POST['bank_account_id'] ?? 0);
        $account_head     = trim($_POST['account_head'] ?? 'Purchase Expenses');
        $sub_account_head = trim($_POST['sub_account_head'] ?? '');
        $voucher_prefix   = 'EXP';

        if (empty($ids)) json_response(['message' => 'No expenditures selected'], 400);
        if (!$bank_account_id) $bank_account_id = getDefaultBankAccountId();

        $transferred = 0;
        $skipped     = 0;
        $errors      = [];

        foreach ($ids as $raw_id) {
            $exp_id = (int)$raw_id;
            if (!$exp_id) continue;

            // Fetch expenditure
            $stmt = $mysqli->prepare(
                "SELECT e.*, c.name AS category_name
                 FROM expenditures e
                 LEFT JOIN expenditure_categories c ON c.id=e.category_id
                 WHERE e.id=? AND e.transferred_to_cashbook=0"
            );
            $stmt->bind_param('i', $exp_id);
            $stmt->execute();
            $exp = $stmt->get_result()->fetch_assoc();
            $stmt->close();

            if (!$exp) { $skipped++; continue; }

            // Build narration
            $narration = trim(($exp['category_name'] ?? '') . ' - ' . $exp['description']);
            $doc_ref   = $exp['invoice_no'] ?: ('EXP-' . $exp_id);
            $pay_date  = $exp['expense_date'];

            // Generate voucher number
            $vr = $mysqli->query(
                "SELECT COUNT(*)+1 AS n FROM transactions
                 WHERE DATE(transaction_date)='" . $mysqli->real_escape_string($pay_date) . "'
                   AND transaction_type='Payment'"
            );
            $seq    = str_pad((int)($vr->fetch_assoc()['n']), 4, '0', STR_PAD_LEFT);
            $voucher= $voucher_prefix . '/' . date('Y', strtotime($pay_date)) . '/' . date('md', strtotime($pay_date)) . '/' . $seq;

            // Record as Payment in cash book
            $finSvc = new FinanceService();
            $result = $finSvc->recordExpense(
                $exp_id,
                $bank_account_id,
                (float)$exp['amount'],
                [
                    'transaction_date'    => $pay_date,
                    'voucher_number'      => $voucher,
                    'narration'           => $narration,
                    'document_reference'  => $doc_ref,
                    'account_head'        => $account_head,
                    'sub_account_head'    => $sub_account_head,
                    'mode_of_payment'     => $exp['payment_method'] ?: 'Bank Transfer',
                    'party_name'          => $exp['vendor'] ?: '',
                    'entered_by'          => $_SESSION['user']['id'] ?? null,
                ]
            );

            if (!empty($result['success'])) {
                $tx_id = $result['transaction_id'] ?? null;
                $upd = $mysqli->prepare(
                    "UPDATE expenditures SET transferred_to_cashbook=1, cashbook_transaction_id=?,
                     cashbook_account_head=?, cashbook_sub_account_head=? WHERE id=?"
                );
                $upd->bind_param('issi', $tx_id, $account_head, $sub_account_head, $exp_id);
                $upd->execute();
                $upd->close();
                $transferred++;
            } else {
                $errors[] = 'EXP #' . $exp_id . ': ' . ($result['message'] ?? 'Unknown error');
            }
        }

        json_response([
            'success'     => true,
            'transferred' => $transferred,
            'skipped'     => $skipped,
            'errors'      => $errors,
            'message'     => $transferred . ' expenditure(s) transferred to cash book.' .
                             ($skipped  ? ' ' . $skipped . ' already transferred.' : '') .
                             (!empty($errors) ? ' Errors: ' . implode('; ', $errors) : ''),
        ]);
        break;

    case 'untransfer':
        if (!has_role(['admin','super_admin'])) json_response(['message' => 'Access denied'], 403);
        if (!validate_csrf($_POST['csrf'] ?? '')) json_response(['message' => 'Invalid CSRF'], 400);
        $exp_id = (int)($_POST['id'] ?? 0);
        if (!$exp_id) json_response(['message' => 'ID required'], 400);

        $stmt = $mysqli->prepare("SELECT cashbook_transaction_id FROM expenditures WHERE id=?");
        $stmt->bind_param('i', $exp_id);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        if ($row && $row['cashbook_transaction_id']) {
            // Cancel the cash book transaction
            $tx_id = (int)$row['cashbook_transaction_id'];
            $tx = $mysqli->query("SELECT * FROM transactions WHERE id=$tx_id")->fetch_assoc();
            if ($tx) {
                // Reverse bank balance
                $bal_col = $tx['transaction_type'] === 'Payment' ? 'current_balance + ' : 'current_balance - ';
                $mysqli->query("UPDATE bank_accounts SET current_balance = current_balance + {$tx['amount']} WHERE id={$tx['bank_account_id']}");
                $mysqli->query("UPDATE transactions SET is_cancelled=1 WHERE id=$tx_id");
                $mysqli->query("DELETE FROM gl_entries WHERE transaction_id=$tx_id");
            }
        }

        $upd = $mysqli->prepare("UPDATE expenditures SET transferred_to_cashbook=0, cashbook_transaction_id=NULL WHERE id=?");
        $upd->bind_param('i', $exp_id);
        $upd->execute();
        json_response(['success' => true, 'message' => 'Transfer reversed']);
        break;

    case 'categories':
        $res = $mysqli->query("SELECT id, name FROM expenditure_categories WHERE is_active=1 ORDER BY name");
        $data = [];
        while ($row = $res->fetch_assoc()) $data[] = $row;
        json_response(['data' => $data]);
        break;

    default:
        json_response(['message' => 'Unknown action'], 400);
}
