<?php
/**
 * ⭐ AccountingService — The Core Double-Entry Engine
 * 
 * GOLDEN RULE: No controller EVER inserts into journals/journal_entries directly.
 * ALL journal creation goes through this service.
 * 
 * This class enforces: Dr == Cr (Always)
 */

class AccountingService {

    /**
     * Prevent outgoing postings from pushing bank/cash accounts below zero.
     * This method is safe to call inside an active transaction before posting.
     */
    public static function assertSufficientBankCashBalance(PDO $db, int $accountId, float $amount): void {
        $amount = round(floatval($amount), 2);
        if ($amount <= 0) {
            return;
        }

        $stmt = $db->prepare("
            SELECT id, name, type
            FROM accounts
            WHERE id = ?
            FOR UPDATE
        ");
        $stmt->execute([$accountId]);
        $account = $stmt->fetch();

        if (!$account) {
            throw new Exception('Account not found.', 404);
        }

        if (($account['type'] ?? '') !== 'bank_cash') {
            return;
        }

        $currentBalance = self::getBalance($db, $accountId);
        if (($currentBalance - $amount) < -0.00001) {
            $available = number_format($currentBalance, 2);
            $required = number_format($amount, 2);
            throw new Exception("ناکافی بیلنس: {$account['name']} میں دستیاب رقم {$available} ہے جبکہ مطلوبہ رقم {$required} ہے۔", 422);
        }
    }

    /**
     * Post a journal with strict double-entry validation.
     * 
     * @param PDO $db Database connection (must be inside an active transaction)
     * @param array $header [
     *   'date'        => '2026-04-01',
     *   'source_type' => 'purchase|sale|cash_bank|expense|opening|settlement',
     *   'source_id'   => 123,
     *   'narration'   => 'Purchase Voucher خ-1004',
     *   'created_by'  => 1
     * ]
     * @param array $entries [
     *   ['account_id' => 1, 'debit' => 50000, 'credit' => 0, 'narration' => 'Dr note'],
     *   ['account_id' => 2, 'debit' => 0, 'credit' => 50000, 'narration' => 'Cr note'],
     * ]
     * @return int The created journal ID
     * @throws Exception if Dr != Cr or validation fails
     */
    public static function postJournal(PDO $db, array $header, array $entries): int {
        // ===== VALIDATION =====
        
        if (empty($entries)) {
            throw new Exception("ACCOUNTING ERROR: No entries provided.", 422);
        }

        // Calculate totals
        $totalDebit = 0;
        $totalCredit = 0;

        foreach ($entries as $entry) {
            if (!isset($entry['account_id']) || empty($entry['account_id'])) {
                throw new Exception("ACCOUNTING ERROR: Missing account_id in journal entry.", 422);
            }

            $dr = floatval($entry['debit'] ?? 0);
            $cr = floatval($entry['credit'] ?? 0);

            if ($dr < 0 || $cr < 0) {
                throw new Exception("ACCOUNTING ERROR: Negative amounts not allowed.", 422);
            }

            if ($dr > 0 && $cr > 0) {
                throw new Exception("ACCOUNTING ERROR: An entry cannot have both debit and credit.", 422);
            }

            if ($dr == 0 && $cr == 0) {
                continue; // Skip zero entries
            }

            $totalDebit += $dr;
            $totalCredit += $cr;
        }

        // THE GOLDEN CHECK: Dr must equal Cr
        if (abs($totalDebit - $totalCredit) > 0.05) {
            throw new Exception(
                "ACCOUNTING ERROR: Debit ({$totalDebit}) != Credit ({$totalCredit}). " .
                "Difference: " . abs($totalDebit - $totalCredit),
                422
            );
        }

        // ===== INSERT JOURNAL HEADER =====
        
        $uuid = $header['uuid'] ?? UUID::generate();
        
        $stmt = $db->prepare("
            INSERT INTO journals (uuid, journal_date, source_type, source_id, narration, total_amount, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");

        $stmt->execute([
            $uuid,
            $header['date'],
            $header['source_type'],
            $header['source_id'] ?? null,
            $header['narration'] ?? null,
            $totalDebit, // Total Dr or Cr (they're equal)
            $header['created_by'] ?? null
        ]);

        $journalId = (int) $db->lastInsertId();

        // ===== INSERT JOURNAL ENTRIES =====
        
        $stmtEntry = $db->prepare("
            INSERT INTO journal_entries (journal_id, account_id, debit, credit, narration)
            VALUES (?, ?, ?, ?, ?)
        ");

        foreach ($entries as $entry) {
            $dr = floatval($entry['debit'] ?? 0);
            $cr = floatval($entry['credit'] ?? 0);

            // Skip zero entries
            if ($dr == 0 && $cr == 0) continue;

            $stmtEntry->execute([
                $journalId,
                $entry['account_id'],
                $dr,
                $cr,
                $entry['narration'] ?? null
            ]);
        }

        return $journalId;
    }

    /**
     * Reverse an existing journal (Contra-Entry).
     * Creates a NEW journal with swapped Dr/Cr.
     * Marks the original journal as is_reversed = 1.
     * 
     * @param PDO $db Must be inside an active transaction
     * @param int $journalId The original journal to reverse
     * @param int $userId Who is performing the reversal
     * @return int The NEW reversal journal ID
     */
    public static function reverseJournal(PDO $db, int $journalId, int $userId): int {
        // 1. Fetch original journal
        $stmt = $db->prepare("SELECT * FROM journals WHERE id = ? FOR UPDATE");
        $stmt->execute([$journalId]);
        $journal = $stmt->fetch();

        if (!$journal) {
            throw new Exception("Journal #{$journalId} not found.", 404);
        }

        if ($journal['is_reversed']) {
            throw new Exception("Journal #{$journalId} is already reversed.", 409);
        }

        // 2. Fetch original entries
        $stmt = $db->prepare("SELECT * FROM journal_entries WHERE journal_id = ?");
        $stmt->execute([$journalId]);
        $originalEntries = $stmt->fetchAll();

        // 3. Build reversed entries (swap Dr and Cr)
        $reversedEntries = [];
        foreach ($originalEntries as $entry) {
            $reversedEntries[] = [
                'account_id' => $entry['account_id'],
                'debit'      => $entry['credit'],  // Swap!
                'credit'     => $entry['debit'],    // Swap!
                'narration'  => 'REVERSAL: ' . ($entry['narration'] ?? '')
            ];
        }

        // 4. Post the reversal journal
        // FIX: Use original journal date, NOT today's date (prevents reversal on wrong date)
        $reversalJournalId = self::postJournal($db, [
            'date'        => $journal['journal_date'],  // FIXED: was date('Y-m-d')
            'source_type' => $journal['source_type'],
            'source_id'   => $journal['source_id'],
            'narration'   => 'REVERSAL of Journal #' . $journalId . ' — ' . ($journal['narration'] ?? ''),
            'created_by'  => $userId
        ], $reversedEntries);

        // 5. Mark original as reversed
        $stmt = $db->prepare("UPDATE journals SET is_reversed = 1, reversed_by_id = ? WHERE id = ?");
        $stmt->execute([$reversalJournalId, $journalId]);

        return $reversalJournalId;
    }

    /**
     * Get ledger entries for an account with running balance.
     * Uses SQL window functions for efficient calculation.
     * 
     * @param PDO $db
     * @param int $accountId
     * @param string $fromDate
     * @param string $toDate
     * @param int $page (1-indexed)
     * @param int $limit
     * @param object|null $user
     * @return array ['account' => [...], 'ledger' => [...], 'pagination' => [...]]
     */
    public static function getLedger(PDO $db, int $accountId, string $fromDate, string $toDate, int $page = 1, int $limit = 100, ?object $user = null): array {
        
        // OPTION A: Always show clean ledger for all users (reversed entries excluded)
        $reversalFilter = " AND j.is_reversed = 0 AND j.narration NOT LIKE 'REVERSAL%'";



        // 1. Get account info
        $stmt = $db->prepare("SELECT * FROM accounts WHERE id = ?");
        $stmt->execute([$accountId]);
        $account = $stmt->fetch();

        if (!$account) {
            throw new Exception("Account not found.", 404);
        }

        // 2. Get total count for pagination
        $countStmt = $db->prepare("
            SELECT COUNT(*) as total
            FROM journal_entries je
            JOIN journals j ON je.journal_id = j.id
            WHERE je.account_id = ?
              AND j.journal_date BETWEEN ? AND ?
              {$reversalFilter}
        ");
        $countStmt->execute([$accountId, $fromDate, $toDate]);
        $total = (int) $countStmt->fetch()['total'];

        // 3. Get opening balance (sum of all entries BEFORE fromDate)
        $openingStmt = $db->prepare("
            SELECT 
                COALESCE(SUM(je.debit), 0) as total_dr,
                COALESCE(SUM(je.credit), 0) as total_cr
            FROM journal_entries je
            JOIN journals j ON je.journal_id = j.id
            WHERE je.account_id = ?
              AND j.journal_date < ?
              {$reversalFilter}
        ");
        $openingStmt->execute([$accountId, $fromDate]);
        $opening = $openingStmt->fetch();
        
        $openingBalance = floatval($account['opening_balance']);
        if ($account['balance_type'] === 'Cr') {
            $openingBalance = -$openingBalance; // Cr balances are negative in running calc
        }
        $openingBalance += (floatval($opening['total_dr']) - floatval($opening['total_cr']));

        // 4. Get ledger entries with running balance
        $offset = ($page - 1) * $limit;

        // PAGINATION FIX: Page 2+ must add previous pages balance to opening balance
        if ($offset > 0) {
            $prevStmt = $db->prepare(`
                SELECT COALESCE(SUM(sub.debit),0) as prev_dr, COALESCE(SUM(sub.credit),0) as prev_cr
                FROM (SELECT je.debit, je.credit FROM journal_entries je
                      JOIN journals j ON je.journal_id = j.id
                      WHERE je.account_id = ? AND j.journal_date BETWEEN ? AND ?
                      AND j.is_reversed = 0 AND j.narration NOT LIKE 'REVERSAL%'
                      ORDER BY j.journal_date ASC, j.id ASC LIMIT ?) as sub
            `);
            $prevStmt->execute([$accountId, $fromDate, $toDate, $offset]);
            $prev = $prevStmt->fetch();
            if ($prev) {
                $openingBalance += (floatval($prev['prev_dr']) - floatval($prev['prev_cr']));
            }
        }

        $stmt = $db->prepare("
            SELECT 
                je.id,
                je.journal_id,
                je.debit,
                je.credit,
                je.narration,
                j.journal_date,
                j.source_type,
                j.narration AS header_narration,
                j.is_reversed,
                j.source_id
            FROM journal_entries je
            JOIN journals j ON je.journal_id = j.id
            WHERE je.account_id = ?
              AND j.journal_date BETWEEN ? AND ?
              {$reversalFilter}
            ORDER BY j.journal_date ASC, j.id ASC
            LIMIT ? OFFSET ?
        ");
        $stmt->execute([$accountId, $fromDate, $toDate, $limit, $offset]);
        $entries = $stmt->fetchAll();

        // Calculate running balance per row
        $runningBalance = $openingBalance;
        foreach ($entries as &$entry) {
            $runningBalance += (floatval($entry['debit']) - floatval($entry['credit']));
            $entry['running_balance'] = round($runningBalance, 2);
        }

        return [
            'account' => $account,
            'opening_balance' => round($openingBalance, 2),
            'ledger' => $entries,
            'pagination' => [
                'total'    => $total,
                'page'     => $page,
                'limit'    => $limit,
                'pages'    => ceil($total / $limit)
            ]
        ];
    }

    /**
     * Get current balance of an account.
     */
    public static function getBalance(PDO $db, int $accountId, ?object $user = null): float {
        $stmt = $db->prepare("
            SELECT 
                a.opening_balance, a.balance_type,
                COALESCE(SUM(je.debit), 0) as total_dr,
                COALESCE(SUM(je.credit), 0) as total_cr
            FROM accounts a
            LEFT JOIN journal_entries je ON je.account_id = a.id
            LEFT JOIN journals j ON je.journal_id = j.id
            WHERE a.id = ?
              AND (
                    je.id IS NULL
                    OR (
                        j.is_reversed = 0
                        AND j.narration NOT LIKE 'REVERSAL%'
                    )
              )
            GROUP BY a.id
        ");
        $stmt->execute([$accountId]);
        $row = $stmt->fetch();

        if (!$row) return 0;

        $opening = floatval($row['opening_balance']);
        if ($row['balance_type'] === 'Cr') $opening = -$opening;

        return round($opening + floatval($row['total_dr']) - floatval($row['total_cr']), 2);
    }
}
