USE `scorenow`;

DROP TABLE IF EXISTS `athletes`;

CREATE TABLE `athletes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fed_id` varchar(50) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `country` varchar(10) NOT NULL, -- zu prüfen mit tabelle countries (country=countries.id)
  `gender` char(1) NOT NULL,
  `year_of_birth` year(4) NOT NULL,
  `competition_code` varchar(255) DEFAULT NULL, --zu prüfen mit tabelle competitions (competition_code=competitions.id)
  `event_code` varchar(255) DEFAULT NULL, --foreign key zu tabelle events (event_code=events.id)
  `entry_type` varchar(255) DEFAULT NULL,
  `participation` tinyint(1) DEFAULT NULL,
  `real_category` varchar(255) DEFAULT NULL, --foreign key zu tabelle categories (real_category=categories.id)
  `category_in_competition` varchar(255) DEFAULT NULL,, --foreign key zu tabelle categories (category_in_competition=categories.id)
  `bib` int(11) DEFAULT NULL,
  `birth_city` varchar(255) DEFAULT NULL,
  `club` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `occupation` varchar(255) DEFAULT NULL,
  `hobbies` varchar(255) DEFAULT NULL,
  `favourite_food` varchar(255) DEFAULT NULL,
  `favourite_music` varchar(255) DEFAULT NULL,
  `favourite_discipline` varchar(255) DEFAULT NULL,
  `favourite_event` varchar(255) DEFAULT NULL,
  `first_competition` varchar(255) DEFAULT NULL,
  `started_skiing_riding` varchar(255) DEFAULT NULL,
  `favourite_trick` varchar(255) DEFAULT NULL,
  `first_flip_invert` varchar(255) DEFAULT NULL,
  `training_site` varchar(255) DEFAULT NULL,
  `coaches` varchar(255) DEFAULT NULL,
  `sponsors` varchar(255) DEFAULT NULL,
  `equipment_used` varchar(255) DEFAULT NULL,
  `if_i_win` varchar(255) DEFAULT NULL,
  `homepage` varchar(255) DEFAULT NULL,
  `philosophy_of_life` varchar(255) DEFAULT NULL,
  `ranking_place` int(11) DEFAULT NULL,
  `best_performance` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `categories`;

CREATE TABLE `categories` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(100) DEFAULT NULL,
  `gender` varchar(1) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `competitions`;

