<?php
declare(strict_types=1);

require_once __DIR__ . '/../../db.php';

class BiometricoRepository
{
    private PDO $pdo;

    public function __construct(?PDO $pdo = null)
    {
        $this->pdo = $pdo ?? db();
    }

    public function obtenerRelojes(): array
    {
        $stmt = $this->pdo->query("SELECT * FROM relojes_biometricos ORDER BY id ASC");
        return $stmt->fetchAll();
    }

    public function obtenerRelojPorId(int $relojId): ?array
    {
        $stmt = $this->pdo->prepare("SELECT * FROM relojes_biometricos WHERE id = ?");
        $stmt->execute([$relojId]);
        $row = $stmt->fetch();

        return $row ?: null;
    }

    public function actualizarMetadataReloj(
        int $relojId,
        ?string $modelo,
        ?string $serialNumber,
        ?string $firmwareVersion
    ): bool {
        $stmt = $this->pdo->prepare("
            UPDATE relojes_biometricos
            SET modelo = ?, serial_number = ?, firmware_version = ?, ultimo_ping = NOW()
            WHERE id = ?
        ");

        return $stmt->execute([$modelo, $serialNumber, $firmwareVersion, $relojId]);
    }

    public function guardarNomina(int $relojId, array $usuarios): array
    {
        $sql = "
            INSERT INTO reloj_nomina
            (reloj_id, device_user_id, nombre, empleado_no, raw_json, actualizado_en)
            VALUES (?, ?, ?, ?, ?, NOW())
            ON DUPLICATE KEY UPDATE
                nombre = VALUES(nombre),
                empleado_no = VALUES(empleado_no),
                raw_json = VALUES(raw_json),
                actualizado_en = NOW()
        ";

        $stmt = $this->pdo->prepare($sql);

        $procesados = 0;

        $this->pdo->beginTransaction();

        try {
            foreach ($usuarios as $u) {
                $stmt->execute([
                    $relojId,
                    (string)$u['device_user_id'],
                    $u['nombre'],
                    $u['empleado_no'],
                    json_encode($u['raw_json'], JSON_UNESCAPED_UNICODE)
                ]);
                $procesados++;
            }

            $this->pdo->commit();

            return [
                'ok' => true,
                'procesados' => $procesados,
            ];
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }

            return [
                'ok' => false,
                'procesados' => $procesados,
                'error' => $e->getMessage(),
            ];
        }
    }

