<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use PDO;

final class AssignmentRepository
{
    public function forPatient(int $patientId): array
    {
        $stmt = Database::connection()->prepare(
            'SELECT pa.id, pa.patient_id, pa.therapist_id, pa.role, pa.assigned_at, pa.ended_at, pa.status, pa.notes,
                    pa.assigned_by, pa.created_at, u.full_name AS therapist_name, u.email AS therapist_email,
                    au.full_name AS assigned_by_name
             FROM patient_assignments pa
             INNER JOIN therapists t ON t.id = pa.therapist_id
             INNER JOIN users u ON u.id = t.user_id
             INNER JOIN users au ON au.id = pa.assigned_by
             WHERE pa.patient_id = :patient_id
             ORDER BY pa.status = "active" DESC, pa.assigned_at DESC, pa.id DESC'
        );
        $stmt->execute(['patient_id' => $patientId]);
        return $stmt->fetchAll();
    }

    public function activeForPatient(int $patientId): array
    {
        $stmt = Database::connection()->prepare(
            'SELECT pa.id, pa.therapist_id, pa.role, pa.assigned_at, pa.notes, u.full_name AS therapist_name
             FROM patient_assignments pa
             INNER JOIN therapists t ON t.id = pa.therapist_id
             INNER JOIN users u ON u.id = t.user_id
             WHERE pa.patient_id = :patient_id AND pa.status = "active" AND pa.ended_at IS NULL
             ORDER BY pa.role = "primary" DESC, u.full_name'
        );
        $stmt->execute(['patient_id' => $patientId]);
        return $stmt->fetchAll();
    }

    public function hasActiveAssignment(int $patientId, int $therapistId): bool
    {
        $stmt = Database::connection()->prepare(
            'SELECT 1 FROM patient_assignments
             WHERE patient_id = :patient_id AND therapist_id = :therapist_id
               AND status = "active" AND ended_at IS NULL LIMIT 1'
        );
        $stmt->execute(['patient_id' => $patientId, 'therapist_id' => $therapistId]);
        return (bool) $stmt->fetchColumn();
    }

    public function therapistIdForUser(int $userId): ?int
    {
        $stmt = Database::connection()->prepare(
            "SELECT t.id
             FROM therapists t
             INNER JOIN users u ON u.id = t.user_id
             WHERE t.user_id = :user_id
               AND LOWER(TRIM(t.status)) = 'active'
               AND LOWER(TRIM(u.status)) = 'active'
               AND (t.deleted_at IS NULL OR CAST(t.deleted_at AS CHAR) = '0000-00-00 00:00:00')
             LIMIT 1"
        );
        $stmt->execute(['user_id' => $userId]);
        $id = $stmt->fetchColumn();
        return $id === false ? null : (int) $id;
    }

    public function availableTherapists(): array
    {
        $stmt = Database::connection()->query(
            'SELECT t.id, t.user_id, u.full_name, u.email, t.professional_title
             FROM therapists t INNER JOIN users u ON u.id = t.user_id
             WHERE t.status = "active" AND u.status = "active"
             ORDER BY u.full_name'
        );
        return $stmt->fetchAll();
    }

    public function create(array $data): int
    {
        $stmt = Database::connection()->prepare(
            'INSERT INTO patient_assignments (patient_id, therapist_id, role, assigned_at, ended_at, status, notes, assigned_by)
             VALUES (:patient_id, :therapist_id, :role, :assigned_at, NULL, "active", :notes, :assigned_by)'
        );
        $stmt->execute($data);
        return (int) Database::connection()->lastInsertId();
    }

    public function end(int $id, string $endedAt): void
    {
        $stmt = Database::connection()->prepare(
            'UPDATE patient_assignments SET ended_at = :ended_at, status = "ended", updated_at = NOW()
             WHERE id = :id AND status = "active" AND ended_at IS NULL'
        );
        $stmt->execute(['id' => $id, 'ended_at' => $endedAt]);
    }

    public function find(int $id): ?array
    {
        $stmt = Database::connection()->prepare('SELECT id, patient_id, therapist_id, role, assigned_at, ended_at, status, notes FROM patient_assignments WHERE id=:id LIMIT 1');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return is_array($row) ? $row : null;
    }

    public function audit(int $userId, string $action, int $entityId, array $metadata = []): void
    {
        $stmt = Database::connection()->prepare('INSERT INTO audit_logs (user_id, action, entity_type, entity_id, ip_address, metadata, created_at) VALUES (:user_id,:action,:entity_type,:entity_id,:ip,:metadata,NOW())');
        $stmt->execute([
            'user_id' => $userId,
            'action' => $action,
            'entity_type' => 'patient_assignment',
            'entity_id' => $entityId,
            'ip' => $_SERVER['REMOTE_ADDR'] ?? null,
            'metadata' => json_encode($metadata, JSON_THROW_ON_ERROR),
        ]);
    }
}
