<?php
/**
 * Order Model
 * Maps to `orders` and `order_items` tables
 */

require_once __DIR__ . '/../Core/Model.php';

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

    public function getCustomerOrders(int $customerId, ?string $status = null): array {
        $where = "o.customer_id = :customer_id AND o.deleted_at IS NULL";
        $params = [':customer_id' => $customerId];

        if ($status && $status !== 'all') {
            $where .= " AND o.order_status = :status";
            $params[':status'] = $status;
        }

        $sql = "SELECT o.*, s.store_name, s.store_slug, s.logo as store_logo,
                (SELECT COUNT(*) FROM `order_items` oi WHERE oi.order_id = o.id) as total_items,
                (SELECT oi.product_name FROM `order_items` oi WHERE oi.order_id = o.id LIMIT 1) as first_item_name,
                (SELECT oi.product_image FROM `order_items` oi WHERE oi.order_id = o.id LIMIT 1) as first_item_image
                FROM `orders` o
                JOIN `stores` s ON o.store_id = s.id
                WHERE {$where}
                ORDER BY o.created_at DESC";

        return $this->fetchAll($sql, $params);
    }

    public function findOrderByNo(string $orderNo, int $customerId): ?array {
        $sql = "SELECT o.*, s.store_name, s.store_slug, s.logo as store_logo, s.phone as store_phone
                FROM `orders` o
                JOIN `stores` s ON o.store_id = s.id
                WHERE o.order_no = :order_no AND o.customer_id = :customer_id AND o.deleted_at IS NULL
                LIMIT 1";
        return $this->fetchOne($sql, [':order_no' => $orderNo, ':customer_id' => $customerId]);
    }

    public function getOrderItems(int $orderId): array {
        $sql = "SELECT oi.*, p.slug as product_slug 
                FROM `order_items` oi
                LEFT JOIN `products` p ON oi.product_id = p.id
                WHERE oi.order_id = :order_id";
        return $this->fetchAll($sql, [':order_id' => $orderId]);
    }

    public function generateOrderNo(): string {
        return 'CG' . date('ymd') . strtoupper(bin2hex(random_bytes(3)));
    }

    /**
     * Create Multi-Vendor Orders transactionally
     */
    public function createVendorOrder(array $orderData, array $items): int {
        $this->db->beginTransaction();
        try {
            $orderId = $this->insert($orderData);

            $sqlItem = "INSERT INTO `order_items` (`order_id`, `product_id`, `sku_id`, `product_name`, `variation_summary`, `product_image`, `unit_price`, `quantity`, `subtotal`, `created_at`)
                        VALUES (:order_id, :product_id, :sku_id, :product_name, :variation_summary, :product_image, :unit_price, :quantity, :subtotal, NOW())";
            $stmtItem = $this->db->prepare($sqlItem);

            foreach ($items as $item) {
                $stmtItem->execute([
                    ':order_id' => $orderId,
                    ':product_id' => $item['product_id'],
                    ':sku_id' => $item['sku_id'] ?? null,
                    ':product_name' => $item['product_name'],
                    ':variation_summary' => $item['variation_summary'] ?? null,
                    ':product_image' => $item['main_image'] ?? null,
                    ':unit_price' => $item['price'],
                    ':quantity' => $item['quantity'],
                    ':subtotal' => (float)$item['price'] * (int)$item['quantity']
                ]);

                // Deduct Product Stock
                $sqlStock = "UPDATE `products` SET `stock` = `stock` - :qty WHERE `id` = :id";
                $stmtStock = $this->db->prepare($sqlStock);
                $stmtStock->execute([':qty' => $item['quantity'], ':id' => $item['product_id']]);
            }

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

    /**
     * Seller Orders Listing
     */
    public function getSellerOrders(int $storeId, array $filters = [], int $limit = 20, int $offset = 0): array {
        [$whereSql, $params] = $this->buildSellerOrderConditions($storeId, $filters);

        $sql = "SELECT o.*,
                (SELECT COUNT(*) FROM `order_items` oi WHERE oi.order_id = o.id) as total_items,
                (SELECT oi.product_name FROM `order_items` oi WHERE oi.order_id = o.id LIMIT 1) as first_item_name,
                (SELECT oi.product_image FROM `order_items` oi WHERE oi.order_id = o.id LIMIT 1) as first_item_image
                FROM `orders` o
                WHERE {$whereSql}
                ORDER BY o.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function countSellerOrders(int $storeId, array $filters = []): int {
        [$whereSql, $params] = $this->buildSellerOrderConditions($storeId, $filters);

        $sql = "SELECT COUNT(*) FROM `orders` o WHERE {$whereSql}";
        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();

        return (int)$stmt->fetchColumn();
    }

    private function buildSellerOrderConditions(int $storeId, array $filters): array {
        $conditions = ["o.store_id = :store_id", "o.deleted_at IS NULL"];
        $params = [':store_id' => $storeId];

        if (!empty($filters['q'])) {
            $conditions[] = "(o.order_no LIKE :kw OR o.customer_name LIKE :kw OR o.customer_phone LIKE :kw)";
            $params[':kw'] = '%' . trim($filters['q']) . '%';
        }

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $conditions[] = "o.order_status = :status";
            $params[':status'] = $filters['status'];
        }

        if (!empty($filters['date_from'])) {
            $conditions[] = "o.created_at >= :date_from";
            $params[':date_from'] = $filters['date_from'] . ' 00:00:00';
        }

        if (!empty($filters['date_to'])) {
            $conditions[] = "o.created_at <= :date_to";
            $params[':date_to'] = $filters['date_to'] . ' 23:59:59';
        }

        return [implode(" AND ", $conditions), $params];
    }

    public function findSellerOrderByNo(string $orderNo, int $storeId): ?array {
        $sql = "SELECT o.* FROM `orders` o 
                WHERE o.order_no = :order_no AND o.store_id = :store_id AND o.deleted_at IS NULL 
                LIMIT 1";
        return $this->fetchOne($sql, [':order_no' => $orderNo, ':store_id' => $storeId]);
    }

    public function updateSellerOrderStatus(int $orderId, int $storeId, string $newStatus, ?string $trackingNo = null, ?string $shippingMethod = null): bool {
        $updateData = [
            'order_status' => $newStatus,
            'updated_at' => date('Y-m-d H:i:s')
        ];

        if ($trackingNo !== null) {
            $updateData['tracking_no'] = $trackingNo;
        }

        if ($shippingMethod !== null) {
            $updateData['shipping_method'] = $shippingMethod;
        }

        $sql = "UPDATE `orders` SET " . implode(', ', array_map(fn($k) => "`$k` = :$k", array_keys($updateData))) . " 
                WHERE `id` = :order_id AND `store_id` = :store_id";

        $params = array_merge($updateData, [
            'order_id' => $orderId,
            'store_id' => $storeId
        ]);

        $stmt = $this->query($sql, $params);
        return $stmt->rowCount() > 0;
    }

    public function getSellerFinanceSummary(int $storeId): array {
        $sql = "SELECT 
                COUNT(*) as total_orders,
                SUM(CASE WHEN order_status = 'completed' THEN subtotal ELSE 0 END) as gross_sales,
                SUM(CASE WHEN order_status = 'completed' THEN shipping_fee ELSE 0 END) as total_shipping_revenue,
                SUM(CASE WHEN order_status = 'completed' THEN total_amount ELSE 0 END) as total_collected,
                SUM(CASE WHEN order_status = 'completed' THEN commission_amount ELSE 0 END) as total_commission,
                SUM(CASE WHEN order_status = 'completed' THEN net_revenue ELSE 0 END) as net_revenue,
                SUM(CASE WHEN order_status IN ('payment_confirmed', 'preparing', 'shipping') THEN net_revenue ELSE 0 END) as pending_settlement
                FROM `orders`
                WHERE store_id = :store_id AND deleted_at IS NULL";
        
        $summary = $this->fetchOne($sql, [':store_id' => $storeId]);
        return $summary ?: [
            'total_orders' => 0,
            'gross_sales' => 0.00,
            'total_shipping_revenue' => 0.00,
            'total_collected' => 0.00,
            'total_commission' => 0.00,
            'net_revenue' => 0.00,
            'pending_settlement' => 0.00
        ];
    }

    // =========================================================
    // Admin Order Monitoring Methods
    // =========================================================

    public function getAdminOrders(array $filters = [], int $limit = 20, int $offset = 0): array {
        [$whereSql, $params] = $this->buildAdminOrderConditions($filters);

        $sql = "SELECT o.*, s.store_name, s.store_slug,
                (SELECT COUNT(*) FROM `order_items` oi WHERE oi.order_id = o.id) as total_items
                FROM `orders` o
                JOIN `stores` s ON o.store_id = s.id
                WHERE {$whereSql}
                ORDER BY o.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit',  $limit,  PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function countAdminOrders(array $filters = []): int {
        [$whereSql, $params] = $this->buildAdminOrderConditions($filters);
        $sql = "SELECT COUNT(*) FROM `orders` o JOIN `stores` s ON o.store_id = s.id WHERE {$whereSql}";
        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    }

    private function buildAdminOrderConditions(array $filters): array {
        $conditions = ['o.deleted_at IS NULL'];
        $params = [];

        if (!empty($filters['q'])) {
            $conditions[] = '(o.order_no LIKE :q OR o.customer_name LIKE :q OR o.customer_phone LIKE :q)';
            $params[':q'] = '%' . trim($filters['q']) . '%';
        }
        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $conditions[] = 'o.order_status = :status';
            $params[':status'] = $filters['status'];
        }
        if (!empty($filters['payment_status']) && $filters['payment_status'] !== 'all') {
            $conditions[] = 'o.payment_status = :pay_status';
            $params[':pay_status'] = $filters['payment_status'];
        }
        if (!empty($filters['store_id'])) {
            $conditions[] = 'o.store_id = :store_id';
            $params[':store_id'] = (int)$filters['store_id'];
        }
        if (!empty($filters['date_from'])) {
            $conditions[] = 'o.created_at >= :date_from';
            $params[':date_from'] = $filters['date_from'] . ' 00:00:00';
        }
        if (!empty($filters['date_to'])) {
            $conditions[] = 'o.created_at <= :date_to';
            $params[':date_to'] = $filters['date_to'] . ' 23:59:59';
        }

        return [implode(' AND ', $conditions), $params];
    }

    public function findAdminOrderByNo(string $orderNo): ?array {
        $sql = "SELECT o.*, s.store_name, s.store_slug, s.phone as store_phone
                FROM `orders` o
                JOIN `stores` s ON o.store_id = s.id
                WHERE o.order_no = :order_no AND o.deleted_at IS NULL
                LIMIT 1";
        return $this->fetchOne($sql, [':order_no' => $orderNo]);
    }

    public function getPlatformOrderStats(): array {
        $sql = "SELECT
                COUNT(*) as total_orders,
                SUM(CASE WHEN order_status = 'pending_payment' THEN 1 ELSE 0 END) as pending_payment,
                SUM(CASE WHEN order_status = 'preparing' THEN 1 ELSE 0 END) as preparing,
                SUM(CASE WHEN order_status = 'shipping' THEN 1 ELSE 0 END) as shipping,
                SUM(CASE WHEN order_status = 'completed' THEN 1 ELSE 0 END) as completed,
                SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as today_orders,
                SUM(CASE WHEN DATE(created_at) = CURDATE() AND order_status = 'completed' THEN total_amount ELSE 0 END) as today_gmv,
                SUM(CASE WHEN order_status = 'completed' THEN total_amount ELSE 0 END) as total_gmv,
                SUM(CASE WHEN order_status = 'completed' THEN commission_amount ELSE 0 END) as total_commission
                FROM `orders` WHERE deleted_at IS NULL";
        return $this->fetchOne($sql) ?: [];
    }
}