-- CrowdSource Funder - baseline schema (MySQL 8.0+ / MariaDB 10.5+).
-- All timestamps are stored in UTC. All money columns are DECIMAL. All tables are InnoDB.
-- Apply with:  php bin/migrate.php     (or import this file, then database/hardening.sql, then database/seed.sql)

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ---------------------------------------------------------------------------
-- Administrators (created first because other tables reference them)
-- ---------------------------------------------------------------------------
CREATE TABLE admins (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL,
  display_name VARCHAR(100) NOT NULL DEFAULT '',
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('super_admin','operator','viewer') NOT NULL DEFAULT 'viewer',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  totp_secret_enc VARCHAR(255) NULL COMMENT 'sodium-encrypted base32 secret; NULL = MFA not enabled',
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admins_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Anonymous visitors (no registration). public_reference is the only id ever shown.
-- Raw session identifiers are never stored here.
-- ---------------------------------------------------------------------------
CREATE TABLE anonymous_visitors (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_reference VARCHAR(24) NOT NULL,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  first_seen_at DATETIME NOT NULL,
  last_seen_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_visitors_reference (public_reference),
  KEY idx_visitors_last_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Remember-this-device secrets (HMAC digest only) so a visitor survives PHP session expiry.
CREATE TABLE visitor_devices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  visitor_id BIGINT UNSIGNED NOT NULL,
  secret_hash CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL,
  last_used_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_devices_secret (secret_hash),
  KEY idx_devices_visitor (visitor_id),
  KEY idx_devices_expires (expires_at),
  CONSTRAINT fk_devices_visitor FOREIGN KEY (visitor_id) REFERENCES anonymous_visitors (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE visitor_recovery_credentials (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  visitor_id BIGINT UNSIGNED NOT NULL,
  credential_hash CHAR(64) NOT NULL COMMENT 'HMAC-SHA256 (keyed) of the 150-bit credential',
  hint CHAR(4) NOT NULL COMMENT 'last 4 characters, for display in Settings only',
  status ENUM('active','revoked') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  expires_at DATETIME NULL,
  last_used_at DATETIME NULL,
  revoked_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_recovery_hash (credential_hash),
  KEY idx_recovery_visitor (visitor_id, status),
  CONSTRAINT fk_recovery_visitor FOREIGN KEY (visitor_id) REFERENCES anonymous_visitors (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Packages and payment destinations
-- ---------------------------------------------------------------------------
CREATE TABLE funding_packages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  package_code VARCHAR(32) NOT NULL,
  name VARCHAR(60) NOT NULL,
  description VARCHAR(255) NOT NULL DEFAULT '',
  purchase_price_usd DECIMAL(12,2) NOT NULL,
  funding_amount_usd DECIMAL(12,2) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order SMALLINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_packages_code (package_code),
  CONSTRAINT chk_packages_amounts CHECK (purchase_price_usd > 0 AND funding_amount_usd > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Public receiving addresses only. NEVER store private keys or seed phrases.
-- Several active rows for one (asset, network) form an address pool; the least-loaded one is assigned per order.
CREATE TABLE payment_destinations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  asset ENUM('BTC','USDT') NOT NULL,
  network VARCHAR(32) NOT NULL,
  address VARCHAR(128) NOT NULL,
  label VARCHAR(64) NOT NULL DEFAULT '',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_destinations_addr (network, address),
  KEY idx_destinations_lookup (asset, network, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Payment orders / observed transfers
-- ---------------------------------------------------------------------------
CREATE TABLE payment_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_reference VARCHAR(24) NOT NULL,
  visitor_id BIGINT UNSIGNED NOT NULL,
  package_id BIGINT UNSIGNED NOT NULL,
  destination_id BIGINT UNSIGNED NOT NULL,
  asset ENUM('BTC','USDT') NOT NULL,
  network VARCHAR(32) NOT NULL,
  usd_price DECIMAL(12,2) NOT NULL,
  funding_amount_usd DECIMAL(12,2) NOT NULL COMMENT 'authorized funding amount snapshot at order time',
  exchange_rate DECIMAL(24,8) NOT NULL COMMENT 'USD per 1 unit of asset at quote time',
  exchange_rate_source VARCHAR(32) NOT NULL DEFAULT '',
  expected_amount DECIMAL(24,8) NOT NULL COMMENT 'rounded UP to the asset precision (BTC 8dp, USDT 6dp)',
  received_amount DECIMAL(24,8) NOT NULL DEFAULT 0,
  receiving_address_reference VARCHAR(64) NOT NULL DEFAULT '',
  receiving_address VARCHAR(128) NOT NULL COMMENT 'address snapshot at quote time',
  quote_created_at DATETIME NOT NULL,
  quote_expires_at DATETIME NOT NULL,
  status ENUM('pending_payment','payment_detected','confirming','confirmed','underpaid','overpaid','expired','failed','refunded') NOT NULL DEFAULT 'pending_payment',
  has_late_payment TINYINT(1) NOT NULL DEFAULT 0,
  last_checked_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  confirmed_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_orders_reference (public_reference),
  KEY idx_orders_visitor (visitor_id, created_at),
  KEY idx_orders_status (status, quote_expires_at),
  KEY idx_orders_destination (destination_id, status),
  KEY idx_orders_address (network, receiving_address, status),
  CONSTRAINT fk_orders_visitor FOREIGN KEY (visitor_id) REFERENCES anonymous_visitors (id) ON DELETE RESTRICT,
  CONSTRAINT fk_orders_package FOREIGN KEY (package_id) REFERENCES funding_packages (id) ON DELETE RESTRICT,
  CONSTRAINT fk_orders_destination FOREIGN KEY (destination_id) REFERENCES payment_destinations (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_status_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payment_order_id BIGINT UNSIGNED NOT NULL,
  from_status VARCHAR(24) NOT NULL,
  to_status VARCHAR(24) NOT NULL,
  reason VARCHAR(255) NOT NULL DEFAULT '',
  actor VARCHAR(40) NOT NULL DEFAULT 'system',
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_psh_order (payment_order_id, id),
  CONSTRAINT fk_psh_order FOREIGN KEY (payment_order_id) REFERENCES payment_orders (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per on-chain transfer we observed. The unique key stops one transfer being credited twice.
-- payment_order_id is NULL for unmatched transfers (kept for monitoring / manual review).
CREATE TABLE payment_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payment_order_id BIGINT UNSIGNED NULL,
  provider VARCHAR(32) NOT NULL,
  network VARCHAR(32) NOT NULL,
  transaction_reference VARCHAR(128) NOT NULL,
  transfer_index INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'vout index / log index: one tx can hold several transfers',
  asset_identifier VARCHAR(128) NOT NULL COMMENT 'BTC, or the token contract address',
  from_address VARCHAR(128) NULL,
  to_address VARCHAR(128) NOT NULL,
  amount_received DECIMAL(24,8) NOT NULL,
  confirmation_count INT UNSIGNED NOT NULL DEFAULT 0,
  verification_status ENUM('unconfirmed','confirmed') NOT NULL DEFAULT 'unconfirmed',
  match_status ENUM('matched','unmatched','review') NOT NULL DEFAULT 'matched',
  is_late TINYINT(1) NOT NULL DEFAULT 0,
  observed_at DATETIME NOT NULL,
  confirmed_at DATETIME NULL,
  provider_event_reference VARCHAR(128) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_payment_transfer (network, transaction_reference, transfer_index, asset_identifier),
  KEY idx_ptx_order (payment_order_id),
  KEY idx_ptx_match (match_status, observed_at),
  CONSTRAINT fk_ptx_order FOREIGN KEY (payment_order_id) REFERENCES payment_orders (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_reviews (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payment_order_id BIGINT UNSIGNED NOT NULL,
  admin_id BIGINT UNSIGNED NOT NULL,
  decision ENUM('approve','reject','refunded') NOT NULL,
  evidence_reference VARCHAR(190) NOT NULL DEFAULT '',
  reason VARCHAR(500) NOT NULL,
  verification_result VARCHAR(255) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_reviews_order (payment_order_id),
  CONSTRAINT fk_reviews_order FOREIGN KEY (payment_order_id) REFERENCES payment_orders (id) ON DELETE RESTRICT,
  CONSTRAINT fk_reviews_admin FOREIGN KEY (admin_id) REFERENCES admins (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE webhook_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  provider VARCHAR(32) NOT NULL,
  event_id VARCHAR(128) NOT NULL,
  payload_hash CHAR(64) NOT NULL,
  status ENUM('received','processed','ignored','failed') NOT NULL DEFAULT 'received',
  error VARCHAR(255) NULL,
  received_at DATETIME NOT NULL,
  processed_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_webhook_event (provider, event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Serial tokens
-- ---------------------------------------------------------------------------
CREATE TABLE serial_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  token_reference VARCHAR(24) NOT NULL,
  token_hash CHAR(64) NOT NULL COMMENT 'keyed HMAC-SHA256 of the 16-char token; key lives outside the DB',
  token_last4 CHAR(4) NOT NULL,
  package_id BIGINT UNSIGNED NOT NULL,
  funding_amount_usd DECIMAL(12,2) NOT NULL COMMENT 'amount this token authorizes; snapshot so later package edits never change issued tokens',
  payment_order_id BIGINT UNSIGNED NULL COMMENT 'NULL only for authorized administrative / promotional issuance',
  visitor_id BIGINT UNSIGNED NULL COMMENT 'owner; NULL for admin-issued tokens',
  issuance_source ENUM('purchase','administrative','promotional') NOT NULL DEFAULT 'purchase',
  issued_by_admin_id BIGINT UNSIGNED NULL,
  issuance_reason VARCHAR(255) NULL,
  status ENUM('active','reserved','redeemed','expired','revoked') NOT NULL DEFAULT 'active',
  revoked_reason VARCHAR(255) NULL,
  revoked_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  activated_at DATETIME NULL,
  reserved_at DATETIME NULL,
  redeemed_at DATETIME NULL,
  expires_at DATETIME NULL,
  revoked_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tokens_reference (token_reference),
  UNIQUE KEY uq_tokens_hash (token_hash),
  UNIQUE KEY uq_tokens_order (payment_order_id),
  KEY idx_tokens_status (status, expires_at),
  KEY idx_tokens_visitor (visitor_id, created_at),
  KEY idx_tokens_package (package_id, status),
  CONSTRAINT fk_tokens_package FOREIGN KEY (package_id) REFERENCES funding_packages (id) ON DELETE RESTRICT,
  CONSTRAINT fk_tokens_order FOREIGN KEY (payment_order_id) REFERENCES payment_orders (id) ON DELETE RESTRICT,
  CONSTRAINT fk_tokens_visitor FOREIGN KEY (visitor_id) REFERENCES anonymous_visitors (id) ON DELETE RESTRICT,
  CONSTRAINT fk_tokens_admin FOREIGN KEY (issued_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT,
  CONSTRAINT fk_tokens_revoker FOREIGN KEY (revoked_by_admin_id) REFERENCES admins (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Encrypted copy of purchased tokens so the purchaser (and only the purchaser) can re-reveal them.
-- Encryption key is derived from APP_KEY (outside the DB). Admin-issued tokens are never vaulted.
CREATE TABLE token_vault (
  token_id BIGINT UNSIGNED NOT NULL,
  ciphertext VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  last_revealed_at DATETIME NULL,
  reveal_count INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (token_id),
  CONSTRAINT fk_vault_token FOREIGN KEY (token_id) REFERENCES serial_tokens (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Funding
-- ---------------------------------------------------------------------------
-- Short-lived, server-side record of a validated (recipient, token) pair. The browser only holds an opaque ticket.
CREATE TABLE funding_intents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  visitor_id BIGINT UNSIGNED NOT NULL,
  serial_token_id BIGINT UNSIGNED NOT NULL,
  package_id BIGINT UNSIGNED NOT NULL,
  ticket_hash CHAR(64) NOT NULL,
  recipient_identifier_enc VARCHAR(512) NOT NULL,
  recipient_display VARCHAR(190) NOT NULL,
  recipient_ref VARCHAR(190) NOT NULL,
  amount_usd DECIMAL(12,2) NOT NULL,
  created_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  consumed_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_intents_ticket (ticket_hash),
  KEY idx_intents_expires (expires_at),
  CONSTRAINT fk_intents_visitor FOREIGN KEY (visitor_id) REFERENCES anonymous_visitors (id) ON DELETE RESTRICT,
  CONSTRAINT fk_intents_token FOREIGN KEY (serial_token_id) REFERENCES serial_tokens (id) ON DELETE RESTRICT,
  CONSTRAINT fk_intents_package FOREIGN KEY (package_id) REFERENCES funding_packages (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE funding_transactions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_reference VARCHAR(24) NOT NULL COMMENT 'also sent to the provider as client_reference / Idempotency-Key',
  visitor_id BIGINT UNSIGNED NOT NULL,
  recipient_identifier_enc VARCHAR(512) NOT NULL,
  recipient_display VARCHAR(190) NOT NULL COMMENT 'masked, safe to display',
  recipient_ref VARCHAR(190) NOT NULL COMMENT 'opaque id returned by CrowdSourc',
  package_id BIGINT UNSIGNED NOT NULL,
  serial_token_id BIGINT UNSIGNED NOT NULL,
  amount_usd DECIMAL(12,2) NOT NULL,
  idempotency_key VARCHAR(64) NOT NULL,
  external_transaction_reference VARCHAR(190) NULL,
  status ENUM('pending','submitting','processing','succeeded','failed','uncertain') NOT NULL DEFAULT 'pending',
  failure_code VARCHAR(64) NULL,
  failure_message VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  submitted_at DATETIME NULL,
  completed_at DATETIME NULL,
  last_reconciled_at DATETIME NULL,
  updated_at DATETIME NOT NULL,
  -- Non-NULL while this row "holds" its token. UNIQUE => at most one live-or-successful funding per token,
  -- which makes double-redemption and concurrent use impossible at the database level.
  blocking_token_id BIGINT UNSIGNED GENERATED ALWAYS AS (
    CASE WHEN status IN ('pending','submitting','processing','uncertain','succeeded') THEN serial_token_id ELSE NULL END
  ) STORED,
  PRIMARY KEY (id),
  UNIQUE KEY uq_funding_reference (public_reference),
  UNIQUE KEY uq_funding_idempotency (visitor_id, idempotency_key),
  UNIQUE KEY uq_funding_blocking_token (blocking_token_id),
  KEY idx_funding_visitor (visitor_id, created_at),
  KEY idx_funding_status (status, updated_at),
  KEY idx_funding_external (external_transaction_reference),
  CONSTRAINT fk_funding_visitor FOREIGN KEY (visitor_id) REFERENCES anonymous_visitors (id) ON DELETE RESTRICT,
  CONSTRAINT fk_funding_package FOREIGN KEY (package_id) REFERENCES funding_packages (id) ON DELETE RESTRICT,
  CONSTRAINT fk_funding_token FOREIGN KEY (serial_token_id) REFERENCES serial_tokens (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE funding_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  funding_transaction_id BIGINT UNSIGNED NOT NULL,
  attempt_number INT UNSIGNED NOT NULL,
  attempt_type ENUM('create','reconcile','manual_resolution') NOT NULL DEFAULT 'create',
  admin_id BIGINT UNSIGNED NULL,
  request_reference VARCHAR(64) NOT NULL,
  external_reference VARCHAR(190) NULL,
  status VARCHAR(32) NOT NULL,
  started_at DATETIME NOT NULL,
  completed_at DATETIME NULL,
  response_code VARCHAR(32) NULL,
  sanitized_response_metadata JSON NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_attempt_number (funding_transaction_id, attempt_number),
  CONSTRAINT fk_attempts_funding FOREIGN KEY (funding_transaction_id) REFERENCES funding_transactions (id) ON DELETE RESTRICT,
  CONSTRAINT fk_attempts_admin FOREIGN KEY (admin_id) REFERENCES admins (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Cross-cutting
-- ---------------------------------------------------------------------------
CREATE TABLE audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_type ENUM('visitor','admin','system','webhook') NOT NULL,
  actor_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(64) NOT NULL,
  resource_type VARCHAR(40) NOT NULL,
  resource_reference VARCHAR(64) NULL,
  request_reference VARCHAR(40) NULL,
  ip_hash CHAR(16) NULL,
  metadata JSON NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_audit_event (event_type, created_at),
  KEY idx_audit_resource (resource_type, resource_reference),
  KEY idx_audit_actor (actor_type, actor_id),
  KEY idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE system_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  setting_key VARCHAR(64) NOT NULL,
  setting_value TEXT NOT NULL,
  updated_by BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_settings_key (setting_key),
  CONSTRAINT fk_settings_admin FOREIGN KEY (updated_by) REFERENCES admins (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rate_limits (
  bucket_key CHAR(64) NOT NULL,
  window_start INT UNSIGNED NOT NULL,
  hits INT UNSIGNED NOT NULL DEFAULT 0,
  expires_at INT UNSIGNED NOT NULL,
  PRIMARY KEY (bucket_key, window_start),
  KEY idx_rate_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  visitor_id BIGINT UNSIGNED NOT NULL,
  type VARCHAR(40) NOT NULL,
  title VARCHAR(120) NOT NULL,
  body VARCHAR(255) NOT NULL DEFAULT '',
  resource_reference VARCHAR(40) NULL,
  created_at DATETIME NOT NULL,
  read_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_notifications_visitor (visitor_id, read_at, id),
  CONSTRAINT fk_notifications_visitor FOREIGN KEY (visitor_id) REFERENCES anonymous_visitors (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE schema_migrations (
  version VARCHAR(64) NOT NULL,
  applied_at DATETIME NOT NULL,
  PRIMARY KEY (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;