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;
