CREATE TABLE `item` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` LONGTEXT DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Reselling item price, not including production item price, service item price, and subscription item price
CREATE TABLE `item_price` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`item` INT(11) NOT NULL,
	`type` VARCHAR(4) NOT NULL, -- buy, sell
	`currency` VARCHAR(3) NOT NULL,
	`value` INT(11) NOT NULL,
	`periods` DATETIME NOT NULL,
	PRIMARY KEY (`id`),
	KEY `item` (`item`),
	CONSTRAINT `item_price_fk_item`
		FOREIGN KEY (`item`)
		REFERENCES `item`(`id`)
		ON UPDATE CASCADE
		ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Not including assets inventory, get from buying/production, and shipping/expedition
CREATE TABLE `inventory` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`item` INT(11) NOT NULL,
	`flow` VARCHAR(20) NOT NULL, -- in, out, buy, sell, buy return, sell return
	`when` DATETIME NOT NULL,
	`qty` INT(11) NOT NULL,
	`note` LONGTEXT DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `item` (`item`),
	CONSTRAINT `inventory_fk_item`
		FOREIGN KEY (`item`)
		REFERENCES `item`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- get from: buying/production