CREATE DATABASE IF NOT EXISTS `Repository`;
USE `Repository`;

/******************** Add Table: Repositories ************************/

/* Build Table Structure */
SET character_set_client = utf8;
CREATE TABLE `Repositories`
(
	`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`Name` VARCHAR(255) NOT NULL,
	`InsertBy` VARCHAR(255) NOT NULL,
	`UpdateBy` VARCHAR(255) NOT NULL,
	`InsertTime` DATETIME NOT NULL,
	`UpdateTime` DATETIME NOT NULL,
	`Path` VARCHAR(255) NOT NULL,
	`Url` VARCHAR(255) NOT NULL,
	PRIMARY KEY (`Id`)
) ENGINE=InnoDB COMMENT "Repositorios de pacotes";

/******************** Add Table: Local ************************/

/* Build Table Structure */
SET character_set_client = utf8;
CREATE TABLE `Local`
(
	`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`Name` VARCHAR(255) NOT NULL,
	`InsertBy` VARCHAR(255) NOT NULL,
	`UpdateBy` VARCHAR(255) NOT NULL,
	`InsertTime` DATETIME NOT NULL,
	`UpdateTime` DATETIME NOT NULL,
	PRIMARY KEY (`Id`)
) ENGINE=InnoDB COMMENT "Producao ou Homologacao";

/******************** Add Table: Media ************************/

/* Build Table Structure */
SET character_set_client = utf8;
CREATE TABLE `Media`
(
	`Id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`Name` VARCHAR(255) NOT NULL,
	`InsertBy` VARCHAR(255) NOT NULL,
	`UpdateBy` VARCHAR(255) NOT NULL,
	`InsertTime` DATETIME NOT NULL,
	`UpdateTime` DATETIME NOT NULL,
	PRIMARY KEY (`Id`)
) ENGINE=InnoDB COMMENT "Midia dos sistemas, 1) Servidor, 2) Cliente, 3) Desktop";

/******************** Add Table: Package ************************/

/* Build Table Structure */
SET character_set_client = utf8;
CREATE TABLE Package
(
	`Id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`FileName` VARCHAR(255) NOT NULL,
	`InsertBy` VARCHAR(255) NOT NULL,
	`UpdateBy` VARCHAR(255) NOT NULL,
	`InsertTime` DATETIME NOT NULL,
	`UpdateTime` DATETIME NOT NULL,
	`Name` VARCHAR(255) NOT NULL,
	`Version` VARCHAR(255) NOT NULL,
	`Release` VARCHAR(255) NOT NULL,
	`Arch` VARCHAR(255) NOT NULL,
	`Hash` VARCHAR(255) NOT NULL,
	`RepositoriesId` INT(11) UNSIGNED NOT NULL,
	PRIMARY KEY (`Id`),
	INDEX (`Hash`, `RepositoriesId`),
	FOREIGN KEY (`RepositoriesId`) REFERENCES Repositories (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB Comment "Pacotes dos produtos";

ALTER TABLE `Package` ADD UNIQUE `Name` (`Name`, `Version`, `Release`, `Arch`, `RepositoriesId`);
ALTER TABLE `Package` ADD UNIQUE `Hash` (`Hash`, `RepositoriesId`);
ALTER TABLE `Package` ADD INDEX `HashIndex` (`Hash`);
/******************** Add Table: Product ************************/

/* Build Table Structure */
SET character_set_client = utf8;
CREATE TABLE `Product`
(
	`Id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`ProductCode` TEXT(50) NOT NULL,
	`InsertBy` TEXT(50) NOT NULL,
	`UpdateBy` TEXT(50) NOT NULL,
	`InsertTime` DATETIME NOT NULL,
	`UpdateTime` DATETIME NOT NULL,
	`Major` INTEGER NOT NULL,
	`Minor` INTEGER NOT NULL,
	`SubVersion` INTEGER NOT NULL,
	`Build` INTEGER NOT NULL,
	`LocalId` INT(11) UNSIGNED NOT NULL,
	PRIMARY KEY (`Id`),
	FOREIGN KEY (`LocalId`) REFERENCES Local (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT "Versoes dos produtos";

/******************** Add Table: PackageProduct ************************/

/* Build Table Structure */
SET character_set_client = utf8;
CREATE TABLE `PackageProduct`
(
	`Id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`InsertBy` VARCHAR(50) NOT NULL,
	`UpdateBy` VARCHAR(50) NOT NULL,
	`InsertTime` DATETIME NOT NULL,
	`UpdateTime` DATETIME NOT NULL,
	`PackageId` BIGINT UNSIGNED NOT NULL,
	`ProductId` BIGINT UNSIGNED NOT NULL,
	`MediaId` INT(11) UNSIGNED NOT NULL,
	PRIMARY KEY (`Id`),
	FOREIGN KEY (`PackageId`) REFERENCES Package (`Id`) ON UPDATE CASCADE,
	FOREIGN KEY (`ProductId`) REFERENCES Product (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB Comment "Relacionamento de pacotes com produtos";


/******************** Add Table: ProductMedia ************************/

/* Build Table Structure */
SET character_set_client = utf8;
CREATE TABLE `ProductMedia`
(
	`Id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
	`InsertBy` VARCHAR(255) NOT NULL,
	`UpdateBy` VARCHAR(255) NOT NULL,
	`InsertTime` DATETIME NOT NULL,
	`UpdateTime` DATETIME NOT NULL,
	`ProductId` BIGINT UNSIGNED NOT NULL,
	`MediaId` INT(11) UNSIGNED NOT NULL,
	PRIMARY KEY (`Id`),
	FOREIGN KEY (`ProductId`) REFERENCES Product (`Id`) ON UPDATE CASCADE,
	FOREIGN KEY (`MediaId`) REFERENCES Media (`Id`) ON UPDATE CASCADE
) ENGINE=InnoDB COMMENT "Relacionamento de produto com midia";