<?php

namespace App\Services\Governance;

use App\Core\Database;
use PDO;

final class SubmissionIntegrityService
{
    public static function summary(): array
    {
        $db = Database::getInstance();

        $columns = self::columns($db, 'submissions');

        $select = [
            'id',
            self::col($columns, 'submission_no') ? 'submission_no' : "id AS submission_no",
            self::col($columns, 'title') ? 'title' :
                (self::col($columns, 'title_en') ? 'title_en AS title' : "'' AS title"),
            self::col($columns, 'status') ? 'status' : "'' AS status",
            self::col($columns, 'stage') ? 'stage' : "'' AS stage",
            self::col($columns, 'workflow_stage') ? 'workflow_stage' : "'' AS workflow_stage",
            self::col($columns, 'conference_id') ? 'conference_id' : "0 AS conference_id"
        ];

        $stmt = $db->query("
            SELECT
                " . implode(",\n                ", $select) . "
            FROM submissions
            ORDER BY id DESC
        ");

        $healthy = 0;
        $warnings = 0;
        $errors = 0;
        $items = [];

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

            if (!$row) {
                continue;
            }

            $state = 'healthy';
            $notes = [];

            if (trim((string)($row['title'] ?? '')) === '') {
                $state = 'warning';
                $notes[] = 'Submission title is empty';
            }

            if ((int)($row['conference_id'] ?? 0) <= 0) {
                $state = 'error';
                $notes[] = 'Conference not assigned';
            }

            if ($state === 'healthy') {
                $healthy++;
            } elseif ($state === 'warning') {
                $warnings++;
            } else {
                $errors++;
            }

            $items[] = [
                'id' => (int)($row['id'] ?? 0),
                'submission_no' => $row['submission_no'] ?? '',
                'status' => $row['status'] ?? '',
                'stage' => $row['stage'] ?? '',
                'workflow_stage' => $row['workflow_stage'] ?? '',
                'state' => $state,
                'notes' => $notes
            ];
        }

        $total = max(1, $healthy + $warnings + $errors);

        return [
            'score' => (int)round(($healthy / $total) * 100),
            'healthy' => $healthy,
            'warnings' => $warnings,
            'errors' => $errors,
            'items' => $items
        ];
    }

    private static function columns(PDO $db, string $table): array
    {
        $stmt = $db->query("SHOW COLUMNS FROM `$table`");
        $cols = [];

        while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $cols[] = $r['Field'];
        }

        return $cols;
    }

    private static function col(array $columns, string $name): bool
    {
        return in_array($name, $columns, true);
    }
}
