<?php

declare(strict_types=1);

namespace App\Models;

class Lead extends BaseModel
{
    protected string $table = 'leads';
    protected string $primaryKey = 'id';

    public function findByEmailAndPhone(?string $email, ?string $phone, int $excludeId = 0): array
    {
        $conditions = [];
        $params = [];

        if ($email) {
            $conditions[] = 'email = :email';
            $params['email'] = $email;
        }
        if ($phone) {
            $conditions[] = 'phone = :phone';
            $params['phone'] = $phone;
        }

        if (empty($conditions)) {
            return [];
        }

        $sql = "SELECT * FROM {$this->table} WHERE (" . implode(' OR ', $conditions) . ")";
        if ($excludeId > 0) {
            $sql .= ' AND id != :exclude_id';
            $params['exclude_id'] = $excludeId;
        }
        $sql .= ' LIMIT 10';

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

    public function createLead(array $data): int
    {
        return $this->create($data);
    }

    public function getCompanies(int $eventId, ?int $userId = null, string $role = ''): array
    {
        $where = ['company IS NOT NULL', 'company != :empty'];
        $params = ['empty' => ''];
        if ($eventId > 0) {
            $where[] = 'event_id = :event_id';
            $params['event_id'] = $eventId;
        }
        if ($role !== 'super_admin' && $userId) {
            $where[] = 'created_by = :created_by';
            $params['created_by'] = $userId;
        }
        $whereClause = implode(' AND ', $where);
        $rows = $this->query(
            "SELECT DISTINCT company FROM {$this->table} WHERE {$whereClause} ORDER BY company ASC LIMIT 100",
            $params
        );
        return array_map(fn($r) => $r['company'], $rows);
    }

    public function getCities(int $eventId, ?int $userId = null, string $role = ''): array
    {
        $where = ['city IS NOT NULL', 'city != :empty'];
        $params = ['empty' => ''];
        if ($eventId > 0) {
            $where[] = 'event_id = :event_id';
            $params['event_id'] = $eventId;
        }
        if ($role !== 'super_admin' && $userId) {
            $where[] = 'created_by = :created_by';
            $params['created_by'] = $userId;
        }
        $whereClause = implode(' AND ', $where);
        $rows = $this->query(
            "SELECT DISTINCT city FROM {$this->table} WHERE {$whereClause} ORDER BY city ASC LIMIT 100",
            $params
        );
        return array_map(fn($r) => $r['city'], $rows);
    }

    public function findByEvent(int $eventId, array $filters = [], int $page = 1, int $perPage = 15): array
    {
        $where = [];
        $params = [];
        if ($eventId > 0) {
            $where[] = 'l.event_id = :event_id';
            $params['event_id'] = $eventId;
        }

        if (!empty($filters['status'])) {
            $where[] = 'l.status = :status';
            $params['status'] = $filters['status'];
        }

        if (!empty($filters['source'])) {
            $where[] = 'l.source = :source';
            $params['source'] = $filters['source'];
        }

        if (!empty($filters['classification'])) {
            $where[] = 'l.classification = :classification';
            $params['classification'] = $filters['classification'];
        }

        if (!empty($filters['client_type'])) {
            $where[] = 'JSON_CONTAINS(l.client_type, :client_type)';
            $params['client_type'] = json_encode($filters['client_type']);
        }

        if (!empty($filters['industry'])) {
            $where[] = 'JSON_CONTAINS(l.industry, :industry)';
            $params['industry'] = json_encode($filters['industry']);
        }

        if (!empty($filters['search'])) {
            $where[] = '(l.first_name LIKE :s1 OR l.last_name LIKE :s2 OR l.email LIKE :s3 OR l.company LIKE :s4)';
            $params['s1'] = '%' . $filters['search'] . '%';
            $params['s2'] = '%' . $filters['search'] . '%';
            $params['s3'] = '%' . $filters['search'] . '%';
            $params['s4'] = '%' . $filters['search'] . '%';
        }

        if (!empty($filters['created_by'])) {
            $where[] = 'l.created_by = :created_by';
            $params['created_by'] = (int) $filters['created_by'];
        }

        $perPage = max(1, $perPage);
        $whereClause = !empty($where) ? implode(' AND ', $where) : '1';
        $offset = ($page - 1) * $perPage;

        $total = (int) $this->queryOne(
            "SELECT COUNT(*) as cnt FROM {$this->table} l WHERE {$whereClause}",
            $params
        )['cnt'];

        $items = $this->query(
            "SELECT l.*, u.name as created_by_name, s.name as assigned_to_name FROM {$this->table} l
             LEFT JOIN users u ON l.created_by = u.id
             LEFT JOIN users s ON l.assigned_to = s.id
             WHERE {$whereClause}
             ORDER BY l.created_at DESC
             LIMIT {$perPage} OFFSET {$offset}",
            $params
        );

        return [
            'items' => $items,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'total_pages' => (int) ceil($total / max(1, $perPage)),
        ];
    }
}
