CREATE SCHEMA `utbfileshare` DEFAULT CHARACTER SET utf8 COLLATE utf8_czech_ci ;

CREATE TABLE `utbfileshare`.`watcher` (
  `id` INT NOT NULL,
  `last_update` DATETIME(6) NULL,
  `last_pid` VARCHAR(45) NULL,
  `paused` TINYINT NULL,
  PRIMARY KEY (`id`));


  CREATE TABLE `utbfileshare`.`client` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `last_update` TIMESTAMP NULL,
  `address` VARCHAR(45) NOT NULL,
  `port` VARCHAR(10) NULL,
  `title` VARCHAR(45) NULL,
  `active` TINYINT NULL,
  `connected` TINYINT NULL,
  PRIMARY KEY (`id`));
  

CREATE TABLE `utbfileshare`.`torrent` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `id_client` INT NOT NULL,
  `hash` VARCHAR(100) NULL,
  `status` VARCHAR(45) NULL,
  `name` VARCHAR(100) NULL,
  `size` BIGINT NULL,
  `percentProgress` INT NULL,
  `downloadedBytes` BIGINT NULL,
  `uploadedBytes` BIGINT NULL,
  `ratio` DECIMAL(10,3) NULL,
  `uploadSpeed` DECIMAL NULL,
  `downloadSpeed` DECIMAL NULL,
  `eta` VARCHAR(45) NULL,
  `label` VARCHAR(45) NULL,
  `peersConnected` INT NULL,
  `peersInSwarm` INT NULL,
  `seedsConnected` INT NULL,
  `seedsInSwarm` INT NULL,
  `availability` DECIMAL NULL,
  `queueOrder` INT NULL,
  `remainingBytes` BIGINT NULL,
  `torrentcol` VARCHAR(45) NULL,
  `last_update` DATETIME(6) NULL,
  `forDistribution` TINYINT, 
  `started` TINYINT(4) NULL,
  `paused` TINYINT(4) NULL,
  PRIMARY KEY (`id`));
    
ALTER TABLE `utbfileshare`.`torrent` 
  ADD INDEX `fkclient_idx` (`id_client` ASC);
  ALTER TABLE `utbfileshare`.`torrent` 
  ADD CONSTRAINT `fkclient`
  FOREIGN KEY (`id_client`)
  REFERENCES `utbfileshare`.`client` (`id`)
  ON DELETE RESTRICT
  ON UPDATE CASCADE;

  CREATE TABLE `utbfileshare`.`distributedtorrent` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NULL,
  `data` LONGBLOB NULL,
  `active` TINYINT(4) NULL,
 `someExtra` TINYINT(4) NULL,
 `allInstalled` TINYINT(4) NULL,
`somePaused` TINYINT(4) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `utbfileshare`.`user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  `passwordHash` VARCHAR(200) NULL,
  `domain` VARCHAR(45) NULL,
  `type` VARCHAR(45) NOT NULL,
  `active` TINYINT NULL,
  `displayname` VARCHAR(45) NULL,
  `admin` TINYINT NULL,
  PRIMARY KEY (`id`));

CREATE VIEW `torrentView` AS
SELECT torrent.*,
client.title as clientTitle,
client.address as clientAddress,
client.port as clientPort
FROM `torrent`
INNER JOIN client ON (id_client=client.id);

CREATE TABLE `utbfileshare`.`groups` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `groupname` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `groupname_UNIQUE` (`groupname` ASC));

CREATE TABLE `utbfileshare`.`torrentgroup` (
  `idtorrent` INT NOT NULL,
  `idgroup` INT NOT NULL,
  INDEX `fk torrent_idx` (`idtorrent` ASC),
  INDEX `fk group_idx` (`idgroup` ASC),
  CONSTRAINT `fk group`
    FOREIGN KEY (`idgroup`)
    REFERENCES `utbfileshare`.`groups` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk torrent`
    FOREIGN KEY (`idtorrent`)
    REFERENCES `utbfileshare`.`distributedtorrent` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT);


CREATE TABLE `utbfileshare`.`clientgroup` (
  `idclient` INT NOT NULL,
  `idgroup` INT NOT NULL,
  INDEX `fkclient_idx` (`idclient` ASC),
  INDEX `fk group_idx` (`idgroup` ASC),
  PRIMARY KEY (`idclient`, `idgroup`),
  CONSTRAINT `fkclient2`
    FOREIGN KEY (`idclient`)
    REFERENCES `utbfileshare`.`client` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fkgroup2`
    FOREIGN KEY (`idgroup`)
    REFERENCES `utbfileshare`.`groups` (`id`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT);



