<?php
/**
 * config/reviewer_sync.php
 * ─────────────────────────────────────────────────────
 * Helper functions to sync suggested/excluded reviewers
 * into the users table when a submission is finally submitted.
 *
 * Include this file wherever submission is finalized.
 * Usage: syncSubmissionReviewers($pdo_or_db(), $submission_id);
 */

// ── Generate unique researcher_id ─────────────────────────────
function generateResearcherId(PDO $db): string {
    $attempts = 0;
    do {
        $letters = strtoupper(chr(rand(65,90)).chr(rand(65,90)));
        $digits  = str_pad(rand(0, 9999), 4, '0', STR_PAD_LEFT);
        $id      = 'RSCH-' . $letters . $digits;
        $q = $db->prepare("SELECT 1 FROM users WHERE researcher_id=? LIMIT 1");
        $q->execute([$id]);
        $exists = $q->fetchColumn();
        $attempts++;
    } while ($exists && $attempts < 100);
    return $id;
}

// ── Create or update user in users table ─────────────────────
function upsertReviewerUser(PDO $db, array $rev, string $email): int {
    // Ensure columns exist
    $colsToAdd = [
        "is_email_verified TINYINT(1) NOT NULL DEFAULT 0",
        "email_verify_token VARCHAR(100) NULL",
        "email_verify_expires DATETIME NULL",
        "password_reset_token VARCHAR(100) NULL",
        "password_reset_expires DATETIME NULL",
        "role ENUM('author','reviewer','editor','both','other') NOT NULL DEFAULT 'author'",
        "researcher_id VARCHAR(20) NULL",
    ];
    foreach ($colsToAdd as $col) {
        try { $db->exec("ALTER TABLE users ADD COLUMN IF NOT EXISTS $col"); }
        catch(PDOException $e) {}
    }

    // Check if user already exists
    $q = $db->prepare("SELECT user_id, role, researcher_id FROM users WHERE email=? LIMIT 1");
    $q->execute([$email]);
    $existing = $q->fetch(PDO::FETCH_ASSOC);

    $researcherId = null;

    if ($existing) {
        $userId       = (int)$existing['user_id'];
        $currentRole  = $existing['role'] ?? 'author';
        $researcherId = $existing['researcher_id'];

        // Update role
        $newRole = match($currentRole) {
            'author' => 'both',
            'reviewer','editor','both','other' => $currentRole,
            default  => 'reviewer',
        };

        // Generate researcher_id if missing
        if (empty($researcherId)) {
            $researcherId = generateResearcherId($db);
        }

        $db->prepare(
            "UPDATE users SET role=?, researcher_id=?, updated_at=NOW() WHERE user_id=?"
        )->execute([$newRole, $researcherId, $userId]);

    } else {
        // Create new reviewer user
        // Default password: 123456 (user must change on first login)
        $tempHash     = password_hash('123456', PASSWORD_BCRYPT);
        $researcherId = generateResearcherId($db);

        // Split name
        $parts     = explode(' ', trim($rev['full_name_combined'] ?? ''), 2);
        $firstName = $parts[0] ?? ($rev['first_name'] ?? 'Reviewer');
        $lastName  = $parts[1] ?? ($rev['last_name'] ?? '');
        $title     = $rev['title'] ?? '';

        $db->prepare(
            "INSERT INTO users
             (email, password_hash, first_name, last_name, title,
              role, researcher_id, is_active, is_email_verified,
              created_at, updated_at)
             VALUES (?,?,?,?,?,'reviewer',?,1,1,NOW(),NOW())"
        )->execute([
            $email, $tempHash, $firstName, $lastName, $title, $researcherId
        ]);

        $userId = (int)$db->lastInsertId();
    }

    return $userId;
}

