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;
