<?php
/**
 * ZLO Platform - Lead Model
 * CRM Lead Management
 */

declare(strict_types=1);

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

class Lead extends Model
{
    protected string $table = 'leads';
    protected string $primaryKey = 'id';
    
    protected array $fillable = [
        'first_name',
        'last_name',
        'company',
        'email',
        'phone',
        'country',
        'city',
        'industry',
        'company_size',
        'interest_solution_id',
        'message',
        'source',
        'utm_source',
        'utm_medium',
        'utm_campaign',
        'ip_address',
        'user_agent',
        'status',
        'score',
        'assigned_to',
        'notes'
    ];
    
    protected array $casts = [
        'id' => 'int',
        'interest_solution_id' => 'int',
        'score' => 'int',
        'assigned_to' => 'int',
        'created_at' => 'datetime',
        'updated_at' => 'datetime'
    ];
    
    /**
     * Create lead with tracking data
     */
    public function create(array $data): int
    {
        $security = new Security();
        
        // Add tracking data
        $data['ip_address'] = $security->getClientIp();
        $data['user_agent'] = $_SERVER['HTTP_USER_AGENT'] ?? null;
        
        // Extract UTM parameters from URL
        $query = Router::getQuery();
        $data['utm_source'] = $query['utm_source'] ?? $data['utm_source'] ?? null;
        $data['utm_medium'] = $query['utm_medium'] ?? $data['utm_medium'] ?? null;
        $data['utm_campaign'] = $query['utm_campaign'] ?? $data['utm_campaign'] ?? null;
        
        return parent::create($data);
    }
    
    /**
     * Get leads by status
     */
    public function getByStatus(string $status, array $options = []): array
    {
        $options['where'] = array_merge($options['where'] ?? [], ['status' => $status]);
        return $this->all($options);
    }
    
    /**
     * Get leads assigned to user
     */
    public function getAssignedTo(int $userId, array $options = []): array
    {
        $options['where'] = array_merge($options['where'] ?? [], ['assigned_to' => $userId]);
        return $this->all($options);
    }
    
    /**
     * Get lead with full details
     */
    public function getWithDetails(int $id): ?array
    {
        $lead = $this->find($id);
        
        if (!$lead) {
            return null;
        }
        
        // Get solution info
        if ($lead['interest_solution_id']) {
            $sql = "SELECT title, slug FROM solutions WHERE id = ?";
            $solution = $this->db->fetchOne($sql, [$lead['interest_solution_id']]);
            $lead['solution'] = $solution;
        }
        
        // Get assigned user
        if ($lead['assigned_to']) {
            $sql = "SELECT id, name, email FROM users WHERE id = ?";
            $user = $this->db->fetchOne($sql, [$lead['assigned_to']]);
            $lead['assigned_user'] = $user;
        }
        
        // Get demo requests
        $sql = "SELECT * FROM demo_requests WHERE lead_id = ? ORDER BY created_at DESC";
        $lead['demo_requests'] = $this->db->fetchAll($sql, [$id]);
        
        // Get pipeline
        $sql = "SELECT * FROM sales_pipeline WHERE lead_id = ?";
        $lead['pipeline'] = $this->db->fetchOne($sql, [$id]);
        
        return $lead;
    }
    
    /**
     * Update lead status
     */
    public function updateStatus(int $id, string $status): bool
    {
        return $this->update($id, ['status' => $status]);
    }
    
    /**
     * Assign lead to user
     */
    public function assign(int $id, int $userId): bool
    {
        return $this->update($id, [
            'assigned_to' => $userId,
            'status' => 'contacted'
        ]);
    }
    
    /**
     * Calculate lead score
     */
    public function calculateScore(int $id): int
    {
        $lead = $this->find($id);
        if (!$lead) {
            return 0;
        }
        
        $score = 0;
        
        // Company size score
        $sizeScores = [
            '1-10' => 10,
            '11-50' => 15,
            '51-200' => 20,
            '201-500' => 25,
            '500+' => 30
        ];
        $score += $sizeScores[$lead['company_size']] ?? 5;
        
        // Demo request score
        $sql = "SELECT COUNT(*) as count FROM demo_requests WHERE lead_id = ?";
        $result = $this->db->fetchOne($sql, [$id]);
        if ($result && $result['count'] > 0) {
            $score += 20;
        }
        
        // Message completeness
        if (!empty($lead['message']) && strlen($lead['message']) > 100) {
            $score += 10;
        }
        
        // Phone provided
        if (!empty($lead['phone'])) {
            $score += 10;
        }
        
        // Company provided
        if (!empty($lead['company'])) {
            $score += 10;
        }
        
        // Update score
        $this->update($id, ['score' => $score]);
        
        return $score;
    }
    
    /**
     * Get lead statistics
     */
    public function getStats(): array
    {
        $stats = [
            'total' => $this->count(),
            'by_status' => [],
            'by_source' => [],
            'by_month' => []
        ];
        
        // By status
        $sql = "SELECT status, COUNT(*) as count FROM {$this->table} GROUP BY status";
        $results = $this->db->fetchAll($sql);
        foreach ($results as $row) {
            $stats['by_status'][$row['status']] = (int) $row['count'];
        }
        
        // By source
        $sql = "SELECT source, COUNT(*) as count FROM {$this->table} GROUP BY source";
        $results = $this->db->fetchAll($sql);
        foreach ($results as $row) {
            $stats['by_source'][$row['source']] = (int) $row['count'];
        }
        
        // By month (last 6 months)
        $sql = "SELECT DATE_FORMAT(created_at, '%Y-%m') as month, COUNT(*) as count 
                FROM {$this->table} 
                WHERE created_at >= DATE_SUB(NOW(), INTERVAL 6 MONTH)
                GROUP BY month 
                ORDER BY month DESC";
        $results = $this->db->fetchAll($sql);
        foreach ($results as $row) {
            $stats['by_month'][$row['month']] = (int) $row['count'];
        }
        
        return $stats;
    }
    
    /**
     * Get high-value leads
     */
    public function getHighValue(int $limit = 10): array
    {
        $sql = "SELECT l.*, s.title as solution_title 
                FROM {$this->table} l 
                LEFT JOIN solutions s ON l.interest_solution_id = s.id 
                WHERE l.score >= 50 
                ORDER BY l.score DESC, l.created_at DESC 
                LIMIT ?";
        
        return $this->db->fetchAll($sql, [$limit]);
    }
    
    /**
     * Search leads
     */
    public function search(string $query, array $options = []): array
    {
        $sql = "SELECT * FROM {$this->table} 
                WHERE (first_name LIKE ? OR last_name LIKE ? OR email LIKE ? OR company LIKE ?)";
        
        $params = ["%{$query}%", "%{$query}%", "%{$query}%", "%{$query}%"];
        
        if (!empty($options['status'])) {
            $sql .= " AND status = ?";
            $params[] = $options['status'];
        }
        
        $sql .= " ORDER BY created_at DESC";
        
        if (!empty($options['limit'])) {
            $sql .= " LIMIT ?";
            $params[] = (int) $options['limit'];
        }
        
        return $this->db->fetchAll($sql, $params);
    }
}
