/**
* DATABASE MIGRATIONS
*/
protected static function migrateToVersion1()
{
$db = Database::getConnection();
$db->query(<<<QUERY
\t\t\tCREATE TABLE `autologins` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `user_id` bigint(20) unsigned NOT NULL,
\t\t\t `browser_parameters_hash` char(64) NOT NULL,
\t\t\t `key_hash` varchar(255) NOT NULL,
\t\t\t `epoch_created` bigint(20) unsigned NOT NULL,
\t\t\t `epoch_last_used` bigint(20) unsigned DEFAULT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `downloads` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `extension` varchar(8) NOT NULL,
\t\t\t `title` varchar(128) NOT NULL,
\t\t\t `description` varchar(255) DEFAULT NULL,
\t\t\t `type` tinyint unsigned NOT NULL,
\t\t\t `league_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `restricted` tinyint(1) unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `fixtures` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `home_team_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `home_team_assigned_number` bigint(20) unsigned DEFAULT NULL,
\t\t\t `away_team_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `away_team_assigned_number` bigint(20) unsigned DEFAULT NULL,
\t\t\t `league_id` bigint(20) unsigned NOT NULL,
\t\t\t `play_by_date` date NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `league_sections` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `letter` varchar(4) NOT NULL,
\t\t\t `league_id` bigint(20) unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `league_letter` (`letter`,`league_id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `leagues` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `name` varchar(32) NOT NULL,
\t\t\t `manager_id` bigint(20) unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `match_reports` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `match_id` bigint(20) unsigned NOT NULL,
\t\t\t `user_id` bigint(20) unsigned NOT NULL,
\t\t\t `epoch` bigint(20) unsigned NOT NULL,
\t\t\t `home_score` tinyint unsigned NOT NULL,
\t\t\t `away_score` tinyint unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `matches` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `date` date NOT NULL,
\t\t\t `league_id` bigint(20) unsigned NOT NULL,
\t\t\t `home_team_id` bigint(20) unsigned NOT NULL,
\t\t\t `away_team_id` bigint(20) unsigned NOT NULL,
\t\t\t `home_score` tinyint unsigned NOT NULL,
\t\t\t `away_score` tinyint unsigned NOT NULL,
\t\t\t `status` tinyint unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `matches_players` (
\t\t\t `match_id` bigint(20) unsigned NOT NULL,
\t\t\t `player_id` bigint(20) unsigned NOT NULL,
\t\t\t `team_id` bigint(20) unsigned NOT NULL
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `organizations` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `name` varchar(128) NOT NULL,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `name` (`name`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `permissions` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `type` varchar(16) NOT NULL,
\t\t\t `name` varchar(32) NOT NULL,
\t\t\t `description` varchar(128) NOT NULL,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `name` (`name`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `players` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `full_name` varchar(128) NOT NULL,
\t\t\t `team_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `exempt` tinyint(1) unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `settings` (
\t\t\t `id` varchar(64) NOT NULL,
\t\t\t `value` longtext NOT NULL,
\t\t\t PRIMARY KEY (`id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `teams` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `designation` varchar(32) NOT NULL,
\t\t\t `organization_id` bigint(20) unsigned NOT NULL,
\t\t\t `league_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `league_section_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t `assigned_number` bigint(20) unsigned DEFAULT NULL,
\t\t\t `epoch_registered` bigint(20) unsigned NOT NULL,
\t\t\t `registrant_id` bigint(20) unsigned NOT NULL,
\t\t\t `score_for` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t `score_against` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t `wins` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t `draws` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t `losses` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t `points` smallint(6) unsigned NOT NULL DEFAULT 0,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `organization_designation` (`organization_id`,`designation`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `user_groups` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `name` varchar(64) NOT NULL,
\t\t\t `special` tinyint(1) unsigned NOT NULL,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `name` (`name`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `user_groups_permissions` (
\t\t\t `group_id` bigint(20) unsigned NOT NULL,
\t\t\t `permission_id` bigint(20) unsigned NOT NULL,
\t\t\t UNIQUE KEY `group_permission` (`group_id`,`permission_id`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `users` (
\t\t\t `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
\t\t\t `email` varchar(254) NOT NULL,
\t\t\t `password_hash` varchar(255) NOT NULL,
\t\t\t `full_name` varchar(64) NOT NULL,
\t\t\t `phone_number` varchar(32) NOT NULL,
\t\t\t `group_id` bigint(20) unsigned NOT NULL,
\t\t\t `organization_id` bigint(20) unsigned DEFAULT NULL,
\t\t\t PRIMARY KEY (`id`),
\t\t\t UNIQUE KEY `email` (`email`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\t\t\tCREATE TABLE `valid_team_designations` (
\t\t\t `designation` varchar(32) NOT NULL,
\t\t\t PRIMARY KEY (`designation`)
\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8;
QUERY
);
Permission::add("Admin", "AdminAccessDashboard", "Access the admin dashboard");
Permission::add("Admin", "AdminUsers", "Administrate users");
Permission::add("Admin", "AdminUserGroups", "Administrate user groups and their assigned permissions");
Permission::add("Admin", "AdminOrganizations", "Administrate organizations");
Permission::add("Admin", "AdminTeams", "Administrate teams");
Permission::add("Admin", "AdminMatches", "Administrate matches and match reports");
Permission::add("Admin", "AdminPlayers", "Administrate players");
Permission::add("Admin", "AdminAllLeagues", "Administrate all leagues (users always have permission to administrate leagues they are assigned as the manager of)");
Permission::add("Admin", "AdminAccessMaintenance", "Access the admin maintenance area and use the maintenance tools");
Permission::add("Admin", "PerformDeletionOperations", "Permanently delete data which they have admin access to");
Permission::add("Team", "RegisterTeamsForOwnOrganization", "Register teams for their own organization");
Permission::add("Team", "RegisterTeamsForAnyOrganization", "Register teams for any organization");
Permission::add("Info", "ViewManagerContactInfo", "View contact information for league managers");
Permission::add("Match", "SubmitMatchReport", "Submit match reports for completed matches");
}
