SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE `category` (
	`id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
	`title` varchar(100) NOT NULL COMMENT 'Category title',
	`parent` bigint(20) unsigned DEFAULT NULL COMMENT 'Parent category',
	PRIMARY KEY (`id`),
	INDEX `parent` (`parent`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Categories';

CREATE TABLE `contacts` (
	`id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
	`own_contact` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether own contact',
	`gender` varchar(10) NOT NULL DEFAULT 'UNKNOWN' COMMENT 'Gender',
	`first_name` varchar(100) NOT NULL COMMENT 'First name',
	`family_name` varchar(100) NOT NULL COMMENT 'Family name',
	`street` varchar(255) DEFAULT NULL COMMENT 'Street name',
	`house_number` smallint(5) unsigned DEFAULT NULL COMMENT 'House number',
	`city` varchar(100) DEFAULT NULL COMMENT 'City name',
	`zip_code` smallint(5) unsigned DEFAULT NULL COMMENT 'ZIP code',
	`country_code` char(2) DEFAULT NULL COMMENT 'Country code',
	`phone_number` varchar(100) DEFAULT NULL COMMENT 'Phone number',
	`cellphone_number` varchar(100) DEFAULT NULL COMMENT 'Cellphone number',
	`fax_number` varchar(100) DEFAULT NULL COMMENT 'Fax number',
	`email_address` varchar(100) DEFAULT NULL COMMENT 'Email addres',
	`birthday` date DEFAULT NULL COMMENT 'Birth day',
	`comment` tinytext NULL DEFAULT NULL COMMENT 'Comment',
	`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Entry created',
	`updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated entry',
	PRIMARY KEY (`id`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Contacts data';

CREATE TABLE `customer` (
	`id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
	`customer_contact_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference on "contact"',
	`customer_number` varchar(20) NOT NULL COMMENT 'Customer number',
	`customer_password_hash` varchar(255) DEFAULT NULL COMMENT 'Password hash',
	`customer_confirm_key` varchar(50) DEFAULT NULL COMMENT 'Email confirmation key',
	`customer_status` enum('UNCONFIRMED','CONFIRMED','LOCKED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Account status',
	`customer_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Account creation',
	`customer_locked` timestamp NULL DEFAULT NULL COMMENT 'Last locked timestamp',
	PRIMARY KEY (`id`),
	UNIQUE (`customer_confirm_key`),
	UNIQUE (`customer_number`),
	INDEX (`customer_contact_id`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Customer data';

CREATE TABLE `ordered_items` (
	`id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
	`order_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference orders',
	`order_status` enum('UNCONFIRMED','CONFIRMED','DELIVERED','CANCELED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Order status',
	`product_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference products',
	`item_type` VARCHAR(20) NOT NULL COMMENT 'Item type',
	`amount` bigint(20) unsigned NOT NULL COMMENT 'Ordered amount',
	`added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record inserted',
	PRIMARY KEY (`id`),
	INDEX (`order_id`),
	INDEX (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Ordered items';

CREATE TABLE `orders` (
	`id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
	`customer_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference customer',
	`access_key` varchar(100) NULL DEFAULT NULL COMMENT 'Access key to receipt',
	`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Order created',
	PRIMARY KEY (`id`),
	INDEX (`customer_id`),
	UNIQUE (`access_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer orders';

CREATE TABLE `products` (
	`id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
	`category` bigint(20) unsigned DEFAULT NULL COMMENT 'Category id',
	`title` varchar(100) NOT NULL COMMENT 'Title of product',
	`price` decimal(20,2) unsigned NOT NULL COMMENT 'Product price',
	`available` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether product is available',
	PRIMARY KEY (`id`),
	INDEX (`category`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Products';

ALTER TABLE `category`
MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';

ALTER TABLE `contacts`
MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';

ALTER TABLE `customer`
MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';

ALTER TABLE `ordered_items`
MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';

ALTER TABLE `orders`
MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';

ALTER TABLE `products`
MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';

ALTER TABLE `category`
ADD CONSTRAINT `category_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

ALTER TABLE `customer`
ADD CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`customer_contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE;

ALTER TABLE `ordered_items`
ADD CONSTRAINT `ordered_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;

ALTER TABLE `orders`
ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`);

ALTER TABLE `products`
ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION;

SET FOREIGN_KEY_CHECKS=1;
