-- Migration: Seat Transfer Track — Schema
-- Description: Creates the seat_transfer_track table used to audit admin-initiated
--              seat transfers (userId swap) on program registrations.
-- Idempotent — safe to re-run.

CREATE TABLE IF NOT EXISTS seat_transfer_track (
  id                  BIGSERIAL     PRIMARY KEY,
  registration_id     BIGINT        NOT NULL,
  original_user_id    BIGINT        NOT NULL,
  new_user_id         BIGINT        NOT NULL,
  source_user_id      BIGINT        NULL,
  original_user_name  VARCHAR(255)  NOT NULL,
  new_user_name       VARCHAR(255)  NOT NULL,
  transfer_reason     TEXT          NULL,
  admin_notes         TEXT          NULL,
  transfer_status     VARCHAR(50)   NOT NULL DEFAULT 'COMPLETED',
  created_at          TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          TIMESTAMP     NULL     DEFAULT CURRENT_TIMESTAMP,
  deleted_at          TIMESTAMP     NULL,
  created_by          BIGINT        NULL,
  updated_by          BIGINT        NULL
);

COMMENT ON TABLE seat_transfer_track IS 'Audit log for admin seat transfers — records every userId swap on a program registration';
COMMENT ON COLUMN seat_transfer_track.source_user_id IS 'Chain-transfer origin: always points to the original registrant across multiple A→B→C transfers';

-- Supporting indexes
CREATE INDEX IF NOT EXISTS idx_stt_registration_id
  ON seat_transfer_track (registration_id);

CREATE INDEX IF NOT EXISTS idx_stt_original_user_id
  ON seat_transfer_track (original_user_id);

CREATE INDEX IF NOT EXISTS idx_stt_new_user_id
  ON seat_transfer_track (new_user_id);
