DROP TABLE IF EXISTS `adspace`;

CREATE TABLE `adspace` (
  `adID` int(11) NOT NULL AUTO_INCREMENT,
  `sortID` int(11) DEFAULT NULL,
  `show_ID` int(11) DEFAULT NULL,
  `loc_ID` int(11) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `imgurl` varchar(200) DEFAULT NULL,
  `adtext` text DEFAULT NULL,
  `date_added` datetime DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `adType` varchar(15) DEFAULT NULL,
  `refresh` int(11) DEFAULT 60,
  PRIMARY KEY (`adID`)
) ENGINE=InnoDB AUTO_INCREMENT=2447 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `league_teams`;

CREATE TABLE `league_teams` (
  `team_ID` int(11) NOT NULL AUTO_INCREMENT,
  `league_ID` int(11) DEFAULT NULL,
  `region_ID` int(11) DEFAULT NULL,
  `teamname` varchar(100) DEFAULT NULL,
  `orig_team_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`team_ID`),
  KEY `league,` (`league_ID`,`region_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5136 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `local_files_history`;

CREATE TABLE `local_files_history` (
  `local_file_history_ID` int(11) NOT NULL AUTO_INCREMENT,
  `local_file_ID` int(11) DEFAULT NULL,
  `user_ID` int(11) DEFAULT NULL,
  `date_downloaded` datetime DEFAULT NULL,
  `downloaded` char(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `uu_ID` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`local_file_history_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=145281 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `pregame_screens`;

CREATE TABLE `pregame_screens` (
  `screen_ID` int(11) NOT NULL AUTO_INCREMENT,
  `loc_ID` int(11) DEFAULT NULL,
  `show_ID` int(11) DEFAULT NULL,
  `region_ID` int(11) DEFAULT NULL,
  `pre_title` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `screen_pos` int(11) DEFAULT NULL,
  `screen_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_added` datetime DEFAULT NULL,
  `added_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`screen_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4098 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `question_view_status`;

CREATE TABLE `question_view_status` (
  `status_ID` int(11) NOT NULL AUTO_INCREMENT,
  `usr_ID` int(11) DEFAULT NULL,
  `loc_ID` int(11) DEFAULT NULL,
  `show_ID` int(11) DEFAULT NULL,
  `question_ID` int(11) DEFAULT NULL,
  `qview` char(10) DEFAULT NULL,
  `gameround` char(1) DEFAULT NULL,
  `qno` int(11) DEFAULT NULL,
  `date_entered` datetime DEFAULT NULL,
  `game_date` date DEFAULT NULL,
  `qlen` int(11) DEFAULT NULL,
  `font_size` int(11) DEFAULT 0,
  `view_num` int(11) DEFAULT 0,
  PRIMARY KEY (`status_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=15873 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `questions`;

CREATE TABLE `questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `game_round` varchar(1) COLLATE utf8_unicode_520_ci DEFAULT NULL,
  `qno` int(11) NOT NULL,
  `question` text COLLATE utf8_unicode_520_ci DEFAULT NULL,
  `answer` text COLLATE utf8_unicode_520_ci DEFAULT NULL,
  `host_info` text COLLATE utf8_unicode_520_ci DEFAULT NULL,
  `host_info_pre` text COLLATE utf8_unicode_520_ci DEFAULT NULL,
  `display_category` varchar(50) COLLATE utf8_unicode_520_ci DEFAULT NULL,
  `game_date` date NOT NULL,
  `date_submitted` date DEFAULT NULL,
  `question_set_id` int(11) NOT NULL DEFAULT 0,
  `media_question` tinyint(1) DEFAULT 0,
  `media_question_url` varchar(20) COLLATE utf8_unicode_520_ci DEFAULT NULL,
  `media_answer` tinyint(1) DEFAULT 0,
  `media_answer_url` varchar(20) COLLATE utf8_unicode_520_ci DEFAULT NULL,
  `answerofnight` tinyint(1) DEFAULT 0,
  `hintofweek` char(1) COLLATE utf8_unicode_520_ci DEFAULT 'N',
  `region_ID` varchar(50) COLLATE utf8_unicode_520_ci NOT NULL,
  `orig_ID` int(11) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4500 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_520_ci;

DROP TABLE IF EXISTS `scoring_tracker`;

CREATE TABLE `scoring_tracker` (
  `tracker_ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_ID` int(11) DEFAULT NULL,
  `region_ID` int(11) DEFAULT NULL,
  `game_date` datetime DEFAULT NULL,
  `loc_ID` int(11) DEFAULT NULL,
  `show_ID` int(11) DEFAULT NULL,
  `scoring_ID` int(11) DEFAULT NULL,
  `game_round` char(1) DEFAULT NULL,
  `qno` int(11) DEFAULT NULL,
  `score_action` char(10) DEFAULT NULL,
  `points` int(11) DEFAULT NULL,
  `yn` char(1) DEFAULT NULL,
  PRIMARY KEY (`tracker_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=531 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `show_schedule_local`;

CREATE TABLE `show_schedule_local` (
  `local_ID` int(11) NOT NULL AUTO_INCREMENT,
  `sched_ID` int(11) DEFAULT NULL,
  `user_ID` int(11) DEFAULT NULL,
  `region_ID` int(11) DEFAULT NULL,
  `game_date` datetime DEFAULT NULL,
  `loc_ID` int(11) DEFAULT NULL,
  `show_ID` int(11) DEFAULT NULL,
  `loc_name` varchar(100) DEFAULT NULL,
  `show_type` varchar(10) DEFAULT NULL,
  `question_set_id` int(11) DEFAULT NULL,
  `logo_loc` varchar(100) DEFAULT NULL,
  `corp_event` varchar(1) DEFAULT NULL,
  PRIMARY KEY (`local_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=832 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `team_scoring`;

CREATE TABLE `team_scoring` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teamname` varchar(255) NOT NULL,
  `aka` varchar(50) NOT NULL,
  `players` int(11) NOT NULL,
  `league_ID` int(11) NOT NULL,
  `team_ID` int(11) NOT NULL,
  `loc_ID` int(11) NOT NULL,
  `show_ID` int(11) NOT NULL,
  `usr_ID` int(11) NOT NULL,
  `game_date` date NOT NULL,
  `1_1` int(11) NOT NULL,
  `1_1_yn` int(11) NOT NULL,
  `1_2` int(11) NOT NULL,
  `1_2_yn` int(11) NOT NULL,
  `1_3` int(11) NOT NULL,
  `1_3_yn` int(11) NOT NULL,
  `2_1` int(11) NOT NULL,
  `2_1_yn` int(11) NOT NULL,
  `2_2` int(11) NOT NULL,
  `2_2_yn` int(11) NOT NULL,
  `2_3` int(11) NOT NULL,
  `2_3_yn` int(11) NOT NULL,
  `3_1` int(11) NOT NULL,
  `3_1_yn` int(11) NOT NULL,
  `3_2` int(11) NOT NULL,
  `3_2_yn` int(11) NOT NULL,
  `3_3` int(11) NOT NULL,
  `3_3_yn` int(11) NOT NULL,
  `H` int(11) NOT NULL,
  `H_in` char(1) DEFAULT NULL,
  `H_finalized` char(1) NOT NULL,
  `first_half_score` int(11) NOT NULL,
  `4_1` int(11) NOT NULL,
  `4_1_yn` int(11) NOT NULL,
  `4_2` int(11) NOT NULL,
  `4_2_yn` int(11) NOT NULL,
  `4_3` int(11) NOT NULL,
  `4_3_yn` int(11) NOT NULL,
  `5_1` int(11) NOT NULL,
  `5_1_yn` int(11) NOT NULL,
  `5_2` int(11) NOT NULL,
  `5_2_yn` int(11) NOT NULL,
  `5_3` int(11) NOT NULL,
  `5_3_yn` int(11) NOT NULL,
  `6_1` int(11) NOT NULL,
  `6_1_yn` int(11) NOT NULL,
  `6_2` int(11) NOT NULL,
  `6_2_yn` int(11) NOT NULL,
  `6_3` int(11) NOT NULL,
  `6_3_yn` int(11) NOT NULL,
  `F` int(11) NOT NULL,
  `F_finalized` char(1) NOT NULL,
  `F_yn` int(11) NOT NULL,
  `second_half_score` int(11) NOT NULL,
  `final_score` int(11) NOT NULL,
  `team_rank` int(11) NOT NULL,
  `team_active` int(11) NOT NULL,
  `tourn_ID` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=291 DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `user_settings`;

CREATE TABLE `user_settings` (
`settings_ID` int(11) NOT NULL AUTO_INCREMENT,
`auto_num_teams` char(1) NOT NULL,	
`final_score_update` char(15) NOT NULL,
`halftime_score_option` varchar(15) NOT NULL,
`final_score_option` varchar(15) NOT NULL,
`timer_default` decimal (10,0) NULL,
`timer_default_bonus` decimal (10,0) NULL,
`split_after` int(11) NOT NULL,
`user_ID` int(11) NOT NULL,
  PRIMARY KEY (`settings_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `feud_responses_host`;

CREATE TABLE `feud_responses_host` (
                                       `aID` int NOT NULL AUTO_INCREMENT,
                                       `sID` int DEFAULT NULL,
                                       `response_rank` int DEFAULT NULL,
                                       `response` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
                                       `hint` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
                                       `region_ID` int DEFAULT '0',
                                       `user_ID` int DEFAULT '0',
                                       `set_ID` int DEFAULT NULL,
                                       PRIMARY KEY (`aID`)
) ENGINE=InnoDB AUTO_INCREMENT=156335 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `feud_sets` */

DROP TABLE IF EXISTS `feud_sets`;

CREATE TABLE `feud_sets` (
                             `set_ID` int NOT NULL AUTO_INCREMENT,
                             `set_name` varchar(100) DEFAULT NULL,
                             `added_by` int DEFAULT NULL,
                             `date_created` datetime DEFAULT NULL,
                             `weekstart` date DEFAULT NULL,
                             `question_set_ID` int DEFAULT NULL,
                             PRIMARY KEY (`set_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=717 DEFAULT CHARSET=utf8mb3;

/*Table structure for table `feud_status` */

DROP TABLE IF EXISTS `feud_status`;

CREATE TABLE `feud_status` (
                               `game_date` datetime DEFAULT NULL,
                               `user_ID` int DEFAULT NULL,
                               `sID` int DEFAULT NULL,
                               `game_round` varchar(1) DEFAULT NULL,
                               `qno` int DEFAULT NULL,
                               `response_rank` int DEFAULT NULL,
                               `response` varchar(100) DEFAULT NULL,
                               `date_entered` datetime DEFAULT NULL,
                               `view` varchar(15) DEFAULT NULL,
                               `survey` text,
                               `status_ID` int NOT NULL AUTO_INCREMENT,
                               `team_rank` int DEFAULT NULL,
                               PRIMARY KEY (`status_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=42445 DEFAULT CHARSET=latin1;

/*Table structure for table `feud_surveys_host` */

DROP TABLE IF EXISTS `feud_surveys_host`;

CREATE TABLE `feud_surveys_host` (
                                     `s_host_ID` int NOT NULL AUTO_INCREMENT,
                                     `sID` int NOT NULL DEFAULT '0',
                                     `sRound` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
                                     `sNo` int DEFAULT NULL,
                                     `survey` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
                                     `response1` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
                                     `response2` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
                                     `response3` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
                                     `response4` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
                                     `response5` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
                                     `set_ID` int DEFAULT NULL,
                                     `region_ID` int DEFAULT '0',
                                     `user_ID` int DEFAULT '0',
                                     PRIMARY KEY (`s_host_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=31788 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `feud_scores`;

CREATE TABLE `feud_scores` (
                               `score_ID` int NOT NULL AUTO_INCREMENT,
                               `teamname` varchar(100) DEFAULT NULL,
                               `aka` varchar(100) DEFAULT NULL,
                               `team_ID` int DEFAULT '0',
                               `players` int DEFAULT NULL,
                               `loc_ID` int DEFAULT '0',
                               `show_ID` int DEFAULT '0',
                               `sched_ID` int DEFAULT '0',
                               `game_date` date DEFAULT NULL,
                               `rtb` int DEFAULT NULL,
                               `1_1` int DEFAULT '0',
                               `1_2` int DEFAULT '0',
                               `1_3` int DEFAULT '0',
                               `1_4` int DEFAULT '0',
                               `B_1` int DEFAULT '0',
                               `2_1` int DEFAULT '0',
                               `2_2` int DEFAULT '0',
                               `2_3` int DEFAULT '0',
                               `2_4` int DEFAULT '0',
                               `B_2` int DEFAULT '0',
                               `3_1` int DEFAULT '0',
                               `3_2` int DEFAULT '0',
                               `3_3` int DEFAULT '0',
                               `3_4` int DEFAULT '0',
                               `B_3` int DEFAULT '0',
                               `F_1` int DEFAULT '0',
                               `F_2` int DEFAULT '0',
                               `F_3` int DEFAULT '0',
                               `total_points` int DEFAULT '0',
                               `team_rank` int DEFAULT '0',
                               `active` int DEFAULT '1',
                               PRIMARY KEY (`score_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=18753 DEFAULT CHARSET=utf8mb3;


CREATE TABLE IF NOT EXISTS `locations` (
  `loc_ID` int(11) DEFAULT NULL, 
 `loc_name` varchar(100) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;