<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use App\Services\NotificationService;
use App\Core\Database;

final class ProceedingsModel extends Model
{
    public function conferences(): array
    {
        return $this->db->query("SELECT * FROM conferences ORDER BY id DESC")->fetchAll();
    }

    public function volumesByConference(int $conferenceId): array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM proceedings_volumes
            WHERE conference_id = :conference_id
            ORDER BY volume_no DESC, id DESC
        ");
        $stmt->execute([':conference_id' => $conferenceId]);
        return $stmt->fetchAll();
    }

    public function issuesByVolume(int $volumeId): array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM proceedings_issues
            WHERE volume_id = :volume_id
            ORDER BY issue_no DESC, id DESC
        ");
        $stmt->execute([':volume_id' => $volumeId]);
        return $stmt->fetchAll();
    }

    public function itemsByIssue(int $issueId): array
    {
        $stmt = $this->db->prepare("
            SELECT pi.*, s.submission_no, s.title, s.title_ar
            FROM proceedings_items pi
            INNER JOIN submissions s ON s.id = pi.submission_id
            WHERE pi.issue_id = :issue_id
            ORDER BY pi.sort_order ASC, pi.id ASC
        ");
        $stmt->execute([':issue_id' => $issueId]);
        return $stmt->fetchAll();
    }

    public function createVolume(array $data): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO proceedings_volumes (conference_id, volume_no, year, title_en, title_ar, isbn, is_published, published_at)
            VALUES (:conference_id, :volume_no, :year, :title_en, :title_ar, :isbn, :is_published,
                    CASE WHEN :is_published2=1 THEN NOW() ELSE NULL END)
        ");
        $stmt->execute([
            ':conference_id' => $data['conference_id'],
            ':volume_no' => $data['volume_no'],
            ':year' => $data['year'] ?: null,
            ':title_en' => $data['title_en'] ?: null,
            ':title_ar' => $data['title_ar'] ?: null,
            ':isbn' => $data['isbn'] ?: null,
            ':is_published' => !empty($data['is_published']) ? 1 : 0,
            ':is_published2' => !empty($data['is_published']) ? 1 : 0,
        ]);
    }

    public function createIssue(array $data): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO proceedings_issues (volume_id, issue_no, title_en, title_ar, is_published, published_at)
            VALUES (:volume_id, :issue_no, :title_en, :title_ar, :is_published,
                    CASE WHEN :is_published2=1 THEN NOW() ELSE NULL END)
        ");
        $stmt->execute([
            ':volume_id' => $data['volume_id'],
            ':issue_no' => $data['issue_no'],
            ':title_en' => $data['title_en'] ?: null,
            ':title_ar' => $data['title_ar'] ?: null,
            ':is_published' => !empty($data['is_published']) ? 1 : 0,
            ':is_published2' => !empty($data['is_published']) ? 1 : 0,
        ]);
    }

    public function attachSubmission(int $issueId, int $submissionId, int $sortOrder): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO proceedings_items (issue_id, submission_id, sort_order, is_published, published_at)
            VALUES (:issue_id, :submission_id, :sort_order, 1, NOW())
            ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order), is_published = 1, published_at = NOW()
        ");
        $stmt->execute([
            ':issue_id' => $issueId,
            ':submission_id' => $submissionId,
            ':sort_order' => $sortOrder,
        ]);

        $stmt = $this->db->prepare("UPDATE submissions SET status='published' WHERE id = :id");
        $stmt->execute([':id' => $submissionId]);

        // Notify author that paper is published
        $db = Database::getInstance();
        $st = $db->prepare("SELECT author_id, submission_no FROM submissions WHERE id = :id LIMIT 1");
        $st->execute([':id' => $submissionId]);
        $srow = $st->fetch();
        if ($srow && !empty($srow['author_id'])) {
            NotificationService::push((int)$srow['author_id'], 'publishing', trans('paper_published') . ' — ' . (string)($srow['submission_no'] ?? ''));
        }

    }
}
