-- 1. Globale Sequence anlegen
CREATE SEQUENCE global_id_seq
    START 1000000
    INCREMENT 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- 2. Value Table
-- Value Table
CREATE TABLE IF NOT EXISTS values (
    id BIGINT PRIMARY KEY DEFAULT nextval('global_id_seq'),
    label TEXT,
    description TEXT,
    propertyId BIGINT
);

-- Property Table
CREATE TABLE IF NOT EXISTS properties (
    id BIGINT PRIMARY KEY DEFAULT nextval('global_id_seq'),
    label TEXT,
    description TEXT,
    propertyTypeId BIGINT,
    productId BIGINT,
    productGroupId BIGINT
);

-- PropertyType Table
CREATE TABLE IF NOT EXISTS propertyTypes(
    id BIGINT PRIMARY KEY DEFAULT nextval('global_id_seq'),
    label TEXT,
    description TEXT,
    propertyTypeGroupId BIGINT
);

-- PropertyTypeGroupTable
CREATE TABLE IF NOT EXISTS propertyTypeGroups (
    id BIGINT PRIMARY KEY DEFAULT nextval('global_id_seq'),
    label TEXT,
    description TEXT,
    parentGroupId BIGINT
);

-- 3. Product Tables
-- Product Table
CREATE TABLE IF NOT EXISTS products (
    id BIGINT PRIMARY KEY DEFAULT nextval('global_id_seq'),
    label TEXT,
    externalId TEXT,
    description TEXT,
    productGroupId BIGINT
);

-- ProductGroup Table
CREATE TABLE IF NOT EXISTS productGroups (
    id BIGINT PRIMARY KEY DEFAULT nextval('global_id_seq'),
    label TEXT,
    description TEXT,
    parentGroupId BIGINT
);

-- Media Table
CREATE TABLE IF NOT EXISTS media (
    id BIGINT PRIMARY KEY DEFAULT nextval('global_id_seq'),
    label TEXT,
    description TEXT,
    media TEXT,
    mediaType TEXT,
    externalId BIGINT
);

CREATE TABLE IF NOT EXISTS variantNodeContent (
    id bigint NOT NULL DEFAULT nextval('global_id_seq'::regclass),
    "variantNodeId" bigint,
    "productId" bigint,
    CONSTRAINT "valueNodeContent_pkey" PRIMARY KEY (id)
)

CREATE TABLE IF NOT EXISTS variantNode
(
    id bigint NOT NULL DEFAULT nextval('global_id_seq'::regclass),
    label text COLLATE pg_catalog."default",
    description text COLLATE pg_catalog."default",
    "bomId" bigint,
    min integer DEFAULT 1,
    max integer DEFAULT 1,
    CONSTRAINT "variantNode_pkey" PRIMARY KEY (id)
)


CREATE TABLE IF NOT EXISTS billOfMaterial
(
    id bigint NOT NULL DEFAULT nextval('global_id_seq'::regclass),
    "productId" bigint,
    label text COLLATE pg_catalog."default",
    description text COLLATE pg_catalog."default",
    "parentBOMId" bigint,
    CONSTRAINT "billOfMaterial_pkey" PRIMARY KEY (id)
)

CREATE TABLE IF NOT EXISTS bomContent
(
    id bigint NOT NULL DEFAULT nextval('global_id_seq'::regclass),
    "bomId" bigint,
    "productId" bigint,
    min integer DEFAULT 1,
    max integer DEFAULT 1,
    CONSTRAINT "bomContent_pkey" PRIMARY KEY (id)
)