<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

final class Calibration extends Model
{
    protected string $table = 'calibration_schedules';
    protected string $primaryKey = 'cal_schedule_id';

    public const RESULTS = ['pass', 'fail', 'adjusted'];

    public static function listForTenant(int $tenantId, array $filters = []): array
    {
        $sql = 'SELECT cs.*, a.name AS asset_name, s.name AS site_name,
                       DATEDIFF(cs.next_due_at, CURDATE()) AS days_to_due
                  FROM calibration_schedules cs
                  JOIN assets a  ON a.asset_id = cs.asset_id
             LEFT JOIN sites s   ON s.site_id  = a.site_id
                 WHERE cs.tenant_id = :t AND cs.deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if (!empty($filters['status'])) {
            if ($filters['status'] === 'overdue') {
                $sql .= ' AND cs.next_due_at IS NOT NULL AND cs.next_due_at < CURDATE()';
            } elseif ($filters['status'] === 'due_soon') {
                $sql .= ' AND cs.next_due_at BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)';
            } elseif ($filters['status'] === 'ok') {
                $sql .= ' AND (cs.next_due_at IS NULL OR cs.next_due_at > DATE_ADD(CURDATE(), INTERVAL 30 DAY))';
            }
        }
        $sql .= ' ORDER BY cs.next_due_at IS NULL, cs.next_due_at';
        return DB::all($sql, $params);
    }

    public static function findInTenant(int $tenantId, int $schedId): ?array
    {
        return DB::one(
            'SELECT cs.*, a.name AS asset_name
               FROM calibration_schedules cs
               JOIN assets a ON a.asset_id = cs.asset_id
              WHERE cs.tenant_id = :t AND cs.cal_schedule_id = :id AND cs.deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $schedId]
        );
    }

    public static function createSchedule(int $tenantId, array $data): int
    {
        $lastCal = !empty($data['last_calibrated_at']) ? $data['last_calibrated_at'] : null;
        $interval = (int) ($data['interval_days'] ?? 365);
        $nextDue = $lastCal
            ? date('Y-m-d', strtotime($lastCal . " +{$interval} days"))
            : (!empty($data['next_due_at']) ? $data['next_due_at'] : null);
        return (int) DB::insert('calibration_schedules', [
            'tenant_id'          => $tenantId,
            'asset_id'           => (int) $data['asset_id'],
            'name'               => $data['name'],
            'standard'           => $data['standard'] ?? null,
            'interval_days'      => $interval,
            'tolerance'          => $data['tolerance'] ?? null,
            'unit'               => $data['unit']      ?? null,
            'last_calibrated_at' => $lastCal,
            'next_due_at'        => $nextDue,
            'is_active'          => !empty($data['is_active']) ? 1 : 1,
            'notes'              => $data['notes'] ?? null,
        ]);
    }

    public static function updateSchedule(int $tenantId, int $schedId, array $data): void
    {
        DB::run(
            'UPDATE calibration_schedules
                SET asset_id = :aid, name = :n, standard = :st, interval_days = :iv,
                    tolerance = :tol, unit = :u, is_active = :a, notes = :nt
              WHERE tenant_id = :t AND cal_schedule_id = :id',
            [
                'aid' => (int) $data['asset_id'],
                'n'   => $data['name'],
                'st'  => $data['standard'] ?? null,
                'iv'  => (int) ($data['interval_days'] ?? 365),
                'tol' => $data['tolerance'] ?? null,
                'u'   => $data['unit']      ?? null,
                'a'   => !empty($data['is_active']) ? 1 : 0,
                'nt'  => $data['notes'] ?? null,
                't'   => $tenantId,
                'id'  => $schedId,
            ]
        );
    }

    public static function softDeleteSchedule(int $tenantId, int $schedId): void
    {
        DB::run(
            'UPDATE calibration_schedules SET deleted_at = NOW() WHERE tenant_id = :t AND cal_schedule_id = :id',
            ['t' => $tenantId, 'id' => $schedId]
        );
    }

    public static function recordsFor(int $schedId): array
    {
        return DB::all(
            'SELECT r.*, u.username AS performed_username
               FROM calibration_records r
          LEFT JOIN users u ON u.user_id = r.performed_by
              WHERE r.cal_schedule_id = :id
              ORDER BY r.performed_at DESC',
            ['id' => $schedId]
        );
    }

    /** Log a calibration event and roll the schedule forward by interval_days. */
    public static function addRecord(int $tenantId, int $schedId, int $userId, array $data): int
    {
        $sched = self::findInTenant($tenantId, $schedId);
        if (!$sched) return 0;

        $performedAt = !empty($data['performed_at']) ? str_replace('T', ' ', $data['performed_at']) : date('Y-m-d H:i:s');
        $performedDate = substr($performedAt, 0, 10);

        $recordId = (int) DB::insert('calibration_records', [
            'tenant_id'          => $tenantId,
            'cal_schedule_id'    => $schedId,
            'performed_by'       => $userId ?: null,
            'performed_at'       => $performedAt,
            'technician_name'    => $data['technician_name'] ?? null,
            'as_found'           => $data['as_found'] ?? null,
            'as_left'            => $data['as_left']  ?? null,
            'result'             => in_array($data['result'] ?? '', self::RESULTS, true) ? $data['result'] : 'pass',
            'certificate_number' => $data['certificate_number'] ?? null,
            'notes'              => $data['notes'] ?? null,
        ]);

        $interval = (int) $sched['interval_days'];
        $nextDue  = date('Y-m-d', strtotime($performedDate . " +{$interval} days"));

        DB::run(
            'UPDATE calibration_schedules
                SET last_calibrated_at = :lc, next_due_at = :nd
              WHERE tenant_id = :t AND cal_schedule_id = :id',
            ['lc' => $performedDate, 'nd' => $nextDue, 't' => $tenantId, 'id' => $schedId]
        );

        return $recordId;
    }

    public static function countsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS total,
                    SUM(next_due_at IS NOT NULL AND next_due_at < CURDATE()) AS overdue,
                    SUM(next_due_at BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)) AS due_soon,
                    SUM(next_due_at IS NULL OR next_due_at > DATE_ADD(CURDATE(), INTERVAL 30 DAY)) AS ok_count
               FROM calibration_schedules
              WHERE tenant_id = :t AND deleted_at IS NULL AND is_active = 1',
            ['t' => $tenantId]
        ) ?? ['total' => 0, 'overdue' => 0, 'due_soon' => 0, 'ok_count' => 0];
    }
}
