<?php
declare(strict_types=1);

namespace App\Models;

use Core\DB;
use Core\Model;

/**
 * Training courses + per-user completion records. The record's `expires_at`
 * is auto-computed as `completed_at + validity_months` from the course, so
 * the expiring-soon dashboard tile is just a single date query.
 */
final class Training extends Model
{
    protected string $table = 'training_courses';
    protected string $primaryKey = 'course_id';

    // ── Courses ─────────────────────────────────────────────────────────

    public static function coursesForTenant(int $tenantId, bool $activeOnly = false): array
    {
        $sql = 'SELECT c.*,
                       (SELECT COUNT(*) FROM training_records tr
                          WHERE tr.course_id = c.course_id AND tr.deleted_at IS NULL) AS record_count
                  FROM training_courses c
                 WHERE c.tenant_id = :t AND c.deleted_at IS NULL';
        if ($activeOnly) $sql .= ' AND c.is_active = 1';
        $sql .= ' ORDER BY c.name';
        return DB::all($sql, ['t' => $tenantId]);
    }

    public static function findCourse(int $tenantId, int $courseId): ?array
    {
        return DB::one(
            'SELECT * FROM training_courses WHERE tenant_id = :t AND course_id = :id AND deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $courseId]
        );
    }

    public static function createCourse(int $tenantId, array $data): int
    {
        return (int) DB::insert('training_courses', [
            'tenant_id'       => $tenantId,
            'code'            => $data['code']        ?? null,
            'name'            => $data['name'],
            'description'     => $data['description'] ?? null,
            'validity_months' => (int) ($data['validity_months'] ?? 12),
            'issuer'          => $data['issuer']      ?? null,
            'is_active'       => !empty($data['is_active']) ? 1 : 1,
        ]);
    }

    public static function updateCourse(int $tenantId, int $courseId, array $data): void
    {
        DB::run(
            'UPDATE training_courses
                SET code = :c, name = :n, description = :d, validity_months = :v,
                    issuer = :i, is_active = :a
              WHERE tenant_id = :t AND course_id = :id',
            [
                'c' => $data['code'] ?? null, 'n' => $data['name'],
                'd' => $data['description'] ?? null,
                'v' => (int) ($data['validity_months'] ?? 12),
                'i' => $data['issuer'] ?? null,
                'a' => !empty($data['is_active']) ? 1 : 0,
                't' => $tenantId, 'id' => $courseId,
            ]
        );
    }

    public static function softDeleteCourse(int $tenantId, int $courseId): void
    {
        DB::run(
            'UPDATE training_courses SET deleted_at = NOW() WHERE tenant_id = :t AND course_id = :id',
            ['t' => $tenantId, 'id' => $courseId]
        );
    }

    // ── Records ─────────────────────────────────────────────────────────

    public static function recordsForTenant(int $tenantId, array $filters = []): array
    {
        $sql = 'SELECT r.*, c.code AS course_code, c.name AS course_name, c.validity_months,
                       u.username, u.f_name, u.l_name,
                       DATEDIFF(r.expires_at, CURDATE()) AS days_to_expiry
                  FROM training_records r
                  JOIN training_courses c ON c.course_id = r.course_id
             LEFT JOIN users u             ON u.user_id   = r.user_id
                 WHERE r.tenant_id = :t AND r.deleted_at IS NULL';
        $params = ['t' => $tenantId];
        if (!empty($filters['course_id'])) {
            $sql .= ' AND r.course_id = :cid';
            $params['cid'] = (int) $filters['course_id'];
        }
        if (!empty($filters['user_id'])) {
            $sql .= ' AND r.user_id = :uid';
            $params['uid'] = (int) $filters['user_id'];
        }
        if (!empty($filters['status'])) {
            if ($filters['status'] === 'expired') {
                $sql .= ' AND r.expires_at IS NOT NULL AND r.expires_at < CURDATE()';
            } elseif ($filters['status'] === 'expiring_soon') {
                $sql .= ' AND r.expires_at BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)';
            } elseif ($filters['status'] === 'valid') {
                $sql .= ' AND r.expires_at > DATE_ADD(CURDATE(), INTERVAL 60 DAY)';
            }
        }
        $sql .= ' ORDER BY r.expires_at IS NULL, r.expires_at';
        return DB::all($sql, $params);
    }

