<?php
/**
 * Accounting Helper Functions
 * Double-entry bookkeeping engine
 */

// ── Generate next journal entry number ──────────────────────
function nextJournalNo() {
    global $pdo;
    $last = $pdo->query("SELECT MAX(CAST(SUBSTRING_INDEX(entry_no,'-',-1) AS UNSIGNED)) FROM journal_entries")->fetchColumn() ?: 0;
    return 'JE-' . date('Ym') . '-' . str_pad($last + 1, 4, '0', STR_PAD_LEFT);
}

// ── Create a double-entry journal ────────────────────────────
// $lines = [['account_id'=>X,'debit'=>Y,'credit'=>Z,'narration'=>'...'], ...]
function createJournalEntry($date, $type, $narration, $lines, $refType=null, $refId=null, $userId=null) {
    global $pdo;

    $totalDebit  = array_sum(array_column($lines, 'debit'));
    $totalCredit = array_sum(array_column($lines, 'credit'));

    if (abs($totalDebit - $totalCredit) > 0.01) {
        throw new Exception("Journal not balanced: Debit=$totalDebit Credit=$totalCredit");
    }

    $entryNo = nextJournalNo();
    $pdo->prepare("INSERT INTO journal_entries (entry_no,entry_date,entry_type,reference_type,reference_id,narration,total_debit,total_credit,created_by)
                   VALUES (?,?,?,?,?,?,?,?,?)")
        ->execute([$entryNo, $date, $type, $refType, $refId, $narration, $totalDebit, $totalCredit, $userId]);
    $journalId = $pdo->lastInsertId();

    $ins = $pdo->prepare("INSERT INTO journal_lines (journal_id,account_id,debit,credit,narration) VALUES (?,?,?,?,?)");
    foreach ($lines as $line) {
        $ins->execute([$journalId, $line['account_id'], $line['debit'] ?? 0, $line['credit'] ?? 0, $line['narration'] ?? null]);
        // Update account balance: debit increases asset/expense, credit increases liability/income/equity
        updateAccountBalance($line['account_id'], $line['debit'] ?? 0, $line['credit'] ?? 0);
    }
    return $journalId;
}

// ── Update running account balance ───────────────────────────
function updateAccountBalance($accountId, $debit, $credit) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT account_type FROM accounts WHERE id=?");
    $stmt->execute([$accountId]);
    $acc = $stmt->fetch();
    if (!$acc) return;

    // Normal balance: Asset/Expense = Debit side. Liability/Equity/Income = Credit side.
    $change = in_array($acc['account_type'], ['asset','expense'])
        ? ($debit - $credit)
        : ($credit - $debit);

    $pdo->prepare("UPDATE accounts SET current_balance = current_balance + ? WHERE id=?")
        ->execute([$change, $accountId]);
}

// ── Get account balance ───────────────────────────────────────
function getAccountBalance($accountId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT current_balance, account_type FROM accounts WHERE id=?");
    $stmt->execute([$accountId]);
    return $stmt->fetch();
}

// ── Auto-journal: Sale Invoice payment received ───────────────
function journalSalePayment($invoiceId, $amount, $bankAccountId, $date, $userId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT i.*, c.customer_name FROM invoices i JOIN sale_vendors c ON c.id=i.customer_id WHERE i.id=?");
    $stmt->execute([$invoiceId]);
    $inv = $stmt->fetch();
    if (!$inv) return null;

    // DR Bank Account / Cash    CR Accounts Receivable
    $arAccount = $pdo->query("SELECT id FROM accounts WHERE account_code='1100' LIMIT 1")->fetchColumn();
    return createJournalEntry($date, 'receipt',
        "Payment received from {$inv['customer_name']} for Invoice {$inv['invoice_no']}",
        [
            ['account_id' => $bankAccountId, 'debit'  => $amount, 'credit' => 0],
            ['account_id' => $arAccount,     'debit'  => 0,       'credit' => $amount],
        ],
        'invoice', $invoiceId, $userId
    );
}

// ── Auto-journal: Purchase payment made ──────────────────────
function journalPurchasePayment($purchaseId, $amount, $bankAccountId, $date, $userId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT p.*, v.vendor_name FROM purchases p JOIN purchase_vendors v ON v.id=p.vendor_id WHERE p.id=?");
    $stmt->execute([$purchaseId]);
    $p = $stmt->fetch();
    if (!$p) return null;

    // DR Accounts Payable    CR Bank Account
    $apAccount = $pdo->query("SELECT id FROM accounts WHERE account_code='2100' LIMIT 1")->fetchColumn();
    return createJournalEntry($date, 'payment',
        "Payment to {$p['vendor_name']} for Purchase {$p['purchase_no']}",
        [
            ['account_id' => $apAccount,     'debit'  => $amount, 'credit' => 0],
            ['account_id' => $bankAccountId, 'debit'  => 0,       'credit' => $amount],
        ],
        'purchase', $purchaseId, $userId
    );
}

