Neuer Changes-DB-Import: Übersicht und Datenbankschema

CREATE TABLE brands (
    id                            BIGINT         IDENTITY(1,1) NOT NULL UNIQUE,
    code                          VARCHAR(50)    NOT NULL PRIMARY KEY,
    name                          VARCHAR(255)   NULL,
    logo                          VARCHAR(512)   NULL,
    description                   VARCHAR(MAX)   NULL,
    slogan                        VARCHAR(500)   NULL,
    supplierNumber                VARCHAR(50)    NULL,
    priority                      BIGINT         NOT NULL,
    useOxomiPreviewImages         BIT            NOT NULL,
    oxomiSupplierNumber           BIT            NOT NULL,
    manufacturerName              VARCHAR(255)   NULL,
    manufacturerPostalAddress     VARCHAR(500)   NULL,
    manufacturerElectronicAddress VARCHAR(255)   NULL,
    distributorName               VARCHAR(255)   NULL,
    distributorPostalAddress      VARCHAR(500)   NULL,
    distributorElectronicAddress  VARCHAR(255)   NULL,
    changeDate                    DATETIME2      NULL
);
CREATE INDEX idx_brands_changedate ON brands(changeDate);

CREATE TABLE featuresettings (
    id                   BIGINT       IDENTITY(1,1) NOT NULL UNIQUE,
    code                 VARCHAR(255) NOT NULL PRIMARY KEY,
    type                 VARCHAR(20)  NOT NULL,
    label                VARCHAR(255) NULL,
    priority             INT          NOT NULL,
    showInDetails        BIT          NOT NULL,
    showInList           BIT          NOT NULL,
    showAsFilter         BIT          NOT NULL,
    noRangeFilter        BIT          NOT NULL,
    useAsContent         BIT          NOT NULL,
    useAsExtendedContent BIT          NOT NULL,
    showInTooltip        BIT          NOT NULL,
    characteristic       BIT          NOT NULL,
    multiple             BIT          NOT NULL,
    changeDate           DATETIME2    NULL
);
CREATE INDEX idx_featuresettings_changedate ON featuresettings(changeDate);

CREATE TABLE catalogs (
    id          BIGINT       IDENTITY(1,1) NOT NULL UNIQUE,
    name        VARCHAR(255) NOT NULL PRIMARY KEY,
    visibleName VARCHAR(255) NULL,
    logo        VARCHAR(512) NULL,
    offer       BIT          NOT NULL,
    partnerRole VARCHAR(255) NULL,
    validFrom   DATE         NULL,
    validUntil  DATE         NULL,
    language    VARCHAR(2)   NULL,
    changeDate  DATETIME2    NULL
);
CREATE INDEX idx_catalogs_changedate ON catalogs(changeDate);

CREATE TABLE [groups] (
    id         BIGINT         IDENTITY(1,1) NOT NULL UNIQUE,
    code       VARCHAR(255) NOT NULL PRIMARY KEY,
    name       VARCHAR(255) NULL,
    image      VARCHAR(512) NULL,
    parent     VARCHAR(255) NULL,
    type       VARCHAR(30)  NOT NULL,
    brand      VARCHAR(50)  NULL,
    priority   INT          NOT NULL,
    cssClass   VARCHAR(255) NULL,
    changeDate DATETIME2    NULL
);
CREATE INDEX idx_groups_changedate ON [groups](changeDate);

CREATE TABLE grouptexts (
    id         BIGINT          IDENTITY(1,1) NOT NULL UNIQUE,
    [group]    VARCHAR(255)  NOT NULL,
    type       VARCHAR(50)   NOT NULL,
    text       VARCHAR(MAX)  NOT NULL,
    changeDate DATETIME2     NULL
);
CREATE INDEX idx_grouptexts_group ON grouptexts([group]);
CREATE INDEX idx_grouptexts_changedate ON grouptexts(changeDate);

