<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use Throwable;

final class PublishingModel extends Model
{
    private function tableExists(string $table): bool
    {
        $stmt = $this->db->prepare("
            SELECT COUNT(*)
            FROM information_schema.TABLES
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = :table_name
        ");
        $stmt->execute([':table_name' => $table]);
        return ((int) $stmt->fetchColumn()) > 0;
    }

    private function columnExists(string $table, string $column): bool
    {
        $stmt = $this->db->prepare("
            SELECT COUNT(*)
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = :table_name
              AND COLUMN_NAME = :column_name
        ");
        $stmt->execute([
            ':table_name' => $table,
            ':column_name' => $column,
        ]);
        return ((int) $stmt->fetchColumn()) > 0;
    }

    public function getBySubmission(int $submissionId): ?array
    {
        try {
            $stmt = $this->db->prepare("
                SELECT p.*, c.title_ar, c.title_en, s.title AS submission_title, s.submission_no
                FROM proceedings p
                INNER JOIN submissions s ON s.id = p.paper_id
                INNER JOIN conferences c ON c.id = p.conference_id
                WHERE p.paper_id = :paper_id
                LIMIT 1
            ");
            $stmt->execute([':paper_id' => $submissionId]);
            $row = $stmt->fetch();
            return $row ?: null;
        } catch (Throwable $e) {
            return null;
        }
    }

    public function upsertCameraReady(int $conferenceId, int $submissionId, string $cameraReadyPath, ?string $doi = null): int
    {
        $existing = $this->getBySubmission($submissionId);

        if ($existing) {
            $stmt = $this->db->prepare("
                UPDATE proceedings
                SET camera_ready_path = :camera_ready_path,
                    doi = :doi
                WHERE paper_id = :paper_id
            ");
            $stmt->execute([
                ':camera_ready_path' => $cameraReadyPath,
                ':doi' => $doi,
                ':paper_id' => $submissionId,
            ]);
            return (int) $existing['id'];
        }

        $hasPublishedAt = $this->columnExists('proceedings', 'published_at');
        $hasIsPublished = $this->columnExists('proceedings', 'is_published');

        if ($hasPublishedAt && $hasIsPublished) {
            $stmt = $this->db->prepare("
                INSERT INTO proceedings (
                    conference_id, paper_id, camera_ready_path, doi, published_at, is_published
                ) VALUES (
                    :conference_id, :paper_id, :camera_ready_path, :doi, NULL, 0
                )
            ");
        } else {
            $stmt = $this->db->prepare("
                INSERT INTO proceedings (
                    conference_id, paper_id, camera_ready_path, doi
                ) VALUES (
                    :conference_id, :paper_id, :camera_ready_path, :doi
                )
            ");
        }

        $stmt->execute([
            ':conference_id' => $conferenceId,
            ':paper_id' => $submissionId,
            ':camera_ready_path' => $cameraReadyPath,
            ':doi' => $doi,
        ]);

        return (int) $this->db->lastInsertId();
    }

    public function publish(int $submissionId, ?string $doi = null): void
    {
        $hasPublishedAt = $this->columnExists('proceedings', 'published_at');
        $hasIsPublished = $this->columnExists('proceedings', 'is_published');

        if ($hasPublishedAt && $hasIsPublished) {
            $stmt = $this->db->prepare("
                UPDATE proceedings
                SET is_published = 1,
                    published_at = NOW(),
                    doi = COALESCE(:doi, doi)
                WHERE paper_id = :paper_id
            ");
        } elseif ($this->columnExists('proceedings', 'doi')) {
            $stmt = $this->db->prepare("
                UPDATE proceedings
                SET doi = COALESCE(:doi, doi)
                WHERE paper_id = :paper_id
            ");
        } else {
            $stmt = $this->db->prepare("
                UPDATE proceedings
                SET paper_id = paper_id
                WHERE paper_id = :paper_id
            ");
        }

        $stmt->execute([
            ':doi' => $doi,
            ':paper_id' => $submissionId,
        ]);
    }

    public function unpublish(int $submissionId): void
    {
        if ($this->columnExists('proceedings', 'is_published')) {
            $stmt = $this->db->prepare("
                UPDATE proceedings
                SET is_published = 0
                WHERE paper_id = :paper_id
            ");
            $stmt->execute([':paper_id' => $submissionId]);
        }
    }

    public function publishedByConference(int $conferenceId): array
    {
        try {
            $hasPublishedAt = $this->columnExists('proceedings', 'published_at');
            $hasIsPublished = $this->columnExists('proceedings', 'is_published');

            $sql = "
                SELECT p.*, s.title AS submission_title, s.abstract, s.keywords, s.submission_no,
                       c.title_ar, c.title_en
                FROM proceedings p
                INNER JOIN submissions s ON s.id = p.paper_id
                INNER JOIN conferences c ON c.id = p.conference_id
                WHERE p.conference_id = :conference_id
            ";

            if ($hasIsPublished) {
                $sql .= " AND p.is_published = 1";
            }

            $sql .= $hasPublishedAt
                ? " ORDER BY p.published_at DESC, p.id DESC"
                : " ORDER BY p.id DESC";

            $stmt = $this->db->prepare($sql);
            $stmt->execute([':conference_id' => $conferenceId]);
            return $stmt->fetchAll();
        } catch (Throwable $e) {
            return [];
        }
    }

    public function publishedPaper(int $submissionId): ?array
    {
        try {
            $hasIsPublished = $this->columnExists('proceedings', 'is_published');

            $sql = "
                SELECT p.*, s.title AS submission_title, s.abstract, s.keywords, s.submission_no,
                       c.title_ar, c.title_en, t.name_ar, t.name_en
                FROM proceedings p
                INNER JOIN submissions s ON s.id = p.paper_id
                INNER JOIN conferences c ON c.id = p.conference_id
                INNER JOIN tracks t ON t.id = s.track_id
                WHERE p.paper_id = :paper_id
            ";

            if ($hasIsPublished) {
                $sql .= " AND p.is_published = 1";
            }

            $sql .= " LIMIT 1";

            $stmt = $this->db->prepare($sql);
            $stmt->execute([':paper_id' => $submissionId]);
            $row = $stmt->fetch();
            return $row ?: null;
        } catch (Throwable $e) {
            return null;
        }
    }

    public function archiveSearch(?int $conferenceId = null, string $keyword = ''): array
    {
        try {
            if (
                !$this->tableExists('proceedings') ||
                !$this->tableExists('submissions') ||
                !$this->tableExists('conferences') ||
                !$this->tableExists('tracks')
            ) {
                return [];
            }

            $hasIsPublished = $this->columnExists('proceedings', 'is_published');
            $hasPublishedAt = $this->columnExists('proceedings', 'published_at');
            $hasDoi = $this->columnExists('proceedings', 'doi');

            $doiSelect = $hasDoi ? "p.doi," : "NULL AS doi,";
            $publishedAtSelect = $hasPublishedAt ? "p.published_at," : "NULL AS published_at,";

            $sql = "
                SELECT p.id,
                       p.conference_id,
                       p.paper_id,
                       p.camera_ready_path,
                       {$doiSelect}
                       {$publishedAtSelect}
                       s.title AS submission_title,
                       s.abstract,
                       s.keywords,
                       s.submission_no,
                       c.slug,
                       c.title_ar,
                       c.title_en,
                       t.name_ar,
                       t.name_en
                FROM proceedings p
                INNER JOIN submissions s ON s.id = p.paper_id
                INNER JOIN conferences c ON c.id = p.conference_id
                INNER JOIN tracks t ON t.id = s.track_id
                WHERE 1=1
            ";

            $params = [];

            if ($hasIsPublished) {
                $sql .= " AND p.is_published = 1";
            }

            if ($conferenceId !== null && $conferenceId > 0) {
                $sql .= " AND p.conference_id = :conference_id";
                $params[':conference_id'] = $conferenceId;
            }

            if ($keyword !== '') {
                $sql .= " AND (
                    s.title LIKE :kw
                    OR s.abstract LIKE :kw
                    OR s.keywords LIKE :kw
                ";
                if ($hasDoi) {
                    $sql .= " OR p.doi LIKE :kw";
                }
                $sql .= ")";
                $params[':kw'] = '%' . $keyword . '%';
            }

            $sql .= $hasPublishedAt
                ? " ORDER BY p.published_at DESC, p.id DESC"
                : " ORDER BY p.id DESC";

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

            return $stmt->fetchAll();
        } catch (Throwable $e) {
            return [];
        }
    }

    public function publishedConferences(): array
    {
        try {
            if (!$this->tableExists('conferences') || !$this->tableExists('proceedings')) {
                return [];
            }

            $hasIsPublished = $this->columnExists('proceedings', 'is_published');

            $sql = "
                SELECT c.id, c.slug, c.title_ar, c.title_en, COUNT(p.id) AS papers_count
                FROM conferences c
                INNER JOIN proceedings p ON p.conference_id = c.id
            ";

            if ($hasIsPublished) {
                $sql .= " AND p.is_published = 1";
            }

            $sql .= "
                GROUP BY c.id, c.slug, c.title_ar, c.title_en
                ORDER BY c.id DESC
            ";

            $stmt = $this->db->query($sql);
            return $stmt->fetchAll();
        } catch (Throwable $e) {
            return [];
        }
    }
}
