CREATE SCHEMA IF NOT EXISTS application;
USE application;

DROP TABLE IF EXISTS users ;
CREATE TABLE IF NOT EXISTS users (
	`id` VARCHAR(40) NOT NULL,
	`role` VARCHAR(10) NOT NULL,
	`username` VARCHAR(20) NOT NULL,
	`password` VARCHAR(256) NOT NULL,
	`email` VARCHAR(100) NOT NULL,
	`phone` VARCHAR(15) NULL DEFAULT NULL,
	`email_2fa` TINYINT(1) NULL DEFAULT '0',
	`phone_2fa` TINYINT(1) NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	UNIQUE INDEX `id_UNIQUE` (`id` ASC),
	UNIQUE INDEX `username_UNIQUE` (`username` ASC)
);
  
DROP TABLE IF EXISTS locations;
CREATE TABLE IF NOT EXISTS locations (
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`city` VARCHAR(45) NOT NULL,
	`state` VARCHAR(45) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `id_UNIQUE` (`id` ASC)
);

DROP TABLE IF EXISTS orders;
CREATE TABLE IF NOT EXISTS orders (
	`id` VARCHAR(40) NOT NULL,
	`user_id` VARCHAR(40) NOT NULL,
	`location_id` INT UNSIGNED NOT NULL,
	`order_total` DECIMAL(9,2) NOT NULL DEFAULT 0.00,
    
	PRIMARY KEY (`id`),
	UNIQUE INDEX `id_UNIQUE` (`id` ASC),
    
    CONSTRAINT `fk_order_user_id` FOREIGN KEY (`user_id`)
		REFERENCES `application`.`users` (`id`),
	CONSTRAINT `fk_order_loc_id` FOREIGN KEY (`location_id`)
		REFERENCES `application`.`locations` (`id`)
);

DROP TABLE IF EXISTS items;
CREATE TABLE IF NOT EXISTS items (
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`item_description` VARCHAR(45) NOT NULL,
	`item_price` DECIMAL(9,2) NOT NULL,
    
	PRIMARY KEY (`id`),
	UNIQUE INDEX `id_UNIQUE` (`id` ASC)
);

DROP TABLE IF EXISTS order_line_items;
CREATE TABLE IF NOT EXISTS order_line_items (
	`order_id` VARCHAR(40) NOT NULL,
	`item_id` INT UNSIGNED NOT NULL,
	`quantity` INT UNSIGNED NULL,
	`line_item_amount` DECIMAL(9,2) NULL,
	
    CONSTRAINT `fk_line_order_id` FOREIGN KEY (`order_id`)
		REFERENCES `application`.`orders` (`id`),
	CONSTRAINT `fk_line_item_id` FOREIGN KEY (`item_id`)
		REFERENCES `application`.`items` (`id`)
);


DROP TABLE IF EXISTS inventory;
CREATE TABLE IF NOT EXISTS inventory (
	`location_id` INT UNSIGNED NOT NULL,
	`item_id` INT UNSIGNED NOT NULL,
	`amount` INT UNSIGNED NOT NULL,
    
	CONSTRAINT `fk_inv_loc_id` FOREIGN KEY (`location_id`)
		REFERENCES `application`.`locations` (`id`),
	CONSTRAINT `fk_inv_item_id` FOREIGN KEY (`item_id`)
		REFERENCES `application`.`items` (`id`)
);