CREATE TABLE client_data_structure_nodes (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  parent_id CHAR(36) NULL,
  node_type VARCHAR(20) NOT NULL,
  field_key VARCHAR(120) NOT NULL,
  label VARCHAR(255) NOT NULL,
  description TEXT NULL,
  ai_instructions TEXT NULL,
  data_type VARCHAR(50) NULL,
  document_type_code VARCHAR(50) NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 0,
  is_repeatable TINYINT(1) NOT NULL DEFAULT 0,
  is_conditional TINYINT(1) NOT NULL DEFAULT 0,
  conditional_prompt VARCHAR(500) NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 1,
  created_by_user_id CHAR(36) 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_client_data_structure_nodes_scope_key (law_firm_id, field_key),
  KEY idx_client_data_structure_nodes_parent (law_firm_id, parent_id, sort_order),
  KEY idx_client_data_structure_nodes_created_by (created_by_user_id),
  CONSTRAINT fk_client_data_structure_nodes_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_client_data_structure_nodes_parent
    FOREIGN KEY (parent_id) REFERENCES client_data_structure_nodes (id)
    ON DELETE CASCADE,
  CONSTRAINT fk_client_data_structure_nodes_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
