seed.db

-- Initialize the db with all of the tables needed to run the app.

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS channels;
DROP TABLE IF EXISTS bot_logging;

CREATE TABLE `bot_logging` (
  `id` int(11) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `source` text NOT NULL,
  `channel` text NOT NULL,
  `search_type` text NOT NULL,
  `query` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `twitch_id` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `display_name` varchar(255) NOT NULL,
  `discord_channel` varchar(40) DEFAULT NULL,
  `access_token` varchar(255) NOT NULL,
  `refresh_token` varchar(255) NOT NULL,
  `created_on` varchar(255) NOT NULL,
  `lang` varchar(255) NOT NULL DEFAULT 'en',
  `cooldown` int(11) NOT NULL DEFAULT '10',
  `aliases` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`twitch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `bot_logging`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `channels`
  ADD PRIMARY KEY (`name`);

ALTER TABLE `users`
  ADD PRIMARY KEY (`twitch_id`);

ALTER TABLE `bot_logging`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

-- INSERT SAMPLE DATA
INSERT INTO `users` (`twitch_id`, `username`, `display_name`, `discord_channel`, `access_token`, `refresh_token`, `created_on`, `lang`, `cooldown`, `aliases`) VALUES
('74992193',	'logicalsolutions',	'LogicalSolutions',	'578055538810814467',	'iqniy2m77jepwmfsuto92qyhz9xhts',	'y2wbyp0f07ao6be9fjbi57ze9ntcrnsfl9xx4gegsm807xvtbf',	'2021/2/07',	'en',	5,	NULL);