<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;

final class DashboardRepository
{
    private function scope(?int $therapistId): array
    {
        if ($therapistId === null) return ['', []];
        return [' AND EXISTS (SELECT 1 FROM patient_assignments pa_scope WHERE pa_scope.patient_id = p.id AND pa_scope.therapist_id = :scope_therapist AND pa_scope.status = "active" AND pa_scope.ended_at IS NULL)', ['scope_therapist' => $therapistId]];
    }

    public function overview(?int $therapistId = null): array
    {
        $pdo = Database::connection();
        [$patientScope, $scopeParams] = $this->scope($therapistId);

        $q = static function ($pdo, string $sql, array $params = []) {
            $s = $pdo->prepare($sql); $s->execute($params); return $s;
        };

        $activePatients = (int)$q($pdo, 'SELECT COUNT(DISTINCT p.id) FROM patients p WHERE p.deleted_at IS NULL AND p.status = "active"' . $patientScope, $scopeParams)->fetchColumn();
        $totalPatients = (int)$q($pdo, 'SELECT COUNT(DISTINCT p.id) FROM patients p WHERE p.deleted_at IS NULL' . $patientScope, $scopeParams)->fetchColumn();

        $sessionScope = $therapistId !== null ? ' AND s.therapist_id = :therapist' : '';
        $sessionParams = $therapistId !== null ? ['therapist' => $therapistId] : [];
        $sessionsToday = (int)$q($pdo, 'SELECT COUNT(*) FROM therapy_sessions s WHERE s.session_date = CURDATE()' . $sessionScope, $sessionParams)->fetchColumn();
        $sessionsMonth = (int)$q($pdo, 'SELECT COUNT(*) FROM therapy_sessions s WHERE s.session_date >= DATE_FORMAT(CURDATE(), "%Y-%m-01") AND s.session_date < DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY)' . $sessionScope, $sessionParams)->fetchColumn();
        $todaySessions = $q($pdo, 'SELECT s.id,s.session_date,s.start_time,s.end_time,s.status,p.first_name,p.last_name,p.patient_reference,sv.name AS service_name FROM therapy_sessions s INNER JOIN patients p ON p.id=s.patient_id LEFT JOIN services sv ON sv.id=s.service_id WHERE s.session_date = CURDATE()' . $sessionScope . ' ORDER BY s.start_time IS NULL, s.start_time, s.id LIMIT 12', $sessionParams)->fetchAll();

        $assessmentCount = 0; $activePlans = 0;
        if ($therapistId !== null) {
            $assessmentCount = (int)$q($pdo, 'SELECT COUNT(*) FROM assessments a WHERE a.therapist_id=:assessment_count_therapist AND a.status NOT IN ("approved")', ['assessment_count_therapist'=>$therapistId])->fetchColumn();
            $activePlans = (int)$q($pdo, 'SELECT COUNT(*) FROM treatment_plans tp WHERE tp.therapist_id=:plan_therapist AND tp.status <> "closed"', ['plan_therapist'=>$therapistId])->fetchColumn();
        }
        $billableUninvoiced = (int)$q($pdo, 'SELECT COUNT(*) FROM therapy_sessions s LEFT JOIN invoice_items ii ON ii.session_id=s.id WHERE s.billable=1 AND s.service_id IS NOT NULL AND s.session_date <= CURDATE() AND ii.id IS NULL' . $sessionScope, $sessionParams)->fetchColumn();

        $assessmentScope = $therapistId !== null ? ' AND a.therapist_id = :assessment_therapist' : '';
        $assessmentParams = $therapistId !== null ? ['assessment_therapist'=>$therapistId] : [];
        $assessmentServiceExists = (int)$q($pdo, "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='assessments' AND column_name='service_id'")->fetchColumn() === 1;
        $assessmentInvoiceLinkExists = (int)$q($pdo, "SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='invoice_items' AND column_name='assessment_id'")->fetchColumn() === 1;
        if ($assessmentServiceExists && $assessmentInvoiceLinkExists) {
            $billableUninvoiced += (int)$q($pdo, 'SELECT COUNT(*) FROM assessments a LEFT JOIN invoice_items ai ON ai.assessment_id=a.id WHERE a.status="approved" AND a.service_id IS NOT NULL AND a.assessment_date <= CURDATE() AND ai.id IS NULL' . $assessmentScope, $assessmentParams)->fetchColumn();
        }

        $invoiceScope = $therapistId !== null ? ' AND EXISTS (SELECT 1 FROM patient_assignments pa_i WHERE pa_i.patient_id=i.patient_id AND pa_i.therapist_id=:invoice_therapist AND pa_i.status="active" AND pa_i.ended_at IS NULL)' : '';
        $invoiceParams = $therapistId !== null ? ['invoice_therapist'=>$therapistId] : [];
        $outstanding = (float)$q($pdo, 'SELECT COALESCE(SUM(i.total - COALESCE((SELECT SUM(p.amount) FROM payments p WHERE p.invoice_id=i.id AND p.status="posted"),0)),0) FROM invoices i WHERE i.status NOT IN ("paid","cancelled")' . $invoiceScope, $invoiceParams)->fetchColumn();
        $overdue = (float)$q($pdo, 'SELECT COALESCE(SUM(i.total - COALESCE((SELECT SUM(p.amount) FROM payments p WHERE p.invoice_id=i.id AND p.status="posted"),0)),0) FROM invoices i WHERE i.status NOT IN ("paid","cancelled") AND i.due_date IS NOT NULL AND i.due_date < CURDATE()' . $invoiceScope, $invoiceParams)->fetchColumn();
        $paidMonth = (float)$q($pdo, 'SELECT COALESCE(SUM(p.amount),0) FROM payments p INNER JOIN invoices i ON i.id=p.invoice_id WHERE p.status="posted" AND p.payment_date >= DATE_FORMAT(CURDATE(), "%Y-%m-01") AND p.payment_date < DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY)' . $invoiceScope, $invoiceParams)->fetchColumn();

        $upcomingParams = $sessionParams;
        $upcoming = $q($pdo, 'SELECT s.id,s.session_date,s.start_time,s.end_time,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.session_date >= CURDATE()' . $sessionScope . ' ORDER BY s.session_date, s.start_time LIMIT 8', $upcomingParams)->fetchAll();

        $recentInvoices = $q($pdo, 'SELECT i.id,i.invoice_number,i.invoice_date,i.total,i.status,p.patient_reference,p.first_name,p.last_name,COALESCE((SELECT SUM(py.amount) FROM payments py WHERE py.invoice_id=i.id AND py.status="posted"),0) AS paid FROM invoices i INNER JOIN patients p ON p.id=i.patient_id WHERE 1=1' . $invoiceScope . ' ORDER BY i.created_at DESC LIMIT 7', $invoiceParams)->fetchAll();

        $recentPatients = $q($pdo, 'SELECT p.id,p.patient_reference,p.first_name,p.last_name,p.status,p.created_at FROM patients p WHERE p.deleted_at IS NULL' . $patientScope . ' ORDER BY p.created_at DESC LIMIT 6', $scopeParams)->fetchAll();

        $monthSessions = $q($pdo, 'SELECT DATE_FORMAT(s.session_date,"%b") AS label, COUNT(*) AS total FROM therapy_sessions s WHERE s.session_date >= DATE_SUB(DATE_FORMAT(CURDATE(), "%Y-%m-01"), INTERVAL 5 MONTH) AND s.session_date < DATE_ADD(LAST_DAY(CURDATE()), INTERVAL 1 DAY)' . $sessionScope . ' GROUP BY YEAR(s.session_date), MONTH(s.session_date), DATE_FORMAT(s.session_date,"%b") ORDER BY YEAR(s.session_date), MONTH(s.session_date)', $sessionParams)->fetchAll();

        $appointmentScope = $therapistId !== null ? ' AND a.therapist_id = :appointment_therapist' : '';
        $appointmentParams = $therapistId !== null ? ['appointment_therapist' => $therapistId] : [];
        $todayAppointments = $q($pdo, 'SELECT a.id,a.appointment_date,a.start_time,a.end_time,a.status,p.patient_reference,p.first_name,p.last_name,sv.name AS service_name FROM appointments a INNER JOIN patients p ON p.id=a.patient_id LEFT JOIN services sv ON sv.id=a.service_id WHERE a.appointment_date=CURDATE() AND a.status NOT IN ("cancelled","no_show")' . $appointmentScope . ' ORDER BY a.start_time IS NULL,a.start_time,a.id LIMIT 12', $appointmentParams)->fetchAll();
        $upcomingAppointments = $q($pdo, 'SELECT a.id,a.appointment_date,a.start_time,a.end_time,a.status,p.patient_reference,p.first_name,p.last_name,sv.name AS service_name FROM appointments a INNER JOIN patients p ON p.id=a.patient_id LEFT JOIN services sv ON sv.id=a.service_id WHERE a.appointment_date>=CURDATE() AND a.status NOT IN ("cancelled","no_show","completed")' . $appointmentScope . ' ORDER BY a.appointment_date,a.start_time,a.id LIMIT 8', $appointmentParams)->fetchAll();
        $assignedPatients = $therapistId !== null
            ? (int)$q($pdo, 'SELECT COUNT(DISTINCT pa.patient_id) FROM patient_assignments pa INNER JOIN patients p ON p.id=pa.patient_id WHERE pa.therapist_id=:assigned_therapist AND pa.status="active" AND pa.ended_at IS NULL AND p.deleted_at IS NULL AND p.status="active"', ['assigned_therapist'=>$therapistId])->fetchColumn()
            : $activePatients;

        $statusCounts = $q(
    $pdo,
    'SELECT p.status, COUNT(*) AS total
     FROM patients p
     WHERE p.deleted_at IS NULL' . $patientScope . '
     GROUP BY p.status
     ORDER BY p.status',
    $scopeParams
)->fetchAll();

return compact('activePatients','totalPatients','sessionsToday','sessionsMonth','billableUninvoiced','outstanding','overdue','paidMonth','upcoming','recentInvoices','recentPatients','monthSessions','statusCounts','todaySessions','assessmentCount','activePlans','todayAppointments','upcomingAppointments','assignedPatients');
    }
}
