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';

CREATE SCHEMA IF NOT EXISTS `cdc` DEFAULT CHARACTER SET latin1 ;
USE `cdc` ;

-- -----------------------------------------------------
-- Table `cdc`.`config`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`config` (
  `rules` TEXT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' NOT NULL )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`profile`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`profile` (
  `id_profile` TINYINT(1) UNSIGNED NOT NULL ,
  `grade` VARCHAR(35) NULL DEFAULT NULL ,
  `have_school_report` TINYINT(1)  NOT NULL ,
  PRIMARY KEY (`id_profile`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`user`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`user` (
  `id_user` INT(11) NOT NULL AUTO_INCREMENT ,
  `login` VARCHAR(50) NOT NULL ,
  `password` VARCHAR(40) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
  `lastname` VARCHAR(30) NOT NULL ,
  `firstname` VARCHAR(30) NOT NULL ,
  `email` VARCHAR(40) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL ,
  `birth_date` DATE NOT NULL DEFAULT '9999-01-01' ,
  `id_profile` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' ,
  `rules` TINYINT(1)  NOT NULL DEFAULT '0' ,
  `enabled` TINYINT(1)  NOT NULL DEFAULT '0' ,
  `admin` TINYINT(1)  NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`id_user`) ,
  INDEX `fk_user_profile` (`id_profile` ASC) ,
  CONSTRAINT `fk_user_profile`
    FOREIGN KEY (`id_profile` )
    REFERENCES `cdc`.`profile` (`id_profile` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`connection`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`connection` (
  `id_connection` INT(11) NOT NULL AUTO_INCREMENT ,
  `date_connection` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  `ip` VARCHAR(15) NULL DEFAULT NULL ,
  `id_user` INT(11) NOT NULL ,
  PRIMARY KEY (`id_connection`) ,
  INDEX `fk_connection_user` (`id_user` ASC) ,
  CONSTRAINT `fk_connection_user`
    FOREIGN KEY (`id_user` )
    REFERENCES `cdc`.`user` (`id_user` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`discipline`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`discipline` (
  `id_discipline` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci' NOT NULL ,
  PRIMARY KEY (`id_discipline`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`level`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`level` (
  `id_level` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`id_level`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`member_of_level`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`member_of_level` (
  `id_level` INT(11) NOT NULL ,
  `id_user` INT(11) NOT NULL ,
  `manager` TINYINT(1)  NOT NULL DEFAULT 0 ,
  INDEX `fk_member_of_level_level` (`id_level` ASC) ,
  INDEX `fk_member_of_level_user` (`id_user` ASC) ,
  CONSTRAINT `fk_member_of_level_level`
    FOREIGN KEY (`id_level` )
    REFERENCES `cdc`.`level` (`id_level` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_member_of_level_user`
    FOREIGN KEY (`id_user` )
    REFERENCES `cdc`.`user` (`id_user` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`type`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`type` (
  `id_type` INT(11) NOT NULL AUTO_INCREMENT ,
  `wording` VARCHAR(50) NULL DEFAULT NULL ,
  PRIMARY KEY (`id_type`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`message`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`message` (
  `id_message` INT(11) NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(50) NULL DEFAULT NULL ,
  `content` VARCHAR(2500) NULL DEFAULT NULL ,
  `transmitter` INT(11) NOT NULL ,
  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  `id_type` INT(11) NOT NULL ,
  PRIMARY KEY (`id_message`) ,
  INDEX `fk_message_type` (`id_type` ASC) ,
  INDEX `fk_message_transmitter` (`transmitter` ASC) ,
  CONSTRAINT `fk_message_type`
    FOREIGN KEY (`id_type` )
    REFERENCES `cdc`.`type` (`id_type` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_message_transmitter`
    FOREIGN KEY (`transmitter` )
    REFERENCES `cdc`.`user` (`id_user` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`responsible`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`responsible` (
  `id_student` INT(11) NOT NULL ,
  `id_responsible` INT(11) NOT NULL ,
  INDEX `fk_responsible` (`id_responsible` ASC) ,
  INDEX `fk_student` (`id_student` ASC) ,
  CONSTRAINT `fk_responsible`
    FOREIGN KEY (`id_responsible` )
    REFERENCES `cdc`.`user` (`id_user` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_student`
    FOREIGN KEY (`id_student` )
    REFERENCES `cdc`.`user` (`id_user` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`school_report`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`school_report` (
  `id_report` INT(11) NOT NULL AUTO_INCREMENT ,
  `status` TINYINT(1)  NULL DEFAULT '0' ,
  `id_user` INT(11) NOT NULL ,
  PRIMARY KEY (`id_report`) ,
  INDEX `fk_user` (`id_user` ASC) ,
  CONSTRAINT `fk_user`
    FOREIGN KEY (`id_user` )
    REFERENCES `cdc`.`user` (`id_user` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`teacher`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`teacher` (
  `id_user` INT(11) NOT NULL ,
  `id_discipline` INT(11) NOT NULL ,
  INDEX `fk_teacher_user` (`id_user` ASC) ,
  INDEX `fk_teacher_discipline` (`id_discipline` ASC) ,
  CONSTRAINT `fk_teacher_user`
    FOREIGN KEY (`id_user` )
    REFERENCES `cdc`.`user` (`id_user` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_teacher_discipline`
    FOREIGN KEY (`id_discipline` )
    REFERENCES `cdc`.`discipline` (`id_discipline` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `cdc`.`belong`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `cdc`.`belong` (
  `id_message` INT(11) NOT NULL ,
  `id_report` INT(11) NOT NULL ,
  `id_recipient` INT(11) NOT NULL ,
  `validation` TINYINT(1)  NOT NULL ,
  `validation_date` TIMESTAMP NOT NULL ,
  INDEX `fk_belong_school_report` (`id_report` ASC) ,
  INDEX `fk_belong_message` (`id_message` ASC) ,
  INDEX `fk_belong_id_user` (`id_recipient` ASC) ,
  CONSTRAINT `fk_belong_message`
    FOREIGN KEY (`id_message` )
    REFERENCES `cdc`.`message` (`id_message` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_belong_school_report`
    FOREIGN KEY (`id_report` )
    REFERENCES `cdc`.`school_report` (`id_report` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_belong_id_user`
    FOREIGN KEY (`id_recipient` )
    REFERENCES `cdc`.`user` (`id_user` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;



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