-- Loom schema. Every extracted value is anchored to a source document, an
-- extraction method, a locator and a timestamp. There is no path in this
-- schema for an attribute value to exist without provenance.

CREATE TABLE IF NOT EXISTS schema_version (
  version    INTEGER PRIMARY KEY,
  applied_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS setting (
  key        TEXT PRIMARY KEY,
  value      TEXT,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- ---------------------------------------------------------------- suppliers

CREATE TABLE IF NOT EXISTS supplier (
  id                   INTEGER PRIMARY KEY,
  slug                 TEXT NOT NULL UNIQUE,
  name                 TEXT NOT NULL,
  homepage_url         TEXT,
  status               TEXT NOT NULL DEFAULT 'active'
                         CHECK (status IN ('active','paused','archived')),
  allowed_hosts        TEXT NOT NULL DEFAULT '[]',   -- json array of hostnames
  seed_urls            TEXT NOT NULL DEFAULT '[]',   -- json array
  url_patterns         TEXT NOT NULL DEFAULT '{}',   -- json {pdp,series,listing,catalog,datasheet,exclude}
  rate_limit_ms        INTEGER,
  max_pages            INTEGER,
  max_depth            INTEGER,
  robots_policy        TEXT NOT NULL DEFAULT 'enforce'
                         CHECK (robots_policy IN ('enforce','override')),
  robots_override_note TEXT,                          -- required when policy = override
  data_agreement       TEXT,                          -- free text: contract / permission of record
  notes                TEXT,
  created_at           TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at           TEXT NOT NULL DEFAULT (datetime('now'))
);

-- --------------------------------------------------------------- acquisition

CREATE TABLE IF NOT EXISTS crawl_run (
  id               INTEGER PRIMARY KEY,
  supplier_id      INTEGER NOT NULL REFERENCES supplier(id) ON DELETE CASCADE,
  mode             TEXT NOT NULL DEFAULT 'full'
                     CHECK (mode IN ('discover','extract','full','refresh')),
  status           TEXT NOT NULL DEFAULT 'queued'
                     CHECK (status IN ('queued','running','done','failed','cancelled')),
  config           TEXT NOT NULL DEFAULT '{}',
  started_at       TEXT,
  finished_at      TEXT,
  pages_fetched    INTEGER NOT NULL DEFAULT 0,
  pages_skipped    INTEGER NOT NULL DEFAULT 0,
  pages_failed     INTEGER NOT NULL DEFAULT 0,
  docs_new         INTEGER NOT NULL DEFAULT 0,
  docs_changed     INTEGER NOT NULL DEFAULT 0,
  docs_unchanged   INTEGER NOT NULL DEFAULT 0,
  products_new     INTEGER NOT NULL DEFAULT 0,
  products_updated INTEGER NOT NULL DEFAULT 0,
  attrs_extracted  INTEGER NOT NULL DEFAULT 0,
  attrs_mapped     INTEGER NOT NULL DEFAULT 0,
  attrs_unmapped   INTEGER NOT NULL DEFAULT 0,
  assets_found     INTEGER NOT NULL DEFAULT 0,
  issues_found     INTEGER NOT NULL DEFAULT 0,
  error            TEXT,
  actor            TEXT NOT NULL DEFAULT 'operator'
);

CREATE TABLE IF NOT EXISTS crawl_log (
  id      INTEGER PRIMARY KEY,
  run_id  INTEGER NOT NULL REFERENCES crawl_run(id) ON DELETE CASCADE,
  at      TEXT NOT NULL DEFAULT (datetime('now')),
  level   TEXT NOT NULL DEFAULT 'info' CHECK (level IN ('info','warn','error')),
  stage   TEXT,
  url     TEXT,
  message TEXT NOT NULL,
  meta    TEXT
);
CREATE INDEX IF NOT EXISTS ix_crawl_log_run ON crawl_log(run_id, id);

CREATE TABLE IF NOT EXISTS crawl_queue (
  id            INTEGER PRIMARY KEY,
  run_id        INTEGER NOT NULL REFERENCES crawl_run(id) ON DELETE CASCADE,
  url           TEXT NOT NULL,
  depth         INTEGER NOT NULL DEFAULT 0,
  doc_type_hint TEXT,
  state         TEXT NOT NULL DEFAULT 'pending'
                  CHECK (state IN ('pending','active','done','error','skipped')),
  reason        TEXT,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_queue_run_url ON crawl_queue(run_id, url);
CREATE INDEX IF NOT EXISTS ix_queue_state ON crawl_queue(run_id, state, depth);

CREATE TABLE IF NOT EXISTS robots_cache (
  host       TEXT PRIMARY KEY,
  status     INTEGER,
  body       TEXT,
  fetched_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- The provenance anchor. One row per retrieved URL.
CREATE TABLE IF NOT EXISTS source_document (
  id              INTEGER PRIMARY KEY,
  supplier_id     INTEGER NOT NULL REFERENCES supplier(id) ON DELETE CASCADE,
  url             TEXT NOT NULL,
  canonical_url   TEXT,
  doc_type        TEXT NOT NULL DEFAULT 'unknown'
                    CHECK (doc_type IN ('pdp','series','listing','catalog','datasheet','drawing','other','unknown')),
  depth           INTEGER NOT NULL DEFAULT 0,
  discovered_from INTEGER REFERENCES source_document(id) ON DELETE SET NULL,
  http_status     INTEGER,
  content_type    TEXT,
  byte_size       INTEGER,
  content_sha256  TEXT,
  blob_path       TEXT,
  etag            TEXT,
  last_modified   TEXT,
  title           TEXT,
  lang            TEXT,
  robots_allowed  INTEGER NOT NULL DEFAULT 1,
  fetch_error     TEXT,
  fetch_ms        INTEGER,
  fetched_at      TEXT,
  first_run_id    INTEGER REFERENCES crawl_run(id) ON DELETE SET NULL,
  last_run_id     INTEGER REFERENCES crawl_run(id) ON DELETE SET NULL,
  revision        INTEGER NOT NULL DEFAULT 1,
  created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_doc_supplier_url ON source_document(supplier_id, url);
CREATE INDEX IF NOT EXISTS ix_doc_type ON source_document(supplier_id, doc_type);
CREATE INDEX IF NOT EXISTS ix_doc_hash ON source_document(content_sha256);

-- ------------------------------------------------------------------ catalog

CREATE TABLE IF NOT EXISTS product_series (
  id                 INTEGER PRIMARY KEY,
  supplier_id        INTEGER NOT NULL REFERENCES supplier(id) ON DELETE CASCADE,
  series_code        TEXT NOT NULL,
  name               TEXT,
  description        TEXT,
  source_document_id INTEGER REFERENCES source_document(id) ON DELETE SET NULL,
  created_at         TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at         TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_series_supplier_code ON product_series(supplier_id, series_code);

CREATE TABLE IF NOT EXISTS product (
  id                   INTEGER PRIMARY KEY,
  supplier_id          INTEGER NOT NULL REFERENCES supplier(id) ON DELETE CASCADE,
  supplier_part_number TEXT NOT NULL,
  series_id            INTEGER REFERENCES product_series(id) ON DELETE SET NULL,
  name                 TEXT,
  description_short    TEXT,
  description_long     TEXT,
  primary_image_url    TEXT,
  product_url          TEXT,
  source_document_id   INTEGER REFERENCES source_document(id) ON DELETE SET NULL,
  lifecycle_status     TEXT NOT NULL DEFAULT 'unknown',
  review_status        TEXT NOT NULL DEFAULT 'draft'
                         CHECK (review_status IN ('draft','in_review','approved','rejected','published')),
  completeness         REAL NOT NULL DEFAULT 0,
  attr_total           INTEGER NOT NULL DEFAULT 0,
  attr_accepted        INTEGER NOT NULL DEFAULT 0,
  attr_unmapped        INTEGER NOT NULL DEFAULT 0,
  issue_error          INTEGER NOT NULL DEFAULT 0,
  issue_warn           INTEGER NOT NULL DEFAULT 0,
  first_run_id         INTEGER REFERENCES crawl_run(id) ON DELETE SET NULL,
  last_run_id          INTEGER REFERENCES crawl_run(id) ON DELETE SET NULL,
  created_at           TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at           TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_product_supplier_spn ON product(supplier_id, supplier_part_number);
CREATE INDEX IF NOT EXISTS ix_product_review ON product(review_status);
CREATE INDEX IF NOT EXISTS ix_product_series ON product(series_id);

-- ---------------------------------------------------------- the dictionary

CREATE TABLE IF NOT EXISTS attribute_definition (
  id             INTEGER PRIMARY KEY,
  key            TEXT NOT NULL UNIQUE,
  label          TEXT NOT NULL,
  group_name     TEXT NOT NULL DEFAULT 'General',
  data_type      TEXT NOT NULL DEFAULT 'string'
                   CHECK (data_type IN ('string','text','number','boolean','enum','range','url')),
  unit_family    TEXT,                     -- length | current | voltage | ...
  canonical_unit TEXT,
  allowed_values TEXT,                     -- json array for enum
  min_value      REAL,
  max_value      REAL,
  pattern        TEXT,
  pim_field      TEXT,                     -- target field(s) in the PIM
  description    TEXT,
  example        TEXT,
  is_required    INTEGER NOT NULL DEFAULT 0,
  status         TEXT NOT NULL DEFAULT 'active'
                   CHECK (status IN ('active','deprecated')),
  version        INTEGER NOT NULL DEFAULT 1,
  owner          TEXT,
  created_at     TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at     TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS ix_attrdef_group ON attribute_definition(group_name, label);

-- Curated crosswalk from what a supplier calls a thing to what we call it.
-- Nothing maps itself: rows are created by a person or by an exact-match
-- suggestion a person confirms.
CREATE TABLE IF NOT EXISTS attribute_mapping (
  id               INTEGER PRIMARY KEY,
  supplier_id      INTEGER REFERENCES supplier(id) ON DELETE CASCADE, -- NULL = global
  normalized_label TEXT NOT NULL,
  attribute_key    TEXT NOT NULL REFERENCES attribute_definition(key) ON DELETE CASCADE,
  unit_hint        TEXT,
  transform        TEXT,
  note             TEXT,
  times_applied    INTEGER NOT NULL DEFAULT 0,
  created_by       TEXT NOT NULL DEFAULT 'operator',
  created_at       TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_mapping_scope_label
  ON attribute_mapping(COALESCE(supplier_id, 0), normalized_label);

-- Raw supplier labels seen but not yet mapped. This is the human work queue.
CREATE TABLE IF NOT EXISTS unmapped_label (
  id               INTEGER PRIMARY KEY,
  supplier_id      INTEGER NOT NULL REFERENCES supplier(id) ON DELETE CASCADE,
  normalized_label TEXT NOT NULL,
  raw_label        TEXT NOT NULL,
  sample_value     TEXT,
  occurrences      INTEGER NOT NULL DEFAULT 1,
  suggestion       TEXT,                   -- attribute_key candidate, never auto-applied
  suggestion_score REAL,
  dismissed        INTEGER NOT NULL DEFAULT 0,
  first_seen       TEXT NOT NULL DEFAULT (datetime('now')),
  last_seen        TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_unmapped_supplier_label
  ON unmapped_label(supplier_id, normalized_label);

-- --------------------------------------------------------------- the values

CREATE TABLE IF NOT EXISTS product_attribute (
  id                 INTEGER PRIMARY KEY,
  product_id         INTEGER NOT NULL REFERENCES product(id) ON DELETE CASCADE,
  attribute_key      TEXT REFERENCES attribute_definition(key) ON DELETE SET NULL,
  raw_label          TEXT NOT NULL,
  normalized_label   TEXT NOT NULL,
  raw_value          TEXT NOT NULL,

  value_text         TEXT,
  value_num          REAL,
  value_num_max      REAL,
  value_unit         TEXT,
  value_bool         INTEGER,
  qualifier          TEXT,      -- exact | min | max | typ | nominal | range | approx
  normalized         INTEGER NOT NULL DEFAULT 0,
  norm_note          TEXT,

  source_document_id INTEGER NOT NULL REFERENCES source_document(id) ON DELETE CASCADE,
  extraction_method  TEXT NOT NULL,
  locator            TEXT,      -- css path, table cell, or pdf page:line
  snippet            TEXT,      -- verbatim source text the value came from
  page_no            INTEGER,
  confidence         REAL NOT NULL DEFAULT 0,

  review_state       TEXT NOT NULL DEFAULT 'unreviewed'
                       CHECK (review_state IN ('unreviewed','accepted','rejected','edited')),
  reviewed_by        TEXT,
  reviewed_at        TEXT,
  review_note        TEXT,
  original_value     TEXT,      -- retained verbatim when a reviewer edits

  run_id             INTEGER REFERENCES crawl_run(id) ON DELETE SET NULL,
  created_at         TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at         TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_pattr_product_label_doc
  ON product_attribute(product_id, normalized_label, source_document_id);
CREATE INDEX IF NOT EXISTS ix_pattr_product ON product_attribute(product_id);
CREATE INDEX IF NOT EXISTS ix_pattr_key ON product_attribute(attribute_key);
CREATE INDEX IF NOT EXISTS ix_pattr_review ON product_attribute(review_state);

-- One label can be read from several documents. This view defines which
-- reading is "the" value: a reviewer's decision outranks trust, trust outranks
-- recency. Everything else is retained and visible as an alternative source.
CREATE VIEW IF NOT EXISTS v_attribute_ranked AS
SELECT
  pa.*,
  ROW_NUMBER() OVER (
    PARTITION BY pa.product_id, pa.normalized_label
    ORDER BY
      CASE pa.review_state
        WHEN 'edited'     THEN 0
        WHEN 'accepted'   THEN 1
        WHEN 'unreviewed' THEN 2
        ELSE 3
      END,
      pa.confidence DESC,
      pa.updated_at DESC,
      pa.id DESC
  ) AS source_rank,
  COUNT(*) OVER (PARTITION BY pa.product_id, pa.normalized_label) AS source_count
FROM product_attribute pa;

CREATE TABLE IF NOT EXISTS asset (
  id                 INTEGER PRIMARY KEY,
  supplier_id        INTEGER NOT NULL REFERENCES supplier(id) ON DELETE CASCADE,
  product_id         INTEGER REFERENCES product(id) ON DELETE CASCADE,
  series_id          INTEGER REFERENCES product_series(id) ON DELETE CASCADE,
  kind               TEXT NOT NULL DEFAULT 'other'
                       CHECK (kind IN ('image','datasheet','catalog','drawing','cad','certificate','manual','other')),
  url                TEXT NOT NULL,
  title              TEXT,
  source_document_id INTEGER REFERENCES source_document(id) ON DELETE SET NULL,
  mime               TEXT,
  byte_size          INTEGER,
  sha256             TEXT,
  blob_path          TEXT,
  downloaded         INTEGER NOT NULL DEFAULT 0,
  fetch_error        TEXT,
  review_state       TEXT NOT NULL DEFAULT 'unreviewed'
                       CHECK (review_state IN ('unreviewed','accepted','rejected')),
  created_at         TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS ux_asset_scope_url
  ON asset(supplier_id, COALESCE(product_id, 0), url);
CREATE INDEX IF NOT EXISTS ix_asset_product ON asset(product_id);

CREATE TABLE IF NOT EXISTS validation_issue (
  id            INTEGER PRIMARY KEY,
  product_id    INTEGER NOT NULL REFERENCES product(id) ON DELETE CASCADE,
  attribute_key TEXT,
  attribute_id  INTEGER REFERENCES product_attribute(id) ON DELETE CASCADE,
  severity      TEXT NOT NULL DEFAULT 'warn' CHECK (severity IN ('error','warn','info')),
  code          TEXT NOT NULL,
  message       TEXT NOT NULL,
  run_id        INTEGER REFERENCES crawl_run(id) ON DELETE SET NULL,
  created_at    TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS ix_issue_product ON validation_issue(product_id, severity);

-- Human-authored, per-supplier deterministic extraction recipes. Highest trust
-- tier because a person wrote and verified the selector.
CREATE TABLE IF NOT EXISTS extraction_rule (
  id          INTEGER PRIMARY KEY,
  supplier_id INTEGER NOT NULL REFERENCES supplier(id) ON DELETE CASCADE,
  doc_type    TEXT NOT NULL DEFAULT 'pdp',
  name        TEXT NOT NULL,
  kind        TEXT NOT NULL DEFAULT 'css' CHECK (kind IN ('css','css_pairs','regex','jsonld')),
  selector    TEXT NOT NULL,
  target      TEXT NOT NULL,   -- field:<product column> | attr:<attribute_key> | pairs
  attr_of     TEXT,            -- read an element attribute instead of text
  transform   TEXT,
  priority    INTEGER NOT NULL DEFAULT 100,
  enabled     INTEGER NOT NULL DEFAULT 1,
  note        TEXT,
  created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS ix_rule_supplier ON extraction_rule(supplier_id, doc_type, priority);

CREATE TABLE IF NOT EXISTS audit_log (
  id          INTEGER PRIMARY KEY,
  at          TEXT NOT NULL DEFAULT (datetime('now')),
  actor       TEXT NOT NULL DEFAULT 'operator',
  action      TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id   TEXT,
  summary     TEXT,
  before      TEXT,
  after       TEXT
);
CREATE INDEX IF NOT EXISTS ix_audit_entity ON audit_log(entity_type, entity_id, id);

CREATE TABLE IF NOT EXISTS export_batch (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  format      TEXT NOT NULL DEFAULT 'csv' CHECK (format IN ('csv','json')),
  supplier_id INTEGER REFERENCES supplier(id) ON DELETE SET NULL,
  filter      TEXT NOT NULL DEFAULT '{}',
  gate        TEXT NOT NULL DEFAULT '{}',
  row_count   INTEGER NOT NULL DEFAULT 0,
  value_count INTEGER NOT NULL DEFAULT 0,
  path        TEXT,
  manifest    TEXT,
  created_by  TEXT NOT NULL DEFAULT 'operator',
  created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);
