SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS festival_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  valid_from DATETIME NULL,
  valid_until DATETIME NULL,
  timezone VARCHAR(80) NOT NULL DEFAULT 'Europe/Bucharest',
  max_entries_per_day INT NOT NULL DEFAULT 3,
  require_exit_scan TINYINT(1) NOT NULL DEFAULT 1,
  allow_manual_override TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY festival_settings_event_unique (event_id),
  KEY festival_settings_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS festival_days (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  day_date DATE NOT NULL,
  opens_at DATETIME NOT NULL,
  closes_at DATETIME NOT NULL,
  max_entries_per_day INT NULL,
  status ENUM('active','inactive','cancelled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY festival_days_event_date_unique (event_id, day_date),
  KEY festival_days_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS access_zones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(140) NOT NULL,
  code VARCHAR(80) NOT NULL,
  description TEXT NULL,
  capacity_limit INT NULL,
  color VARCHAR(40) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY access_zones_event_code_unique (event_id, code),
  KEY access_zones_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_gates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  zone_id BIGINT UNSIGNED NULL,
  name VARCHAR(140) NOT NULL,
  code VARCHAR(80) NOT NULL,
  type ENUM('entry','exit','mixed') NOT NULL DEFAULT 'mixed',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY event_gates_event_code_unique (event_id, code),
  KEY event_gates_event_id_idx (event_id),
  KEY event_gates_zone_id_idx (zone_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_type_access_zones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_type_id BIGINT UNSIGNED NOT NULL,
  event_id BIGINT UNSIGNED NOT NULL,
  zone_id BIGINT UNSIGNED NOT NULL,
  max_entries_per_day INT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY ticket_type_access_zones_unique (ticket_type_id, zone_id),
  KEY ticket_type_access_zones_ticket_type_idx (ticket_type_id),
  KEY ticket_type_access_zones_event_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_access_entitlements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NOT NULL,
  event_id BIGINT UNSIGNED NOT NULL,
  zone_id BIGINT UNSIGNED NOT NULL,
  valid_from DATETIME NULL,
  valid_until DATETIME NULL,
  max_entries_per_day INT NULL,
  status ENUM('active','inactive','revoked') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY ticket_access_entitlements_unique (ticket_id, zone_id),
  KEY ticket_access_entitlements_ticket_id_idx (ticket_id),
  KEY ticket_access_entitlements_event_id_idx (event_id),
  KEY ticket_access_entitlements_zone_id_idx (zone_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_daily_access_counters (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NOT NULL,
  event_id BIGINT UNSIGNED NOT NULL,
  festival_day_id BIGINT UNSIGNED NOT NULL,
  entries_count INT NOT NULL DEFAULT 0,
  exits_count INT NOT NULL DEFAULT 0,
  current_state ENUM('outside','inside','unknown') NOT NULL DEFAULT 'outside',
  last_entry_at DATETIME NULL,
  last_exit_at DATETIME NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY ticket_daily_access_counters_unique (ticket_id, festival_day_id),
  KEY ticket_daily_access_counters_ticket_id_idx (ticket_id),
  KEY ticket_daily_access_counters_event_id_idx (event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_scan_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id BIGINT UNSIGNED NULL,
  event_id BIGINT UNSIGNED NOT NULL,
  festival_day_id BIGINT UNSIGNED NULL,
  gate_id BIGINT UNSIGNED NULL,
  zone_id BIGINT UNSIGNED NULL,
  scanned_by_user_id BIGINT UNSIGNED NULL,
  scanner_device_id VARCHAR(120) NULL,
  scan_mode ENUM('entry','exit','validation_only') NOT NULL,
  result ENUM('valid','invalid','already_inside','already_outside','max_entries_reached','wrong_zone','wrong_day','expired','blocked','refunded','duplicate','manual_override') NOT NULL,
  before_state VARCHAR(40) NULL,
  after_state VARCHAR(40) NULL,
  entries_count_after INT NULL,
  exits_count_after INT NULL,
  source ENUM('central') NOT NULL DEFAULT 'central',
  idempotency_key VARCHAR(190) NOT NULL,
  metadata_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY ticket_scan_events_idempotency_unique (idempotency_key),
  KEY ticket_scan_events_ticket_id_idx (ticket_id),
  KEY ticket_scan_events_event_id_idx (event_id),
  KEY ticket_scan_events_day_idx (festival_day_id),
  KEY ticket_scan_events_gate_idx (gate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;