<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use PDO;

final class TherapistRepository
{
    private static ?bool $hasDeletedAt = null;

    private function hasDeletedAt(): bool
    {
        if (self::$hasDeletedAt !== null) {
            return self::$hasDeletedAt;
        }

        $stmt = Database::connection()->query("SHOW COLUMNS FROM therapists LIKE 'deleted_at'");
        self::$hasDeletedAt = (bool) $stmt->fetch();
        return self::$hasDeletedAt;
    }

    private function activeCondition(string $alias = 't'): string
    {
        return $this->hasDeletedAt() ? "{$alias}.deleted_at IS NULL" : '1=1';
    }

    public function paginate(string $search = '', string $status = '', int $page = 1, int $perPage = 25): array
    {
        $where = [$this->activeCondition('t')];
        $params = [];
        if ($search !== '') {
            $where[] = '(u.full_name LIKE :search OR u.email LIKE :search OR t.professional_title LIKE :search OR t.registration_number LIKE :search)';
            $params['search'] = '%' . $search . '%';
        }
        if ($status !== '') {
            $where[] = 't.status = :status';
            $params['status'] = $status;
        }
        $whereSql = 'WHERE ' . implode(' AND ', $where);
        $pdo = Database::connection();
        $count = $pdo->prepare("SELECT COUNT(*) FROM therapists t INNER JOIN users u ON u.id=t.user_id $whereSql");
        $count->execute($params);
        $total = (int) $count->fetchColumn();
        $pages = max(1, (int) ceil($total / $perPage));
        $page = min(max(1, $page), $pages);
        $offset = ($page - 1) * $perPage;
        $sql = "SELECT t.id, t.user_id, t.professional_title, t.registration_number, t.status, t.notes,
                       t.created_at, t.updated_at, u.full_name, u.email, u.phone
                FROM therapists t INNER JOIN users u ON u.id=t.user_id
                $whereSql ORDER BY u.full_name ASC LIMIT :limit OFFSET :offset";
        $stmt = $pdo->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue(':' . $key, $value);
        }
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return ['items' => $stmt->fetchAll(), 'total' => $total, 'page' => $page, 'pages' => $pages, 'perPage' => $perPage];
    }

    public function find(int $id): ?array
    {
        $stmt = Database::connection()->prepare('SELECT t.*, u.full_name, u.email, u.phone, u.status AS user_status FROM therapists t INNER JOIN users u ON u.id=t.user_id WHERE t.id=:id AND ' . $this->activeCondition('t') . ' LIMIT 1');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch();
        return is_array($row) ? $row : null;
    }

    public function create(array $data): int
    {
        $stmt = Database::connection()->prepare('INSERT INTO therapists (user_id, professional_title, registration_number, status, notes) VALUES (:user_id, :professional_title, :registration_number, :status, :notes)');
        $stmt->execute($data);
        return (int) Database::connection()->lastInsertId();
    }

    public function softDelete(int $id): void
    {
        $pdo = Database::connection();
        if ($this->hasDeletedAt()) {
            $pdo->prepare('UPDATE therapists SET deleted_at=NOW(), updated_at=NOW() WHERE id=:id AND deleted_at IS NULL')->execute(['id' => $id]);
            return;
        }

        // Backward-compatible fallback if migration 005 has not yet been run.
        $pdo->prepare("UPDATE therapists SET status='inactive', updated_at=NOW() WHERE id=:id")->execute(['id' => $id]);
    }

    public function update(int $id, array $data): void
    {
        $stmt = Database::connection()->prepare('UPDATE therapists SET professional_title=:professional_title, registration_number=:registration_number, status=:status, notes=:notes, updated_at=NOW() WHERE id=:id');
        $data['id'] = $id;
        $stmt->execute($data);
    }

    public function existsForUser(int $userId, ?int $ignoreId = null): bool
    {
        $sql = 'SELECT 1 FROM therapists WHERE user_id=:user_id AND ' . $this->activeCondition('therapists');
        $params = ['user_id' => $userId];
        if ($ignoreId !== null) {
            $sql .= ' AND id <> :id';
            $params['id'] = $ignoreId;
        }
        $sql .= ' LIMIT 1';
        $stmt = Database::connection()->prepare($sql);
        $stmt->execute($params);
        return (bool) $stmt->fetchColumn();
    }

    public function therapistUsers(): array
    {
        $activeTherapist = $this->activeCondition('t');
        return Database::connection()->query("SELECT u.id, u.full_name, u.email, u.phone FROM users u INNER JOIN user_roles ur ON ur.user_id=u.id INNER JOIN roles r ON r.id=ur.role_id LEFT JOIN therapists t ON t.user_id=u.id AND {$activeTherapist} WHERE r.name='THERAPIST' AND u.status='active' AND t.id IS NULL ORDER BY u.full_name")->fetchAll();
    }

    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' => 'therapist', 'entity_id' => $entityId, 'ip' => $_SERVER['REMOTE_ADDR'] ?? null, 'metadata' => json_encode($metadata, JSON_THROW_ON_ERROR)]);
    }
}
