-- Migration 004: Analytics Events Table
-- Captures website behavioral data for the pluggable analytics module.
-- Completely separate from product and message data tables.
-- Indexed for query performance on event_type, visitor_id, session_id, and timestamp.

CREATE TABLE IF NOT EXISTS analytics_events (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  event_type  TEXT NOT NULL,
  page_url    TEXT,
  visitor_id  TEXT NOT NULL,
  session_id  TEXT NOT NULL,
  timestamp   DATETIME DEFAULT CURRENT_TIMESTAMP,
  metadata    TEXT
);

CREATE INDEX IF NOT EXISTS idx_analytics_event_type   ON analytics_events(event_type);
CREATE INDEX IF NOT EXISTS idx_analytics_visitor_id   ON analytics_events(visitor_id);
CREATE INDEX IF NOT EXISTS idx_analytics_session_id   ON analytics_events(session_id);
CREATE INDEX IF NOT EXISTS idx_analytics_timestamp    ON analytics_events(timestamp);