exports.up = async function(knex) {
  const hasGender = await knex.schema.hasColumn('users', 'gender');
  if (!hasGender) {
    await knex.schema.raw("ALTER TABLE users ADD COLUMN gender TEXT CHECK(gender IN ('male', 'female', 'other'))");
  }

  await knex.schema.raw("UPDATE users SET gender = LOWER(gender) WHERE gender IS NOT NULL AND LOWER(gender) IN ('male', 'female', 'other')");
  await knex.schema.raw("UPDATE users SET gender = NULL WHERE gender IS NOT NULL AND gender NOT IN ('male', 'female', 'other')");

  await knex.schema.raw(`
    CREATE TRIGGER IF NOT EXISTS users_gender_insert_check
    BEFORE INSERT ON users
    WHEN NEW.gender IS NOT NULL AND NEW.gender NOT IN ('male', 'female', 'other')
    BEGIN
      SELECT RAISE(ABORT, 'invalid gender');
    END;
  `);

  await knex.schema.raw(`
    CREATE TRIGGER IF NOT EXISTS users_gender_update_check
    BEFORE UPDATE OF gender ON users
    WHEN NEW.gender IS NOT NULL AND NEW.gender NOT IN ('male', 'female', 'other')
    BEGIN
      SELECT RAISE(ABORT, 'invalid gender');
    END;
  `);
};

exports.down = async function(knex) {
  await knex.schema.raw('DROP TRIGGER IF EXISTS users_gender_update_check');
  await knex.schema.raw('DROP TRIGGER IF EXISTS users_gender_insert_check');
};
