import { DataTypes, type QueryInterface } from 'sequelize';

const TABLE = 'personality_quiz_results';

async function columnExists(
  queryInterface: QueryInterface,
  column: string,
): Promise<boolean> {
  const [results] = await queryInterface.sequelize.query(`
    SELECT 1
    FROM information_schema.columns
    WHERE table_name = '${TABLE}'
      AND column_name = '${column}'
  `);
  return (results as unknown[]).length > 0;
}

async function addColumnIfMissing(
  queryInterface: QueryInterface,
  column: string,
  definition: Parameters<QueryInterface['addColumn']>[2],
): Promise<void> {
  if (!(await columnExists(queryInterface, column))) {
    await queryInterface.addColumn(TABLE, column, definition);
  }
}

export default {
  up: async (queryInterface: QueryInterface) => {
    await addColumnIfMissing(queryInterface, 'quiz_kind', {
      type: DataTypes.TEXT,
      allowNull: false,
      defaultValue: 'personality_type',
    });
    await addColumnIfMissing(queryInterface, 'quiz_id', {
      type: DataTypes.TEXT,
      allowNull: false,
      defaultValue: 'personality-type',
    });
    await addColumnIfMissing(queryInterface, 'quiz_title', {
      type: DataTypes.TEXT,
      allowNull: false,
      defaultValue: 'Personality Type Quiz',
    });
    await addColumnIfMissing(queryInterface, 'week_of', {
      type: DataTypes.TEXT,
      allowNull: true,
    });
    await addColumnIfMissing(queryInterface, 'score', {
      type: DataTypes.INTEGER,
      allowNull: true,
    });
    await addColumnIfMissing(queryInterface, 'total_questions', {
      type: DataTypes.INTEGER,
      allowNull: false,
      defaultValue: 0,
    });
    await addColumnIfMissing(queryInterface, 'result_label', {
      type: DataTypes.TEXT,
      allowNull: true,
    });
    await addColumnIfMissing(queryInterface, 'result_payload', {
      type: DataTypes.JSONB,
      allowNull: true,
    });
    await addColumnIfMissing(queryInterface, 'user_name', {
      type: DataTypes.TEXT,
      allowNull: true,
    });
    await addColumnIfMissing(queryInterface, 'user_role', {
      type: DataTypes.TEXT,
      allowNull: true,
    });

    await queryInterface.sequelize.query(`
      UPDATE "${TABLE}"
      SET
        "result_label" = COALESCE("result_label", "personality_type"),
        "total_questions" = CASE
          WHEN "total_questions" > 0 THEN "total_questions"
          WHEN jsonb_typeof("quiz_answers") = 'object' THEN (
            SELECT count(*)::integer
            FROM jsonb_object_keys("quiz_answers")
          )
          ELSE 0
        END
      WHERE "deletedAt" IS NULL
    `);

    await queryInterface.changeColumn(TABLE, 'personality_type', {
      type: DataTypes.TEXT,
      allowNull: true,
    });
  },

  down: async (queryInterface: QueryInterface) => {
    await queryInterface.sequelize.query(`
      DELETE FROM "${TABLE}"
      WHERE "personality_type" IS NULL
    `);
    await queryInterface.changeColumn(TABLE, 'personality_type', {
      type: DataTypes.TEXT,
      allowNull: false,
    });
    await queryInterface.removeColumn(TABLE, 'user_role');
    await queryInterface.removeColumn(TABLE, 'user_name');
    await queryInterface.removeColumn(TABLE, 'result_payload');
    await queryInterface.removeColumn(TABLE, 'result_label');
    await queryInterface.removeColumn(TABLE, 'total_questions');
    await queryInterface.removeColumn(TABLE, 'score');
    await queryInterface.removeColumn(TABLE, 'week_of');
    await queryInterface.removeColumn(TABLE, 'quiz_title');
    await queryInterface.removeColumn(TABLE, 'quiz_id');
    await queryInterface.removeColumn(TABLE, 'quiz_kind');
  },
};
