db/schema.sql

-- Minimal schema for GEO runs/tasks/evidence/mentions + brand mapping.
-- Dialect: PostgreSQL (recommended). Adjust types if using MySQL.

create table if not exists dim_brand_pairs (
  version text not null,
  effective_at timestamptz not null default now(),
  main_brand text not null,
  competitor_brand text not null,
  industry text not null,
  priority integer not null default 0,
  primary key (version, main_brand)
);

create table if not exists runs (
  run_id text primary key,
  trigger text not null, -- scheduled | manual
  schedule_id text,
  config_version text not null,
  config_snapshot_url text,
  params_json jsonb not null default '{}'::jsonb,
  started_at timestamptz not null default now(),
  finished_at timestamptz
);

create table if not exists questions (
  question_id text not null,
  run_id text not null references runs(run_id) on delete cascade,
  question_text text not null,
  entity_set_json jsonb not null default '[]'::jsonb,
  primary key (run_id, question_id)
);

create table if not exists tasks (
  task_id text primary key,
  run_id text not null references runs(run_id) on delete cascade,
  question_id text not null,
  site text not null,
  profile_id text not null,
  status text not null, -- running | success | failed | needs_human
  reason text,
  handoff_url text,
  started_at timestamptz not null default now(),
  finished_at timestamptz
);

create index if not exists tasks_run_site_status_idx on tasks (run_id, site, status);

create table if not exists task_artifacts (
  task_id text primary key references tasks(task_id) on delete cascade,
  answer_text text not null,
  citations_json jsonb not null default '[]'::jsonb,
  screenshots_json jsonb not null default '[]'::jsonb,
  raw_html_url text,
  log_url text
);

create table if not exists mentions (
  task_id text not null references tasks(task_id) on delete cascade,
  entity_name text not null,
  entity_type text not null, -- main | competitor
  recommended boolean not null,
  rank integer,
  first_index integer,
  citations_json jsonb not null default '[]'::jsonb,
  primary key (task_id, entity_name)
);

create index if not exists mentions_entity_type_idx on mentions (entity_name, entity_type);

-- Optional: daily pre-aggregation for dashboards.
create table if not exists fact_geo_daily (
  date date not null,
  site text not null,
  main_brand text not null,
  competitor_brand text not null,
  recommended_rate double precision not null,
  top1_share double precision not null,
  avg_rank double precision,
  win_rate double precision,
  absent_vs_competitor_rate double precision,
  sample_n integer not null,
  primary key (date, site, main_brand)
);

create index if not exists fact_geo_daily_site_date_idx on fact_geo_daily (site, date);