<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;

final class Review extends Model
{
    public function submissionById(int $submissionId): ?array
    {
        $stmt = $this->db->prepare("
            SELECT s.*, c.title_en AS conference_title_en, c.title_ar AS conference_title_ar,
                   c.review_instructions_en, c.review_instructions_ar
            FROM submissions s
            LEFT JOIN conferences c ON c.id = s.conference_id
            WHERE s.id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $submissionId]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function reviewerOptions(): array
    {
        return $this->db->query("
            SELECT u.id, u.first_name, u.last_name, u.email
            FROM users u
            INNER JOIN roles r ON r.id = u.role_id AND r.code = 'reviewer'
            WHERE COALESCE(u.is_active,1) = 1
            ORDER BY u.first_name, u.last_name
        ")->fetchAll();
    }

    public function assignmentsBySubmission(int $submissionId): array
    {
        $stmt = $this->db->prepare("
            SELECT ra.*, u.first_name, u.last_name, u.email,
                   rr.rating_score, rr.rating_notes
            FROM review_assignments ra
            INNER JOIN users u ON u.id = ra.reviewer_id
            LEFT JOIN reviewer_ratings rr ON rr.review_assignment_id = ra.id
            WHERE ra.submission_id = :submission_id
            ORDER BY ra.id DESC
        ");
        $stmt->execute([':submission_id' => $submissionId]);
        return $stmt->fetchAll();
    }

    public function assignmentsForReviewer(int $reviewerId): array
    {
        $stmt = $this->db->prepare("
            SELECT ra.*, s.title, s.title_ar, s.submission_no, s.conference_id,
                   c.title_en AS conference_title_en, c.title_ar AS conference_title_ar,
                   c.review_instructions_en, c.review_instructions_ar
            FROM review_assignments ra
            INNER JOIN submissions s ON s.id = ra.submission_id
            LEFT JOIN conferences c ON c.id = s.conference_id
            WHERE ra.reviewer_id = :reviewer_id
            ORDER BY ra.id DESC
        ");
        $stmt->execute([':reviewer_id' => $reviewerId]);
        return $stmt->fetchAll();
    }

    public function assignmentById(int $id): ?array
    {
        $stmt = $this->db->prepare("
            SELECT ra.*, s.title, s.title_ar, s.submission_no, s.conference_id,
                   c.title_en AS conference_title_en, c.title_ar AS conference_title_ar,
                   c.review_instructions_en, c.review_instructions_ar,
                   u.first_name, u.last_name
            FROM review_assignments ra
            INNER JOIN submissions s ON s.id = ra.submission_id
            LEFT JOIN conferences c ON c.id = s.conference_id
            LEFT JOIN users u ON u.id = ra.reviewer_id
            WHERE ra.id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $id]);
        return $stmt->fetch() ?: null;
    }

    public function assignmentExists(int $submissionId, int $reviewerId): bool
    {
        $stmt = $this->db->prepare("
            SELECT id
            FROM review_assignments
            WHERE submission_id = :submission_id
              AND reviewer_id = :reviewer_id
            LIMIT 1
        ");
        $stmt->execute([
            ':submission_id' => $submissionId,
            ':reviewer_id' => $reviewerId,
        ]);
        return (bool) $stmt->fetchColumn();
    }

    public function assignReviewer(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO review_assignments (
                submission_id, reviewer_id, assigned_by, due_date, status, created_at
            ) VALUES (
                :submission_id, :reviewer_id, :assigned_by, :due_date, :status, NOW()
            )
        ");
        $stmt->execute([
            ':submission_id' => $data['submission_id'],
            ':reviewer_id' => $data['reviewer_id'],
            ':assigned_by' => $data['assigned_by'] ?? null,
            ':due_date' => $data['due_date'] ?: null,
            ':status' => $data['status'] ?? 'assigned',
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function saveReviewForm(int $assignmentId, array $data): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO reviews (
                review_assignment_id, overall_score, comments_to_author, comments_to_editor,
                recommendation, submitted_at
            ) VALUES (
                :review_assignment_id, :overall_score, :comments_to_author, :comments_to_editor,
                :recommendation, NOW()
            )
            ON DUPLICATE KEY UPDATE
                overall_score = VALUES(overall_score),
                comments_to_author = VALUES(comments_to_author),
                comments_to_editor = VALUES(comments_to_editor),
                recommendation = VALUES(recommendation),
                submitted_at = NOW()
        ");
        $stmt->execute([
            ':review_assignment_id' => $assignmentId,
            ':overall_score' => $data['overall_score'] ?: null,
            ':comments_to_author' => $data['comments_to_author'] ?: null,
            ':comments_to_editor' => $data['comments_to_editor'] ?: null,
            ':recommendation' => $data['recommendation'] ?: null,
        ]);

        $stmt = $this->db->prepare("UPDATE review_assignments SET status = 'completed' WHERE id = :id");
        $stmt->execute([':id' => $assignmentId]);
    }

    public function addReviewFile(int $assignmentId, string $path, string $name, ?int $uploadedBy = null): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO review_files (review_assignment_id, file_path, original_filename, uploaded_by)
            VALUES (:review_assignment_id, :file_path, :original_filename, :uploaded_by)
        ");
        $stmt->execute([
            ':review_assignment_id' => $assignmentId,
            ':file_path' => $path,
            ':original_filename' => $name,
            ':uploaded_by' => $uploadedBy,
        ]);
    }

    public function reviewFiles(int $assignmentId): array
    {
        $stmt = $this->db->prepare("SELECT * FROM review_files WHERE review_assignment_id = :id ORDER BY id DESC");
        $stmt->execute([':id' => $assignmentId]);
        return $stmt->fetchAll();
    }

    public function saveReviewerRating(int $assignmentId, int $reviewerId, int $ratedBy, float $score, string $notes = ''): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO reviewer_ratings (review_assignment_id, reviewer_id, rated_by, rating_score, rating_notes)
            VALUES (:review_assignment_id, :reviewer_id, :rated_by, :rating_score, :rating_notes)
            ON DUPLICATE KEY UPDATE
                reviewer_id = VALUES(reviewer_id),
                rated_by = VALUES(rated_by),
                rating_score = VALUES(rating_score),
                rating_notes = VALUES(rating_notes)
        ");
        $stmt->execute([
            ':review_assignment_id' => $assignmentId,
            ':reviewer_id' => $reviewerId,
            ':rated_by' => $ratedBy,
            ':rating_score' => $score,
            ':rating_notes' => $notes ?: null,
        ]);
    }
}
