SET NAMES utf8;

SET foreign_key_checks = 0;

SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

DROP TABLE IF EXISTS `eshop_delivery_methods`;

CREATE TABLE `eshop_delivery_methods`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `price` text NOT NULL,
    `show` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS `eshop_delivery_methods_contents`;

CREATE TABLE `eshop_delivery_methods_contents`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `delivery_method` int(11) NOT NULL,
    `language` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `delivery_method_language`(`delivery_method`, `language`),
    UNIQUE KEY `language_name`(`language`, `name`),
    CONSTRAINT `eshop_delivery_methods_contents_ibfk_3` FOREIGN KEY (`delivery_method`) REFERENCES `eshop_delivery_methods`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `eshop_delivery_methods_contents_ibfk_4` FOREIGN KEY (`language`) REFERENCES `languages`(`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS `eshop_delivery_methods_payment_methods`;

CREATE TABLE `eshop_delivery_methods_payment_methods`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `delivery_method` int(11) NOT NULL,
    `payment_method` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `delivery_method_payment_method`(`delivery_method`, `payment_method`),
    KEY `delivery_method`(`delivery_method`),
    KEY `payment_method`(`payment_method`),
    CONSTRAINT `eshop_delivery_methods_payment_methods_ibfk_3` FOREIGN KEY (`delivery_method`) REFERENCES `eshop_delivery_methods`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `eshop_delivery_methods_payment_methods_ibfk_4` FOREIGN KEY (`payment_method`) REFERENCES `eshop_payment_methods`(`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS `eshop_orders`;

CREATE TABLE `eshop_orders`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `customer_id` int(11) DEFAULT NULL,
    `customer_name` varchar(255) NOT NULL,
    `customer_email` varchar(255) NOT NULL,
    `customer_data` mediumblob NOT NULL,
    `customer_major` tinyint(1) NOT NULL DEFAULT '0',
    `cart` mediumblob NOT NULL,
    `received` datetime NOT NULL,
    `processing` datetime DEFAULT NULL,
    `sent` datetime DEFAULT NULL,
    `cancel` datetime DEFAULT NULL,
    `note` text,
    `note_internal` text,
    `price` varchar(20) NOT NULL,
    `discount` varchar(10) NOT NULL DEFAULT '0',
    `delivery_method` varchar(255) NOT NULL,
    `delivery_price` varchar(10) NOT NULL DEFAULT '0',
    `payment_method` varchar(255) NOT NULL,
    `state` enum('received', 'processing', 'sent', 'cancel') NOT NULL DEFAULT 'received',
    `language` int(11) NOT NULL DEFAULT '1',
    `date_of_payment` datetime DEFAULT NULL,
    `invoice_number` varchar(40) DEFAULT NULL,
    `invoice_date` date DEFAULT NULL,
    `invoice_date_delivery` date DEFAULT NULL,
    `invoice_issued_by` varchar(255) DEFAULT NULL,
    `tax_compliance` date DEFAULT NULL,
    `maturity` tinyint(4) DEFAULT NULL,
    `vat` tinyint(1) NOT NULL DEFAULT '0',
    `vat_payer` tinyint(1) NOT NULL,
    `trader` blob NOT NULL,
    PRIMARY KEY (`id`),
    KEY `language`(`language`),
    KEY `customer_id`(`customer_id`),
    CONSTRAINT `eshop_orders_ibfk_1` FOREIGN KEY (`language`) REFERENCES `languages`(`id`),
    CONSTRAINT `eshop_orders_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `users`(`id`) ON DELETE SET NULL) ENGINE = InnoDB DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS `eshop_payment_methods`;

CREATE TABLE `eshop_payment_methods`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `show` tinyint(4) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE = latin1_bin;

DROP TABLE IF EXISTS `eshop_payment_methods_contents`;

CREATE TABLE `eshop_payment_methods_contents`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `payment_method` int(11) NOT NULL,
    `language` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `payment_method_language`(`payment_method`, `language`),
    UNIQUE KEY `language_name`(`language`, `name`),
    CONSTRAINT `eshop_payment_methods_contents_ibfk_3` FOREIGN KEY (`payment_method`) REFERENCES `eshop_payment_methods`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `eshop_payment_methods_contents_ibfk_5` FOREIGN KEY (`language`) REFERENCES `languages`(`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS `eshop_products`;

CREATE TABLE `eshop_products`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product_code` varchar(255) character
    SET utf8 NOT NULL,
    `show` tinyint(1) NOT NULL,
    `added` date NOT NULL,
    `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `mainphoto` int(11) DEFAULT NULL,
    `price` varchar(20) COLLATE latin1_bin NOT NULL,
    `discount` varchar(10) COLLATE latin1_bin NOT NULL DEFAULT '0',
    `mass_prices` text COLLATE latin1_bin,
    `wholesale_price` varchar(20) COLLATE latin1_bin DEFAULT NULL,
    `wholesale_discount` varchar(10) COLLATE latin1_bin NOT NULL DEFAULT '0',
    `wholesale_mass_prices` text COLLATE latin1_bin,
    `width` DECIMAL(9, 3) DEFAULT NULL,
    `height` DECIMAL(9, 3) DEFAULT NULL,
    `depth` DECIMAL(9, 3) DEFAULT NULL,
    `weight` mediumint(9) DEFAULT NULL,
    `available` int(11) NOT NULL DEFAULT '0',
    `vat` tinyint(7) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `mainphoto`(`mainphoto`),
    CONSTRAINT `eshop_products_ibfk_2` FOREIGN KEY (`mainphoto`) REFERENCES `photos`(`id`) ON DELETE SET NULL) ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE = latin1_bin;

