CREATE DATABASE `munchies_dw`;

USE `munchies_dw`;

CREATE TABLE `client` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `location` VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
)

CREATE TABLE `damaged_goods` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `damage_type` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
)

CREATE TABLE `employee` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `employee_type` VARCHAR(45) NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `surname` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
)

CREATE TABLE `fact_expenses` (
  `date_timestamp` datetime NOT NULL,
  `employee_id` INT(11) DEFAULT NULL,
  `van_id` INT(11) DEFAULT NULL,
  `fuel_allowance` DECIMAL(7,2) DEFAULT NULL,
  `van_maintenance` DECIMAL(7,2) DEFAULT NULL,
  `company_car_lease` DECIMAL(7,2) DEFAULT NULL,
  `total_depreciation_to_date` DECIMAL(7,2) DEFAULT NULL,
  `total_sales` DECIMAL(10,2) DEFAULT NULL,
  `telephone_expenses` DECIMAL(7,2) DEFAULT NULL,
  `utility_expenses` DECIMAL(7,2) DEFAULT NULL,
  `marketing_expense` DECIMAL(7,2) DEFAULT NULL,
  `employee_commission` DECIMAL(7,2) DEFAULT NULL,
  `employee_wage` DECIMAL(7,2) DEFAULT NULL,
  PRIMARY KEY (`date_timestamp`),
  KEY `fact_expenses_date_timestamp` (`date_timestamp`),
  KEY `fact_expenses_employee_id` (`employee_id`),
  KEY `fact_expenses_van_id` (`van_id`),
  CONSTRAINT `fact_expenses_date_timestamp` FOREIGN KEY (`date_timestamp`) REFERENCES `transaction_date` (`date_timestamp`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fact_expenses_employee_id` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fact_expenses_van_id` FOREIGN KEY (`van_id`) REFERENCES `van` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE `fact_inventory` (
  `date_timestamp` datetime NOT NULL,
  `product_id` INT(11) DEFAULT NULL,
  `damaged_goods_id` INT(11) DEFAULT NULL,
  `client_id` INT(11) DEFAULT NULL,
  `damaged_quantity` INT(11) DEFAULT NULL,
  `standard_purchase_price` DECIMAL(6,2) DEFAULT NULL,
  `average_purchase_price` DECIMAL(6,2) DEFAULT NULL,
  PRIMARY KEY (`date_timestamp`),
  KEY `fact_inventory_date_timestamp` (`date_timestamp`),
  KEY `fact_inventory_product_id` (`product_id`),
  KEY `fact_inventory_damaged_goods_id` (`damaged_goods_id`),
  KEY `fact_inventory_client_id` (`client_id`),
  CONSTRAINT `fact_inventory_date_timestamp` FOREIGN KEY (`date_timestamp`) REFERENCES `transaction_date` (`date_timestamp`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fact_inventory_product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fact_inventory_damaged_goods_id` FOREIGN KEY (`damaged_goods_id`) REFERENCES `damaged_goods` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fact_inventory_client_id` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE `fact_promotion` (
  `date_timestamp` datetime NOT NULL,
  `promotion_id` INT(11) DEFAULT NULL,
  `product_id` INT(11) DEFAULT NULL,
  `total_profit` DECIMAL(10,2) DEFAULT NULL,
  `marketing_cost` DECIMAL(7,2) DEFAULT NULL,
  `total_sales` DECIMAL(10,2) DEFAULT NULL,
  PRIMARY KEY (`date_timestamp`),
  KEY `fact_promotion_date_timestamp` (`date_timestamp`),
  KEY `fact_promotion_promotion_id` (`promotion_id`),
  KEY `fact_promotion_product_id` (`product_id`),
  CONSTRAINT `fact_promotion_date_timestamp` FOREIGN KEY (`date_timestamp`) REFERENCES `transaction_date` (`date_timestamp`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fact_promotion_promotion_id` FOREIGN KEY (`promotion_id`) REFERENCES `promotion` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fact_promotion_product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE `fact_sales` (
  `date_timestamp` datetime NOT NULL,
  `product_id` INT(11) DEFAULT NULL,
  `client_id` INT(11) DEFAULT NULL,
  `sales_id` INT(11) DEFAULT NULL,
  `sales_total` DECIMAL(8,2) DEFAULT NULL,
  `quantity` INT(11) DEFAULT NULL,
  `purchases_total` DECIMAL(8,2) DEFAULT NULL,
  `standard_purchase_price` DECIMAL(6,2) DEFAULT NULL,
  `standard_selling_price` DECIMAL(6,2) DEFAULT NULL,
  `average_purchase_price` DECIMAL(6,2) DEFAULT NULL,
  `average_selling_price` DECIMAL(6,2) DEFAULT NULL,
  KEY `fact_sales_date_timestamp` (`date_timestamp`),
  KEY `fact_sales_product_id` (`product_id`),
  KEY `fact_sales_client_id` (`client_id`),
  KEY `fact_sales_sales_id` (`sales_id`),
  CONSTRAINT `fact_sales_date_timestamp` FOREIGN KEY (`date_timestamp`) REFERENCES `transaction_date` (`date_timestamp`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fact_sales_product_id` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fact_sales_client_id` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fact_sales_sales_id` FOREIGN KEY (`sales_id`) REFERENCES `sales` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)

CREATE TABLE `product` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `brand` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
)

CREATE TABLE `promotion` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `promotion_month` INT(11) NOT NULL,
  `promotion_year` INT(11) NOT NULL,
  `clients_offered` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
)

CREATE TABLE `sales` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `employee_id` INT(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
)

CREATE TABLE `transaction_date` (
  `date_timestamp` datetime NOT NULL,
  `date_year` INT(11) NOT NULL,
  `date_quarter` INT(11) NOT NULL,
  `date_month` INT(11) NOT NULL,
  `date_week` INT(11) NOT NULL,
  `date_day` INT(11) NOT NULL,
  PRIMARY KEY (`date_timestamp`),
  UNIQUE KEY `date_timestamp_UNIQUE` (`date_timestamp`)
)

CREATE TABLE `van` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `plate` VARCHAR(6) NOT NULL,
  `description` VARCHAR(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `plate_UNIQUE` (`plate`)
)