CRATE SCHEMA IF NOT EXIST iconet;
CREATE  TABLE IF NOT EXISTS `iconet`.`contracts` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `clientid` INT(11) NOT NULL ,
  `mac` VARCHAR(16) NOT NULL DEFAULT '0000000000000000' ,
  `lastpaid` DATE NULL DEFAULT NULL ,
  `daytopay` SMALLINT(6) NOT NULL DEFAULT '1' ,
  `upload` FLOAT NOT NULL DEFAULT '0' ,
  `download` FLOAT NOT NULL DEFAULT '0' ,
  `planid` INT(11) NOT NULL DEFAULT '1' ,
  `ip` VARCHAR(15) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `id_idx` (`clientid` ASC) ,
  INDEX `id_idx1` (`planid` ASC) ,
  CONSTRAINT `ClientId`
    FOREIGN KEY (`clientid` )
    REFERENCES `iconet`.`clients` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `PlanID`
    FOREIGN KEY (`planid` )
    REFERENCES `iconet`.`plans` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
AUTO_INCREMENT = 16
DEFAULT CHARACTER SET = utf8


CREATE  TABLE IF NOT EXISTS `iconet`.`plans` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `tax` FLOAT NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = utf8


CREATE  TABLE IF NOT EXISTS `iconet`.`clients` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `firstname` VARCHAR(45) NOT NULL ,
  `surename` VARCHAR(45) NOT NULL ,
  `lastname` VARCHAR(45) NOT NULL ,
  `docid` VARCHAR(20) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
AUTO_INCREMENT = 9
DEFAULT CHARACTER SET = utf8