<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;

final class DashboardAnalyticsModel extends Model
{
    public function adminStats(): array
    {
        $stats = [];

        $stats['users'] = (int) $this->db->query("SELECT COUNT(*) FROM users")->fetchColumn();
        $stats['conferences'] = (int) $this->db->query("SELECT COUNT(*) FROM conferences")->fetchColumn();
        $stats['submissions'] = (int) $this->db->query("SELECT COUNT(*) FROM submissions")->fetchColumn();
        $stats['reviews'] = (int) $this->db->query("SELECT COUNT(*) FROM review_assignments")->fetchColumn();

        return $stats;
    }

    public function submissionSummary(): array
    {
        $rows = $this->db->query("
            SELECT status, COUNT(*) AS total
            FROM submissions
            GROUP BY status
        ")->fetchAll();

        $out = [
            'draft' => 0,
            'submitted' => 0,
            'under_review' => 0,
            'accepted' => 0,
            'rejected' => 0,
            'revision_required' => 0,
        ];

        foreach ($rows as $row) {
            $key = (string) ($row['status'] ?? '');
            if (array_key_exists($key, $out)) {
                $out[$key] = (int) $row['total'];
            }
        }

        return $out;
    }

    public function reviewerWorkload(): array
    {
        return $this->db->query("
            SELECT
                u.id,
                CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) AS full_name,
                u.email,
                COUNT(ra.id) AS total_assignments,
                SUM(CASE WHEN ra.status = 'completed' THEN 1 ELSE 0 END) AS completed_reviews,
                SUM(CASE WHEN ra.status <> 'completed' OR ra.status IS NULL THEN 1 ELSE 0 END) AS pending_reviews
            FROM users u
            INNER JOIN roles r ON r.id = u.role_id AND r.code = 'reviewer'
            LEFT JOIN review_assignments ra ON ra.reviewer_id = u.id
            GROUP BY u.id, u.first_name, u.last_name, u.email
            ORDER BY total_assignments DESC, completed_reviews DESC, u.id DESC
            LIMIT 8
        ")->fetchAll();
    }

    public function conferenceSummary(): array
    {
        return $this->db->query("
            SELECT
                c.id,
                c.title_en,
                c.title_ar,
                c.status,
                COUNT(DISTINCT s.id) AS submissions_count,
                COUNT(DISTINCT t.id) AS tracks_count
            FROM conferences c
            LEFT JOIN submissions s ON s.conference_id = c.id
            LEFT JOIN tracks t ON t.conference_id = c.id
            GROUP BY c.id, c.title_en, c.title_ar, c.status
            ORDER BY c.id DESC
            LIMIT 10
        ")->fetchAll();
    }

    public function recentActivity(): array
    {
        return $this->db->query("
            SELECT
                al.id,
                al.action,
                al.entity_name,
                al.entity_id,
                al.created_at,
                CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) AS full_name
            FROM audit_logs al
            LEFT JOIN users u ON u.id = al.user_id
            ORDER BY al.id DESC
            LIMIT 12
        ")->fetchAll();
    }
}
