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

class Customer {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create customer profile
     */
    public function create($data) {
        $sql = "INSERT INTO customers (user_id, first_name, last_name, phone, address, city, state, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, ?, NOW())";
        
        $result = $this->db->execute($sql, [
            $data['user_id'],
            $data['first_name'],
            $data['last_name'],
            $data['phone'],
            $data['address'] ?? null,
            $data['city'] ?? null,
            $data['state'] ?? null
        ]);
        
        return $result ? $this->db->lastInsertId() : false;
    }
    
    /**
     * Find customer by ID
     */
    public function find($id) {
        $sql = "SELECT c.*, u.email, u.status as user_status 
                FROM customers c
                JOIN users u ON c.user_id = u.id
                WHERE c.id = ? LIMIT 1";
        return $this->db->fetch($sql, [$id]);
    }
    
    /**
     * Find customer by user ID
     */
    public function findByUserId($user_id) {
        $sql = "SELECT c.*, u.email, u.status as user_status 
                FROM customers c
                JOIN users u ON c.user_id = u.id
                WHERE c.user_id = ? LIMIT 1";
        return $this->db->fetch($sql, [$user_id]);
    }
    
    /**
     * Get all customers with pagination
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT c.*, u.email, u.status as user_status, u.created_at as registered_at,
                (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) as total_orders,
                (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id AND o.status = 'completed') as completed_orders,
                (SELECT COALESCE(SUM(o.total_amount), 0) FROM orders o WHERE o.customer_id = c.id AND o.payment_status = 'paid') as total_spent
                FROM customers c
                JOIN users u ON c.user_id = u.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['state'])) {
            $sql .= " AND c.state = ?";
            $params[] = $filters['state'];
        }
        
        if (!empty($filters['city'])) {
            $sql .= " AND c.city = ?";
            $params[] = $filters['city'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (CONCAT(c.first_name, ' ', c.last_name) LIKE ? OR u.email LIKE ? OR c.phone LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }
        
        $sql .= " ORDER BY c.created_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Count customers
     */
    public function count($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM customers c WHERE 1=1";
        $params = [];
        
        if (!empty($filters['state'])) {
            $sql .= " AND c.state = ?";
            $params[] = $filters['state'];
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }
    
    /**
     * Update customer
     */
    public function update($id, $data) {
        $fields = [];
        $params = [];
        
        foreach ($data as $key => $value) {
            $fields[] = "{$key} = ?";
            $params[] = $value;
        }
        
        $params[] = $id;
        
        $sql = "UPDATE customers SET " . implode(', ', $fields) . ", updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, $params);
    }
    
    /**
     * Delete customer
     */
    public function delete($id) {
        $sql = "DELETE FROM customers WHERE id = ?";
        return $this->db->execute($sql, [$id]);
    }
    
    /**
     * Get customer statistics
     */
    public function getStats($customer_id = null) {
        if ($customer_id) {
            $sql = "SELECT 
                    (SELECT COUNT(*) FROM orders WHERE customer_id = ?) as total_orders,
                    (SELECT COUNT(*) FROM orders WHERE customer_id = ? AND status = 'ongoing') as active_orders,
                    (SELECT COUNT(*) FROM orders WHERE customer_id = ? AND status = 'completed') as completed_orders,
                    (SELECT COUNT(*) FROM orders WHERE customer_id = ? AND status = 'cancelled') as cancelled_orders,
                    (SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE customer_id = ? AND payment_status = 'paid') as total_spent,
                    (SELECT COUNT(*) FROM reviews r JOIN orders o ON r.order_id = o.id WHERE o.customer_id = ?) as total_reviews";
            
            return $this->db->fetch($sql, [$customer_id, $customer_id, $customer_id, $customer_id, $customer_id, $customer_id]);
        } else {
            $sql = "SELECT 
                    COUNT(*) as total_customers,
                    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 customers";
            
            return $this->db->fetch($sql);
        }
    }
    
    /**
     * Get top customers by orders
     */
    public function getTopByOrders($limit = 10) {
        $sql = "SELECT c.*, u.email,
                (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) as total_orders,
                (SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE customer_id = c.id AND payment_status = 'paid') as total_spent,
                (SELECT COUNT(*) FROM orders WHERE customer_id = c.id AND status = 'completed') as completed_orders
                FROM customers c
                JOIN users u ON c.user_id = u.id
                ORDER BY total_orders DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Get top customers by spending
     */
    public function getTopBySpending($limit = 10) {
        $sql = "SELECT c.*, u.email,
                (SELECT COALESCE(SUM(total_amount), 0) FROM orders WHERE customer_id = c.id AND payment_status = 'paid') as total_spent,
                (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) as total_orders,
                (SELECT COUNT(*) FROM orders WHERE customer_id = c.id AND status = 'completed') as completed_orders
                FROM customers c
                JOIN users u ON c.user_id = u.id
                ORDER BY total_spent DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Get recently active customers
     */
    public function getRecentlyActive($limit = 10) {
        $sql = "SELECT c.*, u.email, u.last_login,
                (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) as total_orders
                FROM customers c
                JOIN users u ON c.user_id = u.id
                WHERE u.last_login IS NOT NULL
                ORDER BY u.last_login DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Search customers
     */
    public function search($keyword, $limit = 20) {
        $sql = "SELECT c.*, u.email,
                (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) as total_orders
                FROM customers c
                JOIN users u ON c.user_id = u.id
                WHERE CONCAT(c.first_name, ' ', c.last_name) LIKE ? 
                OR u.email LIKE ? 
                OR c.phone LIKE ?
                ORDER BY c.first_name ASC
                LIMIT ?";
        
        $search = '%' . $keyword . '%';
        return $this->db->fetchAll($sql, [$search, $search, $search, $limit]);
    }


        /**
     * Get customers for message dropdown (simplified version)
     */
    public function getForMessageDropdown($limit = 100)
    {
        try {
            $sql = "SELECT c.id, 
                        c.user_id,
                        c.first_name, 
                        c.last_name,
                        CONCAT(c.first_name, ' ', c.last_name) as name, 
                        u.email, 
                        c.city, 
                        c.state
                    FROM customers c
                    JOIN users u ON c.user_id = u.id
                    WHERE u.status = 'active'
                    ORDER BY c.first_name, c.last_name
                    LIMIT ?";
            
            return $this->db->fetchAll($sql, [(int)$limit]);
        } catch (Exception $e) {
            error_log("Customer dropdown failed: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get active customers count
     */
    public function getActiveCount()
    {
        $sql = "SELECT COUNT(*) as count 
                FROM customers c 
                JOIN users u ON c.user_id = u.id 
                WHERE u.status = 'active'";
        $result = $this->db->fetch($sql);
        return (int)($result['count'] ?? 0);
    }

    /**
     * Get customer favorites/wishlist
     */
    public function getFavorites($customer_id, $limit = 20, $offset = 0) {
        $sql = "SELECT f.*, 
                i.title as item_title, 
                i.main_image as item_image,
                i.price_per_day,
                i.rating_average,
                i.status as item_status,
                i.approval_status,
                v.business_name as vendor_name,
                c.name as category_name
                FROM favorites f
                JOIN items i ON f.item_id = i.id
                JOIN vendors v ON i.vendor_id = v.id
                JOIN categories c ON i.category_id = c.id
                WHERE f.customer_id = ?
                ORDER BY f.created_at DESC
                LIMIT ? OFFSET ?";
        
        return $this->db->fetchAll($sql, [$customer_id, $limit, $offset]);
    }

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

    /**
     * Get favorites count (alias for countFavorites)
     */
    public function getFavoritesCount($customer_id) {
        return $this->countFavorites($customer_id);
    }

    /**
     * Add item to favorites
     */
    public function addToFavorites($customer_id, $item_id) {
        // Check if already exists
        $check_sql = "SELECT id FROM favorites WHERE customer_id = ? AND item_id = ?";
        $exists = $this->db->fetch($check_sql, [$customer_id, $item_id]);
        
        if ($exists) {
            return false; // Already in favorites
        }
        
        $sql = "INSERT INTO favorites (customer_id, item_id, created_at) VALUES (?, ?, NOW())";
        return $this->db->execute($sql, [$customer_id, $item_id]);
    }

    /**
     * Remove item from favorites
     */
    public function removeFromFavorites($customer_id, $item_id) {
        $sql = "DELETE FROM favorites WHERE customer_id = ? AND item_id = ?";
        return $this->db->execute($sql, [$customer_id, $item_id]);
    }

    /**
     * Check if item is in favorites
     */
    public function isInFavorites($customer_id, $item_id) {
        $sql = "SELECT id FROM favorites WHERE customer_id = ? AND item_id = ?";
        $result = $this->db->fetch($sql, [$customer_id, $item_id]);
        return $result !== null;
    }

    /**
     * Check if customer has favorited an item
     */
    public function hasItemInFavorites($customer_id, $item_id) {
        $sql = "SELECT COUNT(*) as count 
                FROM favorites 
                WHERE customer_id = ? 
                AND item_id = ?";
        
        $result = $this->db->fetch($sql, [$customer_id, $item_id]);
        return ($result['count'] ?? 0) > 0;
    }

    /**
     * Get total count of active customers
     */
    public function getTotalCount() {
        $sql = "SELECT COUNT(*) as count 
                FROM customers c
                JOIN users u ON c.user_id = u.id
                WHERE u.status = 'active'";
        
        $result = $this->db->fetch($sql);
        return (int)($result['count'] ?? 0);
    }

}