<?php
declare(strict_types=1);

namespace App\Models;

final class Invoice extends BaseModel
{
    protected static string $table = 'invoices';

    public static function paginate(?int $ownerId, string $status, string $q, int $page, int $perPage, int $accountId = 0, string $due = 'all'): array
    {
        $where = 'i.tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $where .= ' AND i.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($status !== 'all' && $status !== '') {
            $where .= ' AND i.status = :st';
            $params['st'] = $status;
        }
        if ($accountId > 0) {
            $where .= ' AND i.account_id = :aid';
            $params['aid'] = $accountId;
        }
        if ($due === 'overdue') {
            $where .= " AND i.due_date IS NOT NULL AND i.due_date < CURDATE() AND i.status NOT IN ('paid','void')";
        } elseif ($due === 'week') {
            $where .= ' AND i.due_date IS NOT NULL AND YEARWEEK(i.due_date, 1) = YEARWEEK(CURDATE(), 1)';
        } elseif ($due === 'month') {
            $where .= ' AND i.due_date IS NOT NULL AND YEAR(i.due_date) = YEAR(CURDATE()) AND MONTH(i.due_date) = MONTH(CURDATE())';
        }
        if ($q !== '') {
            $where .= ' AND (i.title LIKE :q OR i.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 invoices i INNER JOIN accounts a ON a.id = i.account_id WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT i.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM invoices i
             INNER JOIN accounts a ON a.id = i.account_id
             LEFT JOIN users u ON u.id = i.owner_id
             WHERE $where
             ORDER BY i.due_date IS NULL, i.due_date, i.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 invoices WHERE $where GROUP BY status", $params);
        $out = ['all' => 0, 'unpaid' => 0, 'partial' => 0, 'paid' => 0, 'void' => 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 i.*, a.name AS account_name, CONCAT(u.first_name,' ',u.last_name) AS owner_name,
                    o.number AS order_number
             FROM invoices i
             INNER JOIN accounts a ON a.id = i.account_id
             LEFT JOIN users u ON u.id = i.owner_id
             LEFT JOIN orders o ON o.id = i.order_id
             WHERE i.id = :id AND i.tenant_id = :tid",
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }

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

    public static function markPaid(int $id, float $amount): void
    {
        $row = static::find($id);
        if (!$row) {
            return;
        }
        $paid = min((float) $row['amount'], (float) $row['paid_amount'] + $amount);
        $status = $paid <= 0 ? 'unpaid' : ($paid + 0.009 >= (float) $row['amount'] ? 'paid' : 'partial');
        static::update($id, [
            'paid_amount' => $paid,
            'status' => $status,
            'paid_at' => $status === 'paid' ? date('Y-m-d H:i:s') : ($row['paid_at'] ?? null),
        ]);
    }
}
