CREATE TABLE teleshop_user (
  id bigint,
  is_bot tinyint(1) DEFAULT 0,
  first_name char(255) NOT NULL DEFAULT '',
  last_name char(255) DEFAULT NULL,
  username char(191) DEFAULT NULL,
  language_code char(10) DEFAULT NULL,
  is_premium tinyint(1) DEFAULT 0,
  added_to_attachment_menu tinyint(1) DEFAULT 0,
  created_at timestamp NULL DEFAULT NULL,
  updated_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_chat (
  id bigint,
  type ENUM('private', 'group', 'supergroup', 'channel') NOT NULL,
  title char(255) DEFAULT '',
  username char(255) DEFAULT NULL,
  first_name char(255) DEFAULT NULL,
  last_name char(255) DEFAULT NULL,
  is_forum tinyint(1) DEFAULT 0,
  all_members_are_administrators tinyint(1) DEFAULT 0,
  created_at timestamp NULL DEFAULT NULL,
  updated_at timestamp NULL DEFAULT NULL,
  old_id bigint DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY old_id (old_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_user_chat (
  user_id bigint,
  chat_id bigint,

  PRIMARY KEY  (user_id, chat_id),

  FOREIGN KEY (user_id) REFERENCES teleshop_user (id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (chat_id) REFERENCES teleshop_chat (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_message_reaction (
  id bigint UNSIGNED AUTO_INCREMENT,
  chat_id bigint,
  message_id bigint,
  user_id bigint NULL,
  actor_chat_id bigint NULL,
  old_reaction text NOT NULL,
  new_reaction text NOT NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY chat_id (chat_id),
  KEY user_id (user_id),
  KEY actor_chat_id (actor_chat_id),

  FOREIGN KEY (chat_id) REFERENCES teleshop_chat (id),
  FOREIGN KEY (user_id) REFERENCES teleshop_user (id),
  FOREIGN KEY (actor_chat_id) REFERENCES teleshop_chat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_message_reaction_count (
  id bigint UNSIGNED AUTO_INCREMENT,
  chat_id bigint,
  message_id bigint,
  reactions text NOT NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY chat_id (chat_id),

  FOREIGN KEY (chat_id) REFERENCES teleshop_chat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_inline_query (
  id bigint UNSIGNED,
  user_id bigint NULL,
  location char(255) NULL DEFAULT NULL,
  query text NOT NULL,
  offset char(255) NULL DEFAULT NULL,
  chat_type char(255) NULL DEFAULT NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY user_id (user_id),

  FOREIGN KEY (user_id) REFERENCES teleshop_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_chosen_inline_result (
  id bigint UNSIGNED AUTO_INCREMENT,
  result_id char(255) NOT NULL DEFAULT '',
  user_id bigint NULL,
  location char(255) NULL DEFAULT NULL,
  inline_message_id char(255) NULL DEFAULT NULL,
  query text NOT NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY user_id (user_id),

  FOREIGN KEY (user_id) REFERENCES teleshop_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_message (
  chat_id bigint,
  sender_chat_id bigint,
  id bigint UNSIGNED,
  message_thread_id bigint(20) DEFAULT NULL,
  user_id bigint NULL,
  sender_boost_count bigint NULL,
  date timestamp NULL DEFAULT NULL,
  forward_from bigint NULL DEFAULT NULL,
  forward_from_chat bigint NULL DEFAULT NULL,
  forward_from_message_id bigint NULL DEFAULT NULL,
  forward_signature text NULL DEFAULT NULL,
  forward_sender_name text NULL DEFAULT NULL,
  forward_date timestamp NULL DEFAULT NULL,
  is_topic_message tinyint(1) DEFAULT 0,
  is_automatic_forward tinyint(1) DEFAULT 0,
  reply_to_chat bigint NULL DEFAULT NULL,
  reply_to_message bigint UNSIGNED DEFAULT NULL,
  external_reply text NULL DEFAULT NULL,
  quote text NULL DEFAULT NULL,
  reply_to_story text NULL DEFAULT NULL,
  via_bot bigint NULL DEFAULT NULL,
  link_preview_options text NULL DEFAULT NULL,
  edit_date timestamp NULL DEFAULT NULL,
  has_protected_content tinyint(1) DEFAULT 0,
  media_group_id text,
  author_signature text,
  text text,
  entities text,
  caption_entities text,
  audio text,
  document text,
  animation text,
  game text,
  photo text,
  sticker text,
  story text,
  video text,
  voice text,
  video_note text,
  caption text,
  has_media_spoiler tinyint(1) DEFAULT 0,
  contact text,
  location text,
  venue text,
  poll text,
  dice text,
  new_chat_members text,
  left_chat_member bigint NULL DEFAULT NULL,
  new_chat_title char(255) DEFAULT NULL,
  new_chat_photo text,
  delete_chat_photo tinyint(1) DEFAULT 0,
  group_chat_created tinyint(1) DEFAULT 0,
  supergroup_chat_created tinyint(1) DEFAULT 0,
  channel_chat_created tinyint(1) DEFAULT 0,
  message_auto_delete_timer_changed text,
  migrate_to_chat_id bigint NULL DEFAULT NULL,
  migrate_from_chat_id bigint NULL DEFAULT NULL,
  pinned_message text NULL,
  invoice text NULL,
  successful_payment text NULL,
  users_shared text NULL,
  chat_shared text NULL,
  connected_website text NULL,
  write_access_allowed text DEFAULT NULL,
  passport_data text NULL,
  proximity_alert_triggered text NULL,
  boost_added text NULL,
  forum_topic_created text DEFAULT NULL,
  forum_topic_edited text DEFAULT NULL,
  forum_topic_closed text DEFAULT NULL,
  forum_topic_reopened text DEFAULT NULL,
  general_forum_topic_hidden text DEFAULT NULL,
  general_forum_topic_unhidden text DEFAULT NULL,
  video_chat_scheduled text,
  video_chat_started text,
  video_chat_ended text,
  video_chat_participants_invited text,
  web_app_data text,
  reply_markup text NULL,

  PRIMARY KEY  (chat_id, id),
  KEY user_id (user_id),
  KEY forward_from (forward_from),
  KEY forward_from_chat (forward_from_chat),
  KEY reply_to_chat (reply_to_chat),
  KEY reply_to_message (reply_to_message),
  KEY via_bot (via_bot),
  KEY left_chat_member (left_chat_member),
  KEY migrate_from_chat_id (migrate_from_chat_id),
  KEY migrate_to_chat_id (migrate_to_chat_id),

  FOREIGN KEY (user_id) REFERENCES teleshop_user (id),
  FOREIGN KEY (chat_id) REFERENCES teleshop_chat (id),
  FOREIGN KEY (forward_from) REFERENCES teleshop_user (id),
  FOREIGN KEY (forward_from_chat) REFERENCES teleshop_chat (id),
  FOREIGN KEY (reply_to_chat, reply_to_message) REFERENCES teleshop_message (chat_id, id),
  FOREIGN KEY (via_bot) REFERENCES teleshop_user (id),
  FOREIGN KEY (left_chat_member) REFERENCES teleshop_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_edited_message (
  id bigint UNSIGNED AUTO_INCREMENT,
  chat_id bigint,
  message_id bigint UNSIGNED,
  user_id bigint NULL,
  edit_date timestamp NULL DEFAULT NULL,
  text text,
  entities text,
  caption text,

  PRIMARY KEY  (id),
  KEY chat_id (chat_id),
  KEY message_id (message_id),
  KEY user_id (user_id),

  FOREIGN KEY (chat_id) REFERENCES teleshop_chat (id),
  FOREIGN KEY (chat_id, message_id) REFERENCES teleshop_message (chat_id, id),
  FOREIGN KEY (user_id) REFERENCES teleshop_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_callback_query (
  id bigint UNSIGNED,
  user_id bigint NULL,
  chat_id bigint NULL,
  message_id bigint UNSIGNED,
  inline_message_id char(255) NULL DEFAULT NULL,
  chat_instance char(255) NOT NULL DEFAULT '',
  data char(255) NOT NULL DEFAULT '',
  game_short_name char(255) NOT NULL DEFAULT '',
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY user_id (user_id),
  KEY chat_id (chat_id),
  KEY message_id (message_id),

  FOREIGN KEY (user_id) REFERENCES teleshop_user (id),
  FOREIGN KEY (chat_id, message_id) REFERENCES teleshop_message (chat_id, id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_shipping_query (
  id bigint UNSIGNED,
  user_id bigint,
  invoice_payload char(255) NOT NULL DEFAULT '',
  shipping_address char(255) NOT NULL DEFAULT '',
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY user_id (user_id),

  FOREIGN KEY (user_id) REFERENCES teleshop_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_pre_checkout_query (
  id bigint UNSIGNED,
  user_id bigint,
  currency char(3),
  total_amount bigint,
  invoice_payload char(255) NOT NULL DEFAULT '',
  shipping_option_id char(255) NULL,
  order_info text NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY user_id (user_id),

  FOREIGN KEY (user_id) REFERENCES teleshop_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_poll (
  id bigint UNSIGNED,
  question text NOT NULL,
  options text NOT NULL,
  total_voter_count int UNSIGNED,
  is_closed tinyint(1) DEFAULT 0,
  is_anonymous tinyint(1) DEFAULT 1,
  type char(255),
  allows_multiple_answers tinyint(1) DEFAULT 0,
  correct_option_id int UNSIGNED,
  explanation varchar(255) DEFAULT NULL,
  explanation_entities text DEFAULT NULL,
  open_period int UNSIGNED DEFAULT NULL,
  close_date timestamp NULL DEFAULT NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_poll_answer (
  poll_id bigint UNSIGNED,
  user_id bigint NOT NULL,
  option_ids text NOT NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (poll_id, user_id),
  FOREIGN KEY (poll_id) REFERENCES teleshop_poll (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_chat_member_updated (
  id BIGINT UNSIGNED AUTO_INCREMENT,
  chat_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  date TIMESTAMP NOT NULL,
  old_chat_member text NOT NULL,
  new_chat_member text NOT NULL,
  invite_link text NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  FOREIGN KEY (chat_id) REFERENCES teleshop_chat (id),
  FOREIGN KEY (user_id) REFERENCES teleshop_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_chat_join_request (
  id BIGINT UNSIGNED AUTO_INCREMENT,
  chat_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  date TIMESTAMP NOT NULL,
  bio text NULL,
  invite_link text NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),

  FOREIGN KEY (chat_id) REFERENCES teleshop_chat (id),
  FOREIGN KEY (user_id) REFERENCES teleshop_user (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_chat_boost_updated (
  id bigint UNSIGNED AUTO_INCREMENT,
  chat_id bigint,
  boost text NOT NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY chat_id (chat_id),

  FOREIGN KEY (chat_id) REFERENCES teleshop_chat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_chat_boost_removed (
  id bigint UNSIGNED AUTO_INCREMENT,
  chat_id bigint,
  boost_id varchar(200) NOT NULL,
  remove_date timestamp NOT NULL,
  source text NOT NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY chat_id (chat_id),

  FOREIGN KEY (chat_id) REFERENCES teleshop_chat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_telegram_update (
  id bigint UNSIGNED,
  chat_id bigint NULL DEFAULT NULL,
  message_id bigint UNSIGNED DEFAULT NULL,
  edited_message_id bigint UNSIGNED DEFAULT NULL,
  channel_post_id bigint UNSIGNED DEFAULT NULL,
  edited_channel_post_id bigint UNSIGNED DEFAULT NULL,
  message_reaction_id bigint UNSIGNED DEFAULT NULL,
  message_reaction_count_id bigint UNSIGNED DEFAULT NULL,
  inline_query_id bigint UNSIGNED DEFAULT NULL,
  chosen_inline_result_id bigint UNSIGNED DEFAULT NULL,
  callback_query_id bigint UNSIGNED DEFAULT NULL,
  shipping_query_id bigint UNSIGNED DEFAULT NULL,
  pre_checkout_query_id bigint UNSIGNED DEFAULT NULL,
  poll_id bigint UNSIGNED DEFAULT NULL,
  poll_answer_poll_id bigint UNSIGNED DEFAULT NULL,
  my_chat_member_updated_id BIGINT UNSIGNED NULL,
  chat_member_updated_id BIGINT UNSIGNED NULL,
  chat_join_request_id BIGINT UNSIGNED NULL,
  chat_boost_updated_id BIGINT UNSIGNED NULL,
  chat_boost_removed_id BIGINT UNSIGNED NULL,

  PRIMARY KEY  (id),
  KEY message_id (message_id),
  KEY chat_message_id (chat_id, message_id),
  KEY edited_message_id (edited_message_id),
  KEY channel_post_id (channel_post_id),
  KEY edited_channel_post_id (edited_channel_post_id),
  KEY inline_query_id (inline_query_id),
  KEY chosen_inline_result_id (chosen_inline_result_id),
  KEY callback_query_id (callback_query_id),
  KEY shipping_query_id (shipping_query_id),
  KEY pre_checkout_query_id (pre_checkout_query_id),
  KEY poll_id (poll_id),
  KEY poll_answer_poll_id (poll_answer_poll_id),
  KEY my_chat_member_updated_id (my_chat_member_updated_id),
  KEY chat_member_updated_id (chat_member_updated_id),
  KEY chat_join_request_id (chat_join_request_id),

  FOREIGN KEY (chat_id, message_id) REFERENCES teleshop_message (chat_id, id),
  FOREIGN KEY (edited_message_id) REFERENCES teleshop_edited_message (id),
  FOREIGN KEY (chat_id, channel_post_id) REFERENCES teleshop_message (chat_id, id),
  FOREIGN KEY (edited_channel_post_id) REFERENCES teleshop_edited_message (id),
  FOREIGN KEY (inline_query_id) REFERENCES teleshop_inline_query (id),
  FOREIGN KEY (chosen_inline_result_id) REFERENCES teleshop_chosen_inline_result (id),
  FOREIGN KEY (callback_query_id) REFERENCES teleshop_callback_query (id),
  FOREIGN KEY (shipping_query_id) REFERENCES teleshop_shipping_query (id),
  FOREIGN KEY (pre_checkout_query_id) REFERENCES teleshop_pre_checkout_query (id),
  FOREIGN KEY (poll_id) REFERENCES teleshop_poll (id),
  FOREIGN KEY (poll_answer_poll_id) REFERENCES teleshop_poll_answer (poll_id),
  FOREIGN KEY (my_chat_member_updated_id) REFERENCES teleshop_chat_member_updated (id),
  FOREIGN KEY (chat_member_updated_id) REFERENCES teleshop_chat_member_updated (id),
  FOREIGN KEY (chat_join_request_id) REFERENCES teleshop_chat_join_request (id),
  FOREIGN KEY (chat_boost_updated_id) REFERENCES teleshop_chat_boost_updated (id),
  FOREIGN KEY (chat_boost_removed_id) REFERENCES teleshop_chat_boost_removed (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_conversation (
  id bigint(20) unsigned AUTO_INCREMENT,
  user_id bigint NULL DEFAULT NULL,
  chat_id bigint NULL DEFAULT NULL,
  status ENUM('active', 'cancelled', 'stopped') NOT NULL DEFAULT 'active',
  command varchar(160) DEFAULT '',
  notes text DEFAULT NULL,
  created_at timestamp NULL DEFAULT NULL,
  updated_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id),
  KEY user_id (user_id),
  KEY chat_id (chat_id),
  KEY status (status),

  FOREIGN KEY (user_id) REFERENCES teleshop_user (id),
  FOREIGN KEY (chat_id) REFERENCES teleshop_chat (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

CREATE TABLE teleshop_request_limiter (
  id bigint UNSIGNED AUTO_INCREMENT,
  chat_id char(255) NULL DEFAULT NULL,
  inline_message_id char(255) NULL DEFAULT NULL,
  method char(255) DEFAULT NULL,
  created_at timestamp NULL DEFAULT NULL,

  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;