<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;

final class ProductionModel extends Model
{
    public function submissionsForProduction(int $conferenceId = 0): array
    {
        $sql = "
            SELECT s.*, c.title_en AS conference_title_en, c.title_ar AS conference_title_ar
            FROM submissions s
            LEFT JOIN conferences c ON c.id = s.conference_id
            WHERE s.status IN ('accepted','camera_ready','published')
        ";
        $params = [];
        if ($conferenceId > 0) {
            $sql .= " AND s.conference_id = :conference_id";
            $params[':conference_id'] = $conferenceId;
        }
        $sql .= " ORDER BY s.id DESC LIMIT 300";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

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

    public function copyediting(int $submissionId): ?array
    {
        $stmt = $this->db->prepare("
            SELECT ca.*, u.first_name, u.last_name, u.email
            FROM copyediting_assignments ca
            LEFT JOIN users u ON u.id = ca.copyeditor_id
            WHERE ca.submission_id = :submission_id
            LIMIT 1
        ");
        $stmt->execute([':submission_id' => $submissionId]);
        return $stmt->fetch() ?: null;
    }

    public function copyeditors(): array
    {
        return $this->db->query("
            SELECT id, first_name, last_name, email
            FROM users
            WHERE COALESCE(is_active,1)=1
            ORDER BY first_name, last_name
        ")->fetchAll();
    }

    public function assignCopyeditor(int $submissionId, int $copyeditorId, int $assignedBy, ?string $dueDate): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO copyediting_assignments (submission_id, copyeditor_id, assigned_by, due_date, status)
            VALUES (:submission_id, :copyeditor_id, :assigned_by, :due_date, 'assigned')
            ON DUPLICATE KEY UPDATE
                copyeditor_id = VALUES(copyeditor_id),
                assigned_by = VALUES(assigned_by),
                due_date = VALUES(due_date),
                status = 'assigned'
        ");
        $stmt->execute([
            ':submission_id' => $submissionId,
            ':copyeditor_id' => $copyeditorId,
            ':assigned_by' => $assignedBy,
            ':due_date' => $dueDate ?: null,
        ]);
    }

    public function updateCopyediting(int $submissionId, string $status, string $notesEn, string $notesAr): void
    {
        $stmt = $this->db->prepare("
            UPDATE copyediting_assignments
            SET status = :status,
                notes_en = :notes_en,
                notes_ar = :notes_ar
            WHERE submission_id = :submission_id
        ");
        $stmt->execute([
            ':status' => $status,
            ':notes_en' => $notesEn ?: null,
            ':notes_ar' => $notesAr ?: null,
            ':submission_id' => $submissionId,
        ]);
    }

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

    public function addGalley(array $data): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO submission_galleys (
                submission_id, label, lang_code, file_path, original_filename, file_type,
                is_published, published_at, uploaded_by
            ) VALUES (
                :submission_id, :label, :lang_code, :file_path, :original_filename, :file_type,
                0, NULL, :uploaded_by
            )
        ");
        $stmt->execute([
            ':submission_id' => $data['submission_id'],
            ':label' => $data['label'],
            ':lang_code' => $data['lang_code'],
            ':file_path' => $data['file_path'],
            ':original_filename' => $data['original_filename'] ?: null,
            ':file_type' => $data['file_type'],
            ':uploaded_by' => $data['uploaded_by'] ?: null,
        ]);
    }

    public function toggleGalley(int $galleyId, int $publish): void
    {
        $stmt = $this->db->prepare("
            UPDATE submission_galleys
            SET is_published = :is_published,
                published_at = CASE WHEN :is_published2 = 1 THEN NOW() ELSE NULL END
            WHERE id = :id
        ");
        $stmt->execute([
            ':id' => $galleyId,
            ':is_published' => $publish ? 1 : 0,
            ':is_published2' => $publish ? 1 : 0,
        ]);
    }

    public function doi(int $submissionId): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM submission_doi_metadata WHERE submission_id = :submission_id LIMIT 1");
        $stmt->execute([':submission_id' => $submissionId]);
        return $stmt->fetch() ?: null;
    }

    public function saveDoi(int $submissionId, array $data): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO submission_doi_metadata (
                submission_id, doi, landing_url, publisher_name, year, meta_json, is_ready
            ) VALUES (
                :submission_id, :doi, :landing_url, :publisher_name, :year, :meta_json, :is_ready
            )
            ON DUPLICATE KEY UPDATE
                doi = VALUES(doi),
                landing_url = VALUES(landing_url),
                publisher_name = VALUES(publisher_name),
                year = VALUES(year),
                meta_json = VALUES(meta_json),
                is_ready = VALUES(is_ready)
        ");
        $stmt->execute([
            ':submission_id' => $submissionId,
            ':doi' => $data['doi'] ?: null,
            ':landing_url' => $data['landing_url'] ?: null,
            ':publisher_name' => $data['publisher_name'] ?: null,
            ':year' => $data['year'] ?: null,
            ':meta_json' => $data['meta_json'] ?: null,
            ':is_ready' => !empty($data['is_ready']) ? 1 : 0,
        ]);
    }
}
