-- neurav2 production bootstrap
-- Fresh database bootstrap for production.
-- Update the database name below if your production database uses a different name.
-- Some historical migrations are intentionally omitted here because their changes are already folded into newer base schema files.
CREATE DATABASE IF NOT EXISTS `neurav2_legal` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `neurav2_legal`;

-- ============================================================================
-- Source: 001_schema.sql
-- ============================================================================


-- -----------------------------------------------------------------------------
-- 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_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,
  title VARCHAR(255) NOT NULL,
  document_status VARCHAR(50) NOT NULL DEFAULT 'received',
  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_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_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_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;

-- ============================================================================
-- Source: 002_seed_master_data.sql
-- ============================================================================

-- -----------------------------------------------------------------------------
-- Roles
-- -----------------------------------------------------------------------------

INSERT INTO roles (code, name, description, is_system_role) VALUES
  ('admin', 'Admin', 'Full tenant administration access', 1),
  ('partner', 'Partner', 'Firm leadership with strategic and approval access', 1),
  ('attorney', 'Attorney', 'Case owner and legal reviewer', 1),
  ('paralegal', 'Paralegal', 'Operational case preparation and document management', 1),
  ('intake', 'Intake', 'Lead intake and client onboarding', 1),
  ('assistant', 'Assistant', 'Administrative support and coordination', 1),
  ('reviewer', 'Reviewer', 'Quality control and legal review', 1);

-- -----------------------------------------------------------------------------
-- Permissions
-- -----------------------------------------------------------------------------

INSERT INTO permissions (code, module_name, name, description) VALUES
  ('tenant.manage', 'tenant', 'Manage tenant', 'Manage law firm level settings and billing'),
  ('users.manage', 'users', 'Manage users', 'Invite users, assign roles and deactivate access'),
  ('clients.read', 'clients', 'Read clients', 'Read client profiles and linked data'),
  ('clients.write', 'clients', 'Write clients', 'Create and update clients and related parties'),
  ('cases.read', 'cases', 'Read cases', 'Read cases, timelines and workflow state'),
  ('cases.write', 'cases', 'Write cases', 'Create and update cases and assignments'),
  ('documents.manage', 'documents', 'Manage documents', 'Upload, classify and review documents'),
  ('workflows.manage', 'workflows', 'Manage workflows', 'Create workflow templates and activate them'),
  ('questionnaires.manage', 'questionnaires', 'Manage questionnaires', 'Launch and review intelligent questionnaires'),
  ('ai.execute', 'ai', 'Execute AI', 'Run AI extraction, gap analysis and form filling'),
  ('ai.review', 'ai', 'Review AI', 'Review AI outputs and confirm facts'),
  ('packets.manage', 'packets', 'Manage packets', 'Assemble and finalize filing packets'),
  ('audit.read', 'audit', 'Read audit', 'Read audit logs and review history');

INSERT INTO role_permissions (role_code, permission_code) VALUES
  ('admin', 'tenant.manage'),
  ('admin', 'users.manage'),
  ('admin', 'clients.read'),
  ('admin', 'clients.write'),
  ('admin', 'cases.read'),
  ('admin', 'cases.write'),
  ('admin', 'documents.manage'),
  ('admin', 'workflows.manage'),
  ('admin', 'questionnaires.manage'),
  ('admin', 'ai.execute'),
  ('admin', 'ai.review'),
  ('admin', 'packets.manage'),
  ('admin', 'audit.read'),
  ('partner', 'clients.read'),
  ('partner', 'cases.read'),
  ('partner', 'documents.manage'),
  ('partner', 'workflows.manage'),
  ('partner', 'questionnaires.manage'),
  ('partner', 'ai.execute'),
  ('partner', 'ai.review'),
  ('partner', 'packets.manage'),
  ('partner', 'audit.read'),
  ('attorney', 'clients.read'),
  ('attorney', 'clients.write'),
  ('attorney', 'cases.read'),
  ('attorney', 'cases.write'),
  ('attorney', 'documents.manage'),
  ('attorney', 'questionnaires.manage'),
  ('attorney', 'ai.execute'),
  ('attorney', 'ai.review'),
  ('attorney', 'packets.manage'),
  ('paralegal', 'clients.read'),
  ('paralegal', 'clients.write'),
  ('paralegal', 'cases.read'),
  ('paralegal', 'cases.write'),
  ('paralegal', 'documents.manage'),
  ('paralegal', 'questionnaires.manage'),
  ('paralegal', 'ai.execute'),
  ('paralegal', 'packets.manage'),
  ('intake', 'clients.read'),
  ('intake', 'clients.write'),
  ('intake', 'cases.read'),
  ('intake', 'cases.write'),
  ('intake', 'questionnaires.manage'),
  ('assistant', 'clients.read'),
  ('assistant', 'cases.read'),
  ('assistant', 'documents.manage'),
  ('reviewer', 'clients.read'),
  ('reviewer', 'cases.read'),
  ('reviewer', 'documents.manage'),
  ('reviewer', 'ai.review'),
  ('reviewer', 'audit.read');

-- -----------------------------------------------------------------------------
-- Core statuses
-- -----------------------------------------------------------------------------

INSERT INTO case_statuses (code, name, description, sort_order, is_closed_status) VALUES
  ('intake', 'Intake', 'Case is being qualified and structured', 10, 0),
  ('collecting_documents', 'Collecting Documents', 'Required evidence is still being collected', 20, 0),
  ('questionnaire_pending', 'Questionnaire Pending', 'Awaiting client responses to missing information', 30, 0),
  ('drafting', 'Drafting', 'Forms and packet are being prepared', 40, 0),
  ('attorney_review', 'Attorney Review', 'Case is under legal review', 50, 0),
  ('ready_to_file', 'Ready To File', 'Case is complete and ready for submission', 60, 0),
  ('filed', 'Filed', 'Case was filed with the target authority', 70, 0),
  ('closed', 'Closed', 'Case was completed or archived', 999, 1);

INSERT INTO questionnaire_statuses (code, name, description, is_terminal) VALUES
  ('draft', 'Draft', 'Session created but not sent yet', 0),
  ('issued', 'Issued', 'Link created and waiting for client access', 0),
  ('active', 'Active', 'Client is answering or the system is re-evaluating gaps', 0),
  ('waiting_re_evaluation', 'Waiting Re-Evaluation', 'Answer received and follow-up analysis pending', 0),
  ('completed', 'Completed', 'All mandatory gaps were resolved', 1),
  ('expired', 'Expired', 'Link/session expired', 1),
  ('cancelled', 'Cancelled', 'Session intentionally closed', 1);

