CREATE TABLE repository_folder_upload_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,
  fallback_document_type_code VARCHAR(50) NULL,
  status_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  stage_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  total_files INT UNSIGNED NOT NULL DEFAULT 0,
  processed_files INT UNSIGNED NOT NULL DEFAULT 0,
  succeeded_files INT UNSIGNED NOT NULL DEFAULT 0,
  failed_files 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,
  classification_ai_count INT UNSIGNED NOT NULL DEFAULT 0,
  classification_heuristic_count INT UNSIGNED NOT NULL DEFAULT 0,
  classification_fallback_count INT UNSIGNED NOT NULL DEFAULT 0,
  current_file_name VARCHAR(255) NULL,
  consolidation_status_code VARCHAR(40) NULL,
  consolidation_ai_run_id CHAR(36) NULL,
  consolidated_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_folder_upload_jobs_law_firm (law_firm_id, created_at),
  KEY idx_repository_folder_upload_jobs_client (client_id, created_at),
  KEY idx_repository_folder_upload_jobs_case (case_id, created_at),
  KEY idx_repository_folder_upload_jobs_status (law_firm_id, status_code, updated_at),
  CONSTRAINT fk_repository_folder_upload_jobs_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_repository_folder_upload_jobs_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_repository_folder_upload_jobs_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_repository_folder_upload_jobs_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_repository_folder_upload_jobs_fallback_type
    FOREIGN KEY (fallback_document_type_code) REFERENCES document_types (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE repository_folder_upload_job_files (
  id CHAR(36) NOT NULL,
  job_id CHAR(36) NOT NULL,
  sort_order INT UNSIGNED NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  relative_path VARCHAR(1024) NULL,
  title VARCHAR(255) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  staging_storage_provider VARCHAR(50) NULL,
  staging_storage_bucket VARCHAR(191) NULL,
  staging_object_key VARCHAR(512) NULL,
  staging_storage_region VARCHAR(100) NULL,
  staging_original_file_name VARCHAR(255) NULL,
  status_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  document_type_code VARCHAR(50) NULL,
  classification_source VARCHAR(40) NULL,
  classification_confidence DECIMAL(5,4) NULL,
  classification_reason LONGTEXT NULL,
  repository_item_id CHAR(36) NULL,
  file_id CHAR(36) NULL,
  document_record_id CHAR(36) NULL,
  extracted_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  synced_client_field_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),
  UNIQUE KEY uq_repository_folder_upload_job_files_order (job_id, sort_order),
  KEY idx_repository_folder_upload_job_files_job (job_id, status_code, sort_order),
  KEY idx_repository_folder_upload_job_files_repository_item (repository_item_id),
  KEY idx_repository_folder_upload_job_files_file (file_id),
  KEY idx_repository_folder_upload_job_files_document_record (document_record_id),
  CONSTRAINT fk_repository_folder_upload_job_files_job
    FOREIGN KEY (job_id) REFERENCES repository_folder_upload_jobs (id),
  CONSTRAINT fk_repository_folder_upload_job_files_document_type
    FOREIGN KEY (document_type_code) REFERENCES document_types (code),
  CONSTRAINT fk_repository_folder_upload_job_files_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id),
  CONSTRAINT fk_repository_folder_upload_job_files_file
    FOREIGN KEY (file_id) REFERENCES files (id),
  CONSTRAINT fk_repository_folder_upload_job_files_document_record
    FOREIGN KEY (document_record_id) REFERENCES document_records (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