CREATE TABLE quantityunits (
    id           BIGINT         IDENTITY(1,1) NOT NULL UNIQUE,
    code         VARCHAR(255) NOT NULL PRIMARY KEY,
    shortName    VARCHAR(255) NULL,
    unitName     VARCHAR(255) NULL,
    alias1       VARCHAR(255) NULL,
    alias2       VARCHAR(255) NULL,
    alias3       VARCHAR(255) NULL,
    alias4       VARCHAR(255) NULL,
    alias5       VARCHAR(255) NULL,
    alias6       VARCHAR(255) NULL,
    standardCode VARCHAR(255) NULL,
    erpCode      VARCHAR(255) NULL,
    changeDate   DATETIME2    NULL
);
CREATE INDEX idx_quantityunits_changedate ON quantityunits(changeDate);

CREATE TABLE variants (
    id                        BIGINT        IDENTITY(1,1) NOT NULL UNIQUE,
    code                      VARCHAR(255)  NOT NULL PRIMARY KEY,
    name                      VARCHAR(1024) NULL,
    previewImage              VARCHAR(512)  NULL,
    featureX                  VARCHAR(128)  NULL,
    featureY                  VARCHAR(128)  NULL,
    itemPreviewIsVariantImage BIT           NOT NULL,
    changeDate                DATETIME2     NULL
);
CREATE INDEX idx_variants_changedate ON variants(changeDate);

CREATE TABLE items (
    id                    BIGINT         IDENTITY(1,1) NOT NULL UNIQUE,
    itemNumber            VARCHAR(50)    NOT NULL PRIMARY KEY,
    shortText             VARCHAR(MAX)   NOT NULL,
    previewImage          VARCHAR(512)   NULL,
    brand                 VARCHAR(50)    NULL,
    supplierNumber        VARCHAR(50)    NULL,
    ranking               BIGINT         NOT NULL,
    quantityUnit          VARCHAR(255)   NULL,
    priceQuantity         DECIMAL(20, 3) NOT NULL,
    minOrderQuantity      DECIMAL(20, 3) NOT NULL,
    orderStep             DECIMAL(20, 3) NOT NULL,
    gtin                   VARCHAR(50)    NULL,
    supplierItemNumber     VARCHAR(50)    NULL,
    userCorrelationNumber VARCHAR(50)    NULL,
    model                 VARCHAR(50)    NULL,
    variant               VARCHAR(255)   NULL,
    classificationSystem  VARCHAR(50)    NULL,
    classificationClass   VARCHAR(50)    NULL,
    priority              BIGINT         NOT NULL,
    changeDate            DATETIME2      NOT NULL,
    deleted               BIT            NOT NULL
);
CREATE INDEX idx_items_changedate ON items(changeDate);

CREATE TABLE features (
    id               BIGINT        IDENTITY(1,1) NOT NULL UNIQUE,
    item             VARCHAR(50)   NOT NULL,
    code             VARCHAR(128)  NOT NULL,
    value            VARCHAR(255)  NULL,
    value2           VARCHAR(255)  NULL,
    valueDescription VARCHAR(255)  NULL,
    unit             VARCHAR(50)   NULL
);
CREATE INDEX idx_features_item ON features(item);

CREATE TABLE additionaltexts (
    id       BIGINT       IDENTITY(1,1) NOT NULL UNIQUE,
    item     VARCHAR(50)  NOT NULL,
    language VARCHAR(3)   NOT NULL,
    type     VARCHAR(50)  NOT NULL,
    text     VARCHAR(MAX) NOT NULL
);
CREATE INDEX idx_additionaltexts_item ON additionaltexts(item);

CREATE TABLE attachments (
    id          BIGINT        IDENTITY(1,1) NOT NULL UNIQUE,
    item        VARCHAR(50)   NOT NULL,
    type        VARCHAR(50)   NOT NULL,
    [file]      VARCHAR(1024) NOT NULL,
    languages   VARCHAR(60)   NULL,
    description VARCHAR(255)  NULL,
    priority    INT           NOT NULL
);
CREATE INDEX idx_attachments_item ON attachments(item);