CREATE TABLE `competitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `discipline` varchar(100) NOT NULL,
  `name` varchar(255) NOT NULL,
  `organizing_country` int(11) NOT NULL,
  `tournament_type` varchar(100) NOT NULL,
  `venue` varchar(255) NOT NULL,
  `site_code` varchar(50) NOT NULL,
  `beginning_date` date NOT NULL,
  `end_date` date NOT NULL,
  `age_group` varchar(50) NOT NULL,
  `timestamp` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `countries`;

CREATE TABLE `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` char(3) NOT NULL,
  `name` varchar(255) NOT NULL,
  `timezone_name` VARCHAR(50) NOT NULL,
  `time_diff` TIME NOT NULL,
  PRIMARY KEY (`id`)
);


INSERT INTO countries (id, code, name, timezone_name, time_diff)
VALUES
    (1, 'AFG', 'Afghanistan', 'UTC+04:30', '04:30'),
    (2, 'ALB', 'Albania', 'UTC+01:00', '01:00'),
    (3, 'DZA', 'Algeria', 'UTC+01:00', '01:00'),
    (4, 'AND', 'Andorra', 'UTC+01:00', '01:00'),
    (5, 'AGO', 'Angola', 'UTC+01:00', '01:00'),
    (6, 'ATG', 'Antigua and Barbuda', 'UTC-04:00', '-04:00'),
    (7, 'ARG', 'Argentina', 'UTC-03:00', '-03:00'),
    (8, 'ARM', 'Armenia', 'UTC+04:00', '04:00'),
    (9, 'AUS', 'Australia', 'UTC+10:00', '10:00'),
    (10, 'AUT', 'Austria', 'UTC+01:00', '01:00'),
    (11, 'AZE', 'Azerbaijan', 'UTC+04:00', '04:00'),
    (12, 'BHS', 'Bahamas', 'UTC-05:00', '-05:00'),
    (13, 'BHR', 'Bahrain', 'UTC+03:00', '03:00'),
    (14, 'BGD', 'Bangladesh', 'UTC+06:00', '06:00'),
    (15, 'BRB', 'Barbados', 'UTC-04:00', '-04:00'),
    (16, 'BLR', 'Belarus', 'UTC+03:00', '03:00'),
    (17, 'BEL', 'Belgium', 'UTC+01:00', '01:00'),
    (18, 'BLZ', 'Belize', 'UTC-06:00', '-06:00'),
    (19, 'BEN', 'Benin', 'UTC+01:00', '01:00'),
    (20, 'BTN', 'Bhutan', 'UTC+06:00', '06:00'),
    (21, 'BOL', 'Bolivia', 'UTC-04:00', '-04:00'),
    (22, 'BIH', 'Bosnia and Herzegovina', 'UTC+01:00', '01:00'),
    (23, 'BWA', 'Botswana', 'UTC+02:00', '02:00'),
    (24, 'BRA', 'Brazil', 'UTC-03:00', '-03:00'),
    (25, 'BRN', 'Brunei Darussalam', 'UTC+08:00', '08:00'),
    (26, 'BGR', 'Bulgaria', 'UTC+02:00', '02:00'),
    (27, 'BFA', 'Burkina Faso', 'UTC+00:00', '00:00'),
    (28, 'BDI', 'Burundi', 'UTC+02:00', '02:00'),
    (29, 'CPV', 'Cabo Verde', 'UTC-01:00', '-01:00'),
    (30, 'KHM', 'Cambodia', 'UTC+07:00', '07:00'),
    (31, 'CMR', 'Cameroon', 'UTC+01:00', '01:00'),
    (32, 'CAN', 'Canada', 'UTC-05:00', '-05:00'),
    (33, 'CAF', 'Central African Republic', 'UTC+01:00', '01:00'),
    (34, 'TCD', 'Chad', 'UTC+01:00', '01:00'),
    (35, 'CHL', 'Chile', 'UTC-03:00', '-03:00'),
    (36, 'CHN', 'China', 'UTC+08:00', '08:00'),
    (37, 'COL', 'Colombia', 'UTC-05:00', '-05:00'),
    (38, 'COM', 'Comoros', 'UTC+03:00', '03:00'),
    (39, 'COG', 'Congo', 'UTC+01:00', '01:00'),
    (40, 'COD', 'Congo, Democratic Republic of the', 'UTC+02:00', '02:00'),
    (41, 'CRI', 'Costa Rica', 'UTC-06:00', '-06:00'),
    (42, 'CIV', 'Côte Ivoire', 'UTC+00:00', '00:00'),
    (43, 'HRV', 'Croatia', 'UTC+01:00', '01:00'),
    (44, 'CUB', 'Cuba', 'UTC-05:00', '-05:00'),
    (45, 'CYP', 'Cyprus', 'UTC+02:00', '02:00'),
    (46, 'CZE', 'Czech Republic', 'UTC+01:00', '01:00'),
    (47, 'DNK', 'Denmark', 'UTC+01:00', '01:00'),
    (48, 'DJI', 'Djibouti', 'UTC+03:00', '03:00'),
    (49, 'DMA', 'Dominica', 'UTC-04:00', '-04:00'),
    (50, 'DOM', 'Dominican Republic', 'UTC-04:00', '-04:00'),
    (51, 'ECU', 'Ecuador', 'UTC-05:00', '-05:00'),
    (52, 'EGY', 'Egypt', 'UTC+02:00', '02:00'),
    (53, 'SLV', 'El Salvador', 'UTC-06:00', '-06:00'),
    (54, 'GNQ', 'Equatorial Guinea', 'UTC+01:00', '01:00'),
    (55, 'ERI', 'Eritrea', 'UTC+03:00', '03:00'),
    (56, 'EST', 'Estonia', 'UTC+02:00', '02:00'),
    (57, 'SWZ', 'Eswatini', 'UTC+02:00', '02:00'),
    (58, 'ETH', 'Ethiopia', 'UTC+03:00', '03:00'),
    (59, 'FJI', 'Fiji', 'UTC+12:00', '12:00'),
    (60, 'FIN', 'Finland', 'UTC+02:00', '02:00'),
    (61, 'FRA', 'France', 'UTC+01:00', '01:00'),
    (62, 'GAB', 'Gabon', 'UTC+01:00', '01:00'),
    (63, 'GMB', 'Gambia', 'UTC+00:00', '00:00'),
    (64, 'GEO', 'Georgia', 'UTC+04:00', '04:00'),
    (65, 'DEU', 'Germany', 'UTC+01:00', '01:00'),
    (66, 'GHA', 'Ghana', 'UTC+00:00', '00:00'),
    (67, 'GRC', 'Greece', 'UTC+02:00', '02:00'),
    (68, 'GRD', 'Grenada', 'UTC-04:00', '-04:00'),
    (69, 'GTM', 'Guatemala', 'UTC-06:00', '-06:00'),
    (70, 'GIN', 'Guinea', 'UTC+00:00', '00:00'),
    (71, 'GNB', 'Guinea-Bissau', 'UTC+00:00', '00:00'),
    (72, 'GUY', 'Guyana', 'UTC-04:00', '-04:00'),
    (73, 'HTI', 'Haiti', 'UTC-05:00', '-05:00'),
    (74, 'HND', 'Honduras', 'UTC-06:00', '-06:00'),
    (75, 'HUN', 'Hungary', 'UTC+01:00', '01:00'),
    (76, 'ISL', 'Iceland', 'UTC+00:00', '00:00'),
    (77, 'IND', 'India', 'UTC+05:30', '05:30'),
    (78, 'IDN', 'Indonesia', 'UTC+07:00', '07:00'),
    (79, 'IRN', 'Iran', 'UTC+03:30', '03:30'),
    (80, 'IRQ', 'Iraq', 'UTC+03:00', '03:00'),
    (81, 'IRL', 'Ireland', 'UTC+00:00', '00:00'),
    (82, 'ISR', 'Israel', 'UTC+02:00', '02:00'),
    (83, 'ITA', 'Italy', 'UTC+01:00', '01:00'),
    (84, 'JAM', 'Jamaica', 'UTC-05:00', '-05:00'),
    (85, 'JPN', 'Japan', 'UTC+09:00', '09:00'),
    (86, 'JOR', 'Jordan', 'UTC+02:00', '02:00'),
    (87, 'KAZ', 'Kazakhstan', 'UTC+06:00', '06:00'),
    (88, 'KEN', 'Kenya', 'UTC+03:00', '03:00'),
    (89, 'KIR', 'Kiribati', 'UTC+14:00', '14:00'),
    (90, 'PRK', 'Korea (North)', 'UTC+09:00', '09:00'),
    (91, 'KOR', 'Korea (South)', 'UTC+09:00', '09:00'),
    (92, 'KWT', 'Kuwait', 'UTC+03:00', '03:00'),
    (93, 'KGZ', 'Kyrgyzstan', 'UTC+06:00', '06:00'),
    (94, 'LAO', 'Lao PDR', 'UTC+07:00', '07:00'),
    (95, 'LVA', 'Latvia', 'UTC+02:00', '02:00'),
    (96, 'LBN', 'Lebanon', 'UTC+02:00', '02:00'),
    (97, 'LSO', 'Lesotho', 'UTC+02:00', '02:00'),
    (98, 'LBR', 'Liberia', 'UTC+00:00', '00:00'),
    (99, 'LBY', 'Libya', 'UTC+02:00', '02:00'),
    (100, 'LIE', 'Liechtenstein', 'UTC+01:00', '01:00'),
    (101, 'LTU', 'Lithuania', 'UTC+02:00', '02:00'),
    (102, 'LUX', 'Luxembourg', 'UTC+01:00', '01:00'),
    (103, 'MDG', 'Madagascar', 'UTC+03:00', '03:00'),
    (104, 'MWI', 'Malawi', 'UTC+02:00', '02:00'),
    (105, 'MYS', 'Malaysia', 'UTC+08:00', '08:00'),
    (106, 'MDV', 'Maldives', 'UTC+05:00', '05:00'),
    (107, 'MLI', 'Mali', 'UTC+00:00', '00:00'),
    (108, 'MLT', 'Malta', 'UTC+01:00', '01:00'),
    (109, 'MHL', 'Marshall Islands', 'UTC+12:00', '12:00'),
    (110, 'MRT', 'Mauritania', 'UTC+00:00', '00:00'),
    (111, 'MUS', 'Mauritius', 'UTC+04:00', '04:00'),
    (112, 'MEX', 'Mexico', 'UTC-06:00', '-06:00'),
    (113, 'FSM', 'Micronesia', 'UTC+10:00', '10:00'),
    (114, 'MDA', 'Moldova', 'UTC+02:00', '02:00'),
    (115, 'MCO', 'Monaco', 'UTC+01:00', '01:00'),
    (116, 'MNG', 'Mongolia', 'UTC+08:00', '08:00'),
    (117, 'MNE', 'Montenegro', 'UTC+01:00', '01:00'),
    (118, 'MAR', 'Morocco', 'UTC+00:00', '00:00'),
    (119, 'MOZ', 'Mozambique', 'UTC+02:00', '02:00'),
    (120, 'MMR', 'Myanmar', 'UTC+06:30', '06:30'),
    (121, 'NAM', 'Namibia', 'UTC+02:00', '02:00'),
    (122, 'NRU', 'Nauru', 'UTC+12:00', '12:00'),
    (123, 'NPL', 'Nepal', 'UTC+05:45', '05:45'),
    (124, 'NLD', 'Netherlands', 'UTC+01:00', '01:00'),
    (125, 'NZL', 'New Zealand', 'UTC+12:00', '12:00'),
    (126, 'NIC', 'Nicaragua', 'UTC-06:00', '-06:00'),
    (127, 'NER', 'Niger', 'UTC+01:00', '01:00'),
    (128, 'NGA', 'Nigeria', 'UTC+01:00', '01:00'),
    (129, 'MKD', 'North Macedonia', 'UTC+01:00', '01:00'),
    (130, 'NOR', 'Norway', 'UTC+01:00', '01:00'),
    (131, 'OMN', 'Oman', 'UTC+04:00', '04:00'),
    (132, 'PAK', 'Pakistan', 'UTC+05:00', '05:00'),
    (133, 'PLW', 'Palau', 'UTC+09:00', '09:00'),
    (134, 'PAN', 'Panama', 'UTC-05:00', '-05:00'),
    (135, 'PNG', 'Papua New Guinea', 'UTC+10:00', '10:00'),
    (136, 'PRY', 'Paraguay', 'UTC-04:00', '-04:00'),
    (137, 'PER', 'Peru', 'UTC-05:00', '-05:00'),
    (138, 'PHL', 'Philippines', 'UTC+08:00', '08:00'),
    (139, 'POL', 'Poland', 'UTC+01:00', '01:00'),
    (140, 'PRT', 'Portugal', 'UTC+00:00', '00:00'),
    (141, 'QAT', 'Qatar', 'UTC+03:00', '03:00'),
    (142, 'ROU', 'Romania', 'UTC+02:00', '02:00'),
    (143, 'RUS', 'Russia', 'UTC+03:00', '03:00'),
    (144, 'RWA', 'Rwanda', 'UTC+02:00', '02:00'),
    (145, 'KNA', 'Saint Kitts and Nevis', 'UTC-04:00', '-04:00'),
    (146, 'LCA', 'Saint Lucia', 'UTC-04:00', '-04:00'),
    (147, 'VCT', 'Saint Vincent and Grenadines', 'UTC-04:00', '-04:00'),
    (148, 'WSM', 'Samoa', 'UTC+13:00', '13:00'),
    (149, 'SMR', 'San Marino', 'UTC+01:00', '01:00'),
    (150, 'STP', 'Sao Tome and Principe', 'UTC+00:00', '00:00'),
    (151, 'SAU', 'Saudi Arabia', 'UTC+03:00', '03:00'),
    (152, 'SEN', 'Senegal', 'UTC+00:00', '00:00'),
    (153, 'SRB', 'Serbia', 'UTC+01:00', '01:00'),
    (154, 'SYC', 'Seychelles', 'UTC+04:00', '04:00'),
    (155, 'SLE', 'Sierra Leone', 'UTC+00:00', '00:00'),
    (156, 'SGP', 'Singapore', 'UTC+08:00', '08:00'),
    (157, 'SVK', 'Slovakia', 'UTC+01:00', '01:00'),
    (158, 'SVN', 'Slovenia', 'UTC+01:00', '01:00'),
    (159, 'SLB', 'Solomon Islands', 'UTC+11:00', '11:00'),
    (160, 'SOM', 'Somalia', 'UTC+03:00', '03:00'),
    (161, 'ZAF', 'South Africa', 'UTC+02:00', '02:00'),
    (162, 'SSD', 'South Sudan', 'UTC+03:00', '03:00'),
    (163, 'ESP', 'Spain', 'UTC+01:00', '01:00'),
    (164, 'LKA', 'Sri Lanka', 'UTC+05:30', '05:30'),
    (165, 'SDN', 'Sudan', 'UTC+02:00', '02:00'),
    (166, 'SUR', 'Suriname', 'UTC-03:00', '-03:00'),
    (167, 'SWE', 'Sweden', 'UTC+01:00', '01:00'),
    (168, 'CHE', 'Switzerland', 'UTC+01:00', '01:00'),
    (169, 'SYR', 'Syrian Arab Republic', 'UTC+02:00', '02:00'),
    (170, 'TWN', 'Taiwan', 'UTC+08:00', '08:00'),
    (171, 'TJK', 'Tajikistan', 'UTC+05:00', '05:00'),
    (172, 'TZA', 'Tanzania', 'UTC+03:00', '03:00'),
    (173, 'THA', 'Thailand', 'UTC+07:00', '07:00'),
    (174, 'TLS', 'Timor-Leste', 'UTC+09:00', '09:00'),
    (175, 'TGO', 'Togo', 'UTC+00:00', '00:00'),
    (176, 'TON', 'Tonga', 'UTC+13:00', '13:00'),
    (177, 'TTO', 'Trinidad and Tobago', 'UTC-04:00', '-04:00'),
    (178, 'TUN', 'Tunisia', 'UTC+01:00', '01:00'),
    (179, 'TUR', 'Turkey', 'UTC+03:00', '03:00'),
    (180, 'TKM', 'Turkmenistan', 'UTC+05:00', '05:00'),
    (181, 'TUV', 'Tuvalu', 'UTC+12:00', '12:00'),
    (182, 'UGA', 'Uganda', 'UTC+03:00', '03:00'),
    (183, 'UKR', 'Ukraine', 'UTC+02:00', '02:00'),
    (184, 'ARE', 'United Arab Emirates', 'UTC+04:00', '04:00'),
    (185, 'GBR', 'United Kingdom', 'UTC+00:00', '00:00'),
    (186, 'USA', 'United States', 'UTC-05:00', '-05:00'),
    (187, 'URY', 'Uruguay', 'UTC-03:00', '-03:00'),
    (188, 'UZB', 'Uzbekistan', 'UTC+05:00', '05:00'),
    (189, 'VUT', 'Vanuatu', 'UTC+11:00', '11:00'),
    (190, 'VEN', 'Venezuela', 'UTC-04:00', '-04:00'),
    (191, 'VNM', 'Vietnam', 'UTC+07:00', '07:00'),
    (192, 'YEM', 'Yemen', 'UTC+03:00', '03:00'),
    (193, 'ZMB', 'Zambia', 'UTC+02:00', '02:00'),
    (194, 'ZWE', 'Zimbabwe', 'UTC+02:00', '02:00');

DROP TABLE IF EXISTS `events`;

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_code` varchar(100) DEFAULT NULL,
  `competition_code` varchar(100) DEFAULT NULL, --foreign key zu tabelle competitions (competition_code=competitions.id)
  `event_name` varchar(100) NOT NULL,
  `ems_code` varchar(50) DEFAULT NULL,
  `class` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `heatsystem`;

