<?php
// ============================================================
//  MJPOS – ChequesController  (Sales + Purchase Cheques)
// ============================================================

class ChequesController extends Controller
{
    public function index(): void
    {
        Auth::requirePermission('cheques.view');

        $status = $this->get('status', 'pending');
        $type   = $this->get('type',   'all');
        $from   = $this->get('from',   date('Y-m-01'));
        $to     = $this->get('to',     date('Y-m-d'));
        $search = trim($this->get('search', ''));

        // Use COALESCE so cheques with NULL cheque_date still show (fall back to created_at)
        $where  = ['COALESCE(ct.cheque_date, DATE(ct.created_at)) BETWEEN ? AND ?'];
        $params = [$from, $to];

        if ($status !== 'all') { $where[] = 'ct.status = ?';           $params[] = $status; }
        if ($type   !== 'all') { $where[] = 'ct.transaction_type = ?'; $params[] = $type;   }
        if ($search) {
            $where[]  = '(ct.cheque_no LIKE ? OR ct.party_name LIKE ? OR ct.bank_name LIKE ?)';
            $params[] = "%$search%";
            $params[] = "%$search%";
            $params[] = "%$search%";
        }

        $cheques = Database::fetchAll(
            'SELECT ct.*,
                    s.invoice_no  AS sale_invoice,
                    s.customer_id AS s_customer_id,
                    p.invoice_no  AS purchase_invoice,
                    sp.reference  AS sale_pmt_ref,
                    u.name        AS created_by_name
             FROM cheque_tracking ct
             LEFT JOIN sales          s  ON s.id  = ct.sale_id
             LEFT JOIN purchases      p  ON p.id  = ct.purchase_id
             LEFT JOIN sale_payments  sp ON sp.id = ct.sale_payment_id
             LEFT JOIN users          u  ON u.id  = ct.created_by
             WHERE ' . implode(' AND ', $where) . '
             ORDER BY
               CASE ct.status WHEN \'pending\' THEN 0 WHEN \'bounced\' THEN 1 ELSE 2 END,
               COALESCE(ct.cheque_date, DATE(ct.created_at)) ASC,
               ct.id DESC',
            $params
        );

        // Parse drawer from sale_payment reference for display
        foreach ($cheques as &$c) {
            $c['drawer'] = '';
            $ref = $c['sale_pmt_ref'] ?? '';
            if ($ref) {
                foreach (array_map('trim', explode('|', $ref)) as $part) {
                    if (str_starts_with($part, 'Drawer:')) {
                        $c['drawer'] = substr($part, 7);
                        break;
                    }
                }
            }
        }
        unset($c);

        // Overall summary (all time, not filtered by date)
        $totalPending    = (float) Database::fetchColumn("SELECT COALESCE(SUM(amount),0) FROM cheque_tracking WHERE status='pending'");
        $totalCleared    = (float) Database::fetchColumn("SELECT COALESCE(SUM(amount),0) FROM cheque_tracking WHERE status='cleared'");
        $totalBounced    = (float) Database::fetchColumn("SELECT COALESCE(SUM(amount),0) FROM cheque_tracking WHERE status='bounced'");
        $countPending    = (int)   Database::fetchColumn("SELECT COUNT(*) FROM cheque_tracking WHERE status='pending'");
        $salePending     = (float) Database::fetchColumn("SELECT COALESCE(SUM(amount),0) FROM cheque_tracking WHERE status='pending' AND transaction_type='sale'");
        $purchasePending = (float) Database::fetchColumn("SELECT COALESCE(SUM(amount),0) FROM cheque_tracking WHERE status='pending' AND transaction_type='purchase'");

        // Overdue cheques (cheque_date < today AND still pending)
        $overdueCount  = (int) Database::fetchColumn(
            "SELECT COUNT(*) FROM cheque_tracking WHERE status='pending' AND cheque_date < CURDATE()"
        );
        $overdueAmount = (float) Database::fetchColumn(
            "SELECT COALESCE(SUM(amount),0) FROM cheque_tracking WHERE status='pending' AND cheque_date < CURDATE()"
        );

        $pageTitle  = 'Cheque Management';
        $breadcrumb = [['label' => 'Cheques']];
        ob_start();
        require APP_PATH . '/views/cheques/index.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function pending(): void
    {
        Auth::requirePermission('cheques.view');

        $today = date('Y-m-d');

        $allPending = Database::fetchAll(
            "SELECT ct.*,
                    s.invoice_no  AS sale_invoice,
                    p.invoice_no  AS purchase_invoice,
                    u.name        AS created_by_name
             FROM cheque_tracking ct
             LEFT JOIN sales     s ON s.id = ct.sale_id
             LEFT JOIN purchases p ON p.id = ct.purchase_id
             LEFT JOIN users     u ON u.id = ct.created_by
             WHERE ct.status = 'pending'
             ORDER BY COALESCE(ct.cheque_date, ct.created_at) ASC",
            []
        );

        // Group by cheque date
        $overdue  = array_values(array_filter($allPending,
            fn($c) => !empty($c['cheque_date']) && $c['cheque_date'] < $today));
        $dueToday = array_values(array_filter($allPending,
            fn($c) => !empty($c['cheque_date']) && $c['cheque_date'] === $today));
        $upcoming = array_values(array_filter($allPending,
            fn($c) => !empty($c['cheque_date']) && $c['cheque_date'] > $today));
        $noDate   = array_values(array_filter($allPending,
            fn($c) => empty($c['cheque_date'])));

        $totalPending  = (float) array_sum(array_column($allPending, 'amount'));
        $totalOverdue  = (float) array_sum(array_column($overdue,    'amount'));
        $totalDueToday = (float) array_sum(array_column($dueToday,   'amount'));
        $totalUpcoming = (float) array_sum(array_column($upcoming,   'amount'));

        $pageTitle  = 'Pending Cheques';
        $breadcrumb = [
            ['label' => 'Cheques', 'url' => APP_URL . '/cheques'],
            ['label' => 'Pending'],
        ];
        ob_start();
        require APP_PATH . '/views/cheques/pending.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function updateStatus(string $id): void
    {
        Auth::requirePermission('cheques.view');
        Auth::requireCsrf();

        $newStatus   = $this->post('status', 'cleared');
        $clearedDate = $this->post('cleared_date', date('Y-m-d'));
        $note        = trim($this->post('note', ''));

        $cheque = Database::fetchOne(
            'SELECT ct.*,
                    s.customer_id,
                    s.balance  AS sale_balance,
                    s.paid     AS sale_paid,
                    s.total    AS sale_total,
                    p.supplier_id,
                    p.balance  AS purchase_balance,
                    p.paid     AS purchase_paid,
                    p.total    AS purchase_total
             FROM cheque_tracking ct
             LEFT JOIN sales     s ON s.id = ct.sale_id
             LEFT JOIN purchases p ON p.id = ct.purchase_id
             WHERE ct.id = ?',
            [(int)$id]
        );

        if (!$cheque) {
            redirectWith(APP_URL.'/cheques','error','Cheque not found.');
            return;
        }

        if ($cheque['status'] !== 'pending') {
            redirectWith(APP_URL.'/cheques','error','Only pending cheques can be updated.');
            return;
        }

        Database::beginTransaction();
        try {
            // Mark cheque status
            Database::execute(
                'UPDATE cheque_tracking
                 SET status = ?, cleared_date = ?, note = ?, updated_by = ?
                 WHERE id = ?',
                [
                    $newStatus,
                    $newStatus === 'cleared' ? $clearedDate : null,
                    $note ?: null,
                    Auth::id(),
                    (int)$id,
                ]
            );

            $amount = (float) $cheque['amount'];

            // ══════════════════════════════════════════════════
            //  SALE CHEQUE
            // ══════════════════════════════════════════════════
            if ($cheque['transaction_type'] === 'sale' && $cheque['sale_id']) {

                $saleId = (int) $cheque['sale_id'];

                // Was the cheque amount already included in sales.paid?
                // Compare sales.paid vs sum of non-cheque sale_payments
                $salePaidNow = (float) Database::fetchColumn(
                    'SELECT paid FROM sales WHERE id = ?', [$saleId]
                );
                $nonChequePaid = (float) Database::fetchColumn(
                    "SELECT COALESCE(SUM(amount),0) FROM sale_payments
                     WHERE sale_id = ? AND payment_method != 'cheque'",
                    [$saleId]
                );
                $chequeWasCounted = ($salePaidNow > $nonChequePaid + 0.01);

                if ($newStatus === 'cleared') {
                    // ── Cleared ──────────────────────────────────────
                    // Add cheque to paid, reduce balance
                    $newPaid    = round($salePaidNow + $amount, 2);
                    $newBalance = round(
                        (float) Database::fetchColumn('SELECT balance FROM sales WHERE id=?', [$saleId]) - $amount,
                        2
                    );
                    $newPmtStatus = $newBalance <= 0 ? 'paid' : 'partial';
                    Database::execute(
                        'UPDATE sales SET paid = ?, balance = ?, payment_status = ? WHERE id = ?',
                        [$newPaid, max(0, $newBalance), $newPmtStatus, $saleId]
                    );
                    // Reduce customer outstanding balance (cheque clears what they owed)
                    if (!empty($cheque['customer_id'])) {
                        Database::execute(
                            'UPDATE customers SET current_balance = GREATEST(0, current_balance - ?) WHERE id = ?',
                            [$amount, (int)$cheque['customer_id']]
                        );
                    }

                } elseif ($newStatus === 'bounced') {
                    // ── Bounced ──────────────────────────────────────
                    if ($chequeWasCounted) {
                        // Old data: cheque was counted as paid — reverse it
                        $newPaid    = round($salePaidNow - $amount, 2);
                        $newBalance = round(
                            (float) Database::fetchColumn('SELECT balance FROM sales WHERE id=?', [$saleId]) + $amount,
                            2
                        );
                        $saleTotal  = (float) Database::fetchColumn('SELECT total FROM sales WHERE id=?', [$saleId]);
                        $newPmtStatus = $newPaid <= 0 ? 'due' : 'partial';
                        Database::execute(
                            'UPDATE sales SET paid = ?, balance = ?, payment_status = ? WHERE id = ?',
                            [max(0, $newPaid), min($saleTotal, $newBalance), $newPmtStatus, $saleId]
                        );
                        // Customer now owes the bounced amount again
                        if (!empty($cheque['customer_id'])) {
                            Database::execute(
                                'UPDATE customers SET current_balance = current_balance + ? WHERE id = ?',
                                [$amount, (int)$cheque['customer_id']]
                            );
                        }
                    }
                    // else: New system — cheque was PENDING (never counted in paid).
                    // Sale balance already shows the amount as due. Nothing to update.
                }
            }

            // ══════════════════════════════════════════════════
            //  PURCHASE CHEQUE
            // ══════════════════════════════════════════════════
            if ($cheque['transaction_type'] === 'purchase' && $cheque['purchase_id']) {

                $purId = (int) $cheque['purchase_id'];

                // Was the cheque already counted in purchases.paid?
                $purPaidNow = (float) Database::fetchColumn(
                    'SELECT paid FROM purchases WHERE id = ?', [$purId]
                );
                $nonChequePurPaid = (float) Database::fetchColumn(
                    "SELECT COALESCE(SUM(amount),0) FROM supplier_payments
                     WHERE purchase_id = ? AND payment_method != 'cheque'",
                    [$purId]
                );
                $purChequeWasCounted = ($purPaidNow > $nonChequePurPaid + 0.01);

                if ($newStatus === 'cleared') {
                    // ── Cleared ──────────────────────────────────────
                    $newPaid    = round($purPaidNow + $amount, 2);
                    $newBalance = round(
                        (float) Database::fetchColumn('SELECT balance FROM purchases WHERE id=?', [$purId]) - $amount,
                        2
                    );
                    $newPmtStatus = $newBalance <= 0 ? 'paid' : 'partial';
                    Database::execute(
                        'UPDATE purchases SET paid = ?, balance = ?, payment_status = ? WHERE id = ?',
                        [$newPaid, max(0, $newBalance), $newPmtStatus, $purId]
                    );
                    if (!empty($cheque['supplier_id'])) {
                        Database::execute(
                            'UPDATE suppliers SET current_balance = GREATEST(0, current_balance - ?) WHERE id = ?',
                            [$amount, (int)$cheque['supplier_id']]
                        );
                    }

                } elseif ($newStatus === 'bounced') {
                    // ── Bounced ──────────────────────────────────────
                    if ($purChequeWasCounted) {
                        // Cheque was already recorded as paid — reverse it
                        $newPaid    = round($purPaidNow - $amount, 2);
                        $newBalance = round(
                            (float) Database::fetchColumn('SELECT balance FROM purchases WHERE id=?', [$purId]) + $amount,
                            2
                        );
                        $purTotal   = (float) Database::fetchColumn('SELECT total FROM purchases WHERE id=?', [$purId]);
                        $newPmtStatus = $newPaid <= 0 ? 'due' : 'partial';
                        Database::execute(
                            'UPDATE purchases SET paid = ?, balance = ?, payment_status = ? WHERE id = ?',
                            [max(0, $newPaid), min($purTotal, $newBalance), $newPmtStatus, $purId]
                        );
                        if (!empty($cheque['supplier_id'])) {
                            Database::execute(
                                'UPDATE suppliers SET current_balance = current_balance + ? WHERE id = ?',
                                [$amount, (int)$cheque['supplier_id']]
                            );
                        }
                    }
                    // else: cheque was pending (not counted) — nothing to reverse
                }
            }

            Database::commit();

            Auth::logAudit(Auth::id(), 'cheque_update', 'cheques',
                "Cheque #{$cheque['cheque_no']} ({$cheque['transaction_type']}) marked as {$newStatus}");

            redirectWith(APP_URL.'/cheques', 'success',
                "Cheque #{$cheque['cheque_no']} marked as " . ucfirst($newStatus) . '.');

        } catch (Exception $e) {
            Database::rollback();
            redirectWith(APP_URL.'/cheques','error','Update failed: '.$e->getMessage());
        }
    }
}