<?php
declare(strict_types=1);

namespace App\Models;

final class Contact extends BaseModel
{
    protected static string $table = 'contacts';
    protected static bool $softDelete = true;

    public static function paginate(string $q, int $page, int $perPage, ?int $accountId = null, string $filter = 'all'): array
    {
        $where = 'c.tenant_id = :tid AND c.deleted_at IS NULL';
        $params = ['tid' => static::tenantId()];
        if ($q !== '') {
            $where .= " AND (CONCAT(c.first_name, ' ', c.last_name) LIKE :q OR c.email LIKE :q2 OR a.name LIKE :q3)";
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
            $params['q3'] = $like;
        }
        if ($accountId) {
            $where .= ' AND c.account_id = :aid';
            $params['aid'] = $accountId;
        }
        if ($filter === 'primary') {
            $where .= ' AND c.is_primary = 1';
        } elseif ($filter === 'email') {
            $where .= " AND c.email IS NOT NULL AND c.email <> ''";
        } elseif ($filter === 'bounced') {
            $where .= " AND c.email_status = 'bounced'";
        }
        $from = 'FROM contacts c INNER JOIN accounts a ON a.id = c.account_id';
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) $from WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT c.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             $from
             LEFT JOIN users u ON u.id = c.owner_id
             WHERE $where
             ORDER BY c.is_primary DESC, c.last_name, c.first_name
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function forAccount(int $accountId): array
    {
        return static::db()->fetchAll(
            'SELECT * FROM contacts WHERE tenant_id = :tid AND account_id = :aid AND deleted_at IS NULL ORDER BY is_primary DESC, last_name',
            ['tid' => static::tenantId(), 'aid' => $accountId]
        );
    }

    public static function search(string $q, int $limit = 8): array
    {
        $like = '%' . $q . '%';
        return static::db()->fetchAll(
            "SELECT c.id, c.first_name, c.last_name, c.email, c.phone, a.name AS account_name
             FROM contacts c
             INNER JOIN accounts a ON a.id = c.account_id
             WHERE c.tenant_id = :tid AND c.deleted_at IS NULL
               AND (c.first_name LIKE :q OR c.last_name LIKE :q2 OR c.email LIKE :q3 OR c.phone LIKE :q4)
             ORDER BY c.last_name LIMIT $limit",
            ['tid' => static::tenantId(), 'q' => $like, 'q2' => $like, 'q3' => $like, 'q4' => $like]
        );
    }

    public static function detail(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT c.*, a.name AS account_name, a.status AS account_status, a.city AS account_city,
                    a.state AS account_state, a.industry AS account_industry, a.website AS account_website,
                    a.next_action AS account_next_action, a.next_action_at AS account_next_action_at,
                    a.email AS account_email, a.phone AS account_phone,
                    CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM contacts c
             INNER JOIN accounts a ON a.id = c.account_id
             LEFT JOIN users u ON u.id = c.owner_id
             WHERE c.id = :id AND c.tenant_id = :tid AND c.deleted_at IS NULL",
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }

    public static function boardCounts(): array
    {
        $sql = "SELECT
                    COUNT(*) AS all_n,
                    SUM(is_primary = 1) AS primary_n,
                    SUM(email IS NOT NULL AND email <> '') AS email_n,
                    SUM(email_status = 'bounced') AS bounced_n
                FROM contacts
                WHERE tenant_id = :tid AND deleted_at IS NULL";
        $row = static::db()->fetch($sql, ['tid' => static::tenantId()]) ?: [];
        return [
            'all' => (int) ($row['all_n'] ?? 0),
            'primary' => (int) ($row['primary_n'] ?? 0),
            'email' => (int) ($row['email_n'] ?? 0),
            'bounced' => (int) ($row['bounced_n'] ?? 0),
        ];
    }
}
