<?php
namespace App\Models;

use App\Core\Model;
use PDO;

class Contact extends Model {
    protected static string $table = 'contacts';

    public static function findByWhatsAppId(string $whatsappId): ?array {
        return self::firstWhere('whatsapp_id', $whatsappId);
    }

    public static function findByPhone(string $phone): ?array {
        $clean = preg_replace('/[^0-9]/', '', $phone);
        $stmt = self::db()->prepare("SELECT * FROM contacts WHERE REPLACE(REPLACE(phone_number, '+', ''), ' ', '') LIKE ? LIMIT 1");
        $stmt->execute(["%{$clean}%"]);
        $res = $stmt->fetch();
        return $res ?: null;
    }

    public static function getOrCreateByWhatsAppId(string $whatsappId, string $name = 'WhatsApp User'): array {
        $contact = self::findByWhatsAppId($whatsappId);
        if ($contact) {
            return $contact;
        }

        $id = self::create([
            'whatsapp_id'   => $whatsappId,
            'phone_number'  => '+' . ltrim($whatsappId, '+'),
            'name'          => !empty($name) ? $name : 'WhatsApp User',
            'stage_id'      => 1, // Default stage
            'source'        => 'whatsapp',
            'created_at'    => date('Y-m-d H:i:s')
        ]);

        return self::find($id);
    }

    public static function getWithDetails(int $id): ?array {
        $stmt = self::db()->prepare("
            SELECT c.*, 
                   ps.name as stage_name, ps.color as stage_color,
                   u.name as assigned_user_name,
                   conv.id as conversation_id, conv.status as conversation_status
            FROM contacts c
            LEFT JOIN pipeline_stages ps ON c.stage_id = ps.id
            LEFT JOIN users u ON c.assigned_to = u.id
            LEFT JOIN conversations conv ON c.id = conv.contact_id
            WHERE c.id = ?
            LIMIT 1
        ");
        $stmt->execute([$id]);
        $contact = $stmt->fetch();
        if ($contact) {
            $contact['tags'] = self::getTags($id);
        }
        return $contact ?: null;
    }

    public static function getTags(int $contactId): array {
        $stmt = self::db()->prepare("
            SELECT t.* FROM tags t
            JOIN contact_tags ct ON t.id = ct.tag_id
            WHERE ct.contact_id = ?
        ");
        $stmt->execute([$contactId]);
        return $stmt->fetchAll();
    }

    public static function syncTags(int $contactId, array $tagIds): void {
        $stmt = self::db()->prepare("DELETE FROM contact_tags WHERE contact_id = ?");
        $stmt->execute([$contactId]);

        if (!empty($tagIds)) {
            $insert = self::db()->prepare("INSERT INTO contact_tags (contact_id, tag_id) VALUES (?, ?)");
            foreach ($tagIds as $tagId) {
                $insert->execute([$contactId, (int)$tagId]);
            }
        }
    }

    public static function addTag(int $contactId, int $tagId): void {
        $stmt = self::db()->prepare("INSERT IGNORE INTO contact_tags (contact_id, tag_id) VALUES (?, ?)");
        $stmt->execute([$contactId, $tagId]);
    }

    public static function getAllWithFilters(array $filters = []): array {
        $sql = "
            SELECT c.*, 
                   ps.name as stage_name, ps.color as stage_color,
                   u.name as assigned_user_name,
                   conv.id as conversation_id, conv.unread_count
            FROM contacts c
            LEFT JOIN pipeline_stages ps ON c.stage_id = ps.id
            LEFT JOIN users u ON c.assigned_to = u.id
            LEFT JOIN conversations conv ON c.id = conv.contact_id
            WHERE 1=1
        ";
        $params = [];

        if (!empty($filters['search'])) {
            $sql .= " AND (c.name LIKE ? OR c.phone_number LIKE ? OR c.email LIKE ? OR c.company LIKE ?)";
            $searchVal = "%{$filters['search']}%";
            $params[] = $searchVal;
            $params[] = $searchVal;
            $params[] = $searchVal;
            $params[] = $searchVal;
        }

        if (!empty($filters['stage_id'])) {
            $sql .= " AND c.stage_id = ?";
            $params[] = (int)$filters['stage_id'];
        }

        if (!empty($filters['assigned_to'])) {
            $sql .= " AND c.assigned_to = ?";
            $params[] = (int)$filters['assigned_to'];
        }

        if (!empty($filters['tag_id'])) {
            $sql .= " AND c.id IN (SELECT contact_id FROM contact_tags WHERE tag_id = ?)";
            $params[] = (int)$filters['tag_id'];
        }

        $sql .= " ORDER BY c.updated_at DESC";

        $stmt = self::db()->prepare($sql);
        $stmt->execute($params);
        $contacts = $stmt->fetchAll();

        foreach ($contacts as &$contact) {
            $contact['tags'] = self::getTags((int)$contact['id']);
        }

        return $contacts;
    }
}
