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

class Vendor {
    private $db;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create vendor profile
     */
    public function create($data) {
        $sql = "INSERT INTO vendors (user_id, business_name, business_type, phone, address, city, state, 
                business_description, approval_status, created_at) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'pending', NOW())";
        
        $result = $this->db->execute($sql, [
            $data['user_id'],
            $data['business_name'],
            $data['business_type'] ?? null,
            $data['phone'],
            $data['address'],
            $data['city'],
            $data['state'],
            $data['business_description'] ?? null
        ]);
        
        return $result ? $this->db->lastInsertId() : false;
    }
    
    /**
     * Find vendor by ID
     */
    public function find($id) {
        $sql = "SELECT v.*, u.email, u.status as user_status 
                FROM vendors v
                JOIN users u ON v.user_id = u.id
                WHERE v.id = ? LIMIT 1";
        return $this->db->fetch($sql, [$id]);
    }
    
    /**
     * Find vendor by user ID
     */
    public function findByUserId($user_id) {
        $sql = "SELECT v.*, u.email, u.status as user_status 
                FROM vendors v
                JOIN users u ON v.user_id = u.id
                WHERE v.user_id = ? LIMIT 1";
        return $this->db->fetch($sql, [$user_id]);
    }
    
    /**
     * Get all vendors with pagination
     */
    public function getAll($limit = 20, $offset = 0, $filters = []) {
        $sql = "SELECT v.*, u.email, u.status as user_status, u.email_verified as reg_verification, u.created_at as registered_at,
                (SELECT COUNT(*) FROM items WHERE vendor_id = v.id) as total_items,
                (SELECT COUNT(*) FROM items WHERE vendor_id = v.id AND approval_status = 'approved') as approved_items,
                (SELECT COUNT(*) FROM orders WHERE vendor_id = v.id) as total_orders
                FROM vendors v
                JOIN users u ON v.user_id = u.id
                WHERE 1=1";
        
        $params = [];
        
        if (!empty($filters['approval_status'])) {
            $sql .= " AND v.approval_status = ?";
            $params[] = $filters['approval_status'];
        }
        
        if (!empty($filters['state'])) {
            $sql .= " AND v.state = ?";
            $params[] = $filters['state'];
        }
        
        if (!empty($filters['city'])) {
            $sql .= " AND v.city = ?";
            $params[] = $filters['city'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= " AND (v.business_name LIKE ? OR u.email LIKE ? OR v.phone LIKE ?)";
            $search = '%' . $filters['search'] . '%';
            $params[] = $search;
            $params[] = $search;
            $params[] = $search;
        }
        
        $sql .= " ORDER BY v.created_at DESC LIMIT ? OFFSET ?";
        $params[] = $limit;
        $params[] = $offset;
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Count vendors
     */
    public function count($filters = []) {
        $sql = "SELECT COUNT(*) as count FROM vendors v WHERE 1=1";
        $params = [];
        
        if (!empty($filters['approval_status'])) {
            $sql .= " AND v.approval_status = ?";
            $params[] = $filters['approval_status'];
        }
        
        if (!empty($filters['state'])) {
            $sql .= " AND v.state = ?";
            $params[] = $filters['state'];
        }
        
        $result = $this->db->fetch($sql, $params);
        return $result['count'] ?? 0;
    }
    
    /**
     * Update vendor
     */
    public function update($id, $data) {
        $fields = [];
        $params = [];
        
        foreach ($data as $key => $value) {
            $fields[] = "{$key} = ?";
            $params[] = $value;
        }
        
        $params[] = $id;
        
        $sql = "UPDATE vendors SET " . implode(', ', $fields) . ", updated_at = NOW() WHERE id = ?";
        return $this->db->execute($sql, $params);
    }
    
    /**
     * Update approval status
     */
    public function updateApprovalStatus($id, $status, $approved_by, $reason = null) {
        $sql = "UPDATE vendors SET 
                approval_status = ?, 
                approved_by = ?, 
                approval_date = NOW(),
                rejection_reason = ?,
                updated_at = NOW() 
                WHERE id = ?";
        
        return $this->db->execute($sql, [$status, $approved_by, $reason, $id]);
    }
    
    /**
     * Delete vendor
     */
    public function delete($id) {
        $sql = "DELETE FROM vendors WHERE id = ?";
        return $this->db->execute($sql, [$id]);
    }
    
    /**
     * Get vendor statistics
     */
    public function getStats($vendor_id = null) {
        if ($vendor_id) {
            $sql = "SELECT 
                    (SELECT COUNT(*) FROM items WHERE vendor_id = ?) as total_items,
                    (SELECT COUNT(*) FROM items WHERE vendor_id = ? AND approval_status = 'approved') as approved_items,
                    (SELECT COUNT(*) FROM orders WHERE vendor_id = ?) as total_orders,
                    (SELECT COUNT(*) FROM orders WHERE vendor_id = ? AND status = 'ongoing') as active_orders,
                    (SELECT COUNT(*) FROM orders WHERE vendor_id = ? AND status = 'completed') as completed_orders,
                    (SELECT COALESCE(SUM(vendor_amount), 0) FROM commissions WHERE vendor_id = ? AND status = 'paid') as total_earnings,
                    (SELECT COALESCE(balance, 0) FROM wallets w JOIN users u ON w.user_id = u.id JOIN vendors v ON u.id = v.user_id WHERE v.id = ?) as wallet_balance";
            
            return $this->db->fetch($sql, [$vendor_id, $vendor_id, $vendor_id, $vendor_id, $vendor_id, $vendor_id, $vendor_id]);
        } else {
            $sql = "SELECT 
                    COUNT(*) as total_vendors,
                    SUM(CASE WHEN approval_status = 'approved' THEN 1 ELSE 0 END) as approved_vendors,
                    SUM(CASE WHEN approval_status = 'pending' THEN 1 ELSE 0 END) as pending_vendors,
                    SUM(CASE WHEN approval_status = 'rejected' THEN 1 ELSE 0 END) as rejected_vendors,
                    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 vendors";
            
            return $this->db->fetch($sql);
        }
    }
    
    /**
     * Get pending approval vendors
     */
    public function getPendingApproval($limit = 10) {
        $sql = "SELECT v.*, u.email, u.created_at as registered_at
                FROM vendors v
                JOIN users u ON v.user_id = u.id
                WHERE v.approval_status = 'pending'
                ORDER BY v.created_at ASC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Get top vendors by orders
     */
    public function getTopByOrders($limit = 10) {
        $sql = "SELECT v.*, u.email,
                (SELECT COUNT(*) FROM orders WHERE vendor_id = v.id) as total_orders,
                (SELECT COUNT(*) FROM orders WHERE vendor_id = v.id AND status = 'completed') as completed_orders,
                (SELECT COALESCE(AVG(r.rating), 0) FROM reviews r WHERE r.vendor_id = v.id) as average_rating
                FROM vendors v
                JOIN users u ON v.user_id = u.id
                WHERE v.approval_status = 'approved'
                ORDER BY total_orders DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Get top vendors by earnings
     */
    public function getTopByEarnings($limit = 10) {
        $sql = "SELECT v.*, u.email,
                (SELECT COALESCE(SUM(vendor_amount), 0) FROM commissions WHERE vendor_id = v.id AND status = 'paid') as total_earnings,
                (SELECT COUNT(*) FROM orders WHERE vendor_id = v.id AND status = 'completed') as completed_orders
                FROM vendors v
                JOIN users u ON v.user_id = u.id
                WHERE v.approval_status = 'approved'
                ORDER BY total_earnings DESC
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Search vendors
     */
    public function search($keyword, $limit = 20) {
        $sql = "SELECT v.*, u.email,
                (SELECT COUNT(*) FROM items WHERE vendor_id = v.id AND approval_status = 'approved') as total_items
                FROM vendors v
                JOIN users u ON v.user_id = u.id
                WHERE v.approval_status = 'approved'
                AND (v.business_name LIKE ? OR v.city LIKE ? OR v.state LIKE ?)
                ORDER BY v.business_name ASC
                LIMIT ?";
        
        $search = '%' . $keyword . '%';
        return $this->db->fetchAll($sql, [$search, $search, $search, $limit]);
    }

    /**
     * Get vendor by user ID
     */
    public function getByUserId($user_id) {
        $sql = "SELECT v.*, u.email 
                FROM vendors v
                LEFT JOIN users u ON v.user_id = u.id
                WHERE v.user_id = ? 
                LIMIT 1";
        return $this->db->fetch($sql, [$user_id]);
    }


    /**
     * Get vendors for message dropdown
     */
    public function getForMessageDropdown($limit = 100)
    {
        try {
            $sql = "SELECT v.id, 
                        v.user_id,
                        v.business_name, 
                        v.business_name as name, 
                        u.email, 
                        v.city, 
                        v.state
                    FROM vendors v
                    JOIN users u ON v.user_id = u.id
                    WHERE v.approval_status = 'approved' AND u.status = 'active'
                    ORDER BY v.business_name
                    LIMIT ?";
            
            return $this->db->fetchAll($sql, [(int)$limit]);
        } catch (Exception $e) {
            error_log("Vendor dropdown failed: " . $e->getMessage());
            return [];
        }
    }
    
    /**
     * Get approved vendors count
     */
    public function getApprovedCount()
    {
        $sql = "SELECT COUNT(*) as count 
                FROM vendors v 
                JOIN users u ON v.user_id = u.id 
                WHERE v.approval_status = 'approved' AND u.status = 'active'";
        $result = $this->db->fetch($sql);
        return (int)($result['count'] ?? 0);
    }

        /**
     * Get item count for vendor
     */
    public function getItemCount($vendor_id) {
        $sql = "SELECT COUNT(*) as count FROM items WHERE vendor_id = ?";
        $result = $this->db->fetch($sql, [$vendor_id]);
        return $result['count'] ?? 0;
    }

    /**
     * Get order count for vendor
     */
    public function getOrderCount($vendor_id) {
        $sql = "SELECT COUNT(*) as count FROM orders WHERE vendor_id = ?";
        $result = $this->db->fetch($sql, [$vendor_id]);
        return $result['count'] ?? 0;
    }

    /**
     * Suspend vendor account using approval_status
     */
    public function suspend($vendor_id, $admin_id, $reason) {
        $sql = "UPDATE vendors SET 
                approval_status = 'suspended', 
                rejection_reason = ?,
                approved_by = ?,
                approval_date = NOW(),
                updated_at = NOW()
                WHERE id = ?";
        
        return $this->db->execute($sql, [$reason, $admin_id, $vendor_id]);
    }

    /**
     * Activate vendor account using approval_status
     */
    public function activate($vendor_id, $admin_id) {
        $sql = "UPDATE vendors SET 
                approval_status = 'approved', 
                rejection_reason = NULL,
                approved_by = ?,
                approval_date = NOW(),
                updated_at = NOW()
                WHERE id = ?";
        
        return $this->db->execute($sql, [$admin_id, $vendor_id]);
    }
    
    /**
 * Get total count of active approved vendors
 */
    public function getTotalCount() {
        $sql = "SELECT COUNT(*) as count 
                FROM vendors v
                JOIN users u ON v.user_id = u.id
                WHERE u.status = 'active' 
                AND v.approval_status = 'approved'";
        
        $result = $this->db->fetch($sql);
        return (int)($result['count'] ?? 0);
    }


    /**
     * Find vendor by ID (with business details)
     */
    public function findById($id) {
        $sql = "SELECT v.*, u.email 
                FROM vendors v
                LEFT JOIN users u ON v.user_id = u.id
                WHERE v.id = ? 
                LIMIT 1";
        return $this->db->fetch($sql, [$id]);
    }

}