<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

/**
 * Triage layer that feeds into WorkOrder. Life-cycle:
 *   new → approved → converted (to a WO)  OR  new → rejected
 */
final class WorkRequest extends Model
{
    protected string $table = 'work_requests';
    protected string $primaryKey = 'request_id';

    public const STATUSES   = ['new', 'approved', 'rejected', 'converted'];
    public const PRIORITIES = ['low', 'medium', 'high', 'urgent'];

    public static function listForTenant(int $tenantId, array $filters = []): array
    {
        $sql = 'SELECT r.*, a.name AS asset_name, s.name AS site_name,
                       u_req.username AS requested_username,
                       u_rev.username AS reviewed_username
                  FROM work_requests r
             LEFT JOIN assets a         ON a.asset_id     = r.asset_id
             LEFT JOIN sites s          ON s.site_id      = r.site_id
             LEFT JOIN users u_req      ON u_req.user_id  = r.requested_by
             LEFT JOIN users u_rev      ON u_rev.user_id  = r.reviewed_by
                 WHERE r.tenant_id = :t AND r.deleted_at IS NULL';
        $params = ['t' => $tenantId];

        foreach (['status', 'priority'] as $col) {
            if (!empty($filters[$col])) {
                $sql .= " AND r.{$col} = :{$col}";
                $params[$col] = $filters[$col];
            }
        }
        if (!empty($filters['q'])) {
            $sql .= ' AND (r.title LIKE :q1 OR r.code LIKE :q2)';
            $q = '%' . $filters['q'] . '%';
            $params['q1'] = $q;
            $params['q2'] = $q;
        }
        $sql .= ' ORDER BY r.created_at DESC';
        return DB::all($sql, $params);
    }

    public static function findInTenant(int $tenantId, int $requestId): ?array
    {
        return DB::one(
            'SELECT r.*, a.name AS asset_name, s.name AS site_name,
                    u_req.username AS requested_username,
                    u_rev.username AS reviewed_username
               FROM work_requests r
          LEFT JOIN assets a    ON a.asset_id    = r.asset_id
          LEFT JOIN sites s     ON s.site_id     = r.site_id
          LEFT JOIN users u_req ON u_req.user_id = r.requested_by
          LEFT JOIN users u_rev ON u_rev.user_id = r.reviewed_by
              WHERE r.tenant_id = :t AND r.request_id = :id AND r.deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $requestId]
        );
    }

    public static function create(int $tenantId, ?int $requestedBy, array $data): int
    {
        return (int) DB::insert('work_requests', [
            'tenant_id'       => $tenantId,
            'code'            => $data['code'] ?? null,
            'title'           => $data['title'],
            'description'     => $data['description'] ?? null,
            'priority'        => in_array($data['priority'] ?? '', self::PRIORITIES, true) ? $data['priority'] : 'medium',
            'status'          => 'new',
            'asset_id'        => !empty($data['asset_id']) ? (int) $data['asset_id'] : null,
            'site_id'         => !empty($data['site_id'])  ? (int) $data['site_id']  : null,
            'requested_by'    => $requestedBy,
            'requested_email' => $data['requested_email'] ?? null,
        ]);
    }

    public static function markReviewed(int $tenantId, int $requestId, int $userId, string $status, ?string $note): void
    {
        if (!in_array($status, ['approved', 'rejected'], true)) return;
        DB::run(
            'UPDATE work_requests
                SET status = :s, reviewed_by = :u, reviewed_at = NOW(), review_note = :n
              WHERE tenant_id = :t AND request_id = :id',
            ['s' => $status, 'u' => $userId, 'n' => $note ?: null, 't' => $tenantId, 'id' => $requestId]
        );
    }

    public static function markConverted(int $tenantId, int $requestId, int $woId): void
    {
        DB::run(
            'UPDATE work_requests
                SET status = "converted", converted_wo_id = :wo
              WHERE tenant_id = :t AND request_id = :id',
            ['wo' => $woId, 't' => $tenantId, 'id' => $requestId]
        );
    }

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

    public static function countsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS total,
                    SUM(status = "new")       AS new_count,
                    SUM(status = "approved")  AS approved_count,
                    SUM(status = "rejected")  AS rejected_count,
                    SUM(status = "converted") AS converted_count
               FROM work_requests WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?? ['total' => 0, 'new_count' => 0, 'approved_count' => 0, 'rejected_count' => 0, 'converted_count' => 0];
    }
}