// ── Assign research interests to user ────────────────────────
// $reviewerAreas: JSON string from suggested_reviewers.research_areas (already computed at save time)
function assignResearchInterests(PDO $db, int $userId, int $submissionId, string $source, ?string $reviewerAreas = null): void {
    // Ensure table exists
    try {
        $db->exec("CREATE TABLE IF NOT EXISTS user_research_interests (
            id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            user_id    INT UNSIGNED NOT NULL,
            topic_id   INT UNSIGNED NOT NULL,
            source     ENUM('manual','auto_suggested','auto_excluded') DEFAULT 'manual',
            created_at DATETIME DEFAULT NOW(),
            UNIQUE KEY uq_user_topic (user_id, topic_id),
            KEY idx_user_id  (user_id),
            KEY idx_topic_id (topic_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    } catch(PDOException $e) {}

    // PRIORITY 1: Use research_areas already saved in suggested_reviewers
    // (saved at editorial step — exact topics from submission)
    $topics = [];
    if (!empty($reviewerAreas)) {
        $areaNames = json_decode($reviewerAreas, true) ?: [];
        if ($areaNames) {
            try {
                $placeholders = implode(',', array_fill(0, count($areaNames), '?'));
                $q = $db->prepare(
                    "SELECT topic_id FROM research_topics
                     WHERE title IN ($placeholders) AND is_active=1"
                );
                $q->execute($areaNames);
                $topics = $q->fetchAll(PDO::FETCH_COLUMN);
            } catch(PDOException $e) {}
        }
    }

    // PRIORITY 2: Fallback — get from specialty → research_topics
    if (empty($topics)) {
        try {
            $q = $db->prepare("
                SELECT rt.topic_id
                FROM research_topics rt
                JOIN submissions s ON s.specialty_id = rt.specialty_id
                WHERE s.submission_id = ? AND rt.is_active = 1
            ");
            $q->execute([$submissionId]);
            $topics = $q->fetchAll(PDO::FETCH_COLUMN);
        } catch(PDOException $e) {}
    }

    // PRIORITY 3: keyword-based fallback
    if (empty($topics)) {
        try {
            $q = $db->prepare("
                SELECT DISTINCT rt.topic_id
                FROM submission_keywords sk
                JOIN keywords k ON k.keyword_id = sk.keyword_id
                JOIN research_topics rt ON rt.title LIKE CONCAT('%', k.name, '%')
                WHERE sk.submission_id = ? AND rt.is_active = 1
            ");
            $q->execute([$submissionId]);
            $topics = $q->fetchAll(PDO::FETCH_COLUMN);
        } catch(PDOException $e) {}
    }

    // Insert interests (ignore duplicates)
    foreach ($topics as $topicId) {
        try {
            $db->prepare(
                "INSERT IGNORE INTO user_research_interests
                 (user_id, topic_id, source, created_at)
                 VALUES (?, ?, ?, NOW())"
            )->execute([$userId, (int)$topicId, $source]);
        } catch(PDOException $e) {}
    }
}

// ── Link suggested_reviewer row back to user ─────────────────
function linkReviewerToUser(PDO $db, int $reviewerId, int $userId): void {
    try {
        $db->exec("ALTER TABLE suggested_reviewers
            ADD COLUMN IF NOT EXISTS linked_user_id INT UNSIGNED NULL");
    } catch(PDOException $e) {}

    try {
        $db->prepare(
            "UPDATE suggested_reviewers SET linked_user_id=? WHERE reviewer_id=?"
        )->execute([$userId, $reviewerId]);
    } catch(PDOException $e) {}
}

// ══════════════════════════════════════════════════════════════
// MAIN FUNCTION — Call this when submission is finalized
// ══════════════════════════════════════════════════════════════
function syncSubmissionReviewers(PDO $db, int $submissionId): array {
    $results = [
        'synced'   => 0,
        'skipped'  => 0,
        'errors'   => [],
    ];

    // Fetch all suggested + excluded reviewers for this submission
    try {
        $q = $db->prepare(
            "SELECT reviewer_id, reviewer_type,
                    first_name, middle_name, last_name, title,
                    email, institutional_email, research_areas
             FROM suggested_reviewers
             WHERE submission_id = ?"
        );
        $q->execute([$submissionId]);
        $reviewers = $q->fetchAll(PDO::FETCH_ASSOC);
    } catch(PDOException $e) {
        $results['errors'][] = 'Fetch failed: ' . $e->getMessage();
        return $results;
    }

    foreach ($reviewers as $rev) {
        // Pick valid email
        $email = '';
        $inst  = trim($rev['institutional_email'] ?? '');
        $pers  = trim($rev['email'] ?? '');
        if (filter_var($inst, FILTER_VALIDATE_EMAIL)) $email = $inst;
        elseif (filter_var($pers, FILTER_VALIDATE_EMAIL)) $email = $pers;

        if (!$email) {
            $results['skipped']++;
            continue;
        }

        try {
            // Build combined name for new user creation
            $rev['full_name_combined'] = trim(
                ($rev['first_name'] ?? '') . ' ' .
                ($rev['middle_name'] ?? '') . ' ' .
                ($rev['last_name'] ?? '')
            );

            // 1. Create/update user
            $userId = upsertReviewerUser($db, $rev, $email);

            // 2. Assign research interests from submission
            // source: suggested → 'auto_suggested', excluded → 'auto_excluded'
            $source = $rev['reviewer_type'] === 'recommended'
                ? 'auto_suggested'
                : 'auto_excluded';
            assignResearchInterests($db, $userId, $submissionId, $source, $rev['research_areas'] ?? null);

            // 3. Link suggested_reviewers row → users row
            linkReviewerToUser($db, (int)$rev['reviewer_id'], $userId);

            $results['synced']++;

        } catch(PDOException $e) {
            $results['errors'][] = "reviewer_id={$rev['reviewer_id']}: " . $e->getMessage();
            $results['skipped']++;
        }
    }

    return $results;
}