<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

/**
 * Permit to Work + LOTO. A permit is tied to a permit_type (Hot Work,
 * Confined Space, LOTO, …) and optionally links to a Work Order — a WO
 * with permit_required=1 cannot start until at least one linked permit
 * is in status `approved` or `active`.
 */
final class Permit extends Model
{
    protected string $table = 'permits';
    protected string $primaryKey = 'permit_id';

    public const STATUSES = ['draft', 'requested', 'approved', 'rejected', 'active', 'closed', 'expired'];

    // ── Permit types ────────────────────────────────────────────────────

    public static function typesForTenant(int $tenantId): array
    {
        return DB::all(
            'SELECT permit_type_id, code, name, description, icon
               FROM permit_types
              WHERE tenant_id = :t AND is_active = 1
              ORDER BY name',
            ['t' => $tenantId]
        );
    }

    // ── Permits ─────────────────────────────────────────────────────────

    public static function listForTenant(int $tenantId, array $filters = []): array
    {
        $sql = 'SELECT p.*, pt.code AS type_code, pt.name AS type_name, pt.icon AS type_icon,
                       a.name AS asset_name, s.name AS site_name, w.title AS wo_title,
                       u_req.username AS requested_username,
                       u_app.username AS approved_username
                  FROM permits p
                  JOIN permit_types pt ON pt.permit_type_id = p.permit_type_id
             LEFT JOIN assets a       ON a.asset_id        = p.asset_id
             LEFT JOIN sites s        ON s.site_id         = p.site_id
             LEFT JOIN work_orders w  ON w.work_order_id   = p.work_order_id
             LEFT JOIN users u_req    ON u_req.user_id     = p.requested_by
             LEFT JOIN users u_app    ON u_app.user_id     = p.approved_by
                 WHERE p.tenant_id = :t AND p.deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if (!empty($filters['status'])) {
            $sql .= ' AND p.status = :s';
            $params['s'] = $filters['status'];
        }
        if (!empty($filters['permit_type_id'])) {
            $sql .= ' AND p.permit_type_id = :pt';
            $params['pt'] = (int) $filters['permit_type_id'];
        }
        $sql .= ' ORDER BY p.created_at DESC';
        return DB::all($sql, $params);
    }

