<?php
namespace App\Controllers;

use App\Core\Database;
use App\Core\BusinessPermission;

class AdminConferenceBusinessController
{
    private function startSession(): void
    {
        if (session_status() !== PHP_SESSION_ACTIVE) {
            session_start();
        }
    }

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

    private function role(): string
    {
        return (string)($_SESSION['role'] ?? $_SESSION['user_role'] ?? '');
    }

    private function requireAccess(): void
    {
        $this->startSession();

        if (empty($_SESSION['user_id'])) {
            header("Location: /easychair/login");
            exit;
        }

        if (!in_array($this->role(), ['super_admin','org_admin','admin','organizer','conference_chair','track_chair'], true)) {
            http_response_code(403);
            exit("Access denied.");
        }

        if (empty($_SESSION['conference_id'])) {
            header("Location: /easychair/select-conference");
            exit;
        }
    }

    private function conference(): array
    {
        $stmt = $this->db()->prepare("SELECT * FROM conferences WHERE id=? LIMIT 1");
        $stmt->execute([(int)$_SESSION['conference_id']]);
        $c = $stmt->fetch(\PDO::FETCH_ASSOC);

        if (!$c) {
            exit("Conference not found.");
        }

        if ($this->role() !== 'super_admin' && (int)$c['org_id'] !== (int)($_SESSION['org_id'] ?? 0)) {
            http_response_code(403);
            exit("Invalid organization conference access.");
        }

        return $c;
    }

    private function rows(string $sql, array $params = []): array
    {
        $stmt = $this->db()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(\PDO::FETCH_ASSOC) ?: [];
    }

    private function certToken(): string
    {
        return bin2hex(random_bytes(16));
    }

    private function certificateNo(int $conferenceId, string $type): string
    {
        return 'CERT-' . $conferenceId . '-' . strtoupper($type) . '-' . date('YmdHis') . '-' . rand(100,999);
    }

    private function renderText(string $text, array $vars): string
    {
        foreach ($vars as $k => $v) {
            $text = str_replace('{' . $k . '}', (string)$v, $text);
        }
        return $text;
    }

    private function issueCertificateRow(array $conference, string $type, string $name, ?string $email, ?int $userId = null, ?int $submissionId = null, string $submissionTitle = ''): void
    {
        $stmt = $this->db()->prepare("
            SELECT *
            FROM certificate_templates
            WHERE conference_id = ?
              AND certificate_type = ?
              AND is_active = 1
            LIMIT 1
        ");
        $stmt->execute([(int)$conference['id'], $type]);
        $tpl = $stmt->fetch(\PDO::FETCH_ASSOC);

        $vars = [
            'recipient_name' => $name,
            'conference_short_name' => $conference['short_name'] ?? '',
            'conference_title' => ($conference['title_ar'] ?? '') ?: ($conference['title_en'] ?? ''),
            'submission_title' => $submissionTitle,
        ];

        $titleAr = $tpl ? $this->renderText((string)$tpl['title_ar'], $vars) : 'شهادة';
        $titleEn = $tpl ? $this->renderText((string)$tpl['title_en'], $vars) : 'Certificate';
        $bodyAr  = $tpl ? $this->renderText((string)$tpl['body_ar'], $vars) : '';
        $bodyEn  = $tpl ? $this->renderText((string)$tpl['body_en'], $vars) : '';

        $token = $this->certToken();
        $certNo = $this->certificateNo((int)$conference['id'], $type);
        $verifyUrl = '/easychair/certificates/verify?token=' . $token;

        $stmt = $this->db()->prepare("
            INSERT INTO conference_certificates
            (org_id, conference_id, user_id, submission_id, recipient_name, recipient_email, certificate_type,
             role_label, certificate_no, verify_token, title_ar, title_en, body_ar, body_en, verification_url,
             issued_by, issued_at, status)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), 'issued')
        ");
        $stmt->execute([
            (int)$conference['org_id'],
            (int)$conference['id'],
            $userId,
            $submissionId,
            $name,
            $email,
            $type,
            $type,
            $certNo,
            $token,
            $titleAr,
            $titleEn,
            $bodyAr,
            $bodyEn,
            $verifyUrl,
            (int)$_SESSION['user_id']
        ]);
    }

