SET NAMES utf8mb4;

SET @col_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'festival_settings' AND COLUMN_NAME = 'cashless_enabled'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE festival_settings ADD COLUMN cashless_enabled TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'festival_settings' AND COLUMN_NAME = 'online_checkin_enabled'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE festival_settings ADD COLUMN online_checkin_enabled TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'festival_settings' AND COLUMN_NAME = 'nfc_enabled'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE festival_settings ADD COLUMN nfc_enabled TINYINT(1) NOT NULL DEFAULT 0', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'festival_settings' AND COLUMN_NAME = 'refund_window_hours'
);
SET @sql := IF(@col_exists = 0, 'ALTER TABLE festival_settings ADD COLUMN refund_window_hours INT NOT NULL DEFAULT 48', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS wristbands (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  uid_hash CHAR(64) NOT NULL,
  public_code VARCHAR(120) NOT NULL,
  status ENUM('blank','assigned','active','blocked','lost','replaced','refunded') NOT NULL DEFAULT 'blank',
  issued_at DATETIME NULL,
  issued_by_user_id BIGINT UNSIGNED NULL,
  replaced_by_wristband_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY wristbands_uid_hash_idx (uid_hash),
  UNIQUE KEY wristbands_public_code_unique (public_code),
  KEY wristbands_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wristband_assignments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wristband_id BIGINT UNSIGNED NOT NULL,
  ticket_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  event_id BIGINT UNSIGNED NOT NULL,
  assigned_by_user_id BIGINT UNSIGNED NOT NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status ENUM('active','replaced','revoked') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY wristband_assignments_ticket_id_idx (ticket_id),
  KEY wristband_assignments_wristband_id_idx (wristband_id),
  KEY wristband_assignments_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cashless_wallets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  ticket_id BIGINT UNSIGNED NULL,
  wristband_id BIGINT UNSIGNED NULL,
  currency CHAR(3) NOT NULL DEFAULT 'RON',
  balance DECIMAL(10,2) NOT NULL DEFAULT 0,
  status ENUM('active','frozen','refunded','closed') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY cashless_wallets_ticket_unique (ticket_id),
  KEY cashless_wallets_ticket_id_idx (ticket_id),
  KEY cashless_wallets_wristband_id_idx (wristband_id),
  KEY cashless_wallets_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cashless_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wallet_id BIGINT UNSIGNED NOT NULL,
  event_id BIGINT UNSIGNED NOT NULL,
  wristband_id BIGINT UNSIGNED NULL,
  ticket_id BIGINT UNSIGNED NULL,
  type ENUM('topup_online','topup_pos','purchase','refund','adjustment','reversal') NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  balance_before DECIMAL(10,2) NOT NULL,
  balance_after DECIMAL(10,2) NOT NULL,
  vendor_id BIGINT UNSIGNED NULL,
  terminal_id BIGINT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NULL,
  payment_id BIGINT UNSIGNED NULL,
  status ENUM('pending','completed','failed','reversed') NOT NULL DEFAULT 'completed',
  source ENUM('central') NOT NULL DEFAULT 'central',
  idempotency_key VARCHAR(190) NOT NULL,
  metadata_json JSON NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY cashless_transactions_wallet_id_idx (wallet_id),
  UNIQUE KEY cashless_transactions_idempotency_unique (idempotency_key),
  KEY cashless_transactions_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS festival_vendors (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  fiscal_name VARCHAR(190) NULL,
  cui VARCHAR(60) NULL,
  status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  commission_rate DECIMAL(5,2) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY festival_vendors_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vendor_terminals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  vendor_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  device_code VARCHAR(120) NOT NULL,
  status ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY vendor_terminals_device_unique (device_code),
  KEY vendor_terminals_vendor_id_idx (vendor_id),
  KEY vendor_terminals_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS online_checkins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  ticket_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  full_name VARCHAR(190) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(40) NULL,
  date_of_birth DATE NULL,
  document_type VARCHAR(60) NULL,
  document_last4 VARCHAR(4) NULL,
  verification_photo_url VARCHAR(500) NULL,
  raw_document_url VARCHAR(500) NULL,
  raw_document_status ENUM('not_uploaded','uploaded','verified','rejected','deleted') NOT NULL DEFAULT 'not_uploaded',
  consent_accepted_at DATETIME NULL,
  status ENUM('pending','approved','rejected','needs_review') NOT NULL DEFAULT 'pending',
  reviewed_by_user_id BIGINT UNSIGNED NULL,
  reviewed_at DATETIME NULL,
  rejection_reason TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY online_checkins_ticket_unique (ticket_id),
  KEY online_checkins_ticket_id_idx (ticket_id),
  KEY online_checkins_event_id_idx (event_id),
  KEY online_checkins_status_idx (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invitation_batches (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  created_by_user_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(190) NOT NULL,
  quantity INT NOT NULL,
  zone_id BIGINT UNSIGNED NULL,
  activation_fee DECIMAL(10,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'RON',
  expires_at DATETIME NULL,
  status ENUM('active','paused','cancelled','completed') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY invitation_batches_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invitations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  batch_id BIGINT UNSIGNED NOT NULL,
  event_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(80) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  zone_id BIGINT UNSIGNED NULL,
  activation_fee DECIMAL(10,2) NOT NULL DEFAULT 0,
  status ENUM('generated','sent','opened','activated','expired','cancelled') NOT NULL DEFAULT 'generated',
  assigned_email VARCHAR(190) NULL,
  activated_by_user_id BIGINT UNSIGNED NULL,
  activated_order_id BIGINT UNSIGNED NULL,
  activated_ticket_id BIGINT UNSIGNED NULL,
  activated_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY invitations_code_unique (code),
  KEY invitations_token_hash_idx (token_hash),
  KEY invitations_event_id_idx (event_id),
  KEY invitations_batch_id_idx (batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nfc_write_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  ticket_id BIGINT UNSIGNED NOT NULL,
  wristband_id BIGINT UNSIGNED NULL,
  operator_user_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending','written','verified','failed','cancelled','simulation') NOT NULL DEFAULT 'pending',
  write_payload_hash CHAR(64) NOT NULL,
  error_message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  KEY nfc_write_jobs_ticket_id_idx (ticket_id),
  KEY nfc_write_jobs_event_id_idx (event_id),
  KEY nfc_write_jobs_wristband_id_idx (wristband_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;