<?php
declare(strict_types=1);

namespace App\Models;

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

    public static function paginate(string $q, int $page, int $perPage, ?int $ownerId = null, string $status = 'active'): array
    {
        $tid = static::tenantId();
        $where = 'a.tenant_id = :tid AND a.deleted_at IS NULL';
        $params = ['tid' => $tid];
        if ($q !== '') {
            $where .= ' AND (a.name LIKE :q OR a.email LIKE :q2 OR a.phone LIKE :q3)';
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
            $params['q3'] = $like;
        }
        if ($ownerId) {
            $where .= ' AND a.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($status === 'active' || $status === 'inactive') {
            $where .= ' AND a.status = :st';
            $params['st'] = $status;
        }
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM accounts a WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT a.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name, t.name AS territory_name
             FROM accounts a
             LEFT JOIN users u ON u.id = a.owner_id
             LEFT JOIN territories t ON t.id = a.territory_id
             WHERE $where
             ORDER BY a.next_action_at IS NULL, a.next_action_at ASC, a.name
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function detail(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT a.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name, t.name AS territory_name
             FROM accounts a
             LEFT JOIN users u ON u.id = a.owner_id
             LEFT JOIN territories t ON t.id = a.territory_id
             WHERE a.id = :id AND a.tenant_id = :tid AND a.deleted_at IS NULL",
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }

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

    public static function allForSelect(): array
    {
        return static::db()->fetchAll(
            'SELECT id, name FROM accounts WHERE tenant_id = :tid AND deleted_at IS NULL AND status = :st ORDER BY name',
            ['tid' => static::tenantId(), 'st' => 'active']
        );
    }

    public static function visits(?int $assigneeId = null, array $filters = []): array
    {
        $sql = "SELECT a.*, CONCAT(u.first_name,' ',u.last_name) AS assignee_name
                FROM accounts a
                LEFT JOIN users u ON u.id = a.service_assignee_id
                WHERE a.tenant_id = :tid AND a.deleted_at IS NULL AND a.status = 'active'
                  AND " . self::listedVisitClause('a');
        $params = ['tid' => static::tenantId()];
        if ($assigneeId) {
            $sql .= ' AND a.service_assignee_id = :aid';
            $params['aid'] = $assigneeId;
        }
        $q = trim((string) ($filters['q'] ?? ''));
        if ($q !== '') {
            $sql .= ' AND (a.name LIKE :q OR a.city LIKE :q2 OR a.phone LIKE :q3 OR a.visit_subject LIKE :q4 OR a.visit_detail LIKE :q5)';
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
            $params['q3'] = $like;
            $params['q4'] = $like;
            $params['q5'] = $like;
        }
        $when = (string) ($filters['when'] ?? 'all');
        if ($when === 'attended') {
            $sql .= ' AND ' . self::attendedVisitClause('a');
        } elseif (in_array($when, ['today', 'late', 'soon'], true)) {
            $sql .= ' AND ' . self::openVisitClause('a');
            if ($when === 'today') {
                $sql .= ' AND DATE(a.next_visit_at) = CURDATE()';
            } elseif ($when === 'late') {
                $sql .= ' AND DATE(a.next_visit_at) < CURDATE()';
            } else {
                $sql .= ' AND DATE(a.next_visit_at) > CURDATE()';
            }
        }
        if (($filters['week'] ?? 'all') === 'this') {
            $sql .= $when === 'attended'
                ? ' AND YEARWEEK(a.last_visit_at, 1) = YEARWEEK(CURDATE(), 1)'
                : ' AND YEARWEEK(COALESCE(a.next_visit_at, a.last_visit_at), 1) = YEARWEEK(CURDATE(), 1)';
        }
        $sql .= ' ORDER BY CASE WHEN ' . self::openVisitClause('a') . ' THEN 0 ELSE 1 END,
                         a.next_visit_at ASC, a.last_visit_at DESC';
        return static::db()->fetchAll($sql, $params);
    }

    public static function listedVisitClause(string $alias = 'a'): string
    {
        $col = $alias !== '' ? $alias . '.' : '';
        return "({$col}next_visit_at IS NOT NULL OR {$col}last_visit_at IS NOT NULL)";
    }

    public static function attendedVisitClause(string $alias = 'a'): string
    {
        $col = $alias !== '' ? $alias . '.' : '';
        return "{$col}last_visit_at IS NOT NULL AND {$col}next_visit_at IS NULL";
    }

    public static function openVisitClause(string $alias = 'a'): string
    {
        $col = $alias !== '' ? $alias . '.' : '';
        return "{$col}next_visit_at IS NOT NULL";
    }

    public static function visitDetail(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT a.*, CONCAT(u.first_name,' ',u.last_name) AS assignee_name
             FROM accounts a
             LEFT JOIN users u ON u.id = a.service_assignee_id
             WHERE a.id = :id AND a.tenant_id = :tid AND a.deleted_at IS NULL",
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }

    public static function boardCounts(?int $ownerId = null): array
    {
        $sql = "SELECT
                    SUM(status = 'active') AS active_n,
                    SUM(status = 'inactive') AS inactive_n
                FROM accounts
                WHERE tenant_id = :tid AND deleted_at IS NULL";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $row = static::db()->fetch($sql, $params) ?: [];
        $active = (int) ($row['active_n'] ?? 0);
        $inactive = (int) ($row['inactive_n'] ?? 0);
        return [
            'active' => $active,
            'inactive' => $inactive,
            'all' => $active + $inactive,
        ];
    }
}