    public function index()
    {
        $this->requireAccess();
        BusinessPermission::requirePermission('business.dashboard.view');

        $conference = $this->conference();
        $cid = (int)$conference['id'];
        $orgId = (int)$conference['org_id'];

        $committees = $this->rows("
            SELECT *
            FROM conference_committees
            WHERE conference_id=? AND org_id=?
            ORDER BY committee_type, sort_order, id DESC
        ", [$cid, $orgId]);

        $themes = $this->rows("
            SELECT t.*, CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) AS editor_name
            FROM conference_themes t
            LEFT JOIN users u ON u.id = t.section_editor_id
            WHERE t.conference_id=? AND t.org_id=?
            ORDER BY t.theme_type, t.sort_order, t.id
        ", [$cid, $orgId]);

        $days = $this->rows("
            SELECT *
            FROM conference_days
            WHERE conference_id=? AND org_id=?
            ORDER BY day_date, sort_order, id
        ", [$cid, $orgId]);

        $venues = $this->rows("
            SELECT *
            FROM conference_venues
            WHERE conference_id=? AND org_id=?
            ORDER BY sort_order, id
        ", [$cid, $orgId]);

        $sessions = $this->rows("
            SELECT 
                s.*,
                d.day_date,
                COALESCE(v.name_ar, v.name_en, s.room_name) AS venue_name,
                COALESCE(t.title_ar, t.title_en) AS theme_title,
                CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) AS chair_name,
                COALESCE(pc.papers_count, 0) AS papers_count
            FROM conference_sessions s
            LEFT JOIN conference_days d ON d.id = COALESCE(s.day_id, s.conference_day_id)
            LEFT JOIN conference_venues v ON v.id = COALESCE(s.venue_id, s.room_id)
            LEFT JOIN conference_themes t ON t.id = COALESCE(s.theme_id, s.track_id)
            LEFT JOIN users u ON u.id = s.chair_user_id
            LEFT JOIN (
                SELECT session_id, COUNT(*) AS papers_count
                FROM conference_session_papers
                GROUP BY session_id
            ) pc ON pc.session_id = s.id
            WHERE s.conference_id=? AND s.org_id=?
            ORDER BY COALESCE(d.day_date, s.session_date), s.start_time, s.sort_order, s.id
        ", [$cid, $orgId]);

        $acceptedPapers = $this->rows("
            SELECT id, submission_no, COALESCE(title,title_en,title_ar,CONCAT('Submission #',id)) AS title
            FROM submissions
            WHERE conference_id=? AND org_id=? AND status IN ('accepted','galley','assigned_to_issue','production','published')
            ORDER BY id DESC
        ", [$cid, $orgId]);

        $users = $this->rows("
            SELECT DISTINCT u.id, u.first_name, u.last_name, u.email, r.code AS role_code
            FROM users u
            LEFT JOIN user_conferences uc ON uc.user_id = u.id
            LEFT JOIN roles r ON r.id = uc.role_id
            WHERE u.org_id=? AND (uc.conference_id=? OR uc.conference_id IS NULL)
            ORDER BY u.first_name, u.last_name
        ", [$orgId, $cid]);

        $galleys = $this->rows("
            SELECT g.*, s.submission_no, COALESCE(s.title,s.title_en,s.title_ar) AS submission_title
            FROM production_galleys g
            JOIN submissions s ON s.id = g.submission_id
            WHERE g.conference_id=? AND g.org_id=?
            ORDER BY g.id DESC
        ", [$cid, $orgId]);

        $issues = $this->rows("
            SELECT i.*, COALESCE(ac.articles_count,0) AS articles_count
            FROM proceedings_issues i
            LEFT JOIN (
                SELECT issue_id, COUNT(*) AS articles_count
                FROM proceedings_issue_articles
                GROUP BY issue_id
            ) ac ON ac.issue_id = i.id
            WHERE i.conference_id=? AND i.org_id=?
            ORDER BY i.id DESC
        ", [$cid, $orgId]);

        $certificates = $this->rows("
            SELECT *
            FROM conference_certificates
            WHERE conference_id=? AND org_id=?
            ORDER BY id DESC
            LIMIT 100
        ", [$cid, $orgId]);

        ob_start();
        require BASE_PATH . '/app/views/admin/conference_business/index.php';
        $content = ob_get_clean();

        require BASE_PATH . '/app/views/layouts/admin_layout.php';
    }

    public function storeCommittee()
    {
        $this->requireAccess();
        BusinessPermission::requireCrud('committees', 'create');
        $c = $this->conference();

        $imagePath = null;

        if (!empty($_FILES['image']['name']) && is_uploaded_file($_FILES['image']['tmp_name'])) {
            $dir = BASE_PATH . '/public/storage/committees/' . (int)$c['id'];
            if (!is_dir($dir)) mkdir($dir, 0775, true);

            $ext = strtolower(pathinfo($_FILES['image']['name'], PATHINFO_EXTENSION));
            $ext = preg_replace('/[^a-z0-9]/', '', $ext);
            $file = 'committee_' . time() . '_' . rand(1000,9999) . '.' . $ext;

            if (move_uploaded_file($_FILES['image']['tmp_name'], $dir . '/' . $file)) {
                $imagePath = 'committees/' . (int)$c['id'] . '/' . $file;
            }
        }

        $type = strtolower(trim($_POST['committee_type'] ?? 'other'));
        $allowed = ['chair','co_chair','editor','section_editor','patron','keynote','tpc','reviewer','panel','organizing','scientific','steering','sponsor','other'];
        if (!in_array($type, $allowed, true)) $type = 'other';

        $stmt = $this->db()->prepare("
            INSERT INTO conference_committees
            (org_id, conference_id, committee_type, full_name_ar, full_name_en, title_ar, title_en,
             affiliation_ar, affiliation_en, email, image_path, bio_ar, bio_en, sort_order, is_active, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, ?)
        ");
        $stmt->execute([
            (int)$c['org_id'],
            (int)$c['id'],
            $type,
            trim($_POST['full_name_ar'] ?? ''),
            trim($_POST['full_name_en'] ?? ''),
            trim($_POST['title_ar'] ?? ''),
            trim($_POST['title_en'] ?? ''),
            trim($_POST['affiliation_ar'] ?? ''),
            trim($_POST['affiliation_en'] ?? ''),
            trim($_POST['email'] ?? ''),
            $imagePath,
            trim($_POST['bio_ar'] ?? ''),
            trim($_POST['bio_en'] ?? ''),
            (int)($_POST['sort_order'] ?? 1),
            (int)$_SESSION['user_id']
        ]);

        header("Location: /easychair/admin/conference-business#committees");
        exit;
    }

    public function deleteCommittee()
    {
        $this->requireAccess();
        BusinessPermission::requireCrud('committees', 'delete');
        $c = $this->conference();

        $stmt = $this->db()->prepare("DELETE FROM conference_committees WHERE id=? AND conference_id=? AND org_id=?");
        $stmt->execute([(int)($_GET['id'] ?? 0), (int)$c['id'], (int)$c['org_id']]);

        header("Location: /easychair/admin/conference-business#committees");
        exit;
    }

    public function storeTheme()
    {
        $this->requireAccess();
        BusinessPermission::requireCrud('themes', 'create');
        $c = $this->conference();

        $stmt = $this->db()->prepare("
            INSERT INTO conference_themes
            (org_id, conference_id, theme_type, title_ar, title_en, description_ar, description_en, section_editor_id, sort_order, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            (int)$c['org_id'], (int)$c['id'], $_POST['theme_type'] ?? 'theme',
            trim($_POST['title_ar'] ?? ''), trim($_POST['title_en'] ?? ''),
            trim($_POST['description_ar'] ?? ''), trim($_POST['description_en'] ?? ''),
            !empty($_POST['section_editor_id']) ? (int)$_POST['section_editor_id'] : null,
            (int)($_POST['sort_order'] ?? 0), (int)$_SESSION['user_id']
        ]);

        header("Location: /easychair/admin/conference-business#themes");
        exit;
    }

    public function storeDay()
    {
        $this->requireAccess();
        BusinessPermission::requireCrud('days', 'create');
        $c = $this->conference();

        $stmt = $this->db()->prepare("
            INSERT INTO conference_days
            (org_id, conference_id, day_date, title_ar, title_en, sort_order, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            (int)$c['org_id'], (int)$c['id'], $_POST['day_date'] ?? date('Y-m-d'),
            trim($_POST['title_ar'] ?? ''), trim($_POST['title_en'] ?? ''),
            (int)($_POST['sort_order'] ?? 0), (int)$_SESSION['user_id']
        ]);

        header("Location: /easychair/admin/conference-business#timetable");
        exit;
    }

    public function storeVenue()
    {
        $this->requireAccess();
        BusinessPermission::requireCrud('venues', 'create');
        $c = $this->conference();

        $stmt = $this->db()->prepare("
            INSERT INTO conference_venues
            (org_id, conference_id, name_ar, name_en, location_ar, location_en, capacity, sort_order, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            (int)$c['org_id'], (int)$c['id'],
            trim($_POST['name_ar'] ?? ''), trim($_POST['name_en'] ?? ''),
            trim($_POST['location_ar'] ?? ''), trim($_POST['location_en'] ?? ''),
            !empty($_POST['capacity']) ? (int)$_POST['capacity'] : null,
            (int)($_POST['sort_order'] ?? 0), (int)$_SESSION['user_id']
        ]);

        header("Location: /easychair/admin/conference-business#timetable");
        exit;
    }

    public function storeSession()
    {
        $this->requireAccess();
        BusinessPermission::requireCrud('sessions', 'create');
        $c = $this->conference();

        $dayId = !empty($_POST['day_id']) ? (int)$_POST['day_id'] : null;
        $venueId = !empty($_POST['venue_id']) ? (int)$_POST['venue_id'] : null;
        $themeId = !empty($_POST['theme_id']) ? (int)$_POST['theme_id'] : null;

        $type = $_POST['session_type'] ?? 'paper_session';
        if ($type === 'paper') $type = 'paper_session';

        $titleAr = trim($_POST['title_ar'] ?? '');
        $titleEn = trim($_POST['title_en'] ?? '');
        if ($titleEn === '') $titleEn = $titleAr ?: 'Conference Session';

        $start = $_POST['start_time'] ?? '09:00:00';
        $end = $_POST['end_time'] ?? '10:00:00';

        $stmt = $this->db()->prepare("
            INSERT INTO conference_sessions
            (org_id, conference_id, day_id, venue_id, theme_id,
             conference_day_id, program_day_id, room_id, track_id,
             session_type, title_ar, title_en, chair_user_id, start_time, end_time, sort_order, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            (int)$c['org_id'], (int)$c['id'], $dayId, $venueId, $themeId,
            $dayId, $dayId ?: 0, $venueId, $themeId,
            $type, $titleAr, $titleEn,
            !empty($_POST['chair_user_id']) ? (int)$_POST['chair_user_id'] : null,
            $start, $end, (int)($_POST['sort_order'] ?? 0), (int)$_SESSION['user_id']
        ]);

        header("Location: /easychair/admin/conference-business#timetable");
        exit;
    }

    public function assignPaper()
    {
        $this->requireAccess();
        BusinessPermission::requireCrud('session_papers', 'create');
        $c = $this->conference();

        $stmt = $this->db()->prepare("
            INSERT INTO conference_session_papers
            (org_id, conference_id, session_id, submission_id, presentation_order, presentation_time, duration_minutes, presenter_name)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                presentation_order=VALUES(presentation_order),
                presentation_time=VALUES(presentation_time),
                duration_minutes=VALUES(duration_minutes),
                presenter_name=VALUES(presenter_name)
        ");
        $stmt->execute([
            (int)$c['org_id'], (int)$c['id'],
            (int)($_POST['session_id'] ?? 0), (int)($_POST['submission_id'] ?? 0),
            (int)($_POST['presentation_order'] ?? 0), $_POST['presentation_time'] ?? null,
            !empty($_POST['duration_minutes']) ? (int)$_POST['duration_minutes'] : null,
            trim($_POST['presenter_name'] ?? '')
        ]);

        header("Location: /easychair/admin/conference-business#timetable");
        exit;
    }

    public function uploadGalley()
    {
        $this->requireAccess();
        BusinessPermission::requirePermission('business.production.upload');
        $c = $this->conference();

        $submissionId = (int)($_POST['submission_id'] ?? 0);

        if (empty($_FILES['galley_file']['name']) || !is_uploaded_file($_FILES['galley_file']['tmp_name'])) {
            exit("Galley file required.");
        }

        $dir = BASE_PATH . '/public/storage/galleys/' . (int)$c['id'];
        if (!is_dir($dir)) mkdir($dir, 0775, true);

        $ext = strtolower(pathinfo($_FILES['galley_file']['name'], PATHINFO_EXTENSION));
        $ext = preg_replace('/[^a-z0-9]/', '', $ext);
        $file = 'galley_' . $submissionId . '_' . time() . '.' . $ext;

        if (!move_uploaded_file($_FILES['galley_file']['tmp_name'], $dir . '/' . $file)) {
            exit("Could not upload galley.");
        }

        $path = 'galleys/' . (int)$c['id'] . '/' . $file;

        $stmt = $this->db()->prepare("
            INSERT INTO production_galleys
            (org_id, conference_id, submission_id, galley_type, file_path, original_filename, status, notes, uploaded_by)
            VALUES (?, ?, ?, ?, ?, ?, 'pending', ?, ?)
        ");
        $stmt->execute([
            (int)$c['org_id'], (int)$c['id'], $submissionId,
            $_POST['galley_type'] ?? 'pdf', $path, $_FILES['galley_file']['name'],
            trim($_POST['notes'] ?? ''), (int)$_SESSION['user_id']
        ]);

        $this->db()->prepare("UPDATE submissions SET status='galley', stage='galley', production_status='galley_uploaded', updated_at=NOW() WHERE id=?")
            ->execute([$submissionId]);

        header("Location: /easychair/admin/conference-business#production");
        exit;
    }

    public function approveGalley()
    {
        $this->requireAccess();
        BusinessPermission::requirePermission('business.production.approve');
        $c = $this->conference();

        $stmt = $this->db()->prepare("
            UPDATE production_galleys
            SET status='approved', approved_by=?, approved_at=NOW()
            WHERE id=? AND conference_id=? AND org_id=?
        ");
        $stmt->execute([(int)$_SESSION['user_id'], (int)($_GET['id'] ?? 0), (int)$c['id'], (int)$c['org_id']]);

        header("Location: /easychair/admin/conference-business#production");
        exit;
    }

    public function storeIssue()
    {
        $this->requireAccess();
        BusinessPermission::requireCrud('issues', 'create');
        $c = $this->conference();

        $stmt = $this->db()->prepare("
            INSERT INTO proceedings_issues
            (org_id, conference_id, title_ar, title_en, volume_no, issue_no, publication_date, doi_prefix, status, created_by)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'draft', ?)
            ON DUPLICATE KEY UPDATE
                title_ar=VALUES(title_ar),
                title_en=VALUES(title_en),
                volume_no=VALUES(volume_no),
                issue_no=VALUES(issue_no),
                publication_date=VALUES(publication_date),
                doi_prefix=VALUES(doi_prefix),
                status=VALUES(status),
                updated_at=NOW()
        ");
        $stmt->execute([
            (int)$c['org_id'], (int)$c['id'],
            trim($_POST['title_ar'] ?? ''), trim($_POST['title_en'] ?? ''),
            trim($_POST['volume_no'] ?? ''), trim($_POST['issue_no'] ?? ''),
            $_POST['publication_date'] ?? null, trim($_POST['doi_prefix'] ?? ''),
            (int)$_SESSION['user_id']
        ]);

        header("Location: /easychair/admin/conference-business#issues");
        exit;
    }

    public function addArticleToIssue()
    {
        $this->requireAccess();
        BusinessPermission::requirePermission('business.issues.add_article');
        $c = $this->conference();

        $issueId = (int)($_POST['issue_id'] ?? 0);
        $submissionId = (int)($_POST['submission_id'] ?? 0);

        $stmt = $this->db()->prepare("
            SELECT id FROM production_galleys
            WHERE submission_id=? AND status IN ('approved','published')
            ORDER BY id DESC LIMIT 1
        ");
        $stmt->execute([$submissionId]);
        $galleyId = $stmt->fetchColumn() ?: null;

        $stmt = $this->db()->prepare("
            INSERT INTO proceedings_issue_articles
            (org_id, conference_id, issue_id, submission_id, galley_id, page_from, page_to, article_order, doi)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                galley_id=VALUES(galley_id),
                page_from=VALUES(page_from),
                page_to=VALUES(page_to),
                article_order=VALUES(article_order),
                doi=VALUES(doi)
        ");
        $stmt->execute([
            (int)$c['org_id'], (int)$c['id'], $issueId, $submissionId, $galleyId,
            trim($_POST['page_from'] ?? ''), trim($_POST['page_to'] ?? ''),
            (int)($_POST['article_order'] ?? 0), trim($_POST['doi'] ?? '')
        ]);

        $this->db()->prepare("UPDATE submissions SET status='assigned_to_issue', stage='issue', issue_id=?, doi=COALESCE(NULLIF(?,''),doi), updated_at=NOW() WHERE id=?")
            ->execute([$issueId, trim($_POST['doi'] ?? ''), $submissionId]);

        header("Location: /easychair/admin/conference-business#issues");
        exit;
    }

    public function publishIssue()
    {
        $this->requireAccess();
        BusinessPermission::requirePermission('business.issues.publish');
        $c = $this->conference();
        $issueId = (int)($_GET['id'] ?? 0);

        $this->db()->prepare("UPDATE proceedings_issues SET status='published', updated_at=NOW() WHERE id=? AND conference_id=? AND org_id=?")
            ->execute([$issueId, (int)$c['id'], (int)$c['org_id']]);

        $this->db()->prepare("
            UPDATE submissions s
            JOIN proceedings_issue_articles a ON a.submission_id=s.id
            SET s.status='published', s.stage='published', s.published_at=NOW()
            WHERE a.issue_id=? AND a.conference_id=? AND a.org_id=?
        ")->execute([$issueId, (int)$c['id'], (int)$c['org_id']]);

        header("Location: /easychair/admin/conference-business#issues");
        exit;
    }

    public function issueCertificate()
    {
        $this->requireAccess();
        BusinessPermission::requirePermission('business.certificates.issue');
        $c = $this->conference();

        $type = $_POST['certificate_type'] ?? 'other';
        $name = trim($_POST['recipient_name'] ?? '');
        $email = trim($_POST['recipient_email'] ?? '');

        if ($name === '') {
            exit("Recipient name required.");
        }

        $this->issueCertificateRow($c, $type, $name, $email ?: null);

        header("Location: /easychair/admin/conference-business#certificates");
        exit;
    }

    public function issueBulkCertificates()
    {
        $this->requireAccess();
        BusinessPermission::requirePermission('business.certificates.issue');
        $c = $this->conference();
        $type = $_POST['certificate_type'] ?? 'other';

        $issued = 0;

        if ($type === 'author') {
            $stmt = $this->db()->prepare("
                SELECT s.id AS submission_id, s.author_id,
                       COALESCE(s.title, s.title_en, s.title_ar) AS submission_title,
                       u.email,
                       CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) AS user_name
                FROM submissions s
                JOIN users u ON u.id = s.author_id
                WHERE s.conference_id=? AND s.org_id=?
                  AND s.status IN ('accepted','published','assigned_to_issue','galley','production')
            ");
            $stmt->execute([(int)$c['id'], (int)$c['org_id']]);

            foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $r) {
                $this->issueCertificateRow($c, 'author', trim($r['user_name']) ?: $r['email'], $r['email'], (int)$r['author_id'], (int)$r['submission_id'], $r['submission_title'] ?? '');
                $issued++;
            }
        } elseif ($type === 'reviewer') {
            $stmt = $this->db()->prepare("
                SELECT DISTINCT u.id, u.email, CONCAT(COALESCE(u.first_name,''),' ',COALESCE(u.last_name,'')) AS user_name
                FROM review_assignments ra
                JOIN users u ON u.id = ra.reviewer_id
                WHERE ra.conference_id=? AND ra.org_id=? AND ra.status IN ('completed','accepted')
            ");
            $stmt->execute([(int)$c['id'], (int)$c['org_id']]);

            foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $r) {
                $this->issueCertificateRow($c, 'reviewer', trim($r['user_name']) ?: $r['email'], $r['email'], (int)$r['id']);
                $issued++;
            }
        } else {
            $committeeType = $type === 'chair' ? 'chair' : ($type === 'tpc' ? 'tpc' : ($type === 'editor' ? 'editor' : 'other'));

            $stmt = $this->db()->prepare("
                SELECT *
                FROM conference_committees
                WHERE conference_id=? AND org_id=? AND committee_type=? AND is_active=1
            ");
            $stmt->execute([(int)$c['id'], (int)$c['org_id'], $committeeType]);

            foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $r) {
                $name = ($r['full_name_ar'] ?? '') ?: (($r['full_name_en'] ?? '') ?: ($r['email'] ?? 'Committee Member'));
                $this->issueCertificateRow($c, $type, $name, $r['email'] ?? null);
                $issued++;
            }
        }

        header("Location: /easychair/admin/conference-business#certificates");
        exit;
    }

    public function printCertificate()
    {
        $this->requireAccess();
        BusinessPermission::requirePermission('business.certificates.view');
        $c = $this->conference();

        $stmt = $this->db()->prepare("
            SELECT cert.*, conf.short_name, conf.title_ar AS conference_title_ar, conf.title_en AS conference_title_en
            FROM conference_certificates cert
            LEFT JOIN conferences conf ON conf.id = cert.conference_id
            WHERE cert.id=? AND cert.conference_id=? AND cert.org_id=?
            LIMIT 1
        ");
        $stmt->execute([(int)($_GET['id'] ?? 0), (int)$c['id'], (int)$c['org_id']]);
        $certificate = $stmt->fetch(\PDO::FETCH_ASSOC);

        if (!$certificate) exit("Certificate not found.");

        require BASE_PATH . '/app/views/certificates/print.php';
    }
}
