
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- amp_seller
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller`;

CREATE TABLE `amp_seller`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `ref` VARCHAR(255) NOT NULL,
    `visible` TINYINT DEFAULT 0 NOT NULL,
    `address1` VARCHAR(255) NOT NULL,
    `address2` VARCHAR(255),
    `address3` VARCHAR(255),
    `zipcode` VARCHAR(10) NOT NULL,
    `city` VARCHAR(255) NOT NULL,
    `country_id` INTEGER NOT NULL,
    `email` VARCHAR(255),
    `phone` VARCHAR(255),
    `fax` VARCHAR(255),
    `url` VARCHAR(255),
    `facebook` VARCHAR(255),
    `twitter` VARCHAR(255),
    `pinterest` VARCHAR(255),
    `google` VARCHAR(255),
    `youtube` VARCHAR(255),
    `instagram` VARCHAR(255),
    `latitude` DECIMAL(16,13) DEFAULT 0,
    `longitude` DECIMAL(16,13) DEFAULT 0,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    `version` INTEGER DEFAULT 0,
    `version_created_at` DATETIME,
    `version_created_by` VARCHAR(100),
    PRIMARY KEY (`id`),
    INDEX `FI_amp_seller_country_id` (`country_id`),
    CONSTRAINT `fk_amp_seller_country_id`
        FOREIGN KEY (`country_id`)
        REFERENCES `country` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_admin
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_admin`;

