-- -----------------------------------------------------
-- Table `ascmanagtool`.`Club`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ascmanagtool`.`Club` ;

CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Club` (
  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(500) NOT NULL,
  `location` VARCHAR(500) NOT NULL,
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`),
  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ascmanagtool`.`Group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ascmanagtool`.`Group` ;

CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Group` (
  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `club_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(500) NOT NULL,
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`),
  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
  INDEX `fk_club_id_idx` (`club_id` ASC),
  CONSTRAINT `fk_club_id`
    FOREIGN KEY (`club_id`)
    REFERENCES `ascmanagtool`.`Club` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ascmanagtool`.`Licenced`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ascmanagtool`.`Licenced` ;

CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Licenced` (
  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `licence_number` INT UNSIGNED NOT NULL,
  `gender` TINYINT(1) UNSIGNED NOT NULL,
  `birth_date` DATETIME NOT NULL,
  `birth_city` VARCHAR(500) NOT NULL,
  `nationality` VARCHAR(500) NOT NULL,
  `firstname` VARCHAR(500) NOT NULL,
  `lastname` VARCHAR(500) NOT NULL,
  `road_num` INT(3) UNSIGNED NOT NULL,
  `street_name` VARCHAR(500) NOT NULL,
  `zip` INT(5) UNSIGNED NOT NULL,
  `city` VARCHAR(500) NOT NULL,
  `email` VARCHAR(500) NULL,
  `cellphone` VARCHAR(12) NULL,
  `password` VARCHAR(500) NOT NULL,
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`entity_id`),
  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ascmanagtool`.`Stats`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ascmanagtool`.`Stats` ;

CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Stats` (
  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `licence_id` INT UNSIGNED NOT NULL,
  `goals` INT(4) UNSIGNED NOT NULL,
  `stops` INT(4) UNSIGNED NOT NULL,
  `decisive_assists` INT(4) UNSIGNED NOT NULL,
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`),
  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
  INDEX `fk_licenced_id_idx` (`licence_id` ASC),
  CONSTRAINT `fk_licenced_id`
    FOREIGN KEY (`licence_id`)
    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ascmanagtool`.`Matchs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ascmanagtool`.`Matchs` ;

CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Matchs` (
  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `locals` INT UNSIGNED NOT NULL,
  `visitors` INT UNSIGNED NOT NULL,
  `score` VARCHAR(5) NULL,
  `date` DATETIME NOT NULL,
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`),
  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
  INDEX `fk_locals_id_idx` (`locals` ASC),
  INDEX `fl_visitors_id_idx` (`visitors` ASC),
  CONSTRAINT `fk_locals_id`
    FOREIGN KEY (`locals`)
    REFERENCES `ascmanagtool`.`Club` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fl_visitors_id`
    FOREIGN KEY (`visitors`)
    REFERENCES `ascmanagtool`.`Club` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ascmanagtool`.`Players`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ascmanagtool`.`Players` ;

CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Players` (
  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `match_id` INT UNSIGNED NOT NULL,
  `player_id` INT UNSIGNED NOT NULL,
  `position` VARCHAR(45) NOT NULL,
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`),
  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
  INDEX `fk_match_id_idx` (`match_id` ASC),
  INDEX `fk_player_id_idx` (`player_id` ASC),
  CONSTRAINT `fk_match_id`
    FOREIGN KEY (`match_id`)
    REFERENCES `ascmanagtool`.`Matchs` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_player_id`
    FOREIGN KEY (`player_id`)
    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ascmanagtool`.`Lasts`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ascmanagtool`.`Lasts` ;

CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Lasts` (
  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `licenced_id` INT UNSIGNED NOT NULL,
  `pword` VARCHAR(500) NULL,
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`),
  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
  INDEX `fk_l_id_idx` (`licenced_id` ASC),
  CONSTRAINT `fk_l_id`
    FOREIGN KEY (`licenced_id`)
    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ascmanagtool`.`Favs`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ascmanagtool`.`Favs` ;

CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Favs` (
  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `licence_id` INT UNSIGNED NOT NULL,
  `pages` VARCHAR(500) NULL,
  PRIMARY KEY (`entity_id`),
  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
  INDEX `fkey_licenced_id_idx` (`licence_id` ASC),
  CONSTRAINT `fkey_licenced_id`
    FOREIGN KEY (`licence_id`)
    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ascmanagtool`.`Trainings`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ascmanagtool`.`Trainings` ;

CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Trainings` (
  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` INT UNSIGNED NOT NULL,
  `date` DATETIME NOT NULL,
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`),
  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC),
  INDEX `fkey_group_id_idx` (`group_id` ASC),
  CONSTRAINT `fkey_group_id`
    FOREIGN KEY (`group_id`)
    REFERENCES `ascmanagtool`.`Group` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ascmanagtool`.`Categories`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ascmanagtool`.`Categories` ;

CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Categories` (
  `entity_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `label` VARCHAR(70) NOT NULL,
  `created_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`),
  UNIQUE INDEX `entity_id_UNIQUE` (`entity_id` ASC))
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `mydb`.`Trainings_has_Licenced`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Trainings_has_Licenced` (
  `Trainings_entity_id` INT UNSIGNED NOT NULL,
  `Licenced_entity_id` INT UNSIGNED  NOT NULL,
  PRIMARY KEY (`Trainings_entity_id`, `Licenced_entity_id`),
  INDEX `fk_Trainings_has_Licenced_Licenced1_idx` (`Licenced_entity_id` ASC),
  INDEX `fk_Trainings_has_Licenced_Trainings1_idx` (`Trainings_entity_id` ASC),
  CONSTRAINT `fk_Trainings_has_Licenced_Trainings1`
    FOREIGN KEY (`Trainings_entity_id`)
    REFERENCES `ascmanagtool`.`Trainings` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Trainings_has_Licenced_Licenced1`
    FOREIGN KEY (`Licenced_entity_id`)
    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Group_has_Licenced`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Group_has_Licenced` (
  `Group_entity_id` INT UNSIGNED NOT NULL,
  `Licenced_entity_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`Group_entity_id`, `Licenced_entity_id`),
  INDEX `fk_Group_has_Licenced_Licenced1_idx` (`Licenced_entity_id` ASC),
  INDEX `fk_Group_has_Licenced_Group1_idx` (`Group_entity_id` ASC),
  CONSTRAINT `fk_Group_has_Licenced_Group1`
    FOREIGN KEY (`Group_entity_id`)
    REFERENCES `ascmanagtool`.`Group` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Group_has_Licenced_Licenced1`
    FOREIGN KEY (`Licenced_entity_id`)
    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`Categories_has_Licenced`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ascmanagtool`.`Categories_has_Licenced` (
  `Categories_entity_id` INT UNSIGNED NOT NULL,
  `Licenced_entity_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`Categories_entity_id`, `Licenced_entity_id`),
  INDEX `fk_Categories_has_Licenced_Licenced1_idx` (`Licenced_entity_id` ASC),
  INDEX `fk_Categories_has_Licenced_Categories1_idx` (`Categories_entity_id` ASC),
  CONSTRAINT `fk_Categories_has_Licenced_Categories1`
    FOREIGN KEY (`Categories_entity_id`)
    REFERENCES `ascmanagtool`.`Categories` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Categories_has_Licenced_Licenced1`
    FOREIGN KEY (`Licenced_entity_id`)
    REFERENCES `ascmanagtool`.`Licenced` (`entity_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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