CREATE TABLE itemrelations (
    id                  BIGINT         IDENTITY(1,1) NOT NULL UNIQUE,
    sourceItem          VARCHAR(50)    NOT NULL,
    targetItem          VARCHAR(50)    NOT NULL,
    relationType        VARCHAR(30)    NOT NULL,
    priority            INT            NOT NULL,
    recommendedQuantity DECIMAL(20, 3) NULL,
    [group]             VARCHAR(50)    NULL
);
CREATE INDEX idx_itemrelations_source ON itemrelations(sourceItem);
CREATE INDEX idx_itemrelations_target ON itemrelations(targetItem);

CREATE TABLE catalogitemmappings (
    id                BIGINT         IDENTITY(1,1) NOT NULL UNIQUE,
    catalog           VARCHAR(255)   NOT NULL,
    item              VARCHAR(50)    NOT NULL,
    catalogItemNumber VARCHAR(50)    NULL,
    price             DECIMAL(20, 3) NULL,
    discountGroup     VARCHAR(50)    NULL,
    PRIMARY KEY (catalog, item)
);
CREATE INDEX idx_catalogitemmappings_item ON catalogitemmappings(item);

CREATE TABLE pricescales (
    id             BIGINT         IDENTITY(1,1) NOT NULL UNIQUE,
    catalog        VARCHAR(255)   NOT NULL,
    item           VARCHAR(50)    NOT NULL,
    conditionValue DECIMAL(20, 3) NOT NULL,
    price          DECIMAL(20, 3) NULL,
    PRIMARY KEY (catalog, item, conditionValue)
);
CREATE INDEX idx_pricescales_item_catalog ON pricescales(item, catalog);

CREATE TABLE groupmappings (
    id      BIGINT         IDENTITY(1,1) NOT NULL UNIQUE,
    item    VARCHAR(50)  NOT NULL,
    [group] VARCHAR(255) NOT NULL,
    PRIMARY KEY (item, [group])
);
CREATE INDEX idx_groupmappings_group ON groupmappings([group]);

CREATE TABLE brands (
    id                            BIGINT         NOT NULL AUTO_INCREMENT,
    code                          VARCHAR(50)    NOT NULL,
    name                          VARCHAR(255)   NULL,
    logo                          VARCHAR(512)   NULL,
    description                   TEXT           NULL,
    slogan                        VARCHAR(500)   NULL,
    supplierNumber                VARCHAR(50)    NULL,
    priority                      BIGINT         NOT NULL,
    useOxomiPreviewImages         TINYINT(1)     NOT NULL,
    oxomiSupplierNumber           TINYINT(1)     NOT NULL,
    manufacturerName              VARCHAR(255)   NULL,
    manufacturerPostalAddress     VARCHAR(500)   NULL,
    manufacturerElectronicAddress VARCHAR(255)   NULL,
    distributorName               VARCHAR(255)   NULL,
    distributorPostalAddress      VARCHAR(500)   NULL,
    distributorElectronicAddress  VARCHAR(255)   NULL,
    changeDate                    DATETIME       NULL,

    PRIMARY KEY (code),
    UNIQUE KEY uk_brands_id (id),
    INDEX idx_brands_changedate (changeDate)
);

CREATE TABLE featuresettings (
    id                   BIGINT       NOT NULL AUTO_INCREMENT,
    code                 VARCHAR(255) NOT NULL,
    type                 VARCHAR(20)  NOT NULL,
    label                VARCHAR(255) NULL,
    priority             INT          NOT NULL,
    showInDetails        TINYINT(1)   NOT NULL,
    showInList           TINYINT(1)   NOT NULL,
    showAsFilter         TINYINT(1)   NOT NULL,
    noRangeFilter        TINYINT(1)   NOT NULL,
    useAsContent         TINYINT(1)   NOT NULL,
    useAsExtendedContent TINYINT(1)   NOT NULL,
    showInTooltip        TINYINT(1)   NOT NULL,
    characteristic       TINYINT(1)   NOT NULL,
    multiple             TINYINT(1)   NOT NULL,
    changeDate           DATETIME     NULL,

    PRIMARY KEY (code),
    UNIQUE KEY uk_featuresettings_id (id),
    INDEX idx_featuresettings_changedate (changeDate)
);

