CREATE DATABASE IF NOT EXISTS veronacup;

USE veronacup;

CREATE TABLE IF NOT EXISTS tournaments (
  tournament_id BIGINT(20) unsigned NOT NULL auto_increment,
  tournament_name VARCHAR(40) NOT NULL UNIQUE,
  tournament_start DATETIME default "0000-01-01 00:00:00",  
  tournament_end DATETIME default "0000-01-01 00:00:00",
  tournament_created DATETIME default "0000-01-01 00:00:00",
  tournament_active INT default 0,
  deleted_id INT DEFAULT 0,
  PRIMARY KEY (tournament_id),
  KEY idx_tournament_key (tournament_name),
  KEY idx_active_key (tournament_active)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS mappointtypes(
  point_type_id BIGINT(20) unsigned NOT NULL auto_increment,
  point_type_description VARCHAR(50)  NOT NULL,
  point_type_group INT NOT NULL,
  deleted_id INT DEFAULT 0,
  PRIMARY KEY (point_type_id)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE mappointtypes ADD UNIQUE INDEX(point_type_id,point_type_description);

CREATE TABLE IF NOT EXISTS mappoints(
  point_id BIGINT(20) unsigned NOT NULL auto_increment,
  point_type INT NOT NULL REFERENCES mapPointTypes(point_type_id),
  point_description_dk VARCHAR(50) NOT NULL,
  point_description_en VARCHAR(50) NOT NULL,
  point_latitude DOUBLE DEFAULT 0.0,
  point_longitude DOUBLE DEFAULT 0.0,
  point_order INT NOT NULL,
  deleted_id INT DEFAULT 0,
  PRIMARY KEY (point_id),
  KEY point_description_01_key (point_description_en),
  KEY point_description_02_key (point_description_dk),
  KEY idx_point_type_key (point_type)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE mappoints ADD UNIQUE INDEX(point_type,point_description_en,point_description_dk);

CREATE TABLE IF NOT EXISTS infoelements(
  info_id BIGINT(20) unsigned NOT NULL auto_increment,
  info_order INT NOT NULL,
  info_header_dk VARCHAR(100) NOT NULL,
  info_header_en VARCHAR(100) NOT NULL,
  info_link_dk VARCHAR(100) NOT NULL,
  info_link_en VARCHAR(100) NOT NULL,
  deleted_id INT DEFAULT 0,
  PRIMARY KEY (info_id),
  KEY idx_info_order_key (info_order)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE infoelements ADD UNIQUE INDEX(info_header_dk,info_header_en);

CREATE TABLE IF NOT EXISTS gameclasses (
  class_id BIGINT(20) unsigned NOT NULL auto_increment,
  class_name VARCHAR(30) NOT NULL,
  class_order INT DEFAULT 999,
  class_game_halfs INT DEFAULT 0,
  class_game_mins INT DEFAULT 0,
  deleted_id INT DEFAULT 0,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (class_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref),
  KEY idx_class_name_key (class_name)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE gameclasses ADD UNIQUE INDEX(class_name,tournament_id_ref);

CREATE TABLE IF NOT EXISTS gamegroups (
  group_id BIGINT(20) unsigned NOT NULL auto_increment,
  group_name VARCHAR(30) NOT NULL,
  group_class_id_ref INT NOT NULL REFERENCES gameclasses(class_id),
  group_isPlayoff BIT DEFAULT FALSE,
  group_playoff_level INT DEFAULT 0,
  group_autoclose BIT DEFAULT TRUE,
  deleted_id INT DEFAULT 0,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (group_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref),
  KEY idx_group_name_key (group_name),
  KEY idx_group_class_id_ref_key (group_class_id_ref)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE gamegroups ADD UNIQUE INDEX(group_name,group_class_id_ref,tournament_id_ref);

CREATE TABLE IF NOT EXISTS clubs (
  club_id BIGINT(20) unsigned NOT NULL auto_increment,
  club_name VARCHAR(40) NOT NULL,
  club_shirt VARCHAR(20),
  club_shorts VARCHAR(20),
  club_country VARCHAR(20),
  deleted_id INT DEFAULT 0,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (club_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref),
  KEY idx_club_name_key (club_name)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE clubs ADD UNIQUE INDEX(club_name,tournament_id_ref);

CREATE TABLE IF NOT EXISTS teams (
  team_id BIGINT(20) unsigned NOT NULL auto_increment,
  team_name VARCHAR(40) NOT NULL,
  team_club_id INT NOT NULL REFERENCES clubs(club_id),
  team_group_id INT NOT NULL REFERENCES gamegroups(group_id),
  team_position INT DEFAULT 1,
  team_position_manual INT DEFAULT 0,
  team_position_manual_note VARCHAR(140) NOT NULL DEFAULT "",
  team_played INT DEFAULT 0,
  team_won INT DEFAULT 0,
  team_draw INT DEFAULT 0,
  team_lost INT DEFAULT 0,
  team_points INT DEFAULT 0,
  team_scoreFor INT DEFAULT 0,
  team_scoreAgainst INT DEFAULT 0,
  team_calculate_class_pos BIT DEFAULT TRUE,
  team_calculate_position INT DEFAULT -1,
  team_calculate_points INT DEFAULT 0,
  team_calculate_scoreFor INT DEFAULT 0,
  team_calculate_scoreAgainst INT DEFAULT 0,
  team_delta_points INT DEFAULT 0,
  team_calculate_class_position INT DEFAULT 0,
  deleted_id INT DEFAULT 0,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (team_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref),
  KEY idx_team_name_key (team_name),
  KEY idx_team_club_id_key (team_club_id)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE teams ADD UNIQUE INDEX(team_name,team_group_id,team_club_id,tournament_id_ref);

CREATE TABLE IF NOT EXISTS games (
  game_id BIGINT(20) unsigned NOT NULL auto_increment,
  game_name VARCHAR(10) NOT NULL,
  game_group_id INT NOT NULL REFERENCES gamegroups(group_id),
  game_team1_id INT NOT NULL REFERENCES teams(team_id),
  game_team2_id INT NOT NULL REFERENCES teams(team_id),
  game_start DATETIME NOT NULL DEFAULT "0000-01-01 00:00:00",
  game_end DATETIME NOT NULL DEFAULT "0000-01-01 00:00:00",
  game_pitch_id INT NOT NULL REFERENCES mapPoints(point_id),
  game_team1_score INT,
  game_team2_score INT,
  game_team1_livescore INT,
  game_team2_livescore INT,
  game_livescore_active BIT DEFAULT TRUE,
  game_team1_from_group INT REFERENCES gamegroups(group_id),
  game_team1_from_group_position INT REFERENCES gamegroups(group_id),
  game_team1_from_game_winner INT REFERENCES games(game_id),
  game_team1_from_game_looser INT REFERENCES games(game_id),
  game_team1_class_pos INT,
  game_team2_from_group INT REFERENCES gamegroups(group_id),
  game_team2_from_group_position INT REFERENCES gamegroups(group_id),
  game_team2_from_game_winner INT REFERENCES games(game_id),
  game_team2_from_game_looser INT REFERENCES games(game_id),
  game_team2_class_pos INT,
  game_team1_penalties INT,
  game_team2_penalties INT,
  game_playoff_level INT DEFAULT 0,
  game_note VARCHAR(140) NOT NULL DEFAULT "",
  game_locked BIT DEFAULT FALSE,
  deleted_id INT DEFAULT 0,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (game_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref),
  KEY idx_game_team1_id_key (game_team1_id),
  KEY idx_game_team2_id_key (game_team2_id),
  KEY idx_game_playoff_level_key (game_playoff_level),
  KEY idx_game_start_key (game_start),
  KEY idx_game_pitch_id_key (game_pitch_id)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE games ADD UNIQUE INDEX(game_name,tournament_id_ref);

CREATE TABLE IF NOT EXISTS subscribers(
  subscriber_id BIGINT(20) unsigned NOT NULL auto_increment,
  subscriber_token VARCHAR(200) NOT NULL,
  subscriber_language INT NOT NULL,
  subscriber_is_android BIT NOT NULL,
  subscriber_livescore_enabled BIT DEFAULT 1,
  subscriber_last_update DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (subscriber_id),
  KEY idx_subscriber_is_android_key (subscriber_is_android),
  KEY idx_subscriber_token_key (subscriber_token)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE subscribers ADD UNIQUE INDEX(subscriber_token);

CREATE TABLE IF NOT EXISTS subscribetogame(
  subscribeToGame_id BIGINT(20) unsigned NOT NULL auto_increment,
  subscribeToGame_subscriber_id INT NOT NULL REFERENCES subscribers(subscriber_id),
  subscribeToGame_game_id INT NOT NULL REFERENCES games(game_id),
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (subscribeToGame_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref),
  KEY idx_subscribeToGame_subscriber_id_key (subscribeToGame_subscriber_id),
  KEY idx_subscribeToGame_game_id_key (subscribeToGame_game_id)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE subscribetogame ADD UNIQUE INDEX(subscribeToGame_subscriber_id,subscribeToGame_game_id,tournament_id_ref);

CREATE TABLE IF NOT EXISTS subscribetoteam(
  subscribeToTeam_id BIGINT(20) unsigned NOT NULL auto_increment,
  subscribeToTeam_subscriber_id INT NOT NULL REFERENCES subscribers(subscriber_id),
  subscribeToTeam_team_id INT NOT NULL REFERENCES teams(team_id),
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (subscribeToTeam_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref),
  KEY idx_subscribeToTeam_subscriber_id_key (subscribeToTeam_subscriber_id),
  KEY idx_subscribeToteam_id_key (subscribeToteam_id)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE subscribetoteam ADD UNIQUE INDEX(subscribeToTeam_subscriber_id,subscribeToteam_id,tournament_id_ref);

CREATE TABLE IF NOT EXISTS schedulepush(
  push_id BIGINT(20) unsigned NOT NULL auto_increment,
  push_time DATETIME NOT NULL,
  push_header_dk VARCHAR(150) NOT NULL,
  push_header_en VARCHAR(150) NOT NULL,
  push_message_dk VARCHAR(235) NOT NULL,
  push_message_en VARCHAR(235) NOT NULL,
  push_query VARCHAR(400) NOT NULL,
  push_category_dk VARCHAR(150) NOT NULL,
  push_category_en VARCHAR(150) NOT NULL,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (push_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref),
  KEY idx_push_time_key (push_time)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE schedulepush ADD UNIQUE INDEX(push_time,push_message_dk,push_message_en,tournament_id_ref);

CREATE TABLE IF NOT EXISTS sentpush(
  push_id BIGINT(20) unsigned NOT NULL auto_increment,
  push_time DATETIME NOT NULL,
  push_header_dk VARCHAR(150) NULL,
  push_header_en VARCHAR(150) NULL,
  push_message_dk VARCHAR(235) NULL,
  push_message_en VARCHAR(235) NULL,
  push_category_dk VARCHAR(150),
  push_category_en VARCHAR(150),
  deleted_id INT DEFAULT 0,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (push_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref),
  KEY idx_push_time_key (push_time)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE sentpush ADD UNIQUE INDEX(push_time,push_message_dk,push_message_en,tournament_id_ref);

CREATE TABLE IF NOT EXISTS flags(
  flag_id BIGINT(20) unsigned NOT NULL auto_increment,
  flag_favorites_version INT NOT NULL DEFAULT 0,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (flag_id)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

INSERT IGNORE INTO mappointtypes (point_type_id,point_type_description) VALUES (1,'fields');
INSERT IGNORE INTO mappointtypes (point_type_id,point_type_description) VALUES (2,'infos');


CREATE TABLE IF NOT EXISTS competences(
  competences_id BIGINT(20) unsigned NOT NULL auto_increment,
  competences_index BIGINT(20) unsigned DEFAULT 0,
  competences_description VARCHAR(150) NULL,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (competences_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref)
)DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS helpers(
  helper_id BIGINT(20) unsigned NOT NULL auto_increment,
  helper_fullname VARCHAR(150) NULL,
  helper_address VARCHAR(100) NULL,
  helper_postal_code VARCHAR(7) NULL,
  helper_city VARCHAR(50) NULL,
  helper_birthdate DATETIME default "0000-01-01 00:00:00",  
  helper_language VARCHAR(50) NULL,
  helper_active BIT DEFAULT FALSE,
  helper_userlevel BIT DEFAULT 0,
  helper_token VARCHAR(200) NOT NULL,
  helper_isAndroid BIT NOT NULL,
  helper_user_name VARCHAR(150) NOT NULL,
  helper_password VARCHAR(130) NOT NULL,
  helper_email VARCHAR(150) NOT NULL,
  helper_salt VARCHAR(130) NOT NULL,
  helper_loginString VARCHAR(130),
  helper_loginTimestamp DATETIME default CURRENT_TIMESTAMP,
  helper_phone1 VARCHAR(20) NOT NULL,
  helper_phone2 VARCHAR(20) NOT NULL,
  helper_phone3 VARCHAR(20) NOT NULL,
  helper_competences INT DEFAULT 0,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (helper_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref)
)DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS taskset(
  taskset_id BIGINT(20) unsigned NOT NULL auto_increment,
  taskset_description VARCHAR(150) NULL,
  taskset_responsible INT NOT NULL REFERENCES helpers(helper_id),
  taskset_public BIT DEFAULT FALSE,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (taskset_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref)
)DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS tasks(
  task_id BIGINT(20) unsigned NOT NULL auto_increment,
  task_description VARCHAR(150) NOT NULL,
  task_start_time DATETIME DEFAULT "0000-01-01 00:00:00",
  task_end_time DATETIME DEFAULT "0000-01-01 00:00:00",
  task_planlink VARCHAR(150),
  task_category INT DEFAULT 0,
  task_allow_switch BIT DEFAULT FALSE,
  task_comment VARCHAR(150),
  task_allocation BIGINT(20) DEFAULT -1 REFERENCES helpers(helper_id),
  task_map_latitude DOUBLE DEFAULT 0.0,
  task_map_longitude DOUBLE DEFAULT 0.0,
  task_description_link VARCHAR(150) NOT NULL,
  task_notification_timespan INT DEFAULT 0,
  task_notification_delivered BIT DEFAULT FALSE,
  task_minimum_age INT DEFAULT 0,
  task_needed_competences INT DEFAULT 0,
  task_completed BIT DEFAULT FALSE,
  task_started BIT DEFAULT FALSE,
  tournament_id_ref INT NOT NULL REFERENCES tournaments(tournament_id),
  PRIMARY KEY (task_id),
  KEY idx_tournament_id_ref_key (tournament_id_ref)  
)DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS tasksetcollection(
  tasksetcollection_id BIGINT(20) unsigned NOT NULL auto_increment,
  tasksetcollection_taskset_id BIGINT(20) REFERENCES taskset(taskset_id),
  tasksetcollection_task_id BIGINT(20) REFERENCES tasks(task_id),
  PRIMARY KEY (tasksetcollection_id)
)DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS taskswap(
  taskswap_id BIGINT(20) unsigned NOT NULL auto_increment,
  task1_id BIGINT(20) REFERENCES tasks(task_id),
  task2_id BIGINT(20) REFERENCES tasks(task_id),
  task1_approval BIT DEFAULT TRUE,
  task2_approval BIT DEFAULT FALSE,  
  PRIMARY KEY (taskswap_id)
)DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


