<?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';
        }
    }

    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
    {
        $field = $channel === 'sms' ? 'c.phone' : 'c.email';
        $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 {$field} = ? LIMIT 1");
        $stmt->execute([$address]);
        return $stmt->fetch() ?: null;
    }

    public function create(array $data): bool
    {
        $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'] ?? null,
            $data['email'] ?? null,
            $data['company'] ?? null,
            $data['tags'] ?? null,
            $data['timezone'] ?? config('app.timezone', 'America/Chicago'),
            $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,
            !empty($data['unsubscribed_sms']) ? 1 : 0,
            !empty($data['unsubscribed_email']) ? 1 : 0,
        ]);
    }


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

    public function importCsv(string $path): int
    {
        $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);
        $count = 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'])) {
                continue;
            }
            $this->create([
                '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),
            ]);
            $count++;
        }
        fclose($handle);
        return $count;
    }

    public function setUnsubscribed(string $channel, string $address, bool $value = true): bool
    {
        $field = $channel === 'sms' ? 'unsubscribed_sms' : 'unsubscribed_email';
        $key = $channel === 'sms' ? 'phone' : 'email';
        $stmt = $this->db->prepare("UPDATE contacts SET {$field} = ?, updated_at = NOW() WHERE {$key} = ?");
        return $stmt->execute([$value ? 1 : 0, $address]);
    }

    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();
    }
}