CREATE TABLE catalogs (
    id              BIGINT         NOT NULL AUTO_INCREMENT,
    name            VARCHAR(255) NOT NULL,
    visibleName     VARCHAR(255) NULL,
    logo            VARCHAR(512) NULL,
    offer           TINYINT(1)   NOT NULL,
    partnerRole     VARCHAR(255) NULL,
    validFrom       DATE         NULL,
    validUntil      DATE         NULL,
    language        VARCHAR(2)   NULL,
    changeDate      DATETIME     NULL,

    PRIMARY KEY (name),
    UNIQUE KEY uk_catalogs_id (id),
    INDEX idx_catalogs_changedate (changeDate)
);

CREATE TABLE `groups` (
    id         BIGINT         NOT NULL AUTO_INCREMENT,
    code       VARCHAR(255) NOT NULL,
    name       VARCHAR(255) NULL,
    image      VARCHAR(512) NULL,
    parent     VARCHAR(255) NULL,
    type       VARCHAR(30)  NOT NULL,
    brand      VARCHAR(50)  NULL,
    priority   INT          NOT NULL,
    cssClass   VARCHAR(255) NULL,
    changeDate DATETIME     NULL,

    PRIMARY KEY (code),
    UNIQUE KEY uk_groups_id (id),
    INDEX idx_groups_changedate (changeDate)
);

CREATE TABLE grouptexts (
    id         BIGINT         NOT NULL AUTO_INCREMENT,
    `group`    VARCHAR(255) NOT NULL,
    type       VARCHAR(50)  NOT NULL,
    text       TEXT         NOT NULL,
    changeDate DATETIME     NULL,

    UNIQUE KEY uk_grouptexts_id (id),
    INDEX idx_grouptexts_group (`group`),
    INDEX idx_grouptexts_changedate (changeDate)
);

CREATE TABLE quantityunits (
    id           BIGINT         NOT NULL AUTO_INCREMENT,
    code         VARCHAR(255) NOT NULL,
    shortName    VARCHAR(255) NULL,
    unitName     VARCHAR(255) NULL,
    alias1       VARCHAR(255) NULL,
    alias2       VARCHAR(255) NULL,
    alias3       VARCHAR(255) NULL,
    alias4       VARCHAR(255) NULL,
    alias5       VARCHAR(255) NULL,
    alias6       VARCHAR(255) NULL,
    standardCode VARCHAR(255) NULL,
    erpCode      VARCHAR(255) NULL,
    changeDate   DATETIME     NULL,

    PRIMARY KEY (code),
    UNIQUE KEY uk_quantityunits_id (id),
    INDEX idx_quantityunits_changedate (changeDate)
);

CREATE TABLE variants (
    id                         BIGINT        NOT NULL AUTO_INCREMENT,
    code                       VARCHAR(255)  NOT NULL,
    name                       VARCHAR(1024) NULL,
    previewImage               VARCHAR(512)  NULL,
    featureX                   VARCHAR(128)  NULL,
    featureY                   VARCHAR(128)  NULL,
    itemPreviewIsVariantImage  TINYINT(1)    NOT NULL,
    changeDate                 DATETIME      NULL,

    PRIMARY KEY (code),
    UNIQUE KEY uk_variants_id (id),
    INDEX idx_variants_changedate (changeDate)
);

CREATE TABLE items (
    id                      BIGINT         NOT NULL AUTO_INCREMENT,
    itemNumber              VARCHAR(50)    NOT NULL,
    shortText               TEXT           NOT NULL,
    previewImage            VARCHAR(512)   NULL,
    brand                   VARCHAR(50)    NULL,
    supplierNumber          VARCHAR(50)    NULL,
    ranking                 BIGINT         NOT NULL,
    quantityUnit            VARCHAR(255)   NULL,
    priceQuantity           DECIMAL(20, 3) NOT NULL,
    minOrderQuantity        DECIMAL(20, 3) NOT NULL,
    orderStep               DECIMAL(20, 3) NOT NULL,
    gtin                    VARCHAR(50)    NULL,
    supplierItemNumber      VARCHAR(50)    NULL,
    userCorrelationNumber   VARCHAR(50)    NULL,
    model                   VARCHAR(50)    NULL,
    variant                 VARCHAR(255)   NULL,
    classificationSystem    VARCHAR(50)    NULL,
    classificationClass     VARCHAR(50)    NULL,
    priority                BIGINT         NOT NULL,
    changeDate              DATETIME       NOT NULL,
    deleted                 TINYINT(1)     NOT NULL,

    PRIMARY KEY (itemNumber),
    UNIQUE KEY uk_items_id (id),
    INDEX idx_items_changedate (changeDate)
);

