
CREATE TABLE IF NOT EXISTS `tb_gdrive_duplicate` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `gdrive_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gdrive_email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `fileSize` bigint(20) unsigned NOT NULL DEFAULT '0',
  `md5Checksum` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sha1Checksum` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sha256Checksum` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `gdup_gdrive_id_idx` (`gdrive_id`),
  KEY `gdrive_duplicate_idx` (`title`,`description`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_gdrive_mirrors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `gdrive_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mirror_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mirror_email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `gmir_gdrive_id_idx` (`gdrive_id`),
  KEY `mirror_id_idx` (`mirror_id`),
  KEY `FK_tb_gdrive_mirrors_mirror_email_tb_gdrive_auth_email` (`mirror_email`),
  CONSTRAINT `FK_tb_gdrive_mirrors_mirror_email_tb_gdrive_auth_email` FOREIGN KEY (`mirror_email`) REFERENCES `tb_gdrive_auth` (`email`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_gdrive_queue` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `gdrive_id` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `delayed` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `gque_gdrive_id_idx` (`gdrive_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_loadbalancers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `link` varchar(263) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `public` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `updated` int(10) unsigned NOT NULL DEFAULT '0',
  `disallow_hosts` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `disallow_continent` text COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `link_idx` (`link`),
  KEY `lb_status_idx` (`status`),
  KEY `public_idx` (`public`),
  KEY `created_idx` (`created`),
  KEY `updated_idx` (`updated`),
  FULLTEXT KEY `disallow_hosts_idx` (`disallow_hosts`),
  FULLTEXT KEY `disallow_continent_idx` (`disallow_continent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_maxmind` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `prefix_len` tinyint(3) unsigned DEFAULT NULL,
  `asn` int(10) unsigned DEFAULT NULL,
  `continent` char(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `maxmind_ip_idx` (`ip`),
  KEY `continent_idx` (`continent`),
  KEY `maxmind_country_idx` (`country`),
  KEY `FK_tb_maxmind_asn_tb_maxmind_asn_id` (`asn`),
  CONSTRAINT `FK_tb_maxmind_asn_tb_maxmind_asn_id` FOREIGN KEY (`asn`) REFERENCES `tb_maxmind_asn` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_maxmind_asn` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_plugins` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `folder` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `config` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `updated` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `plugin_idx` (`name`,`folder`),
  KEY `plugin_status_idx` (`status`),
  KEY `created_idx` (`created`),
  KEY `updated_idx` (`updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tb_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `vid` bigint(20) unsigned NOT NULL,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ua` int(10) unsigned NOT NULL,
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `asn` int(10) unsigned DEFAULT NULL,
  `country` char(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stat_ip_idx` (`ip`),
  KEY `asn_idx` (`asn`),
  KEY `stat_country_idx` (`country`),
  KEY `created_idx` (`created`),
  KEY `FK_tb_stats_vid_tb_videos_id` (`vid`),
  KEY `FK_tb_stats_ua_tb_stats_ua_id` (`ua`),
  CONSTRAINT `FK_tb_stats_ua_tb_stats_ua_id` FOREIGN KEY (`ua`) REFERENCES `tb_stats_ua` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_tb_stats_vid_tb_videos_id` FOREIGN KEY (`vid`) REFERENCES `tb_videos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_stats_ua` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ua` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ua_idx` (`ua`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_subtitles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `language` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `link` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `vid` bigint(20) unsigned NOT NULL,
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `uid` int(10) unsigned NOT NULL,
  `order` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `updated` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `order_idx` (`order`),
  KEY `FK_tb_subtitles_vid_tb_videos_id` (`vid`),
  KEY `FK_tb_subtitles_uid_tb_users_id` (`uid`),
  CONSTRAINT `FK_tb_subtitles_uid_tb_users_id` FOREIGN KEY (`uid`) REFERENCES `tb_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_tb_subtitles_vid_tb_videos_id` FOREIGN KEY (`vid`) REFERENCES `tb_videos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_subtitle_manager` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `file_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `file_size` int(10) unsigned NOT NULL DEFAULT '0',
  `file_type` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,
  `language` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `uid` int(10) unsigned NOT NULL,
  `host` varchar(263) COLLATE utf8mb4_unicode_ci NOT NULL,
  `updated` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `file_name_idx` (`file_name`),
  KEY `host_idx` (`host`(255)),
  KEY `language_idx` (`language`),
  KEY `created_idx` (`created`),
  KEY `updated_idx` (`updated`),
  KEY `FK_tb_subtitle_manager_uid_tb_users_id` (`uid`),
  CONSTRAINT `FK_tb_subtitle_manager_uid_tb_users_id` FOREIGN KEY (`uid`) REFERENCES `tb_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_videos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `host` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `host_id` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `uid` int(10) unsigned NOT NULL,
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `updated` int(10) unsigned NOT NULL DEFAULT '0',
  `poster` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `views` int(10) unsigned NOT NULL DEFAULT '0',
  `dmca` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `slug` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug_idx` (`slug`),
  KEY `videos_idx` (`host`,`host_id`(255)),
  KEY `video_status_idx` (`status`),
  KEY `dmca_idx` (`dmca`),
  KEY `views_idx` (`views`),
  KEY `title_idx` (`title`),
  KEY `poster_idx` (`poster`(255)),
  KEY `created_idx` (`created`),
  KEY `updated_idx` (`updated`),
  KEY `FK_tb_videos_uid_tb_users_id` (`uid`),
  CONSTRAINT `FK_tb_videos_uid_tb_users_id` FOREIGN KEY (`uid`) REFERENCES `tb_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_videos_alternatives` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `vid` bigint(20) unsigned NOT NULL,
  `host` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `host_id` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `order` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `videos_alternatives_idx` (`host`,`host_id`(255)),
  KEY `order_idx` (`order`),
  KEY `FK_tb_videos_alternatives_vid_tb_videos_id` (`vid`),
  CONSTRAINT `FK_tb_videos_alternatives_vid_tb_videos_id` FOREIGN KEY (`vid`) REFERENCES `tb_videos` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_videos_hash` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `host` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `host_id` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gdrive_email` varchar(254) COLLATE utf8mb4_unicode_ci NOT NULL,
  `data` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `videos_hash_idx` (`host`,`host_id`(255)),
  KEY `gdrive_email_idx` (`gdrive_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tb_videos_sources` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `host` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `host_id` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `data` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `dl` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `sid` int(10) unsigned DEFAULT NULL,
  `created` int(10) unsigned NOT NULL DEFAULT '0',
  `expired` int(10) unsigned NOT NULL DEFAULT '0',
  `ua` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lang` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cip` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `videos_sources_idx` (`host`,`host_id`(255),`expired`,`dl`,`ua`,`lang`),
  KEY `videos_sources_ip_idx` (`ip`),
  KEY `cip_idx` (`cip`),
  KEY `expired_idx` (`expired`),
  KEY `created_idx` (`created`),
  KEY `FK_tb_videos_sources_sid_tb_loadbalancers_id` (`sid`),
  FULLTEXT KEY `data_idx` (`data`),
  CONSTRAINT `FK_tb_videos_sources_sid_tb_loadbalancers_id` FOREIGN KEY (`sid`) REFERENCES `tb_loadbalancers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS `tmp_videos_sources` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `host` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `host_id` varchar(2048) COLLATE utf8mb4_unicode_ci NOT NULL,
  `dl` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `tmp_videos_sources_idx` (`host`,`host_id`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP VIEW IF EXISTS `vwloadbalancers`;

DROP VIEW IF EXISTS `vwsubtitle_manager`;

DROP VIEW IF EXISTS `vw_users`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `vw_users` AS select `u`.`id` AS `id`,`u`.`name` AS `name`,`u`.`user` AS `user`,`u`.`email` AS `email`,`u`.`status` AS `status`,`u`.`created` AS `created`,`u`.`updated` AS `updated`,`u`.`role` AS `role`,coalesce(`v`.`cnt`,0) AS `videos` from (`tb_users` `u` left join (select `tb_videos`.`uid` AS `uid`,count(0) AS `cnt` from `tb_videos` group by `tb_videos`.`uid`) `v` on((`v`.`uid` = `u`.`id`)))
;

DROP VIEW IF EXISTS `vwvideos`;

