-- Migrate notification_queue to UUID-based columns (id, tenant_id, branch_id, user_id, record_id)
DO $$ BEGIN
  -- id: replace serial with uuid
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='notification_queue' AND column_name='id' AND data_type IN ('integer','bigint')
  ) THEN
    ALTER TABLE notification_queue ADD COLUMN id_new uuid DEFAULT uuid_generate_v7();
    UPDATE notification_queue SET id_new = uuid_generate_v7();
    ALTER TABLE notification_queue DROP CONSTRAINT IF EXISTS notification_queue_pkey;
    ALTER TABLE notification_queue DROP COLUMN id;
    ALTER TABLE notification_queue RENAME COLUMN id_new TO id;
    ALTER TABLE notification_queue ADD PRIMARY KEY (id);
  END IF;

  -- tenant_id
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='notification_queue' AND column_name='tenant_id' AND data_type IN ('integer','bigint')
  ) THEN
    ALTER TABLE notification_queue RENAME COLUMN tenant_id TO tenant_id_old;
    ALTER TABLE notification_queue ADD COLUMN tenant_id uuid NULL;
    UPDATE notification_queue SET tenant_id = NULL; -- no safe cast; leave null
    ALTER TABLE notification_queue DROP COLUMN tenant_id_old;
  END IF;

  -- branch_id
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='notification_queue' AND column_name='branch_id' AND data_type IN ('integer','bigint')
  ) THEN
    ALTER TABLE notification_queue RENAME COLUMN branch_id TO branch_id_old;
    ALTER TABLE notification_queue ADD COLUMN branch_id uuid NULL;
    UPDATE notification_queue SET branch_id = NULL;
    ALTER TABLE notification_queue DROP COLUMN branch_id_old;
  END IF;

  -- user_id
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='notification_queue' AND column_name='user_id' AND data_type IN ('integer','bigint')
  ) THEN
    ALTER TABLE notification_queue RENAME COLUMN user_id TO user_id_old;
    ALTER TABLE notification_queue ADD COLUMN user_id uuid NULL;
    UPDATE notification_queue SET user_id = NULL;
    ALTER TABLE notification_queue DROP COLUMN user_id_old;
  END IF;

  -- record_id
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='notification_queue' AND column_name='record_id' AND data_type IN ('integer','bigint')
  ) THEN
    ALTER TABLE notification_queue RENAME COLUMN record_id TO record_id_old;
    ALTER TABLE notification_queue ADD COLUMN record_id uuid NULL;
    UPDATE notification_queue SET record_id = NULL;
    ALTER TABLE notification_queue DROP COLUMN record_id_old;
  END IF;
END $$;