CREATE TABLE `amp_seller_admin`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `amp_seller_id` INTEGER NOT NULL,
    `admin_id` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    `version` INTEGER DEFAULT 0,
    `version_created_at` DATETIME,
    `version_created_by` VARCHAR(100),
    PRIMARY KEY (`id`),
    INDEX `FI_amp_seller_admin_amp_seller_id` (`amp_seller_id`),
    INDEX `FI_amp_seller_admin_admin_id` (`admin_id`),
    CONSTRAINT `fk_amp_seller_admin_amp_seller_id`
        FOREIGN KEY (`amp_seller_id`)
        REFERENCES `amp_seller` (`id`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE,
    CONSTRAINT `fk_amp_seller_admin_admin_id`
        FOREIGN KEY (`admin_id`)
        REFERENCES `admin` (`id`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_product
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_product`;

CREATE TABLE `amp_seller_product`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `amp_seller_id` INTEGER NOT NULL,
    `product_id` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    `version` INTEGER DEFAULT 0,
    `version_created_at` DATETIME,
    `version_created_by` VARCHAR(100),
    PRIMARY KEY (`id`),
    INDEX `FI_amp_seller_product_amp_seller_id` (`amp_seller_id`),
    INDEX `FI_amp_seller_product_product_id` (`product_id`),
    CONSTRAINT `fk_amp_seller_product_amp_seller_id`
        FOREIGN KEY (`amp_seller_id`)
        REFERENCES `amp_seller` (`id`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE,
    CONSTRAINT `fk_amp_seller_product_product_id`
        FOREIGN KEY (`product_id`)
        REFERENCES `product` (`id`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_image
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_image`;

CREATE TABLE `amp_seller_image`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `amp_seller_id` INTEGER NOT NULL,
    `file` VARCHAR(255) NOT NULL,
    `visible` TINYINT DEFAULT 1 NOT NULL,
    `position` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    INDEX `fk_amp_seller_image_amp_seller_id` (`amp_seller_id`),
    INDEX `fk_amp_seller_image_amp_seller_id_position` (`amp_seller_id`, `position`),
    CONSTRAINT `amp_seller_image_FK_1`
        FOREIGN KEY (`amp_seller_id`)
        REFERENCES `amp_seller` (`id`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_order
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_order`;

CREATE TABLE `amp_seller_order`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `order_id` INTEGER NOT NULL,
    `amp_seller_id` INTEGER NOT NULL,
    `customer_id` INTEGER NOT NULL,
    `order_product_id` VARCHAR(1000) COMMENT 'the order products form the order',
    `amp_seller_order_counter` INTEGER NOT NULL,
    `seller_order_ref` VARCHAR(100) COMMENT 'the seller order reference',
    `discount` DECIMAL(16,6) DEFAULT 0.000000,
    `postage` DECIMAL(16,6) DEFAULT 0.000000 NOT NULL,
    `postage_tax` DECIMAL(16,6) DEFAULT 0.000000 NOT NULL,
    `status_id` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    `version` INTEGER DEFAULT 0,
    `version_created_at` DATETIME,
    `version_created_by` VARCHAR(100),
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ref_UNIQUE` (`seller_order_ref`),
    INDEX `idx_amp_seller_order_amp_seller_id` (`amp_seller_id`),
    INDEX `idx_amp_seller_order_customer_id` (`customer_id`),
    INDEX `idx_amp_seller_order_status_id` (`status_id`),
    INDEX `FI_amp_seller_order_order_id` (`order_id`),
    CONSTRAINT `fk_amp_seller_order_customer_id`
        FOREIGN KEY (`customer_id`)
        REFERENCES `customer` (`id`)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT,
    CONSTRAINT `amp_seller_order_FK_2`
        FOREIGN KEY (`amp_seller_id`)
        REFERENCES `amp_seller` (`id`),
    CONSTRAINT `fk_amp_seller_order_status_id`
        FOREIGN KEY (`status_id`)
        REFERENCES `amp_seller_order_status` (`id`)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT,
    CONSTRAINT `fk_amp_seller_order_order_id`
        FOREIGN KEY (`order_id`)
        REFERENCES `order` (`id`)
        ON UPDATE RESTRICT
        ON DELETE RESTRICT
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_order_status
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_order_status`;

CREATE TABLE `amp_seller_order_status`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `code` VARCHAR(45) NOT NULL,
    `color` CHAR(7),
    `position` INTEGER,
    `protected_status` TINYINT(1) DEFAULT 0,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `code_UNIQUE` (`code`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_order_delivery_reference
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_order_delivery_reference`;

CREATE TABLE `amp_seller_order_delivery_reference`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `order_seller_id` INTEGER NOT NULL,
    `delivery_reference` VARCHAR(255) NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    `version` INTEGER DEFAULT 0,
    `version_created_at` DATETIME,
    `version_created_by` VARCHAR(100),
    PRIMARY KEY (`id`),
    INDEX `FI_amp_seller_order_delivery_reference_order_seller_id` (`order_seller_id`),
    CONSTRAINT `fk_amp_seller_order_delivery_reference_order_seller_id`
        FOREIGN KEY (`order_seller_id`)
        REFERENCES `amp_seller_order` (`id`)
        ON UPDATE RESTRICT
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_i18n
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_i18n`;

CREATE TABLE `amp_seller_i18n`
(
    `id` INTEGER NOT NULL,
    `locale` VARCHAR(5) DEFAULT 'en_US' NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `access` TEXT,
    PRIMARY KEY (`id`,`locale`),
    CONSTRAINT `amp_seller_i18n_FK_1`
        FOREIGN KEY (`id`)
        REFERENCES `amp_seller` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_image_i18n
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_image_i18n`;

CREATE TABLE `amp_seller_image_i18n`
(
    `id` INTEGER NOT NULL,
    `locale` VARCHAR(5) DEFAULT 'en_US' NOT NULL,
    `title` VARCHAR(255),
    `description` LONGTEXT,
    `chapo` TEXT,
    `postscriptum` TEXT,
    PRIMARY KEY (`id`,`locale`),
    CONSTRAINT `amp_seller_image_i18n_FK_1`
        FOREIGN KEY (`id`)
        REFERENCES `amp_seller_image` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_order_status_i18n
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_order_status_i18n`;

CREATE TABLE `amp_seller_order_status_i18n`
(
    `id` INTEGER NOT NULL,
    `locale` VARCHAR(5) DEFAULT 'en_US' NOT NULL,
    `title` VARCHAR(255),
    `description` LONGTEXT,
    `chapo` TEXT,
    `postscriptum` TEXT,
    PRIMARY KEY (`id`,`locale`),
    CONSTRAINT `amp_seller_order_status_i18n_FK_1`
        FOREIGN KEY (`id`)
        REFERENCES `amp_seller_order_status` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_version
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_version`;

CREATE TABLE `amp_seller_version`
(
    `id` INTEGER NOT NULL,
    `ref` VARCHAR(255) NOT NULL,
    `visible` TINYINT DEFAULT 0 NOT NULL,
    `address1` VARCHAR(255) NOT NULL,
    `address2` VARCHAR(255),
    `address3` VARCHAR(255),
    `zipcode` VARCHAR(10) NOT NULL,
    `city` VARCHAR(255) NOT NULL,
    `country_id` INTEGER NOT NULL,
    `email` VARCHAR(255),
    `phone` VARCHAR(255),
    `fax` VARCHAR(255),
    `url` VARCHAR(255),
    `facebook` VARCHAR(255),
    `twitter` VARCHAR(255),
    `pinterest` VARCHAR(255),
    `google` VARCHAR(255),
    `youtube` VARCHAR(255),
    `instagram` VARCHAR(255),
    `latitude` DECIMAL(16,13) DEFAULT 0,
    `longitude` DECIMAL(16,13) DEFAULT 0,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    `version` INTEGER DEFAULT 0 NOT NULL,
    `version_created_at` DATETIME,
    `version_created_by` VARCHAR(100),
    `amp_seller_admin_ids` TEXT,
    `amp_seller_admin_versions` TEXT,
    `amp_seller_product_ids` TEXT,
    `amp_seller_product_versions` TEXT,
    `amp_seller_order_ids` TEXT,
    `amp_seller_order_versions` TEXT,
    PRIMARY KEY (`id`,`version`),
    CONSTRAINT `amp_seller_version_FK_1`
        FOREIGN KEY (`id`)
        REFERENCES `amp_seller` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_admin_version
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_admin_version`;

CREATE TABLE `amp_seller_admin_version`
(
    `id` INTEGER NOT NULL,
    `amp_seller_id` INTEGER NOT NULL,
    `admin_id` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    `version` INTEGER DEFAULT 0 NOT NULL,
    `version_created_at` DATETIME,
    `version_created_by` VARCHAR(100),
    `amp_seller_id_version` INTEGER DEFAULT 0,
    PRIMARY KEY (`id`,`version`),
    CONSTRAINT `amp_seller_admin_version_FK_1`
        FOREIGN KEY (`id`)
        REFERENCES `amp_seller_admin` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_product_version
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_product_version`;

CREATE TABLE `amp_seller_product_version`
(
    `id` INTEGER NOT NULL,
    `amp_seller_id` INTEGER NOT NULL,
    `product_id` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    `version` INTEGER DEFAULT 0 NOT NULL,
    `version_created_at` DATETIME,
    `version_created_by` VARCHAR(100),
    `amp_seller_id_version` INTEGER DEFAULT 0,
    `product_id_version` INTEGER DEFAULT 0,
    PRIMARY KEY (`id`,`version`),
    CONSTRAINT `amp_seller_product_version_FK_1`
        FOREIGN KEY (`id`)
        REFERENCES `amp_seller_product` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_order_version
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_order_version`;

CREATE TABLE `amp_seller_order_version`
(
    `id` INTEGER NOT NULL,
    `order_id` INTEGER NOT NULL,
    `amp_seller_id` INTEGER NOT NULL,
    `customer_id` INTEGER NOT NULL,
    `order_product_id` VARCHAR(1000) COMMENT 'the order products form the order',
    `amp_seller_order_counter` INTEGER NOT NULL,
    `seller_order_ref` VARCHAR(100) COMMENT 'the seller order reference',
    `discount` DECIMAL(16,6) DEFAULT 0.000000,
    `postage` DECIMAL(16,6) DEFAULT 0.000000 NOT NULL,
    `postage_tax` DECIMAL(16,6) DEFAULT 0.000000 NOT NULL,
    `status_id` INTEGER NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    `version` INTEGER DEFAULT 0 NOT NULL,
    `version_created_at` DATETIME,
    `version_created_by` VARCHAR(100),
    `customer_id_version` INTEGER DEFAULT 0,
    `amp_seller_id_version` INTEGER DEFAULT 0,
    `order_id_version` INTEGER DEFAULT 0,
    `amp_seller_order_delivery_reference_ids` TEXT,
    `amp_seller_order_delivery_reference_versions` TEXT,
    PRIMARY KEY (`id`,`version`),
    CONSTRAINT `amp_seller_order_version_FK_1`
        FOREIGN KEY (`id`)
        REFERENCES `amp_seller_order` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- amp_seller_order_delivery_reference_version
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `amp_seller_order_delivery_reference_version`;

CREATE TABLE `amp_seller_order_delivery_reference_version`
(
    `id` INTEGER NOT NULL,
    `order_seller_id` INTEGER NOT NULL,
    `delivery_reference` VARCHAR(255) NOT NULL,
    `created_at` DATETIME,
    `updated_at` DATETIME,
    `version` INTEGER DEFAULT 0 NOT NULL,
    `version_created_at` DATETIME,
    `version_created_by` VARCHAR(100),
    `order_seller_id_version` INTEGER DEFAULT 0,
    PRIMARY KEY (`id`,`version`),
    CONSTRAINT `amp_seller_order_delivery_reference_version_FK_1`
        FOREIGN KEY (`id`)
        REFERENCES `amp_seller_order_delivery_reference` (`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO `amp_seller_order_status`(`id`, `code`, `color`, `position`, `protected_status`, `created_at`, `updated_at`) VALUES
(1, 'not_paid', '#f0ad4e', 1, 1, NOW(), NOW()),
(2, 'paid', '#5cb85c', 2, 1, NOW(), NOW()),
(3, 'processing', '#f39922', 3, 1, NOW(), NOW()),
(4, 'sent', '#5bc0de', 4, 1, NOW(), NOW()),
(5, 'canceled', '#d9534f', 5, 1, NOW(), NOW()),
(6, 'refunded', '#986dff', 6, 1, NOW(), NOW());

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
