<?php
declare(strict_types=1);

namespace App\Models;

final class Quote extends BaseModel
{
    protected static string $table = 'quotes';

    public static function paginate(?int $ownerId, string $status, string $q, int $page, int $perPage, int $accountId = 0, string $created = 'all'): array
    {
        [$where, $params] = self::filters($ownerId, $status, $q, $accountId, $created);
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM quotes q INNER JOIN accounts a ON a.id = q.account_id WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT q.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM quotes q
             INNER JOIN accounts a ON a.id = q.account_id
             LEFT JOIN users u ON u.id = q.owner_id
             WHERE $where
             ORDER BY q.created_at DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function counts(?int $ownerId): array
    {
        [$where, $params] = self::filters($ownerId, 'all', '');
        $rows = static::db()->fetchAll("SELECT q.status, COUNT(*) n FROM quotes q INNER JOIN accounts a ON a.id = q.account_id WHERE $where GROUP BY q.status", $params);
        $out = ['all' => 0, 'draft' => 0, 'pending_approval' => 0, 'approved' => 0, 'rejected' => 0, 'sent' => 0, 'signed' => 0, 'void' => 0];
        foreach ($rows as $r) {
            $out[(string) $r['status']] = (int) $r['n'];
            $out['all'] += (int) $r['n'];
        }
        return $out;
    }

    public static function pendingApproval(): array
    {
        return static::db()->fetchAll(
            "SELECT q.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM quotes q
             INNER JOIN accounts a ON a.id = q.account_id
             LEFT JOIN users u ON u.id = q.owner_id
             WHERE q.tenant_id = :tid AND q.status = 'pending_approval'
             ORDER BY q.created_at",
            ['tid' => static::tenantId()]
        );
    }

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

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

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

    /** @return array{0:string,1:array<string,mixed>} */
    private static function filters(?int $ownerId, string $status, string $q, int $accountId = 0, string $created = 'all'): array
    {
        $where = 'q.tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $where .= ' AND q.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($status !== 'all' && $status !== '') {
            $where .= ' AND q.status = :st';
            $params['st'] = $status;
        }
        if ($accountId > 0) {
            $where .= ' AND q.account_id = :aid';
            $params['aid'] = $accountId;
        }
        if ($created === 'today') {
            $where .= ' AND DATE(q.created_at) = CURDATE()';
        } elseif ($created === 'week') {
            $where .= ' AND YEARWEEK(q.created_at, 1) = YEARWEEK(CURDATE(), 1)';
        } elseif ($created === 'month') {
            $where .= ' AND YEAR(q.created_at) = YEAR(CURDATE()) AND MONTH(q.created_at) = MONTH(CURDATE())';
        }
        if ($q !== '') {
            $where .= ' AND (q.title LIKE :q OR q.number LIKE :q2 OR a.name LIKE :q3)';
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
            $params['q3'] = $like;
        }
        return [$where, $params];
    }
}
