<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class Incident extends Model
{
    protected string $table = 'incidents';
    protected string $primaryKey = 'incident_id';

    public const CATEGORIES = ['equipment', 'external', 'health_safety'];
    public const SEVERITIES = ['low', 'medium', 'high', 'critical'];
    public const STATUSES   = ['draft', 'open', 'investigating', 'rca', 'closed_permanent', 'closed_temporary', 'cancelled'];

    private static function orderClause(string $sort): string
    {
        switch ($sort) {
            case 'oldest':       return ' ORDER BY i.created_at ASC';
            case 'start_desc':   return ' ORDER BY i.start_at DESC, i.created_at DESC';
            case 'severity':     return " ORDER BY FIELD(i.severity, 'critical', 'high', 'medium', 'low'), i.start_at DESC";
            case 'status_group': return " ORDER BY FIELD(i.status, 'open', 'investigating', 'rca', 'draft', 'closed_permanent', 'closed_temporary', 'cancelled'), i.start_at DESC";
            default:             return ' ORDER BY i.created_at DESC';
        }
    }

    public static function listForTenant(int $tenantId, array $filters = [], string $sort = 'newest'): array
    {
        $sql = 'SELECT i.*, it.name AS type_name, a.name AS asset_name, s.name AS site_name,
                       fc.code AS failure_code, fc.mode AS failure_mode,
                       u_assigned.username AS assigned_username,
                       u_reported.username AS reported_username
                  FROM incidents i
             LEFT JOIN incident_types it   ON it.incident_type_id = i.incident_type_id
             LEFT JOIN failure_codes fc    ON fc.failure_code_id  = i.failure_code_id
             LEFT JOIN assets a            ON a.asset_id          = i.asset_id
             LEFT JOIN sites s             ON s.site_id           = i.site_id
             LEFT JOIN users u_assigned    ON u_assigned.user_id  = i.assigned_to
             LEFT JOIN users u_reported    ON u_reported.user_id  = i.reported_by
                 WHERE i.tenant_id = :t AND i.deleted_at IS NULL';
        $params = ['t' => $tenantId];

        foreach (['category', 'severity', 'status'] as $col) {
            if (!empty($filters[$col])) {
                $sql .= " AND i.{$col} = :{$col}";
                $params[$col] = $filters[$col];
            }
        }
        if (!empty($filters['asset_id'])) {
            $sql .= ' AND i.asset_id = :aid';
            $params['aid'] = (int) $filters['asset_id'];
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (i.title LIKE :q1 OR i.code LIKE :q2)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
        }
        $sql .= self::orderClause($sort);
        return DB::all($sql, $params);
    }

    public static function findInTenant(int $tenantId, int $incidentId): ?array
    {
        return DB::one(
            'SELECT i.*, it.name AS type_name, a.name AS asset_name, s.name AS site_name,
                    fc.code AS failure_code, fc.mode AS failure_mode,
                    fc.mechanism AS failure_mechanism, fc.cause AS failure_cause,
                    u_assigned.username AS assigned_username,
                    u_reported.username AS reported_username
               FROM incidents i
          LEFT JOIN incident_types it ON it.incident_type_id = i.incident_type_id
          LEFT JOIN failure_codes fc  ON fc.failure_code_id  = i.failure_code_id
          LEFT JOIN assets a          ON a.asset_id          = i.asset_id
          LEFT JOIN sites s           ON s.site_id           = i.site_id
          LEFT JOIN users u_assigned  ON u_assigned.user_id  = i.assigned_to
          LEFT JOIN users u_reported  ON u_reported.user_id  = i.reported_by
              WHERE i.tenant_id = :t AND i.incident_id = :id AND i.deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $incidentId]
        );
    }

    public static function typesForTenant(int $tenantId, ?string $category = null): array
    {
        $sql = 'SELECT * FROM incident_types WHERE tenant_id = :t';
        $params = ['t' => $tenantId];
        if ($category !== null && $category !== '') {
            $sql .= ' AND category = :c';
            $params['c'] = $category;
        }
        $sql .= ' ORDER BY category, name';
        return DB::all($sql, $params);
    }

    public static function create(int $tenantId, int $userId, array $data): int
    {
        $payload = [
            'tenant_id'        => $tenantId,
            'category'         => in_array($data['category'] ?? '', self::CATEGORIES, true) ? $data['category'] : 'equipment',
            'incident_type_id' => !empty($data['incident_type_id']) ? (int) $data['incident_type_id'] : null,
            'failure_code_id'  => !empty($data['failure_code_id']) ? (int) $data['failure_code_id'] : null,
            'asset_id'         => !empty($data['asset_id']) ? (int) $data['asset_id'] : null,
            'site_id'          => !empty($data['site_id']) ? (int) $data['site_id'] : null,
            'code'             => $data['code']         ?? null,
            'title'            => $data['title'],
            'description'     => $data['description'] ?? null,
            'severity'         => in_array($data['severity'] ?? '', self::SEVERITIES, true) ? $data['severity'] : 'medium',
            'impact'           => $data['impact']       ?? null,
            'urgency'          => $data['urgency']      ?? null,
            'status'           => in_array($data['status'] ?? '', self::STATUSES, true) ? $data['status'] : 'draft',
            'reported_by'      => $userId,
            'assigned_to'      => !empty($data['assigned_to']) ? (int) $data['assigned_to'] : null,
            'start_at'         => !empty($data['start_at']) ? str_replace('T', ' ', $data['start_at']) : null,
            'end_at'           => !empty($data['end_at']) ? str_replace('T', ' ', $data['end_at']) : null,
            'downtime_minutes' => isset($data['downtime_minutes']) && $data['downtime_minutes'] !== '' ? (int) $data['downtime_minutes'] : null,
            'root_cause'       => $data['root_cause'] ?? null,
            'resolution'       => $data['resolution'] ?? null,
            'details_json'     => !empty($data['details_json']) ? (is_array($data['details_json']) ? json_encode($data['details_json']) : (string) $data['details_json']) : null,
        ];
        $id = (int) DB::insert('incidents', $payload);
        self::logEvent($id, $userId, 'status_change', null, $payload['status'], 'Incident created');
        return $id;
    }

    public static function update(int $tenantId, int $userId, int $incidentId, array $data): void
    {
        $existing = self::findInTenant($tenantId, $incidentId);
        if (!$existing) return;

        $allowed = ['category','incident_type_id','failure_code_id','asset_id','site_id','code','title','description',
                    'severity','impact','urgency','status','assigned_to','start_at','end_at','downtime_minutes','root_cause','resolution','details_json'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $incidentId];
        $statusChanged = false;
        $oldStatus = $existing['status'];

        foreach ($allowed as $col) {
            if (!array_key_exists($col, $data)) continue;
            $val = $data[$col];
            if (in_array($col, ['incident_type_id','failure_code_id','asset_id','site_id','assigned_to'], true)) {
                $val = !empty($val) ? (int) $val : null;
            }
            if ($col === 'downtime_minutes') {
                $val = ($val === '' || $val === null) ? null : (int) $val;
            }
            if (in_array($col, ['start_at','end_at'], true)) {
                $val = !empty($val) ? str_replace('T', ' ', (string) $val) : null;
            }
            if ($col === 'details_json' && is_array($val)) { $val = json_encode($val); }
            if ($col === 'status' && $val !== $oldStatus) { $statusChanged = true; }
            $set[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }

        if ($statusChanged && in_array($data['status'] ?? '', ['closed_permanent','closed_temporary','cancelled'], true)) {
            $set[] = '`closed_at` = NOW()';
        }

        if (!$set) return;
        DB::run(
            'UPDATE incidents SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND incident_id = :id',
            $params
        );

        if ($statusChanged) {
            self::logEvent($incidentId, $userId, 'status_change', $oldStatus, $data['status'], 'Status changed');
        }
    }

    public static function softDelete(int $tenantId, int $incidentId): void
    {
        DB::run(
            'UPDATE incidents SET deleted_at = NOW() WHERE tenant_id = :t AND incident_id = :id',
            ['t' => $tenantId, 'id' => $incidentId]
        );
    }

    public static function logEvent(int $incidentId, ?int $userId, string $eventType, ?string $from, ?string $to, ?string $message): void
    {
        DB::insert('incident_events', [
            'incident_id' => $incidentId,
            'user_id'     => $userId,
            'event_type'  => $eventType,
            'from_value'  => $from,
            'to_value'    => $to,
            'message'     => $message,
        ]);
    }

    public static function eventsFor(int $incidentId): array
    {
        return DB::all(
            'SELECT e.*, u.username FROM incident_events e
        LEFT JOIN users u ON u.user_id = e.user_id
             WHERE e.incident_id = :i
          ORDER BY e.created_at DESC',
            ['i' => $incidentId]
        );
    }

    public static function countsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS total,
                    SUM(status IN ("open","investigating","rca","draft")) AS open_count,
                    SUM(status = "open")             AS status_open,
                    SUM(severity = "critical")       AS critical,
                    SUM(status LIKE "closed_%")      AS closed
               FROM incidents WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?? ['total'=>0,'open_count'=>0,'status_open'=>0,'critical'=>0,'closed'=>0];
    }
}