-- -----------------------------------------------------------------------------
-- Repository item types
-- -----------------------------------------------------------------------------

INSERT INTO repository_item_types (code, name, description, supports_file, supports_text) VALUES
  ('email', 'Email', 'Inbound or outbound email', 0, 1),
  ('phone_call', 'Phone Call', 'Phone call summary or transcript', 0, 1),
  ('whatsapp_message', 'WhatsApp Message', 'WhatsApp conversation message', 0, 1),
  ('form_submission', 'Form Submission', 'Submitted intake or portal form', 0, 1),
  ('document', 'Document', 'Uploaded document or evidence file', 1, 1),
  ('image', 'Image', 'Image file linked to the case', 1, 0),
  ('audio', 'Audio', 'Audio file or recording', 1, 0),
  ('video', 'Video', 'Video file', 1, 0),
  ('note', 'Note', 'Internal note', 0, 1),
  ('extracted_fact', 'Extracted Fact', 'Structured fact derived from sources', 0, 1),
  ('client_profile_summary', 'Client Profile Summary', 'AI-synthesized client knowledge summary for downstream form filling', 0, 1),
  ('generated_form', 'Generated Form', 'AI or system-generated filled form', 1, 1),
  ('final_packet', 'Final Packet', 'Compiled final filing packet', 1, 1),
  ('questionnaire_answer', 'Questionnaire Answer', 'Client answer from intelligent questionnaire', 0, 1),
  ('ai_gap_analysis', 'AI Gap Analysis', 'Gap analysis run artifact', 0, 1),
  ('client_portal_chat', 'Client Portal Chat', 'Portal chat message', 0, 1);

-- -----------------------------------------------------------------------------
-- Channels
-- -----------------------------------------------------------------------------

INSERT INTO channels (code, name, description, is_external) VALUES
  ('email', 'Email', 'Email channel', 1),
  ('phone', 'Phone', 'Phone calls and voice interactions', 1),
  ('whatsapp', 'WhatsApp', 'WhatsApp messages', 1),
  ('web_form', 'Web Form', 'Website or intake form submission', 1),
  ('portal', 'Client Portal', 'Secure client portal interaction', 1),
  ('internal', 'Internal', 'Internal staff-only interaction', 0),
  ('ocr', 'OCR Pipeline', 'Automated OCR and extraction pipeline', 0),
  ('system', 'System', 'Automated system generated event', 0);

-- -----------------------------------------------------------------------------
-- Document types
-- -----------------------------------------------------------------------------

INSERT INTO document_types (code, name, description, category_code, is_identity_document, is_expirable) VALUES
  ('passport', 'Passport', 'Passport identity document', 'identity', 1, 1),
  ('birth_certificate', 'Birth Certificate', 'Civil birth certificate', 'civil_record', 1, 0),
  ('marriage_certificate', 'Marriage Certificate', 'Civil marriage certificate', 'civil_record', 0, 0),
  ('resume', 'Resume', 'Professional resume or CV', 'professional', 0, 0),
  ('employment_letter', 'Employment Letter', 'Offer letter or employment verification', 'employment', 0, 0),
  ('pay_stub', 'Pay Stub', 'Proof of current compensation', 'financial', 0, 0),
  ('tax_return', 'Tax Return', 'Income tax filing evidence', 'financial', 0, 0),
  ('visa', 'Visa', 'Visa page or approval evidence', 'immigration', 1, 1),
  ('i94', 'I-94', 'Admission record', 'immigration', 0, 0),
  ('photo', 'Photo', 'Passport-style or evidence photo', 'media', 0, 0),
  ('translation', 'Translation', 'Certified translation document', 'supporting', 0, 0),
  ('other_supporting', 'Other Supporting', 'Any additional supporting evidence', 'supporting', 0, 0);

-- -----------------------------------------------------------------------------
-- AI run types
-- -----------------------------------------------------------------------------

INSERT INTO ai_run_types (code, name, description, billable_default) VALUES
  ('extraction', 'Extraction', 'Extract structured data from documents or interactions', 1),
  ('summarization', 'Summarization', 'Summarize long-form content', 1),
  ('classification', 'Classification', 'Categorize content or case artifacts', 1),
  ('fact_consolidation', 'Fact Consolidation', 'Merge evidence into normalized facts', 1),
  ('gap_analysis', 'Gap Analysis', 'Identify missing, conflicting or low-confidence data', 1),
  ('questionnaire_generation', 'Questionnaire Generation', 'Generate the next set of client questions', 1),
  ('questionnaire_re_evaluation', 'Questionnaire Re-Evaluation', 'Re-evaluate gaps after each answer', 1),
  ('form_fill', 'Form Fill', 'Fill forms from normalized facts', 1),
  ('packet_assembly', 'Packet Assembly', 'Assemble the final case packet', 1),
  ('pre_submission_analysis', 'Pre-Submission Analysis', 'Consultive AI analysis before document review submission', 1),
  ('submission_analysis', 'Submission Analysis', 'AI analysis executed during document review submission', 1),
  ('review_assist', 'Review Assist', 'Reviewer support analysis for a submitted document version', 1),
  ('final_review_validation', 'Final Review Validation', 'Mandatory AI validation before approving a reviewed document', 1),
  ('review_comment_reply', 'Review Comment Reply', 'AI response for anchored reviewer comments on document excerpts', 1),
  ('team_agent_assistant', 'Team Agent Assistant', 'Conversational query against a team member agent memory base', 1),
  ('workspace_global_assistant', 'Workspace Global Assistant', 'Conversational query against the consolidated workspace knowledge base', 1);

-- ============================================================================
-- Source: 003_document_review_schema.sql
-- ============================================================================

INSERT IGNORE INTO ai_run_types (code, name, description, billable_default) VALUES
  ('pre_submission_analysis', 'Pre-Submission Analysis', 'Consultive AI analysis before document review submission', 1),
  ('submission_analysis', 'Submission Analysis', 'AI analysis executed during document review submission', 1),
  ('review_assist', 'Review Assist', 'Reviewer support analysis for a submitted document version', 1),
  ('final_review_validation', 'Final Review Validation', 'Mandatory AI validation before approving a reviewed document', 1),
  ('review_comment_reply', 'Review Comment Reply', 'AI response for anchored reviewer comments on document excerpts', 1);

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS 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 IF NOT EXISTS 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;

