/**
 * Idempotent: add submission_role to ptw_submissions if missing.
 * Fixes: "Unknown column 'submission_role' in 'field list'"
 *
 * Usage (from ptw_backend folder):
 *   node scripts/apply-submission-role-column.js
 */
import path from "path";
import { fileURLToPath } from "url";
import dotenv from "dotenv";
import { pool } from "../src/config/db.js";

const __dirname = path.dirname(fileURLToPath(import.meta.url));
dotenv.config({ path: path.join(__dirname, "..", ".env") });

async function columnExists(tableName, columnName) {
  const [rows] = await pool.query(
    `SELECT COUNT(*) AS c FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = ?
       AND COLUMN_NAME = ?`,
    [tableName, columnName]
  );
  return rows[0].c > 0;
}

async function main() {
  try {
    if (await columnExists("ptw_submissions", "submission_role")) {
      console.log("[skip] ptw_submissions.submission_role already exists");
    } else {
      await pool.query(
        "ALTER TABLE ptw_submissions ADD COLUMN submission_role VARCHAR(32) NULL AFTER form_id"
      );
      console.log("[ok]   ptw_submissions.submission_role added");
    }
    console.log("Done.");
  } catch (e) {
    console.error(e);
    process.exitCode = 1;
  } finally {
    await pool.end();
  }
}

main();