CREATE TABLE `heatsystem` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `heatsystem` int(11) DEFAULT NULL, --foreign key zu tabelle heatsystems (heatsystem=heatsystems.id)
  `athletes` int(11) DEFAULT NULL, --foreign key zu tabelle athletes (athletes=athletes.id)
  `round` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `capacity` int(11) DEFAULT NULL,
  `runs` int(11) DEFAULT NULL,
  `seeding` int(11) DEFAULT NULL,
  `system` varchar(20) DEFAULT NULL,
  `qualify` int(11) DEFAULT NULL,
  `termination` int(11) DEFAULT NULL,
  `reversed` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `heatsystems`;

CREATE TABLE `heatsystems` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(250) DEFAULT NULL,
  `description` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `officials`;

CREATE TABLE `officials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `competition_code` varchar(50) DEFAULT NULL, --foreign key zu tabelle competitions (competition_code=competitions.id)
  `iwwf_id` varchar(50) NOT NULL,
  `position` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `country` varchar(10) NOT NULL,
  `region` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `officials_panel`;

CREATE TABLE `officials_panel` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `competition` int(11) DEFAULT NULL, --foreign key zu tabelle competitions (competition=competitions.id)
  `event` int(11) DEFAULT NULL, --foreign key zu tabelle events (event=events.id)
  `category` int(11) DEFAULT NULL, --foreign key zu tabelle categories (category=categories.id)
  `official` int(11) DEFAULT NULL, --foreign key zu tabelle officials (official=officials.id)?
  `judge` int(11) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `runs`;

CREATE TABLE `runs` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `schedule` int(11) DEFAULT NULL,
  `group` int(11) DEFAULT NULL,
  `order` int(11) DEFAULT NULL,
  `run` int(11) DEFAULT NULL,
  `athlete` int(11) DEFAULT NULL, --foreign key zu tabelle athletes (athlete=athletes.id)?
  `origin` varchar(100) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `real_time` int(11) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `schedule`;

CREATE TABLE `schedule` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `comp` int(11) DEFAULT NULL, --foreign key zu tabelle competitions (comp=competitions.id)?
  `event` int(11) DEFAULT NULL, --foreign key zu tabelle events (event=events.id)?
  `category` int(11) DEFAULT NULL, --foreign key zu tabelle categories (category=categories.id)?
  `heatsystem` int(11) DEFAULT NULL, --foreign key zu tabelle heatsystem (heatsystem=heatsystem.id)?
  `order` int(11) DEFAULT NULL,
  `time_planned` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `scores`;

CREATE TABLE `scores` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `run` int(11) DEFAULT NULL, --foreign key zu tabelle runs (run=runs.id)?
  `judge` int(11) DEFAULT NULL, --foreign key zu tabelle officials (judge=officials.id)?
  `score_type` int(11) DEFAULT NULL,
  `score` float(4,2) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `settings`;

CREATE TABLE `settings` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `competition_code` varchar(11) DEFAULT NULL, --foreign key zu tabelle competitions (competition_code=competitions.id)?
  `event` int(11) DEFAULT NULL, --foreign key zu tabelle events (event=events.id)?
  `heatsystem` int(11) DEFAULT NULL, --foreign key zu tabelle heatsystem (heatsystem=heatsystem.id)?
  `runtime` int(11) DEFAULT NULL,
  `judges` int(11) DEFAULT NULL, --foreign key zu tabelle officials (judges=officials.id)?
  `mode` int(11) DEFAULT NULL,
  `judging` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

DROP TABLE IF EXISTS `tv`;

CREATE TABLE `tv` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `competition_code` varchar(100) DEFAULT NULL, -- zu prüfen mit tabelle competitions (competition_code=competitions.id)
  `athlete` int(11) DEFAULT NULL, -- zu prüfen mit tabelle athletes (athlete=athletes.id)
  `profile` int(11) DEFAULT NULL, -- zu prüfen mit tabelle athletes (profile=athletes.id)
  `startlist` int(11) DEFAULT NULL, -- zu prüfen mit tabelle athletes (startlist=athletes.id)
  `results` int(11) DEFAULT NULL, -- zu prüfen mit tabelle athletes (results=athletes.id)
  `schedule` int(11) DEFAULT NULL, -- zu prüfen mit tabelle schedule (schedule=schedule.id)
  `automate` int(11) DEFAULT NULL, -- zu prüfen mit tabelle schedule (automate=schedule.id)
  PRIMARY KEY (`id`)
);


DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `iwwf_id` varchar(100) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  `fed_admin` int(11) DEFAULT NULL,
  `council_admin` int(11) DEFAULT NULL,
  `token` varchar(300) DEFAULT NULL,
  `token_expiration` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);
/*
ALTER TABLE `athletes` ADD FOREIGN KEY (`competition_code`) REFERENCES `competitions` (`code`);
ALTER TABLE `athletes` ADD FOREIGN KEY (`event_code`) REFERENCES `events` (`event_code`);
ALTER TABLE `athletes` ADD FOREIGN KEY (`real_category`) REFERENCES `categories` (`code`);
ALTER TABLE `athletes` ADD FOREIGN KEY (`category_in_competition`) REFERENCES `categories` (`code`);
ALTER TABLE `competitions` ADD FOREIGN KEY (`organizing_country`) REFERENCES `countries` (`id`);
ALTER TABLE `events` ADD FOREIGN KEY (`competition_code`) REFERENCES `competitions` (`code`);
ALTER TABLE `officials` ADD FOREIGN KEY (`competition_code`) REFERENCES `competitions` (`code`);
ALTER TABLE `officials_panel` ADD FOREIGN KEY (`competition`) REFERENCES `competitions` (`id`);
ALTER TABLE `officials_panel` ADD FOREIGN KEY (`event`) REFERENCES `events` (`id`);
ALTER TABLE `officials_panel` ADD FOREIGN KEY (`category`) REFERENCES `categories` (`id`);
ALTER TABLE `officials_panel` ADD FOREIGN KEY (`official`) REFERENCES `officials` (`id`);
ALTER TABLE `runs` ADD FOREIGN KEY (`schedule`) REFERENCES `schedule` (`id`);
ALTER TABLE `runs` ADD FOREIGN KEY (`athlete`) REFERENCES `athletes` (`id`);
ALTER TABLE `schedule` ADD FOREIGN KEY (`comp`) REFERENCES `competitions` (`id`);
ALTER TABLE `schedule` ADD FOREIGN KEY (`event`) REFERENCES `events` (`id`);
ALTER TABLE `schedule` ADD FOREIGN KEY (`category`) REFERENCES `categories` (`id`);
ALTER TABLE `schedule` ADD FOREIGN KEY (`heatsystem`) REFERENCES `heatsystem` (`id`);
ALTER TABLE `scores` ADD FOREIGN KEY (`run`) REFERENCES `runs` (`id`);
ALTER TABLE `scores` ADD FOREIGN KEY (`judge`) REFERENCES `officials` (`id`);
ALTER TABLE `settings` ADD FOREIGN KEY (`competition_code`) REFERENCES `competitions` (`code`);
ALTER TABLE `settings` ADD FOREIGN KEY (`event`) REFERENCES `events` (`id`);
ALTER TABLE `settings` ADD FOREIGN KEY (`heatsystem`) REFERENCES `heatsystem` (`id`);
*/
