INSERT IGNORE INTO ai_run_types (code, name, description, billable_default) VALUES
  ('workflow_blueprint_analysis', 'Workflow Blueprint Analysis', 'Analyze workflow templates and generate document checklists plus unique intake questions', 1);

CREATE TABLE IF NOT EXISTS workflow_template_ai_analyses (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  workflow_template_id CHAR(36) NOT NULL,
  ai_run_id CHAR(36) NULL,
  status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
  generated_analysis_json JSON NULL,
  edited_analysis_json JSON NULL,
  error_message TEXT NULL,
  generated_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_workflow_template_ai_analyses_scope (law_firm_id, workflow_template_id),
  KEY idx_workflow_template_ai_analyses_template (workflow_template_id),
  KEY idx_workflow_template_ai_analyses_law_firm (law_firm_id, status_code, updated_at),
  KEY idx_workflow_template_ai_analyses_ai_run (ai_run_id),
  CONSTRAINT fk_workflow_template_ai_analyses_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_workflow_template_ai_analyses_workflow
    FOREIGN KEY (workflow_template_id) REFERENCES workflow_templates (id),
  CONSTRAINT fk_workflow_template_ai_analyses_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
