CREATE TABLE IF NOT EXISTS `warehouse` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(150) NOT NULL DEFAULT '',
    `comment` TEXT,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `stock_item_attribute_kit` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(150) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `stock_item_attribute` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(150) NOT NULL DEFAULT '',
    `type` tinyint,
    `required` tinyint NOT NULL DEFAULT 0,
    `unique_attr` tinyint NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `stock_item_attribute_link_kit` (
    `attribute_kit_id` int(10) unsigned NOT NULL,
    `attribute_id` int(10) unsigned NOT NULL,
    FOREIGN KEY (`attribute_id`) REFERENCES `stock_item_attribute` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`attribute_kit_id`) REFERENCES `stock_item_attribute_kit` (`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `stock_item_type` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(150) NOT NULL DEFAULT '',
    `stock_item_attribute_kit` int(10),
    `parent_type_id` int(10),
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `stock_item` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(150) NOT NULL DEFAULT '',
    `warehouse_id` int(10) NOT NULL,
    `stock_item_type_id` int(10) unsigned NOT NULL,
    `contract_id` int(10) unsigned NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`stock_item_type_id`) REFERENCES `stock_item_type` (`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `stock_item_attribute_value` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `stock_item_id` int(10) unsigned,
    `value` TEXT,
    `attribute_id` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`stock_item_id`) REFERENCES `stock_item` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`attribute_id`) REFERENCES `stock_item_attribute` (`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `contract_stock_item` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `stock_item_id` int(10) unsigned,
    `contract_id` int(10) unsigned,
    `date_from` datetime,
    `date_to` datetime,
    `user_id` int(10) unsigned,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `warehouse_operation` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `stock_item_id` int(10) unsigned,
    `stock_item_type_title` varchar(256) NOT NULL DEFAULT '',
    `warehouse_from_id` int(10) unsigned,
    `warehouse_to_id` int(10) unsigned,
    `contract_id` int(10) unsigned,
    `operation_type` tinyint,
    `operation_time` datetime,
    `user_id` int(10) unsigned,
    PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `foc_node` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(150) NOT NULL DEFAULT '',
    `type` tinyint NOT NULL DEFAULT 0,
    `status` tinyint NOT NULL DEFAULT 0,
    `latitude` double NOT NULL DEFAULT 0,
    `longitude` double NOT NULL DEFAULT 0,
    `parent_node_id` int(10) unsigned NOT NULL DEFAULT 0,
    `stock_item_id` int(10) unsigned NOT NULL DEFAULT 0,
    `address` varchar(255) NOT NULL DEFAULT '',
    `comment` TEXT,
    PRIMARY KEY (`id`),
    KEY `parent_node_id` (`parent_node_id`),
    KEY `stock_item_id` (`stock_item_id`)
);

CREATE TABLE IF NOT EXISTS `foc_cable` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(150) NOT NULL DEFAULT '',
    `type` tinyint NOT NULL DEFAULT 0,
    `status` tinyint NOT NULL DEFAULT 0,
    `node_from_id` int(10) unsigned NOT NULL DEFAULT 0,
    `node_to_id` int(10) unsigned NOT NULL DEFAULT 0,
    `length` double NOT NULL DEFAULT 0,
    `geometry` TEXT,
    `stock_item_id` int(10) unsigned NOT NULL DEFAULT 0,
    `comment` TEXT,
    PRIMARY KEY (`id`),
    KEY `node_from_id` (`node_from_id`),
    KEY `node_to_id` (`node_to_id`),
    KEY `stock_item_id` (`stock_item_id`)
);

CREATE TABLE IF NOT EXISTS `foc_port` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `node_id` int(10) unsigned NOT NULL DEFAULT 0,
    `title` varchar(150) NOT NULL DEFAULT '',
    `type` tinyint NOT NULL DEFAULT 0,
    `status` tinyint NOT NULL DEFAULT 0,
    `contract_id` int(10) unsigned NOT NULL DEFAULT 0,
    `stock_item_id` int(10) unsigned NOT NULL DEFAULT 0,
    `comment` TEXT,
    PRIMARY KEY (`id`),
    KEY `node_id` (`node_id`),
    KEY `contract_id` (`contract_id`),
    KEY `stock_item_id` (`stock_item_id`)
);