    public static function findInTenant(int $tenantId, int $permitId): ?array
    {
        return DB::one(
            'SELECT p.*, pt.code AS type_code, pt.name AS type_name, pt.icon AS type_icon,
                    a.name AS asset_name, s.name AS site_name, w.title AS wo_title,
                    u_req.username AS requested_username,
                    u_app.username AS approved_username,
                    u_cls.username AS closed_username
               FROM permits p
               JOIN permit_types pt ON pt.permit_type_id = p.permit_type_id
          LEFT JOIN assets a       ON a.asset_id       = p.asset_id
          LEFT JOIN sites s        ON s.site_id        = p.site_id
          LEFT JOIN work_orders w  ON w.work_order_id  = p.work_order_id
          LEFT JOIN users u_req    ON u_req.user_id    = p.requested_by
          LEFT JOIN users u_app    ON u_app.user_id    = p.approved_by
          LEFT JOIN users u_cls    ON u_cls.user_id    = p.closed_by
              WHERE p.tenant_id = :t AND p.permit_id = :id AND p.deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $permitId]
        );
    }

    public static function forWorkOrder(int $tenantId, int $woId): array
    {
        return DB::all(
            'SELECT p.*, pt.code AS type_code, pt.name AS type_name, pt.icon AS type_icon,
                    u_app.username AS approved_username
               FROM permits p
               JOIN permit_types pt ON pt.permit_type_id = p.permit_type_id
          LEFT JOIN users u_app    ON u_app.user_id = p.approved_by
              WHERE p.tenant_id = :t AND p.work_order_id = :w AND p.deleted_at IS NULL
              ORDER BY p.created_at DESC',
            ['t' => $tenantId, 'w' => $woId]
        );
    }

    public static function hasApprovedForWo(int $tenantId, int $woId): bool
    {
        $n = (int) DB::value(
            'SELECT COUNT(*) FROM permits
              WHERE tenant_id = :t AND work_order_id = :w AND deleted_at IS NULL
                AND status IN ("approved", "active")
                AND (valid_to IS NULL OR valid_to >= NOW())',
            ['t' => $tenantId, 'w' => $woId]
        );
        return $n > 0;
    }

    public static function create(int $tenantId, ?int $userId, array $data): int
    {
        return (int) DB::insert('permits', [
            'tenant_id'      => $tenantId,
            'permit_type_id' => (int) $data['permit_type_id'],
            'work_order_id'  => !empty($data['work_order_id']) ? (int) $data['work_order_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,
            'hazards'        => $data['hazards']     ?? null,
            'controls'       => $data['controls']    ?? null,
            'ppe'            => $data['ppe']         ?? null,
            'lockout_tags'   => $data['lockout_tags']?? null,
            'valid_from'     => !empty($data['valid_from']) ? str_replace('T', ' ', $data['valid_from']) : null,
            'valid_to'       => !empty($data['valid_to'])   ? str_replace('T', ' ', $data['valid_to'])   : null,
            'requested_by'   => $userId,
            'status'         => 'requested',
        ]);
    }

    public static function update(int $tenantId, int $permitId, array $data): void
    {
        $allowed = ['permit_type_id','work_order_id','asset_id','site_id','code','title','description',
                    'hazards','controls','ppe','lockout_tags','valid_from','valid_to'];
        $set = [];
        $params = ['t' => $tenantId, 'id' => $permitId];
        foreach ($allowed as $col) {
            if (!array_key_exists($col, $data)) continue;
            $val = $data[$col];
            if (in_array($col, ['permit_type_id','work_order_id','asset_id','site_id'], true)) {
                $val = !empty($val) ? (int) $val : null;
            }
            if (in_array($col, ['valid_from','valid_to'], true)) {
                $val = !empty($val) ? str_replace('T', ' ', (string) $val) : null;
            }
            $set[] = "`{$col}` = :{$col}";
            $params[$col] = $val;
        }
        if (!$set) return;
        DB::run(
            'UPDATE permits SET ' . implode(', ', $set) . ' WHERE tenant_id = :t AND permit_id = :id',
            $params
        );
    }

    public static function approve(int $tenantId, int $permitId, int $userId): void
    {
        DB::run(
            'UPDATE permits SET status = "approved", approved_by = :u, approved_at = NOW(), rejection_note = NULL
              WHERE tenant_id = :t AND permit_id = :id',
            ['u' => $userId, 't' => $tenantId, 'id' => $permitId]
        );
    }

    public static function reject(int $tenantId, int $permitId, int $userId, ?string $note): void
    {
        DB::run(
            'UPDATE permits SET status = "rejected", approved_by = :u, approved_at = NOW(), rejection_note = :n
              WHERE tenant_id = :t AND permit_id = :id',
            ['u' => $userId, 'n' => $note ?: null, 't' => $tenantId, 'id' => $permitId]
        );
    }

    public static function activate(int $tenantId, int $permitId): void
    {
        DB::run(
            'UPDATE permits SET status = "active" WHERE tenant_id = :t AND permit_id = :id AND status = "approved"',
            ['t' => $tenantId, 'id' => $permitId]
        );
    }

    public static function close(int $tenantId, int $permitId, int $userId): void
    {
        DB::run(
            'UPDATE permits SET status = "closed", closed_by = :u, closed_at = NOW()
              WHERE tenant_id = :t AND permit_id = :id',
            ['u' => $userId, 't' => $tenantId, 'id' => $permitId]
        );
    }

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

    public static function countsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS total,
                    SUM(status = "requested") AS pending,
                    SUM(status IN ("approved","active")) AS active,
                    SUM(status = "expired") AS expired,
                    SUM(status = "closed") AS closed
               FROM permits WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?? ['total'=>0,'pending'=>0,'active'=>0,'expired'=>0,'closed'=>0];
    }
}
