<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use PDO;

final class AssessmentRepository
{
    private function hasServiceField(): bool
    {
        static $result = null;
        if ($result !== null) return $result;
        $stmt = Database::connection()->query("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='assessments' AND column_name='service_id'");
        return $result = ((int)$stmt->fetchColumn() === 1);
    }

    private function serviceSelect(string $alias = 'a'): string
    {
        return $this->hasServiceField() ? "$alias.service_id" : 'NULL';
    }

    public function paginate(array $filters, ?int $therapistId = null, int $perPage = 25): array
    {
        $where = ['1=1']; $params = [];
        if ($therapistId !== null) { $where[] = 'a.therapist_id=:therapist_id'; $params['therapist_id']=$therapistId; }
        if (($filters['search'] ?? '') !== '') {
            $where[]='(a.assessment_reference LIKE :search OR p.patient_reference LIKE :search OR p.first_name LIKE :search OR p.last_name LIKE :search OR u.full_name LIKE :search)';
            $params['search']='%'.trim((string)$filters['search']).'%';
        }
        if (($filters['status'] ?? '') !== '') { $where[]='a.status=:status'; $params['status']=$filters['status']; }
        if (($filters['date'] ?? '') !== '') { $where[]='a.assessment_date=:assessment_date'; $params['assessment_date']=$filters['date']; }
        $whereSql='WHERE '.implode(' AND ',$where); $pdo=Database::connection();
        $count=$pdo->prepare("SELECT COUNT(*) FROM assessments a INNER JOIN patients p ON p.id=a.patient_id INNER JOIN therapists t ON t.id=a.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;
        $serviceSelect=$this->serviceSelect();
        $sql="SELECT a.id,a.assessment_reference,a.patient_id,a.therapist_id,a.assignment_id,$serviceSelect AS service_id,a.assessment_date,a.status,a.submitted_at,a.reviewed_at,a.approved_at,a.created_at,a.updated_at,
                p.patient_reference,p.first_name,p.last_name,u.full_name AS therapist_name
                FROM assessments a INNER JOIN patients p ON p.id=a.patient_id INNER JOIN therapists t ON t.id=a.therapist_id INNER JOIN users u ON u.id=t.user_id
                $whereSql ORDER BY a.assessment_date DESC,a.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
    {
        $serviceJoin = $this->hasServiceField() ? 'LEFT JOIN services sv ON sv.id=a.service_id' : '';
        $serviceSelect = $this->hasServiceField() ? 'a.service_id' : 'NULL';
        $serviceName = $this->hasServiceField() ? 'sv.name' : 'NULL';
        $servicePrice = $this->hasServiceField() ? 'sv.price' : 'NULL';
        $stmt=Database::connection()->prepare("SELECT a.*, $serviceSelect AS service_id, $serviceName AS service_name, $servicePrice AS service_price, p.patient_reference,p.first_name,p.last_name,u.full_name AS therapist_name FROM assessments a INNER JOIN patients p ON p.id=a.patient_id INNER JOIN therapists t ON t.id=a.therapist_id INNER JOIN users u ON u.id=t.user_id $serviceJoin WHERE a.id=:id LIMIT 1");
        $stmt->execute(['id'=>$id]); $row=$stmt->fetch(); return is_array($row)?$row:null;
    }

    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 services(): array
    {
        return Database::connection()->query("SELECT id,name,price FROM services WHERE status='active' ORDER BY name")->fetchAll();
    }

    public function therapists(): array
    {
        return Database::connection()->query('SELECT t.id,u.full_name,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'=>'assessments']); $row=$stmt->fetch();
            if (!$row) { $pdo->prepare('INSERT INTO numbering_sequences(sequence_key,prefix,next_number,padding) VALUES ("assessments","ASM-",1,6)')->execute(); $row=['prefix'=>'ASM-','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'=>'assessments']); $pdo->commit(); return $reference;
        } catch (\Throwable $e) { if($pdo->inTransaction()) $pdo->rollBack(); throw $e; }
    }

    public function create(array $data): int
    {
        $fields=['assessment_reference','patient_id','therapist_id','assignment_id','assessment_date','reason_for_referral','background','observations','clinical_findings','goals','recommendations','additional_notes','status'];
        if ($this->hasServiceField()) $fields[]='service_id';
        $sql='INSERT INTO assessments ('.implode(',',$fields).') VALUES ('.implode(',',array_map(fn($f)=>':'.$f,$fields)).')'; $stmt=Database::connection()->prepare($sql); $stmt->execute(array_intersect_key($data,array_flip($fields))); return (int)Database::connection()->lastInsertId();
    }

    public function update(int $id,array $data): void
    {
        $fields=['patient_id','therapist_id','assignment_id','assessment_date','reason_for_referral','background','observations','clinical_findings','goals','recommendations','additional_notes'];
        if ($this->hasServiceField()) $fields[]='service_id';
        $set=implode(',',array_map(fn($f)=>$f.'=:'.$f,$fields)); $data['id']=$id; $stmt=Database::connection()->prepare("UPDATE assessments SET $set,updated_at=NOW() WHERE id=:id"); $stmt->execute(array_intersect_key($data,array_flip([...$fields,'id'])));
    }

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


    public function billableAssessments(int $patientId): array
    {
        if (!$this->hasServiceField()) return [];
        $pdo=Database::connection();
        $check=$pdo->query("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='invoice_items' AND column_name='assessment_id'");
        if ((int)$check->fetchColumn() !== 1) return [];
        $stmt=$pdo->prepare("SELECT a.id,a.patient_id,a.therapist_id,a.assessment_reference,a.assessment_date,a.service_id,sv.name AS service_name,sv.price AS service_price,p.patient_reference,p.first_name,p.last_name,u.full_name AS therapist_name FROM assessments a INNER JOIN patients p ON p.id=a.patient_id INNER JOIN therapists t ON t.id=a.therapist_id INNER JOIN users u ON u.id=t.user_id INNER JOIN services sv ON sv.id=a.service_id WHERE a.patient_id=:patient_id AND a.status='approved' AND NOT EXISTS (SELECT 1 FROM invoice_items ii WHERE ii.assessment_id=a.id) ORDER BY a.assessment_date DESC,a.id DESC");
        $stmt->execute(['patient_id'=>$patientId]); return $stmt->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,"assessment",: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)]);
    }
}