DROP TABLE IF EXISTS `eshop_products_contents`;

CREATE TABLE `eshop_products_contents`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product` int(11) NOT NULL,
    `language` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    `description` varchar(255) NOT NULL,
    `long_description` text NOT NULL,
    `availability` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `language_product`(`language`, `product`),
    KEY `product`(`product`),
    KEY `language`(`language`),
    CONSTRAINT `eshop_products_contents_ibfk_2` FOREIGN KEY (`language`) REFERENCES `languages`(`id`) ON DELETE CASCADE,
    CONSTRAINT `eshop_products_contents_ibfk_3` FOREIGN KEY (`product`) REFERENCES `eshop_products`(`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS `eshop_products_photos`;

CREATE TABLE `eshop_products_photos`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product` int(11) NOT NULL,
    `photo` int(11) NOT NULL,
    `order` mediumint(9) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `product_photo`(`product`, `photo`),
    KEY `photo`(`photo`),
    CONSTRAINT `eshop_products_photos_ibfk_2` FOREIGN KEY (`photo`) REFERENCES `photos`(`id`) ON DELETE CASCADE,
    CONSTRAINT `eshop_products_photos_ibfk_3` FOREIGN KEY (`product`) REFERENCES `eshop_products`(`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE = latin1_bin;

DROP TABLE IF EXISTS `eshop_products_tags`;

CREATE TABLE `eshop_products_tags`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `product` int(11) NOT NULL,
    `tag` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `product_tag`(`product`, `tag`),
    KEY `tag`(`tag`),
    CONSTRAINT `eshop_products_tags_ibfk_2` FOREIGN KEY (`tag`) REFERENCES `eshop_tags`(`id`) ON DELETE CASCADE,
    CONSTRAINT `eshop_products_tags_ibfk_3` FOREIGN KEY (`product`) REFERENCES `eshop_products`(`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE = latin1_bin;

DROP TABLE IF EXISTS `eshop_tags`;

CREATE TABLE `eshop_tags`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `parent` int(11) DEFAULT NULL,
    `order` int(11) NOT NULL,
    `lft` int(11) DEFAULT NULL,
    `rgt` int(11) DEFAULT NULL,
    `level` int(11) DEFAULT NULL,
    `product_code` varchar(255) COLLATE latin1_bin NOT NULL,
    `show` tinyint(1) NOT NULL DEFAULT '1',
    `mainphoto` int(11) DEFAULT NULL,
    `extra_fee` char(40) COLLATE latin1_bin NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    KEY `parent`(`parent`),
    KEY `mainphoto`(`mainphoto`),
    CONSTRAINT `eshop_tags_ibfk_4` FOREIGN KEY (`parent`) REFERENCES `eshop_tags`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `eshop_tags_ibfk_5` FOREIGN KEY (`mainphoto`) REFERENCES `photos`(`id`) ON DELETE SET NULL) ENGINE = InnoDB DEFAULT CHARSET = latin1 COLLATE = latin1_bin;

DROP TABLE IF EXISTS `eshop_tags_contents`;

CREATE TABLE `eshop_tags_contents`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `tag` int(11) NOT NULL,
    `language` int(11) NOT NULL,
    `name` varchar(255) NOT NULL,
    `description` text NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `language_name`(`language`, `name`),
    UNIQUE KEY `language_tag`(`language`, `tag`),
    KEY `tag`(`tag`),
    CONSTRAINT `eshop_tags_contents_ibfk_2` FOREIGN KEY (`language`) REFERENCES `languages`(`id`) ON DELETE CASCADE,
    CONSTRAINT `eshop_tags_contents_ibfk_3` FOREIGN KEY (`tag`) REFERENCES `eshop_tags`(`id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8;

