CREATE TABLE IF NOT EXISTS analytics_traffic_daily (
  day DATE NOT NULL,
  source_group TEXT NOT NULL,
  source_name TEXT NOT NULL,
  device_type TEXT NOT NULL,
  os_name TEXT NOT NULL,
  keyword TEXT NOT NULL DEFAULT '',
  page_views INTEGER NOT NULL DEFAULT 0,
  visitors INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (day, source_group, source_name, device_type, os_name, keyword)
);

CREATE INDEX IF NOT EXISTS analytics_traffic_daily_day_idx
  ON analytics_traffic_daily (day DESC);

CREATE INDEX IF NOT EXISTS analytics_traffic_daily_source_idx
  ON analytics_traffic_daily (source_group, source_name);

ALTER TABLE analytics_daily_visitors
  ADD COLUMN IF NOT EXISTS source_group TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS source_name TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS device_type TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS os_name TEXT NOT NULL DEFAULT '',
  ADD COLUMN IF NOT EXISTS keyword TEXT NOT NULL DEFAULT '';

ALTER TABLE analytics_daily_visitors
  DROP CONSTRAINT IF EXISTS analytics_daily_visitors_scope_check;

ALTER TABLE analytics_daily_visitors
  ADD CONSTRAINT analytics_daily_visitors_scope_check CHECK (scope IN ('site', 'post', 'page', 'traffic'));

CREATE UNIQUE INDEX IF NOT EXISTS analytics_daily_visitors_traffic_uidx
  ON analytics_daily_visitors (
    day,
    visitor_hash,
    source_group,
    source_name,
    device_type,
    os_name,
    keyword
  )
  WHERE scope = 'traffic';