CREATE TABLE telegram_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(190) NOT NULL,
  telegram_user_id BIGINT NULL,
  username VARCHAR(190) NULL,
  phone_masked VARCHAR(32) NULL,
  display_name VARCHAR(255) NULL,
  encrypted_session MEDIUMTEXT NOT NULL,
  session_key_version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  status ENUM('active','disabled','error','revoked') NOT NULL DEFAULT 'active',
  last_connected_at DATETIME NULL,
  last_error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_telegram_user_id (telegram_user_id),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE manager_account_assignments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  amo_user_id BIGINT NOT NULL,
  telegram_account_id BIGINT UNSIGNED NOT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  can_initiate TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_manager_account (amo_user_id, telegram_account_id),
  KEY idx_manager_default (amo_user_id, is_default),
  CONSTRAINT fk_assignment_account FOREIGN KEY (telegram_account_id) REFERENCES telegram_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE telegram_peers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  telegram_account_id BIGINT UNSIGNED NOT NULL,
  peer_id BIGINT NOT NULL,
  access_hash VARCHAR(64) NULL,
  username VARCHAR(190) NULL,
  phone VARCHAR(32) NULL,
  display_name VARCHAR(255) NULL,
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_account_peer (telegram_account_id, peer_id),
  KEY idx_username (username),
  KEY idx_phone (phone),
  CONSTRAINT fk_peer_account FOREIGN KEY (telegram_account_id) REFERENCES telegram_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE conversations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  external_conversation_id VARCHAR(190) NOT NULL,
  telegram_account_id BIGINT UNSIGNED NOT NULL,
  telegram_peer_id BIGINT NOT NULL,
  amo_conversation_ref_id VARCHAR(190) NULL,
  amo_contact_id BIGINT NULL,
  amo_lead_id BIGINT NULL,
  responsible_amo_user_id BIGINT NULL,
  initiated_by_us TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_external_conversation (external_conversation_id),
  UNIQUE KEY uq_account_peer_conversation (telegram_account_id, telegram_peer_id),
  KEY idx_amo_lead (amo_lead_id),
  KEY idx_responsible (responsible_amo_user_id),
  CONSTRAINT fk_conversation_account FOREIGN KEY (telegram_account_id) REFERENCES telegram_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE message_jobs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  direction ENUM('to_telegram','to_amo') NOT NULL,
  job_type ENUM('text','file','status') NOT NULL DEFAULT 'text',
  telegram_account_id BIGINT UNSIGNED NOT NULL,
  conversation_id BIGINT UNSIGNED NULL,
  payload JSON NOT NULL,
  dedupe_key VARCHAR(190) NOT NULL,
  status ENUM('pending','processing','done','retry','failed','cancelled') NOT NULL DEFAULT 'pending',
  attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  locked_at DATETIME NULL,
  locked_by VARCHAR(190) NULL,
  last_error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_job_dedupe (dedupe_key),
  KEY idx_queue (status, available_at),
  CONSTRAINT fk_job_account FOREIGN KEY (telegram_account_id) REFERENCES telegram_accounts(id) ON DELETE CASCADE,
  CONSTRAINT fk_job_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE message_map (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED NOT NULL,
  telegram_message_id BIGINT NULL,
  amo_message_id VARCHAR(190) NULL,
  direction ENUM('incoming','outgoing') NOT NULL,
  text_sha256 CHAR(64) NULL,
  sent_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_tg_message (conversation_id, telegram_message_id),
  UNIQUE KEY uq_amo_message (amo_message_id),
  CONSTRAINT fk_map_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE first_message_limits (
  telegram_account_id BIGINT UNSIGNED NOT NULL,
  date_utc DATE NOT NULL,
  messages_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_sent_at DATETIME NULL,
  PRIMARY KEY (telegram_account_id, date_utc),
  CONSTRAINT fk_limit_account FOREIGN KEY (telegram_account_id) REFERENCES telegram_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE webhook_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source ENUM('amo','telegram') NOT NULL,
  event_key VARCHAR(190) NOT NULL,
  payload JSON NOT NULL,
  processed_at DATETIME NULL,
  error TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_event (source, event_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_type ENUM('admin','manager','system','amo') NOT NULL,
  actor_id VARCHAR(190) NULL,
  action VARCHAR(190) NOT NULL,
  entity_type VARCHAR(100) NULL,
  entity_id VARCHAR(190) NULL,
  metadata JSON NULL,
  ip_address VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_entity (entity_type, entity_id),
  KEY idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;