SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- -----------------------------------------------------------------------------
-- Core tenant and access control
-- -----------------------------------------------------------------------------

CREATE TABLE law_firms (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  name VARCHAR(255) NOT NULL,
  legal_name VARCHAR(255) NULL,
  slug VARCHAR(100) NOT NULL,
  timezone VARCHAR(64) NOT NULL DEFAULT 'America/New_York',
  default_locale VARCHAR(16) NOT NULL DEFAULT 'en-US',
  status VARCHAR(30) NOT NULL DEFAULT 'active',
  billing_email VARCHAR(255) NULL,
  phone VARCHAR(50) NULL,
  website VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_law_firms_slug (slug),
  KEY idx_law_firms_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE offices (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(50) NOT NULL,
  timezone VARCHAR(64) NOT NULL DEFAULT 'America/New_York',
  phone VARCHAR(50) NULL,
  email VARCHAR(255) NULL,
  address_line1 VARCHAR(255) NULL,
  address_line2 VARCHAR(255) NULL,
  city VARCHAR(100) NULL,
  state_region VARCHAR(100) NULL,
  postal_code VARCHAR(30) NULL,
  country_code CHAR(2) NULL,
  is_headquarters TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_offices_law_firm_code (law_firm_id, code),
  KEY idx_offices_law_firm (law_firm_id),
  CONSTRAINT fk_offices_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE roles (
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  is_system_role TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
  code VARCHAR(100) NOT NULL,
  module_name VARCHAR(100) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (code),
  KEY idx_permissions_module (module_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
  role_code VARCHAR(50) NOT NULL,
  permission_code VARCHAR(100) NOT NULL,
  granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (role_code, permission_code),
  CONSTRAINT fk_role_permissions_role
    FOREIGN KEY (role_code) REFERENCES roles (code),
  CONSTRAINT fk_role_permissions_permission
    FOREIGN KEY (permission_code) REFERENCES permissions (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NULL,
  primary_office_id CHAR(36) NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  display_name VARCHAR(150) NULL,
  avatar_url VARCHAR(2048) NULL,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NULL,
  auth_provider VARCHAR(50) NOT NULL DEFAULT 'local',
  phone VARCHAR(50) NULL,
  locale VARCHAR(16) NOT NULL DEFAULT 'en-US',
  timezone VARCHAR(64) NOT NULL DEFAULT 'America/New_York',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_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 DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  UNIQUE KEY uq_users_law_firm_email (law_firm_id, email),
  KEY idx_users_law_firm (law_firm_id),
  KEY idx_users_primary_office (primary_office_id),
  KEY idx_users_active (law_firm_id, is_active),
  CONSTRAINT fk_users_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_users_primary_office
    FOREIGN KEY (primary_office_id) REFERENCES offices (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_offices (
  user_id CHAR(36) NOT NULL,
  office_id CHAR(36) NOT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, office_id),
  KEY idx_user_offices_office (office_id),
  CONSTRAINT fk_user_offices_user
    FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_user_offices_office
    FOREIGN KEY (office_id) REFERENCES offices (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_roles (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  user_id CHAR(36) NOT NULL,
  role_code VARCHAR(50) NOT NULL,
  office_id CHAR(36) NULL,
  scope_type VARCHAR(30) NOT NULL DEFAULT 'law_firm',
  assigned_by_user_id CHAR(36) NULL,
  assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_user_roles_user (user_id),
  KEY idx_user_roles_role (role_code),
  KEY idx_user_roles_office (office_id),
  CONSTRAINT fk_user_roles_user
    FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_user_roles_role
    FOREIGN KEY (role_code) REFERENCES roles (code),
  CONSTRAINT fk_user_roles_office
    FOREIGN KEY (office_id) REFERENCES offices (id),
  CONSTRAINT fk_user_roles_assigned_by
    FOREIGN KEY (assigned_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE workspace_memberships (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  membership_role VARCHAR(30) NOT NULL DEFAULT 'member',
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  created_by_user_id CHAR(36) NULL,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_workspace_memberships_law_firm_user (law_firm_id, user_id),
  KEY idx_workspace_memberships_user (user_id),
  KEY idx_workspace_memberships_law_firm_role (law_firm_id, membership_role),
  KEY idx_workspace_memberships_created_by (created_by_user_id),
  CONSTRAINT fk_workspace_memberships_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_workspace_memberships_user
    FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_workspace_memberships_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE workspace_invitations (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  invitee_email VARCHAR(255) NOT NULL,
  invitee_user_id CHAR(36) NULL,
  invited_by_user_id CHAR(36) NULL,
  status_code VARCHAR(30) NOT NULL DEFAULT 'pending',
  responded_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_workspace_invitations_law_firm_email (law_firm_id, invitee_email),
  KEY idx_workspace_invitations_invitee_status (invitee_user_id, status_code),
  KEY idx_workspace_invitations_law_firm_status (law_firm_id, status_code),
  KEY idx_workspace_invitations_invited_by (invited_by_user_id),
  CONSTRAINT fk_workspace_invitations_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_workspace_invitations_invitee_user
    FOREIGN KEY (invitee_user_id) REFERENCES users (id),
  CONSTRAINT fk_workspace_invitations_invited_by
    FOREIGN KEY (invited_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Lookup catalogs
-- -----------------------------------------------------------------------------

CREATE TABLE case_statuses (
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 100,
  is_closed_status TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE repository_item_types (
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  supports_file TINYINT(1) NOT NULL DEFAULT 0,
  supports_text TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE channels (
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  is_external TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE document_types (
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  category_code VARCHAR(50) NOT NULL,
  is_identity_document TINYINT(1) NOT NULL DEFAULT 0,
  is_expirable TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (code),
  KEY idx_document_types_category (category_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_run_types (
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  billable_default TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE questionnaire_statuses (
  code VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  is_terminal TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Clients and cases
-- -----------------------------------------------------------------------------

CREATE TABLE clients (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  primary_office_id CHAR(36) NULL,
  client_number VARCHAR(50) NULL,
  first_name VARCHAR(100) NOT NULL,
  middle_name VARCHAR(100) NULL,
  last_name VARCHAR(100) NOT NULL,
  preferred_name VARCHAR(100) NULL,
  date_of_birth DATE NULL,
  gender VARCHAR(30) NULL,
  email VARCHAR(255) NULL,
  phone VARCHAR(50) NULL,
  preferred_language VARCHAR(30) NULL,
  country_of_birth CHAR(2) NULL,
  country_of_citizenship CHAR(2) NULL,
  immigration_status VARCHAR(100) NULL,
  intake_channel_code VARCHAR(50) NULL,
  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,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_clients_law_firm_number (law_firm_id, client_number),
  KEY idx_clients_law_firm (law_firm_id),
  KEY idx_clients_email (law_firm_id, email),
  KEY idx_clients_phone (law_firm_id, phone),
  CONSTRAINT fk_clients_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_clients_primary_office
    FOREIGN KEY (primary_office_id) REFERENCES offices (id),
  CONSTRAINT fk_clients_intake_channel
    FOREIGN KEY (intake_channel_code) REFERENCES channels (code),
  CONSTRAINT fk_clients_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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;

CREATE TABLE client_addresses (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  address_type VARCHAR(30) NOT NULL,
  address_line1 VARCHAR(255) NOT NULL,
  address_line2 VARCHAR(255) NULL,
  city VARCHAR(100) NOT NULL,
  state_region VARCHAR(100) NULL,
  postal_code VARCHAR(30) NULL,
  country_code CHAR(2) NOT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  valid_from DATE NULL,
  valid_to DATE 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_client_addresses_client (client_id),
  KEY idx_client_addresses_type (client_id, address_type),
  CONSTRAINT fk_client_addresses_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_client_addresses_client
    FOREIGN KEY (client_id) REFERENCES clients (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE client_identifiers (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  identifier_type VARCHAR(50) NOT NULL,
  identifier_value VARCHAR(191) NOT NULL,
  issuing_country_code CHAR(2) NULL,
  issued_at DATE NULL,
  expires_at DATE NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  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_identifiers_value (law_firm_id, identifier_type, identifier_value),
  KEY idx_client_identifiers_client (client_id),
  KEY idx_client_identifiers_expiration (expires_at),
  CONSTRAINT fk_client_identifiers_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_client_identifiers_client
    FOREIGN KEY (client_id) REFERENCES clients (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cases (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  office_id CHAR(36) NULL,
  client_id CHAR(36) NOT NULL,
  case_number VARCHAR(50) NOT NULL,
  title VARCHAR(255) NOT NULL,
  case_type_code VARCHAR(50) NOT NULL,
  case_subtype_code VARCHAR(50) NULL,
  responsible_attorney_id CHAR(36) NULL,
  responsible_paralegal_id CHAR(36) NULL,
  status_code VARCHAR(50) NOT NULL,
  priority_code VARCHAR(30) NOT NULL DEFAULT 'normal',
  opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  target_filing_at DATETIME NULL,
  closed_at DATETIME NULL,
  description TEXT NULL,
  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,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_cases_law_firm_number (law_firm_id, case_number),
  KEY idx_cases_client (client_id),
  KEY idx_cases_status (law_firm_id, status_code),
  KEY idx_cases_type (law_firm_id, case_type_code, case_subtype_code),
  KEY idx_cases_attorney (responsible_attorney_id),
  KEY idx_cases_paralegal (responsible_paralegal_id),
  CONSTRAINT fk_cases_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_cases_office
    FOREIGN KEY (office_id) REFERENCES offices (id),
  CONSTRAINT fk_cases_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_cases_attorney
    FOREIGN KEY (responsible_attorney_id) REFERENCES users (id),
  CONSTRAINT fk_cases_paralegal
    FOREIGN KEY (responsible_paralegal_id) REFERENCES users (id),
  CONSTRAINT fk_cases_status
    FOREIGN KEY (status_code) REFERENCES case_statuses (code),
  CONSTRAINT fk_cases_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE case_timeline_events (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  case_id CHAR(36) NOT NULL,
  event_type VARCHAR(50) NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  due_at DATETIME NULL,
  occurred_at DATETIME NULL,
  actor_user_id CHAR(36) NULL,
  metadata_json JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_case_timeline_case (case_id, due_at, occurred_at),
  KEY idx_case_timeline_actor (actor_user_id),
  CONSTRAINT fk_case_timeline_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_case_timeline_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_case_timeline_actor
    FOREIGN KEY (actor_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE related_parties (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  relation_type VARCHAR(50) NOT NULL,
  entity_type VARCHAR(20) NOT NULL DEFAULT 'person',
  first_name VARCHAR(100) NULL,
  last_name VARCHAR(100) NULL,
  company_name VARCHAR(255) NULL,
  preferred_name VARCHAR(100) NULL,
  email VARCHAR(255) NULL,
  phone VARCHAR(50) NULL,
  date_of_birth DATE NULL,
  country_of_birth CHAR(2) NULL,
  country_of_citizenship CHAR(2) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_related_parties_client (client_id),
  KEY idx_related_parties_case (case_id),
  KEY idx_related_parties_relation (law_firm_id, relation_type),
  CONSTRAINT fk_related_parties_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_related_parties_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_related_parties_case
    FOREIGN KEY (case_id) REFERENCES cases (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- AI configuration and execution
-- -----------------------------------------------------------------------------

CREATE TABLE law_firm_ai_credentials (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  provider VARCHAR(50) NOT NULL,
  credential_name VARCHAR(100) NOT NULL,
  encrypted_api_key VARBINARY(4096) NOT NULL COMMENT 'Ciphertext only; never plaintext',
  encryption_key_version VARCHAR(50) NOT NULL,
  key_last4 CHAR(4) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  rotated_from_credential_id CHAR(36) NULL,
  deactivated_at DATETIME NULL,
  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),
  KEY idx_ai_credentials_law_firm (law_firm_id, provider, is_active),
  KEY idx_ai_credentials_default (law_firm_id, provider, is_default),
  KEY idx_ai_credentials_rotated_from (rotated_from_credential_id),
  CONSTRAINT fk_ai_credentials_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_ai_credentials_rotated_from
    FOREIGN KEY (rotated_from_credential_id) REFERENCES law_firm_ai_credentials (id),
  CONSTRAINT fk_ai_credentials_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE law_firm_ai_settings (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  default_provider VARCHAR(50) NOT NULL DEFAULT 'openai',
  default_model VARCHAR(100) NOT NULL DEFAULT 'gpt-5.4',
  active_credential_id CHAR(36) NULL,
  rate_limit_per_minute INT NULL,
  monthly_budget DECIMAL(12,2) NULL,
  alert_threshold DECIMAL(5,2) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  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_ai_settings_law_firm (law_firm_id),
  KEY idx_ai_settings_active_credential (active_credential_id),
  CONSTRAINT fk_ai_settings_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_ai_settings_active_credential
    FOREIGN KEY (active_credential_id) REFERENCES law_firm_ai_credentials (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_runs (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  client_id CHAR(36) NULL,
  ai_provider VARCHAR(50) NOT NULL,
  ai_model VARCHAR(100) NOT NULL,
  api_key_reference_id CHAR(36) NOT NULL,
  ai_run_type_code VARCHAR(50) NOT NULL,
  status VARCHAR(50) NOT NULL,
  input_tokens INT NOT NULL DEFAULT 0,
  output_tokens INT NOT NULL DEFAULT 0,
  estimated_cost DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  prompt_template_version VARCHAR(50) NULL,
  request_checksum CHAR(64) NULL,
  error_message TEXT NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ai_runs_law_firm_created (law_firm_id, created_at),
  KEY idx_ai_runs_case (case_id, created_at),
  KEY idx_ai_runs_client (client_id, created_at),
  KEY idx_ai_runs_type_status (ai_run_type_code, status),
  KEY idx_ai_runs_credential (api_key_reference_id, created_at),
  CONSTRAINT fk_ai_runs_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_ai_runs_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_ai_runs_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_ai_runs_credential
    FOREIGN KEY (api_key_reference_id) REFERENCES law_firm_ai_credentials (id),
  CONSTRAINT fk_ai_runs_type
    FOREIGN KEY (ai_run_type_code) REFERENCES ai_run_types (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Unified repository, communications and files
-- -----------------------------------------------------------------------------

CREATE TABLE repository_items (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NULL,
  case_id CHAR(36) NULL,
  item_type_code VARCHAR(50) NOT NULL,
  channel_code VARCHAR(50) NULL,
  source_entity_type VARCHAR(50) NULL,
  source_entity_id CHAR(36) NULL,
  direction_code VARCHAR(20) NULL,
  subject VARCHAR(255) NULL,
  body_text LONGTEXT NULL,
  summary_text TEXT NULL,
  metadata_json JSON NULL,
  authored_by_user_id CHAR(36) NULL,
  created_by_user_id CHAR(36) NULL,
  external_reference VARCHAR(191) NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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_items_law_firm_occurred (law_firm_id, occurred_at),
  KEY idx_repository_items_client (client_id, occurred_at),
  KEY idx_repository_items_case (case_id, occurred_at),
  KEY idx_repository_items_type (item_type_code, occurred_at),
  KEY idx_repository_items_channel (channel_code, occurred_at),
  KEY idx_repository_items_external_reference (external_reference),
  CONSTRAINT fk_repository_items_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_repository_items_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_repository_items_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_repository_items_type
    FOREIGN KEY (item_type_code) REFERENCES repository_item_types (code),
  CONSTRAINT fk_repository_items_channel
    FOREIGN KEY (channel_code) REFERENCES channels (code),
  CONSTRAINT fk_repository_items_authored_by
    FOREIGN KEY (authored_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_repository_items_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE conversations (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  channel_code VARCHAR(50) NOT NULL,
  subject VARCHAR(255) NULL,
  participants_json JSON NULL,
  external_thread_id VARCHAR(191) NULL,
  started_at DATETIME NULL,
  ended_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_conversations_client (client_id),
  KEY idx_conversations_case (case_id),
  KEY idx_conversations_channel (channel_code),
  CONSTRAINT fk_conversations_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_conversations_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_conversations_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_conversations_channel
    FOREIGN KEY (channel_code) REFERENCES channels (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE files (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NULL,
  case_id CHAR(36) NULL,
  repository_item_id CHAR(36) NOT NULL,
  storage_provider VARCHAR(50) NOT NULL,
  storage_bucket VARCHAR(191) NOT NULL,
  object_key VARCHAR(512) NOT NULL,
  storage_region VARCHAR(100) NULL,
  original_file_name VARCHAR(255) NOT NULL,
  stored_file_name VARCHAR(255) NULL,
  mime_type VARCHAR(100) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  checksum_sha256 CHAR(64) NULL,
  is_encrypted TINYINT(1) NOT NULL DEFAULT 0,
  uploaded_by_user_id CHAR(36) NULL,
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_files_repository_item (repository_item_id),
  UNIQUE KEY uq_files_storage_object (storage_provider, storage_bucket, object_key),
  KEY idx_files_client (client_id),
  KEY idx_files_case (case_id),
  KEY idx_files_uploaded_by (uploaded_by_user_id),
  CONSTRAINT fk_files_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_files_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_files_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_files_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id),
  CONSTRAINT fk_files_uploaded_by
    FOREIGN KEY (uploaded_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE messages (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  conversation_id CHAR(36) NOT NULL,
  repository_item_id CHAR(36) NOT NULL,
  sender_type VARCHAR(30) NOT NULL,
  sender_name VARCHAR(255) NULL,
  sender_address VARCHAR(255) NULL,
  recipient_address VARCHAR(255) NULL,
  message_direction VARCHAR(20) NOT NULL,
  external_message_id VARCHAR(191) NULL,
  message_status VARCHAR(50) NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_messages_repository_item (repository_item_id),
  KEY idx_messages_conversation (conversation_id, sent_at),
  KEY idx_messages_external_id (external_message_id),
  CONSTRAINT fk_messages_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_messages_conversation
    FOREIGN KEY (conversation_id) REFERENCES conversations (id),
  CONSTRAINT fk_messages_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE call_logs (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  conversation_id CHAR(36) NULL,
  repository_item_id CHAR(36) NOT NULL,
  from_number VARCHAR(50) NULL,
  to_number VARCHAR(50) NULL,
  direction_code VARCHAR(20) NOT NULL,
  duration_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  started_at DATETIME NULL,
  ended_at DATETIME NULL,
  transcript_text LONGTEXT NULL,
  recording_file_id CHAR(36) NULL,
  external_call_id VARCHAR(191) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_call_logs_repository_item (repository_item_id),
  KEY idx_call_logs_conversation (conversation_id),
  KEY idx_call_logs_started (started_at),
  KEY idx_call_logs_recording_file (recording_file_id),
  CONSTRAINT fk_call_logs_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_call_logs_conversation
    FOREIGN KEY (conversation_id) REFERENCES conversations (id),
  CONSTRAINT fk_call_logs_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id),
  CONSTRAINT fk_call_logs_recording_file
    FOREIGN KEY (recording_file_id) REFERENCES files (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE form_submissions (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  repository_item_id CHAR(36) NOT NULL,
  submission_channel_code VARCHAR(50) NOT NULL,
  submitted_by_name VARCHAR(255) NULL,
  submitted_by_email VARCHAR(255) NULL,
  schema_name VARCHAR(100) NOT NULL,
  schema_version VARCHAR(50) NULL,
  payload_json JSON NOT NULL,
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_form_submissions_repository_item (repository_item_id),
  KEY idx_form_submissions_client (client_id, submitted_at),
  KEY idx_form_submissions_case (case_id, submitted_at),
  CONSTRAINT fk_form_submissions_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_form_submissions_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_form_submissions_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_form_submissions_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id),
  CONSTRAINT fk_form_submissions_channel
    FOREIGN KEY (submission_channel_code) REFERENCES channels (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE document_records (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  file_id CHAR(36) NOT NULL,
  document_type_code VARCHAR(50) NOT NULL,
  client_structure_node_id CHAR(36) NULL,
  title VARCHAR(255) NOT NULL,
  document_status VARCHAR(50) NOT NULL DEFAULT 'received',
  validation_status VARCHAR(30) NULL,
  validation_confidence DECIMAL(5,4) NULL,
  validation_reason TEXT NULL,
  issuing_country_code CHAR(2) NULL,
  issue_date DATE NULL,
  expiration_date DATE NULL,
  document_language VARCHAR(10) NULL,
  page_count INT UNSIGNED NULL,
  extracted_text LONGTEXT NULL,
  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_document_records_file (file_id),
  KEY idx_document_records_client (client_id),
  KEY idx_document_records_case (case_id),
  KEY idx_document_records_type (document_type_code),
  KEY idx_document_records_client_structure_node (client_structure_node_id),
  KEY idx_document_records_expiration (expiration_date),
  CONSTRAINT fk_document_records_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_document_records_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_document_records_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_document_records_file
    FOREIGN KEY (file_id) REFERENCES files (id),
  CONSTRAINT fk_document_records_type
    FOREIGN KEY (document_type_code) REFERENCES document_types (code),
  CONSTRAINT fk_document_records_client_structure_node
    FOREIGN KEY (client_structure_node_id) REFERENCES client_data_structure_nodes (id)
    ON DELETE SET NULL,
  CONSTRAINT fk_document_records_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE document_extractions (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  document_record_id CHAR(36) NOT NULL,
  ai_run_id CHAR(36) NULL,
  extraction_version INT NOT NULL DEFAULT 1,
  status VARCHAR(50) NOT NULL,
  extractor_name VARCHAR(100) NOT NULL,
  extracted_data_json JSON NOT NULL,
  confidence_score DECIMAL(5,4) NOT NULL DEFAULT 0.0000,
  raw_text LONGTEXT 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_document_extractions_document (document_record_id, created_at),
  KEY idx_document_extractions_ai_run (ai_run_id),
  CONSTRAINT fk_document_extractions_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_document_extractions_document
    FOREIGN KEY (document_record_id) REFERENCES document_records (id),
  CONSTRAINT fk_document_extractions_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id),
  CONSTRAINT chk_document_extractions_confidence
    CHECK (confidence_score >= 0.0000 AND confidence_score <= 1.0000)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Workflow and forms
-- -----------------------------------------------------------------------------

CREATE TABLE form_templates (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NULL,
  owner_scope_key VARCHAR(36) GENERATED ALWAYS AS (COALESCE(law_firm_id, 'SYSTEM')) STORED,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  edition VARCHAR(50) NULL,
  jurisdiction_country_code CHAR(2) NULL,
  version_label VARCHAR(50) NOT NULL,
  base_pdf_file_id CHAR(36) NULL,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_system_template TINYINT(1) NOT NULL DEFAULT 0,
  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_form_templates_scope_code_version (owner_scope_key, code, version_label),
  KEY idx_form_templates_law_firm (law_firm_id),
  KEY idx_form_templates_base_pdf (base_pdf_file_id),
  CONSTRAINT fk_form_templates_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_form_templates_base_pdf
    FOREIGN KEY (base_pdf_file_id) REFERENCES files (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE data_fields (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  field_key VARCHAR(100) NOT NULL,
  label VARCHAR(255) NOT NULL,
  entity_scope VARCHAR(50) NOT NULL,
  data_type VARCHAR(50) NOT NULL,
  is_repeatable TINYINT(1) NOT NULL DEFAULT 0,
  pii_level VARCHAR(20) NOT NULL DEFAULT 'high',
  normalization_rule_json JSON NULL,
  description TEXT 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_data_fields_key (field_key),
  KEY idx_data_fields_scope_type (entity_scope, data_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE form_fields (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  form_template_id CHAR(36) NOT NULL,
  section_name VARCHAR(100) NULL,
  page_number INT NULL,
  field_key VARCHAR(100) NOT NULL,
  label VARCHAR(255) NOT NULL,
  pdf_field_name VARCHAR(255) NOT NULL,
  data_type VARCHAR(50) NOT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 0,
  is_repeatable TINYINT(1) NOT NULL DEFAULT 0,
  condition_expression_json JSON NULL,
  instructions TEXT 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_form_fields_pdf_name (form_template_id, pdf_field_name),
  UNIQUE KEY uq_form_fields_key (form_template_id, field_key),
  KEY idx_form_fields_required (form_template_id, is_required),
  CONSTRAINT fk_form_fields_template
    FOREIGN KEY (form_template_id) REFERENCES form_templates (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE workflow_templates (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NULL,
  owner_scope_key VARCHAR(36) GENERATED ALWAYS AS (COALESCE(law_firm_id, 'SYSTEM')) STORED,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  case_type_code VARCHAR(50) NOT NULL,
  case_subtype_code VARCHAR(50) NULL,
  version_number INT NOT NULL DEFAULT 1,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_system_template TINYINT(1) NOT NULL DEFAULT 0,
  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_workflow_templates_scope_code_version (owner_scope_key, code, version_number),
  KEY idx_workflow_templates_type (case_type_code, case_subtype_code),
  KEY idx_workflow_templates_law_firm (law_firm_id),
  CONSTRAINT fk_workflow_templates_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_workflow_templates_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE 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;

CREATE TABLE workflow_steps (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  workflow_template_id CHAR(36) NOT NULL,
  step_code VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  step_order INT NOT NULL,
  step_type VARCHAR(50) NOT NULL DEFAULT 'task',
  description TEXT NULL,
  condition_expression_json JSON NULL,
  completion_rule_json JSON NULL,
  sla_days INT 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_workflow_steps_code (workflow_template_id, step_code),
  UNIQUE KEY uq_workflow_steps_order (workflow_template_id, step_order),
  CONSTRAINT fk_workflow_steps_template
    FOREIGN KEY (workflow_template_id) REFERENCES workflow_templates (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE workflow_required_documents (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  workflow_template_id CHAR(36) NOT NULL,
  workflow_step_id CHAR(36) NULL,
  document_type_code VARCHAR(50) NOT NULL,
  requirement_code VARCHAR(100) NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  minimum_quantity INT NOT NULL DEFAULT 1,
  condition_expression_json JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_workflow_required_documents_code (workflow_template_id, requirement_code),
  KEY idx_workflow_required_documents_step (workflow_step_id),
  KEY idx_workflow_required_documents_type (document_type_code),
  CONSTRAINT fk_workflow_required_documents_template
    FOREIGN KEY (workflow_template_id) REFERENCES workflow_templates (id),
  CONSTRAINT fk_workflow_required_documents_step
    FOREIGN KEY (workflow_step_id) REFERENCES workflow_steps (id),
  CONSTRAINT fk_workflow_required_documents_type
    FOREIGN KEY (document_type_code) REFERENCES document_types (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE workflow_required_forms (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  workflow_template_id CHAR(36) NOT NULL,
  workflow_step_id CHAR(36) NULL,
  form_template_id CHAR(36) NOT NULL,
  requirement_code VARCHAR(100) NOT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  due_offset_days INT NULL,
  condition_expression_json JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_workflow_required_forms_code (workflow_template_id, requirement_code),
  KEY idx_workflow_required_forms_step (workflow_step_id),
  KEY idx_workflow_required_forms_form (form_template_id),
  CONSTRAINT fk_workflow_required_forms_template
    FOREIGN KEY (workflow_template_id) REFERENCES workflow_templates (id),
  CONSTRAINT fk_workflow_required_forms_step
    FOREIGN KEY (workflow_step_id) REFERENCES workflow_steps (id),
  CONSTRAINT fk_workflow_required_forms_form
    FOREIGN KEY (form_template_id) REFERENCES form_templates (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE workflow_instances (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  case_id CHAR(36) NOT NULL,
  workflow_template_id CHAR(36) NOT NULL,
  status_code VARCHAR(50) NOT NULL DEFAULT 'active',
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  current_step_order INT NULL,
  template_snapshot_json JSON NULL,
  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),
  KEY idx_workflow_instances_case (case_id, status_code),
  KEY idx_workflow_instances_template (workflow_template_id),
  CONSTRAINT fk_workflow_instances_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_workflow_instances_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_workflow_instances_template
    FOREIGN KEY (workflow_template_id) REFERENCES workflow_templates (id),
  CONSTRAINT fk_workflow_instances_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE workflow_step_instances (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  workflow_instance_id CHAR(36) NOT NULL,
  workflow_step_id CHAR(36) NOT NULL,
  assignee_user_id CHAR(36) NULL,
  status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
  due_at DATETIME NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  decision_json JSON 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_workflow_step_instances_step (workflow_instance_id, workflow_step_id),
  KEY idx_workflow_step_instances_assignee (assignee_user_id, status_code, due_at),
  CONSTRAINT fk_workflow_step_instances_instance
    FOREIGN KEY (workflow_instance_id) REFERENCES workflow_instances (id),
  CONSTRAINT fk_workflow_step_instances_step
    FOREIGN KEY (workflow_step_id) REFERENCES workflow_steps (id),
  CONSTRAINT fk_workflow_step_instances_assignee
    FOREIGN KEY (assignee_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE case_required_documents (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  case_id CHAR(36) NOT NULL,
  workflow_instance_id CHAR(36) NULL,
  workflow_required_document_id CHAR(36) NULL,
  document_type_code VARCHAR(50) NOT NULL,
  requirement_name VARCHAR(255) NOT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
  due_at DATETIME NULL,
  satisfied_by_document_record_id CHAR(36) NULL,
  notes TEXT 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_case_required_documents_case (case_id, status_code),
  KEY idx_case_required_documents_type (document_type_code),
  KEY idx_case_required_documents_document (satisfied_by_document_record_id),
  CONSTRAINT fk_case_required_documents_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_case_required_documents_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_case_required_documents_instance
    FOREIGN KEY (workflow_instance_id) REFERENCES workflow_instances (id),
  CONSTRAINT fk_case_required_documents_requirement
    FOREIGN KEY (workflow_required_document_id) REFERENCES workflow_required_documents (id),
  CONSTRAINT fk_case_required_documents_type
    FOREIGN KEY (document_type_code) REFERENCES document_types (code),
  CONSTRAINT fk_case_required_documents_document
    FOREIGN KEY (satisfied_by_document_record_id) REFERENCES document_records (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE case_forms (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  case_id CHAR(36) NOT NULL,
  workflow_instance_id CHAR(36) NULL,
  workflow_required_form_id CHAR(36) NULL,
  form_template_id CHAR(36) NOT NULL,
  requirement_name VARCHAR(255) NOT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
  due_at DATETIME NULL,
  last_reviewed_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_case_forms_case (case_id, status_code),
  KEY idx_case_forms_template (form_template_id),
  CONSTRAINT fk_case_forms_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_case_forms_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_case_forms_instance
    FOREIGN KEY (workflow_instance_id) REFERENCES workflow_instances (id),
  CONSTRAINT fk_case_forms_requirement
    FOREIGN KEY (workflow_required_form_id) REFERENCES workflow_required_forms (id),
  CONSTRAINT fk_case_forms_template
    FOREIGN KEY (form_template_id) REFERENCES form_templates (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Facts, missing information and client portal
-- -----------------------------------------------------------------------------

CREATE TABLE missing_information_rules (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NULL,
  workflow_template_id CHAR(36) NULL,
  form_template_id CHAR(36) NULL,
  form_field_id CHAR(36) NULL,
  data_field_id CHAR(36) NULL,
  rule_name VARCHAR(255) NOT NULL,
  trigger_scope VARCHAR(50) NOT NULL,
  description TEXT NULL,
  minimum_confidence DECIMAL(5,4) NOT NULL DEFAULT 0.8000,
  conflict_policy VARCHAR(50) NOT NULL DEFAULT 'ask_for_confirmation',
  condition_expression_json JSON NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  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_missing_information_rules_law_firm (law_firm_id, is_active),
  KEY idx_missing_information_rules_workflow (workflow_template_id),
  KEY idx_missing_information_rules_form (form_template_id, form_field_id),
  KEY idx_missing_information_rules_data_field (data_field_id),
  CONSTRAINT fk_missing_information_rules_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_missing_information_rules_workflow
    FOREIGN KEY (workflow_template_id) REFERENCES workflow_templates (id),
  CONSTRAINT fk_missing_information_rules_form_template
    FOREIGN KEY (form_template_id) REFERENCES form_templates (id),
  CONSTRAINT fk_missing_information_rules_form_field
    FOREIGN KEY (form_field_id) REFERENCES form_fields (id),
  CONSTRAINT fk_missing_information_rules_data_field
    FOREIGN KEY (data_field_id) REFERENCES data_fields (id),
  CONSTRAINT chk_missing_information_rules_confidence
    CHECK (minimum_confidence >= 0.0000 AND minimum_confidence <= 1.0000)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE information_requests (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  request_type VARCHAR(50) NOT NULL,
  title VARCHAR(255) NOT NULL,
  status_code VARCHAR(50) NOT NULL DEFAULT 'draft',
  delivery_channel_code VARCHAR(50) NULL,
  triggered_by_ai_run_id CHAR(36) NULL,
  requested_by_user_id CHAR(36) NULL,
  sent_at DATETIME NULL,
  opened_at DATETIME NULL,
  completed_at DATETIME NULL,
  expires_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_information_requests_client (client_id, status_code, created_at),
  KEY idx_information_requests_case (case_id, status_code, created_at),
  CONSTRAINT fk_information_requests_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_information_requests_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_information_requests_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_information_requests_channel
    FOREIGN KEY (delivery_channel_code) REFERENCES channels (code),
  CONSTRAINT fk_information_requests_ai_run
    FOREIGN KEY (triggered_by_ai_run_id) REFERENCES ai_runs (id),
  CONSTRAINT fk_information_requests_requested_by
    FOREIGN KEY (requested_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE information_request_items (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  information_request_id CHAR(36) NOT NULL,
  data_field_id CHAR(36) NULL,
  related_party_id CHAR(36) NULL,
  form_field_id CHAR(36) NULL,
  requirement_type VARCHAR(50) NOT NULL,
  reason_code VARCHAR(50) NOT NULL,
  reason_text TEXT NULL,
  current_confidence DECIMAL(5,4) NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
  resolved_by_source_type VARCHAR(50) NULL,
  resolved_by_source_id CHAR(36) NULL,
  resolved_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_information_request_items_request (information_request_id, status_code),
  KEY idx_information_request_items_data_field (data_field_id),
  KEY idx_information_request_items_form_field (form_field_id),
  KEY idx_information_request_items_related_party (related_party_id),
  CONSTRAINT fk_information_request_items_request
    FOREIGN KEY (information_request_id) REFERENCES information_requests (id),
  CONSTRAINT fk_information_request_items_data_field
    FOREIGN KEY (data_field_id) REFERENCES data_fields (id),
  CONSTRAINT fk_information_request_items_related_party
    FOREIGN KEY (related_party_id) REFERENCES related_parties (id),
  CONSTRAINT fk_information_request_items_form_field
    FOREIGN KEY (form_field_id) REFERENCES form_fields (id),
  CONSTRAINT chk_information_request_items_confidence
    CHECK (current_confidence IS NULL OR (current_confidence >= 0.0000 AND current_confidence <= 1.0000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE client_portal_sessions (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  information_request_id CHAR(36) NULL,
  token_hash CHAR(64) NOT NULL,
  session_type VARCHAR(50) NOT NULL,
  status_code VARCHAR(50) NOT NULL DEFAULT 'issued',
  expires_at DATETIME NOT NULL,
  opened_at DATETIME NULL,
  last_activity_at DATETIME NULL,
  closed_at DATETIME NULL,
  reopened_from_session_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_portal_sessions_token (token_hash),
  KEY idx_client_portal_sessions_client (client_id, status_code),
  KEY idx_client_portal_sessions_case (case_id, status_code),
  KEY idx_client_portal_sessions_expiration (expires_at),
  CONSTRAINT fk_client_portal_sessions_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_client_portal_sessions_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_client_portal_sessions_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_client_portal_sessions_request
    FOREIGN KEY (information_request_id) REFERENCES information_requests (id),
  CONSTRAINT fk_client_portal_sessions_reopened_from
    FOREIGN KEY (reopened_from_session_id) REFERENCES client_portal_sessions (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE client_chat_messages (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  client_portal_session_id CHAR(36) NOT NULL,
  repository_item_id CHAR(36) NOT NULL,
  sender_type VARCHAR(30) NOT NULL,
  message_type VARCHAR(50) NOT NULL DEFAULT 'text',
  body_text LONGTEXT NULL,
  payload_json JSON NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_client_chat_messages_repository_item (repository_item_id),
  KEY idx_client_chat_messages_session (client_portal_session_id, sent_at),
  CONSTRAINT fk_client_chat_messages_session
    FOREIGN KEY (client_portal_session_id) REFERENCES client_portal_sessions (id),
  CONSTRAINT fk_client_chat_messages_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE case_facts (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  related_party_id CHAR(36) NULL,
  data_field_id CHAR(36) NOT NULL,
  source_context_code VARCHAR(50) NOT NULL DEFAULT 'ai',
  raw_value LONGTEXT NULL,
  normalized_value_json JSON NULL,
  normalized_value_hash CHAR(64) NULL,
  confidence_score DECIMAL(5,4) NOT NULL DEFAULT 0.0000,
  status_code VARCHAR(50) NOT NULL DEFAULT 'proposed',
  requires_human_confirmation TINYINT(1) NOT NULL DEFAULT 0,
  human_confirmed_by_user_id CHAR(36) NULL,
  human_confirmed_at DATETIME NULL,
  ai_run_id CHAR(36) NULL,
  superseded_by_fact_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_case_facts_case_field (case_id, data_field_id, status_code, confidence_score),
  KEY idx_case_facts_client_field (client_id, data_field_id, status_code, confidence_score),
  KEY idx_case_facts_related_party (related_party_id, data_field_id),
  KEY idx_case_facts_ai_run (ai_run_id),
  KEY idx_case_facts_superseded_by (superseded_by_fact_id),
  CONSTRAINT fk_case_facts_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_case_facts_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_case_facts_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_case_facts_related_party
    FOREIGN KEY (related_party_id) REFERENCES related_parties (id),
  CONSTRAINT fk_case_facts_data_field
    FOREIGN KEY (data_field_id) REFERENCES data_fields (id),
  CONSTRAINT fk_case_facts_human_confirmed_by
    FOREIGN KEY (human_confirmed_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_case_facts_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id),
  CONSTRAINT fk_case_facts_superseded_by
    FOREIGN KEY (superseded_by_fact_id) REFERENCES case_facts (id),
  CONSTRAINT chk_case_facts_confidence
    CHECK (confidence_score >= 0.0000 AND confidence_score <= 1.0000)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fact_sources (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  case_fact_id CHAR(36) NOT NULL,
  source_type VARCHAR(50) NOT NULL,
  source_id CHAR(36) NULL,
  repository_item_id CHAR(36) NULL,
  ai_run_id CHAR(36) NULL,
  source_rank INT NOT NULL DEFAULT 1,
  field_path VARCHAR(255) NULL,
  page_number INT NULL,
  evidence_excerpt TEXT NULL,
  confidence_contribution DECIMAL(5,4) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_fact_sources_fact (case_fact_id),
  KEY idx_fact_sources_source (source_type, source_id),
  KEY idx_fact_sources_repository_item (repository_item_id),
  CONSTRAINT fk_fact_sources_fact
    FOREIGN KEY (case_fact_id) REFERENCES case_facts (id),
  CONSTRAINT fk_fact_sources_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id),
  CONSTRAINT fk_fact_sources_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id),
  CONSTRAINT chk_fact_sources_confidence
    CHECK (confidence_contribution IS NULL OR (confidence_contribution >= 0.0000 AND confidence_contribution <= 1.0000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Intelligent questionnaire
-- -----------------------------------------------------------------------------

CREATE TABLE questionnaire_runs (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  workflow_instance_id CHAR(36) NULL,
  information_request_id CHAR(36) NULL,
  ai_run_id CHAR(36) NULL,
  status_code VARCHAR(50) NOT NULL DEFAULT 'completed',
  run_reason VARCHAR(255) NULL,
  coverage_summary_json JSON NULL,
  requested_by_user_id CHAR(36) NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_questionnaire_runs_case (case_id, status_code, created_at),
  KEY idx_questionnaire_runs_client (client_id, status_code, created_at),
  KEY idx_questionnaire_runs_ai_run (ai_run_id),
  CONSTRAINT fk_questionnaire_runs_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_questionnaire_runs_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_questionnaire_runs_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_questionnaire_runs_instance
    FOREIGN KEY (workflow_instance_id) REFERENCES workflow_instances (id),
  CONSTRAINT fk_questionnaire_runs_request
    FOREIGN KEY (information_request_id) REFERENCES information_requests (id),
  CONSTRAINT fk_questionnaire_runs_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id),
  CONSTRAINT fk_questionnaire_runs_requested_by
    FOREIGN KEY (requested_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE questionnaire_run_sources (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  questionnaire_run_id CHAR(36) NOT NULL,
  source_type VARCHAR(50) NOT NULL,
  source_id CHAR(36) NULL,
  repository_item_id CHAR(36) NULL,
  document_record_id CHAR(36) NULL,
  document_extraction_id CHAR(36) NULL,
  case_fact_id CHAR(36) NULL,
  was_consulted TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_questionnaire_run_sources_run (questionnaire_run_id, source_type),
  KEY idx_questionnaire_run_sources_repository_item (repository_item_id),
  KEY idx_questionnaire_run_sources_document_record (document_record_id),
  KEY idx_questionnaire_run_sources_case_fact (case_fact_id),
  CONSTRAINT fk_questionnaire_run_sources_run
    FOREIGN KEY (questionnaire_run_id) REFERENCES questionnaire_runs (id),
  CONSTRAINT fk_questionnaire_run_sources_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id),
  CONSTRAINT fk_questionnaire_run_sources_document_record
    FOREIGN KEY (document_record_id) REFERENCES document_records (id),
  CONSTRAINT fk_questionnaire_run_sources_document_extraction
    FOREIGN KEY (document_extraction_id) REFERENCES document_extractions (id),
  CONSTRAINT fk_questionnaire_run_sources_case_fact
    FOREIGN KEY (case_fact_id) REFERENCES case_facts (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE questionnaire_run_forms (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  questionnaire_run_id CHAR(36) NOT NULL,
  form_template_id CHAR(36) NOT NULL,
  case_form_id CHAR(36) NULL,
  evaluation_status VARCHAR(50) NOT NULL DEFAULT 'evaluated',
  covered_count INT NOT NULL DEFAULT 0,
  missing_count INT NOT NULL DEFAULT 0,
  low_confidence_count INT NOT NULL DEFAULT 0,
  conflicting_count INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_questionnaire_run_forms_run (questionnaire_run_id),
  KEY idx_questionnaire_run_forms_form (form_template_id),
  KEY idx_questionnaire_run_forms_case_form (case_form_id),
  CONSTRAINT fk_questionnaire_run_forms_run
    FOREIGN KEY (questionnaire_run_id) REFERENCES questionnaire_runs (id),
  CONSTRAINT fk_questionnaire_run_forms_form
    FOREIGN KEY (form_template_id) REFERENCES form_templates (id),
  CONSTRAINT fk_questionnaire_run_forms_case_form
    FOREIGN KEY (case_form_id) REFERENCES case_forms (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE questionnaire_run_field_results (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  questionnaire_run_form_id CHAR(36) NOT NULL,
  form_field_id CHAR(36) NOT NULL,
  data_field_id CHAR(36) NULL,
  related_party_id CHAR(36) NULL,
  chosen_case_fact_id CHAR(36) NULL,
  result_status VARCHAR(50) NOT NULL,
  current_confidence DECIMAL(5,4) NULL,
  reasoning TEXT NULL,
  source_summary_json JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_questionnaire_run_field_results_run (questionnaire_run_form_id, result_status),
  KEY idx_questionnaire_run_field_results_form_field (form_field_id),
  KEY idx_questionnaire_run_field_results_data_field (data_field_id),
  CONSTRAINT fk_questionnaire_run_field_results_run_form
    FOREIGN KEY (questionnaire_run_form_id) REFERENCES questionnaire_run_forms (id),
  CONSTRAINT fk_questionnaire_run_field_results_form_field
    FOREIGN KEY (form_field_id) REFERENCES form_fields (id),
  CONSTRAINT fk_questionnaire_run_field_results_data_field
    FOREIGN KEY (data_field_id) REFERENCES data_fields (id),
  CONSTRAINT fk_questionnaire_run_field_results_related_party
    FOREIGN KEY (related_party_id) REFERENCES related_parties (id),
  CONSTRAINT fk_questionnaire_run_field_results_case_fact
    FOREIGN KEY (chosen_case_fact_id) REFERENCES case_facts (id),
  CONSTRAINT chk_questionnaire_run_field_results_confidence
    CHECK (current_confidence IS NULL OR (current_confidence >= 0.0000 AND current_confidence <= 1.0000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE questionnaire_sessions (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  questionnaire_run_id CHAR(36) NOT NULL,
  client_portal_session_id CHAR(36) NULL,
  token_hash CHAR(64) NOT NULL,
  status_code VARCHAR(50) NOT NULL,
  expires_at DATETIME NOT NULL,
  started_at DATETIME NULL,
  last_activity_at DATETIME NULL,
  completed_at DATETIME NULL,
  reopened_from_session_id CHAR(36) NULL,
  close_reason VARCHAR(255) NULL,
  is_reopen_allowed TINYINT(1) NOT NULL DEFAULT 0,
  max_reopens INT NOT NULL DEFAULT 0,
  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_questionnaire_sessions_token (token_hash),
  KEY idx_questionnaire_sessions_client (client_id, status_code),
  KEY idx_questionnaire_sessions_case (case_id, status_code),
  KEY idx_questionnaire_sessions_expires (expires_at),
  CONSTRAINT fk_questionnaire_sessions_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_questionnaire_sessions_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_questionnaire_sessions_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_questionnaire_sessions_run
    FOREIGN KEY (questionnaire_run_id) REFERENCES questionnaire_runs (id),
  CONSTRAINT fk_questionnaire_sessions_portal_session
    FOREIGN KEY (client_portal_session_id) REFERENCES client_portal_sessions (id),
  CONSTRAINT fk_questionnaire_sessions_status
    FOREIGN KEY (status_code) REFERENCES questionnaire_statuses (code),
  CONSTRAINT fk_questionnaire_sessions_reopened_from
    FOREIGN KEY (reopened_from_session_id) REFERENCES questionnaire_sessions (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE questionnaire_questions (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  questionnaire_session_id CHAR(36) NOT NULL,
  generated_by_run_id CHAR(36) NULL,
  sequence_no INT NOT NULL,
  question_type VARCHAR(50) NOT NULL,
  question_text TEXT NOT NULL,
  question_context_group VARCHAR(100) NULL,
  reason_text TEXT NULL,
  consulted_sources_json JSON NULL,
  current_confidence DECIMAL(5,4) NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  is_confirmation TINYINT(1) NOT NULL DEFAULT 0,
  priority INT NOT NULL DEFAULT 100,
  dependency_expression_json JSON NULL,
  status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
  asked_at DATETIME NULL,
  answered_at DATETIME NULL,
  retired_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_questionnaire_questions_sequence (questionnaire_session_id, sequence_no),
  KEY idx_questionnaire_questions_session (questionnaire_session_id, status_code, priority),
  CONSTRAINT fk_questionnaire_questions_session
    FOREIGN KEY (questionnaire_session_id) REFERENCES questionnaire_sessions (id),
  CONSTRAINT fk_questionnaire_questions_run
    FOREIGN KEY (generated_by_run_id) REFERENCES questionnaire_runs (id),
  CONSTRAINT chk_questionnaire_questions_confidence
    CHECK (current_confidence IS NULL OR (current_confidence >= 0.0000 AND current_confidence <= 1.0000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE questionnaire_question_targets (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  question_id CHAR(36) NOT NULL,
  data_field_id CHAR(36) NULL,
  form_field_id CHAR(36) NULL,
  information_request_item_id CHAR(36) NULL,
  related_party_id CHAR(36) NULL,
  target_role_code VARCHAR(50) NULL,
  target_priority INT NOT NULL DEFAULT 100,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_questionnaire_question_targets_question (question_id),
  KEY idx_questionnaire_question_targets_data_field (data_field_id),
  KEY idx_questionnaire_question_targets_form_field (form_field_id),
  CONSTRAINT fk_questionnaire_question_targets_question
    FOREIGN KEY (question_id) REFERENCES questionnaire_questions (id),
  CONSTRAINT fk_questionnaire_question_targets_data_field
    FOREIGN KEY (data_field_id) REFERENCES data_fields (id),
  CONSTRAINT fk_questionnaire_question_targets_form_field
    FOREIGN KEY (form_field_id) REFERENCES form_fields (id),
  CONSTRAINT fk_questionnaire_question_targets_request_item
    FOREIGN KEY (information_request_item_id) REFERENCES information_request_items (id),
  CONSTRAINT fk_questionnaire_question_targets_related_party
    FOREIGN KEY (related_party_id) REFERENCES related_parties (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE generated_documents (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  repository_item_id CHAR(36) NOT NULL,
  file_id CHAR(36) NOT NULL,
  document_kind VARCHAR(50) NOT NULL,
  source_entity_type VARCHAR(50) NOT NULL,
  source_entity_id CHAR(36) NULL,
  ai_run_id CHAR(36) NULL,
  version_number INT NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_generated_documents_repository_item (repository_item_id),
  UNIQUE KEY uq_generated_documents_file (file_id),
  KEY idx_generated_documents_case (case_id, document_kind),
  KEY idx_generated_documents_ai_run (ai_run_id),
  CONSTRAINT fk_generated_documents_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_generated_documents_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_generated_documents_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_generated_documents_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id),
  CONSTRAINT fk_generated_documents_file
    FOREIGN KEY (file_id) REFERENCES files (id),
  CONSTRAINT fk_generated_documents_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE questionnaire_answers (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  questionnaire_session_id CHAR(36) NOT NULL,
  question_id CHAR(36) NOT NULL,
  repository_item_id CHAR(36) NULL,
  channel_code VARCHAR(50) NULL,
  answer_text LONGTEXT NULL,
  structured_answer_json JSON NULL,
  answer_source VARCHAR(50) NOT NULL DEFAULT 'client',
  answered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_case_fact_id CHAR(36) NULL,
  confidence_override DECIMAL(5,4) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_questionnaire_answers_repository_item (repository_item_id),
  KEY idx_questionnaire_answers_session_question (questionnaire_session_id, question_id, answered_at),
  KEY idx_questionnaire_answers_case_fact (created_case_fact_id),
  CONSTRAINT fk_questionnaire_answers_session
    FOREIGN KEY (questionnaire_session_id) REFERENCES questionnaire_sessions (id),
  CONSTRAINT fk_questionnaire_answers_question
    FOREIGN KEY (question_id) REFERENCES questionnaire_questions (id),
  CONSTRAINT fk_questionnaire_answers_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id),
  CONSTRAINT fk_questionnaire_answers_channel
    FOREIGN KEY (channel_code) REFERENCES channels (code),
  CONSTRAINT fk_questionnaire_answers_case_fact
    FOREIGN KEY (created_case_fact_id) REFERENCES case_facts (id),
  CONSTRAINT chk_questionnaire_answers_confidence
    CHECK (confidence_override IS NULL OR (confidence_override >= 0.0000 AND confidence_override <= 1.0000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE questionnaire_events (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  questionnaire_session_id CHAR(36) NOT NULL,
  question_id CHAR(36) NULL,
  answer_id CHAR(36) NULL,
  ai_run_id CHAR(36) NULL,
  event_type VARCHAR(50) NOT NULL,
  payload_json JSON NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_questionnaire_events_session (questionnaire_session_id, occurred_at),
  KEY idx_questionnaire_events_type (event_type, occurred_at),
  CONSTRAINT fk_questionnaire_events_session
    FOREIGN KEY (questionnaire_session_id) REFERENCES questionnaire_sessions (id),
  CONSTRAINT fk_questionnaire_events_question
    FOREIGN KEY (question_id) REFERENCES questionnaire_questions (id),
  CONSTRAINT fk_questionnaire_events_answer
    FOREIGN KEY (answer_id) REFERENCES questionnaire_answers (id),
  CONSTRAINT fk_questionnaire_events_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Form fill and final packet assembly
-- -----------------------------------------------------------------------------

CREATE TABLE form_mappings (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  form_template_id CHAR(36) NOT NULL,
  form_field_id CHAR(36) NOT NULL,
  data_field_id CHAR(36) NOT NULL,
  mapping_strategy VARCHAR(50) NOT NULL DEFAULT 'direct',
  transform_rule_json JSON NULL,
  confidence_threshold DECIMAL(5,4) NOT NULL DEFAULT 0.8000,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  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_form_mappings_field_data (form_field_id, data_field_id),
  KEY idx_form_mappings_template_data_field (form_template_id, data_field_id),
  CONSTRAINT fk_form_mappings_template
    FOREIGN KEY (form_template_id) REFERENCES form_templates (id),
  CONSTRAINT fk_form_mappings_form_field
    FOREIGN KEY (form_field_id) REFERENCES form_fields (id),
  CONSTRAINT fk_form_mappings_data_field
    FOREIGN KEY (data_field_id) REFERENCES data_fields (id),
  CONSTRAINT chk_form_mappings_confidence
    CHECK (confidence_threshold >= 0.0000 AND confidence_threshold <= 1.0000)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE form_fill_runs (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  case_form_id CHAR(36) NOT NULL,
  generated_document_id CHAR(36) NULL,
  ai_run_id CHAR(36) NULL,
  status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
  mapping_snapshot_json JSON NULL,
  fill_summary_json JSON NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  error_message TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_form_fill_runs_case_form (case_form_id, status_code, created_at),
  KEY idx_form_fill_runs_generated_document (generated_document_id),
  KEY idx_form_fill_runs_ai_run (ai_run_id),
  CONSTRAINT fk_form_fill_runs_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_form_fill_runs_case_form
    FOREIGN KEY (case_form_id) REFERENCES case_forms (id),
  CONSTRAINT fk_form_fill_runs_generated_document
    FOREIGN KEY (generated_document_id) REFERENCES generated_documents (id),
  CONSTRAINT fk_form_fill_runs_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE packet_templates (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NULL,
  owner_scope_key VARCHAR(36) GENERATED ALWAYS AS (COALESCE(law_firm_id, 'SYSTEM')) STORED,
  code VARCHAR(50) NOT NULL,
  name VARCHAR(255) NOT NULL,
  version_number INT NOT NULL DEFAULT 1,
  description TEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  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_packet_templates_scope_code_version (owner_scope_key, code, version_number),
  KEY idx_packet_templates_law_firm (law_firm_id),
  CONSTRAINT fk_packet_templates_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE packet_template_items (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  packet_template_id CHAR(36) NOT NULL,
  sort_order INT NOT NULL,
  item_type VARCHAR(50) NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  form_template_id CHAR(36) NULL,
  document_type_code VARCHAR(50) NULL,
  source_strategy VARCHAR(50) NOT NULL DEFAULT 'static',
  condition_expression_json JSON NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_packet_template_items_order (packet_template_id, sort_order),
  KEY idx_packet_template_items_form (form_template_id),
  KEY idx_packet_template_items_document_type (document_type_code),
  CONSTRAINT fk_packet_template_items_template
    FOREIGN KEY (packet_template_id) REFERENCES packet_templates (id),
  CONSTRAINT fk_packet_template_items_form
    FOREIGN KEY (form_template_id) REFERENCES form_templates (id),
  CONSTRAINT fk_packet_template_items_document_type
    FOREIGN KEY (document_type_code) REFERENCES document_types (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE case_packets (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  case_id CHAR(36) NOT NULL,
  packet_template_id CHAR(36) NULL,
  generated_document_id CHAR(36) NULL,
  status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
  assembled_by_user_id CHAR(36) NULL,
  ai_run_id CHAR(36) 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_case_packets_case (case_id, status_code),
  KEY idx_case_packets_generated_document (generated_document_id),
  CONSTRAINT fk_case_packets_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_case_packets_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_case_packets_template
    FOREIGN KEY (packet_template_id) REFERENCES packet_templates (id),
  CONSTRAINT fk_case_packets_generated_document
    FOREIGN KEY (generated_document_id) REFERENCES generated_documents (id),
  CONSTRAINT fk_case_packets_assembled_by
    FOREIGN KEY (assembled_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_case_packets_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE case_packet_items (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  case_packet_id CHAR(36) NOT NULL,
  packet_template_item_id CHAR(36) NULL,
  sort_order INT NOT NULL,
  source_entity_type VARCHAR(50) NOT NULL,
  source_entity_id CHAR(36) NULL,
  generated_document_id CHAR(36) NULL,
  status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
  notes TEXT 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_case_packet_items_packet (case_packet_id, sort_order),
  KEY idx_case_packet_items_generated_document (generated_document_id),
  CONSTRAINT fk_case_packet_items_packet
    FOREIGN KEY (case_packet_id) REFERENCES case_packets (id),
  CONSTRAINT fk_case_packet_items_template_item
    FOREIGN KEY (packet_template_item_id) REFERENCES packet_template_items (id),
  CONSTRAINT fk_case_packet_items_generated_document
    FOREIGN KEY (generated_document_id) REFERENCES generated_documents (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Document review
-- -----------------------------------------------------------------------------

CREATE TABLE document_review_items (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  case_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  document_name VARCHAR(255) NOT NULL,
  objective TEXT NOT NULL,
  due_at DATETIME NOT NULL,
  current_status VARCHAR(50) NOT NULL DEFAULT 'draft',
  latest_version_number INT NOT NULL DEFAULT 0,
  has_ai_alert TINYINT(1) NOT NULL DEFAULT 0,
  latest_risk_level VARCHAR(20) NULL,
  latest_decision_code VARCHAR(20) NULL,
  latest_decision_reason TEXT NULL,
  latest_analysis_confidence DECIMAL(5,4) NULL,
  latest_final_validation_confidence DECIMAL(5,4) NULL,
  created_by_user_id CHAR(36) NULL,
  last_submitted_by_user_id CHAR(36) NULL,
  last_submitted_at DATETIME NULL,
  last_reviewed_at DATETIME NULL,
  approved_at DATETIME NULL,
  rejected_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_document_review_items_status (law_firm_id, current_status),
  KEY idx_document_review_items_case_status (case_id, current_status),
  KEY idx_document_review_items_client_status (client_id, current_status),
  KEY idx_document_review_items_due (law_firm_id, due_at),
  CONSTRAINT fk_document_review_items_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_document_review_items_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_document_review_items_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_document_review_items_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_document_review_items_last_submitted_by
    FOREIGN KEY (last_submitted_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE document_review_versions (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  item_id CHAR(36) NOT NULL,
  case_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  file_id CHAR(36) NOT NULL,
  previous_version_id CHAR(36) NULL,
  version_number INT NOT NULL,
  submission_status VARCHAR(50) NOT NULL DEFAULT 'draft',
  submission_note TEXT NULL,
  extracted_text LONGTEXT NULL,
  structure_json JSON NULL,
  identified_patterns_json JSON NULL,
  historical_context_json JSON NULL,
  comparison_json JSON NULL,
  ai_alerts_json JSON NULL,
  possible_rejection_reasons JSON NULL,
  has_ai_alert TINYINT(1) NOT NULL DEFAULT 0,
  risk_level VARCHAR(20) NULL,
  last_analysis_confidence DECIMAL(5,4) NULL,
  submitted_by_user_id CHAR(36) NULL,
  submitted_at DATETIME NULL,
  cancelled_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_document_review_versions_item_version (item_id, version_number),
  KEY idx_document_review_versions_status (law_firm_id, submission_status),
  KEY idx_document_review_versions_item_created (item_id, created_at),
  KEY idx_document_review_versions_file (file_id),
  CONSTRAINT fk_document_review_versions_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_document_review_versions_item
    FOREIGN KEY (item_id) REFERENCES document_review_items (id),
  CONSTRAINT fk_document_review_versions_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_document_review_versions_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_document_review_versions_file
    FOREIGN KEY (file_id) REFERENCES files (id),
  CONSTRAINT fk_document_review_versions_previous
    FOREIGN KEY (previous_version_id) REFERENCES document_review_versions (id),
  CONSTRAINT fk_document_review_versions_submitted_by
    FOREIGN KEY (submitted_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE document_review_ai_analyses (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  item_id CHAR(36) NULL,
  version_id CHAR(36) NULL,
  ai_run_id CHAR(36) NULL,
  analysis_type VARCHAR(50) NOT NULL,
  summary TEXT NULL,
  risks JSON NULL,
  strengths JSON NULL,
  weaknesses JSON NULL,
  suggestions JSON NULL,
  possible_rejection_reasons JSON NULL,
  confidence DECIMAL(5,4) NULL,
  confidence_label VARCHAR(20) NULL,
  triggered_manually TINYINT(1) NOT NULL DEFAULT 0,
  extracted_text LONGTEXT NULL,
  structure_json JSON NULL,
  identified_patterns_json JSON NULL,
  historical_context_json JSON NULL,
  comparison_json JSON NULL,
  source_payload_json JSON NULL,
  alert_points_json JSON NULL,
  risk_level VARCHAR(20) NULL,
  raw_result_json JSON NULL,
  created_by_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_document_review_ai_analyses_item_created (item_id, created_at),
  KEY idx_document_review_ai_analyses_version_created (version_id, created_at),
  KEY idx_document_review_ai_analyses_type_created (analysis_type, created_at),
  KEY idx_document_review_ai_analyses_ai_run (ai_run_id),
  CONSTRAINT fk_document_review_ai_analyses_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_document_review_ai_analyses_item
    FOREIGN KEY (item_id) REFERENCES document_review_items (id),
  CONSTRAINT fk_document_review_ai_analyses_version
    FOREIGN KEY (version_id) REFERENCES document_review_versions (id),
  CONSTRAINT fk_document_review_ai_analyses_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id),
  CONSTRAINT fk_document_review_ai_analyses_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE document_review_decisions (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  item_id CHAR(36) NOT NULL,
  version_id CHAR(36) NOT NULL,
  decision_code VARCHAR(20) NOT NULL,
  justification TEXT NOT NULL,
  rejection_reason TEXT NULL,
  guidance_for_new_version TEXT NULL,
  final_validation_analysis_id CHAR(36) NULL,
  analysis_snapshot_json JSON NULL,
  reviewer_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_document_review_decisions_item_created (item_id, created_at),
  KEY idx_document_review_decisions_version (version_id),
  KEY idx_document_review_decisions_code_created (decision_code, created_at),
  CONSTRAINT fk_document_review_decisions_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_document_review_decisions_item
    FOREIGN KEY (item_id) REFERENCES document_review_items (id),
  CONSTRAINT fk_document_review_decisions_version
    FOREIGN KEY (version_id) REFERENCES document_review_versions (id),
  CONSTRAINT fk_document_review_decisions_final_validation
    FOREIGN KEY (final_validation_analysis_id) REFERENCES document_review_ai_analyses (id),
  CONSTRAINT fk_document_review_decisions_reviewer
    FOREIGN KEY (reviewer_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE document_review_comments (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  item_id CHAR(36) NOT NULL,
  version_id CHAR(36) NOT NULL,
  ai_run_id CHAR(36) NULL,
  page_number INT NULL,
  anchor_x_ratio DECIMAL(8,6) NULL,
  anchor_y_ratio DECIMAL(8,6) NULL,
  selection_rects_json JSON NULL,
  selection_start_offset INT NULL,
  selection_end_offset INT NULL,
  selected_text TEXT NOT NULL,
  comment_text TEXT NOT NULL,
  assistant_response LONGTEXT NOT NULL,
  knowledge_summary TEXT NULL,
  created_by_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_document_review_comments_item_created (item_id, created_at),
  KEY idx_document_review_comments_version_created (version_id, created_at),
  KEY idx_document_review_comments_ai_run (ai_run_id),
  CONSTRAINT fk_document_review_comments_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_document_review_comments_item
    FOREIGN KEY (item_id) REFERENCES document_review_items (id),
  CONSTRAINT fk_document_review_comments_version
    FOREIGN KEY (version_id) REFERENCES document_review_versions (id),
  CONSTRAINT fk_document_review_comments_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id),
  CONSTRAINT fk_document_review_comments_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Audit and review
-- -----------------------------------------------------------------------------

CREATE TABLE audit_logs (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  office_id CHAR(36) NULL,
  actor_user_id CHAR(36) NULL,
  entity_type VARCHAR(50) NOT NULL,
  entity_id CHAR(36) NULL,
  action VARCHAR(100) NOT NULL,
  change_reason VARCHAR(255) NULL,
  before_json JSON NULL,
  after_json JSON NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(512) NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_logs_law_firm_occurred (law_firm_id, occurred_at),
  KEY idx_audit_logs_entity (entity_type, entity_id),
  KEY idx_audit_logs_actor (actor_user_id, occurred_at),
  CONSTRAINT fk_audit_logs_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_audit_logs_office
    FOREIGN KEY (office_id) REFERENCES offices (id),
  CONSTRAINT fk_audit_logs_actor
    FOREIGN KEY (actor_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reviews (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  entity_type VARCHAR(50) NOT NULL,
  entity_id CHAR(36) NULL,
  review_type VARCHAR(50) NOT NULL,
  review_status VARCHAR(50) NOT NULL DEFAULT 'pending',
  reviewer_user_id CHAR(36) NULL,
  ai_run_id CHAR(36) NULL,
  notes TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  PRIMARY KEY (id),
  KEY idx_reviews_case (case_id, review_status),
  KEY idx_reviews_reviewer (reviewer_user_id, review_status),
  CONSTRAINT fk_reviews_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_reviews_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_reviews_reviewer
    FOREIGN KEY (reviewer_user_id) REFERENCES users (id),
  CONSTRAINT fk_reviews_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
