<?php
// ajax/save_editorial.php — v6
// Saves: preferred_editor_ids, suggested_editors table, reviewers
require_once __DIR__ . '/../config/db.php';
header('Content-Type: application/json');
startSession();

$user    = getCurrentUser();
$user_id = $user['user_id'];

$submissionId = (int)($_POST['submission_id'] ?? 0);
$data         = json_decode($_POST['data'] ?? '{}', true);

if (!$submissionId)   jsonResponse(false, 'Submission ID required');
if (!is_array($data)) jsonResponse(false, 'Invalid data');

// Verify ownership
$stmt = db()->prepare("SELECT submission_id FROM submissions WHERE submission_id=? AND user_id=?");
$stmt->execute([$submissionId, $user_id]);
if (!$stmt->fetch()) jsonResponse(false, 'Access denied');

$db = db();

// DDL statements must be OUTSIDE transactions (MySQL auto-commits on DDL)
try {
    $db->exec("CREATE TABLE IF NOT EXISTS suggested_editors (
        editor_suggest_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        submission_id     INT UNSIGNED NOT NULL,
        editor_id         INT UNSIGNED NULL,
        name              VARCHAR(200) NOT NULL,
        institution       VARCHAR(300) NULL,
        expertise         VARCHAR(500) NULL,
        display_order     TINYINT UNSIGNED DEFAULT 1,
        created_at        DATETIME DEFAULT CURRENT_TIMESTAMP
    )");
    $db->exec("CREATE TABLE IF NOT EXISTS submission_editorial (
        editorial_id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        submission_id        INT UNSIGNED NOT NULL UNIQUE,
        preferred_editor_ids TEXT,
        created_at           DATETIME DEFAULT NOW(),
        updated_at           DATETIME DEFAULT NOW() ON UPDATE NOW()
    )");
    // Ensure suggested_reviewers has new affiliation columns (ALTER if old version existed)
    $db->exec("CREATE TABLE IF NOT EXISTS suggested_reviewers (
        reviewer_id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        submission_id      INT UNSIGNED NOT NULL,
        reviewer_type      ENUM('recommended','excluded') NOT NULL DEFAULT 'recommended',
        email              VARCHAR(200),
        title              VARCHAR(30),
        first_name         VARCHAR(100) NOT NULL DEFAULT '',
        middle_name        VARCHAR(100),
        last_name          VARCHAR(100) NOT NULL DEFAULT '',
        affiliation        VARCHAR(300),
        affiliation_id     INT UNSIGNED,
        affiliation_name   VARCHAR(300),
        department         VARCHAR(300),
        additional_comment TEXT,
        display_order      SMALLINT UNSIGNED DEFAULT 1,
        created_at         DATETIME DEFAULT NOW()
    )");
    // Add columns if they didn't exist in older schema
    try { $db->exec("ALTER TABLE suggested_reviewers ADD COLUMN affiliation_id INT UNSIGNED NULL AFTER affiliation"); } catch(PDOException $e){}
    try { $db->exec("ALTER TABLE suggested_reviewers ADD COLUMN affiliation_name VARCHAR(300) NULL AFTER affiliation_id"); } catch(PDOException $e){}
    try { $db->exec("ALTER TABLE suggested_reviewers ADD COLUMN reviewer_type ENUM('recommended','excluded') NOT NULL DEFAULT 'recommended'"); } catch(PDOException $e){}
    try { $db->exec("ALTER TABLE suggested_reviewers ADD COLUMN department VARCHAR(300) NULL"); } catch(PDOException $e){}
    try { $db->exec("ALTER TABLE suggested_reviewers ADD COLUMN additional_comment TEXT NULL"); } catch(PDOException $e){}
} catch (PDOException $e) { /* already exists */ }

try {
    $db->beginTransaction();

    // ── 1. Upsert submission_editorial ───────────────────────
    $editorIds = json_encode($data['preferred_editor_ids'] ?? []);
    $stmt = $db->prepare("SELECT editorial_id FROM submission_editorial WHERE submission_id=?");
    $stmt->execute([$submissionId]);
    if ($stmt->fetchColumn()) {
        $db->prepare("UPDATE submission_editorial SET preferred_editor_ids=?, updated_at=NOW() WHERE submission_id=?")
           ->execute([$editorIds, $submissionId]);
    } else {
        $db->prepare("INSERT INTO submission_editorial (submission_id, preferred_editor_ids, created_at, updated_at) VALUES (?,?,NOW(),NOW())")
           ->execute([$submissionId, $editorIds]);
    }

    // ── 2. Replace suggested_editors ─────────────────────────

    $db->prepare("DELETE FROM suggested_editors WHERE submission_id=?")->execute([$submissionId]);

    $insSE = $db->prepare(
        "INSERT INTO suggested_editors (submission_id, editor_id, name, institution, expertise, display_order)
         VALUES (?,?,?,?,?,?)"
    );
    foreach (($data['suggested_editors'] ?? []) as $se) {
        if (empty($se['name'])) continue;
        $insSE->execute([
            $submissionId,
            $se['editor_id']   ?? null,
            $se['name'],
            $se['institution'] ?? null,
            $se['expertise']   ?? null,
            (int)($se['display_order'] ?? 1),
        ]);
    }

    // ── 3. Replace suggested_reviewers ───────────────────────
    $db->prepare("DELETE FROM suggested_reviewers WHERE submission_id=?")->execute([$submissionId]);

    $insRv = $db->prepare(
        "INSERT INTO suggested_reviewers
           (submission_id, reviewer_type, email, title,
            first_name, middle_name, last_name,
            affiliation_id, affiliation_name, department,
            additional_comment, display_order)
         VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"
    );
    foreach (($data['reviewers'] ?? []) as $r) {
        $type = in_array($r['reviewer_type']??'', ['recommended','excluded'])
            ? $r['reviewer_type'] : 'recommended';
        $hasData = trim($r['first_name']??'') || trim($r['last_name']??'') || trim($r['email']??'');
        if (!$hasData) continue;

        $affilId   = (int)($r['affiliation_id'] ?? 0) ?: null;
        $affilName = trim($r['affiliation_name'] ?? '') ?: null;

        $insRv->execute([
            $submissionId,
            $type,
            trim($r['email']              ?? '') ?: null,
            trim($r['title']              ?? '') ?: null,
            trim($r['first_name']         ?? ''),
            trim($r['middle_name']        ?? '') ?: null,
            trim($r['last_name']          ?? ''),
            $affilId,
            $affilName,
            trim($r['department']         ?? '') ?: null,
            trim($r['additional_comment'] ?? '') ?: null,
            (int)($r['display_order']     ?? 1),
        ]);
    }

    // ── 4. Touch submission timestamp ────────────────────────
    $db->prepare("UPDATE submissions SET updated_at=NOW() WHERE submission_id=?")->execute([$submissionId]);

    $db->commit();
    jsonResponse(true, 'Saved successfully');

} catch (PDOException $e) {
    if ($db->inTransaction()) $db->rollBack();
    jsonResponse(false, 'DB error: ' . $e->getMessage());
}