<?php

class Order extends Model
{
    protected string $table = 'orders';

    public function generateOrderNumber(): string
    {
        $prefix = setting('order_number_prefix', 'ORD');
        $today  = date('Ymd');
        $stmt = $this->db->prepare(
            "SELECT COUNT(*) as cnt FROM orders WHERE DATE(created_at) = CURDATE()"
        );
        $stmt->execute();
        $countToday = (int) $stmt->fetch()['cnt'] + 1;
        return sprintf('%s-%s-%04d', $prefix, $today, $countToday);
    }

    /**
     * Create an order + its line items inside a single transaction.
     * $items = [['menu_item_id'=>, 'variant_id'=>, 'name'=>, 'unit_price'=>, 'quantity'=>], ...]
     * Also deducts raw-material stock per recipe and logs stock_transactions.
     * Returns the new order id.
     */
    public function createWithItems(array $orderData, array $items): int
    {
        $this->db->beginTransaction();
        try {
            $orderData['order_number'] = $this->generateOrderNumber();
            $orderId = $this->insert($orderData);

            $itemStmt = $this->db->prepare(
                "INSERT INTO order_items (order_id, menu_item_id, variant_id, item_name_snapshot, unit_price, quantity, line_total)
                 VALUES (:order_id, :menu_item_id, :variant_id, :name, :unit_price, :quantity, :line_total)"
            );

            $recipeStmt = $this->db->prepare(
                "SELECT raw_material_id, quantity_required FROM recipes WHERE menu_item_id = :menu_item_id"
            );
            $deductStmt = $this->db->prepare(
                "UPDATE raw_materials SET current_stock = current_stock - :qty WHERE id = :id"
            );
            $stockTxnStmt = $this->db->prepare(
                "INSERT INTO stock_transactions (raw_material_id, transaction_type, quantity, reference_type, reference_id, notes, created_by)
                 VALUES (:raw_material_id, 'SALE_DEDUCT', :quantity, 'ORDER', :order_id, :notes, :created_by)"
            );

            $trackStockStmt = $this->db->prepare("SELECT track_stock FROM menu_items WHERE id = :id");

            foreach ($items as $item) {
                $lineTotal = $item['unit_price'] * $item['quantity'];
                $itemStmt->execute([
                    'order_id'     => $orderId,
                    'menu_item_id' => $item['menu_item_id'],
                    'variant_id'   => $item['variant_id'] ?: null,
                    'name'         => $item['name'],
                    'unit_price'   => $item['unit_price'],
                    'quantity'     => $item['quantity'],
                    'line_total'   => $lineTotal,
                ]);

                $trackStockStmt->execute(['id' => $item['menu_item_id']]);
                $tracksStock = (bool) ($trackStockStmt->fetch()['track_stock'] ?? false);

                if ($tracksStock) {
                    $recipeStmt->execute(['menu_item_id' => $item['menu_item_id']]);
                    foreach ($recipeStmt->fetchAll() as $recipe) {
                        $qtyToDeduct = (float) $recipe['quantity_required'] * (int) $item['quantity'];
                        $deductStmt->execute(['qty' => $qtyToDeduct, 'id' => $recipe['raw_material_id']]);
                        $stockTxnStmt->execute([
                            'raw_material_id' => $recipe['raw_material_id'],
                            'quantity'        => -$qtyToDeduct,
                            'order_id'        => $orderId,
                            'notes'           => "Sold: {$item['name']} x{$item['quantity']}",
                            'created_by'      => $orderData['created_by'] ?? null,
                        ]);
                    }
                }
            }

            $this->logStatus($orderId, $orderData['status'] ?? 'PENDING', $orderData['created_by'] ?? null);

            $this->db->commit();
            return $orderId;
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function logStatus(int $orderId, string $status, ?int $userId): void
    {
        $stmt = $this->db->prepare(
            "INSERT INTO order_status_history (order_id, status, changed_by) VALUES (:order_id, :status, :changed_by)"
        );
        $stmt->execute(['order_id' => $orderId, 'status' => $status, 'changed_by' => $userId]);
    }

    public function updateStatus(int $orderId, string $status, ?int $userId): void
    {
        $this->update($orderId, ['status' => $status]);
        $this->logStatus($orderId, $status, $userId);

        // For online-delivery orders, once the kitchen marks it READY,
        // create an UNASSIGNED delivery record for the Delivery module to pick up.
        $order = $this->find($orderId);
        if ($order && $order['order_type'] === 'ONLINE_DELIVERY' && $status === 'READY') {
            $exists = $this->db->prepare("SELECT id FROM deliveries WHERE order_id = :id");
            $exists->execute(['id' => $orderId]);
            if (!$exists->fetch()) {
                $this->db->prepare(
                    "INSERT INTO deliveries (order_id, status) VALUES (:id, 'UNASSIGNED')"
                )->execute(['id' => $orderId]);
            }
        }
    }

    /**
     * Cancel an order and restock any raw materials that were deducted for it.
     */
    public function cancelAndRestock(int $orderId, ?int $userId): void
    {
        $this->db->beginTransaction();
        try {
            $txnStmt = $this->db->prepare(
                "SELECT raw_material_id, quantity FROM stock_transactions
                 WHERE reference_type = 'ORDER' AND reference_id = :order_id AND transaction_type = 'SALE_DEDUCT'"
            );
            $txnStmt->execute(['order_id' => $orderId]);
            $deductions = $txnStmt->fetchAll();

            $restockStmt = $this->db->prepare(
                "UPDATE raw_materials SET current_stock = current_stock - :qty WHERE id = :id"
            ); // quantity stored is negative, so subtracting a negative restocks it
            $logStmt = $this->db->prepare(
                "INSERT INTO stock_transactions (raw_material_id, transaction_type, quantity, reference_type, reference_id, notes, created_by)
                 VALUES (:raw_material_id, 'ADJUSTMENT', :quantity, 'ORDER', :order_id, 'Order cancelled - stock restored', :created_by)"
            );

            foreach ($deductions as $d) {
                $restockStmt->execute(['qty' => (float) $d['quantity'], 'id' => $d['raw_material_id']]);
                $logStmt->execute([
                    'raw_material_id' => $d['raw_material_id'],
                    'quantity'        => -$d['quantity'], // positive back on the ledger
                    'order_id'        => $orderId,
                    'created_by'      => $userId,
                ]);
            }

            $this->update($orderId, ['status' => 'CANCELLED']);
            $this->logStatus($orderId, 'CANCELLED', $userId);

            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function findWithDetails(int $orderId): array|false
    {
        $stmt = $this->db->prepare(
            "SELECT o.*, c.full_name as customer_name, c.phone as customer_phone,
                    dz.zone_name, u.full_name as created_by_name
             FROM orders o
             LEFT JOIN customers c ON c.id = o.customer_id
             LEFT JOIN delivery_zones dz ON dz.id = o.delivery_zone_id
             LEFT JOIN users u ON u.id = o.created_by
             WHERE o.id = :id LIMIT 1"
        );
        $stmt->execute(['id' => $orderId]);
        $order = $stmt->fetch();
        if (!$order) {
            return false;
        }

        $itemStmt = $this->db->prepare("SELECT * FROM order_items WHERE order_id = :id");
        $itemStmt->execute(['id' => $orderId]);
        $order['items'] = $itemStmt->fetchAll();

        $historyStmt = $this->db->prepare(
            "SELECT h.*, u.full_name as changed_by_name FROM order_status_history h
             LEFT JOIN users u ON u.id = h.changed_by
             WHERE h.order_id = :id ORDER BY h.changed_at ASC"
        );
        $historyStmt->execute(['id' => $orderId]);
        $order['history'] = $historyStmt->fetchAll();

        $paymentStmt = $this->db->prepare(
            "SELECT p.*, pm.method_name FROM payments p
             JOIN payment_methods pm ON pm.id = p.payment_method_id
             WHERE p.order_id = :id ORDER BY p.paid_at DESC"
        );
        $paymentStmt->execute(['id' => $orderId]);
        $order['payments'] = $paymentStmt->fetchAll();

        $deliveryStmt = $this->db->prepare(
            "SELECT d.*, u.full_name as rider_name FROM deliveries d
             LEFT JOIN users u ON u.id = d.rider_id
             WHERE d.order_id = :id LIMIT 1"
        );
        $deliveryStmt->execute(['id' => $orderId]);
        $order['delivery'] = $deliveryStmt->fetch() ?: null;

        return $order;
    }

    public function listWithFilters(string $status = '', string $search = ''): array
    {
        $sql = "SELECT o.*, c.full_name as customer_name, c.phone as customer_phone,
                       COALESCE((SELECT SUM(p.amount) FROM payments p WHERE p.order_id = o.id AND p.status='PAID'),0) as total_paid,
                       d.status as delivery_status, u.full_name as rider_name
                FROM orders o
                LEFT JOIN customers c ON c.id = o.customer_id
                LEFT JOIN deliveries d ON d.order_id = o.id
                LEFT JOIN users u ON u.id = d.rider_id
                WHERE 1=1";
        $params = [];

        if ($status !== '' && $status !== 'ALL') {
            $sql .= " AND o.status = :status";
            $params['status'] = $status;
        }
        if ($search !== '') {
            $sql .= " AND (o.order_number LIKE :s1 OR c.phone LIKE :s2 OR c.full_name LIKE :s3)";
            $params['s1'] = "%{$search}%";
            $params['s2'] = "%{$search}%";
            $params['s3'] = "%{$search}%";
        }
        $sql .= " ORDER BY o.id DESC LIMIT 100";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function statusCounts(): array
    {
        $rows = $this->db->query("SELECT status, COUNT(*) as cnt FROM orders GROUP BY status")->fetchAll();
        $counts = ['ALL' => 0];
        foreach ($rows as $row) {
            $counts[$row['status']] = (int) $row['cnt'];
            $counts['ALL'] += (int) $row['cnt'];
        }
        return $counts;
    }

    public function todayStats(): array
    {
        $stmt = $this->db->query(
            "SELECT
                COUNT(*) as today_count,
                COALESCE(SUM(total_amount),0) as today_revenue,
                COALESCE(SUM(CASE WHEN status NOT IN ('CANCELLED') AND id NOT IN (
                    SELECT order_id FROM payments WHERE status='PAID'
                ) THEN 1 ELSE 0 END),0) as unpaid_count
             FROM orders WHERE DATE(created_at) = CURDATE() AND status != 'CANCELLED'"
        );
        return $stmt->fetch();
    }

    /**
     * Record a payment against an order and post it to the linked finance account.
     */
    public function recordPayment(int $orderId, int $paymentMethodId, float $amount, ?int $userId): void
    {
        $this->db->beginTransaction();
        try {
            $methodStmt = $this->db->prepare("SELECT * FROM payment_methods WHERE id = :id");
            $methodStmt->execute(['id' => $paymentMethodId]);
            $method = $methodStmt->fetch();
            if (!$method) {
                throw new RuntimeException('Invalid payment method');
            }

            $feeAmount = round($amount * ((float) $method['fee_percentage'] / 100), 2);

            $this->db->prepare(
                "INSERT INTO payments (order_id, payment_method_id, amount, fee_amount, status, received_by)
                 VALUES (:order_id, :method_id, :amount, :fee, 'PAID', :user_id)"
            )->execute([
                'order_id'  => $orderId,
                'method_id' => $paymentMethodId,
                'amount'    => $amount,
                'fee'       => $feeAmount,
                'user_id'   => $userId,
            ]);

            if ($method['finance_account_id']) {
                $netAmount = $amount - $feeAmount;
                $this->db->prepare(
                    "UPDATE finance_accounts SET current_balance = current_balance + :amt WHERE id = :id"
                )->execute(['amt' => $netAmount, 'id' => $method['finance_account_id']]);

                $this->db->prepare(
                    "INSERT INTO finance_transactions (finance_account_id, transaction_type, amount, category, reference_type, reference_id, description, created_by)
                     VALUES (:account_id, 'CREDIT', :amount, 'SALES', 'ORDER', :order_id, :description, :user_id)"
                )->execute([
                    'account_id'  => $method['finance_account_id'],
                    'amount'      => $netAmount,
                    'order_id'    => $orderId,
                    'description' => "Payment for order #{$orderId} via {$method['method_name']}",
                    'user_id'     => $userId,
                ]);
            }

            $this->db->commit();
        } catch (\Throwable $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function totalPaid(int $orderId): float
    {
        $stmt = $this->db->prepare(
            "SELECT COALESCE(SUM(amount),0) as total FROM payments WHERE order_id = :id AND status = 'PAID'"
        );
        $stmt->execute(['id' => $orderId]);
        return (float) $stmt->fetch()['total'];
    }

    public function recentOrders(int $limit = 8): array
    {
        $stmt = $this->db->query(
            "SELECT o.*, c.full_name as customer_name, c.phone as customer_phone
             FROM orders o LEFT JOIN customers c ON c.id = o.customer_id
             ORDER BY o.id DESC LIMIT " . (int) $limit
        );
        return $stmt->fetchAll();
    }

    public function findByCustomer(int $customerId, int $limit = 30): array
    {
        $stmt = $this->db->prepare(
            "SELECT o.*,
                    (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) as item_count
             FROM orders o
             WHERE o.customer_id = :customer_id
             ORDER BY o.id DESC LIMIT " . (int) $limit
        );
        $stmt->execute(['customer_id' => $customerId]);
        return $stmt->fetchAll();
    }
}
