<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use Throwable;

final class AnalyticsModel extends Model
{
    private function tableExists(string $table): bool
    {
        $stmt = $this->db->prepare("
            SELECT COUNT(*)
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = :table_name
        ");
        $stmt->execute([':table_name' => $table]);
        return ((int) $stmt->fetchColumn()) > 0;
    }

    private function columnExists(string $table, string $column): bool
    {
        $stmt = $this->db->prepare("
            SELECT COUNT(*)
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = :table_name
              AND COLUMN_NAME = :column_name
        ");
        $stmt->execute([
            ':table_name' => $table,
            ':column_name' => $column,
        ]);
        return ((int) $stmt->fetchColumn()) > 0;
    }

    private function safeCount(string $sql): int
    {
        try {
            return (int) $this->db->query($sql)->fetchColumn();
        } catch (Throwable $e) {
            return 0;
        }
    }

    public function dashboardStats(): array
    {
        $stats = [
            'total_submissions' => 0,
            'total_reviews' => 0,
            'published_count' => 0,
            'accepted_count' => 0,
            'rejected_count' => 0,
            'revision_count' => 0,
            'under_review_count' => 0,
            'reviews_completed' => 0,
            'reviews_pending' => 0,
        ];

        if ($this->tableExists('submissions')) {
            $stats['total_submissions'] = $this->safeCount("SELECT COUNT(*) FROM submissions");

            if ($this->columnExists('submissions', 'status')) {
                $stats['accepted_count'] = $this->safeCount("SELECT COUNT(*) FROM submissions WHERE status = 'accepted'");
                $stats['rejected_count'] = $this->safeCount("SELECT COUNT(*) FROM submissions WHERE status = 'rejected'");
                $stats['revision_count'] = $this->safeCount("SELECT COUNT(*) FROM submissions WHERE status = 'revision_required'");
                $stats['under_review_count'] = $this->safeCount("SELECT COUNT(*) FROM submissions WHERE status = 'under_review'");
            }
        }

        if ($this->tableExists('reviews')) {
            $stats['total_reviews'] = $this->safeCount("SELECT COUNT(*) FROM reviews");
        }

        if ($this->tableExists('review_assignments') && $this->columnExists('review_assignments', 'status')) {
            $stats['reviews_completed'] = $this->safeCount("SELECT COUNT(*) FROM review_assignments WHERE status = 'completed'");
            $stats['reviews_pending'] = $this->safeCount("SELECT COUNT(*) FROM review_assignments WHERE status IN ('assigned','accepted','overdue')");
        }

        if ($this->tableExists('proceedings')) {
            if ($this->columnExists('proceedings', 'is_published')) {
                $stats['published_count'] = $this->safeCount("SELECT COUNT(*) FROM proceedings WHERE is_published = 1");
            } else {
                $stats['published_count'] = $this->safeCount("SELECT COUNT(*) FROM proceedings");
            }
        }

        return $stats;
    }

    public function statusDistribution(): array
    {
        try {
            if (!$this->tableExists('submissions') || !$this->columnExists('submissions', 'status')) {
                return [];
            }

            $stmt = $this->db->query("
                SELECT status, COUNT(*) AS total
                FROM submissions
                GROUP BY status
                ORDER BY total DESC, status ASC
            ");
            return $stmt->fetchAll();
        } catch (Throwable $e) {
            return [];
        }
    }

    public function submissionsByConference(): array
    {
        try {
            if (!$this->tableExists('conferences')) {
                return [];
            }

            $stmt = $this->db->query("
                SELECT c.id,
                       c.title_ar,
                       c.title_en,
                       COUNT(s.id) AS total_submissions
                FROM conferences c
                LEFT JOIN submissions s ON s.conference_id = c.id
                GROUP BY c.id, c.title_ar, c.title_en
                ORDER BY total_submissions DESC, c.id DESC
            ");
            return $stmt->fetchAll();
        } catch (Throwable $e) {
            return [];
        }
    }

    public function conferenceStats(): array
    {
        try {
            if (!$this->tableExists('conferences')) {
                return [];
            }

            $hasSubmissionStatus = $this->tableExists('submissions') && $this->columnExists('submissions', 'status');
            $hasProceedingsPublished = $this->tableExists('proceedings') && $this->columnExists('proceedings', 'is_published');

            $acceptedExpr = $hasSubmissionStatus
                ? "COUNT(DISTINCT CASE WHEN s.status = 'accepted' THEN s.id END)"
                : "0";
            $rejectedExpr = $hasSubmissionStatus
                ? "COUNT(DISTINCT CASE WHEN s.status = 'rejected' THEN s.id END)"
                : "0";
            $revisionExpr = $hasSubmissionStatus
                ? "COUNT(DISTINCT CASE WHEN s.status = 'revision_required' THEN s.id END)"
                : "0";
            $publishedExpr = $hasProceedingsPublished
                ? "COUNT(DISTINCT CASE WHEN p.is_published = 1 THEN p.paper_id END)"
                : "COUNT(DISTINCT p.paper_id)";

            $sql = "
                SELECT c.id,
                       c.title_ar,
                       c.title_en,
                       COUNT(DISTINCT s.id) AS submissions_count,
                       {$acceptedExpr} AS accepted_count,
                       {$rejectedExpr} AS rejected_count,
                       {$revisionExpr} AS revision_count,
                       {$publishedExpr} AS published_count
                FROM conferences c
                LEFT JOIN submissions s ON s.conference_id = c.id
                LEFT JOIN proceedings p ON p.conference_id = c.id AND p.paper_id = s.id
                GROUP BY c.id, c.title_ar, c.title_en
                ORDER BY c.id DESC
            ";

            $stmt = $this->db->query($sql);
            return $stmt->fetchAll();
        } catch (Throwable $e) {
            return [];
        }
    }
}
