/**
 * Adds googleId, profilePicture, microsoftId to Users if missing.
 * Run: node scripts/add-user-oauth-columns.js
 */
require("dotenv").config();
const sequelize = require("../db");

async function addColumnIfMissing(name, definition) {
  const [rows] = await sequelize.query(
    `SELECT COUNT(*) AS c FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'Users' AND COLUMN_NAME = :name`,
    { replacements: { name } },
  );
  const count = rows[0]?.c ?? rows[0]?.C;
  if (Number(count) > 0) {
    console.log(`Column Users.${name} already exists — skip`);
    return;
  }
  await sequelize.query(`ALTER TABLE Users ADD COLUMN \`${name}\` ${definition}`);
  console.log(`Added column Users.${name}`);
}

async function addIndexIfMissing(indexName, columnName) {
  const [rows] = await sequelize.query(
    `SELECT COUNT(*) AS c FROM INFORMATION_SCHEMA.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'Users' AND INDEX_NAME = :ix`,
    { replacements: { ix: indexName } },
  );
  const count = rows[0]?.c ?? rows[0]?.C;
  if (Number(count) > 0) {
    console.log(`Index ${indexName} already exists — skip`);
    return;
  }
  await sequelize.query(
    `CREATE INDEX \`${indexName}\` ON Users (\`${columnName}\`)`,
  );
  console.log(`Created index ${indexName}`);
}

(async () => {
  try {
    await addColumnIfMissing("googleId", "VARCHAR(255) NULL");
    await addColumnIfMissing("profilePicture", "TEXT NULL");
    await addColumnIfMissing("microsoftId", "VARCHAR(255) NULL");
    await addIndexIfMissing("idx_users_google_id", "googleId");
    await addIndexIfMissing("idx_users_microsoft_id", "microsoftId");
    console.log("Done.");
    process.exit(0);
  } catch (err) {
    console.error(err);
    process.exit(1);
  }
})();
