CREATE TABLE repository_document_reprocess_jobs (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  created_by_user_id CHAR(36) NULL,
  status_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  stage_code VARCHAR(60) NOT NULL DEFAULT 'queued',
  total_documents INT UNSIGNED NOT NULL DEFAULT 0,
  processed_documents INT UNSIGNED NOT NULL DEFAULT 0,
  succeeded_documents INT UNSIGNED NOT NULL DEFAULT 0,
  failed_documents INT UNSIGNED NOT NULL DEFAULT 0,
  extracted_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  synced_client_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  current_document_record_id CHAR(36) NULL,
  current_document_title VARCHAR(255) NULL,
  synthesis_status_code VARCHAR(40) NULL,
  synthesis_repository_item_id CHAR(36) NULL,
  synthesized_profile_fact_count INT UNSIGNED NOT NULL DEFAULT 0,
  synthesized_knowledge_fact_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_message LONGTEXT NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_repository_document_reprocess_jobs_law_firm (law_firm_id, created_at),
  KEY idx_repository_document_reprocess_jobs_client (client_id, created_at),
  KEY idx_repository_document_reprocess_jobs_case (case_id, created_at),
  KEY idx_repository_document_reprocess_jobs_status (law_firm_id, status_code, updated_at),
  CONSTRAINT fk_repository_document_reprocess_jobs_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_repository_document_reprocess_jobs_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_repository_document_reprocess_jobs_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_repository_document_reprocess_jobs_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_repository_document_reprocess_jobs_current_document
    FOREIGN KEY (current_document_record_id) REFERENCES document_records (id),
  CONSTRAINT fk_repository_document_reprocess_jobs_synthesis_repository_item
    FOREIGN KEY (synthesis_repository_item_id) REFERENCES repository_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE repository_document_reprocess_job_documents (
  id CHAR(36) NOT NULL,
  job_id CHAR(36) NOT NULL,
  document_record_id CHAR(36) NOT NULL,
  sort_order INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  document_type_code VARCHAR(50) NOT NULL,
  status_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  extracted_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  synced_client_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  ai_run_id CHAR(36) NULL,
  extraction_id CHAR(36) NULL,
  summary_text LONGTEXT NULL,
  extracted_preview_json JSON NULL,
  error_message LONGTEXT NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_repository_document_reprocess_job_documents_order (job_id, sort_order),
  UNIQUE KEY uq_repository_document_reprocess_job_documents_document (job_id, document_record_id),
  KEY idx_repository_document_reprocess_job_documents_job (job_id, status_code, sort_order),
  KEY idx_repository_document_reprocess_job_documents_ai_run (ai_run_id),
  KEY idx_repository_document_reprocess_job_documents_extraction (extraction_id),
  CONSTRAINT fk_repository_document_reprocess_job_documents_job
    FOREIGN KEY (job_id) REFERENCES repository_document_reprocess_jobs (id),
  CONSTRAINT fk_repository_document_reprocess_job_documents_document
    FOREIGN KEY (document_record_id) REFERENCES document_records (id),
  CONSTRAINT fk_repository_document_reprocess_job_documents_document_type
    FOREIGN KEY (document_type_code) REFERENCES document_types (code),
  CONSTRAINT fk_repository_document_reprocess_job_documents_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id),
  CONSTRAINT fk_repository_document_reprocess_job_documents_extraction
    FOREIGN KEY (extraction_id) REFERENCES document_extractions (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
