db_schema.sql

-- アクセス解析パイプライン SQLite スキーマ
-- 全テーブルに site カラムを持たせ、複合主キーで INSERT OR REPLACE（upsert）を効かせる。
-- すべて CREATE TABLE IF NOT EXISTS なので、何度流しても安全（冪等）。

-- 1. ページ別（ローリング期間の合計・上位N）。snapshot_date = 取得実行日
CREATE TABLE IF NOT EXISTS page_metrics (
  site TEXT NOT NULL,
  snapshot_date TEXT NOT NULL,      -- ローリング集計を取った日 (YYYY-MM-DD)
  page_path TEXT NOT NULL,          -- 正規化済みパス
  page_title TEXT,
  page_views INTEGER,
  active_users INTEGER,
  engagement_duration REAL,         -- 秒
  engagement_rate REAL,
  bounce_rate REAL,
  PRIMARY KEY (site, snapshot_date, page_path)
);

-- 2. 流入チャネル（日次）
CREATE TABLE IF NOT EXISTS channel_metrics (
  site TEXT NOT NULL,
  date TEXT NOT NULL,
  channel_group TEXT NOT NULL,
  source TEXT NOT NULL,
  medium TEXT NOT NULL,
  sessions INTEGER,
  active_users INTEGER,
  engaged_sessions INTEGER,
  PRIMARY KEY (site, date, channel_group, source, medium)
);

-- 3. ランディングページ（日次）
CREATE TABLE IF NOT EXISTS landing_metrics (
  site TEXT NOT NULL,
  date TEXT NOT NULL,
  landing_page TEXT NOT NULL,       -- 正規化済みパス
  sessions INTEGER,
  bounce_rate REAL,
  engagement_rate REAL,
  PRIMARY KEY (site, date, landing_page)
);

-- 4. 新規 vs リピーター（日次）
CREATE TABLE IF NOT EXISTS user_type_metrics (
  site TEXT NOT NULL,
  date TEXT NOT NULL,
  user_type TEXT NOT NULL,          -- new / returning / (not set) など
  active_users INTEGER,
  sessions INTEGER,
  PRIMARY KEY (site, date, user_type)
);

-- 5. デバイス・地域（日次）
CREATE TABLE IF NOT EXISTS device_geo_metrics (
  site TEXT NOT NULL,
  date TEXT NOT NULL,
  device_category TEXT NOT NULL,
  country TEXT NOT NULL,
  region TEXT NOT NULL,
  active_users INTEGER,
  sessions INTEGER,
  PRIMARY KEY (site, date, device_category, country, region)
);

-- 6. 時間別（日次）
CREATE TABLE IF NOT EXISTS hourly_metrics (
  site TEXT NOT NULL,
  date TEXT NOT NULL,
  hour INTEGER NOT NULL,            -- 0-23
  day_of_week INTEGER,              -- 0-6（GA4 の dayOfWeek。0=日曜）
  sessions INTEGER,
  active_users INTEGER,
  page_views INTEGER,
  PRIMARY KEY (site, date, hour)
);

-- 7. イベント / キーイベント（日次）
CREATE TABLE IF NOT EXISTS event_metrics (
  site TEXT NOT NULL,
  date TEXT NOT NULL,
  event_name TEXT NOT NULL,
  key_events REAL,
  event_count INTEGER,
  total_revenue REAL,
  PRIMARY KEY (site, date, event_name)
);

-- 8. アフィリエイトクリック（日次・ページ別×遷移先ホスト別）。任意。
-- 設定ファイルで ga4_affiliate_event を指定したサイトだけ行が入る。
-- サイト側が送るイベントパラメータ article_path / link_host を、GA4 のカスタム
-- ディメンション経由で取得する。article_path は正規化済み（page_metrics /
-- gsc_page_metrics と JOIN 可能）。
-- 注意: カスタムディメンションを登録する前の日付は "(not set)" になる
--       （GA4 は登録前のイベントに遡って値を付けない）。
CREATE TABLE IF NOT EXISTS affiliate_click_metrics (
  site TEXT NOT NULL,
  date TEXT NOT NULL,
  article_path TEXT NOT NULL,       -- クリック元ページの正規化済みパス。無しは (not set)
  link_host TEXT NOT NULL,          -- 遷移先ホスト名。無しは (not set)
  event_count INTEGER,
  PRIMARY KEY (site, date, article_path, link_host)
);

-- Search Console: クエリ別（日次）
CREATE TABLE IF NOT EXISTS gsc_query_metrics (
  site TEXT NOT NULL,
  date TEXT NOT NULL,
  query TEXT NOT NULL,
  clicks INTEGER,
  impressions INTEGER,
  ctr REAL,
  position REAL,
  PRIMARY KEY (site, date, query)
);

-- Search Console: ページ別（日次）。page_path は正規化済み（GA4 と JOIN 可能）
CREATE TABLE IF NOT EXISTS gsc_page_metrics (
  site TEXT NOT NULL,
  date TEXT NOT NULL,
  page_path TEXT NOT NULL,          -- 正規化済みパス
  clicks INTEGER,
  impressions INTEGER,
  ctr REAL,
  position REAL,
  PRIMARY KEY (site, date, page_path)
);

-- Search Console: ページ×クエリ別（日次）。ページごとの流入キーワードが分かる。
-- page_path は正規化済み（page_metrics / gsc_page_metrics と JOIN 可能）
CREATE TABLE IF NOT EXISTS gsc_page_query_metrics (
  site TEXT NOT NULL,
  date TEXT NOT NULL,
  page_path TEXT NOT NULL,          -- 正規化済みパス
  query TEXT NOT NULL,
  clicks INTEGER,
  impressions INTEGER,
  ctr REAL,
  position REAL,
  PRIMARY KEY (site, date, page_path, query)
);

-- URL Inspection API: インデックス状況（既定は週次）。sitemap の各URLを1本ずつ検査。
-- page_path は正規化済み（GA4 / GSC と JOIN 可能）。値は API が返す英語の文字列のまま。
CREATE TABLE IF NOT EXISTS inspect_metrics (
  site             TEXT NOT NULL,
  inspected_date   TEXT NOT NULL,   -- 検査実行日 (YYYY-MM-DD)
  page_path        TEXT NOT NULL,   -- 正規化済みパス
  coverage_state   TEXT,            -- 例: Submitted and indexed / Crawled - currently not indexed
  verdict          TEXT,            -- PASS / NEUTRAL / FAIL
  robots_state     TEXT,            -- 例: ALLOWED
  indexing_state   TEXT,            -- 例: INDEXING_ALLOWED
  last_crawl_time  TEXT,            -- API の lastCrawlTime（ISO8601/UTC の文字列のまま保存）
  google_canonical TEXT,
  user_canonical   TEXT,
  PRIMARY KEY (site, inspected_date, page_path)
);

-- バッチ実行ログ（サイト×実行日×ソースごとの成否）
CREATE TABLE IF NOT EXISTS batch_runs (
  site TEXT NOT NULL,
  run_date TEXT NOT NULL,
  source TEXT NOT NULL,             -- ga4 / ga4_affiliate / gsc / url_inspection / *_backfill
  status TEXT NOT NULL,             -- success / failed / skipped
  rows_written INTEGER,
  message TEXT,
  finished_at TEXT,
  PRIMARY KEY (site, run_date, source)
);