CREATE TABLE IF NOT EXISTS users (
  id CHAR(32) PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  email VARCHAR(190) NULL UNIQUE,
  phone VARCHAR(40) NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  email_verified_at DATETIME NULL,
  phone_verified_at DATETIME NULL,
  status ENUM('active','pending','suspended','deleted') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  CHECK (email IS NOT NULL OR phone IS NOT NULL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS password_reset_tokens (
  id CHAR(32) PRIMARY KEY,
  user_id CHAR(32) NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  KEY idx_password_reset_user (user_id),
  KEY idx_password_reset_expires (expires_at),
  CONSTRAINT fk_password_reset_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_verification_tokens (
  id CHAR(32) PRIMARY KEY,
  user_id CHAR(32) NOT NULL,
  email VARCHAR(190) NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  KEY idx_email_verification_user (user_id),
  KEY idx_email_verification_expires (expires_at),
  CONSTRAINT fk_email_verification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mfa_challenges (
  id CHAR(32) PRIMARY KEY,
  user_id CHAR(32) NOT NULL,
  purpose VARCHAR(40) NOT NULL DEFAULT 'mfa_login',
  channel ENUM('email','sms') NOT NULL DEFAULT 'email',
  recipient VARCHAR(190) NOT NULL,
  code_hash CHAR(64) NOT NULL,
  status ENUM('pending','verified','expired','failed') NOT NULL DEFAULT 'pending',
  attempt_count INT NOT NULL DEFAULT 0,
  expires_at DATETIME NOT NULL,
  verified_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  KEY idx_mfa_user_status (user_id, status, expires_at),
  KEY idx_mfa_expires (expires_at),
  CONSTRAINT fk_mfa_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rate_limit_attempts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  bucket VARCHAR(80) NOT NULL,
  attempt_key CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_rate_limit_bucket_key_time (bucket, attempt_key, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
  id CHAR(32) PRIMARY KEY,
  role_key VARCHAR(120) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  scope ENUM('platform','organiser','event','customer') NOT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
  id CHAR(32) PRIMARY KEY,
  permission_key VARCHAR(160) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  module VARCHAR(80) NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id CHAR(32) NOT NULL,
  permission_id CHAR(32) NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organisers (
  id CHAR(32) PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  slug VARCHAR(190) NOT NULL UNIQUE,
  status ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending',
  country CHAR(2) NOT NULL,
  tax_identifier VARCHAR(80) NULL,
  website VARCHAR(255) NULL,
  description TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organiser_applications (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NULL,
  organisation_name VARCHAR(190) NOT NULL,
  contact_name VARCHAR(190) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  address TEXT NOT NULL,
  country CHAR(2) NOT NULL,
  business_details TEXT NULL,
  status ENUM('submitted','approved','rejected','suspended') NOT NULL DEFAULT 'submitted',
  reviewed_by CHAR(32) NULL,
  reviewed_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_organiser_applications_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_organiser_applications_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organiser_staff (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  user_id CHAR(32) NOT NULL,
  status ENUM('active','invited','suspended') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_organiser_staff (organiser_id, user_id),
  CONSTRAINT fk_organiser_staff_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_organiser_staff_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organiser_features (
  feature_key VARCHAR(80) PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  description VARCHAR(255) NULL,
  default_enabled TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organiser_feature_overrides (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  feature_key VARCHAR(80) NOT NULL,
  enabled TINYINT(1) NOT NULL,
  updated_by CHAR(32) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_organiser_feature_override (organiser_id, feature_key),
  KEY idx_organiser_feature_override_org (organiser_id),
  KEY idx_organiser_feature_override_feature (feature_key),
  CONSTRAINT fk_organiser_feature_override_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id) ON DELETE CASCADE,
  CONSTRAINT fk_organiser_feature_override_feature FOREIGN KEY (feature_key) REFERENCES organiser_features(feature_key) ON DELETE CASCADE,
  CONSTRAINT fk_organiser_feature_override_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_roles (
  id CHAR(32) PRIMARY KEY,
  user_id CHAR(32) NOT NULL,
  role_id CHAR(32) NOT NULL,
  organiser_id CHAR(32) NULL,
  event_id CHAR(32) NULL,
  created_at DATETIME NOT NULL,
  KEY idx_user_roles_user (user_id),
  CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_categories (
  id CHAR(32) PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS venues (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  name VARCHAR(190) NOT NULL,
  address TEXT NOT NULL,
  country CHAR(2) NOT NULL,
  floor_plan_path VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_venues_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS venue_sections (
  id CHAR(32) PRIMARY KEY,
  venue_id CHAR(32) NOT NULL,
  venue_seat_map_id CHAR(32) NULL,
  name VARCHAR(120) NOT NULL,
  admission_type ENUM('reserved','general_admission') NOT NULL DEFAULT 'reserved',
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  KEY idx_venue_sections_map (venue_seat_map_id),
  KEY idx_venue_sections_admission (admission_type),
  CONSTRAINT fk_venue_sections_venue FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS venue_seat_maps (
  id CHAR(32) PRIMARY KEY,
  venue_id CHAR(32) NOT NULL,
  name VARCHAR(160) NOT NULL,
  layout_json JSON NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_seat_maps_venue FOREIGN KEY (venue_id) REFERENCES venues(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS seats (
  id CHAR(32) PRIMARY KEY,
  venue_seat_map_id CHAR(32) NOT NULL,
  venue_section_id CHAR(32) NULL,
  label VARCHAR(40) NOT NULL,
  row_label VARCHAR(20) NULL,
  seat_number VARCHAR(20) NULL,
  seat_type ENUM('standard','accessible','vip','table','box','standing') NOT NULL DEFAULT 'standard',
  x DECIMAL(10,2) NOT NULL DEFAULT 0,
  y DECIMAL(10,2) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_seat_map_label (venue_seat_map_id, label),
  CONSTRAINT fk_seats_map FOREIGN KEY (venue_seat_map_id) REFERENCES venue_seat_maps(id) ON DELETE CASCADE,
  CONSTRAINT fk_seats_section FOREIGN KEY (venue_section_id) REFERENCES venue_sections(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS events (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  category_id CHAR(32) NULL,
  venue_id CHAR(32) NULL,
  venue_seat_map_id CHAR(32) NULL,
  title VARCHAR(190) NOT NULL,
  slug VARCHAR(190) NOT NULL UNIQUE,
  description TEXT NOT NULL,
  start_at DATETIME NOT NULL,
  end_at DATETIME NOT NULL,
  timezone VARCHAR(80) NOT NULL,
  city VARCHAR(120) NULL,
  country_code CHAR(2) NOT NULL DEFAULT 'GB',
  currency_code CHAR(3) NOT NULL DEFAULT 'GBP',
  locale VARCHAR(20) NOT NULL DEFAULT 'en_GB',
  date_style ENUM('short','medium','long','full') NOT NULL DEFAULT 'medium',
  time_style ENUM('24h','12h','auto') NOT NULL DEFAULT '24h',
  address_display_options JSON NULL,
  online_payment_enabled TINYINT(1) NOT NULL DEFAULT 1,
  counter_cash_enabled TINYINT(1) NOT NULL DEFAULT 1,
  counter_card_enabled TINYINT(1) NOT NULL DEFAULT 1,
  counter_external_card_enabled TINYINT(1) NOT NULL DEFAULT 1,
  section_ticketing_enabled TINYINT(1) NOT NULL DEFAULT 0,
  seating_mode ENUM('reserved','general_admission','mixed','standing','table','vip_boxes') NOT NULL,
  status ENUM('draft','published','paused','cancelled') NOT NULL DEFAULT 'draft',
  visibility ENUM('public','private','unlisted') NOT NULL DEFAULT 'public',
  donation_enabled TINYINT(1) NOT NULL DEFAULT 0,
  merchandise_enabled TINYINT(1) NOT NULL DEFAULT 0,
  promo_enabled TINYINT(1) NOT NULL DEFAULT 0,
  max_tickets_per_order INT NOT NULL DEFAULT 10,
  refund_policy_text TEXT NULL,
  all_ages_allowed TINYINT(1) NOT NULL DEFAULT 1,
  minimum_age INT NULL,
  child_tickets_allowed TINYINT(1) NOT NULL DEFAULT 1,
  infants_allowed TINYINT(1) NOT NULL DEFAULT 1,
  under_18_requires_adult TINYINT(1) NOT NULL DEFAULT 0,
  age_restriction_message VARCHAR(255) NULL,
  venue_age_note VARCHAR(255) NULL,
  seo_title VARCHAR(190) NULL,
  seo_description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  KEY idx_events_country_currency (country_code, currency_code),
  CONSTRAINT fk_events_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_events_category FOREIGN KEY (category_id) REFERENCES event_categories(id),
  CONSTRAINT fk_events_venue FOREIGN KEY (venue_id) REFERENCES venues(id),
  CONSTRAINT fk_events_venue_seat_map FOREIGN KEY (venue_seat_map_id) REFERENCES venue_seat_maps(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_seats (
  id CHAR(32) PRIMARY KEY,
  event_id CHAR(32) NOT NULL,
  seat_id CHAR(32) NOT NULL,
  status ENUM('available','held','sold','blocked','inactive') NOT NULL DEFAULT 'available',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_event_seat (event_id, seat_id),
  KEY idx_event_seats_status (event_id, status),
  CONSTRAINT fk_event_seats_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  CONSTRAINT fk_event_seats_seat FOREIGN KEY (seat_id) REFERENCES seats(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tax_rules (
  id CHAR(32) PRIMARY KEY,
  label VARCHAR(80) NOT NULL,
  country CHAR(2) NULL,
  region VARCHAR(80) NULL,
  rate_percent DECIMAL(7,4) NOT NULL,
  applies_to_ticket TINYINT(1) NOT NULL DEFAULT 1,
  applies_to_platform_fee TINYINT(1) NOT NULL DEFAULT 1,
  applies_to_merchandise TINYINT(1) NOT NULL DEFAULT 1,
  applies_to_donation TINYINT(1) NOT NULL DEFAULT 0,
  active_from DATE NOT NULL,
  active_until DATE NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS platform_fee_rules (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NULL,
  event_id CHAR(32) NULL,
  category_id CHAR(32) NULL,
  country CHAR(2) NOT NULL DEFAULT 'GB',
  fee_type ENUM('fixed','percentage','per_ticket') NOT NULL,
  fixed_minor_amount INT NOT NULL DEFAULT 0,
  percentage_rate DECIMAL(7,4) NOT NULL DEFAULT 0,
  min_minor_amount INT NULL,
  max_minor_amount INT NULL,
  currency CHAR(3) NOT NULL,
  active_from DATE NOT NULL,
  active_until DATE NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  KEY idx_fee_global_country (country, is_active),
  KEY idx_fee_event_policy (event_id, is_active),
  CONSTRAINT fk_fee_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_fee_event FOREIGN KEY (event_id) REFERENCES events(id),
  CONSTRAINT fk_fee_category FOREIGN KEY (category_id) REFERENCES event_categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_section_ticketing (
  id CHAR(32) PRIMARY KEY,
  event_id CHAR(32) NOT NULL,
  venue_section_id CHAR(32) NOT NULL,
  requires_ticket_mapping TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_event_section_ticketing (event_id, venue_section_id),
  KEY idx_event_section_ticketing_section (venue_section_id),
  CONSTRAINT fk_event_section_ticketing_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  CONSTRAINT fk_event_section_ticketing_section FOREIGN KEY (venue_section_id) REFERENCES venue_sections(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_types (
  id CHAR(32) PRIMARY KEY,
  event_id CHAR(32) NOT NULL,
  catalog_ticket_type_id CHAR(32) NULL,
  tax_rule_id CHAR(32) NULL,
  name VARCHAR(160) NOT NULL,
  description TEXT NULL,
  ticket_category ENUM('adult','child','infant','student','senior','family','carer','companion','general','vip','custom') NOT NULL DEFAULT 'general',
  min_age INT NULL,
  max_age INT NULL,
  requires_adult TINYINT(1) NOT NULL DEFAULT 0,
  adult_ticket_required TINYINT(1) NOT NULL DEFAULT 0,
  is_free TINYINT(1) NOT NULL DEFAULT 0,
  requires_id_check TINYINT(1) NOT NULL DEFAULT 0,
  requires_parental_consent TINYINT(1) NOT NULL DEFAULT 0,
  terms_text TEXT NULL,
  price_minor_amount INT NOT NULL,
  currency CHAR(3) NOT NULL,
  quantity INT NOT NULL,
  min_per_order INT NOT NULL DEFAULT 1,
  max_per_order INT NOT NULL DEFAULT 10,
  sale_start_at DATETIME NULL,
  sale_end_at DATETIME NULL,
  visibility ENUM('public','private','hidden') NOT NULL DEFAULT 'public',
  status ENUM('active','paused','sold_out','archived') NOT NULL DEFAULT 'active',
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  KEY idx_ticket_types_catalog (catalog_ticket_type_id),
  CONSTRAINT fk_ticket_types_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  CONSTRAINT fk_ticket_types_tax FOREIGN KEY (tax_rule_id) REFERENCES tax_rules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_type_sections (
  id CHAR(32) PRIMARY KEY,
  ticket_type_id CHAR(32) NOT NULL,
  venue_section_id CHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_ticket_type_section (ticket_type_id, venue_section_id),
  KEY idx_ticket_type_sections_section (venue_section_id),
  CONSTRAINT fk_ticket_type_sections_ticket FOREIGN KEY (ticket_type_id) REFERENCES ticket_types(id) ON DELETE CASCADE,
  CONSTRAINT fk_ticket_type_sections_section FOREIGN KEY (venue_section_id) REFERENCES venue_sections(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_type_catalog (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  name VARCHAR(160) NOT NULL,
  description TEXT NULL,
  ticket_category ENUM('adult','child','infant','student','senior','family','carer','companion','general','vip','custom') NOT NULL DEFAULT 'general',
  min_age INT NULL,
  max_age INT NULL,
  requires_adult TINYINT(1) NOT NULL DEFAULT 0,
  adult_ticket_required TINYINT(1) NOT NULL DEFAULT 0,
  requires_id_check TINYINT(1) NOT NULL DEFAULT 0,
  requires_parental_consent TINYINT(1) NOT NULL DEFAULT 0,
  terms_text TEXT NULL,
  status ENUM('active','archived') NOT NULL DEFAULT 'active',
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_ticket_catalog_org_name (organiser_id, name),
  KEY idx_ticket_catalog_org_status (organiser_id, status, sort_order),
  CONSTRAINT fk_ticket_catalog_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_inventory (
  id CHAR(32) PRIMARY KEY,
  ticket_type_id CHAR(32) NOT NULL,
  available_quantity INT NOT NULL,
  held_quantity INT NOT NULL DEFAULT 0,
  sold_quantity INT NOT NULL DEFAULT 0,
  updated_at DATETIME NULL,
  KEY idx_ticket_inventory_ticket_type (ticket_type_id),
  CONSTRAINT fk_inventory_ticket_type FOREIGN KEY (ticket_type_id) REFERENCES ticket_types(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS checkout_drafts (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  checkout_id VARCHAR(80) NOT NULL UNIQUE,
  event_slug VARCHAR(190) NOT NULL,
  payload_json JSON NULL,
  total_minor_amount INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'GBP',
  status ENUM('created','pending','pending_payment','paid','failed','cancelled','expired') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  KEY idx_checkout_drafts_status_created (status, created_at),
  KEY idx_checkout_drafts_event_status (event_slug, status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS checkout_payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  checkout_id VARCHAR(80) NOT NULL UNIQUE,
  provider VARCHAR(40) NOT NULL DEFAULT 'local_mock',
  provider_reference VARCHAR(160) NULL,
  status ENUM('pending','succeeded','failed','cancelled') NOT NULL DEFAULT 'pending',
  response_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  KEY idx_checkout_payments_status_updated (status, updated_at),
  KEY idx_checkout_payments_provider_ref (provider, provider_reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS seat_holds (
  id CHAR(32) PRIMARY KEY,
  event_seat_id CHAR(32) NOT NULL,
  customer_token VARCHAR(120) NOT NULL,
  status ENUM('active','expired','released','converted') NOT NULL DEFAULT 'active',
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  KEY idx_seat_holds_expiry (status, expires_at),
  KEY idx_seat_holds_seat_status_expiry (event_seat_id, status, expires_at),
  KEY idx_seat_holds_customer_status (customer_token, status, expires_at),
  CONSTRAINT fk_seat_holds_event_seat FOREIGN KEY (event_seat_id) REFERENCES event_seats(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
  id CHAR(32) PRIMARY KEY,
  user_id CHAR(32) NULL,
  organiser_id CHAR(32) NOT NULL,
  event_id CHAR(32) NOT NULL,
  customer_name VARCHAR(190) NOT NULL,
  customer_email VARCHAR(190) NULL,
  customer_phone VARCHAR(40) NULL,
  status ENUM('draft','pending_payment','paid','payment_failed','cancelled','refunded','partially_refunded') NOT NULL DEFAULT 'draft',
  ticket_net_amount INT NOT NULL DEFAULT 0,
  ticket_tax_amount INT NOT NULL DEFAULT 0,
  ticket_gross_amount INT NOT NULL DEFAULT 0,
  platform_fee_net_amount INT NOT NULL DEFAULT 0,
  platform_fee_tax_amount INT NOT NULL DEFAULT 0,
  platform_fee_gross_amount INT NOT NULL DEFAULT 0,
  organiser_gross_amount INT NOT NULL DEFAULT 0,
  stripe_fee_amount INT NULL,
  donation_amount INT NOT NULL DEFAULT 0,
  merchandise_net_amount INT NOT NULL DEFAULT 0,
  merchandise_tax_amount INT NOT NULL DEFAULT 0,
  merchandise_gross_amount INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL,
  tax_rule_id CHAR(32) NULL,
  fee_rule_id CHAR(32) NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CHECK (customer_email IS NOT NULL OR customer_phone IS NOT NULL),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_orders_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_orders_event FOREIGN KEY (event_id) REFERENCES events(id),
  CONSTRAINT fk_orders_tax FOREIGN KEY (tax_rule_id) REFERENCES tax_rules(id),
  CONSTRAINT fk_orders_fee FOREIGN KEY (fee_rule_id) REFERENCES platform_fee_rules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NOT NULL,
  item_type ENUM('ticket','merchandise','donation','service_fee') NOT NULL,
  reference_id CHAR(32) NULL,
  description VARCHAR(190) NOT NULL,
  quantity INT NOT NULL,
  net_minor_amount INT NOT NULL,
  tax_minor_amount INT NOT NULL,
  gross_minor_amount INT NOT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tickets (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NOT NULL,
  event_id CHAR(32) NOT NULL,
  ticket_type_id CHAR(32) NOT NULL,
  event_seat_id CHAR(32) NULL,
  attendee_name VARCHAR(190) NULL,
  status ENUM('reserved','issued','checked_in','cancelled','refunded','void') NOT NULL DEFAULT 'reserved',
  checked_in_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_tickets_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_tickets_event FOREIGN KEY (event_id) REFERENCES events(id),
  CONSTRAINT fk_tickets_type FOREIGN KEY (ticket_type_id) REFERENCES ticket_types(id),
  CONSTRAINT fk_tickets_event_seat FOREIGN KEY (event_seat_id) REFERENCES event_seats(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_qr_tokens (
  id CHAR(32) PRIMARY KEY,
  ticket_id CHAR(32) NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,
  issued_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  CONSTRAINT fk_ticket_qr_ticket FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stripe_connected_accounts (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  stripe_account_id VARCHAR(120) NOT NULL UNIQUE,
  onboarding_status ENUM('not_started','pending','complete','restricted') NOT NULL DEFAULT 'not_started',
  charges_enabled TINYINT(1) NOT NULL DEFAULT 0,
  payouts_enabled TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_stripe_accounts_organiser (organiser_id),
  CONSTRAINT fk_stripe_accounts_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NOT NULL,
  gateway VARCHAR(40) NOT NULL,
  status ENUM('created','requires_action','processing','succeeded','failed','cancelled','refunded','disputed') NOT NULL,
  amount_minor INT NOT NULL,
  currency CHAR(3) NOT NULL,
  gateway_payment_intent_id VARCHAR(120) NULL,
  gateway_charge_id VARCHAR(120) NULL,
  gateway_application_fee_id VARCHAR(120) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_transactions (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NOT NULL,
  gateway VARCHAR(40) NOT NULL,
  gateway_reference VARCHAR(160) NOT NULL,
  status VARCHAR(60) NOT NULL,
  amount_minor INT NOT NULL,
  currency CHAR(3) NOT NULL,
  raw_payload JSON NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_payment_transactions_order FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS counter_payments (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NOT NULL,
  organiser_id CHAR(32) NOT NULL,
  event_id CHAR(32) NOT NULL,
  cashier_user_id CHAR(32) NULL,
  payment_method ENUM('cash','external_card') NOT NULL DEFAULT 'cash',
  external_reference VARCHAR(160) NULL,
  amount_minor INT NOT NULL,
  cash_received_minor INT NULL,
  change_due_minor INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL,
  platform_fee_gross_amount INT NOT NULL DEFAULT 0,
  organiser_gross_amount INT NOT NULL DEFAULT 0,
  status ENUM('collected','void') NOT NULL DEFAULT 'collected',
  note VARCHAR(255) NULL,
  collected_at DATETIME NOT NULL,
  voided_by CHAR(32) NULL,
  voided_at DATETIME NULL,
  void_reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  KEY idx_counter_payments_organiser_collected (organiser_id, collected_at),
  KEY idx_counter_payments_event_status (event_id, status, collected_at),
  KEY idx_counter_payments_method_status (payment_method, status, collected_at),
  CONSTRAINT fk_counter_payments_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_counter_payments_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_counter_payments_event FOREIGN KEY (event_id) REFERENCES events(id),
  CONSTRAINT fk_counter_payments_cashier FOREIGN KEY (cashier_user_id) REFERENCES users(id),
  CONSTRAINT fk_counter_payments_voided_by FOREIGN KEY (voided_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organiser_platform_fee_payables (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  event_id CHAR(32) NULL,
  order_id CHAR(32) NULL,
  counter_payment_id CHAR(32) NULL,
  source_type VARCHAR(60) NOT NULL,
  amount_net_minor INT NOT NULL DEFAULT 0,
  amount_tax_minor INT NOT NULL DEFAULT 0,
  amount_gross_minor INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'GBP',
  status ENUM('open','invoiced','settled','void') NOT NULL DEFAULT 'open',
  invoice_reference VARCHAR(160) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  KEY idx_platform_fee_payables_organiser_status (organiser_id, status, created_at),
  KEY idx_platform_fee_payables_order (order_id),
  CONSTRAINT fk_platform_fee_payables_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_platform_fee_payables_event FOREIGN KEY (event_id) REFERENCES events(id),
  CONSTRAINT fk_platform_fee_payables_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_platform_fee_payables_counter FOREIGN KEY (counter_payment_id) REFERENCES counter_payments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ledger_entries (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NULL,
  organiser_id CHAR(32) NULL,
  entry_type VARCHAR(80) NOT NULL,
  debit_minor_amount INT NOT NULL DEFAULT 0,
  credit_minor_amount INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL,
  description VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_ledger_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_ledger_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tax_breakdowns (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NOT NULL,
  order_item_id CHAR(32) NULL,
  tax_rule_id CHAR(32) NOT NULL,
  component VARCHAR(60) NOT NULL,
  net_minor_amount INT NOT NULL,
  tax_minor_amount INT NOT NULL,
  gross_minor_amount INT NOT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_tax_breakdowns_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_tax_breakdowns_item FOREIGN KEY (order_item_id) REFERENCES order_items(id),
  CONSTRAINT fk_tax_breakdowns_rule FOREIGN KEY (tax_rule_id) REFERENCES tax_rules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS refunds (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NOT NULL,
  payment_id CHAR(32) NULL,
  status ENUM('requested','processing','succeeded','failed','cancelled') NOT NULL DEFAULT 'requested',
  amount_minor INT NOT NULL,
  reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_refunds_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_refunds_payment FOREIGN KEY (payment_id) REFERENCES payments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS refund_items (
  id CHAR(32) PRIMARY KEY,
  refund_id CHAR(32) NOT NULL,
  order_item_id CHAR(32) NULL,
  item_type VARCHAR(40) NOT NULL,
  description VARCHAR(190) NOT NULL,
  quantity INT NOT NULL DEFAULT 1,
  amount_minor INT NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_refund_items_refund (refund_id),
  KEY idx_refund_items_order_item (order_item_id),
  CONSTRAINT fk_refund_items_refund FOREIGN KEY (refund_id) REFERENCES refunds(id) ON DELETE CASCADE,
  CONSTRAINT fk_refund_items_order_item FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS promo_codes (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  event_id CHAR(32) NULL,
  code VARCHAR(60) NOT NULL,
  discount_type ENUM('percentage','fixed') NOT NULL,
  discount_value DECIMAL(10,2) NOT NULL,
  usage_limit INT NULL,
  per_customer_limit INT NULL,
  min_order_minor_amount INT NULL,
  starts_at DATETIME NULL,
  expires_at DATETIME NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_promo_code_organiser (organiser_id, code),
  CONSTRAINT fk_promos_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_promos_event FOREIGN KEY (event_id) REFERENCES events(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS promo_code_redemptions (
  id CHAR(32) PRIMARY KEY,
  promo_code_id CHAR(32) NOT NULL,
  order_id CHAR(32) NOT NULL,
  user_id CHAR(32) NULL,
  discount_minor_amount INT NOT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_promo_redemptions_promo FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id),
  CONSTRAINT fk_promo_redemptions_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_promo_redemptions_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS donations (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NOT NULL,
  organiser_id CHAR(32) NOT NULL,
  event_id CHAR(32) NULL,
  amount_minor INT NOT NULL,
  currency CHAR(3) NOT NULL,
  tax_rule_id CHAR(32) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_donations_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_donations_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_donations_event FOREIGN KEY (event_id) REFERENCES events(id),
  CONSTRAINT fk_donations_tax FOREIGN KEY (tax_rule_id) REFERENCES tax_rules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_donation_settings (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  event_id CHAR(32) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  donation_only_checkout_allowed TINYINT(1) NOT NULL DEFAULT 0,
  custom_amount_allowed TINYINT(1) NOT NULL DEFAULT 1,
  suggested_amounts_json JSON NULL,
  appeal_title VARCHAR(160) NULL,
  appeal_body TEXT NULL,
  tax_rule_id CHAR(32) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_event_donation_settings_event (event_id),
  KEY idx_event_donation_settings_organiser (organiser_id),
  CONSTRAINT fk_event_donation_settings_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_event_donation_settings_event FOREIGN KEY (event_id) REFERENCES events(id),
  CONSTRAINT fk_event_donation_settings_tax FOREIGN KEY (tax_rule_id) REFERENCES tax_rules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  event_id CHAR(32) NULL,
  name VARCHAR(190) NOT NULL,
  sku VARCHAR(100) NOT NULL,
  price_minor_amount INT NOT NULL,
  currency CHAR(3) NOT NULL,
  stock_quantity INT NOT NULL DEFAULT 0,
  status ENUM('active','inactive','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_product_sku (organiser_id, sku),
  CONSTRAINT fk_products_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_products_event FOREIGN KEY (event_id) REFERENCES events(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_images (
  id CHAR(32) PRIMARY KEY,
  product_id CHAR(32) NOT NULL,
  path VARCHAR(255) NOT NULL,
  alt_text VARCHAR(190) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_product_images_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_event_assignments (
  id CHAR(32) PRIMARY KEY,
  product_id CHAR(32) NOT NULL,
  event_id CHAR(32) NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_product_event_assignment (product_id, event_id),
  KEY idx_product_event_assignments_event (event_id),
  CONSTRAINT fk_product_event_assignments_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  CONSTRAINT fk_product_event_assignments_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_orders (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NOT NULL,
  fulfilment_status ENUM('pending','ready','collected','cancelled') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_product_orders_order FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_order_items (
  id CHAR(32) PRIMARY KEY,
  product_order_id CHAR(32) NOT NULL,
  product_id CHAR(32) NOT NULL,
  quantity INT NOT NULL,
  gross_minor_amount INT NOT NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_product_order_items_order FOREIGN KEY (product_order_id) REFERENCES product_orders(id),
  CONSTRAINT fk_product_order_items_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scanner_devices (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  event_id CHAR(32) NULL,
  name VARCHAR(160) NOT NULL,
  api_key_hash CHAR(64) NULL,
  status ENUM('active','revoked') NOT NULL DEFAULT 'active',
  last_seen_at DATETIME NULL,
  created_by CHAR(32) NULL,
  key_rotated_at DATETIME NULL,
  revoked_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  CONSTRAINT fk_scanner_devices_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_scanner_devices_event FOREIGN KEY (event_id) REFERENCES events(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scan_logs (
  id CHAR(32) PRIMARY KEY,
  ticket_id CHAR(32) NULL,
  event_id CHAR(32) NOT NULL,
  scanner_user_id CHAR(32) NULL,
  device_id VARCHAR(120) NOT NULL,
  status ENUM('valid','already_scanned','wrong_event','cancelled','invalid','duplicate_offline') NOT NULL,
  scanned_at DATETIME NOT NULL,
  synced_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  KEY idx_scan_logs_event (event_id, scanned_at),
  CONSTRAINT fk_scan_logs_ticket FOREIGN KEY (ticket_id) REFERENCES tickets(id),
  CONSTRAINT fk_scan_logs_event FOREIGN KEY (event_id) REFERENCES events(id),
  CONSTRAINT fk_scan_logs_user FOREIGN KEY (scanner_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scanner_validations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  token_hash CHAR(64) NOT NULL,
  result VARCHAR(40) NOT NULL,
  payload_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_scanner_validations_token_created (token_hash, created_at),
  KEY idx_scanner_validations_result_created (result, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendee_imports (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  event_id CHAR(32) NOT NULL,
  uploaded_by CHAR(32) NOT NULL,
  original_filename VARCHAR(190) NOT NULL,
  status ENUM('uploaded','processing','completed','failed') NOT NULL DEFAULT 'uploaded',
  row_count INT NOT NULL DEFAULT 0,
  error_count INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_imports_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_imports_event FOREIGN KEY (event_id) REFERENCES events(id),
  CONSTRAINT fk_imports_user FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attendee_import_rows (
  id CHAR(32) PRIMARY KEY,
  attendee_import_id CHAR(32) NOT NULL,
  row_number INT NOT NULL,
  attendee_name VARCHAR(190) NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(40) NULL,
  status ENUM('valid','invalid') NOT NULL DEFAULT 'valid',
  error_message VARCHAR(255) NULL,
  raw_json JSON NULL,
  created_at DATETIME NOT NULL,
  KEY idx_attendee_import_rows_import (attendee_import_id),
  CONSTRAINT fk_attendee_import_rows_import FOREIGN KEY (attendee_import_id) REFERENCES attendee_imports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS featured_event_packages (
  id CHAR(32) PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  description VARCHAR(255) NULL,
  duration_days INT NOT NULL,
  price_minor_amount INT NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'GBP',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 50,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  KEY idx_featured_packages_active (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS featured_event_purchases (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  event_id CHAR(32) NOT NULL,
  package_id CHAR(32) NOT NULL,
  purchased_by CHAR(32) NULL,
  checkout_id VARCHAR(80) NOT NULL UNIQUE,
  payment_provider VARCHAR(40) NOT NULL DEFAULT 'payment_unavailable',
  provider_reference VARCHAR(160) NULL,
  status ENUM('pending_payment','paid','cancelled','expired') NOT NULL DEFAULT 'pending_payment',
  price_minor_amount INT NOT NULL,
  currency CHAR(3) NOT NULL,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  paid_at DATETIME NULL,
  response_json JSON NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  KEY idx_featured_purchase_event_status (event_id, status, starts_at, ends_at),
  KEY idx_featured_purchase_organiser_status (organiser_id, status, created_at),
  KEY idx_featured_purchase_provider (payment_provider, provider_reference),
  CONSTRAINT fk_featured_purchase_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_featured_purchase_event FOREIGN KEY (event_id) REFERENCES events(id),
  CONSTRAINT fk_featured_purchase_package FOREIGN KEY (package_id) REFERENCES featured_event_packages(id),
  CONSTRAINT fk_featured_purchase_user FOREIGN KEY (purchased_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(160) NOT NULL UNIQUE,
  setting_value JSON NOT NULL,
  status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  updated_by CHAR(32) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  CONSTRAINT fk_site_settings_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS finance_export_runs (
  id CHAR(32) PRIMARY KEY,
  schedule_key VARCHAR(120) NOT NULL,
  export_type VARCHAR(40) NOT NULL,
  period_key VARCHAR(40) NOT NULL,
  file_path VARCHAR(500) NULL,
  row_count INT NOT NULL DEFAULT 0,
  byte_size INT NOT NULL DEFAULT 0,
  status VARCHAR(40) NOT NULL,
  message TEXT NULL,
  generated_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_finance_export_runs_period (schedule_key, export_type, status),
  KEY idx_finance_export_runs_generated (generated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS finance_export_deliveries (
  id CHAR(32) PRIMARY KEY,
  run_id CHAR(32) NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  channel VARCHAR(40) NOT NULL,
  status VARCHAR(40) NOT NULL,
  provider_reference VARCHAR(160) NULL,
  message TEXT NULL,
  queued_at DATETIME NOT NULL,
  sent_at DATETIME NULL,
  failed_at DATETIME NULL,
  KEY idx_finance_export_deliveries_run (run_id),
  KEY idx_finance_export_deliveries_status (status, queued_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media_assets (
  id CHAR(32) PRIMARY KEY,
  owner_type VARCHAR(40) NOT NULL,
  owner_id CHAR(32) NULL,
  media_type VARCHAR(60) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_path VARCHAR(255) NOT NULL,
  public_url VARCHAR(255) NOT NULL,
  mime_type VARCHAR(80) NOT NULL,
  size_bytes INT NOT NULL,
  alt_text VARCHAR(255) NULL,
  crop_json JSON NULL,
  status ENUM('active','archived') NOT NULL DEFAULT 'active',
  sha256_hash CHAR(64) NULL,
  image_width INT NULL,
  image_height INT NULL,
  scan_status ENUM('clean','blocked','infected','failed') NOT NULL DEFAULT 'clean',
  scan_engine VARCHAR(80) NULL,
  scan_message VARCHAR(255) NULL,
  variants_json JSON NULL,
  processing_status ENUM('processed','skipped','failed') NOT NULL DEFAULT 'skipped',
  processing_message VARCHAR(255) NULL,
  created_by CHAR(32) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  deleted_at DATETIME NULL,
  KEY idx_media_owner (owner_type, owner_id),
  KEY idx_media_status (status, deleted_at),
  KEY idx_media_assets_scan_status (scan_status, processing_status, deleted_at),
  CONSTRAINT fk_media_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 media_scan_runs (
  id CHAR(32) PRIMARY KEY,
  scanned_count INT NOT NULL DEFAULT 0,
  clean_count INT NOT NULL DEFAULT 0,
  blocked_count INT NOT NULL DEFAULT 0,
  infected_count INT NOT NULL DEFAULT 0,
  failed_count INT NOT NULL DEFAULT 0,
  missing_count INT NOT NULL DEFAULT 0,
  status ENUM('ok','warning','failed') NOT NULL DEFAULT 'ok',
  message TEXT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_media_scan_runs_created (created_at),
  KEY idx_media_scan_runs_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media_retention_runs (
  id CHAR(32) PRIMARY KEY,
  mode ENUM('dry_run','soft_archive') NOT NULL DEFAULT 'dry_run',
  candidate_count INT NOT NULL DEFAULT 0,
  soft_archived_count INT NOT NULL DEFAULT 0,
  review_delete_count INT NOT NULL DEFAULT 0,
  status ENUM('ok','review','failed') NOT NULL DEFAULT 'ok',
  message TEXT NULL,
  created_by CHAR(32) NULL,
  created_at DATETIME NOT NULL,
  KEY idx_media_retention_runs_created (created_at),
  KEY idx_media_retention_runs_status (status, created_at),
  CONSTRAINT fk_media_retention_runs_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 system_alerts (
  id CHAR(32) PRIMARY KEY,
  alert_key VARCHAR(190) NOT NULL UNIQUE,
  source VARCHAR(80) NOT NULL,
  severity ENUM('info','warning','critical') NOT NULL DEFAULT 'info',
  title VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  status ENUM('open','acknowledged','resolved') NOT NULL DEFAULT 'open',
  subject_type VARCHAR(80) NULL,
  subject_id VARCHAR(120) NULL,
  metadata_json JSON NULL,
  first_seen_at DATETIME NOT NULL,
  last_seen_at DATETIME NOT NULL,
  acknowledged_by CHAR(32) NULL,
  acknowledged_at DATETIME NULL,
  resolved_at DATETIME NULL,
  KEY idx_system_alerts_status_seen (status, severity, last_seen_at),
  KEY idx_system_alerts_source_status (source, status),
  CONSTRAINT fk_system_alerts_acknowledged_by FOREIGN KEY (acknowledged_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_alert_notifications (
  id CHAR(32) PRIMARY KEY,
  alert_id CHAR(32) NOT NULL,
  alert_key VARCHAR(190) NOT NULL,
  severity ENUM('info','warning','critical') NOT NULL DEFAULT 'info',
  channel ENUM('email','sms') NOT NULL DEFAULT 'email',
  recipient VARCHAR(190) NOT NULL,
  status ENUM('queued','sent','failed','skipped') NOT NULL DEFAULT 'queued',
  provider_reference VARCHAR(160) NULL,
  message TEXT NULL,
  queued_at DATETIME NOT NULL,
  sent_at DATETIME NULL,
  failed_at DATETIME NULL,
  last_error TEXT NULL,
  KEY idx_alert_notifications_alert_recipient (alert_id, channel, recipient, queued_at),
  KEY idx_alert_notifications_status (status, queued_at),
  CONSTRAINT fk_system_alert_notifications_alert FOREIGN KEY (alert_id) REFERENCES system_alerts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS organiser_admin_requests (
  id CHAR(32) PRIMARY KEY,
  organiser_id CHAR(32) NOT NULL,
  requested_by CHAR(32) NOT NULL,
  request_type ENUM('email_campaign','support','feature','other') NOT NULL DEFAULT 'email_campaign',
  subject VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  status ENUM('submitted','reviewing','completed','declined') NOT NULL DEFAULT 'submitted',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  KEY idx_admin_requests_organiser_status (organiser_id, status, created_at),
  KEY idx_admin_requests_type_status (request_type, status, created_at),
  CONSTRAINT fk_admin_requests_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id) ON DELETE CASCADE,
  CONSTRAINT fk_admin_requests_requested_by FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_media (
  id CHAR(32) PRIMARY KEY,
  event_id CHAR(32) NOT NULL,
  media_asset_id CHAR(32) NOT NULL,
  slot ENUM('poster','hero','gallery') NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_event_media_slot_asset (event_id, media_asset_id, slot),
  KEY idx_event_media_slot (event_id, slot, sort_order),
  CONSTRAINT fk_event_media_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  CONSTRAINT fk_event_media_asset FOREIGN KEY (media_asset_id) REFERENCES media_assets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS venue_media (
  id CHAR(32) PRIMARY KEY,
  venue_id CHAR(32) NOT NULL,
  media_asset_id CHAR(32) NOT NULL,
  slot ENUM('floor_plan') NOT NULL DEFAULT 'floor_plan',
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_venue_media_slot (venue_id, slot),
  KEY idx_venue_media_asset (media_asset_id),
  CONSTRAINT fk_venue_media_venue FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE,
  CONSTRAINT fk_venue_media_asset FOREIGN KEY (media_asset_id) REFERENCES media_assets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS seat_map_media (
  id CHAR(32) PRIMARY KEY,
  venue_seat_map_id CHAR(32) NOT NULL,
  media_asset_id CHAR(32) NOT NULL,
  slot ENUM('floor_plan') NOT NULL DEFAULT 'floor_plan',
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_seat_map_media_slot (venue_seat_map_id, slot),
  KEY idx_seat_map_media_asset (media_asset_id),
  KEY idx_seat_map_media_map (venue_seat_map_id),
  CONSTRAINT fk_seat_map_media_map FOREIGN KEY (venue_seat_map_id) REFERENCES venue_seat_maps(id) ON DELETE CASCADE,
  CONSTRAINT fk_seat_map_media_asset FOREIGN KEY (media_asset_id) REFERENCES media_assets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id CHAR(32) PRIMARY KEY,
  actor_user_id CHAR(32) NULL,
  organiser_id CHAR(32) NULL,
  event_id CHAR(32) NULL,
  action VARCHAR(160) NOT NULL,
  subject_type VARCHAR(120) NOT NULL,
  subject_id CHAR(32) NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  metadata JSON NULL,
  created_at DATETIME NOT NULL,
  KEY idx_audit_actor (actor_user_id, created_at),
  CONSTRAINT fk_audit_actor FOREIGN KEY (actor_user_id) REFERENCES users(id),
  CONSTRAINT fk_audit_organiser FOREIGN KEY (organiser_id) REFERENCES organisers(id),
  CONSTRAINT fk_audit_event FOREIGN KEY (event_id) REFERENCES events(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_log_integrity (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  audit_log_id CHAR(32) NOT NULL UNIQUE,
  previous_hash CHAR(64) NULL,
  entry_hash CHAR(64) NOT NULL,
  algorithm VARCHAR(40) NOT NULL DEFAULT 'sha256',
  created_at DATETIME NOT NULL,
  KEY idx_audit_log_integrity_hash (entry_hash),
  KEY idx_audit_log_integrity_created (created_at),
  CONSTRAINT fk_audit_integrity_log FOREIGN KEY (audit_log_id) REFERENCES audit_logs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_consents (
  id CHAR(32) PRIMARY KEY,
  user_id CHAR(32) NULL,
  order_id CHAR(32) NULL,
  consent_type VARCHAR(80) NOT NULL,
  consent_version VARCHAR(40) NOT NULL,
  granted TINYINT(1) NOT NULL,
  granted_at DATETIME NOT NULL,
  ip_address VARCHAR(45) NULL,
  CONSTRAINT fk_consents_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_consents_order FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS privacy_requests (
  id CHAR(32) PRIMARY KEY,
  user_id CHAR(32) NULL,
  email VARCHAR(190) NULL,
  request_type ENUM('export','delete','anonymise','correction') NOT NULL,
  status ENUM('submitted','reviewing','completed','rejected') NOT NULL DEFAULT 'submitted',
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  completed_at DATETIME NULL,
  CONSTRAINT fk_privacy_requests_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_sms_delivery_logs (
  id CHAR(32) PRIMARY KEY,
  order_id CHAR(32) NULL,
  ticket_id CHAR(32) NULL,
  channel ENUM('email','sms','wallet','pdf') NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  status ENUM('queued','sent','failed','skipped') NOT NULL DEFAULT 'queued',
  provider_reference VARCHAR(160) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_delivery_order FOREIGN KEY (order_id) REFERENCES orders(id),
  CONSTRAINT fk_delivery_ticket FOREIGN KEY (ticket_id) REFERENCES tickets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
  id CHAR(32) PRIMARY KEY,
  setting_key VARCHAR(160) NOT NULL UNIQUE,
  setting_value JSON NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS webhooks (
  id CHAR(32) PRIMARY KEY,
  provider VARCHAR(40) NOT NULL,
  event_id VARCHAR(160) NOT NULL,
  event_type VARCHAR(160) NOT NULL,
  signature_valid TINYINT(1) NOT NULL DEFAULT 0,
  processed_at DATETIME NULL,
  payload JSON NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uq_webhook_provider_event (provider, event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;