
CREATE TABLE IF NOT EXISTS bot_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  chat_id BIGINT NOT NULL UNIQUE,
  user_id BIGINT NULL,
  name VARCHAR(255) NULL,
  username VARCHAR(255) NULL,
  first_seen_at DATETIME NOT NULL,
  last_seen_at DATETIME NOT NULL,
  message_count INT NOT NULL DEFAULT 0,
  last_message_text TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pricing_leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  chat_id BIGINT NOT NULL,
  user_id BIGINT NULL,
  name VARCHAR(255) NULL,
  phone VARCHAR(80) NULL,
  model_id INT NULL,
  model_name VARCHAR(255) NULL,
  capacity_id INT NULL,
  capacity_label VARCHAR(50) NULL,
  registry_label VARCHAR(100) NULL,
  sim_label VARCHAR(100) NULL,
  battery_label VARCHAR(100) NULL,
  cycle_count INT NULL,
  body_label VARCHAR(150) NULL,
  body_deduction BIGINT NOT NULL DEFAULT 0,
  screen_label VARCHAR(150) NULL,
  screen_deduction BIGINT NOT NULL DEFAULT 0,
  base_price BIGINT NULL,
  final_price BIGINT NULL,
  price_found TINYINT(1) NOT NULL DEFAULT 0,
  status VARCHAR(50) NOT NULL DEFAULT 'new',
  created_at DATETIME NOT NULL,
  INDEX idx_created_at (created_at),
  INDEX idx_model (model_id),
  INDEX idx_chat (chat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bot_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_type VARCHAR(80) NOT NULL,
  chat_id BIGINT NULL,
  user_id BIGINT NULL,
  meta_json TEXT NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_event_created (event_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
