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;
