CREATE TABLE specialized_form_generation_jobs (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  form_template_id CHAR(36) NOT NULL,
  form_name VARCHAR(255) NOT NULL,
  agent_code VARCHAR(100) NOT NULL,
  agent_prompt LONGTEXT NOT NULL,
  created_by_user_id CHAR(36) NULL,
  status_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  stage_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  progress_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
  detail_text VARCHAR(500) NULL,
  filled_count INT UNSIGNED NOT NULL DEFAULT 0,
  unresolved_count INT UNSIGNED NOT NULL DEFAULT 0,
  unresolved_fields_json LONGTEXT NULL,
  generated_document_id CHAR(36) NULL,
  repository_item_id CHAR(36) NULL,
  file_id CHAR(36) NULL,
  file_name VARCHAR(255) 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),
  KEY idx_specialized_form_generation_jobs_law_firm (law_firm_id, created_at),
  KEY idx_specialized_form_generation_jobs_client (client_id, created_at),
  KEY idx_specialized_form_generation_jobs_status (law_firm_id, status_code, updated_at),
  KEY idx_specialized_form_generation_jobs_agent (law_firm_id, agent_code, updated_at),
  CONSTRAINT fk_specialized_form_generation_jobs_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_specialized_form_generation_jobs_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_specialized_form_generation_jobs_template
    FOREIGN KEY (form_template_id) REFERENCES form_templates (id),
  CONSTRAINT fk_specialized_form_generation_jobs_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_specialized_form_generation_jobs_generated_document
    FOREIGN KEY (generated_document_id) REFERENCES generated_documents (id),
  CONSTRAINT fk_specialized_form_generation_jobs_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id),
  CONSTRAINT fk_specialized_form_generation_jobs_file
    FOREIGN KEY (file_id) REFERENCES files (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
