<?php
declare(strict_types=1);

namespace App\Services;

use App\Core\Auth;
use App\Core\Database;
use App\Industry\Catalog;

final class IndustryReportService
{
    public static function panel(): array
    {
        $tpl = Catalog::current();
        $code = $tpl['code'];
        $rows = match ($code) {
            Catalog::DISTRIBUTION => self::reorders(),
            Catalog::SOFTWARE => self::renewals(),
            Catalog::CONTRACT => self::bids(),
            Catalog::SERVICE => self::visits(),
            default => [],
        };
        return [
            'title' => $tpl['report_title'],
            'hint' => $tpl['report_hint'],
            'code' => $code,
            'rows' => $rows,
        ];
    }

    public static function reorders(): array
    {
        $out = [];
        foreach (self::accounts() as $r) {
            $attrs = IndustryService::decodeAttrs($r['attrs'] ?? null);
            $last = (string) ($attrs['last_order_at'] ?? '');
            $cycle = (int) ($attrs['reorder_cycle_days'] ?? 90);
            if ($cycle < 1) {
                $cycle = 90;
            }
            if ($last === '' || strtotime($last) > strtotime('-' . $cycle . ' days')) {
                continue;
            }
            $r['last_order_at'] = $last;
            $r['reorder_cycle_days'] = $cycle;
            $out[] = $r;
        }
        return array_slice($out, 0, 40);
    }

    public static function renewals(): array
    {
        $out = [];
        $until = strtotime('+60 days');
        foreach (self::accounts() as $r) {
            $attrs = IndustryService::decodeAttrs($r['attrs'] ?? null);
            $renew = (string) ($attrs['renewal_date'] ?? '');
            if ($renew === '') {
                continue;
            }
            $ts = strtotime($renew);
            if ($ts !== false && $ts <= $until) {
                $r['renewal_date'] = $renew;
                $out[] = $r;
            }
        }
        return array_slice($out, 0, 40);
    }

    private static function accounts(): array
    {
        return Database::instance()->fetchAll(
            "SELECT a.id, a.name, a.attrs, CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM accounts a
             LEFT JOIN users u ON u.id = a.owner_id
             WHERE a.tenant_id = :tid AND a.deleted_at IS NULL AND a.status = 'active'
             ORDER BY a.name LIMIT 200",
            ['tid' => Auth::instance()->tenantId()]
        );
    }

    private static function bids(): array
    {
        return Database::instance()->fetchAll(
            "SELECT o.id, o.name, o.amount, o.attrs, a.name AS account_name, s.name AS stage_name
             FROM opportunities o
             INNER JOIN accounts a ON a.id = o.account_id
             INNER JOIN pipeline_stages s ON s.id = o.stage_id
             WHERE o.tenant_id = :tid AND o.deleted_at IS NULL AND s.is_lost = 0 AND s.is_won = 0
             ORDER BY o.close_date IS NULL, o.close_date ASC LIMIT 40",
            ['tid' => Auth::instance()->tenantId()]
        );
    }

    private static function visits(): array
    {
        return Database::instance()->fetchAll(
            "SELECT a.id, a.name, a.next_visit_at, a.last_visit_at,
                    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 a.next_visit_at IS NOT NULL
             ORDER BY a.next_visit_at ASC LIMIT 40",
            ['tid' => Auth::instance()->tenantId()]
        );
    }
}
