starr-philproperties/apps/api/database/migrations/20270101000085-convert-advertisements-badge-label-to-array.js

'use strict';

// badge_label was a single STRING(100). The Type step redesign lets admins
// attach up to 2 badges (outline chips) to a "Text with Image" ad, so the
// column becomes a JSONB string array (renamed badge_labels to match the
// plural shape, mirroring the existing `ctas` JSONB column). Any existing
// scalar value is wrapped into a single-element array; null/empty becomes [].
module.exports = {
  async up(queryInterface) {
    await queryInterface.sequelize.query(`
      ALTER TABLE advertisements
      ALTER COLUMN badge_label TYPE JSONB
      USING (
        CASE
          WHEN badge_label IS NULL OR badge_label = '' THEN '[]'::jsonb
          ELSE jsonb_build_array(badge_label)
        END
      )
    `);

    await queryInterface.sequelize.query(`
      ALTER TABLE advertisements
      ALTER COLUMN badge_label SET DEFAULT '[]'::jsonb
    `);

    await queryInterface.sequelize.query(`
      ALTER TABLE advertisements
      ALTER COLUMN badge_label SET NOT NULL
    `);

    await queryInterface.renameColumn('advertisements', 'badge_label', 'badge_labels');
  },

  async down(queryInterface) {
    await queryInterface.renameColumn('advertisements', 'badge_labels', 'badge_label');

    await queryInterface.sequelize.query(`
      ALTER TABLE advertisements
      ALTER COLUMN badge_label DROP NOT NULL
    `);

    await queryInterface.sequelize.query(`
      ALTER TABLE advertisements
      ALTER COLUMN badge_label TYPE TEXT
      USING (
        CASE
          WHEN jsonb_array_length(badge_label) = 0 THEN NULL
          ELSE badge_label->>0
        END
      )
    `);

    await queryInterface.sequelize.query(`
      ALTER TABLE advertisements
      ALTER COLUMN badge_label DROP DEFAULT
    `);
  },
};