<?php
declare(strict_types=1);

namespace App\Models;

final class ServiceJob extends BaseModel
{
    protected static string $table = 'service_jobs';

    public static function paginate(?int $assigneeId, string $status, string $q, int $page, int $perPage, string $source = 'all'): array
    {
        $where = 'j.tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if ($assigneeId) {
            $where .= ' AND j.assignee_id = :oid';
            $params['oid'] = $assigneeId;
        }
        if ($status !== 'all' && $status !== '') {
            $where .= ' AND j.status = :st';
            $params['st'] = $status;
        }
        if ($source !== 'all' && $source !== '') {
            $where .= ' AND j.source = :src';
            $params['src'] = $source;
        }
        if ($q !== '') {
            $where .= ' AND (j.subject LIKE :q OR j.number LIKE :q2 OR a.name LIKE :q3)';
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
            $params['q3'] = $like;
        }
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM service_jobs j INNER JOIN accounts a ON a.id = j.account_id WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT j.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS assignee_name
             FROM service_jobs j
             INNER JOIN accounts a ON a.id = j.account_id
             LEFT JOIN users u ON u.id = j.assignee_id
             WHERE $where
             ORDER BY FIELD(j.status,'open','in_progress','done','cancelled'), j.id DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function counts(?int $assigneeId): array
    {
        $where = 'tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if ($assigneeId) {
            $where .= ' AND assignee_id = :oid';
            $params['oid'] = $assigneeId;
        }
        $rows = static::db()->fetchAll("SELECT status, COUNT(*) n FROM service_jobs WHERE $where GROUP BY status", $params);
        $out = ['all' => 0, 'open' => 0, 'in_progress' => 0, 'done' => 0, 'cancelled' => 0];
        foreach ($rows as $r) {
            $out[(string) $r['status']] = (int) $r['n'];
            $out['all'] += (int) $r['n'];
        }
        return $out;
    }

    public static function detail(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT j.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS assignee_name
             FROM service_jobs j
             INNER JOIN accounts a ON a.id = j.account_id
             LEFT JOIN users u ON u.id = j.assignee_id
             WHERE j.id = :id AND j.tenant_id = :tid",
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }

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

    public static function forOpportunity(int $oppId): ?array
    {
        return static::db()->fetch(
            'SELECT * FROM service_jobs WHERE tenant_id = :tid AND opportunity_id = :oid ORDER BY id DESC LIMIT 1',
            ['tid' => static::tenantId(), 'oid' => $oppId]
        );
    }

    public static function customerOpen(int $accountId): array
    {
        return static::db()->fetchAll(
            "SELECT * FROM service_jobs WHERE tenant_id = :tid AND account_id = :aid AND source = 'customer' ORDER BY id DESC",
            ['tid' => static::tenantId(), 'aid' => $accountId]
        );
    }
}