    public function obtenerNominaPorReloj(int $relojId): array
    {
        $stmt = $this->pdo->prepare("
            SELECT *
            FROM reloj_nomina
            WHERE reloj_id = ?
            ORDER BY nombre ASC, device_user_id ASC
        ");
        $stmt->execute([$relojId]);

        return $stmt->fetchAll();
    }

    public function obtenerMapeosPorReloj(int $relojId): array
    {
        $stmt = $this->pdo->prepare("
            SELECT *
            FROM estudiantes_biometrico
            WHERE reloj_id = ?
        ");
        $stmt->execute([$relojId]);

        $rows = $stmt->fetchAll();
        $map = [];

        foreach ($rows as $row) {
            $map[$row['device_user_id']] = $row;
        }

        return $map;
    }

    public function guardarMapeos(int $relojId, array $deviceIds, array $estIds, array $activos): array
    {
        $sql = "
            INSERT INTO estudiantes_biometrico
            (estudiante_id, reloj_id, device_user_id, activo)
            VALUES (?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                estudiante_id = VALUES(estudiante_id),
                activo = VALUES(activo)
        ";

        $stmt = $this->pdo->prepare($sql);

        $guardados = 0;

        $this->pdo->beginTransaction();

        try {
            foreach ($deviceIds as $i => $deviceUserId) {
                $deviceUserId = trim((string)$deviceUserId);
                $estudianteId = (int)($estIds[$i] ?? 0);
                $activo = (int)($activos[$i] ?? 1);

                if ($deviceUserId === '' || $estudianteId <= 0) {
                    continue;
                }

                $stmt->execute([
                    $estudianteId,
                    $relojId,
                    $deviceUserId,
                    $activo
                ]);

                $guardados++;
            }

            $this->pdo->commit();

            return [
                'ok' => true,
                'guardados' => $guardados,
            ];
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }

            return [
                'ok' => false,
                'guardados' => $guardados,
                'error' => $e->getMessage(),
            ];
        }
    }

    public function obtenerEstudiantesFormateados(): array
    {
        $colsStmt = $this->pdo->query("DESCRIBE estudiantes");
        $colsRows = $colsStmt->fetchAll();

        $studentColumns = [];
        foreach ($colsRows as $col) {
            $studentColumns[] = $col['Field'];
        }

        $idCol = 'id';

        $possibleNombreCols = ['nombre', 'nombres', 'primer_nombre', 'nombre_completo'];
        $possibleApellidoCols = ['apellido', 'apellidos', 'primer_apellido'];

        $nombreCol = null;
        $apellidoCol = null;

        foreach ($possibleNombreCols as $c) {
            if (in_array($c, $studentColumns, true)) {
                $nombreCol = $c;
                break;
            }
        }

        foreach ($possibleApellidoCols as $c) {
            if (in_array($c, $studentColumns, true)) {
                $apellidoCol = $c;
                break;
            }
        }

        $orderBy = "id ASC";
        if ($apellidoCol && $nombreCol) {
            $orderBy = "$apellidoCol ASC, $nombreCol ASC";
        } elseif ($nombreCol) {
            $orderBy = "$nombreCol ASC";
        }

        $studentsStmt = $this->pdo->query("SELECT * FROM estudiantes ORDER BY $orderBy");
        $studentsRaw = $studentsStmt->fetchAll();

        $estudiantes = [];

        foreach ($studentsRaw as $row) {
            $labelParts = [];

            if ($apellidoCol && !empty($row[$apellidoCol])) {
                $labelParts[] = $row[$apellidoCol];
            }

            if ($nombreCol && !empty($row[$nombreCol])) {
                $labelParts[] = empty($labelParts) ? $row[$nombreCol] : ', ' . $row[$nombreCol];
            }

            $label = trim(implode('', $labelParts));
            if ($label === '') {
                $label = 'Estudiante #' . $row[$idCol];
            }

            $estudiantes[] = [
                'id' => $row[$idCol],
                'label' => $row[$idCol] . ' - ' . $label,
            ];
        }

        return $estudiantes;
    }

    public function obtenerUltimasMarcaciones(int $limit = 50): array
    {
        $sql = "
            SELECT
                m.id,
                m.reloj_id,
                m.device_user_id,
                m.fecha_hora,
                m.tipo_evento,
                m.creado_en,
                r.nombre AS reloj_nombre,
                rn.nombre AS nombre_reloj,
                eb.estudiante_id
            FROM marcaciones m
            INNER JOIN relojes_biometricos r
                ON r.id = m.reloj_id
            LEFT JOIN reloj_nomina rn
                ON rn.reloj_id = m.reloj_id
               AND rn.device_user_id = m.device_user_id
            LEFT JOIN estudiantes_biometrico eb
                ON eb.reloj_id = m.reloj_id
               AND eb.device_user_id = m.device_user_id
            ORDER BY m.fecha_hora DESC, m.id DESC
            LIMIT :limit
        ";

        $stmt = $this->pdo->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function obtenerResumenMonitor(): array
    {
        $sql = "
            SELECT
                COUNT(*) AS total,
                SUM(CASE WHEN DATE(fecha_hora) = CURDATE() THEN 1 ELSE 0 END) AS hoy,
                MAX(fecha_hora) AS ultima_fecha_hora
            FROM marcaciones
        ";

        $stmt = $this->pdo->query($sql);
        $row = $stmt->fetch();

        return $row ?: [
            'total' => 0,
            'hoy' => 0,
            'ultima_fecha_hora' => null,
        ];
    }
}