CREATE TABLE features (
    id               BIGINT       NOT NULL AUTO_INCREMENT,
    item             VARCHAR(50)  NOT NULL,
    code             VARCHAR(128) NOT NULL,
    value            VARCHAR(255) NULL,
    value2           VARCHAR(255) NULL,
    valueDescription VARCHAR(255) NULL,
    unit             VARCHAR(50)  NULL,

    UNIQUE KEY uk_features_id (id),
    INDEX idx_features_item (item)
);

CREATE TABLE additionaltexts (
    id       BIGINT      NOT NULL AUTO_INCREMENT,
    item     VARCHAR(50) NOT NULL,
    language VARCHAR(3)  NOT NULL,
    type     VARCHAR(50) NOT NULL,
    text     TEXT        NOT NULL,

    UNIQUE KEY uk_additionaltexts_id (id),
    INDEX idx_additionaltexts_item (item)
);

CREATE TABLE attachments (
    id          BIGINT        NOT NULL AUTO_INCREMENT,
    item        VARCHAR(50)   NOT NULL,
    type        VARCHAR(50)   NOT NULL,
    file        VARCHAR(1024) NOT NULL,
    languages   VARCHAR(60)   NULL,
    description VARCHAR(255)  NULL,
    priority    INT           NOT NULL,

    UNIQUE KEY uk_attachments_id (id),
    INDEX idx_attachments_item (item)
);

CREATE TABLE itemrelations (
    id                  BIGINT         NOT NULL AUTO_INCREMENT,
    sourceItem          VARCHAR(50)    NOT NULL,
    targetItem          VARCHAR(50)    NOT NULL,
    relationType        VARCHAR(30)    NOT NULL,
    priority            INT            NOT NULL,
    recommendedQuantity DECIMAL(20, 3) NULL,
    `group`             VARCHAR(50)    NULL,

    UNIQUE KEY uk_itemrelations_id (id),
    INDEX idx_itemrelations_source (sourceItem),
    INDEX idx_itemrelations_target (targetItem)
);

CREATE TABLE catalogitemmappings (
    id                BIGINT         NOT NULL AUTO_INCREMENT,
    catalog           VARCHAR(255)   NOT NULL,
    item              VARCHAR(50)    NOT NULL,
    catalogItemNumber VARCHAR(50)    NULL,
    price             DECIMAL(20, 3) NULL,
    discountGroup     VARCHAR(50)    NULL,

    PRIMARY KEY (catalog, item),
    UNIQUE KEY uk_catalogitemmappings_id (id),
    INDEX idx_catalogitemmappings_item (item)
);

CREATE TABLE pricescales (
    id             BIGINT         NOT NULL AUTO_INCREMENT,
    catalog        VARCHAR(255)   NOT NULL,
    item           VARCHAR(50)    NOT NULL,
    conditionValue DECIMAL(20, 3) NOT NULL,
    price          DECIMAL(20, 3) NULL,

    PRIMARY KEY (catalog, item, conditionValue),
    UNIQUE KEY uk_pricescales_id (id),
    INDEX idx_pricescales_item_catalog (item, catalog)
);

CREATE TABLE groupmappings (
    id        BIGINT         NOT NULL AUTO_INCREMENT,
    item      VARCHAR(50)  NOT NULL,
    `group`   VARCHAR(255) NOT NULL,

    PRIMARY KEY (item, `group`),
    UNIQUE KEY uk_groupmappings_id (id),
    INDEX idx_groupmappings_group (`group`)
);

