<?php

namespace App\Controllers;

use App\Core\Database;
use PDO;

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

    public function index()
    {
        if (session_status() !== PHP_SESSION_ACTIVE) {
            session_start();
        }

        $conferenceId =
            (int)($_SESSION['conference_id'] ?? 0);

        $orgId =
            (int)($_SESSION['org_id'] ?? 0);

        /*
        --------------------------------------------------
        CHECK 1
        --------------------------------------------------
        */

        $stmt = $this->db()->prepare("
            SELECT
                s.id,
                s.submission_no,
                s.title,
                s.issue_id
            FROM submissions s
            LEFT JOIN proceedings_issue_articles pia
                ON pia.submission_id=s.id
            WHERE s.conference_id=?
              AND s.org_id=?
              AND s.status='assigned_to_issue'
              AND pia.id IS NULL
        ");

        $stmt->execute([
            $conferenceId,
            $orgId
        ]);

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

        /*
        --------------------------------------------------
        CHECK 2
        --------------------------------------------------
        */

        $stmt = $this->db()->prepare("
            SELECT
                s.id,
                s.submission_no,
                pia.galley_id
            FROM proceedings_issue_articles pia
            JOIN submissions s
                ON s.id=pia.submission_id
            LEFT JOIN production_galleys g
                ON g.id=pia.galley_id
            WHERE pia.conference_id=?
              AND pia.org_id=?
              AND g.id IS NULL
        ");

        $stmt->execute([
            $conferenceId,
            $orgId
        ]);

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

        /*
        --------------------------------------------------
        CHECK 3
        --------------------------------------------------
        */

        $stmt = $this->db()->prepare("
            SELECT
                s.id,
                s.submission_no,
                g.status
            FROM proceedings_issue_articles pia
            JOIN submissions s
                ON s.id=pia.submission_id
            JOIN production_galleys g
                ON g.id=pia.galley_id
            WHERE pia.conference_id=?
              AND pia.org_id=?
              AND g.status<>'approved'
        ");

        $stmt->execute([
            $conferenceId,
            $orgId
        ]);

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

        require
        BASE_PATH .
        '/app/views/admin/publication_integrity/index.php';
    }
}
