CREATE TABLE IF NOT EXISTS va_sites (
  id INT AUTO_INCREMENT PRIMARY KEY,
  site_key VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(190) NOT NULL,
  domain VARCHAR(190) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS va_visitors (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  site_id INT NOT NULL,
  visitor_uuid CHAR(36) NOT NULL,
  fingerprint_hash CHAR(64) NULL,
  first_ip VARCHAR(45) NULL,
  last_ip VARCHAR(45) NULL,
  user_agent TEXT NULL,
  browser VARCHAR(80) NULL,
  os VARCHAR(80) NULL,
  device VARCHAR(80) NULL,
  language VARCHAR(50) NULL,
  timezone_name VARCHAR(100) NULL,
  screen VARCHAR(50) NULL,
  country VARCHAR(100) NULL,
  city VARCHAR(100) NULL,
  isp VARCHAR(190) NULL,
  first_seen DATETIME NOT NULL,
  last_seen DATETIME NOT NULL,
  UNIQUE KEY uniq_site_visitor (site_id, visitor_uuid),
  KEY idx_site_last_seen (site_id, last_seen),
  KEY idx_ip (last_ip),
  CONSTRAINT fk_va_visitors_site FOREIGN KEY (site_id) REFERENCES va_sites(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS va_sessions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  site_id INT NOT NULL,
  visitor_id BIGINT NOT NULL,
  session_uuid CHAR(36) NOT NULL,
  ip VARCHAR(45) NULL,
  referrer TEXT NULL,
  landing_page TEXT NULL,
  current_page TEXT NULL,
  started_at DATETIME NOT NULL,
  ended_at DATETIME NULL,
  last_activity DATETIME NOT NULL,
  duration_seconds INT NOT NULL DEFAULT 0,
  max_scroll TINYINT UNSIGNED NOT NULL DEFAULT 0,
  page_views_count INT NOT NULL DEFAULT 0,
  clicks_count INT NOT NULL DEFAULT 0,
  is_bot TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_session (session_uuid),
  KEY idx_site_started (site_id, started_at),
  KEY idx_visitor (visitor_id),
  CONSTRAINT fk_va_sessions_site FOREIGN KEY (site_id) REFERENCES va_sites(id) ON DELETE CASCADE,
  CONSTRAINT fk_va_sessions_visitor FOREIGN KEY (visitor_id) REFERENCES va_visitors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS va_events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  site_id INT NOT NULL,
  visitor_id BIGINT NOT NULL,
  session_id BIGINT NOT NULL,
  event_type ENUM('pageview','click','scroll','heartbeat','leave') NOT NULL,
  page_url TEXT NULL,
  page_title VARCHAR(255) NULL,
  target_url TEXT NULL,
  target_text VARCHAR(255) NULL,
  target_tag VARCHAR(50) NULL,
  scroll_percent TINYINT UNSIGNED NULL,
  duration_seconds INT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_site_event_time (site_id, event_type, created_at),
  KEY idx_session_time (session_id, created_at),
  CONSTRAINT fk_va_events_site FOREIGN KEY (site_id) REFERENCES va_sites(id) ON DELETE CASCADE,
  CONSTRAINT fk_va_events_visitor FOREIGN KEY (visitor_id) REFERENCES va_visitors(id) ON DELETE CASCADE,
  CONSTRAINT fk_va_events_session FOREIGN KEY (session_id) REFERENCES va_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;