CREATE  TABLE IF NOT EXISTS `users` (
  `login` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `surname` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(45) NOT NULL ,
  `birthdate` DATE NOT NULL ,
  `logdate` DATE NULL ,
  `regdate` DATE NULL ,
  PRIMARY KEY (`login`) )
ENGINE = InnoDB;



CREATE  TABLE IF NOT EXISTS `groups` (
  `groupID` INT NOT NULL AUTO_INCREMENT ,
  `groupname` VARCHAR(45) NOT NULL ,
  `groupowner` VARCHAR(45) NOT NULL ,
  `autooptymalization` TINYINT(1)  NOT NULL ,
  PRIMARY KEY (`groupID`) ,
  INDEX `fk_groups_users` (`groupowner` ASC) ,
  CONSTRAINT `fk_groups_users`
    FOREIGN KEY (`groupowner` )
    REFERENCES `users` (`login` ))
ENGINE = InnoDB;




CREATE  TABLE IF NOT EXISTS `groupmembers` (
  `login` VARCHAR(45) NOT NULL ,
  `groupID` INT NOT NULL ,
  PRIMARY KEY (`login`, `groupID`) ,
  INDEX `fk_groupmembers_users1` (`login` ASC) ,
  INDEX `fk_groupmembers_groups1` (`groupID` ASC) ,
  CONSTRAINT `fk_groupmembers_users1`
    FOREIGN KEY (`login` )
    REFERENCES `users` (`login` ),
  CONSTRAINT `fk_groupmembers_groups1`
    FOREIGN KEY (`groupID` )
    REFERENCES `groups` (`groupID` ))
ENGINE = InnoDB;



CREATE  TABLE IF NOT EXISTS `commitments` (
  `commitmentID` INT NOT NULL AUTO_INCREMENT ,
  `commitmentname` VARCHAR(100) NOT NULL ,
  `obligor` VARCHAR(45) NOT NULL ,
  `obligee` VARCHAR(45) NOT NULL ,
  `groupID` INT NOT NULL ,
  `commitmentowner` VARCHAR(45) NOT NULL ,
  `sum` FLOAT NOT NULL ,
  `comment` VARCHAR(200) NULL ,
  `commitmentdate` DATE NOT NULL ,
  `status` INT NOT NULL ,
  PRIMARY KEY (`commitmentID`) ,
  INDEX `fk_commitments_groups1` (`groupID` ASC) ,
  INDEX `fk_commitments_users1` (`obligor` ASC) ,
  INDEX `fk_commitments_users2` (`obligee` ASC) ,
  INDEX `fk_commitments_users` (`commitmentowner` ASC) ,
  CONSTRAINT `fk_commitments_groups1`
    FOREIGN KEY (`groupID` )
    REFERENCES `groups` (`groupID` ),
   CONSTRAINT `fk_commitments_users1`
    FOREIGN KEY (`obligor` )
    REFERENCES `users` (`login` ),
CONSTRAINT `fk_commitments_users2`
    FOREIGN KEY (`obligee` )
    REFERENCES `users` (`login`),
  CONSTRAINT `fk_commitments_users`
    FOREIGN KEY (`commitmentownerID` )
    REFERENCES `users` (`login` ))
ENGINE = InnoDB;




CREATE  TABLE IF NOT EXISTS `groupjoin` (
  `groupjoinID` INT NOT NULL AUTO_INCREMENT ,
  `groupjoinowner` VARCHAR(45) NOT NULL ,
  `groupID` INT NOT NULL ,
  `login` varchar(45) NOT NULL ,
  `invitation` TINYINT(1)  NOT NULL ,
  PRIMARY KEY (`groupjoinID`) ,
  INDEX `fk_groupjoin_users1` (`login` ASC) ,
  INDEX `fk_groupjoin_groups1` (`groupID` ASC) ,
  CONSTRAINT `fk_groupjoin_users1`
    FOREIGN KEY (`login` )
    REFERENCES `users` (`login` ),
  CONSTRAINT `fk_groupjoin_groups1`
    FOREIGN KEY (`groupID` )
    REFERENCES `groups` (`groupID` ))
ENGINE = InnoDB;




CREATE TABLE IF NOT EXISTS `sessions` (
	`sessionID` BIGINT NOT NULL,
	`login` VARCHAR(45) NOT NULL,
	`userIP` VARCHAR(20) NOT NULL,
	`rememberme` TINYINT(1) NOT NULL,
	CONSTRAINT `fk_sessions_users`
		FOREIGN KEY(`login`)
		REFERENCES `users` (`login`))
ENGINE = InnoDB;