<?php
require_once __DIR__ . '/../Core.php';
require_once __DIR__ . '/Segment.php';
require_once __DIR__ . '/Account.php';
require_once __DIR__ . '/Pipeline.php';

class Contact extends Model
{
    public function all(): array
    {
        return $this->db->query('SELECT c.*, a.name AS account_name, p.name AS pipeline_name, u.name AS owner_name FROM contacts c LEFT JOIN accounts a ON a.id = c.account_id LEFT JOIN pipelines p ON p.id = c.pipeline_id LEFT JOIN users u ON u.id = c.owner_user_id ORDER BY c.created_at DESC')->fetchAll();
    }

    private function applySegmentFilter(array $segment, array &$where, array &$params, string $alias = 'c'): void
    {
        $prefix = rtrim($alias, '.') . '.';
        if (!empty($segment['tag_filter'])) {
            $where[] = $prefix . 'tags LIKE ?';
            $params[] = '%' . $segment['tag_filter'] . '%';
        }
        if (!empty($segment['company_filter'])) {
            $where[] = $prefix . 'company LIKE ?';
            $params[] = '%' . $segment['company_filter'] . '%';
        }
        $rules = json_decode((string)($segment['rule_json'] ?? '{}'), true) ?: [];
        if (!empty($rules['lifecycle_stage'])) {
            $where[] = $prefix . 'lifecycle_stage = ?';
            $params[] = $rules['lifecycle_stage'];
        }
        if (!empty($rules['minimum_score'])) {
            $where[] = $prefix . 'score >= ?';
            $params[] = (int)$rules['minimum_score'];
        }
        if (($segment['channel'] ?? 'all') === 'sms') {
            $where[] = $prefix . 'phone IS NOT NULL AND ' . $prefix . 'phone != "" AND ' . $prefix . 'unsubscribed_sms = 0';
        } elseif (($segment['channel'] ?? 'all') === 'email') {
            $where[] = $prefix . 'email IS NOT NULL AND ' . $prefix . 'email != "" AND ' . $prefix . 'unsubscribed_email = 0';
        }
    }

    private function cleanString(mixed $value): ?string
    {
        $value = trim((string)$value);
        return $value !== '' ? $value : null;
    }

    private function normalizePhoneCandidates(?string $phone): array
    {
        $normalized = normalize_phone_address((string)$phone);
        if ($normalized === '') {
            return [];
        }
        $candidates = [$normalized];
        if (strlen($normalized) === 10) {
            $candidates[] = '1' . $normalized;
        } elseif (strlen($normalized) === 11 && str_starts_with($normalized, '1')) {
            $candidates[] = substr($normalized, 1);
        }
        return array_values(array_unique(array_filter($candidates)));
    }

    private function normalizedPhoneSql(string $field = 'c.phone'): string
    {
        return "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(COALESCE({$field}, '')), ' ', ''), '-', ''), '(', ''), ')', ''), '+', ''), '.', ''), '/', '')";
    }

    private function sanitizePayload(array $data): array
    {
        $email = $this->cleanString($data['email'] ?? null);
        $phone = $this->cleanString($data['phone'] ?? null);
        $tags = $this->cleanString($data['tags'] ?? null);

        return [
            'first_name' => $this->cleanString($data['first_name'] ?? null) ?? 'Contact',
            'last_name' => $this->cleanString($data['last_name'] ?? null) ?? '',
            'phone' => $phone,
            'email' => $email ? normalize_address($email) : null,
            'company' => $this->cleanString($data['company'] ?? null),
            'tags' => $tags,
            'timezone' => $this->cleanString($data['timezone'] ?? null) ?? config('app.timezone', 'America/Chicago'),
            'lifecycle_stage' => $this->cleanString($data['lifecycle_stage'] ?? null) ?? 'lead',
            'score' => (int)($data['score'] ?? 0),
            'owner_user_id' => (int)($data['owner_user_id'] ?? 0) ?: null,
            'account_id' => (int)($data['account_id'] ?? 0) ?: null,
            'pipeline_id' => (int)($data['pipeline_id'] ?? 0) ?: null,
            'unsubscribed_sms' => !empty($data['unsubscribed_sms']) ? 1 : 0,
            'unsubscribed_email' => !empty($data['unsubscribed_email']) ? 1 : 0,
        ];
    }