CREATE TABLE brands (
    id                            BIGINT         GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    code                          VARCHAR(50)    PRIMARY KEY,
    name                          VARCHAR(255),
    logo                          VARCHAR(512),
    description                   TEXT,
    slogan                        VARCHAR(500),
    supplierNumber                VARCHAR(50),
    priority                      BIGINT         NOT NULL,
    useOxomiPreviewImages         BOOLEAN        NOT NULL,
    oxomiSupplierNumber           BOOLEAN        NOT NULL,
    manufacturerName              VARCHAR(255),
    manufacturerPostalAddress     VARCHAR(500),
    manufacturerElectronicAddress VARCHAR(255),
    distributorName               VARCHAR(255),
    distributorPostalAddress      VARCHAR(500),
    distributorElectronicAddress  VARCHAR(255),
    changeDate                    TIMESTAMP
);
CREATE INDEX idx_brands_changedate ON brands(changeDate);

CREATE TABLE featuresettings (
    id                   BIGINT       GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    code                 VARCHAR(255) PRIMARY KEY,
    type                 VARCHAR(20)  NOT NULL,
    label                VARCHAR(255),
    priority             INTEGER      NOT NULL,
    showInDetails        BOOLEAN      NOT NULL,
    showInList           BOOLEAN      NOT NULL,
    showAsFilter         BOOLEAN      NOT NULL,
    noRangeFilter        BOOLEAN      NOT NULL,
    useAsContent         BOOLEAN      NOT NULL,
    useAsExtendedContent BOOLEAN      NOT NULL,
    showInTooltip        BOOLEAN      NOT NULL,
    characteristic       BOOLEAN      NOT NULL,
    multiple             BOOLEAN      NOT NULL,
    changeDate           TIMESTAMP
);
CREATE INDEX idx_featuresettings_changedate ON featuresettings(changeDate);

CREATE TABLE catalogs (
    id          BIGINT       GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    name        VARCHAR(255) PRIMARY KEY,
    visibleName VARCHAR(255),
    logo        VARCHAR(512),
    offer       BOOLEAN      NOT NULL,
    partnerRole VARCHAR(255),
    validFrom   DATE,
    validUntil  DATE,
    language    VARCHAR(2),
    changeDate  TIMESTAMP
);
CREATE INDEX idx_catalogs_changedate ON catalogs(changeDate);

CREATE TABLE "groups" (
    id         BIGINT         GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    code       VARCHAR(255) PRIMARY KEY,
    name       VARCHAR(255),
    image      VARCHAR(512),
    parent     VARCHAR(255),
    type       VARCHAR(30)  NOT NULL,
    brand      VARCHAR(50),
    priority   INTEGER      NOT NULL,
    cssClass   VARCHAR(255),
    changeDate TIMESTAMP
);
CREATE INDEX idx_groups_changedate ON "groups"(changeDate);

CREATE TABLE grouptexts (
    id         BIGINT         GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    "group"    VARCHAR(255) NOT NULL,
    type       VARCHAR(50)  NOT NULL,
    text       TEXT         NOT NULL,
    changeDate TIMESTAMP
);
CREATE INDEX idx_grouptexts_group ON grouptexts("group");
CREATE INDEX idx_grouptexts_changedate ON grouptexts(changeDate);

CREATE TABLE quantityunits (
    id           BIGINT         GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    code         VARCHAR(255) PRIMARY KEY,
    shortName    VARCHAR(255),
    unitName     VARCHAR(255),
    alias1       VARCHAR(255),
    alias2       VARCHAR(255),
    alias3       VARCHAR(255),
    alias4       VARCHAR(255),
    alias5       VARCHAR(255),
    alias6       VARCHAR(255),
    standardCode VARCHAR(255),
    erpCode      VARCHAR(255),
    changeDate   TIMESTAMP
);
CREATE INDEX idx_quantityunits_changedate ON quantityunits(changeDate);

CREATE TABLE variants (
    id                        BIGINT        GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    code                      VARCHAR(255)  PRIMARY KEY,
    name                      VARCHAR(1024),
    previewImage              VARCHAR(512),
    featureX                  VARCHAR(128),
    featureY                  VARCHAR(128),
    itemPreviewIsVariantImage BOOLEAN       NOT NULL,
    changeDate                TIMESTAMP
);
CREATE INDEX idx_variants_changedate ON variants(changeDate);

