/**
 * Reconcile user profile schema (B5 of reconcile-historical-add-scripts).
 *
 * Captures the union of DDL effects from two historical scripts that ran
 * against every long-lived env but were never folded into the migration
 * history:
 *
 *   - `scripts/add-user-profile-columns.js` (script #8) — adds `first_name`,
 *     `last_name`, `username UNIQUE`, `profile_image_url`.
 *   - `scripts/add-user-profile-fields.js`  (script #9) — adds the same first
 *     three plus `bio`, `avatar_url`, 10 preference / notification / display
 *     columns, two new tables (`user_settings`, `user_avatars`), 6 CHECK
 *     constraints, and 6 indexes.
 *
 * Per the convoy's Finding 2, BOTH `profile_image_url` (from #8) AND
 * `avatar_url` (from #9) coexist in prod and are read by runtime code
 * (`pages/api/auth/register.js` reads `profile_image_url`; the user/avatar
 * surface reads `avatar_url`). The redundancy is documented as a separate
 * follow-up convoy `unify-user-avatar-column`; this migration must NOT pick
 * one and drop the other.
 *
 * Idempotency (D2):
 *   - Every column ADD uses `ADD COLUMN IF NOT EXISTS`.
 *   - Both new tables use `CREATE TABLE IF NOT EXISTS`.
 *   - Every index uses `CREATE INDEX IF NOT EXISTS`.
 *   - CHECK constraints are wrapped in `DO $$ ... EXCEPTION WHEN
 *     duplicate_object THEN NULL; END $$;` blocks because Postgres pre-15
 *     does not accept `ADD CONSTRAINT ... IF NOT EXISTS` for CHECK
 *     constraints (matches the historical script's try/catch pattern, R5).
 *
 * Defaults-backfill DML (the `UPDATE users SET ... WHERE ... IS NULL` block
 * at the end of script #9) is intentionally NOT replicated. The column
 * DEFAULT clauses are sufficient on a fresh env (every new row gets the
 * default at insert time) and prod-existing rows already have the values
 * from the historical script's run.
 *
 * Down-migration is a hard stub. This is a reconciliation migration; rolling
 * it back on prod would drop columns that runtime code reads.
 *
 * @type {import('node-pg-migrate').ColumnDefinitions | undefined}
 */
export const shorthands = undefined;

/**
 * @param {import('node-pg-migrate').MigrationBuilder} pgm
 * @returns {void}
 */
