CREATE TABLE IF NOT EXISTS form_agent_memories (
  id CHAR(36) NOT NULL PRIMARY KEY,
  law_firm_id CHAR(36) NOT NULL,
  form_template_id CHAR(36) NULL,
  agent_code VARCHAR(80) NOT NULL,
  client_id CHAR(36) NULL,
  case_id CHAR(36) NULL,
  memory_scope VARCHAR(20) NOT NULL DEFAULT 'global',
  memory_type VARCHAR(50) NOT NULL,
  summary_text TEXT NOT NULL,
  detail_json JSON NULL,
  success_score DECIMAL(5,4) NULL,
  coverage_ratio DECIMAL(6,4) NULL,
  created_by_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_form_agent_memories_agent (law_firm_id, agent_code, created_at),
  KEY idx_form_agent_memories_client (client_id, created_at),
  KEY idx_form_agent_memories_case (case_id, created_at),
  KEY idx_form_agent_memories_template (form_template_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS form_global_entity_snapshots (
  id CHAR(36) NOT NULL PRIMARY KEY,
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  snapshot_hash CHAR(64) NOT NULL,
  snapshot_json JSON NOT NULL,
  source_summary_json JSON NULL,
  created_by_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_form_global_entity_snapshots_client (client_id, created_at),
  KEY idx_form_global_entity_snapshots_case (case_id, created_at),
  KEY idx_form_global_entity_snapshots_hash (snapshot_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS immigration_case_analyses (
  id CHAR(36) NOT NULL PRIMARY KEY,
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  case_type_code VARCHAR(50) NOT NULL,
  stage_code VARCHAR(50) NOT NULL,
  detected_forms_json JSON NOT NULL,
  missing_forms_json JSON NOT NULL,
  recommended_next_steps_json JSON NOT NULL,
  evidence_json JSON NULL,
  created_by_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_immigration_case_analyses_client (client_id, created_at),
  KEY idx_immigration_case_analyses_case (case_id, created_at),
  KEY idx_immigration_case_analyses_type (case_type_code, stage_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS form_engine_runs (
  id CHAR(36) NOT NULL PRIMARY KEY,
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  run_status VARCHAR(30) NOT NULL DEFAULT 'running',
  target_coverage DECIMAL(6,4) NOT NULL DEFAULT 0.8000,
  selected_agents_json JSON NOT NULL,
  case_analysis_json JSON NULL,
  summary_json JSON NULL,
  created_by_user_id CHAR(36) NULL,
  started_at DATETIME NOT 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,
  KEY idx_form_engine_runs_client (client_id, created_at),
  KEY idx_form_engine_runs_case (case_id, created_at),
  KEY idx_form_engine_runs_status (run_status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS form_engine_run_agents (
  id CHAR(36) NOT NULL PRIMARY KEY,
  form_engine_run_id CHAR(36) NOT NULL,
  agent_code VARCHAR(80) NOT NULL,
  form_template_id CHAR(36) NOT NULL,
  metrics_json JSON NOT NULL,
  result_json JSON NULL,
  generated_document_id CHAR(36) NULL,
  repository_item_id CHAR(36) NULL,
  file_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_form_engine_run_agents_run (form_engine_run_id, created_at),
  KEY idx_form_engine_run_agents_agent (agent_code, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
