SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `One in a Million` ;
CREATE SCHEMA IF NOT EXISTS `One in a Million` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `One in a Million` ;

-- -----------------------------------------------------
-- Table `One in a Million`.`Player`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `One in a Million`.`Player` ;

CREATE  TABLE IF NOT EXISTS `One in a Million`.`Player` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Given Name` VARCHAR(50) NOT NULL ,
  `Family Name` VARCHAR(50) NOT NULL ,
  `Middle Name` VARCHAR(50) NULL ,
  `Address` VARCHAR(255) NULL ,
  `PostCode` VARCHAR(9) NOT NULL ,
  `Longitude` DOUBLE NULL ,
  `Latitude` DOUBLE NULL ,
  `MapPoint` POINT NULL ,
  `DoB` DATE NULL ,
  `Home Tel` VARCHAR(13) NULL ,
  `Mobile Phone` VARCHAR(13) NULL ,
  PRIMARY KEY (`ID`) ,
  UNIQUE INDEX `Kid` (`Given Name` ASC, `Family Name` ASC, `Middle Name` ASC, `Address` ASC, `PostCode` ASC) )
ENGINE = InnoDB
COMMENT = 'The main listing of registered kids';


-- -----------------------------------------------------
-- Placeholder table for view `One in a Million`.`Player Enhanced`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `One in a Million`.`Player Enhanced` (`ID` INT, `Given Name` INT, `Family Name` INT, `Middle Name` INT, `Home Tel` INT, `Mobile Phone` INT);

-- -----------------------------------------------------
-- Placeholder table for view `One in a Million`.`My Players`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `One in a Million`.`My Players` (`id` INT);

-- -----------------------------------------------------
-- Placeholder table for view `One in a Million`.`Player List`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `One in a Million`.`Player List` (`ID` INT, `Name` INT);

-- -----------------------------------------------------
-- Placeholder table for view `One in a Million`.`My Player IDs`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `One in a Million`.`My Player IDs` (`ID` INT);

-- -----------------------------------------------------
-- View `One in a Million`.`Player Enhanced`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `One in a Million`.`Player Enhanced` ;
DROP TABLE IF EXISTS `One in a Million`.`Player Enhanced`;
USE `One in a Million`;
CREATE  OR REPLACE VIEW `One in a Million`.`Player Enhanced` AS select ID,`Given Name`,`Family Name`,`Middle Name`,`Home Tel`,`Mobile Phone` from `One in a Million`.`My Players`
;

-- -----------------------------------------------------
-- View `One in a Million`.`My Players`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `One in a Million`.`My Players` ;
DROP TABLE IF EXISTS `One in a Million`.`My Players`;
USE `One in a Million`;
CREATE  OR REPLACE VIEW `One in a Million`.`My Players` AS select player.* from player,`My Player IDs` where player.ID=`My Player IDs`.ID;

-- -----------------------------------------------------
-- View `One in a Million`.`Player List`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `One in a Million`.`Player List` ;
DROP TABLE IF EXISTS `One in a Million`.`Player List`;
USE `One in a Million`;
CREATE  OR REPLACE VIEW `One in a Million`.`Player List` AS select ID,concat(`Given Name`,if(isnull(`Middle Name`),'',concat(' ',`Middle Name`)),' ',`Family Name`) `Name` from `My Players`;
;

-- -----------------------------------------------------
-- View `One in a Million`.`My Player IDs`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `One in a Million`.`My Player IDs` ;
DROP TABLE IF EXISTS `One in a Million`.`My Player IDs`;
USE `One in a Million`;
CREATE  OR REPLACE VIEW `One in a Million`.`My Player IDs` AS select distinct p.id ID from `One in a Million`.player p,`One in a Million`.division d,`One in a Million`.DatabaseID L where p.`sponsoring division`=d.ID and d.leader=L.ID union
       select p.id from `One in a Million`.player p,`One in a Million`.`Division Administrators` da,`One in a Million`.DatabaseID L where p.`sponsoring division`=da.Division_ID and da.Volunteer_ID=L.ID union
       select `Kids Base_ID` from `One in a Million`.`Player interest in Programmes` p,`One in a Million`.Programme Pgm,`One in a Million`.DatabaseID L where p.Programmes_id=Pgm.ID and Pgm.Leader=L.ID union
       select p.Player_ID from `One in a Million`.`Players signup to Projects` p,`One in a Million`.Project Pj,`One in a Million`.DatabaseID L where p.Project_ID=Pj.ID and Pj.Leader=L.ID and (Pj.finish is null or Pj.finish > now())
;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;