-- Cache Claude extraction responses per attachment/invoice to avoid repeated API calls
CREATE TABLE IF NOT EXISTS ap_ai_extractions (
  extraction_id        BIGINT PRIMARY KEY AUTO_INCREMENT,
  invoice_id           BIGINT NULL,
  attachment_id        BIGINT NULL,
  source_path          VARCHAR(512) NOT NULL,
  source_sha256        CHAR(64) NOT NULL,
  provider             VARCHAR(32) NOT NULL DEFAULT 'anthropic',
  model                VARCHAR(128) NOT NULL,
  prompt_version       VARCHAR(32) NOT NULL DEFAULT 'v1',
  request_payload      LONGTEXT NULL,
  raw_response_text    LONGTEXT NULL,
  extraction_json      LONGTEXT NOT NULL,
  vendor_match_json    LONGTEXT NULL,
  created_by           VARCHAR(64) NOT NULL,
  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_invoice (invoice_id),
  KEY idx_attachment (attachment_id),
  KEY idx_hash (source_sha256),
  KEY idx_model (model),
  UNIQUE KEY uk_cache_lookup (source_sha256, model, prompt_version)
);