    private function mergedTags(?string $current, ?string $incoming): ?string
    {
        $values = [];
        foreach ([explode(',', (string)$current), explode(',', (string)$incoming)] as $tagSet) {
            foreach ($tagSet as $tag) {
                $tag = trim((string)$tag);
                if ($tag === '') {
                    continue;
                }
                $values[strtolower($tag)] = $tag;
            }
        }
        return $values ? implode(', ', array_values($values)) : null;
    }

    private function fillMissingDetails(int $id, array $data): void
    {
        $existing = $this->find($id);
        if (!$existing) {
            return;
        }
        $payload = $this->sanitizePayload($data);
        $stmt = $this->db->prepare('UPDATE contacts SET first_name = ?, last_name = ?, phone = ?, email = ?, company = ?, tags = ?, timezone = ?, lifecycle_stage = ?, score = ?, owner_user_id = ?, account_id = ?, pipeline_id = ?, unsubscribed_sms = ?, unsubscribed_email = ?, updated_at = NOW() WHERE id = ?');
        $stmt->execute([
            $existing['first_name'] ?: $payload['first_name'],
            $existing['last_name'] ?: $payload['last_name'],
            $existing['phone'] ?: $payload['phone'],
            $existing['email'] ?: $payload['email'],
            $existing['company'] ?: $payload['company'],
            $this->mergedTags($existing['tags'] ?? null, $payload['tags'] ?? null),
            $existing['timezone'] ?: $payload['timezone'],
            $existing['lifecycle_stage'] ?: $payload['lifecycle_stage'],
            max((int)($existing['score'] ?? 0), (int)($payload['score'] ?? 0)),
            (int)($existing['owner_user_id'] ?? 0) ?: ($payload['owner_user_id'] ?? null),
            (int)($existing['account_id'] ?? 0) ?: ($payload['account_id'] ?? null),
            (int)($existing['pipeline_id'] ?? 0) ?: ($payload['pipeline_id'] ?? null),
            ((int)($existing['unsubscribed_sms'] ?? 0) || (int)($payload['unsubscribed_sms'] ?? 0)) ? 1 : 0,
            ((int)($existing['unsubscribed_email'] ?? 0) || (int)($payload['unsubscribed_email'] ?? 0)) ? 1 : 0,
            $id,
        ]);
    }