CREATE TABLE items (
    id                    BIGINT         GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    itemNumber            VARCHAR(50)    PRIMARY KEY,
    shortText             TEXT           NOT NULL,
    previewImage          VARCHAR(512),
    brand                 VARCHAR(50),
    supplierNumber        VARCHAR(50),
    ranking               BIGINT         NOT NULL,
    quantityUnit          VARCHAR(255),
    priceQuantity         NUMERIC(20, 3) NOT NULL,
    minOrderQuantity      NUMERIC(20, 3) NOT NULL,
    orderStep             NUMERIC(20, 3) NOT NULL,
    gtin                   VARCHAR(50),
    supplierItemNumber     VARCHAR(50),
    userCorrelationNumber VARCHAR(50),
    model                 VARCHAR(50),
    variant               VARCHAR(255),
    classificationSystem  VARCHAR(50),
    classificationClass   VARCHAR(50),
    priority              BIGINT         NOT NULL,
    changeDate            TIMESTAMP      NOT NULL,
    deleted               BOOLEAN        NOT NULL
);
CREATE INDEX idx_items_changedate ON items(changeDate);

CREATE TABLE features (
    id               BIGINT       GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    item             VARCHAR(50)  NOT NULL,
    code             VARCHAR(128) NOT NULL,
    value            VARCHAR(255),
    value2           VARCHAR(255),
    valueDescription VARCHAR(255),
    unit             VARCHAR(50)
);
CREATE INDEX idx_features_item ON features(item);

CREATE TABLE additionaltexts (
    id       BIGINT      GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    item     VARCHAR(50) NOT NULL,
    language VARCHAR(3)  NOT NULL,
    type     VARCHAR(50) NOT NULL,
    text     TEXT        NOT NULL
);
CREATE INDEX idx_additionaltexts_item ON additionaltexts(item);

CREATE TABLE attachments (
    id          BIGINT        GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    item        VARCHAR(50)   NOT NULL,
    type        VARCHAR(50)   NOT NULL,
    file        VARCHAR(1024) NOT NULL,
    languages   VARCHAR(60),
    description VARCHAR(255),
    priority    INTEGER       NOT NULL
);
CREATE INDEX idx_attachments_item ON attachments(item);

CREATE TABLE itemrelations (
    id                  BIGINT         GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    sourceItem          VARCHAR(50)    NOT NULL,
    targetItem          VARCHAR(50)    NOT NULL,
    relationType        VARCHAR(30)    NOT NULL,
    priority            INTEGER        NOT NULL,
    recommendedQuantity NUMERIC(20, 3),
    "group"             VARCHAR(50)
);
CREATE INDEX idx_itemrelations_source ON itemrelations(sourceItem);
CREATE INDEX idx_itemrelations_target ON itemrelations(targetItem);

CREATE TABLE catalogitemmappings (
    id                BIGINT         GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    catalog           VARCHAR(255)   NOT NULL,
    item              VARCHAR(50)    NOT NULL,
    catalogItemNumber VARCHAR(50),
    price             NUMERIC(20, 3),
    discountGroup     VARCHAR(50),
    PRIMARY KEY (catalog, item)
);
CREATE INDEX idx_catalogitemmappings_item ON catalogitemmappings(item);

CREATE TABLE pricescales (
    id             BIGINT         GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    catalog        VARCHAR(255)   NOT NULL,
    item           VARCHAR(50)    NOT NULL,
    conditionValue NUMERIC(20, 3) NOT NULL,
    price          NUMERIC(20, 3),
    PRIMARY KEY (catalog, item, conditionValue)
);
CREATE INDEX idx_pricescales_item_catalog ON pricescales(item, catalog);

CREATE TABLE groupmappings (
    id      BIGINT         GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    item    VARCHAR(50)  NOT NULL,
    "group" VARCHAR(255) NOT NULL,
    PRIMARY KEY (item, "group")
);
CREATE INDEX idx_groupmappings_group ON groupmappings("group");