export const up = (pgm) => {
  pgm.sql(`
    ALTER TABLE users
      ADD COLUMN IF NOT EXISTS first_name VARCHAR(255),
      ADD COLUMN IF NOT EXISTS last_name VARCHAR(255),
      ADD COLUMN IF NOT EXISTS username VARCHAR(255) UNIQUE,
      ADD COLUMN IF NOT EXISTS profile_image_url TEXT,
      ADD COLUMN IF NOT EXISTS bio TEXT,
      ADD COLUMN IF NOT EXISTS avatar_url TEXT,
      ADD COLUMN IF NOT EXISTS favorite_games JSONB DEFAULT '["MTG"]',
      ADD COLUMN IF NOT EXISTS collection_visibility VARCHAR(20) DEFAULT 'private',
      ADD COLUMN IF NOT EXISTS preferred_currency VARCHAR(3) DEFAULT 'USD',
      ADD COLUMN IF NOT EXISTS cards_per_page INTEGER DEFAULT 50,
      ADD COLUMN IF NOT EXISTS default_view VARCHAR(10) DEFAULT 'grid',
      ADD COLUMN IF NOT EXISTS notifications_email BOOLEAN DEFAULT true,
      ADD COLUMN IF NOT EXISTS notifications_marketing BOOLEAN DEFAULT false,
      ADD COLUMN IF NOT EXISTS two_factor_enabled BOOLEAN DEFAULT false,
      ADD COLUMN IF NOT EXISTS theme VARCHAR(10) DEFAULT 'system',
      ADD COLUMN IF NOT EXISTS language VARCHAR(5) DEFAULT 'en';
  `);

  pgm.sql(`
    CREATE TABLE IF NOT EXISTS user_settings (
      id SERIAL PRIMARY KEY,
      user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
      setting_key VARCHAR(100) NOT NULL,
      setting_value JSONB NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE(user_id, setting_key)
    );
  `);

  pgm.sql(`
    CREATE TABLE IF NOT EXISTS user_avatars (
      id SERIAL PRIMARY KEY,
      user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
      filename VARCHAR(255) NOT NULL,
      original_name VARCHAR(255),
      mime_type VARCHAR(100),
      file_size INTEGER,
      file_path TEXT NOT NULL,
      is_active BOOLEAN DEFAULT true,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
  `);

  pgm.sql(`
    CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
    CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
    CREATE INDEX IF NOT EXISTS idx_user_settings_user_id ON user_settings(user_id);
    CREATE INDEX IF NOT EXISTS idx_user_settings_key ON user_settings(setting_key);
    CREATE INDEX IF NOT EXISTS idx_user_avatars_user_id ON user_avatars(user_id);
    CREATE INDEX IF NOT EXISTS idx_user_avatars_active ON user_avatars(user_id, is_active);
  `);

  pgm.sql(`
    DO $$
    BEGIN
      ALTER TABLE users
        ADD CONSTRAINT check_collection_visibility
        CHECK (collection_visibility IN ('private', 'public', 'unlisted'));
    EXCEPTION
      WHEN duplicate_object THEN NULL;
    END$$;
  `);

  pgm.sql(`
    DO $$
    BEGIN
      ALTER TABLE users
        ADD CONSTRAINT check_preferred_currency
        CHECK (preferred_currency IN ('USD', 'EUR', 'GBP', 'CAD', 'JPY'));
    EXCEPTION
      WHEN duplicate_object THEN NULL;
    END$$;
  `);

  pgm.sql(`
    DO $$
    BEGIN
      ALTER TABLE users
        ADD CONSTRAINT check_cards_per_page
        CHECK (cards_per_page IN (25, 50, 100));
    EXCEPTION
      WHEN duplicate_object THEN NULL;
    END$$;
  `);

  pgm.sql(`
    DO $$
    BEGIN
      ALTER TABLE users
        ADD CONSTRAINT check_default_view
        CHECK (default_view IN ('grid', 'list'));
    EXCEPTION
      WHEN duplicate_object THEN NULL;
    END$$;
  `);

  pgm.sql(`
    DO $$
    BEGIN
      ALTER TABLE users
        ADD CONSTRAINT check_theme
        CHECK (theme IN ('light', 'dark', 'system'));
    EXCEPTION
      WHEN duplicate_object THEN NULL;
    END$$;
  `);

  pgm.sql(`
    DO $$
    BEGIN
      ALTER TABLE users
        ADD CONSTRAINT check_language
        CHECK (language IN ('en', 'es', 'fr', 'de', 'ja'));
    EXCEPTION
      WHEN duplicate_object THEN NULL;
    END$$;
  `);
};

/**
 * Down-migration is a hard stub. Rolling back this reconciliation would drop
 * columns that runtime code reads (e.g. `pages/api/auth/register.js` writes
 * and reads `profile_image_url`; `pages/api/user/settings.js` reads
 * `theme` / `language` / etc.) and would also drop the `user_settings` and
 * `user_avatars` tables along with any rows in them. If you need to roll
 * forward instead, write a NEW dated migration with a real `down`.
 *
 * @returns {void}
 */
export const down = () => {
  throw new Error(
    '[migration:1781000000005_reconcile-user-profile] Refusing to roll back. ' +
      'This is a reconciliation migration that captures DDL already in prod via ' +
      'scripts/add-user-profile-columns.js and scripts/add-user-profile-fields.js; ' +
      'rolling it back would drop columns that runtime code reads (profile_image_url, ' +
      'avatar_url, theme, language, etc.) and the user_settings / user_avatars tables. ' +
      'Write a new dated migration if a real schema correction is needed.'
  );
};