// ── Auto-journal: Sale Invoice created ───────────────────────
function journalSaleInvoice($invoiceId, $amount, $userId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT i.*, c.customer_name FROM invoices i JOIN sale_vendors c ON c.id=i.customer_id WHERE i.id=?");
    $stmt->execute([$invoiceId]);
    $inv = $stmt->fetch();
    if (!$inv) return null;

    // DR Accounts Receivable    CR Sales Revenue
    $arAccount  = $pdo->query("SELECT id FROM accounts WHERE account_code='1100' LIMIT 1")->fetchColumn();
    $revAccount = $pdo->query("SELECT id FROM accounts WHERE account_code='4001' LIMIT 1")->fetchColumn();
    return createJournalEntry($inv['invoice_date'], 'sale',
        "Sale to {$inv['customer_name']} - Invoice {$inv['invoice_no']}",
        [
            ['account_id' => $arAccount,  'debit'  => $amount, 'credit' => 0],
            ['account_id' => $revAccount, 'debit'  => 0,       'credit' => $amount],
        ],
        'invoice', $invoiceId, $userId
    );
}

// ── Auto-journal: Purchase created ───────────────────────────
function journalPurchaseCreated($purchaseId, $amount, $userId) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT p.*, v.vendor_name FROM purchases p JOIN purchase_vendors v ON v.id=p.vendor_id WHERE p.id=?");
    $stmt->execute([$purchaseId]);
    $p = $stmt->fetch();
    if (!$p) return null;

    // DR Stock/Inventory    CR Accounts Payable
    $stockAccount = $pdo->query("SELECT id FROM accounts WHERE account_code='1200' LIMIT 1")->fetchColumn();
    $apAccount    = $pdo->query("SELECT id FROM accounts WHERE account_code='2100' LIMIT 1")->fetchColumn();
    return createJournalEntry($p['purchase_date'], 'purchase',
        "Purchase from {$p['vendor_name']} - {$p['purchase_no']}",
        [
            ['account_id' => $stockAccount, 'debit'  => $amount, 'credit' => 0],
            ['account_id' => $apAccount,    'debit'  => 0,       'credit' => $amount],
        ],
        'purchase', $purchaseId, $userId
    );
}

// ── Trial Balance data ────────────────────────────────────────
function getTrialBalance($from = null, $to = null) {
    global $pdo;
    $sql = "SELECT a.account_code, a.account_name, a.account_type,
                   ag.group_name,
                   COALESCE(SUM(jl.debit),0) total_debit,
                   COALESCE(SUM(jl.credit),0) total_credit,
                   a.opening_balance
            FROM accounts a
            LEFT JOIN account_groups ag ON ag.id = a.group_id
            LEFT JOIN journal_lines jl ON jl.account_id = a.id
            LEFT JOIN journal_entries je ON je.id = jl.journal_id";
    $params = [];
    $where = ['a.is_active=1'];
    if ($from) { $where[] = "(je.entry_date >= ? OR je.id IS NULL)"; $params[] = $from; }
    if ($to)   { $where[] = "(je.entry_date <= ? OR je.id IS NULL)"; $params[] = $to; }
    $sql .= ' WHERE ' . implode(' AND ', $where);
    $sql .= " GROUP BY a.id ORDER BY a.account_code";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

// ── P&L data ─────────────────────────────────────────────────
function getProfitLoss($from, $to) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT a.account_code, a.account_name, a.account_type,
                                   ag.group_name, ag.id as group_id,
                                   COALESCE(SUM(jl.debit),0) total_debit,
                                   COALESCE(SUM(jl.credit),0) total_credit
                            FROM accounts a
                            LEFT JOIN account_groups ag ON ag.id=a.group_id
                            LEFT JOIN journal_lines jl ON jl.account_id=a.id
                            LEFT JOIN journal_entries je ON je.id=jl.journal_id
                                AND je.entry_date BETWEEN ? AND ?
                            WHERE a.account_type IN ('income','expense') AND a.is_active=1
                            GROUP BY a.id ORDER BY a.account_type DESC, a.account_code");
    $stmt->execute([$from, $to]);
    return $stmt->fetchAll();
}

// ── Balance Sheet data ────────────────────────────────────────
function getBalanceSheet($asOf) {
    global $pdo;
    $stmt = $pdo->prepare("SELECT a.account_code, a.account_name, a.account_type,
                                   ag.group_name, ag.group_type,
                                   a.opening_balance,
                                   COALESCE(SUM(jl.debit),0)   total_debit,
                                   COALESCE(SUM(jl.credit),0)  total_credit
                            FROM accounts a
                            LEFT JOIN account_groups ag ON ag.id=a.group_id
                            LEFT JOIN journal_lines jl ON jl.account_id=a.id
                            LEFT JOIN journal_entries je ON je.id=jl.journal_id
                                AND je.entry_date <= ?
                            WHERE a.account_type IN ('asset','liability','equity') AND a.is_active=1
                            GROUP BY a.id ORDER BY a.account_type, a.account_code");
    $stmt->execute([$asOf]);
    return $stmt->fetchAll();
}