    public function search(string $query = '', string $channel = 'all', int $page = 1, int $perPage = 20, ?int $segmentId = null): array
    {
        $where = [];
        $params = [];
        if ($query !== '') {
            $where[] = '(c.first_name LIKE ? OR c.last_name LIKE ? OR c.email LIKE ? OR c.phone LIKE ? OR c.company LIKE ? OR c.tags LIKE ?)';
            for ($i = 0; $i < 6; $i++) {
                $params[] = '%' . $query . '%';
            }
        }
        if ($channel === 'sms') {
            $where[] = 'c.phone IS NOT NULL AND c.phone != ""';
        } elseif ($channel === 'email') {
            $where[] = 'c.email IS NOT NULL AND c.email != ""';
        }
        if ($segmentId) {
            $segment = (new Segment())->find($segmentId);
            if ($segment) {
                $this->applySegmentFilter($segment, $where, $params, 'c');
            }
        }
        $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';

        $countStmt = $this->db->prepare('SELECT COUNT(*) FROM contacts c ' . $whereSql);
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();
        $meta = paginate_meta($page, $perPage, $total);

        $stmt = $this->db->prepare('SELECT c.*, a.name AS account_name, p.name AS pipeline_name, u.name AS owner_name FROM contacts c LEFT JOIN accounts a ON a.id = c.account_id LEFT JOIN pipelines p ON p.id = c.pipeline_id LEFT JOIN users u ON u.id = c.owner_user_id ' . $whereSql . ' ORDER BY c.created_at DESC LIMIT ? OFFSET ?');
        $i = 1;
        foreach ($params as $param) {
            $stmt->bindValue($i++, $param, is_int($param) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->bindValue($i++, $meta['per_page'], PDO::PARAM_INT);
        $stmt->bindValue($i, $meta['offset'], PDO::PARAM_INT);
        $stmt->execute();
        return ['data' => $stmt->fetchAll(), 'meta' => $meta];
    }

    public function activeByChannel(string $channel, ?string $tag = null, ?int $segmentId = null): array
    {
        $where = [$channel === 'sms' ? 'c.phone IS NOT NULL AND c.phone != "" AND c.unsubscribed_sms = 0' : 'c.email IS NOT NULL AND c.email != "" AND c.unsubscribed_email = 0'];
        $params = [];
        if ($tag) {
            $where[] = 'tags LIKE ?';
            $params[] = '%' . $tag . '%';
        }
        if ($segmentId) {
            $segment = (new Segment())->find($segmentId);
            if ($segment) {
                $this->applySegmentFilter($segment, $where, $params, 'c');
            }
        }
        $stmt = $this->db->prepare('SELECT c.*, a.name AS account_name, p.name AS pipeline_name, u.name AS owner_name FROM contacts c LEFT JOIN accounts a ON a.id = c.account_id LEFT JOIN pipelines p ON p.id = c.pipeline_id LEFT JOIN users u ON u.id = c.owner_user_id WHERE ' . implode(' AND ', $where) . ' ORDER BY c.created_at DESC');
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function counts(): array
    {
        $row = $this->db->query('SELECT COUNT(*) total, SUM(c.unsubscribed_sms = 0 AND c.phone IS NOT NULL AND c.phone != "") sms_reachable, SUM(c.unsubscribed_email = 0 AND c.email IS NOT NULL AND c.email != "") email_reachable FROM contacts c')->fetch();
        return $row ?: ['total' => 0, 'sms_reachable' => 0, 'email_reachable' => 0];
    }

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT c.*, a.name AS account_name, p.name AS pipeline_name, u.name AS owner_name FROM contacts c LEFT JOIN accounts a ON a.id = c.account_id LEFT JOIN pipelines p ON p.id = c.pipeline_id LEFT JOIN users u ON u.id = c.owner_user_id WHERE c.id = ?');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public function findByAddress(string $channel, string $address): ?array
    {
        if ($channel === 'sms') {
            $candidates = $this->normalizePhoneCandidates($address);
            if (!$candidates) {
                return null;
            }
            $placeholders = implode(', ', array_fill(0, count($candidates), '?'));
            $sql = 'SELECT c.*, a.name AS account_name, p.name AS pipeline_name, u.name AS owner_name FROM contacts c LEFT JOIN accounts a ON a.id = c.account_id LEFT JOIN pipelines p ON p.id = c.pipeline_id LEFT JOIN users u ON u.id = c.owner_user_id WHERE ' . $this->normalizedPhoneSql('c.phone') . ' IN (' . $placeholders . ') ORDER BY c.updated_at DESC, c.id DESC LIMIT 1';
            $stmt = $this->db->prepare($sql);
            $stmt->execute($candidates);
            return $stmt->fetch() ?: null;
        }

        $address = normalize_address($address);
        if ($address === '') {
            return null;
        }
        $stmt = $this->db->prepare('SELECT c.*, a.name AS account_name, p.name AS pipeline_name, u.name AS owner_name FROM contacts c LEFT JOIN accounts a ON a.id = c.account_id LEFT JOIN pipelines p ON p.id = c.pipeline_id LEFT JOIN users u ON u.id = c.owner_user_id WHERE LOWER(TRIM(COALESCE(c.email, ""))) = ? ORDER BY c.updated_at DESC, c.id DESC LIMIT 1');
        $stmt->execute([$address]);
        return $stmt->fetch() ?: null;
    }

    public function findDuplicate(?string $email, ?string $phone): ?array
    {
        $existing = null;
        $email = normalize_address((string)$email);
        if ($email !== '') {
            $existing = $this->findByAddress('email', $email);
            if ($existing) {
                $existing['_matched_by'] = 'email';
                return $existing;
            }
        }
        $phoneCandidates = $this->normalizePhoneCandidates($phone);
        if ($phoneCandidates) {
            $existing = $this->findByAddress('sms', $phoneCandidates[0]);
            if ($existing) {
                $existing['_matched_by'] = 'phone';
                return $existing;
            }
        }
        return null;
    }

    public function create(array $data): bool
    {
        $data = $this->sanitizePayload($data);
        $stmt = $this->db->prepare('INSERT INTO contacts (first_name, last_name, phone, email, company, tags, timezone, lifecycle_stage, score, owner_user_id, account_id, pipeline_id, unsubscribed_sms, unsubscribed_email) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
        return $stmt->execute([
            $data['first_name'],
            $data['last_name'],
            $data['phone'],
            $data['email'],
            $data['company'],
            $data['tags'],
            $data['timezone'],
            $data['lifecycle_stage'],
            (int)$data['score'],
            $data['owner_user_id'],
            $data['account_id'],
            $data['pipeline_id'],
            (int)$data['unsubscribed_sms'],
            (int)$data['unsubscribed_email'],
        ]);
    }

    public function createOrReuse(array $data): array
    {
        $existing = $this->findDuplicate($data['email'] ?? null, $data['phone'] ?? null);
        if ($existing) {
            $this->fillMissingDetails((int)$existing['id'], $data);
            return [
                'id' => (int)$existing['id'],
                'created' => false,
                'matched_by' => $existing['_matched_by'] ?? 'email_or_phone',
            ];
        }

        $this->create($data);
        return [
            'id' => (int)$this->db->lastInsertId(),
            'created' => true,
            'matched_by' => null,
        ];
    }

    public function createAndGetId(array $data): int
    {
        $result = $this->createOrReuse($data);
        return (int)$result['id'];
    }

    public function importCsv(string $path): array
    {
        $handle = fopen($path, 'r');
        if (!$handle) {
            throw new RuntimeException('Unable to open CSV file.');
        }
        $headers = fgetcsv($handle) ?: [];
        $headers = array_map(fn($h) => strtolower(trim((string)$h)), $headers);
        $stats = ['processed' => 0, 'new' => 0, 'existing' => 0, 'skipped' => 0];
        while (($row = fgetcsv($handle)) !== false) {
            $record = array_combine($headers, array_pad($row, count($headers), '')) ?: [];
            if (empty($record['first_name']) && empty($record['email']) && empty($record['phone'])) {
                $stats['skipped']++;
                continue;
            }
            $result = $this->createOrReuse([
                'first_name' => $record['first_name'] ?? 'Contact',
                'last_name' => $record['last_name'] ?? '',
                'phone' => $record['phone'] ?? null,
                'email' => $record['email'] ?? null,
                'company' => $record['company'] ?? null,
                'tags' => $record['tags'] ?? null,
                'timezone' => $record['timezone'] ?? config('app.timezone', 'America/Chicago'),
                'lifecycle_stage' => $record['lifecycle_stage'] ?? 'lead',
                'score' => (int)($record['score'] ?? 0),
            ]);
            $stats['processed']++;
            $stats[$result['created'] ? 'new' : 'existing']++;
        }
        fclose($handle);
        return $stats;
    }

    public function setUnsubscribed(string $channel, string $address, bool $value = true): bool
    {
        $field = $channel === 'sms' ? 'unsubscribed_sms' : 'unsubscribed_email';
        $keySql = $channel === 'sms' ? $this->normalizedPhoneSql('phone') : 'LOWER(TRIM(COALESCE(email, "")))';
        $values = $channel === 'sms' ? $this->normalizePhoneCandidates($address) : [normalize_address($address)];
        if (!$values || $values[0] === '') {
            return false;
        }
        $placeholders = implode(', ', array_fill(0, count($values), '?'));
        $stmt = $this->db->prepare("UPDATE contacts SET {$field} = ?, updated_at = NOW() WHERE {$keySql} IN ({$placeholders})");
        return $stmt->execute(array_merge([$value ? 1 : 0], $values));
    }

    public function delete(int $id): bool
    {
        $stmt = $this->db->prepare('DELETE FROM contacts WHERE id = ?');
        return $stmt->execute([$id]);
    }

    public function exportRows(string $query = '', string $channel = 'all', ?int $segmentId = null): array
    {
        return $this->search($query, $channel, 1, 10000, $segmentId)['data'];
    }

    public function activityTimeline(int $contactId, int $limit = 80): array
    {
        $stmt = $this->db->prepare("SELECT 'message' AS item_type, id, channel, direction, status, subject, body, created_at, NULL AS event_type, NULL AS event_value FROM messages WHERE contact_id = ? UNION ALL SELECT 'event' AS item_type, me.id, m.channel, m.direction, me.event_type AS status, m.subject, m.body, me.created_at, me.event_type, me.event_value FROM message_events me INNER JOIN messages m ON m.id = me.message_id WHERE m.contact_id = ? ORDER BY created_at DESC LIMIT ?");
        $stmt->bindValue(1, $contactId, PDO::PARAM_INT);
        $stmt->bindValue(2, $contactId, PDO::PARAM_INT);
        $stmt->bindValue(3, $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function engagementSummary(int $contactId): array
    {
        $stmt = $this->db->prepare("SELECT COUNT(*) AS total_messages, SUM(direction='outbound') AS outbound_total, SUM(direction='inbound') AS inbound_total, SUM(status='delivered') AS delivered_total, (SELECT COUNT(*) FROM message_events me INNER JOIN messages m2 ON m2.id = me.message_id WHERE m2.contact_id = ? AND me.event_type='open') AS open_total, (SELECT COUNT(*) FROM message_events me INNER JOIN messages m3 ON m3.id = me.message_id WHERE m3.contact_id = ? AND me.event_type='click') AS click_total FROM messages WHERE contact_id = ?");
        $stmt->execute([$contactId, $contactId, $contactId]);
        return $stmt->fetch() ?: [];
    }

    public function updateLifecycleProfile(int $id, array $data): bool
    {
        $stmt = $this->db->prepare('UPDATE contacts SET lifecycle_stage = ?, score = ?, owner_user_id = ?, account_id = ?, pipeline_id = ?, updated_at = NOW() WHERE id = ?');
        return $stmt->execute([$data['lifecycle_stage'] ?? 'lead', (int)($data['score'] ?? 0), (int)($data['owner_user_id'] ?? 0) ?: null, (int)($data['account_id'] ?? 0) ?: null, (int)($data['pipeline_id'] ?? 0) ?: null, $id]);
    }

    public function stageBreakdown(): array
    {
        return $this->db->query('SELECT lifecycle_stage, COUNT(*) total FROM contacts GROUP BY lifecycle_stage ORDER BY total DESC, lifecycle_stage ASC')->fetchAll();
    }

    public function pipelineBreakdown(): array
    {
        return $this->db->query('SELECT COALESCE(p.name, "Unassigned") pipeline_name, COUNT(*) total FROM contacts c LEFT JOIN pipelines p ON p.id = c.pipeline_id GROUP BY COALESCE(p.name, "Unassigned") ORDER BY total DESC, pipeline_name ASC')->fetchAll();
    }

    public function accountBreakdown(): array
    {
        $stmt = $this->db->prepare('SELECT COALESCE(a.name, company, "Unlinked") account_name, COUNT(*) total FROM contacts c LEFT JOIN accounts a ON a.id = c.account_id GROUP BY COALESCE(a.name, company, "Unlinked") ORDER BY total DESC, account_name ASC LIMIT 12');
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function pipelineStageReport(): array
    {
        $sql = 'SELECT COALESCE(p.name, "Unassigned") AS pipeline_name, c.lifecycle_stage, COUNT(*) AS total, AVG(c.score) AS avg_score FROM contacts c LEFT JOIN pipelines p ON p.id = c.pipeline_id GROUP BY COALESCE(p.name, "Unassigned"), c.lifecycle_stage ORDER BY pipeline_name ASC, total DESC';
        return $this->db->query($sql)->fetchAll();
    }

    public function pipelineStageMatrix(): array
    {
        $sql = "SELECT COALESCE(p.name, 'Unassigned') AS pipeline_name,
            COUNT(*) AS total_contacts,
            SUM(c.lifecycle_stage = 'lead') AS lead_count,
            SUM(c.lifecycle_stage = 'qualified') AS qualified_count,
            SUM(c.lifecycle_stage IN ('proposal','opportunity')) AS proposal_count,
            SUM(c.lifecycle_stage = 'customer') AS customer_count,
            ROUND(AVG(c.score), 1) AS avg_score,
            ROUND(100 * SUM(c.lifecycle_stage = 'customer') / NULLIF(COUNT(*), 0), 1) AS customer_rate
            FROM contacts c
            LEFT JOIN pipelines p ON p.id = c.pipeline_id
            GROUP BY COALESCE(p.name, 'Unassigned')
            ORDER BY total_contacts DESC, pipeline_name ASC";
        return $this->db->query($sql)->fetchAll();
    }

    public function pipelineOwnerReport(int $limit = 15): array
    {
        $stmt = $this->db->prepare("SELECT COALESCE(p.name, 'Unassigned') AS pipeline_name, COALESCE(u.name, 'Unowned') AS owner_name, COUNT(*) AS total_contacts, ROUND(AVG(c.score), 1) AS avg_score
            FROM contacts c
            LEFT JOIN pipelines p ON p.id = c.pipeline_id
            LEFT JOIN users u ON u.id = c.owner_user_id
            GROUP BY COALESCE(p.name, 'Unassigned'), COALESCE(u.name, 'Unowned')
            ORDER BY total_contacts DESC, pipeline_name ASC, owner_name ASC
            LIMIT ?");
        $stmt->bindValue(1, $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }
}
