<?php
// app/Models/Order.php - Order Model

class Order {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create order
     */
    public function create($data) {
        $sql = "INSERT INTO orders (order_number, customer_id, vendor_id, item_id, start_date, end_date, 
                rental_days, subtotal, security_deposit, platform_fee, total_amount, delivery_address, 
                delivery_city, delivery_state, delivery_phone, special_instructions, status, payment_status, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', 'pending', NOW())";
        
        $result = $this->db->execute($sql, [
            $data['order_number'],
            $data['customer_id'],
            $data['vendor_id'],
            $data['item_id'],
            $data['start_date'],
            $data['end_date'],
            $data['rental_days'],
            $data['subtotal'],
            $data['security_deposit'] ?? 0,
            $data['platform_fee'],
            $data['total_amount'],
            $data['delivery_address'] ?? null,
            $data['delivery_city'] ?? null,
            $data['delivery_state'] ?? null,
            $data['delivery_phone'] ?? null,
            $data['special_instructions'] ?? null
        ]);
        
        return $result ? $this->db->lastInsertId() : false;
    }
    
    /**
     * Find order by ID
     */
    public function find($id) {
        $sql = "SELECT o.*, 
                i.title as item_title, i.main_image as item_image, i.price_per_day,
                v.business_name as vendor_name, v.phone as vendor_phone, v.address as vendor_address,
                c.first_name, c.last_name, c.phone as customer_phone,
                u.email as customer_email
                FROM orders o
                JOIN items i ON o.item_id = i.id
                JOIN vendors v ON o.vendor_id = v.id
                JOIN customers c ON o.customer_id = c.id
                JOIN users u ON c.user_id = u.id
                WHERE o.id = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$id]);
    }
    
    /**
     * Find order by order number
     */
    public function findByOrderNumber($order_number) {
        $sql = "SELECT o.*, 
                i.title as item_title, i.main_image as item_image, i.price_per_day,
                v.business_name as vendor_name, v.phone as vendor_phone,
                c.first_name, c.last_name, c.phone as customer_phone,
                u.email as customer_email
                FROM orders o
                JOIN items i ON o.item_id = i.id
                JOIN vendors v ON o.vendor_id = v.id
                JOIN customers c ON o.customer_id = c.id
                JOIN users u ON c.user_id = u.id
                WHERE o.order_number = ? LIMIT 1";
        
        return $this->db->fetch($sql, [$order_number]);
    }
    
    /**
     * Get all orders with pagination
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT o.*, 
                i.title as item_title, i.main_image as item_image,
                v.business_name as vendor_name,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name
                FROM orders o
                JOIN items i ON o.item_id = i.id
                JOIN vendors v ON o.vendor_id = v.id
                JOIN customers c ON o.customer_id = c.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND o.customer_id = ?";
            $params[] = $filters['customer_id'];
        }
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND o.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND o.status = ?";
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['payment_status'])) {
            $sql .= " AND o.payment_status = ?";
            $params[] = $filters['payment_status'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (o.order_number LIKE ? OR i.title LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
        }
        
        $sql .= " ORDER BY o.created_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Count orders
     */
    public function count($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM orders o WHERE 1=1";
        $params = [];
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND o.customer_id = ?";
            $params[] = $filters['customer_id'];
        }
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND o.vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        if (!empty($filters['status'])) {
            $sql .= " AND o.status = ?";
            $params[] = $filters['status'];
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }
    
    /**
     * Update order
     */
    public function update($id, $data) {
        $fields = [];
        $params = [];
        
        foreach ($data as $key => $value) {
            $fields[] = "{$key} = ?";
            $params[] = $value;
        }
        
        $params[] = $id;
        
        $sql = "UPDATE orders SET " . implode(', ', $fields) . ", updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, $params);
    }
    
    /**
     * Update order status
     */
    public function updateStatus($id, $status) {
        $sql = "UPDATE orders SET status = ?, updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, [$status, $id]);
    }
    
    /**
     * Update payment status
     */
    public function updatePaymentStatus($id, $status) {
        $sql = "UPDATE orders SET payment_status = ?, updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, [$status, $id]);
    }
    
    /**
     * Confirm order
     */
    public function confirm($id) {
        $sql = "UPDATE orders SET status = 'confirmed', confirmed_at = NOW(), updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, [$id]);
    }
    
    /**
     * Start order
     */
    public function start($id) {
        $sql = "UPDATE orders SET status = 'ongoing', started_at = NOW(), updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, [$id]);
    }
    
    /**
     * Complete order
     */
    public function complete($id) {
        $sql = "UPDATE orders SET status = 'completed', completed_at = NOW(), updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, [$id]);
    }
    
    /**
     * Cancel order
     */
    public function cancel($id, $reason, $cancelled_by) {
        $sql = "UPDATE orders SET 
                status = 'cancelled', 
                cancellation_reason = ?, 
                cancelled_by = ?,
                cancelled_at = NOW(),
                updated_at = NOW() 
                WHERE id = ?";
        
        return $this->db->execute($sql, [$reason, $cancelled_by, $id]);
    }
    
    /**
     * Check item availability
     */
    public function checkAvailability($item_id, $start_date, $end_date, $exclude_order_id = null) {
        $sql = "SELECT COUNT(*) as count FROM orders 
                WHERE item_id = ? 
                AND status NOT IN ('cancelled', 'completed')
                AND (
                    (start_date <= ? AND end_date >= ?) OR
                    (start_date <= ? AND end_date >= ?) OR
                    (start_date >= ? AND end_date <= ?)
                )";
        
        $params = [$item_id, $start_date, $start_date, $end_date, $end_date, $start_date, $end_date];
        
        if ($exclude_order_id) {
            $sql .= " AND id != ?";
            $params[] = $exclude_order_id;
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] == 0;
    }
    
    /**
     * Generate unique order number
     */
    public function generateOrderNumber() {
        do {
            $order_number = 'ORD-' . date('Ymd') . '-' . strtoupper(substr(uniqid(), -6));
            $exists = $this->findByOrderNumber($order_number);
        } while ($exists);
        
        return $order_number;
    }
    
    /**
     * Get order statistics
     */
    public function getStats($filters = []) {
        $sql = "SELECT 
                COUNT(*) as total_orders,
                SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_orders,
                SUM(CASE WHEN status = 'confirmed' THEN 1 ELSE 0 END) as confirmed_orders,
                SUM(CASE WHEN status = 'ongoing' THEN 1 ELSE 0 END) as ongoing_orders,
                SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_orders,
                SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_orders,
                SUM(CASE WHEN payment_status = 'paid' THEN 1 ELSE 0 END) as paid_orders,
                COALESCE(SUM(CASE WHEN payment_status = 'paid' THEN total_amount ELSE 0 END), 0) as total_revenue,
                SUM(CASE WHEN DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 ELSE 0 END) as new_this_week,
                SUM(CASE WHEN DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 30 DAY) THEN 1 ELSE 0 END) as new_this_month
                FROM orders
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND customer_id = ?";
            $params[] = $filters['customer_id'];
        }
        
        return $this->db->fetch($sql, $params);
    }
    
    /**
     * Get revenue by period
     */
    public function getRevenueByPeriod($period = 'month', $vendor_id = null) {
        if ($period == 'week') {
            $sql = "SELECT DATE(created_at) as date, SUM(total_amount) as revenue
                    FROM orders
                    WHERE payment_status = 'paid'
                    AND DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
        } else {
            $sql = "SELECT DATE_FORMAT(created_at, '%Y-%m') as date, SUM(total_amount) as revenue
                    FROM orders
                    WHERE payment_status = 'paid'
                    AND DATE(created_at) >= DATE_SUB(NOW(), INTERVAL 12 MONTH)";
        }
        
        if ($vendor_id) {
            $sql .= " AND vendor_id = ?";
            $params = [$vendor_id];
        } else {
            $params = [];
        }
        
        $sql .= " GROUP BY date ORDER BY date ASC";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Get recent orders
     */
    public function getRecent($limit = 10) {
        $sql = "SELECT o.*, 
                i.title as item_title, i.main_image as item_image,
                v.business_name as vendor_name,
                CONCAT(c.first_name, ' ', c.last_name) as customer_name
                FROM orders o
                JOIN items i ON o.item_id = i.id
                JOIN vendors v ON o.vendor_id = v.id
                JOIN customers c ON o.customer_id = c.id
                ORDER BY o.created_at DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }



    /**
     * Count all orders
     */
    // public function count() {
    //     $sql = "SELECT COUNT(*) as total FROM orders";
    //     $result = $this->db->fetch($sql);
    //     return $result['total'] ?? 0;
    // }

    /**
     * Count orders by status
     */
    public function countByStatus($status) {
        $sql = "SELECT COUNT(*) as total FROM orders WHERE status = ?";
        $result = $this->db->fetch($sql, [$status]);
        return $result['total'] ?? 0;
    }

    /**
     * Count orders by vendor
     */
    public function countByVendor($vendor_id) {
        $sql = "SELECT COUNT(*) as total FROM orders WHERE vendor_id = ?";
        $result = $this->db->fetch($sql, [$vendor_id]);
        return $result['total'] ?? 0;
    }

    /**
     * Count orders by vendor and status
     */
    public function countByVendorAndStatus($vendor_id, $status) {
        $sql = "SELECT COUNT(*) as total FROM orders WHERE vendor_id = ? AND status = ?";
        $result = $this->db->fetch($sql, [$vendor_id, $status]);
        return $result['total'] ?? 0;
    }

    /**
     * Count orders by customer
     */
    public function countByCustomer($customer_id) {
        $sql = "SELECT COUNT(*) as total FROM orders WHERE customer_id = ?";
        $result = $this->db->fetch($sql, [$customer_id]);
        return $result['total'] ?? 0;
    }

    /**
     * Count orders by customer and status
     */
    public function countByCustomerAndStatus($customer_id, $status) {
        $sql = "SELECT COUNT(*) as total FROM orders WHERE customer_id = ? AND status = ?";
        $result = $this->db->fetch($sql, [$customer_id, $status]);
        return $result['total'] ?? 0;
    }

    /**
     * Get customer total orders count
     */
    public function getCustomerTotalOrders($customer_id) {
        return $this->countByCustomer($customer_id);
    }

    /**
     * Get customer active orders count
     */
    public function getCustomerActiveOrders($customer_id) {
        $sql = "SELECT COUNT(*) as total FROM orders 
                WHERE customer_id = ? 
                AND status IN ('pending', 'confirmed', 'ongoing')";
        $result = $this->db->fetch($sql, [$customer_id]);
        return $result['total'] ?? 0;
    }

    /**
     * Get customer completed orders count
     */
    public function getCustomerCompletedOrders($customer_id) {
        return $this->countByCustomerAndStatus($customer_id, 'completed');
    }

    /**
     * Get customer total spent
     */
    public function getCustomerTotalSpent($customer_id) {
        $sql = "SELECT COALESCE(SUM(total_amount), 0) as total 
                FROM orders 
                WHERE customer_id = ? 
                AND payment_status = 'paid'";
        $result = $this->db->fetch($sql, [$customer_id]);
        return $result['total'] ?? 0;
    }

    /**
     * Get recent orders by customer
     */
    public function getRecentOrdersByCustomer($customer_id, $limit = 5) {
        $sql = "SELECT o.*, 
                i.title as item_title, 
                i.main_image as item_image,
                v.business_name as vendor_name
                FROM orders o
                JOIN items i ON o.item_id = i.id
                JOIN vendors v ON o.vendor_id = v.id
                WHERE o.customer_id = ?
                ORDER BY o.created_at DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$customer_id, $limit]);
    }

    /**
     * Get customer monthly spent
     */
    public function getCustomerMonthlySpent($customer_id, $year_month) {
        $sql = "SELECT COALESCE(SUM(total_amount), 0) as total 
                FROM orders 
                WHERE customer_id = ? 
                AND payment_status = 'paid'
                AND DATE_FORMAT(created_at, '%Y-%m') = ?";
        $result = $this->db->fetch($sql, [$customer_id, $year_month]);
        return $result['total'] ?? 0;
    }

    /**
     * Get customer monthly orders for chart (last N months)
     */
    public function getCustomerMonthlyOrders($customer_id, $months = 12) {
        $sql = "SELECT 
                DATE_FORMAT(created_at, '%Y-%m') as month,
                DATE_FORMAT(created_at, '%b %Y') as month_label,
                COUNT(*) as order_count,
                COALESCE(SUM(CASE WHEN payment_status = 'paid' THEN total_amount ELSE 0 END), 0) as total_spent
                FROM orders 
                WHERE customer_id = ? 
                AND created_at >= DATE_SUB(NOW(), INTERVAL ? MONTH)
                GROUP BY DATE_FORMAT(created_at, '%Y-%m')
                ORDER BY month ASC";
        
        return $this->db->fetchAll($sql, [$customer_id, $months]);
    }

    /**
     * Get orders by customer ID
     */
    public function getByCustomerId($customer_id, $limit = null, $offset = 0) {
        $sql = "SELECT o.*, 
                i.title as item_title, 
                i.main_image as item_image,
                v.business_name as vendor_name
                FROM orders o
                JOIN items i ON o.item_id = i.id
                JOIN vendors v ON o.vendor_id = v.id
                WHERE o.customer_id = ?
                ORDER BY o.created_at DESC";
        
        $params = [$customer_id];
        
        if ($limit) {
            $sql .= " LIMIT ? OFFSET ?";
            $params[] = $limit;
            $params[] = $offset;
        }
        
        return $this->db->fetchAll($sql, $params);
    }

    /**
     * Get monthly orders (for charts/statistics)
     */
    public function getMonthlyOrders($months = 12, $filters = []) {
        $sql = "SELECT 
                DATE_FORMAT(created_at, '%Y-%m') as month,
                DATE_FORMAT(created_at, '%b %Y') as month_label,
                COUNT(*) as order_count,
                COALESCE(SUM(CASE WHEN payment_status = 'paid' THEN total_amount ELSE 0 END), 0) as total_revenue
                FROM orders 
                WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? MONTH)";
        
        $params = [$months];
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND customer_id = ?";
            $params[] = $filters['customer_id'];
        }
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        $sql .= " GROUP BY DATE_FORMAT(created_at, '%Y-%m')
                 ORDER BY month ASC";
        
        return $this->db->fetchAll($sql, $params);
    }

    /**
     * Get monthly revenue (for charts/statistics)
     */
    public function getMonthlyRevenue($months = 12, $filters = []) {
        $sql = "SELECT 
                DATE_FORMAT(created_at, '%Y-%m') as month,
                DATE_FORMAT(created_at, '%b %Y') as month_label,
                COALESCE(SUM(CASE WHEN payment_status = 'paid' THEN total_amount ELSE 0 END), 0) as revenue,
                COUNT(*) as order_count
                FROM orders 
                WHERE created_at >= DATE_SUB(NOW(), INTERVAL ? MONTH)
                AND payment_status = 'paid'";
        
        $params = [$months];
        
        if (!empty($filters['customer_id'])) {
            $sql .= " AND customer_id = ?";
            $params[] = $filters['customer_id'];
        }
        
        if (!empty($filters['vendor_id'])) {
            $sql .= " AND vendor_id = ?";
            $params[] = $filters['vendor_id'];
        }
        
        $sql .= " GROUP BY DATE_FORMAT(created_at, '%Y-%m')
                 ORDER BY month ASC";
        
        return $this->db->fetchAll($sql, $params);
    }

    /**
     * Get customer earnings (refunds/credits received)
     */
    public function getCustomerEarnings($user_id) {
        // For customers, earnings could be from refunds or referral credits
        // Currently returning 0 as customers don't earn from orders
        return 0;
    }

    /**
     * Get customer pending orders count
     */
    public function getCustomerPendingOrders($user_id) {
        // Get customer_id from user_id
        $customer_sql = "SELECT id FROM customers WHERE user_id = ? LIMIT 1";
        $customer = $this->db->fetch($customer_sql, [$user_id]);
        
        if (!$customer) {
            return 0;
        }
        
        $sql = "SELECT COUNT(*) as total FROM orders 
                WHERE customer_id = ? 
                AND status = 'pending'";
        $result = $this->db->fetch($sql, [$customer['id']]);
        return $result['total'] ?? 0;
    }

    /**
     * Get customer total orders (using user_id)
     */
    // public function getCustomerTotalOrders($customer_id_or_user_id) {
    //     // First check if it's already a customer_id by trying to use it directly
    //     $sql = "SELECT COUNT(*) as total FROM orders WHERE customer_id = ?";
    //     $result = $this->db->fetch($sql, [$customer_id_or_user_id]);
        
    //     if ($result && $result['total'] > 0) {
    //         return $result['total'];
    //     }
        
    //     // If not found, assume it's a user_id and get customer_id first
    //     $customer_sql = "SELECT id FROM customers WHERE user_id = ? LIMIT 1";
    //     $customer = $this->db->fetch($customer_sql, [$customer_id_or_user_id]);
        
    //     if (!$customer) {
    //         return 0;
    //     }
        
    //     $result = $this->db->fetch($sql, [$customer['id']]);
    //     return $result['total'] ?? 0;
    // }

    /**
     * Find order by ID
     */
    public function findById($id) {
        $sql = "SELECT * FROM orders WHERE id = ? LIMIT 1";
        return $this->db->fetch($sql, [$id]);
    }

    /**
     * Get total count of confirmed/completed orders
     */
    public function getTotalCount() {
        $sql = "SELECT COUNT(*) as count 
                FROM orders 
                WHERE status IN ('confirmed', 'completed')";
        
        $result = $this->db->fetch($sql);
        return $result['count'] ?? 0;
    }
}