<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use PDO;

final class SessionRepository
{
    public function paginate(array $filters, ?int $therapistId = null, int $perPage = 25): array
    {
        $where = ['1=1'];
        $params = [];
        $hasWorkflowFields = $this->hasWorkflowFields();
        $hasBillableField = $this->hasBillableField();
        if ($therapistId !== null) {
            $where[] = 's.therapist_id = :therapist_id';
            $params['therapist_id'] = $therapistId;
        }
        if (($filters['search'] ?? '') !== '') {
            $where[] = '(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'] ?? '') !== '' && $hasWorkflowFields) {
            $where[] = 's.status = :status';
            $params['status'] = $filters['status'];
        }
        if (($filters['date'] ?? '') !== '') {
            $where[] = 's.session_date = :session_date';
            $params['session_date'] = $filters['date'];
        }
        $whereSql = 'WHERE ' . implode(' AND ', $where);
        $pdo = Database::connection();
        $count = $pdo->prepare("SELECT COUNT(*) FROM therapy_sessions s INNER JOIN patients p ON p.id=s.patient_id INNER JOIN therapists t ON t.id=s.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;
        $sessionTypeColumn = $hasWorkflowFields ? 's.session_type' : 'NULL';
        $statusColumn = $hasWorkflowFields ? 's.status' : "'completed'";
        $billableColumn = $hasBillableField ? 's.billable' : '0';
        $appointmentIdColumn = $this->hasAppointmentField() ? 's.appointment_id' : 'NULL';
        $sql = "SELECT s.id, s.patient_id, s.therapist_id, s.assignment_id, s.session_date, s.start_time, s.end_time,
                       s.duration_minutes, s.service_id, $appointmentIdColumn AS appointment_id, $sessionTypeColumn AS session_type, $statusColumn AS status, $billableColumn AS billable, s.notes, s.created_at, s.updated_at,
                       p.patient_reference, p.first_name, p.last_name, u.full_name AS therapist_name,
                       sv.name AS service_name
                FROM therapy_sessions s
                INNER JOIN patients p ON p.id=s.patient_id
                INNER JOIN therapists t ON t.id=s.therapist_id
                INNER JOIN users u ON u.id=t.user_id
                LEFT JOIN services sv ON sv.id=s.service_id
                $whereSql ORDER BY s.session_date DESC, s.start_time DESC, s.id DESC 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
    {
        $hasWorkflowFields = $this->hasWorkflowFields();
        $sessionTypeColumn = $hasWorkflowFields ? 's.session_type' : 'NULL';
        $statusColumn = $hasWorkflowFields ? 's.status' : "'completed'";
        $billableColumn = $this->hasBillableField() ? 's.billable' : '0';
        $appointmentIdColumn = $this->hasAppointmentField() ? 's.appointment_id' : 'NULL';
        $stmt = Database::connection()->prepare(
            "SELECT s.*, $appointmentIdColumn AS appointment_id, $sessionTypeColumn AS session_type, $statusColumn AS status, $billableColumn AS billable, p.patient_reference, p.first_name, p.last_name, u.full_name AS therapist_name, sv.name AS service_name
             FROM therapy_sessions s
             INNER JOIN patients p ON p.id=s.patient_id
             INNER JOIN therapists t ON t.id=s.therapist_id
             INNER JOIN users u ON u.id=t.user_id
             LEFT JOIN services sv ON sv.id=s.service_id
             WHERE s.id=:id LIMIT 1"
        );
        $stmt->execute(['id'=>$id]);
        $row = $stmt->fetch();
        return is_array($row) ? $row : null;
    }

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

    public function patients(): 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, 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")->fetchAll();
    }

    public function create(array $data): int
    {
        $fields = ['patient_id','therapist_id','assignment_id','appointment_id','session_date','start_time','end_time','duration_minutes','service_id','interventions','activities','patient_response','progress','recommendations','notes'];
        if ($this->hasBillableField()) $fields[] = 'billable';
        if ($this->hasWorkflowFields()) {
            $fields[] = 'session_type';
            $fields[] = 'status';
        }
        $columns = implode(', ', $fields);
        $placeholders = implode(',', array_map(static fn($field) => ':' . $field, $fields));
        $stmt = Database::connection()->prepare("INSERT INTO therapy_sessions ($columns) VALUES ($placeholders)");
        $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','appointment_id','session_date','start_time','end_time','duration_minutes','service_id','interventions','activities','patient_response','progress','recommendations','notes'];
        if ($this->hasBillableField()) $fields[] = 'billable';
        if ($this->hasWorkflowFields()) {
            $fields[] = 'session_type';
            $fields[] = 'status';
        }
        $set = implode(', ', array_map(static fn($field) => "$field=:$field", $fields));
        $data['id'] = $id;
        $stmt = Database::connection()->prepare("UPDATE therapy_sessions SET $set, updated_at=NOW() WHERE id=:id");
        $stmt->execute(array_intersect_key($data, array_flip([...$fields, 'id'])));
    }

    public function billableSessions(int $patientId): array
    {
        if (!$this->hasBillableField()) return [];

        // A service is required because its catalogue price is what becomes the
        // invoice line price. Existing installations may not yet have the Phase 10
        // status column, so treat legacy sessions as completed rather than making
        // the billing screen fail.
        $statusFilter = $this->hasWorkflowFields() ? " AND s.status='completed'" : '';
        $sql = "SELECT s.id, s.patient_id, s.therapist_id, s.session_date, s.start_time, s.end_time, s.duration_minutes,
                       s.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 therapy_sessions s
                INNER JOIN patients p ON p.id=s.patient_id
                INNER JOIN therapists t ON t.id=s.therapist_id
                INNER JOIN users u ON u.id=t.user_id
                INNER JOIN services sv ON sv.id=s.service_id
                WHERE s.patient_id=:patient_id$statusFilter AND s.service_id IS NOT NULL AND s.billable=1
                  AND NOT EXISTS (SELECT 1 FROM invoice_items ii WHERE ii.session_id=s.id)
                ORDER BY s.session_date DESC, s.start_time DESC, s.id DESC";
        $stmt=Database::connection()->prepare($sql);
        $stmt->execute(['patient_id'=>$patientId]);
        return $stmt->fetchAll();
    }

    private function hasAppointmentField(): bool
    {
        static $result = null;
        if ($result !== null) return $result;
        $stmt=Database::connection()->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name='therapy_sessions' AND column_name='appointment_id'");
        $stmt->execute();
        return $result=((int)$stmt->fetchColumn()===1);
    }

    private function hasBillableField(): bool
    {
        static $result = null;
        if ($result !== null) return $result;
        $stmt=Database::connection()->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name='therapy_sessions' AND column_name='billable'");
        $stmt->execute();
        return $result=((int)$stmt->fetchColumn()===1);
    }

    private function hasWorkflowFields(): bool
    {
        static $result = null;
        if ($result !== null) return $result;
        $stmt = Database::connection()->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = 'therapy_sessions' AND column_name IN ('session_type','status')");
        $stmt->execute();
        return $result = ((int)$stmt->fetchColumn() === 2);
    }

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