<?php

namespace App\Services;

use App\Core\Database;

class GovernanceEngine
{
    public static function platformOverview(): array
    {
        $db = Database::getInstance();

        return [

            'organizations' =>
                (int)$db->query("
                    SELECT COUNT(*)
                    FROM organizations
                ")->fetchColumn(),

            'conferences' =>
                (int)$db->query("
                    SELECT COUNT(*)
                    FROM conferences
                ")->fetchColumn(),

            'tracks' =>
                (int)$db->query("
                    SELECT COUNT(*)
                    FROM tracks
                ")->fetchColumn(),

            'submissions' =>
                (int)$db->query("
                    SELECT COUNT(*)
                    FROM submissions
                ")->fetchColumn(),

            'reviews' =>
                (int)$db->query("
                    SELECT COUNT(*)
                    FROM review_assignments
                ")->fetchColumn(),

            'reviewers' =>
                (int)$db->query("
                    SELECT COUNT(*)
                    FROM conference_reviewers
                ")->fetchColumn()

        ];
    }

    public static function riskCounters(): array
    {
        $db = Database::getInstance();

        $orphanTracks =
            (int)$db->query("

                SELECT COUNT(*)

                FROM tracks t

                WHERE NOT EXISTS
                (
                    SELECT 1
                    FROM track_chair_assignments a
                    WHERE a.track_id=t.id
                    AND a.is_active=1
                )

            ")->fetchColumn();

        $overloadedReviewers =
            (int)$db->query("

                SELECT COUNT(*)

                FROM
                (
                    SELECT reviewer_id

                    FROM review_assignments

                    WHERE status<>'completed'

                    GROUP BY reviewer_id

                    HAVING COUNT(*)>=10
                ) x

            ")->fetchColumn();

        $criticalSubmissions =
            (int)$db->query("

                SELECT COUNT(*)

                FROM
                (
                    SELECT s.id

                    FROM submissions s

                    JOIN review_assignments ra
                        ON ra.submission_id=s.id

                    WHERE
                        ra.status<>'completed'
                        AND ra.due_date<CURDATE()

                    GROUP BY s.id

                    HAVING COUNT(*)>=2
                ) z

            ")->fetchColumn();

        return [

            'orphan_tracks' =>
                $orphanTracks,

            'overloaded_reviewers' =>
                $overloadedReviewers,

            'critical_submissions' =>
                $criticalSubmissions

        ];
    }

    public static function platformHealth(): int
    {
        $risk =
            self::riskCounters();

        return max(
            0,
            100
            -
            ($risk['orphan_tracks'] * 2)
            -
            ($risk['overloaded_reviewers'] * 3)
            -
            ($risk['critical_submissions'] * 4)
        );
    }

    public static function recommendedActions(): array
    {
        $risk =
            self::riskCounters();

        return [

            [
                'action' =>
                    'Assign missing track chairs',

                'count' =>
                    $risk['orphan_tracks'],

                'severity' =>
                    $risk['orphan_tracks'] > 0
                    ? 'Critical'
                    : 'Healthy'
            ],

            [
                'action' =>
                    'Rebalance overloaded reviewers',

                'count' =>
                    $risk['overloaded_reviewers'],

                'severity' =>
                    $risk['overloaded_reviewers'] > 0
                    ? 'High'
                    : 'Healthy'
            ],

            [
                'action' =>
                    'Intervene in critical submissions',

                'count' =>
                    $risk['critical_submissions'],

                'severity' =>
                    $risk['critical_submissions'] > 0
                    ? 'Critical'
                    : 'Healthy'
            ]

        ];
    }
}
