<?php
class SaleController extends Controller
{
    public function index(): void
    {
        Auth::requirePermission('sales.view');
        $from      = $this->get('from',    date('Y-m-d'));
        $to        = $this->get('to',      date('Y-m-d'));
        $status    = $this->get('status',  'completed');
        $pmtStatus = $this->get('pmt',     'all');
        $method    = $this->get('method',  'all');
        $search    = trim($this->get('search', ''));
        $cashierId = (int) $this->get('cashier', 0);

        $where  = ["s.sale_date BETWEEN ? AND ?"];
        $params = [$from . ' 00:00:00', $to . ' 23:59:59'];

        if ($status !== 'all') {
            $where[]  = 's.status = ?';
            $params[] = $status;
        }
        if ($pmtStatus !== 'all') {
            $where[]  = 's.payment_status = ?';
            $params[] = $pmtStatus;
        }
        if ($method !== 'all') {
            $where[]  = "EXISTS (SELECT 1 FROM sale_payments sp WHERE sp.sale_id=s.id AND sp.payment_method=?)";
            $params[] = $method;
        }
        if ($search) {
            $where[]  = '(s.invoice_no LIKE ? OR c.customer_name LIKE ? OR c.phone LIKE ?)';
            $params[] = "%$search%";
            $params[] = "%$search%";
            $params[] = "%$search%";
        }
        if ($cashierId) {
            $where[]  = 's.created_by = ?';
            $params[] = $cashierId;
        }

        $sql = "SELECT s.*,
                       COALESCE(c.customer_name,'Walk-in') AS customer_name,
                       COALESCE(c.phone, s.customer_phone) AS cust_phone,
                       u.name  AS cashier_name
                FROM sales s
                LEFT JOIN customers c ON c.id = s.customer_id
                LEFT JOIN users     u ON u.id = s.created_by
                WHERE " . implode(' AND ', $where) . "
                ORDER BY s.sale_date DESC, s.id DESC";

        $pagination = Database::paginate($sql, $params, max(1, (int)$this->get('page', 1)));

        // Summary for filtered period
        // Use LEAST(paid, total) so change-back overpayments don't inflate "collected"
        // Gross profit via subquery to avoid multiplication by item count on JOIN
        $summary = Database::fetchOne(
            "SELECT COUNT(*)                                           AS count,
                    COALESCE(SUM(s.total),0)                          AS total,
                    COALESCE(SUM(LEAST(s.paid, s.total)),0)           AS paid,
                    COALESCE(SUM(s.balance),0)                        AS balance,
                    COALESCE(SUM(gp.sale_profit),0)                   AS gross_profit
             FROM sales s
             LEFT JOIN customers c ON c.id = s.customer_id
             LEFT JOIN (
                 SELECT sale_id, SUM(quantity*(selling_price-cost_price)) AS sale_profit
                 FROM sale_items GROUP BY sale_id
             ) gp ON gp.sale_id = s.id
             WHERE " . implode(' AND ', $where),
            $params
        );

        // Get cheque info for each sale on this page
        $saleIds  = array_column($pagination['data'], 'id');
        $chequeMap = [];
        if (!empty($saleIds)) {
            $ph = implode(',', array_fill(0, count($saleIds), '?'));

            // First: check sale_payments for cheque method (definitive — always exists)
            $chqPayments = Database::fetchAll(
                "SELECT sp.sale_id, sp.amount, sp.reference
                 FROM sale_payments sp
                 WHERE sp.sale_id IN ($ph) AND sp.payment_method = 'cheque'
                 ORDER BY sp.id ASC",
                $saleIds
            );
            foreach ($chqPayments as $cp) {
                $chequeMap[$cp['sale_id']] = [
                    'has_cheque'    => true,
                    'cheque_status' => 'paid',   // default = counted as paid
                    'cheque_no'     => '',
                    'amount'        => $cp['amount'],
                ];
                // Parse cheque number from reference
                foreach (array_map('trim', explode('|', $cp['reference'] ?? '')) as $part) {
                    if (str_starts_with($part, 'ChqNo:')) {
                        $chequeMap[$cp['sale_id']]['cheque_no'] = substr($part, 6);
                    }
                }
            }

            // Then: overlay actual cheque_tracking status (pending/cleared/bounced)
            $ctRows = Database::fetchAll(
                "SELECT ct.sale_id, ct.status, ct.cheque_no, ct.amount
                 FROM cheque_tracking ct
                 WHERE ct.sale_id IN ($ph) AND ct.transaction_type = 'sale'
                 ORDER BY ct.id ASC",
                $saleIds
            );
            foreach ($ctRows as $ct) {
                if (isset($chequeMap[$ct['sale_id']])) {
                    $chequeMap[$ct['sale_id']]['cheque_status'] = $ct['status'];
                    if (!empty($ct['cheque_no']))
                        $chequeMap[$ct['sale_id']]['cheque_no'] = $ct['cheque_no'];
                } else {
                    $chequeMap[$ct['sale_id']] = [
                        'has_cheque'    => true,
                        'cheque_status' => $ct['status'],
                        'cheque_no'     => $ct['cheque_no'],
                        'amount'        => $ct['amount'],
                    ];
                }
            }
        }

        // Pending cheques count for alert badge
        $pendingCheques = (int) Database::fetchColumn(
            "SELECT COUNT(*) FROM cheque_tracking WHERE status = 'pending' AND transaction_type = 'sale'"
        );

        // Payment methods per sale (may have multiple)
        $methodMap = [];
        if (!empty($saleIds)) {
            $ph = implode(',', array_fill(0, count($saleIds), '?'));
            $methods = Database::fetchAll(
                "SELECT sale_id, GROUP_CONCAT(DISTINCT payment_method ORDER BY id SEPARATOR ',') AS methods
                 FROM sale_payments WHERE sale_id IN ($ph) GROUP BY sale_id",
                $saleIds
            );
            foreach ($methods as $m) {
                $methodMap[$m['sale_id']] = explode(',', $m['methods']);
            }
        }

        // Cashier list for filter dropdown
        $cashiers = Database::fetchAll(
            "SELECT DISTINCT u.id, u.name FROM users u
             JOIN sales s ON s.created_by = u.id
             WHERE DATE(s.sale_date) >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
             ORDER BY u.name"
        );

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

    public function show(string $id): void
    {
        Auth::requirePermission('sales.view');

        // Accept both numeric ID and invoice number (e.g. INV-20260523-0008)
        $field = is_numeric($id) ? 's.id' : 's.invoice_no';

        $sale = Database::fetchOne(
            "SELECT s.*,
                    COALESCE(c.customer_name,'Walk-in') AS customer_name,
                    COALESCE(c.phone, s.customer_phone) AS cust_phone,
                    u.name  AS cashier_name
             FROM sales s
             LEFT JOIN customers c ON c.id = s.customer_id
             LEFT JOIN users     u ON u.id = s.created_by
             WHERE {$field} = ?",
            [$id]
        );

        if (!$sale) {
            http_response_code(404);
            require APP_PATH . '/views/errors/404.php';
            return;
        }
        $items    = Database::fetchAll('SELECT si.*, p.product_name, p.product_code FROM sale_items si JOIN products p ON p.id=si.product_id WHERE si.sale_id=?', [(int)$id]);
        $payments = Database::fetchAll('SELECT * FROM sale_payments WHERE sale_id=?', [(int)$id]);
        $returns  = Database::fetchAll('SELECT * FROM sales_returns WHERE sale_id=?', [(int)$id]);

        $pageTitle = 'Sale '.$sale['invoice_no'];
        $breadcrumb = [['label'=>'Sales','url'=>APP_URL.'/sales'],['label'=>$sale['invoice_no']]];
        ob_start(); require APP_PATH.'/views/sales/view.php'; $content = ob_get_clean();
        require APP_PATH.'/views/layouts/main.php';
    }

    public function cancel(string $id): void
    {
        Auth::requirePermission('sales.create');
        Auth::requireCsrf();

        $field = is_numeric($id) ? 'id' : 'invoice_no';
        $sale  = Database::fetchOne(
            "SELECT * FROM sales WHERE {$field} = ? AND status = 'completed'",
            [$id]
        );
        if (!$sale) { $this->flash('error','Sale not found or cannot be cancelled.'); $this->back(); return; }

        Database::beginTransaction();
        try {
            $items = Database::fetchAll('SELECT * FROM sale_items WHERE sale_id=?', [(int)$id]);
            foreach ($items as $item) {
                $ns = (float)Database::fetchColumn('SELECT current_stock FROM products WHERE id=?', [$item['product_id']]) + $item['quantity'];
                Database::execute('UPDATE products SET current_stock=current_stock+? WHERE id=?', [$item['quantity'], $item['product_id']]);
                Database::execute('INSERT INTO stock_movements (product_id,movement_type,quantity_in,stock_after,reference_no,note,created_by) VALUES (?,?,?,?,?,?,?)',
                    [$item['product_id'],'sale_return',$item['quantity'],$ns,$sale['invoice_no'],'Sale cancelled',Auth::id()]);
            }
            Database::execute("UPDATE sales SET status='cancelled' WHERE id=?", [(int)$id]);
            if ($sale['balance'] > 0 && $sale['customer_id']) {
                Database::execute('UPDATE customers SET current_balance=current_balance-? WHERE id=?',
                    [$sale['balance'], $sale['customer_id']]);
            }
            Database::commit();
            Auth::logAudit(Auth::id(),'sale_cancel','sales',"Cancelled sale {$sale['invoice_no']}");
            redirectWith(APP_URL.'/sales','success','Sale cancelled and stock restored.');
        } catch (Exception $e) {
            Database::rollback();
            redirectWith(APP_URL.'/sales/view/'.$id,'error',$e->getMessage());
        }
    }

    public function paymentForm(string $id): void
    {
        Auth::requirePermission('sales.create');

        $sale = Database::fetchOne(
            "SELECT s.*,
                    COALESCE(c.customer_name,'Walk-in') AS customer_name,
                    COALESCE(c.phone, s.customer_phone) AS cust_phone
             FROM sales s
             LEFT JOIN customers c ON c.id = s.customer_id
             WHERE s.id = ? AND s.status = 'completed'",
            [(int)$id]
        );

        if (!$sale) {
            redirectWith(APP_URL . '/sales', 'error', 'Sale not found.');
            return;
        }

        if ((float)$sale['balance'] <= 0) {
            redirectWith(APP_URL . '/sales/view/' . $id,
                'info', 'This sale is already fully paid.');
            return;
        }

        $payments = Database::fetchAll(
            'SELECT * FROM sale_payments WHERE sale_id = ? ORDER BY id DESC',
            [(int)$id]
        );

        $pageTitle  = 'Record Payment — ' . $sale['invoice_no'];
        $breadcrumb = [
            ['label' => 'Sales', 'url' => APP_URL . '/sales'],
            ['label' => e($sale['invoice_no']), 'url' => APP_URL . '/sales/view/' . $id],
            ['label' => 'Record Payment'],
        ];
        ob_start();
        require APP_PATH . '/views/sales/payment.php';
        $content = ob_get_clean();
        require APP_PATH . '/views/layouts/main.php';
    }

    public function recordPayment(string $id): void
    {
        Auth::requirePermission('sales.create');
        Auth::requireCsrf();

        $sale = Database::fetchOne(
            "SELECT * FROM sales WHERE id = ? AND status = 'completed'",
            [(int)$id]
        );
        if (!$sale) {
            redirectWith(APP_URL.'/sales/view/'.$id,'error','Sale not found or cannot be updated.');
            return;
        }

        $amount = (float) $this->post('amount', 0);
        $method = $this->post('payment_method', 'cash');
        $ref    = trim($this->post('reference', ''));
        $note   = trim($this->post('note', ''));
        $date   = $this->post('payment_date', date('Y-m-d'));

        if ($amount <= 0) {
            redirectWith(APP_URL.'/sales/view/'.$id,'error','Payment amount must be greater than zero.');
            return;
        }

        // Cap at outstanding balance
        $amount = min($amount, (float)$sale['balance']);

        Database::beginTransaction();
        try {
            // 1. Record payment in sale_payments
            Database::insert(
                'INSERT INTO sale_payments (sale_id, payment_method, amount, reference, note)
                 VALUES (?, ?, ?, ?, ?)',
                [(int)$id, $method, $amount, $ref ?: null, $note ?: null]
            );

            // 2. Update sale paid / balance / status
            $newPaid    = round((float)$sale['paid']    + $amount, 2);
            $newBalance = round((float)$sale['balance'] - $amount, 2);
            $newStatus  = $newBalance <= 0 ? 'paid' : 'partial';

            Database::execute(
                'UPDATE sales SET paid = ?, balance = ?, payment_status = ? WHERE id = ?',
                [$newPaid, max(0, $newBalance), $newStatus, (int)$id]
            );

            // 3. Reduce customer outstanding balance (only for registered customers)
            if (!empty($sale['customer_id'])) {
                Database::execute(
                    'UPDATE customers SET current_balance = GREATEST(0, current_balance - ?) WHERE id = ?',
                    [$amount, (int)$sale['customer_id']]
                );

                // 4. Record in customer_payments for history (registered customers only)
                Database::insert(
                    'INSERT INTO customer_payments
                     (customer_id, sale_id, amount, payment_date, payment_method, reference, note, created_by)
                     VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
                    [(int)$sale['customer_id'], (int)$id, $amount, $date,
                     $method, $ref ?: null, $note ?: null, Auth::id()]
                );
            }

            Database::commit();

            Auth::logAudit(Auth::id(), 'sale_payment', 'sales',
                "Payment {$amount} for sale {$sale['invoice_no']}");

            redirectWith(
                APP_URL . '/sales/view/' . $id,
                'success',
                'Payment of ' . currency($amount) . ' recorded successfully.'
            );

        } catch (Exception $e) {
            Database::rollback();
            redirectWith(APP_URL.'/sales/view/'.$id,'error','Payment failed: '.$e->getMessage());
        }
    }
}