<?php

namespace App\Controllers;

use App\Core\Database;

class ProceedingsController
{
    protected function db()
    {
        return Database::getInstance();
    }

    public function index()
    {
        $stmt = $this->db()->query("
            SELECT
                i.*,
                COUNT(pia.id) article_count
            FROM proceedings_issues i
            LEFT JOIN proceedings_issue_articles pia
                ON pia.issue_id=i.id
            GROUP BY i.id
            ORDER BY i.publication_year DESC,
                     i.id DESC
        ");

        $issues =
            $stmt->fetchAll(\PDO::FETCH_ASSOC);

        require BASE_PATH .
        '/app/views/public/proceedings/index.php';
    }

    public function issue()
    {
        $issueId =
            (int)($_GET['id'] ?? 0);

        if (!$issueId) {
            exit('Issue not found');
        }

        $stmt =
            $this->db()->prepare("
                SELECT *
                FROM proceedings_issues
                WHERE id=?
                LIMIT 1
            ");

        $stmt->execute([
            $issueId
        ]);

        $issue =
            $stmt->fetch(\PDO::FETCH_ASSOC);

        if (!$issue) {
            exit('Issue not found');
        }

        $stmt =
            $this->db()->prepare("
                SELECT
                    pia.*,
                    s.submission_no,
                    COALESCE(
                        s.title,
                        s.title_en,
                        s.title_ar
                    ) title,
                    s.doi
                FROM proceedings_issue_articles pia
                JOIN submissions s
                    ON s.id=pia.submission_id
                WHERE pia.issue_id=?
                ORDER BY pia.article_order
            ");

        $stmt->execute([
            $issueId
        ]);

        $articles =
            $stmt->fetchAll(\PDO::FETCH_ASSOC);

        require BASE_PATH .
        '/app/views/public/proceedings/issue.php';
    }
}
