<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use PDO;

final class PatientRepository
{
    public function paginate(string $search = '', string $status = '', int $page = 1, int $perPage = 25): array
    {
        $where = ['deleted_at IS NULL'];
        $params = [];
        if ($search !== '') {
            $where[] = '(patient_reference LIKE :search OR first_name LIKE :search OR last_name LIKE :search OR phone LIKE :search OR email LIKE :search)';
            $params['search'] = '%' . $search . '%';
        }
        if ($status !== '') { $where[] = 'status = :status'; $params['status'] = $status; }
        $whereSql = 'WHERE ' . implode(' AND ', $where);
        $pdo = Database::connection();
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM patients $whereSql");
        $stmt->execute($params);
        $total = (int)$stmt->fetchColumn();
        $pages = max(1, (int)ceil($total / $perPage));
        $page = min(max(1, $page), $pages);
        $offset = ($page - 1) * $perPage;
        $stmt = $pdo->prepare("SELECT id, patient_reference, first_name, last_name, date_of_birth, gender, phone, email, parent_guardian, emergency_contact, status, created_at, updated_at FROM patients $whereSql ORDER BY last_name, first_name LIMIT :limit OFFSET :offset");
        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 paginateForTherapist(int $therapistId, string $search = '', string $status = '', int $page = 1, int $perPage = 25): array
    {
        $where = ['p.deleted_at IS NULL', 'pa.therapist_id = :therapist_id', 'pa.status = "active"', 'pa.ended_at IS NULL'];
        $params = ['therapist_id' => $therapistId];
        if ($search !== '') {
            $where[] = '(p.patient_reference LIKE :search OR p.first_name LIKE :search OR p.last_name LIKE :search OR p.phone LIKE :search OR p.email LIKE :search)';
            $params['search'] = '%' . $search . '%';
        }
        if ($status !== '') { $where[] = 'p.status = :status'; $params['status'] = $status; }
        $whereSql = 'WHERE ' . implode(' AND ', $where);
        $pdo = Database::connection();
        $count = $pdo->prepare("SELECT COUNT(DISTINCT p.id) FROM patients p INNER JOIN patient_assignments pa ON pa.patient_id=p.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;
        $stmt = $pdo->prepare("SELECT DISTINCT p.id, p.patient_reference, p.first_name, p.last_name, p.date_of_birth, p.gender, p.phone, p.email, p.parent_guardian, p.emergency_contact, p.status, p.created_at, p.updated_at FROM patients p INNER JOIN patient_assignments pa ON pa.patient_id=p.id $whereSql ORDER BY p.last_name, p.first_name LIMIT :limit OFFSET :offset");
        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 id, patient_reference, first_name, last_name, date_of_birth, gender, phone, email, address, parent_guardian, emergency_contact, status, created_at, updated_at FROM patients WHERE id=:id AND deleted_at IS NULL LIMIT 1');
        $stmt->execute(['id'=>$id]);
        $row = $stmt->fetch();
        return is_array($row) ? $row : null;
    }

    public function existsReference(string $reference): bool
    {
        $stmt = Database::connection()->prepare('SELECT 1 FROM patients WHERE patient_reference=:reference LIMIT 1');
        $stmt->execute(['reference'=>$reference]);
        return (bool)$stmt->fetchColumn();
    }

    public function create(array $data): int
    {
        $stmt = Database::connection()->prepare('INSERT INTO patients (patient_reference, first_name, last_name, date_of_birth, gender, phone, email, address, parent_guardian, emergency_contact, status) VALUES (:patient_reference,:first_name,:last_name,:date_of_birth,:gender,:phone,:email,:address,:parent_guardian,:emergency_contact,:status)');
        $stmt->execute($data);
        return (int)Database::connection()->lastInsertId();
    }

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

    public function update(int $id, array $data): void
    {
        // Bind only parameters that actually exist in the UPDATE statement.
        // The edit form can contain CSRF, submit and other request fields; passing
        // those through to PDO causes SQLSTATE[HY093].
        $allowed = [
            'first_name','last_name','date_of_birth','gender','phone','email',
            'address','parent_guardian','emergency_contact','status'
        ];
        $params = array_intersect_key($data, array_flip($allowed));
        $params['id'] = $id;
        Database::connection()->prepare(
            'UPDATE patients SET first_name=:first_name,last_name=:last_name,date_of_birth=:date_of_birth,gender=:gender,phone=:phone,email=:email,address=:address,parent_guardian=:parent_guardian,emergency_contact=:emergency_contact,status=:status,updated_at=NOW() WHERE id=:id AND deleted_at IS NULL'
        )->execute($params);
    }

    public function nextSequence(string $key, string $defaultPrefix = 'PT-', int $defaultPadding = 6): string
    {
        $pdo = Database::connection();
        $stmt = $pdo->prepare('SELECT id, prefix, next_number, padding FROM numbering_sequences WHERE sequence_key=:key FOR UPDATE');
        $stmt->execute(['key'=>$key]);
        $row = $stmt->fetch();
        if (!is_array($row)) {
            $insert = $pdo->prepare('INSERT INTO numbering_sequences (sequence_key,prefix,next_number,padding) VALUES (:key,:prefix,1,:padding)');
            $insert->execute(['key'=>$key,'prefix'=>$defaultPrefix,'padding'=>$defaultPadding]);
            $row = ['prefix'=>$defaultPrefix,'next_number'=>1,'padding'=>$defaultPadding];
        }
        $number = (int)$row['next_number'];
        $prefix = (string)$row['prefix'];
        $padding = (int)$row['padding'];
        $update = $pdo->prepare('UPDATE numbering_sequences SET next_number=next_number+1, updated_at=NOW() WHERE sequence_key=:key');
        $update->execute(['key'=>$key]);
        return $prefix . str_pad((string)$number, $padding, '0', STR_PAD_LEFT);
    }

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