/**
 * One-off: fix `orac_question_options` foreign key.
 *
 * Wrong schema ties AUTO_INCREMENT `question_options_id` to `orac_questions.question_id`, which breaks every INSERT.
 * Correct: `question_id` -> `orac_questions.question_id` (same as `orac_answers`).
 *
 * Uses DB_* from `.env`. Safe to run multiple times.
 */
import "dotenv/config";
import mysql from "mysql2/promise";

const {
  DB_HOST = "127.0.0.1",
  DB_PORT = "3306",
  DB_USER = "root",
  DB_PASSWORD = "12345"
} = process.env;

async function main() {
  const conn = await mysql.createConnection({
    host: DB_HOST,
    port: Number(DB_PORT),
    user: DB_USER,
    password: DB_PASSWORD,
    database: "hse"
  });

  try {
    const [badFks] = await conn.query(
      `SELECT CONSTRAINT_NAME
       FROM information_schema.KEY_COLUMN_USAGE
       WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = 'orac_question_options'
         AND REFERENCED_TABLE_NAME = 'orac_questions'
         AND COLUMN_NAME = 'question_options_id'
         AND REFERENCED_COLUMN_NAME = 'question_id'`
    );

    for (const row of badFks) {
      const name = row.CONSTRAINT_NAME;
      await conn.query(`ALTER TABLE \`orac_question_options\` DROP FOREIGN KEY \`${name}\``);
      console.log(`Dropped incorrect FK: ${name}`);
    }

    const [goodFks] = await conn.query(
      `SELECT CONSTRAINT_NAME
       FROM information_schema.KEY_COLUMN_USAGE
       WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = 'orac_question_options'
         AND REFERENCED_TABLE_NAME = 'orac_questions'
         AND COLUMN_NAME = 'question_id'
         AND REFERENCED_COLUMN_NAME = 'question_id'`
    );

    if (goodFks.length > 0) {
      console.log(
        `OK: FK on question_id already exists (${goodFks.map((r) => r.CONSTRAINT_NAME).join(", ")}). No add step.`
      );
      return;
    }

    await conn.query(
      `ALTER TABLE \`orac_question_options\`
       ADD CONSTRAINT \`orac_questions_orac_question_options_question\`
       FOREIGN KEY (\`question_id\`)
       REFERENCES \`orac_questions\` (\`question_id\`)
       ON DELETE CASCADE
       ON UPDATE CASCADE`
    );
    console.log("OK: added FK orac_question_options.question_id -> orac_questions.question_id");
  } finally {
    await conn.end();
  }
}

main().catch((err) => {
  console.error(err.message || err);
  process.exit(1);
});
