/**
 * sat_form_logs incorrectly used PRIMARY KEY (user_id), so only one form log row per user was allowed.
 * Creating a second form failed with ER_DUP_ENTRY on user_id.
 *
 * This migration adds log_id as the primary key and indexes user_id. Idempotent.
 *
 * Usage: npm run migrate:sat-form-logs-pk
 */
const pool = require("../src/config/db");

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

async function getForeignKeysOnTable(table) {
  const [rows] = await pool.query(
    `SELECT CONSTRAINT_NAME
     FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
     WHERE CONSTRAINT_SCHEMA = DATABASE()
       AND TABLE_NAME = ?`,
    [table]
  );
  return (rows || []).map((r) => r.CONSTRAINT_NAME);
}

async function primaryKeyColumns(table) {
  const [rows] = await pool.query(
    `SELECT COLUMN_NAME
     FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME = ?
       AND CONSTRAINT_NAME = 'PRIMARY'
     ORDER BY ORDINAL_POSITION`,
    [table]
  );
  return (rows || []).map((r) => r.COLUMN_NAME);
}

async function main() {
  const table = "sat_form_logs";
  try {
    if (await columnExists(table, "log_id")) {
      const pk = await primaryKeyColumns(table);
      if (pk.includes("log_id")) {
        console.log(`[skip] ${table}.log_id already exists and is part of PRIMARY KEY`);
        return;
      }
    }

    const pkCols = await primaryKeyColumns(table);
    if (pkCols.length === 1 && pkCols[0] === "log_id") {
      console.log(`[skip] ${table} primary key is already log_id`);
      return;
    }

    // Only rewrite PK if it's the broken (user_id-only) pattern
    if (pkCols.length && !pkCols.every((c) => c === "user_id")) {
      console.warn(
        `[warn] ${table} PRIMARY KEY is (${pkCols.join(", ")}); expected only user_id — aborting. Fix manually if needed.`
      );
      return;
    }

    const fks = await getForeignKeysOnTable(table);
    const conn = await pool.getConnection();
    try {
      await conn.beginTransaction();
      for (const name of fks) {
        await conn.query(`ALTER TABLE \`${table}\` DROP FOREIGN KEY \`${name}\``);
        console.log(`[ok]   dropped FK ${name}`);
      }
      await conn.query(`ALTER TABLE \`${table}\` DROP PRIMARY KEY`);
      console.log("[ok]   dropped PRIMARY KEY on user_id");
      await conn.query(`
        ALTER TABLE \`${table}\`
        ADD COLUMN \`log_id\` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST
      `);
      console.log("[ok]   added log_id as PRIMARY KEY");
      await conn.query(
        `ALTER TABLE \`${table}\` ADD INDEX \`idx_sat_form_logs_user_id\` (\`user_id\`)`
      );
      console.log("[ok]   added index on user_id");
      await conn.query(`
        ALTER TABLE \`${table}\`
        ADD CONSTRAINT \`users_sat_form_logs\` FOREIGN KEY (\`user_id\`) REFERENCES \`users\` (\`user_id\`)
      `);
      console.log("[ok]   re-added FOREIGN KEY users_sat_form_logs");
      await conn.commit();
      console.log("Done.");
    } catch (e) {
      await conn.rollback();
      throw e;
    } finally {
      conn.release();
    }
  } catch (e) {
    console.error(e);
    process.exitCode = 1;
  } finally {
    await pool.end();
  }
}

main();
