<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class FailureCode extends Model
{
    protected string $table = 'failure_codes';
    protected string $primaryKey = 'failure_code_id';

    public static function optionsForTenant(int $tenantId): array
    {
        return DB::all(
            'SELECT failure_code_id, code, mode, mechanism, cause
               FROM failure_codes
              WHERE tenant_id = :t
              ORDER BY code',
            ['t' => $tenantId]
        );
    }

    public static function findInTenant(int $tenantId, int $failureCodeId): ?array
    {
        return DB::one(
            'SELECT * FROM failure_codes WHERE tenant_id = :t AND failure_code_id = :id',
            ['t' => $tenantId, 'id' => $failureCodeId]
        );
    }

    /** MTBF/MTTR-style rollup — mean downtime and count per code for a window. */
    public static function analyticsForTenant(int $tenantId, ?string $from = null, ?string $to = null): array
    {
        $from = $from ?: date('Y-m-d', strtotime('-90 days'));
        $to   = $to   ?: date('Y-m-d');
        return DB::all(
            "SELECT fc.code, fc.mode, fc.mechanism, fc.cause,
                    COUNT(i.incident_id) AS n,
                    ROUND(AVG(i.downtime_minutes), 1) AS avg_downtime,
                    SUM(i.downtime_minutes) AS total_downtime
               FROM failure_codes fc
          LEFT JOIN incidents i ON i.failure_code_id = fc.failure_code_id
                                AND i.tenant_id     = fc.tenant_id
                                AND i.deleted_at IS NULL
                                AND i.start_at BETWEEN :from AND :to
              WHERE fc.tenant_id = :t
              GROUP BY fc.failure_code_id
              ORDER BY n DESC, total_downtime DESC",
            ['t' => $tenantId, 'from' => $from . ' 00:00:00', 'to' => $to . ' 23:59:59']
        );
    }
}
