-- Social Runtime Crawler — prototype schema (§36). Raw observations are append-only; canonical tables are derived.
CREATE TABLE IF NOT EXISTS sessions (
  session_id     text PRIMARY KEY,
  platform       text NOT NULL,
  account_alias  text NOT NULL,
  mode           text,
  goal           text,
  started_at     timestamptz NOT NULL DEFAULT now(),
  ended_at       timestamptz,
  health         text,
  stats          jsonb
);

-- Every event, raw (§37). Never updated.
CREATE TABLE IF NOT EXISTS observations (
  event_id    text PRIMARY KEY,
  session_id  text NOT NULL REFERENCES sessions(session_id) ON DELETE CASCADE,
  platform    text NOT NULL,
  event_type  text NOT NULL,
  step        integer,
  ts          timestamptz NOT NULL,
  payload     jsonb NOT NULL,
  provenance  jsonb,
  discovered_via jsonb
);
CREATE INDEX IF NOT EXISTS observations_session_idx ON observations(session_id, step);
CREATE INDEX IF NOT EXISTS observations_type_idx ON observations(event_type);

CREATE TABLE IF NOT EXISTS entities (
  fingerprint   text PRIMARY KEY,
  platform      text NOT NULL,
  entity_type   text NOT NULL,
  platform_id   text,
  url           text,
  name          text,
  text_excerpt  text,
  author        text,
  metrics       jsonb,
  media         jsonb,
  fields        jsonb NOT NULL DEFAULT '{}'::jsonb,   -- field → {value, provenance[]}  (§38 evidence model)
  confidence    real,
  first_seen    timestamptz NOT NULL DEFAULT now(),
  last_seen     timestamptz NOT NULL DEFAULT now(),
  seen_count    integer NOT NULL DEFAULT 1,
  first_session text,
  canonical_id  text
);
CREATE INDEX IF NOT EXISTS entities_platform_type_idx ON entities(platform, entity_type);

CREATE TABLE IF NOT EXISTS entity_observations (
  id          bigserial PRIMARY KEY,
  fingerprint text NOT NULL REFERENCES entities(fingerprint) ON DELETE CASCADE,
  session_id  text NOT NULL,
  step        integer,
  ts          timestamptz NOT NULL,
  surfaces    text[] NOT NULL,
  snapshot    jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS entity_observations_fp_idx ON entity_observations(fingerprint);

CREATE TABLE IF NOT EXISTS media (
  fingerprint        text PRIMARY KEY,
  platform           text NOT NULL,
  media_type         text NOT NULL,
  platform_media_id  text,
  page_url           text,
  url                text,
  title              text,
  author             text,
  duration_s         real,
  width              integer,
  height             integer,
  thumbnail_url      text,
  delivery           jsonb,
  frames             text[],
  provenance         jsonb,
  first_seen         timestamptz NOT NULL DEFAULT now(),
  last_seen          timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS relationships (
  id         bigserial PRIMARY KEY,
  session_id text NOT NULL,
  from_fp    text NOT NULL,
  to_fp      text NOT NULL,
  rel_type   text NOT NULL,
  step       integer,
  UNIQUE (from_fp, to_fp, rel_type)
);

CREATE TABLE IF NOT EXISTS actions (
  id           bigserial PRIMARY KEY,
  session_id   text NOT NULL REFERENCES sessions(session_id) ON DELETE CASCADE,
  step         integer NOT NULL,
  action_id    text,
  action_type  text NOT NULL,
  label        text,
  target_url   text,
  planner      text,
  expected_gain real,
  novelty      real,
  relevance    real,
  reason       text,
  scores       jsonb,
  before_state jsonb,
  after_state  jsonb,
  success      boolean,
  error        text,
  duration_ms  integer,
  ts           timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS actions_session_idx ON actions(session_id, step);

CREATE TABLE IF NOT EXISTS network_responses (
  request_id    text PRIMARY KEY,
  session_id    text NOT NULL,
  step          integer,
  url           text NOT NULL,
  method        text,
  status        integer,
  kind          text,
  content_type  text,
  body_size     integer,
  shape_hash    text,
  hostname      text,
  path_pattern  text,
  graphql_operation text,
  entity_count  integer,
  entity_types  text[],
  confidence    real,
  ts            timestamptz NOT NULL
);
CREATE INDEX IF NOT EXISTS network_responses_shape_idx ON network_responses(shape_hash);

CREATE TABLE IF NOT EXISTS schema_patterns (
  platform      text NOT NULL,
  shape_hash    text NOT NULL,
  fingerprint   jsonb NOT NULL,
  schema        jsonb NOT NULL,
  sample_url    text,
  observed_count integer NOT NULL DEFAULT 1,
  first_seen    timestamptz NOT NULL DEFAULT now(),
  last_seen     timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (platform, shape_hash)
);

CREATE TABLE IF NOT EXISTS feed_items (
  id             bigserial PRIMARY KEY,
  session_id     text NOT NULL,
  step           integer,
  page_url       text,
  page_type      text,
  feed_position  integer,
  fingerprint    text NOT NULL,
  entity_type    text,
  visible        boolean,
  ts             timestamptz NOT NULL
);

CREATE TABLE IF NOT EXISTS connector_versions (
  id          bigserial PRIMARY KEY,
  platform    text NOT NULL,
  compiled_at timestamptz NOT NULL DEFAULT now(),
  confidence  integer,
  summary     jsonb,
  manifest_path text
);

CREATE TABLE IF NOT EXISTS crawl_jobs (
  job_id      text PRIMARY KEY,
  session_id  text,
  platform    text NOT NULL,
  mode        text,
  goal        text,
  budget      jsonb,
  status      text,
  created_at  timestamptz NOT NULL DEFAULT now(),
  finished_at timestamptz,
  result      jsonb
);