ALTER TABLE document_review_comments
  MODIFY COLUMN selection_start_offset INT NULL,
  MODIFY COLUMN selection_end_offset INT NULL;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_comments'
        AND COLUMN_NAME = 'page_number'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_comments ADD COLUMN page_number INT NULL AFTER ai_run_id'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_comments'
        AND COLUMN_NAME = 'anchor_x_ratio'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_comments ADD COLUMN anchor_x_ratio DECIMAL(8,6) NULL AFTER page_number'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_comments'
        AND COLUMN_NAME = 'anchor_y_ratio'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_comments ADD COLUMN anchor_y_ratio DECIMAL(8,6) NULL AFTER anchor_x_ratio'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_comments'
        AND COLUMN_NAME = 'selection_rects_json'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_comments ADD COLUMN selection_rects_json JSON NULL AFTER anchor_y_ratio'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================================
-- Source: 004_team_reviewer_schema.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS teams (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  name VARCHAR(150) NOT NULL,
  description VARCHAR(500) 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_teams_law_firm (law_firm_id),
  KEY idx_teams_created_by (created_by_user_id),
  CONSTRAINT fk_teams_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_teams_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_memberships (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  membership_role VARCHAR(30) NOT NULL DEFAULT 'member',
  invited_by_user_id CHAR(36) NULL,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_memberships_team_user (team_id, user_id),
  KEY idx_team_memberships_law_firm_user (law_firm_id, user_id),
  KEY idx_team_memberships_team_role (team_id, membership_role),
  KEY idx_team_memberships_invited_by (invited_by_user_id),
  CONSTRAINT fk_team_memberships_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_memberships_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_memberships_user
    FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_team_memberships_invited_by
    FOREIGN KEY (invited_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_invitations (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_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_team_invitations_team_email (team_id, invitee_email),
  KEY idx_team_invitations_law_firm_status (law_firm_id, status_code),
  KEY idx_team_invitations_invitee_status (invitee_user_id, status_code),
  KEY idx_team_invitations_invited_by (invited_by_user_id),
  CONSTRAINT fk_team_invitations_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_invitations_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_invitations_invitee_user
    FOREIGN KEY (invitee_user_id) REFERENCES users (id),
  CONSTRAINT fk_team_invitations_invited_by
    FOREIGN KEY (invited_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_agents (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  agent_name VARCHAR(180) NOT NULL,
  system_prompt TEXT NULL,
  learning_summary TEXT NULL,
  memory_count INT NOT NULL DEFAULT 0,
  last_learning_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_team_agents_team_user (team_id, user_id),
  KEY idx_team_agents_law_firm_user (law_firm_id, user_id),
  KEY idx_team_agents_team (team_id),
  CONSTRAINT fk_team_agents_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_agents_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_agents_user
    FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_agent_memories (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  agent_id CHAR(36) NOT NULL,
  source_type VARCHAR(50) NOT NULL,
  source_entity_id CHAR(36) NULL,
  memory_text TEXT NOT NULL,
  created_by_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_team_agent_memories_agent_created (agent_id, created_at),
  KEY idx_team_agent_memories_team_created (team_id, created_at),
  KEY idx_team_agent_memories_created_by (created_by_user_id),
  CONSTRAINT fk_team_agent_memories_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_agent_memories_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_agent_memories_agent
    FOREIGN KEY (agent_id) REFERENCES team_agents (id),
  CONSTRAINT fk_team_agent_memories_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND COLUMN_NAME = 'team_id'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD COLUMN team_id CHAR(36) NULL AFTER law_firm_id'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND COLUMN_NAME = 'assigned_reviewer_user_id'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD COLUMN assigned_reviewer_user_id CHAR(36) NULL AFTER latest_final_validation_confidence'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND COLUMN_NAME = 'assigned_reviewer_agent_id'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD COLUMN assigned_reviewer_agent_id CHAR(36) NULL AFTER assigned_reviewer_user_id'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND COLUMN_NAME = 'assigned_by_user_id'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD COLUMN assigned_by_user_id CHAR(36) NULL AFTER assigned_reviewer_agent_id'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND COLUMN_NAME = 'assigned_at'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD COLUMN assigned_at DATETIME NULL AFTER assigned_by_user_id'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND INDEX_NAME = 'idx_document_review_items_team'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD KEY idx_document_review_items_team (team_id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND INDEX_NAME = 'idx_document_review_items_reviewer'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD KEY idx_document_review_items_reviewer (assigned_reviewer_user_id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND INDEX_NAME = 'idx_document_review_items_reviewer_agent'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD KEY idx_document_review_items_reviewer_agent (assigned_reviewer_agent_id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND CONSTRAINT_NAME = 'fk_document_review_items_team'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD CONSTRAINT fk_document_review_items_team FOREIGN KEY (team_id) REFERENCES teams (id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND CONSTRAINT_NAME = 'fk_document_review_items_assigned_reviewer'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD CONSTRAINT fk_document_review_items_assigned_reviewer FOREIGN KEY (assigned_reviewer_user_id) REFERENCES users (id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND CONSTRAINT_NAME = 'fk_document_review_items_assigned_reviewer_agent'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD CONSTRAINT fk_document_review_items_assigned_reviewer_agent FOREIGN KEY (assigned_reviewer_agent_id) REFERENCES team_agents (id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND CONSTRAINT_NAME = 'fk_document_review_items_assigned_by'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD CONSTRAINT fk_document_review_items_assigned_by FOREIGN KEY (assigned_by_user_id) REFERENCES users (id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS document_review_assignments (
  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) NULL,
  team_id CHAR(36) NOT NULL,
  assignment_type VARCHAR(30) NOT NULL DEFAULT 'transfer',
  from_reviewer_user_id CHAR(36) NULL,
  from_reviewer_agent_id CHAR(36) NULL,
  to_reviewer_user_id CHAR(36) NOT NULL,
  to_reviewer_agent_id CHAR(36) NULL,
  assigned_by_user_id CHAR(36) NULL,
  transfer_note TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_document_review_assignments_item_created (item_id, created_at),
  KEY idx_document_review_assignments_version (version_id),
  KEY idx_document_review_assignments_to_reviewer (to_reviewer_user_id, created_at),
  CONSTRAINT fk_document_review_assignments_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_document_review_assignments_item
    FOREIGN KEY (item_id) REFERENCES document_review_items (id),
  CONSTRAINT fk_document_review_assignments_version
    FOREIGN KEY (version_id) REFERENCES document_review_versions (id),
  CONSTRAINT fk_document_review_assignments_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_document_review_assignments_from_reviewer
    FOREIGN KEY (from_reviewer_user_id) REFERENCES users (id),
  CONSTRAINT fk_document_review_assignments_from_agent
    FOREIGN KEY (from_reviewer_agent_id) REFERENCES team_agents (id),
  CONSTRAINT fk_document_review_assignments_to_reviewer
    FOREIGN KEY (to_reviewer_user_id) REFERENCES users (id),
  CONSTRAINT fk_document_review_assignments_to_agent
    FOREIGN KEY (to_reviewer_agent_id) REFERENCES team_agents (id),
  CONSTRAINT fk_document_review_assignments_assigned_by
    FOREIGN KEY (assigned_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Source: 005_team_messages_schema.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS team_message_threads (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  review_due_at DATETIME NULL,
  subject VARCHAR(255) NOT NULL,
  topic_type VARCHAR(30) NOT NULL DEFAULT 'general',
  status_code VARCHAR(30) NOT NULL DEFAULT 'open',
  participant_a_user_id CHAR(36) NOT NULL,
  participant_b_user_id CHAR(36) NOT NULL,
  created_by_user_id CHAR(36) NULL,
  closed_by_user_id CHAR(36) NULL,
  closed_at DATETIME NULL,
  last_message_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_team_message_threads_team_last (team_id, last_message_at),
  KEY idx_team_message_threads_team_status_last (team_id, status_code, last_message_at),
  KEY idx_team_message_threads_participant_a (participant_a_user_id, created_at),
  KEY idx_team_message_threads_participant_b (participant_b_user_id, created_at),
  KEY idx_team_message_threads_topic (topic_type),
  KEY idx_team_message_threads_case (case_id),
  KEY idx_team_message_threads_review_due (review_due_at),
  CONSTRAINT fk_team_message_threads_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_threads_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_threads_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_team_message_threads_participant_a
    FOREIGN KEY (participant_a_user_id) REFERENCES users (id),
  CONSTRAINT fk_team_message_threads_participant_b
    FOREIGN KEY (participant_b_user_id) REFERENCES users (id),
  CONSTRAINT fk_team_message_threads_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_team_message_threads_closed_by
    FOREIGN KEY (closed_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_message_thread_participants (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  added_by_user_id CHAR(36) NULL,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_message_thread_participants_thread_user (thread_id, user_id),
  KEY idx_team_message_thread_participants_user_joined (user_id, joined_at),
  KEY idx_team_message_thread_participants_team_thread (team_id, thread_id),
  KEY idx_team_message_thread_participants_added_by (added_by_user_id),
  CONSTRAINT fk_team_message_thread_participants_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_thread_participants_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_thread_participants_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_message_thread_participants_user
    FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_team_message_thread_participants_added_by
    FOREIGN KEY (added_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_messages (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  sequence_number BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  sender_user_id CHAR(36) NOT NULL,
  body_text LONGTEXT NOT NULL,
  replied_message_id CHAR(36) NULL,
  attachment_file_id CHAR(36) NULL,
  review_source_file_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_messages_sequence (sequence_number),
  KEY idx_team_messages_thread_created (thread_id, created_at),
  KEY idx_team_messages_sender_created (sender_user_id, created_at),
  KEY idx_team_messages_replied_message (replied_message_id),
  KEY idx_team_messages_attachment_file (attachment_file_id),
  KEY idx_team_messages_review_source_file (review_source_file_id),
  CONSTRAINT fk_team_messages_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_messages_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_messages_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_messages_sender
    FOREIGN KEY (sender_user_id) REFERENCES users (id),
  CONSTRAINT fk_team_messages_replied_message
    FOREIGN KEY (replied_message_id) REFERENCES team_messages (id)
    ON DELETE SET NULL,
  CONSTRAINT fk_team_messages_attachment_file
    FOREIGN KEY (attachment_file_id) REFERENCES files (id)
    ON DELETE SET NULL,
  CONSTRAINT fk_team_messages_review_source_file
    FOREIGN KEY (review_source_file_id) REFERENCES files (id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_message_thread_reads (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  last_read_message_id CHAR(36) NULL,
  last_read_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_team_message_thread_reads_thread_user (thread_id, user_id),
  KEY idx_team_message_thread_reads_user_team_read (user_id, team_id, last_read_at),
  KEY idx_team_message_thread_reads_team_thread (team_id, thread_id),
  CONSTRAINT fk_team_message_thread_reads_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_thread_reads_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_thread_reads_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_message_thread_reads_user
    FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_message_attachment_comments (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  message_id CHAR(36) NOT NULL,
  attachment_file_id CHAR(36) NOT 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,
  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_team_message_attachment_comments_attachment_created (attachment_file_id, created_at),
  KEY idx_team_message_attachment_comments_thread_created (thread_id, created_at),
  KEY idx_team_message_attachment_comments_message (message_id),
  KEY idx_team_message_attachment_comments_created_by (created_by_user_id),
  CONSTRAINT fk_team_message_attachment_comments_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_attachment_comments_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_attachment_comments_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_message_attachment_comments_message
    FOREIGN KEY (message_id) REFERENCES team_messages (id),
  CONSTRAINT fk_team_message_attachment_comments_attachment
    FOREIGN KEY (attachment_file_id) REFERENCES files (id),
  CONSTRAINT fk_team_message_attachment_comments_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Source: 006_multi_team_support.sql
-- ============================================================================

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'teams'
        AND INDEX_NAME = 'idx_teams_law_firm'
    ),
    'SELECT 1',
    'ALTER TABLE teams ADD KEY idx_teams_law_firm (law_firm_id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'teams'
        AND INDEX_NAME = 'uq_teams_law_firm'
    ),
    'ALTER TABLE teams DROP INDEX uq_teams_law_firm',
    'SELECT 1'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'team_memberships'
        AND INDEX_NAME = 'idx_team_memberships_law_firm_user'
    ),
    'SELECT 1',
    'ALTER TABLE team_memberships ADD KEY idx_team_memberships_law_firm_user (law_firm_id, user_id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'team_memberships'
        AND INDEX_NAME = 'uq_team_memberships_law_firm_user'
    ),
    'ALTER TABLE team_memberships DROP INDEX uq_team_memberships_law_firm_user',
    'SELECT 1'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'team_agents'
        AND INDEX_NAME = 'idx_team_agents_law_firm_user'
    ),
    'SELECT 1',
    'ALTER TABLE team_agents ADD KEY idx_team_agents_law_firm_user (law_firm_id, user_id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'team_agents'
        AND INDEX_NAME = 'uq_team_agents_law_firm_user'
    ),
    'ALTER TABLE team_agents DROP INDEX uq_team_agents_law_firm_user',
    'SELECT 1'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================================================
-- Source: 007_workspace_memberships.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS workspace_memberships (
  id CHAR(36) NOT NULL,
  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;

INSERT INTO workspace_memberships (
  id,
  law_firm_id,
  user_id,
  membership_role,
  is_default,
  created_by_user_id,
  joined_at
)
SELECT
  UUID(),
  u.law_firm_id,
  u.id,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM user_roles ur
      WHERE ur.user_id = u.id
        AND ur.role_code = 'admin'
    ) THEN 'owner'
    ELSE 'member'
  END,
  1,
  u.id,
  u.created_at
FROM users u
WHERE u.deleted_at IS NULL
  AND u.law_firm_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM workspace_memberships wm
    WHERE wm.law_firm_id = u.law_firm_id
      AND wm.user_id = u.id
  );

-- ============================================================================
-- Source: 008_workspace_invites_and_nullable_user_workspace.sql
-- ============================================================================

ALTER TABLE users
  MODIFY COLUMN law_firm_id CHAR(36) NULL;

CREATE TABLE IF NOT EXISTS workspace_invitations (
  id CHAR(36) NOT NULL,
  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;

-- ============================================================================
-- Skipped in bootstrap: 009_close_team_message_threads.sql
-- Reason: already incorporated into the newer base team messaging schema.
-- ============================================================================

-- ============================================================================
-- Source: 010_user_avatars.sql
-- ============================================================================

SET @avatar_url_column_exists := (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'users'
    AND COLUMN_NAME = 'avatar_url'
);

SET @avatar_url_statement := IF(
  @avatar_url_column_exists = 0,
  'ALTER TABLE users ADD COLUMN avatar_url VARCHAR(2048) NULL AFTER display_name',
  'SELECT 1'
);

PREPARE avatar_url_stmt FROM @avatar_url_statement;
EXECUTE avatar_url_stmt;
DEALLOCATE PREPARE avatar_url_stmt;

-- ============================================================================
-- Source: 011_team_message_thread_participants.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS team_message_thread_participants (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  added_by_user_id CHAR(36) NULL,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_message_thread_participants_thread_user (thread_id, user_id),
  KEY idx_team_message_thread_participants_user_joined (user_id, joined_at),
  KEY idx_team_message_thread_participants_team_thread (team_id, thread_id),
  KEY idx_team_message_thread_participants_added_by (added_by_user_id),
  CONSTRAINT fk_team_message_thread_participants_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_thread_participants_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_thread_participants_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_message_thread_participants_user
    FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_team_message_thread_participants_added_by
    FOREIGN KEY (added_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO team_message_thread_participants (
  id,
  law_firm_id,
  team_id,
  thread_id,
  user_id,
  added_by_user_id,
  joined_at
)
SELECT
  UUID(),
  tmt.law_firm_id,
  tmt.team_id,
  tmt.id,
  tmt.participant_a_user_id,
  tmt.created_by_user_id,
  tmt.created_at
FROM team_message_threads tmt
LEFT JOIN team_message_thread_participants existing
  ON existing.thread_id = tmt.id
 AND existing.user_id = tmt.participant_a_user_id
WHERE existing.id IS NULL;

INSERT INTO team_message_thread_participants (
  id,
  law_firm_id,
  team_id,
  thread_id,
  user_id,
  added_by_user_id,
  joined_at
)
SELECT
  UUID(),
  tmt.law_firm_id,
  tmt.team_id,
  tmt.id,
  tmt.participant_b_user_id,
  tmt.created_by_user_id,
  tmt.created_at
FROM team_message_threads tmt
LEFT JOIN team_message_thread_participants existing
  ON existing.thread_id = tmt.id
 AND existing.user_id = tmt.participant_b_user_id
WHERE existing.id IS NULL;

-- ============================================================================
-- Source: 012_team_message_thread_reads.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS team_message_thread_reads (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  last_read_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_team_message_thread_reads_thread_user (thread_id, user_id),
  KEY idx_team_message_thread_reads_user_team_read (user_id, team_id, last_read_at),
  KEY idx_team_message_thread_reads_team_thread (team_id, thread_id),
  CONSTRAINT fk_team_message_thread_reads_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_thread_reads_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_thread_reads_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_message_thread_reads_user
    FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO team_message_thread_reads (
  id,
  law_firm_id,
  team_id,
  thread_id,
  user_id,
  last_read_at,
  created_at,
  updated_at
)
SELECT
  UUID(),
  tmtp.law_firm_id,
  tmtp.team_id,
  tmtp.thread_id,
  tmtp.user_id,
  COALESCE(tmt.last_message_at, tmt.created_at),
  CURRENT_TIMESTAMP,
  CURRENT_TIMESTAMP
FROM team_message_thread_participants tmtp
INNER JOIN team_message_threads tmt ON tmt.id = tmtp.thread_id
LEFT JOIN team_message_thread_reads existing
  ON existing.thread_id = tmtp.thread_id
 AND existing.user_id = tmtp.user_id
WHERE existing.id IS NULL;

-- ============================================================================
-- Skipped in bootstrap: 013_team_message_thread_read_markers.sql
-- Reason: already incorporated into the newer base team messaging schema.
-- ============================================================================

-- ============================================================================
-- Skipped in bootstrap: 014_team_message_attachments.sql
-- Reason: already incorporated into the newer base team messaging schema.
-- ============================================================================

-- ============================================================================
-- Skipped in bootstrap: 015_team_message_replies.sql
-- Reason: already incorporated into the newer base team messaging schema.
-- ============================================================================

-- ============================================================================
-- Skipped in bootstrap: 016_team_message_sequence.sql
-- Reason: already incorporated into the newer base team messaging schema.
-- ============================================================================

-- ============================================================================
-- Skipped in bootstrap: 017_team_message_thread_cases.sql
-- Reason: already incorporated into the newer base team messaging schema.
-- ============================================================================

-- ============================================================================
-- Source: 018_team_message_attachment_comments.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS team_message_attachment_comments (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  thread_id CHAR(36) NOT NULL,
  message_id CHAR(36) NOT NULL,
  attachment_file_id CHAR(36) NOT 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,
  created_by_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_team_message_attachment_comments_attachment_created (attachment_file_id, created_at),
  KEY idx_team_message_attachment_comments_thread_created (thread_id, created_at),
  KEY idx_team_message_attachment_comments_message (message_id),
  KEY idx_team_message_attachment_comments_created_by (created_by_user_id),
  CONSTRAINT fk_team_message_attachment_comments_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_message_attachment_comments_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_message_attachment_comments_thread
    FOREIGN KEY (thread_id) REFERENCES team_message_threads (id),
  CONSTRAINT fk_team_message_attachment_comments_message
    FOREIGN KEY (message_id) REFERENCES team_messages (id),
  CONSTRAINT fk_team_message_attachment_comments_attachment
    FOREIGN KEY (attachment_file_id) REFERENCES files (id),
  CONSTRAINT fk_team_message_attachment_comments_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Skipped in bootstrap: 019_team_message_thread_review_due.sql
-- Reason: already incorporated into the newer base team messaging schema.
-- ============================================================================

-- ============================================================================
-- Skipped in bootstrap: 020_team_message_editable_attachment_comments.sql
-- Reason: already incorporated into the newer base team messaging schema.
-- ============================================================================

-- ============================================================================
-- Source: 021_team_agent_ai_run_types.sql
-- ============================================================================

INSERT IGNORE INTO ai_run_types (code, name, description, billable_default) VALUES
  ('team_agent_assistant', 'Team Agent Assistant', 'Conversational query against a team member agent memory base', 1),
  ('workspace_global_assistant', 'Workspace Global Assistant', 'Conversational query against the consolidated workspace knowledge base', 1);

-- ============================================================================
-- Source: 022_kommo_leads.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS kommo_leads (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  kommo_lead_id VARCHAR(191) NOT NULL,
  kommo_contact_id VARCHAR(191) NULL,
  linked_client_id CHAR(36) NULL,
  linked_case_id CHAR(36) NULL,
  conversation_id CHAR(36) NULL,
  display_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NULL,
  phone VARCHAR(50) NULL,
  status_code VARCHAR(30) NOT NULL DEFAULT 'unlinked',
  source_code VARCHAR(50) NULL,
  external_thread_id VARCHAR(191) NULL,
  salesbot_return_url VARCHAR(2048) NULL,
  last_message_preview TEXT NULL,
  last_message_at DATETIME NULL,
  unread_message_count INT NOT NULL DEFAULT 0,
  metadata_json JSON NULL,
  linked_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_kommo_leads_law_firm_lead (law_firm_id, kommo_lead_id),
  KEY idx_kommo_leads_status (law_firm_id, status_code, last_message_at),
  KEY idx_kommo_leads_client (linked_client_id),
  KEY idx_kommo_leads_conversation (conversation_id),
  CONSTRAINT fk_kommo_leads_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_kommo_leads_client
    FOREIGN KEY (linked_client_id) REFERENCES clients (id),
  CONSTRAINT fk_kommo_leads_case
    FOREIGN KEY (linked_case_id) REFERENCES cases (id),
  CONSTRAINT fk_kommo_leads_conversation
    FOREIGN KEY (conversation_id) REFERENCES conversations (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kommo_lead_messages (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  kommo_lead_record_id CHAR(36) NOT NULL,
  conversation_id CHAR(36) NULL,
  repository_item_id CHAR(36) NULL,
  external_message_id VARCHAR(191) NULL,
  direction_code VARCHAR(30) NOT NULL,
  body_text LONGTEXT NOT NULL,
  sender_name VARCHAR(255) NULL,
  sender_address VARCHAR(255) NULL,
  recipient_address VARCHAR(255) NULL,
  message_status VARCHAR(50) NULL,
  raw_payload_json JSON NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_kommo_lead_messages_external (law_firm_id, external_message_id),
  KEY idx_kommo_lead_messages_lead (kommo_lead_record_id, occurred_at),
  KEY idx_kommo_lead_messages_conversation (conversation_id, occurred_at),
  CONSTRAINT fk_kommo_lead_messages_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_kommo_lead_messages_lead
    FOREIGN KEY (kommo_lead_record_id) REFERENCES kommo_leads (id),
  CONSTRAINT fk_kommo_lead_messages_conversation
    FOREIGN KEY (conversation_id) REFERENCES conversations (id),
  CONSTRAINT fk_kommo_lead_messages_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Source: 023_kommo_outbound_deliveries.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS kommo_outbound_deliveries (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  kommo_lead_record_id CHAR(36) NULL,
  conversation_id CHAR(36) NOT NULL,
  repository_item_id CHAR(36) NULL,
  delivery_channel VARCHAR(30) NOT NULL DEFAULT 'salesbot_return_url',
  return_url VARCHAR(2048) NULL,
  payload_json JSON NOT NULL,
  status_code VARCHAR(40) NOT NULL DEFAULT 'pending',
  attempt_count INT NOT NULL DEFAULT 0,
  max_attempts INT NOT NULL DEFAULT 5,
  next_retry_at DATETIME NULL,
  last_attempt_at DATETIME NULL,
  delivered_at DATETIME NULL,
  last_error_code VARCHAR(50) NULL,
  last_error_message 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_kommo_outbound_deliveries_status (law_firm_id, status_code, next_retry_at),
  KEY idx_kommo_outbound_deliveries_conversation (conversation_id, created_at),
  KEY idx_kommo_outbound_deliveries_repository (repository_item_id),
  CONSTRAINT fk_kommo_outbound_deliveries_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_kommo_outbound_deliveries_lead
    FOREIGN KEY (kommo_lead_record_id) REFERENCES kommo_leads (id),
  CONSTRAINT fk_kommo_outbound_deliveries_conversation
    FOREIGN KEY (conversation_id) REFERENCES conversations (id),
  CONSTRAINT fk_kommo_outbound_deliveries_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Source: 024_workspace_document_library.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS workspace_document_library_items (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  source_code VARCHAR(50) NOT NULL DEFAULT 'manual',
  source_reference VARCHAR(191) NULL,
  document_type_code VARCHAR(50) NOT NULL,
  requirement_code VARCHAR(100) NOT NULL,
  display_name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  ai_instructions TEXT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 1,
  minimum_quantity 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_workspace_document_library_source (
    law_firm_id,
    source_code,
    source_reference
  ),
  KEY idx_workspace_document_library_law_firm (law_firm_id, display_name),
  KEY idx_workspace_document_library_document_type (document_type_code),
  CONSTRAINT fk_workspace_document_library_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_workspace_document_library_document_type
    FOREIGN KEY (document_type_code) REFERENCES document_types (code),
  CONSTRAINT fk_workspace_document_library_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Source: 025_law_firm_kommo_settings.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS law_firm_kommo_settings (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  subdomain VARCHAR(120) NULL,
  encrypted_access_token VARBINARY(4096) NULL COMMENT 'Ciphertext only; never plaintext',
  access_token_last4 CHAR(4) NULL,
  encrypted_webhook_secret VARBINARY(4096) NULL COMMENT 'Ciphertext only; never plaintext',
  webhook_secret_last4 CHAR(4) NULL,
  encrypted_integration_secret_key VARBINARY(4096) NULL COMMENT 'Ciphertext only; never plaintext',
  integration_secret_key_last4 CHAR(4) NULL,
  encryption_key_version VARCHAR(50) NOT NULL DEFAULT 'v1',
  is_active TINYINT(1) 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_kommo_settings_law_firm (law_firm_id),
  KEY idx_kommo_settings_law_firm (law_firm_id, is_active),
  CONSTRAINT fk_kommo_settings_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_kommo_settings_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Source: 026_workflow_template_builder_drafts.sql
-- ============================================================================

ALTER TABLE workflow_templates
  ADD COLUMN builder_draft_json LONGTEXT NULL AFTER updated_at,
  ADD COLUMN builder_draft_saved_at DATETIME NULL AFTER builder_draft_json;

-- ============================================================================
-- Source: 027_workflow_template_component_versioning.sql
-- ============================================================================

ALTER TABLE workflow_steps
  ADD COLUMN retired_at DATETIME NULL AFTER updated_at,
  ADD COLUMN active_scope_key VARCHAR(40)
    GENERATED ALWAYS AS (IF(retired_at IS NULL, 'ACTIVE', id)) STORED AFTER retired_at,
  DROP INDEX uq_workflow_steps_code,
  DROP INDEX uq_workflow_steps_order,
  ADD UNIQUE KEY uq_workflow_steps_code (workflow_template_id, step_code, active_scope_key),
  ADD UNIQUE KEY uq_workflow_steps_order (workflow_template_id, step_order, active_scope_key),
  ADD KEY idx_workflow_steps_retired (workflow_template_id, retired_at);

ALTER TABLE workflow_required_documents
  ADD COLUMN retired_at DATETIME NULL AFTER created_at,
  ADD COLUMN active_scope_key VARCHAR(40)
    GENERATED ALWAYS AS (IF(retired_at IS NULL, 'ACTIVE', id)) STORED AFTER retired_at,
  DROP INDEX uq_workflow_required_documents_code,
  ADD UNIQUE KEY uq_workflow_required_documents_code (workflow_template_id, requirement_code, active_scope_key),
  ADD KEY idx_workflow_required_documents_retired (workflow_template_id, retired_at);

ALTER TABLE workflow_required_forms
  ADD COLUMN retired_at DATETIME NULL AFTER created_at,
  ADD COLUMN active_scope_key VARCHAR(40)
    GENERATED ALWAYS AS (IF(retired_at IS NULL, 'ACTIVE', id)) STORED AFTER retired_at,
  DROP INDEX uq_workflow_required_forms_code,
  ADD UNIQUE KEY uq_workflow_required_forms_code (workflow_template_id, requirement_code, active_scope_key),
  ADD KEY idx_workflow_required_forms_retired (workflow_template_id, retired_at);

ALTER TABLE packet_template_items
  ADD COLUMN retired_at DATETIME NULL AFTER created_at,
  ADD COLUMN active_scope_key VARCHAR(40)
    GENERATED ALWAYS AS (IF(retired_at IS NULL, 'ACTIVE', id)) STORED AFTER retired_at,
  DROP INDEX uq_packet_template_items_order,
  ADD UNIQUE KEY uq_packet_template_items_order (packet_template_id, sort_order, active_scope_key),
  ADD KEY idx_packet_template_items_retired (packet_template_id, retired_at);

-- ============================================================================
-- Source: 028_case_archiving.sql
-- ============================================================================

ALTER TABLE cases
  ADD COLUMN archived_at DATETIME NULL AFTER created_by_user_id,
  ADD COLUMN archived_by_user_id CHAR(36) NULL AFTER archived_at,
  ADD KEY idx_cases_archived (law_firm_id, archived_at, status_code),
  ADD CONSTRAINT fk_cases_archived_by
    FOREIGN KEY (archived_by_user_id) REFERENCES users (id);

-- ============================================================================
-- Source: 029_repository_folder_upload_jobs.sql
-- ============================================================================

CREATE TABLE repository_folder_upload_jobs (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  created_by_user_id CHAR(36) NULL,
  fallback_document_type_code VARCHAR(50) NULL,
  status_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  stage_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  total_files INT UNSIGNED NOT NULL DEFAULT 0,
  processed_files INT UNSIGNED NOT NULL DEFAULT 0,
  succeeded_files INT UNSIGNED NOT NULL DEFAULT 0,
  failed_files INT UNSIGNED NOT NULL DEFAULT 0,
  extracted_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  synced_client_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  classification_ai_count INT UNSIGNED NOT NULL DEFAULT 0,
  classification_heuristic_count INT UNSIGNED NOT NULL DEFAULT 0,
  classification_fallback_count INT UNSIGNED NOT NULL DEFAULT 0,
  current_file_name VARCHAR(255) NULL,
  consolidation_status_code VARCHAR(40) NULL,
  consolidation_ai_run_id CHAR(36) NULL,
  consolidated_fact_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_message LONGTEXT NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_repository_folder_upload_jobs_law_firm (law_firm_id, created_at),
  KEY idx_repository_folder_upload_jobs_client (client_id, created_at),
  KEY idx_repository_folder_upload_jobs_case (case_id, created_at),
  KEY idx_repository_folder_upload_jobs_status (law_firm_id, status_code, updated_at),
  CONSTRAINT fk_repository_folder_upload_jobs_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_repository_folder_upload_jobs_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_repository_folder_upload_jobs_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_repository_folder_upload_jobs_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_repository_folder_upload_jobs_fallback_type
    FOREIGN KEY (fallback_document_type_code) REFERENCES document_types (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE repository_folder_upload_job_files (
  id CHAR(36) NOT NULL,
  job_id CHAR(36) NOT NULL,
  sort_order INT UNSIGNED NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  relative_path VARCHAR(1024) NULL,
  title VARCHAR(255) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  staging_storage_provider VARCHAR(50) NULL,
  staging_storage_bucket VARCHAR(191) NULL,
  staging_object_key VARCHAR(512) NULL,
  staging_storage_region VARCHAR(100) NULL,
  staging_original_file_name VARCHAR(255) NULL,
  status_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  document_type_code VARCHAR(50) NULL,
  classification_source VARCHAR(40) NULL,
  classification_confidence DECIMAL(5,4) NULL,
  classification_reason LONGTEXT NULL,
  repository_item_id CHAR(36) NULL,
  file_id CHAR(36) NULL,
  document_record_id CHAR(36) NULL,
  extracted_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  synced_client_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_message LONGTEXT NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_repository_folder_upload_job_files_order (job_id, sort_order),
  KEY idx_repository_folder_upload_job_files_job (job_id, status_code, sort_order),
  KEY idx_repository_folder_upload_job_files_repository_item (repository_item_id),
  KEY idx_repository_folder_upload_job_files_file (file_id),
  KEY idx_repository_folder_upload_job_files_document_record (document_record_id),
  CONSTRAINT fk_repository_folder_upload_job_files_job
    FOREIGN KEY (job_id) REFERENCES repository_folder_upload_jobs (id),
  CONSTRAINT fk_repository_folder_upload_job_files_document_type
    FOREIGN KEY (document_type_code) REFERENCES document_types (code),
  CONSTRAINT fk_repository_folder_upload_job_files_repository_item
    FOREIGN KEY (repository_item_id) REFERENCES repository_items (id),
  CONSTRAINT fk_repository_folder_upload_job_files_file
    FOREIGN KEY (file_id) REFERENCES files (id),
  CONSTRAINT fk_repository_folder_upload_job_files_document_record
    FOREIGN KEY (document_record_id) REFERENCES document_records (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Source: 030_form_template_agent_prompt.sql
-- ============================================================================

ALTER TABLE form_templates
  ADD COLUMN agent_code VARCHAR(100) NULL AFTER description,
  ADD COLUMN agent_prompt LONGTEXT NULL AFTER agent_code;

ALTER TABLE form_templates
  ADD KEY idx_form_templates_agent_code (agent_code);

-- ============================================================================
-- Source: 031_specialized_form_generation_jobs.sql
-- ============================================================================

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

-- Source: 032_repository_document_reprocess_jobs.sql

CREATE TABLE repository_document_reprocess_jobs (
  id CHAR(36) NOT NULL,
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  created_by_user_id CHAR(36) NULL,
  status_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  stage_code VARCHAR(60) NOT NULL DEFAULT 'queued',
  total_documents INT UNSIGNED NOT NULL DEFAULT 0,
  processed_documents INT UNSIGNED NOT NULL DEFAULT 0,
  succeeded_documents INT UNSIGNED NOT NULL DEFAULT 0,
  failed_documents INT UNSIGNED NOT NULL DEFAULT 0,
  extracted_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  synced_client_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  current_document_record_id CHAR(36) NULL,
  current_document_title VARCHAR(255) NULL,
  synthesis_status_code VARCHAR(40) NULL,
  synthesis_repository_item_id CHAR(36) NULL,
  synthesized_profile_fact_count INT UNSIGNED NOT NULL DEFAULT 0,
  synthesized_knowledge_fact_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_message LONGTEXT NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_repository_document_reprocess_jobs_law_firm (law_firm_id, created_at),
  KEY idx_repository_document_reprocess_jobs_client (client_id, created_at),
  KEY idx_repository_document_reprocess_jobs_case (case_id, created_at),
  KEY idx_repository_document_reprocess_jobs_status (law_firm_id, status_code, updated_at),
  CONSTRAINT fk_repository_document_reprocess_jobs_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_repository_document_reprocess_jobs_client
    FOREIGN KEY (client_id) REFERENCES clients (id),
  CONSTRAINT fk_repository_document_reprocess_jobs_case
    FOREIGN KEY (case_id) REFERENCES cases (id),
  CONSTRAINT fk_repository_document_reprocess_jobs_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id),
  CONSTRAINT fk_repository_document_reprocess_jobs_current_document
    FOREIGN KEY (current_document_record_id) REFERENCES document_records (id),
  CONSTRAINT fk_repository_document_reprocess_jobs_synthesis_repository_item
    FOREIGN KEY (synthesis_repository_item_id) REFERENCES repository_items (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE repository_document_reprocess_job_documents (
  id CHAR(36) NOT NULL,
  job_id CHAR(36) NOT NULL,
  document_record_id CHAR(36) NOT NULL,
  sort_order INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  document_type_code VARCHAR(50) NOT NULL,
  status_code VARCHAR(40) NOT NULL DEFAULT 'queued',
  extracted_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  synced_client_field_count INT UNSIGNED NOT NULL DEFAULT 0,
  ai_run_id CHAR(36) NULL,
  extraction_id CHAR(36) NULL,
  summary_text LONGTEXT NULL,
  extracted_preview_json JSON NULL,
  error_message LONGTEXT NULL,
  started_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_repository_document_reprocess_job_documents_order (job_id, sort_order),
  UNIQUE KEY uq_repository_document_reprocess_job_documents_document (job_id, document_record_id),
  KEY idx_repository_document_reprocess_job_documents_job (job_id, status_code, sort_order),
  KEY idx_repository_document_reprocess_job_documents_ai_run (ai_run_id),
  KEY idx_repository_document_reprocess_job_documents_extraction (extraction_id),
  CONSTRAINT fk_repository_document_reprocess_job_documents_job
    FOREIGN KEY (job_id) REFERENCES repository_document_reprocess_jobs (id),
  CONSTRAINT fk_repository_document_reprocess_job_documents_document
    FOREIGN KEY (document_record_id) REFERENCES document_records (id),
  CONSTRAINT fk_repository_document_reprocess_job_documents_document_type
    FOREIGN KEY (document_type_code) REFERENCES document_types (code),
  CONSTRAINT fk_repository_document_reprocess_job_documents_ai_run
    FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id),
  CONSTRAINT fk_repository_document_reprocess_job_documents_extraction
    FOREIGN KEY (extraction_id) REFERENCES document_extractions (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE fact_review_feedback (
  id CHAR(36) NOT NULL PRIMARY KEY,
  law_firm_id CHAR(36) NOT NULL,
  client_id CHAR(36) NOT NULL,
  case_id CHAR(36) NULL,
  case_fact_id CHAR(36) NULL,
  data_field_id CHAR(36) NOT NULL,
  review_action VARCHAR(30) NOT NULL,
  previous_raw_value TEXT NULL,
  previous_normalized_hash CHAR(64) NULL,
  corrected_raw_value TEXT NULL,
  corrected_normalized_hash CHAR(64) NULL,
  source_hint TEXT NULL,
  search_hint TEXT NULL,
  feedback_note 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 TIMESTAMP NULL DEFAULT NULL,
  KEY idx_fact_review_feedback_case_fact (case_fact_id, created_at),
  KEY idx_fact_review_feedback_case (case_id, created_at),
  KEY idx_fact_review_feedback_client_field (client_id, data_field_id, created_at),
  KEY idx_fact_review_feedback_action (review_action, created_at),
  KEY idx_fact_review_feedback_prev_hash (data_field_id, previous_normalized_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