    public static function findRecord(int $tenantId, int $recordId): ?array
    {
        return DB::one(
            'SELECT r.*, c.code AS course_code, c.name AS course_name, c.validity_months,
                    u.username, u.f_name, u.l_name
               FROM training_records r
               JOIN training_courses c ON c.course_id = r.course_id
          LEFT JOIN users u             ON u.user_id   = r.user_id
              WHERE r.tenant_id = :t AND r.record_id = :id AND r.deleted_at IS NULL',
            ['t' => $tenantId, 'id' => $recordId]
        );
    }

    public static function createRecord(int $tenantId, array $data): int
    {
        $course = self::findCourse($tenantId, (int) $data['course_id']);
        if (!$course) return 0;

        $completed = $data['completed_at'];
        $validity  = (int) $course['validity_months'];
        $expires   = $validity > 0 ? date('Y-m-d', strtotime($completed . " +{$validity} months")) : null;

        return (int) DB::insert('training_records', [
            'tenant_id'          => $tenantId,
            'course_id'          => (int) $data['course_id'],
            'user_id'            => !empty($data['user_id']) ? (int) $data['user_id'] : null,
            'external_name'      => $data['external_name'] ?? null,
            'completed_at'       => $completed,
            'expires_at'         => $expires,
            'certificate_number' => $data['certificate_number'] ?? null,
            'notes'              => $data['notes'] ?? null,
        ]);
    }

    public static function updateRecord(int $tenantId, int $recordId, array $data): void
    {
        // Re-compute expiry if either the course or the completion date changed.
        $existing = self::findRecord($tenantId, $recordId);
        if (!$existing) return;
        $courseId  = (int) ($data['course_id']    ?? $existing['course_id']);
        $completed = $data['completed_at'] ?? $existing['completed_at'];
        $course    = self::findCourse($tenantId, $courseId);
        $validity  = $course ? (int) $course['validity_months'] : (int) $existing['validity_months'];
        $expires   = $validity > 0 ? date('Y-m-d', strtotime($completed . " +{$validity} months")) : null;

        DB::run(
            'UPDATE training_records
                SET course_id = :cid, user_id = :uid, external_name = :en,
                    completed_at = :ca, expires_at = :ea,
                    certificate_number = :cn, notes = :nt
              WHERE tenant_id = :t AND record_id = :id',
            [
                'cid' => $courseId,
                'uid' => !empty($data['user_id']) ? (int) $data['user_id'] : null,
                'en'  => $data['external_name']      ?? null,
                'ca'  => $completed,
                'ea'  => $expires,
                'cn'  => $data['certificate_number'] ?? null,
                'nt'  => $data['notes'] ?? null,
                't'   => $tenantId, 'id' => $recordId,
            ]
        );
    }

    public static function softDeleteRecord(int $tenantId, int $recordId): void
    {
        DB::run(
            'UPDATE training_records SET deleted_at = NOW() WHERE tenant_id = :t AND record_id = :id',
            ['t' => $tenantId, 'id' => $recordId]
        );
    }

    /** Roll-up for the HSE dashboard tile. */
    public static function countsForTenant(int $tenantId): array
    {
        return DB::one(
            'SELECT COUNT(*) AS total,
                    SUM(expires_at IS NOT NULL AND expires_at < CURDATE()) AS expired,
                    SUM(expires_at BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 60 DAY)) AS expiring_soon,
                    SUM(expires_at IS NULL OR expires_at > DATE_ADD(CURDATE(), INTERVAL 60 DAY)) AS valid
               FROM training_records
              WHERE tenant_id = :t AND deleted_at IS NULL',
            ['t' => $tenantId]
        ) ?? ['total' => 0, 'expired' => 0, 'expiring_soon' => 0, 'valid' => 0];
    }
}
