<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use PDO;

final class ReportRepository
{
    public function paginate(array $filters, ?int $therapistId = null, int $perPage = 25): array
    {
        $where = ['1=1']; $params = [];
        if ($therapistId !== null) { $where[] = 'r.therapist_id=:therapist_id'; $params['therapist_id']=$therapistId; }
        if (($filters['search'] ?? '') !== '') {
            $where[]='(r.report_reference LIKE :search_ref OR p.patient_reference LIKE :search_patient_ref OR p.first_name LIKE :search_first OR p.last_name LIKE :search_last OR u.full_name LIKE :search_therapist)';
            $search='%'.trim((string)$filters['search']).'%';
            $params['search_ref']=$search; $params['search_patient_ref']=$search; $params['search_first']=$search; $params['search_last']=$search; $params['search_therapist']=$search;
        }
        if (($filters['status'] ?? '') !== '') { $where[]='r.status=:status'; $params['status']=$filters['status']; }
        $whereSql='WHERE '.implode(' AND ',$where); $pdo=Database::connection();
        $count=$pdo->prepare("SELECT COUNT(*) FROM reports r INNER JOIN patients p ON p.id=r.patient_id INNER JOIN therapists t ON t.id=r.therapist_id 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,(int)($filters['page']??1)),$pages); $offset=($page-1)*$perPage;
        $sql="SELECT r.id,r.report_reference,r.patient_id,r.therapist_id,r.assignment_id,r.current_version,r.status,r.submitted_at,r.reviewed_at,r.approved_at,r.created_at,r.updated_at,
                p.patient_reference,p.first_name,p.last_name,u.full_name AS therapist_name
                FROM reports r INNER JOIN patients p ON p.id=r.patient_id INNER JOIN therapists t ON t.id=r.therapist_id INNER JOIN users u ON u.id=t.user_id
                $whereSql ORDER BY r.updated_at DESC,r.id DESC LIMIT :limit OFFSET :offset";
        $stmt=$pdo->prepare($sql); foreach($params as $k=>$v)$stmt->bindValue(':'.$k,$v); $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 r.*,p.patient_reference,p.first_name,p.last_name,u.full_name AS therapist_name FROM reports r INNER JOIN patients p ON p.id=r.patient_id INNER JOIN therapists t ON t.id=r.therapist_id INNER JOIN users u ON u.id=t.user_id WHERE r.id=:id LIMIT 1');
        $stmt->execute(['id'=>$id]); $row=$stmt->fetch(); return is_array($row)?$row:null;
    }

    public function currentVersion(int $reportId): ?array
    {
        $stmt=Database::connection()->prepare('SELECT * FROM report_versions WHERE report_id=:report_id ORDER BY version_number DESC LIMIT 1');
        $stmt->execute(['report_id'=>$reportId]); $row=$stmt->fetch(); return is_array($row)?$row:null;
    }

    public function versions(int $reportId): array
    {
        $stmt=Database::connection()->prepare('SELECT rv.*,u.full_name AS created_by_name,au.full_name AS approved_by_name FROM report_versions rv INNER JOIN users u ON u.id=rv.created_by LEFT JOIN users au ON au.id=rv.approved_by WHERE rv.report_id=:report_id ORDER BY rv.version_number DESC');
        $stmt->execute(['report_id'=>$reportId]); return $stmt->fetchAll();
    }

    public function patientsForTherapist(int $therapistId): array
    {
        $stmt=Database::connection()->prepare('SELECT DISTINCT p.id,p.patient_reference,p.first_name,p.last_name FROM patients p INNER JOIN patient_assignments pa ON pa.patient_id=p.id WHERE p.deleted_at IS NULL AND p.status="active" AND pa.therapist_id=:therapist_id AND pa.status="active" AND pa.ended_at IS NULL ORDER BY p.last_name,p.first_name');
        $stmt->execute(['therapist_id'=>$therapistId]); return $stmt->fetchAll();
    }

    public function allPatients(): array
    { return Database::connection()->query('SELECT id,patient_reference,first_name,last_name FROM patients WHERE deleted_at IS NULL AND status="active" ORDER BY last_name,first_name')->fetchAll(); }

    public function therapists(): array
    { return Database::connection()->query('SELECT t.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')->fetchAll(); }

    public function activeAssignment(int $patientId,int $therapistId): ?array
    {
        $stmt=Database::connection()->prepare('SELECT id,patient_id,therapist_id,role,assigned_at,ended_at,status FROM patient_assignments WHERE patient_id=:patient_id AND therapist_id=:therapist_id AND status="active" AND ended_at IS NULL ORDER BY role="primary" DESC,assigned_at DESC,id DESC LIMIT 1');
        $stmt->execute(['patient_id'=>$patientId,'therapist_id'=>$therapistId]); $row=$stmt->fetch(); return is_array($row)?$row:null;
    }

    public function nextReference(): string
    {
        $pdo=Database::connection(); $pdo->beginTransaction();
        try {
            $stmt=$pdo->prepare('SELECT id,prefix,next_number,padding FROM numbering_sequences WHERE sequence_key=:key FOR UPDATE');
            $stmt->execute(['key'=>'reports']); $row=$stmt->fetch();
            if (!$row) { $pdo->prepare('INSERT INTO numbering_sequences(sequence_key,prefix,next_number,padding) VALUES ("reports","RPT-",1,6)')->execute(); $row=['prefix'=>'RPT-','next_number'=>1,'padding'=>6]; }
            $reference=(string)$row['prefix'].str_pad((string)$row['next_number'],(int)$row['padding'],'0',STR_PAD_LEFT);
            $pdo->prepare('UPDATE numbering_sequences SET next_number=next_number+1,updated_at=NOW() WHERE sequence_key=:key')->execute(['key'=>'reports']); $pdo->commit(); return $reference;
        } catch (\Throwable $e) { if($pdo->inTransaction())$pdo->rollBack(); throw $e; }
    }

    public function createReport(array $data): int
    {
        $pdo=Database::connection();
        $stmt=$pdo->prepare('INSERT INTO reports(report_reference,patient_id,therapist_id,assignment_id,current_version,status) VALUES(:report_reference,:patient_id,:therapist_id,:assignment_id,1,"draft")');
        $stmt->execute(['report_reference'=>$data['report_reference'],'patient_id'=>$data['patient_id'],'therapist_id'=>$data['therapist_id'],'assignment_id'=>$data['assignment_id']]);
        return (int)$pdo->lastInsertId();
    }

    public function createVersion(int $reportId,int $versionNumber,string $content,string $status,int $createdBy): int
    {
        $stmt=Database::connection()->prepare('INSERT INTO report_versions(report_id,version_number,content,status,created_by) VALUES(:report_id,:version_number,:content,:status,:created_by)');
        $stmt->execute(['report_id'=>$reportId,'version_number'=>$versionNumber,'content'=>$content,'status'=>$status,'created_by'=>$createdBy]); return (int)Database::connection()->lastInsertId();
    }

    public function updateVersion(int $versionId,string $content): void
    { $stmt=Database::connection()->prepare('UPDATE report_versions SET content=:content WHERE id=:id AND status IN ("draft","returned")'); $stmt->execute(['content'=>$content,'id'=>$versionId]); }

    public function setReportStatus(int $id,string $status,?string $timestampColumn=null,?int $approvedBy=null): void
    {
        $sql='UPDATE reports SET status=:status,updated_at=NOW()'; $params=['id'=>$id,'status'=>$status];
        if($timestampColumn!==null)$sql.=', '.$timestampColumn.'=NOW()';
        if($approvedBy!==null){$sql.=', approved_by=:approved_by';$params['approved_by']=$approvedBy;}
        $sql.=' WHERE id=:id'; $stmt=Database::connection()->prepare($sql); $stmt->execute($params);
    }

    public function setVersionStatus(int $versionId,string $status,?int $approvedBy=null): void
    {
        $sql='UPDATE report_versions SET status=:status'; $params=['id'=>$versionId,'status'=>$status];
        if($approvedBy!==null){$sql.=', approved_at=NOW(), approved_by=:approved_by';$params['approved_by']=$approvedBy;}
        $sql.=' WHERE id=:id'; $stmt=Database::connection()->prepare($sql); $stmt->execute($params);
    }

    public function setCurrentVersion(int $reportId,int $version): void
    { $stmt=Database::connection()->prepare('UPDATE reports SET current_version=:version,updated_at=NOW() WHERE id=:id');$stmt->execute(['id'=>$reportId,'version'=>$version]); }

    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,"report",:entity_id,:ip,:metadata,NOW())');
        $stmt->execute(['user_id'=>$userId,'action'=>$action,'entity_id'=>$entityId,'ip'=>$_SERVER['REMOTE_ADDR']??null,'metadata'=>json_encode($metadata,JSON_THROW_ON_ERROR)]);
    }
}
