<?php
class Lead extends Model
{
    public function create(array $data): ?string
    {
        $sql = 'INSERT INTO leads (customer_id, name, email, phone, project_type, project_nature, services, project_size, floor_area, location, region, city, area, project_stage, timeline_start, timeline_end, site_visit, preferred_visit_date, preferred_visit_time, preferred_contact_method, whatsapp, notes, status, created_at) VALUES (:customer_id, :name, :email, :phone, :project_type, :project_nature, :services, :project_size, :floor_area, :location, :region, :city, :area, :project_stage, :timeline_start, :timeline_end, :site_visit, :preferred_visit_date, :preferred_visit_time, :preferred_contact_method, :whatsapp, :notes, :status, NOW())';
        $this->execute($sql, [
            'customer_id' => $data['customer_id'] ?? null,
            'name' => $data['name'],
            'email' => $data['email'],
            'phone' => $data['phone'],
            'project_type' => $data['project_type'] ?? $data['property_type'] ?? '',
            'project_nature' => $data['project_nature'] ?? '',
            'services' => $data['services'] ?? '',
            'project_size' => $data['project_size'] ?? ($data['floor_area'] ? $data['floor_area'] . ' sqm' : ''),
            'floor_area' => $data['floor_area'] ?? null,
            'location' => $data['location'] ?? '',
            'region' => $data['region'] ?? null,
            'city' => $data['city'] ?? null,
            'area' => $data['area'] ?? null,
            'project_stage' => $data['project_stage'] ?? null,
            'timeline_start' => $data['timeline_start'] ?? null,
            'timeline_end' => $data['timeline_end'] ?? null,
            'site_visit' => $data['site_visit'] ?? 'No',
            'preferred_visit_date' => $data['preferred_visit_date'] ?? null,
            'preferred_visit_time' => $data['preferred_visit_time'] ?? null,
            'preferred_contact_method' => $data['preferred_contact_method'] ?? null,
            'whatsapp' => $data['whatsapp'] ?? null,
            'notes' => $data['notes'] ?? null,
            'status' => 'New',
        ]);
        return $this->lastInsertId();
    }

    public function all(): array
    {
        $leads = $this->fetchAll('SELECT * FROM leads ORDER BY created_at DESC');
        foreach ($leads as &$lead) {
            $lead['uploads'] = $this->extractUploads($lead['notes'] ?? '');
        }
        return $leads;
    }

    private function extractUploads(string $notes): array
    {
        $uploads = [];
        if (preg_match_all('/storage\/uploads\/[a-zA-Z0-9_\-\.]+\.[a-zA-Z0-9]{1,5}/', $notes, $matches)) {
            $uploads = $matches[0];
        }
        return $uploads;
    }

    public function getUploads(string $notes): array
    {
        return $this->extractUploads($notes);
    }

    public function findById(int $id): ?array
    {
        $lead = $this->fetch('SELECT * FROM leads WHERE id = :id LIMIT 1', ['id' => $id]);
        if ($lead) {
            $lead['uploads'] = $this->extractUploads($lead['notes'] ?? '');
        }
        return $lead;
    }

    public function countByStatus(string $status): int
    {
        $result = $this->fetch('SELECT COUNT(*) AS total FROM leads WHERE status = :status', ['status' => $status]);
        return (int)($result['total'] ?? 0);
    }

    public function appendNotes(int $leadId, string $extra): bool
    {
        $lead = $this->fetch('SELECT notes FROM leads WHERE id = :id', ['id' => $leadId]);
        $existing = $lead['notes'] ?? '';
        $combined = trim($existing . "\n\n" . $extra);
        return (bool)$this->execute('UPDATE leads SET notes = :notes, updated_at = NOW() WHERE id = :id', ['notes' => $combined, 'id' => $leadId]);
    }

    public function updateStatus(int $leadId, string $status): bool
    {
        return $this->execute('UPDATE leads SET status = :status, updated_at = NOW() WHERE id = :id', ['status' => $status, 'id' => $leadId]);
    }

    public function delete(int $id): bool
    {
        return $this->execute('DELETE FROM leads WHERE id = :id', ['id' => $id]);
    }
}
