ALTER TABLE users ADD COLUMN IF NOT EXISTS cpf VARCHAR(20) NULL UNIQUE AFTER phone;
ALTER TABLE users DROP INDEX phone;
ALTER TABLE users ADD INDEX idx_users_phone (phone);
ALTER TABLE campaign_numbers ADD COLUMN IF NOT EXISTS migration_batch_id BIGINT UNSIGNED NULL AFTER reservation_id;
ALTER TABLE reservations ADD COLUMN IF NOT EXISTS migration_batch_id BIGINT UNSIGNED NULL AFTER paid_at;
ALTER TABLE reservations ADD COLUMN IF NOT EXISTS source_payload JSON NULL AFTER migration_batch_id;
ALTER TABLE payments ADD COLUMN IF NOT EXISTS receipt_url TEXT NULL AFTER qr_code_data_url;
ALTER TABLE payments ADD COLUMN IF NOT EXISTS provider_payment_id VARCHAR(120) NULL AFTER provider_reference;
ALTER TABLE payments ADD COLUMN IF NOT EXISTS external_reference VARCHAR(120) NULL AFTER provider_payment_id;

CREATE TABLE IF NOT EXISTS webhook_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(40) NOT NULL,
  event_type VARCHAR(80) NULL,
  provider_payment_id VARCHAR(120) NULL,
  status VARCHAR(40) NULL,
  payload JSON NULL,
  processed TINYINT(1) NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_webhook_provider_payment (provider, provider_payment_id),
  INDEX idx_webhook_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS participants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(40) NULL,
  cpf VARCHAR(20) NULL,
  email VARCHAR(190) NULL,
  source VARCHAR(80) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_participant_cpf (cpf),
  INDEX idx_participant_phone (phone),
  CONSTRAINT fk_participants_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS migration_batches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_type ENUM('csv','json','database','upload') NOT NULL DEFAULT 'upload',
  source_name VARCHAR(190) NULL,
  status ENUM('pending','running','completed','failed') NOT NULL DEFAULT 'pending',
  total_rows INT UNSIGNED NOT NULL DEFAULT 0,
  imported_rows INT UNSIGNED NOT NULL DEFAULT 0,
  created_by BIGINT UNSIGNED NULL,
  raw_payload JSON NULL,
  report_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_migration_status (status),
  CONSTRAINT fk_migration_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS migration_backups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  migration_batch_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  backup_json JSON NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_migration_backup_batch FOREIGN KEY (migration_batch_id) REFERENCES migration_batches(id) ON DELETE CASCADE,
  CONSTRAINT fk_migration_backup_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;