CREATE TABLE IF NOT EXISTS  `user` (
  `id` bigint COMMENT 'Unique user identifier',
  `first_name` CHAR(255) NOT NULL DEFAULT '' COMMENT 'User first name',
  `last_name` CHAR(255) DEFAULT NULL COMMENT 'User last name',
  `username` CHAR(255) DEFAULT NULL COMMENT 'User username',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',
  PRIMARY KEY (`id`),
  KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS  `chat` (
  `id` bigint COMMENT 'Unique user or chat identifier',
  `type` ENUM('private', 'group', 'supergroup', 'channel') NOT NULL COMMENT 'chat type private, group, supergroup or channel',
  `title` CHAR(255) DEFAULT '' COMMENT 'chat title null if case of single chat with the bot',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',
  `old_id` bigint DEFAULT NULL COMMENT 'Unique chat identifieri this is filled when a chat is converted to a superchat',
   PRIMARY KEY (`id`),
   KEY `old_id` (`old_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS  `user_chat` (
  `user_id` bigint COMMENT 'Unique user identifier',
  `chat_id` bigint COMMENT 'Unique user or chat identifier',
  PRIMARY KEY (`user_id`, `chat_id`),
  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`chat_id`) REFERENCES `chat` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


CREATE TABLE IF NOT EXISTS `inline_query` (
  `id` bigint UNSIGNED COMMENT 'Unique identifier for this query.',
  `user_id` bigint NULL COMMENT 'Sender',
  `query` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Text of the query',
  `offset` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Offset of the result',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
   PRIMARY KEY (`id`),
   KEY `user_id` (`user_id`),

   FOREIGN KEY (`user_id`)
   REFERENCES `user` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS `chosen_inline_query` (
  `id` bigint UNSIGNED AUTO_INCREMENT COMMENT 'Unique identifier for chosen query.',
  `result_id` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Id of the chosen result',
  `user_id` bigint NULL COMMENT 'Sender',
  `query` CHAR(255) NOT NULL DEFAULT '' COMMENT 'Text of the query',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
   PRIMARY KEY (`id`),
   KEY `user_id` (`user_id`),

   FOREIGN KEY (`user_id`)
   REFERENCES `user` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS  `message` (
  `chat_id` bigint NULL DEFAULT NULL COMMENT 'Chat identifier.',
  `id` bigint UNSIGNED COMMENT 'Unique message identifier',
  `user_id` bigint NULL COMMENT 'User identifier',
  `date` timestamp NULL DEFAULT NULL COMMENT 'Date the message was sent in timestamp format',
  `forward_from` bigint NULL DEFAULT NULL COMMENT 'User id. For forwarded messages, sender of the original message',
  `forward_date` timestamp NULL DEFAULT NULL COMMENT 'For forwarded messages, date the original message was sent in Unix time',
  `reply_to_chat` bigint NULL DEFAULT NULL COMMENT 'Chat identifier.',
  `reply_to_message` bigint UNSIGNED DEFAULT NULL COMMENT 'Message is a reply to another message.',
  `text` TEXT DEFAULT NULL COMMENT 'For text messages, the actual UTF-8 text of the message max message length 4096 char utf8',
  `audio` TEXT DEFAULT NULL COMMENT 'Audio object. Message is an audio file, information about the file',
  `document` TEXT DEFAULT NULL COMMENT 'Document object. Message is a general file, information about the file',
  `photo` TEXT DEFAULT NULL COMMENT 'Array of PhotoSize objects. Message is a photo, available sizes of the photo',
  `sticker` TEXT DEFAULT NULL COMMENT 'Sticker object. Message is a sticker, information about the sticker',
  `video` TEXT DEFAULT NULL COMMENT 'Video object. Message is a video, information about the video',
  `voice` TEXT DEFAULT NULL COMMENT 'Voice Object. Message is a Voice, information about the Voice',
  `caption` TEXT DEFAULT NULL COMMENT  'For message with caption, the actual UTF-8 text of the caption',
  `contact` TEXT DEFAULT NULL COMMENT 'Contact object. Message is a shared contact, information about the contact',
  `location` TEXT DEFAULT NULL COMMENT 'Location object. Message is a shared location, information about the location',
  `new_chat_participant` bigint NULL DEFAULT NULL COMMENT 'User id. A new member was added to the group, information about them (this member may be bot itself)',
  `left_chat_participant` bigint NULL DEFAULT NULL COMMENT 'User id. A member was removed from the group, information about them (this member may be bot itself)',
  `new_chat_title` CHAR(255) DEFAULT NULL COMMENT 'A group title was changed to this value',
  `new_chat_photo` TEXT DEFAULT NULL COMMENT 'Array of PhotoSize objects. A group photo was change to this value',
  `delete_chat_photo` tinyint(1) DEFAULT 0 COMMENT 'Informs that the group photo was deleted',
  `group_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the group has been created',
  `supergroup_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the supergroup has been created',
  `channel_chat_created` tinyint(1) DEFAULT 0 COMMENT 'Informs that the channel chat has been created',
  `migrate_from_chat_id` bigint NULL DEFAULT NULL COMMENT 'Migrate from chat identifier.',
  `migrate_to_chat_id` bigint NULL DEFAULT NULL COMMENT 'Migrate to chat identifier.',
  PRIMARY KEY (`chat_id`, `id`),
  KEY `user_id` (`user_id`),
  KEY `forward_from` (`forward_from`),
  KEY `reply_to_chat` (`reply_to_chat`),
  KEY `reply_to_message` (`reply_to_message`),
  KEY `new_chat_participant` (`new_chat_participant`),
  KEY `left_chat_participant` (`left_chat_participant`),
  KEY `migrate_from_chat_id` (`migrate_from_chat_id`),
  KEY `migrate_to_chat_id` (`migrate_to_chat_id`),

  FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
  FOREIGN KEY (`chat_id`) REFERENCES `chat` (`id`),
  FOREIGN KEY (`forward_from`) REFERENCES `user` (`id`),
  FOREIGN KEY (`reply_to_chat`, `reply_to_message`) REFERENCES `message` (`chat_id`,`id`),
  FOREIGN KEY (`forward_from`) REFERENCES `user` (`id`),
  FOREIGN KEY (`new_chat_participant`) REFERENCES `user` (`id`),
  FOREIGN KEY (`left_chat_participant`) REFERENCES `user` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


CREATE TABLE IF NOT EXISTS `telegram_update` (
  `id` bigint UNSIGNED COMMENT 'The update\'s unique identifier.',
  `chat_id` bigint NULL DEFAULT NULL COMMENT 'Chat identifier.',
  `message_id` bigint UNSIGNED DEFAULT NULL COMMENT 'Unique message identifier',
  `inline_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'The query unique identifier.',
  `chosen_inline_query_id` bigint UNSIGNED DEFAULT NULL COMMENT 'The chosen query unique identifier.',

  PRIMARY KEY (`id`),
  KEY `message_id` (`chat_id`, `message_id`),
  KEY `inline_query_id` (`inline_query_id`),
  KEY `chosen_inline_query_id` (`chosen_inline_query_id`),

  FOREIGN KEY (`chat_id`, `message_id`) REFERENCES `message` (`chat_id`,`id`),
  FOREIGN KEY (`inline_query_id`) REFERENCES `inline_query` (`id`),
  FOREIGN KEY (`chosen_inline_query_id`) REFERENCES `chosen_inline_query` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

CREATE TABLE IF NOT EXISTS `conversation` (
  `id` bigint(20) unsigned AUTO_INCREMENT COMMENT 'Row unique id',
  `user_id` bigint NULL DEFAULT NULL COMMENT 'User id',
  `chat_id` bigint NULL DEFAULT NULL COMMENT 'Telegram chat_id can be a the user id or the chat id ',
  `status` ENUM('active', 'cancelled', 'stopped') NOT NULL DEFAULT 'active' COMMENT 'active conversation is active, cancelled conversation has been truncated before end, stopped conversation has end',
  `command` varchar(160) DEFAULT '' COMMENT 'Default Command to execute',
  `notes` varchar(1000) DEFAULT 'NULL' COMMENT 'Data stored from command',
  `created_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date creation',
  `updated_at` timestamp NULL DEFAULT NULL COMMENT 'Entry date update',

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

  FOREIGN KEY (`user_id`)
  REFERENCES `user` (`id`),
  FOREIGN KEY (`chat_id`)
  REFERENCES `chat` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;