CREATE DATABASE IF NOT EXISTS tournamentSystemBase;
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY 'difficult#kodeord4database';

USE tournamentSystemBase;

CREATE TABLE IF NOT EXISTS customers (
  uniqueid BIGINT(20) unsigned NOT NULL auto_increment,
  name VARCHAR(100) UNIQUE NOT NULL,
  databaseName VARCHAR(100) UNIQUE NOT NULL,
  link VARCHAR(150) UNIQUE NOT NULL,
  PRIMARY KEY (uniqueid),
  KEY idx_name_key (name)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS userlevels (
  uniqueid BIGINT(20) unsigned NOT NULL auto_increment,
  name VARCHAR(20) UNIQUE NOT NULL,
  description VARCHAR(100) UNIQUE NOT NULL,
  mask INT UNIQUE NOT NULL,
  PRIMARY KEY (uniqueid),
  KEY idx_name_key (name),
  KEY idx_description_key (description)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS users (
  uniqueid BIGINT(20) unsigned NOT NULL auto_increment,
  username VARCHAR(30) NOT NULL,
  email VARCHAR(150) NOT NULL,
  password VARCHAR(130) NOT NULL,
  salt VARCHAR(130) NOT NULL,
  resetString VARCHAR(130),
  loginString VARCHAR(130),
  loginTimestamp DATETIME default CURRENT_TIMESTAMP,
  loginAttempts INT DEFAULT 0,
  userlevel INT NOT NULL DEFAULT 0,
  superuser BIT DEFAULT FALSE,
  customer INT NOT NULL REFERENCES customers(uniqueid),
  PRIMARY KEY (uniqueid),
  KEY idx_username_key (username),
  KEY idx_loginstring_key (loginString),
  KEY idx_password_key (password)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
ALTER TABLE users ADD UNIQUE INDEX(username,customer);

CREATE TABLE IF NOT EXISTS loginattempts (
  uniqueid BIGINT(20) unsigned NOT NULL auto_increment,
  userid INT NOT NULL REFERENCES user(uniqueid),
  logintime DATETIME default CURRENT_TIMESTAMP,
  PRIMARY KEY (uniqueid),
  KEY idx_userid_key (userid),
  KEY idx_logintime_key (logintime)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS features (
  uniqueid BIGINT(20) unsigned NOT NULL auto_increment,
  name VARCHAR(20) UNIQUE NOT NULL,
  description VARCHAR(150) UNIQUE NOT NULL,
  mask INT NOT NULL DEFAULT 0,
  PRIMARY KEY (uniqueid),
  KEY idx_mask_key (mask)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS customerfeatures (
  uniqueid BIGINT(20) unsigned NOT NULL auto_increment,
  customerid VARCHAR(20) UNIQUE NOT NULL REFERENCES customers(uniqueid),
  featureid VARCHAR(150) UNIQUE NOT NULL REFERENCES features(uniqueid),
  PRIMARY KEY (uniqueid),
  KEY idx_customer_key (customerid)
) DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;


INSERT IGNORE INTO userlevels (uniqueid,name,description,mask) VALUES (1,'admin','Request paid app updates and create new users for customer',64);
INSERT IGNORE INTO userlevels (uniqueid,name,description,mask) VALUES (2,'taskplanner','Plan tasks for helpers',32);
INSERT IGNORE INTO userlevels (uniqueid,name,description,mask) VALUES (3,'planner','Create tournament, add schedule, edit schedule, add/remove teams, add/remove groups, add/remove classes, add/remove games',16);
INSERT IGNORE INTO userlevels (uniqueid,name,description,mask) VALUES (4,'control','Update all customer information (web and app) in web interface',8);
INSERT IGNORE INTO userlevels (uniqueid,name,description,mask) VALUES (5,'push','Send and schedule push notifications',4);
INSERT IGNORE INTO userlevels (uniqueid,name,description,mask) VALUES (6,'report','Report results on games',2);
INSERT IGNORE INTO userlevels (uniqueid,name,description,mask) VALUES (7,'monitor','See statistics and other tournament/app data',1);
 
INSERT IGNORE INTO customers (uniqueid,name,databaseName, link) VALUES (2,'Vildbjerg Cup','vildbjergcup','VildbjergCup');

INSERT IGNORE INTO users (uniqueid,username,email,password,salt,userlevel,customer,superuser) VALUES (2,'TestUser','thomasmoesgaard@hotmail.com','d6fe59bac0afdf5c05b89a59090eec90d3901036ed0168aac30aed430966e8eafd32074b6cf8c408339c7ec4fd2690396b7f223d22a03423ba2a7b446ccedc6e','d02f90393ff873a96123103e4e80698ebb58e2b5ab51faf45705fed96945c60318f017ec4caba1be7d27c8d20289101fd273f424308d9ee23fe335e199c6afff',255,2,TRUE);
