PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS scan_events (
  id TEXT PRIMARY KEY NOT NULL,
  created_at TEXT NOT NULL,
  score INTEGER NOT NULL CHECK (score >= 0 AND score <= 100),
  band TEXT NOT NULL CHECK (band IN ('low', 'medium', 'high')),
  locale TEXT,
  timezone TEXT,
  timezone_offset_minutes INTEGER,
  user_agent_family TEXT,
  signals_json TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_scan_events_created_at
  ON scan_events(created_at);

CREATE INDEX IF NOT EXISTS idx_scan_events_band
  ON scan_events(band);

CREATE TABLE IF NOT EXISTS signal_hits (
  event_id TEXT NOT NULL,
  signal_id TEXT NOT NULL,
  contribution INTEGER NOT NULL CHECK (contribution >= 0 AND contribution <= 100),
  verdict TEXT NOT NULL CHECK (verdict IN ('low', 'medium', 'high')),
  raw TEXT,
  PRIMARY KEY (event_id, signal_id),
  FOREIGN KEY (event_id) REFERENCES scan_events(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_signal_hits_signal_id
  ON signal_hits(signal_id);

CREATE TABLE IF NOT EXISTS fingerprint_visits (
  id TEXT PRIMARY KEY NOT NULL,
  scan_event_id TEXT NOT NULL,
  created_at TEXT NOT NULL,
  visitor_key TEXT NOT NULL,
  fingerprint_hash TEXT NOT NULL,
  component_hashes_json TEXT NOT NULL,
  component_count INTEGER NOT NULL CHECK (component_count >= 0 AND component_count <= 96),
  FOREIGN KEY (scan_event_id) REFERENCES scan_events(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_fingerprint_visits_visitor_key
  ON fingerprint_visits(visitor_key, created_at);

CREATE INDEX IF NOT EXISTS idx_fingerprint_visits_fingerprint_hash
  ON fingerprint_visits(fingerprint_hash, created_at);