<?php

namespace App\Services\Certification;

use App\Core\Database;
use PDO;
use Throwable;

class WorkflowStateValidator
{
    private PDO $db;

    private array $validStages = [
        'submission',
        'screening',
        'review',
        'revision',
        'decision',
        'galley',
        'production',
        'issue',
        'assigned_to_issue',
        'published',
        'proofreading'
    ];

    private array $validStatuses = [
        'draft',
        'submitted',
        'screening',
        'under_review',
        'revision_required',
        'revised',
        'accepted',
        'rejected',
        'galley',
        'assigned_to_issue',
        'production',
        'published'
    ];

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    public function validate(?int $conferenceId = null): ValidationResult
    {
        $result = new ValidationResult('Workflow State Validator');

        $submissions = $this->submissions($conferenceId);
        $result->checked = count($submissions);

        foreach ($submissions as $s) {
            $submissionId = (int)$s['id'];
            $issues = [];

            $stage = trim((string)($s['stage'] ?? ''));
            $status = trim((string)($s['status'] ?? ''));

            if ($stage === '') {
                $issues[] = ['critical', 'missing_stage', 'Submission has no workflow stage.'];
            } elseif (!in_array($stage, $this->validStages, true)) {
                $issues[] = ['critical', 'invalid_stage', 'Invalid workflow stage: ' . $stage];
            }

            if ($status === '') {
                $issues[] = ['critical', 'missing_status', 'Submission has no status.'];
            } elseif (!in_array($status, $this->validStatuses, true)) {
                $issues[] = ['critical', 'invalid_status', 'Invalid submission status: ' . $status];
            }

            $reviewers = $this->countReviewers($submissionId);
            $completedReviews = $this->countCompletedReviews($submissionId);
            $approvedGalleys = $this->countApprovedGalleys($submissionId);
            $cameraReady = $this->countCameraReady($submissionId);
            $issueLinked = !empty($s['issue_id']);
            $publishedAt = !empty($s['published_at']);

            if (in_array($stage, ['review'], true) && $reviewers < 1) {
                $issues[] = ['critical', 'review_without_reviewers', 'Submission is in review stage but has no reviewer assignments.'];
            }

            if (($status === 'under_review' || $stage === 'review') && $reviewers < 1) {
                $issues[] = ['critical', 'under_review_without_assignments', 'Submission is under review but no reviewer is assigned.'];
            }

            if (($status === 'accepted' || ($s['decision'] ?? '') === 'accepted' || ($s['decision'] ?? '') === 'accept') && empty($s['decision'])) {
                $issues[] = ['warning', 'accepted_without_decision_field', 'Submission is accepted but decision field is empty or inconsistent.'];
            }

            if (in_array($stage, ['decision', 'production', 'issue', 'assigned_to_issue', 'published'], true) && $reviewers > 0 && $completedReviews < 1) {
                $issues[] = ['warning', 'advanced_without_completed_review', 'Submission advanced beyond review with no completed review assignment.'];
            }

            if (in_array($stage, ['production', 'issue', 'assigned_to_issue', 'published'], true) && $approvedGalleys < 1) {
                $issues[] = ['warning', 'production_without_approved_galley', 'Submission is in production/publication path but no approved galley was found.'];
            }

            if (in_array($stage, ['production'], true) && $cameraReady < 1 && $approvedGalleys < 1) {
                $issues[] = ['warning', 'production_without_camera_ready', 'Production submission has no camera-ready version and no approved galley.'];
            }

            if (in_array($stage, ['issue', 'assigned_to_issue', 'published'], true) && !$issueLinked) {
                $issues[] = ['critical', 'issue_stage_without_issue', 'Submission is in issue/publication stage but issue_id is missing.'];
            }

            if ($status === 'published' || $stage === 'published') {
                if (!$publishedAt) {
                    $issues[] = ['critical', 'published_without_published_at', 'Submission is published but published_at is empty.'];
                }

                if (!$issueLinked) {
                    $issues[] = ['critical', 'published_without_issue', 'Published submission has no issue_id.'];
                }

                if ($approvedGalleys < 1) {
                    $issues[] = ['warning', 'published_without_approved_galley', 'Published submission has no approved galley.'];
                }
            }

            foreach ($issues as $i) {
                $result->addIssue(
                    $i[0],
                    $i[1],
                    $i[2],
                    [
                        'submission_id' => $submissionId,
                        'submission_no' => $s['submission_no'] ?? '',
                        'title' => $s['title'] ?? ''
                    ]
                );
            }

            $result->addItem([
                'submission_id' => $submissionId,
                'submission_no' => $s['submission_no'] ?? '',
                'title' => $s['title'] ?? '',
                'stage' => $stage,
                'status' => $status,
                'decision' => $s['decision'] ?? '',
                'reviewers' => $reviewers,
                'completed_reviews' => $completedReviews,
                'camera_ready' => $cameraReady,
                'approved_galleys' => $approvedGalleys,
                'issue_id' => $s['issue_id'] ?? '',
                'published_at' => $s['published_at'] ?? '',
                'issues' => $issues,
                'health' => empty($issues) ? 'pass' : 'attention'
            ]);
        }

        return $result;
    }

    private function submissions(?int $conferenceId): array
    {
        $where = '';
        $params = [];

        if ($conferenceId !== null && $conferenceId > 0) {
            $where = 'WHERE conference_id = ?';
            $params[] = $conferenceId;
        }

        $stmt = $this->db->prepare("
            SELECT
                id,
                conference_id,
                org_id,
                submission_no,
                title,
                status,
                stage,
                decision,
                production_status,
                issue_id,
                doi,
                submitted_at,
                published_at,
                file_path,
                original_filename,
                created_at,
                updated_at
            FROM submissions
            $where
            ORDER BY id DESC
        ");

        $stmt->execute($params);

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function countReviewers(int $submissionId): int
    {
        return $this->count("
            SELECT COUNT(*)
            FROM review_assignments
            WHERE submission_id = ?
        ", [$submissionId]);
    }

    private function countCompletedReviews(int $submissionId): int
    {
        return $this->count("
            SELECT COUNT(*)
            FROM review_assignments
            WHERE submission_id = ?
              AND status = 'completed'
        ", [$submissionId]);
    }

    private function countApprovedGalleys(int $submissionId): int
    {
        return $this->count("
            SELECT COUNT(*)
            FROM production_galleys
            WHERE submission_id = ?
              AND status IN ('approved','published')
        ", [$submissionId]);
    }

    private function countCameraReady(int $submissionId): int
    {
        return $this->count("
            SELECT COUNT(*)
            FROM submission_versions
            WHERE submission_id = ?
              AND upload_type IN ('camera_ready','final','production')
        ", [$submissionId]);
    }

    private function count(string $sql, array $params): int
    {
        try {
            $stmt = $this->db->prepare($sql);
            $stmt->execute($params);

            return (int)$stmt->fetchColumn();
        } catch (Throwable $e) {
            return 0;
        }
    }
}
