<?php
declare(strict_types=1);

namespace App\Models;

final class Order extends BaseModel
{
    protected static string $table = 'orders';

    public static function paginate(?int $ownerId, string $status, string $q, int $page, int $perPage, int $accountId = 0): array
    {
        $where = 'o.tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $where .= ' AND o.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($status !== 'all' && $status !== '') {
            $where .= ' AND o.status = :st';
            $params['st'] = $status;
        }
        if ($accountId > 0) {
            $where .= ' AND o.account_id = :aid';
            $params['aid'] = $accountId;
        }
        if ($q !== '') {
            $where .= ' AND (o.title LIKE :q OR o.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 orders o INNER JOIN accounts a ON a.id = o.account_id WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT o.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM orders o
             INNER JOIN accounts a ON a.id = o.account_id
             LEFT JOIN users u ON u.id = o.owner_id
             WHERE $where
             ORDER BY o.ordered_at DESC, o.id DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

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

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

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

    public static function lastOrderAt(int $accountId): ?string
    {
        $v = static::db()->fetchColumn(
            'SELECT MAX(ordered_at) FROM orders WHERE tenant_id = :tid AND account_id = :aid AND status <> :st',
            ['tid' => static::tenantId(), 'aid' => $accountId, 'st' => 'cancelled']
        );
        return $v ? (string) $v : null;
    }

    /** @return array<int, string> account_id => last ordered_at */
    public static function lastOrderMap(): array
    {
        $rows = static::db()->fetchAll(
            "SELECT account_id, MAX(ordered_at) AS last_at
             FROM orders WHERE tenant_id = :tid AND status <> 'cancelled'
             GROUP BY account_id",
            ['tid' => static::tenantId()]
        );
        $out = [];
        foreach ($rows as $r) {
            $out[(int) $r['account_id']] = (string) $r['last_at'];
        }
        return $out;
    }
}
