<?php
declare(strict_types=1);

namespace App\Controllers;

use App\Core\Controller;
use App\Core\Database;
use App\Services\NotificationService;
use App\Services\EmailTemplateService;

final class AdminSubmissionsBoardController extends Controller
{
    private function detectTitleCols($db): array
    {
        $cols = $db->query("SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='submissions'")->fetchColumn();
        $set = array_flip(explode(',', (string)$cols));
        $en = null; $ar = null;
        foreach (['title_en','paper_title_en','title','paper_title','name','subject'] as $c) if (isset($set[$c])) { $en=$c; break; }
        foreach (['title_ar','paper_title_ar','name_ar','subject_ar'] as $c) if (isset($set[$c])) { $ar=$c; break; }
        if (!$en && $ar) $en=$ar;
        if (!$ar && $en) $ar=$en;
        if (!$en) $en='id';
        if (!$ar) $ar=$en;
        return [$en,$ar];
    }

    public function index(): void
    {
        require_login();
        $orgId = (int) current_org_id();

        if (!can_chair() && !can_admin() && !can_trackchair()) { http_response_code(403); echo "Forbidden"; return; }

        $db = Database::getInstance();
        [$en,$ar] = $this->detectTitleCols($db);

        $cid = function_exists('default_conference_id') ? default_conference_id() : 0;
        $status = trim((string)($_GET['status'] ?? ''));
        $q = trim((string)($_GET['q'] ?? ''));
        $sid = (int)($_GET['sid'] ?? 0);

        $where = [];
        $params = [];
        if ($cid > 0) { $where[] = "s.conference_id=:c"; $params[':c']=$cid; }
        if ($sid > 0) { $where[] = "s.id=:sid"; $params[':sid']=$sid; }
        if ($status !== '') { $where[] = "s.status=:st"; $params[':st']=$status; }
        if ($q !== '') {
            $where[] = "(s.$en LIKE :q OR s.$ar LIKE :q OR u.email LIKE :q)";
            $params[':q'] = "%$q%";
        }
        $wsql = $where ? ("WHERE ".implode(" AND ", $where)) : "";

        $st = $db->prepare("
          SELECT s.id, s.$en AS title_en, s.$ar AS title_ar, s.status, s.created_at,
                 u.email AS author_email,
                 c.title_en AS conf_title_en, c.title_ar AS conf_title_ar
          FROM submissions s
          JOIN users u ON u.id=s.author_id
          LEFT JOIN conferences c ON c.id=s.conference_id
          $wsql
          WHERE conference_id IN (SELECT id FROM conferences WHERE org_id=".(int)$orgId.")  ORDER BY s.id DESC
          LIMIT 600
        ");
        $st->execute($params);
        $rows = $st->fetchAll();

        $this->view('admin/submissions_board', ['rows'=>$rows, 'status'=>$status, 'q'=>$q]);
    }

    public function bulkStatus(): void
    {
        require_login();
        if (!can_chair() && !can_admin() && !can_trackchair()) { http_response_code(403); echo "Forbidden"; return; }
        csrf_check();

        $db = Database::getInstance();
        $ids = $_POST['ids'] ?? [];
        if (!is_array($ids)) $ids = [];
        $ids = array_values(array_filter(array_map('intval', $ids)));

        $new = (string)($_POST['new_status'] ?? '');
        $allowed = ['submitted','under_review','revision_minor','revision_major','revision_submitted','accepted','rejected','camera_ready_required','camera_ready_submitted','published','archived'];
        if (!$ids || !in_array($new, $allowed, true)) {
            header('Location: ' . base_path('admin/submissions'));
            exit;
        }

        $in = implode(',', array_fill(0, count($ids), '?'));
        // old status snapshot
        $st = $db->prepare("SELECT id,author_id,status FROM submissions WHERE id IN ($in) AND conference_id IN (SELECT id FROM conferences WHERE org_id=".(int)$orgId.") ");
        $st->execute($ids);
        $rows = $st->fetchAll();

        foreach ($rows as $r) {
            $db->prepare("INSERT INTO submission_status_history(submission_id,old_status,new_status,changed_by,note)
                          VALUES(:sid,:o,:n,:u,:note)")
               ->execute([
                 ':sid'=>(int)$r['id'],
                 ':o'=>(string)$r['status'],
                 ':n'=>$new,
                 ':u'=>(int)auth_id(),
                 ':note'=>'bulk status update'
               ]);

            $db->prepare("UPDATE submissions SET status=:s WHERE id=:id")->execute([':s'=>$new, ':id'=>(int)$r['id']]);

            // notify author
            NotificationService::notify((int)$r['author_id'], 'status_update', "Your paper #{$r['id']} status updated to $new", base_path('author/submission?id='.(int)$r['id']));
        }

        header('Location: ' . base_path('admin/submissions'));
        exit;
    }
}
