CREATE TABLE IF NOT EXISTS site_analytics_daily (
  day DATE PRIMARY KEY,
  page_views INTEGER NOT NULL DEFAULT 0,
  visitors INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS post_analytics_daily (
  day DATE NOT NULL,
  post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  views INTEGER NOT NULL DEFAULT 0,
  reads INTEGER NOT NULL DEFAULT 0,
  visitors INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (day, post_id)
);

CREATE INDEX IF NOT EXISTS post_analytics_daily_day_idx
  ON post_analytics_daily (day DESC);

CREATE TABLE IF NOT EXISTS analytics_daily_visitors (
  id BIGSERIAL PRIMARY KEY,
  day DATE NOT NULL,
  scope TEXT NOT NULL,
  post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
  visitor_hash TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT analytics_daily_visitors_scope_check CHECK (scope IN ('site', 'post'))
);

CREATE UNIQUE INDEX IF NOT EXISTS analytics_daily_visitors_site_uidx
  ON analytics_daily_visitors (day, visitor_hash)
  WHERE scope = 'site';

CREATE UNIQUE INDEX IF NOT EXISTS analytics_daily_visitors_post_uidx
  ON analytics_daily_visitors (day, post_id, visitor_hash)
  WHERE scope = 'post';