#!/usr/bin/env php
<?php
/**
 * Migrate keywords from theses.remarks → tags + thesis_tags.
 *
 * The CSV importer (before the column-shift fix) stored comma-separated
 * keywords in the `remarks` column. This script extracts them and creates
 * proper tag rows, then clears the remarks column.
 *
 * Run: php migrations/pending/013_fix_remarks_keywords.php
 */

require_once __DIR__ . '/../../src/Database.php';

$db = Database::getInstance();
$pdo = $db->getPDO();

// Fetch theses with non-empty remarks
$rows = $pdo->query(
    "SELECT id, remarks FROM theses WHERE remarks IS NOT NULL AND remarks != ''"
)->fetchAll();

$insertTag  = $pdo->prepare('INSERT OR IGNORE INTO tags (name) VALUES (?)');
$getTagId   = $pdo->prepare('SELECT id FROM tags WHERE name = ?');
$insertLink = $pdo->prepare('INSERT OR IGNORE INTO thesis_tags (thesis_id, tag_id) VALUES (?, ?)');
$clearRemarks = $pdo->prepare('UPDATE theses SET remarks = NULL WHERE id = ?');

$pdo->beginTransaction();

try {
    $migrated = 0;
    foreach ($rows as $row) {
        $thesisId = (int)$row['id'];
        $raw      = trim($row['remarks']);
        if ($raw === '') {
            $clearRemarks->execute([$thesisId]);
            continue;
        }

        $keywords = array_map('trim', explode(',', $raw));
        foreach ($keywords as $kw) {
            $kw = trim($kw);
            if ($kw === '' || strlen($kw) > 100) continue;

            // Create tag if needed
            $insertTag->execute([$kw]);
            $getTagId->execute([$kw]);
            $tagId = $getTagId->fetchColumn();
            if ($tagId) {
                $insertLink->execute([$thesisId, (int)$tagId]);
            }
        }

        $clearRemarks->execute([$thesisId]);
        $migrated++;
    }

    $pdo->commit();
    echo "Done. Migrated keywords for $migrated theses.\n";
} catch (Throwable $e) {
    $pdo->rollBack();
    echo "Error: " . $e->getMessage() . "\n";
    exit(1);
}