CREATE TABLE BRANDS
(
    id   BIGSERIAL PRIMARY KEY,
    name VARCHAR(63) NOT NULL,
    CONSTRAINT UQ_BRANDS_NAME UNIQUE (name),
    CONSTRAINT CK_BRANDS_NAME_NOT_EMPTY CHECK (btrim(name) <> '')
);

CREATE TABLE PRODUCTS
(
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(63) NOT NULL,
    description TEXT,
    created_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT CK_PRODUCTS_NAME_NOT_EMPTY CHECK (btrim(name) <> '')
);

CREATE TABLE PRODUCT_VARIANTS
(
    id         BIGSERIAL PRIMARY KEY,
    product_id BIGINT         NOT NULL,
    sku        VARCHAR(63)    NOT NULL,
    brand_id   BIGINT         NOT NULL,
    barcode    VARCHAR(63),
    price      NUMERIC(10, 2) NOT NULL,
    weight     NUMERIC(10, 2),
    status     VARCHAR(50)    NOT NULL,
    created_at TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_PRODUCT_VARIANTS_PRODUCT FOREIGN KEY (product_id) REFERENCES PRODUCTS (id) ON DELETE RESTRICT,
    CONSTRAINT UQ_PRODUCT_VARIANTS_SKU UNIQUE (sku),
    CONSTRAINT FK_PRODUCT_VARIANTS_BRAND FOREIGN KEY (brand_id) REFERENCES BRANDS (id) ON DELETE RESTRICT,
    CONSTRAINT UQ_PRODUCT_VARIANTS_BARCODE UNIQUE (barcode),
    CONSTRAINT CK_PRODUCT_VARIANTS_SKU_NOT_EMPTY CHECK (btrim(sku) <> ''),
    CONSTRAINT CK_PRODUCT_VARIANTS_BARCODE_NOT_EMPTY CHECK (barcode IS NULL OR btrim(barcode) <> ''),
    CONSTRAINT CK_PRODUCT_VARIANTS_PRICE_NONNEGATIVE CHECK (price >= 0),
    CONSTRAINT CK_PRODUCT_VARIANTS_WEIGHT_NONNEGATIVE CHECK (weight IS NULL OR weight >= 0),
    CONSTRAINT CK_PRODUCT_VARIANTS_STATUS_NOT_EMPTY CHECK (btrim(status) <> '')
);

--we don't want to keep images for products that don't exist
CREATE TABLE PRODUCT_IMAGES
(
    id                  BIGSERIAL PRIMARY KEY,
    url                 TEXT   NOT NULL,
    position            INT    NOT NULL,
    product_variants_id BIGINT NOT NULL,
    CONSTRAINT FK_PRODUCT_IMAGES_VARIANT FOREIGN KEY (product_variants_id) REFERENCES PRODUCT_VARIANTS (id) ON DELETE CASCADE,
    CONSTRAINT UQ_PRODUCT_IMAGES_VARIANT_POSITION UNIQUE (product_variants_id, position),
    CONSTRAINT CK_PRODUCT_IMAGES_URL_NOT_EMPTY CHECK (btrim(url) <> ''),
    CONSTRAINT CK_PRODUCT_IMAGES_POSITION_POSITIVE CHECK (position > 0)
);

CREATE TABLE CATEGORIES
(
    id        BIGSERIAL PRIMARY KEY,
    name      VARCHAR(63) NOT NULL,
    parent_id BIGINT,
    CONSTRAINT FK_CATEGORIES_PARENT FOREIGN KEY (parent_id) REFERENCES CATEGORIES (id) ON DELETE SET NULL,
    CONSTRAINT UQ_CATEGORIES_PARENT_NAME UNIQUE (parent_id, name),
    CONSTRAINT CK_CATEGORIES_NAME_NOT_EMPTY CHECK (btrim(name) <> ''),
    CONSTRAINT CK_CATEGORIES_NOT_OWN_PARENT CHECK (parent_id IS NULL OR parent_id <> id)
);


--Junction tables have no meaning without either foreign keys
CREATE TABLE PRODUCT_CATEGORIES
(
    product_id  BIGINT NOT NULL,
    category_id BIGINT NOT NULL,
    PRIMARY KEY (product_id, category_id),
    CONSTRAINT FK_PRODUCT_CATEGORIES_PRODUCT FOREIGN KEY (product_id) REFERENCES PRODUCTS (id) ON DELETE CASCADE,
    CONSTRAINT FK_PRODUCT_CATEGORIES_CATEGORY FOREIGN KEY (category_id) REFERENCES CATEGORIES (id) ON DELETE CASCADE
);

CREATE TABLE ATTRIBUTES
(
    id                   BIGSERIAL PRIMARY KEY,
    name                 VARCHAR(63) NOT NULL,
    data_type            VARCHAR(50) NOT NULL,
    unit                 VARCHAR(50),
    is_variant_attribute BOOLEAN     NOT NULL DEFAULT FALSE,
    CONSTRAINT UQ_ATTRIBUTES_NAME UNIQUE (name),
    CONSTRAINT CK_ATTRIBUTES_NAME_NOT_EMPTY CHECK (btrim(name) <> ''),
    CONSTRAINT CK_ATTRIBUTES_DATA_TYPE_NOT_EMPTY CHECK (btrim(data_type) <> '')
);

CREATE TABLE ATTRIBUTE_VALUES
(
    id           BIGSERIAL PRIMARY KEY,
    attribute_id BIGINT      NOT NULL,
    value        VARCHAR(63) NOT NULL,
    CONSTRAINT FK_ATTRIBUTE_VALUES_ATTRIBUTE FOREIGN KEY (attribute_id) REFERENCES ATTRIBUTES (id) ON DELETE RESTRICT,
    CONSTRAINT UQ_ATTRIBUTE_VALUES_ATTRIBUTE_VALUE UNIQUE (attribute_id, value),
    CONSTRAINT UQ_ATTRIBUTE_VALUES_ATTRIBUTE_ID_ID UNIQUE (attribute_id, id),
    CONSTRAINT CK_ATTRIBUTE_VALUES_VALUE_NOT_EMPTY CHECK (btrim(value) <> '')
);


--Deleting a variant should delete its attribute assignments
--But deleting an attribute definition that is already used should be blocked
CREATE TABLE VARIANT_ATTRIBUTES
(
    product_variant_id BIGINT NOT NULL,
    attribute_value_id BIGINT NOT NULL,
    attribute_id       BIGINT NOT NULL,
    PRIMARY KEY (product_variant_id, attribute_value_id, attribute_id),
    CONSTRAINT FK_VARIANT_ATTRIBUTES_VARIANT FOREIGN KEY (product_variant_id) REFERENCES PRODUCT_VARIANTS (id) ON DELETE CASCADE,
    CONSTRAINT FK_VARIANT_ATTRIBUTES_ATTRIBUTE FOREIGN KEY (attribute_id) REFERENCES ATTRIBUTES (id) ON DELETE RESTRICT,
    CONSTRAINT FK_VARIANT_ATTRIBUTES_ATTRIBUTE_VALUE FOREIGN KEY (attribute_id, attribute_value_id) REFERENCES ATTRIBUTE_VALUES (attribute_id, id) ON DELETE RESTRICT
);

CREATE TABLE WAREHOUSES
(
    id      BIGSERIAL PRIMARY KEY,
    name    VARCHAR(63) NOT NULL,
    address TEXT,
    city    VARCHAR(63) NOT NULL,
    country VARCHAR(63) NOT NULL,
    CONSTRAINT UQ_WAREHOUSES_NAME UNIQUE (name),
    CONSTRAINT CK_WAREHOUSES_NAME_NOT_EMPTY CHECK (btrim(name) <> ''),
    CONSTRAINT CK_WAREHOUSES_CITY_NOT_EMPTY CHECK (btrim(city) <> ''),
    CONSTRAINT CK_WAREHOUSES_COUNTRY_NOT_EMPTY CHECK (btrim(country) <> '')
);

CREATE TABLE SECTIONS
(
    id           BIGSERIAL PRIMARY KEY,
    warehouse_id BIGINT      NOT NULL,
    name         VARCHAR(63) NOT NULL,
    description  TEXT,
    CONSTRAINT FK_SECTIONS_WAREHOUSE FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (id) ON DELETE RESTRICT,
    CONSTRAINT UQ_SECTIONS_WAREHOUSE_NAME UNIQUE (warehouse_id, name),
    CONSTRAINT CK_SECTIONS_NAME_NOT_EMPTY CHECK (btrim(name) <> '')
);

CREATE TABLE LOCATIONS
(
    id            BIGSERIAL PRIMARY KEY,
    section_id    BIGINT      NOT NULL,
    row_number    INT         NOT NULL,
    column_number INT         NOT NULL,
    level_number  INT         NOT NULL,
    location_code VARCHAR(63) NOT NULL,
    CONSTRAINT FK_LOCATIONS_SECTION FOREIGN KEY (section_id) REFERENCES SECTIONS (id) ON DELETE RESTRICT,
    CONSTRAINT UQ_LOCATIONS_CODE UNIQUE (location_code),
    CONSTRAINT UQ_LOCATIONS_POSITION UNIQUE (section_id, row_number, column_number, level_number),
    CONSTRAINT CK_LOCATIONS_ROW_POSITIVE CHECK (row_number > 0),
    CONSTRAINT CK_LOCATIONS_COLUMN_POSITIVE CHECK (column_number > 0),
    CONSTRAINT CK_LOCATIONS_LEVEL_POSITIVE CHECK (level_number > 0),
    CONSTRAINT CK_LOCATIONS_CODE_NOT_EMPTY CHECK (btrim(location_code) <> '')
);

CREATE TABLE BINS
(
    id          BIGSERIAL PRIMARY KEY,
    location_id BIGINT      NOT NULL,
    bin_code    VARCHAR(63) NOT NULL,
    capacity    INT         NOT NULL,
    CONSTRAINT FK_BINS_LOCATION FOREIGN KEY (location_id) REFERENCES LOCATIONS (id) ON DELETE RESTRICT,
    CONSTRAINT UQ_BINS_CODE UNIQUE (bin_code),
    CONSTRAINT UQ_BINS_LOCATION UNIQUE (location_id, bin_code),
    CONSTRAINT CK_BINS_CODE_NOT_EMPTY CHECK (btrim(bin_code) <> ''),
    CONSTRAINT CK_BINS_CAPACITY_POSITIVE CHECK (capacity > 0)
);

CREATE TABLE INVENTORY
(
    id                 BIGSERIAL PRIMARY KEY,
    product_variant_id BIGINT    NOT NULL,
    bin_id             BIGINT    NOT NULL,
    quantity           INT       NOT NULL DEFAULT 0,
    reserved_quantity  INT       NOT NULL DEFAULT 0,
    updated_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status             TEXT      NOT NULL,
    CONSTRAINT FK_INVENTORY_VARIANT FOREIGN KEY (product_variant_id) REFERENCES PRODUCT_VARIANTS (id) ON DELETE RESTRICT,
    CONSTRAINT FK_INVENTORY_BIN FOREIGN KEY (bin_id) REFERENCES BINS (id) ON DELETE RESTRICT,
    CONSTRAINT UQ_INVENTORY_VARIANT_BIN UNIQUE (product_variant_id, bin_id),
    CONSTRAINT CK_INVENTORY_QUANTITY_NONNEGATIVE CHECK (quantity >= 0),
    CONSTRAINT CK_INVENTORY_RESERVED_NONNEGATIVE CHECK (reserved_quantity >= 0),
    CONSTRAINT CK_INVENTORY_RESERVED_WITHIN_QUANTITY CHECK (reserved_quantity <= quantity),
    CONSTRAINT CK_INVENTORY_STATUS_NOT_EMPTY CHECK (btrim(status) <> '')
);



CREATE TABLE EMPLOYEES
(
    id                BIGSERIAL PRIMARY KEY,
    employee_number   VARCHAR(63) NOT NULL,
    first_name        VARCHAR(63) NOT NULL,
    last_name         VARCHAR(63) NOT NULL,
    email             VARCHAR(63) NOT NULL,
    phone             VARCHAR(63),
    job_title         VARCHAR(63) NOT NULL,
    employment_status VARCHAR(63) NOT NULL,
    hired_at          TIMESTAMP   NOT NULL,
    terminated_at     TIMESTAMP,
    manager_id        BIGINT,
    created_at        TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_EMPLOYEES_MANAGER FOREIGN KEY (manager_id) REFERENCES EMPLOYEES (id) ON DELETE SET NULL,
    CONSTRAINT UQ_EMPLOYEES_EMPLOYEE_NUMBER UNIQUE (employee_number),
    CONSTRAINT UQ_EMPLOYEES_EMAIL UNIQUE (email),
    CONSTRAINT CK_EMPLOYEES_EMPLOYEE_NUMBER_NOT_EMPTY CHECK (btrim(employee_number) <> ''),
    CONSTRAINT CK_EMPLOYEES_FIRST_NAME_NOT_EMPTY CHECK (btrim(first_name) <> ''),
    CONSTRAINT CK_EMPLOYEES_LAST_NAME_NOT_EMPTY CHECK (btrim(last_name) <> ''),
    CONSTRAINT CK_EMPLOYEES_EMAIL_FORMAT CHECK (email ~* '^[^@[:space:]]+@[^@[:space:]]+\.[^@[:space:]]+$'
        ),
    CONSTRAINT CK_EMPLOYEES_JOB_TITLE_NOT_EMPTY CHECK (btrim(job_title) <> ''),
    CONSTRAINT CK_EMPLOYEES_STATUS_NOT_EMPTY CHECK (btrim(employment_status) <> ''),
    CONSTRAINT CK_EMPLOYEES_TERM_AFTER_HIRE CHECK (terminated_at IS NULL OR terminated_at >= hired_at)
);

CREATE TABLE EMPLOYEE_WAREHOUSE_ASSIGNMENTS
(
    id           BIGSERIAL PRIMARY KEY,
    employee_id  BIGINT    NOT NULL,
    warehouse_id BIGINT    NOT NULL,
    start_date   DATE      NOT NULL,
    end_date     DATE,
    is_primary   BOOLEAN   NOT NULL DEFAULT FALSE,
    notes        TEXT,
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT FK_EMPLOYEE_ASSIGNMENTS_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES EMPLOYEES (id) ON DELETE RESTRICT,
    CONSTRAINT FK_EMPLOYEE_ASSIGNMENTS_WAREHOUSE FOREIGN KEY (warehouse_id) REFERENCES WAREHOUSES (id) ON DELETE RESTRICT,
    CONSTRAINT UQ_EMPLOYEE_ASSIGNMENTS_UNIQUE_PERIOD UNIQUE (employee_id, warehouse_id, start_date),
    CONSTRAINT CK_EMPLOYEE_ASSIGNMENTS_DATE_ORDER CHECK (end_date IS NULL OR end_date >= start_date)
);

CREATE TABLE TRANSACTION_TYPES
(
    code        VARCHAR(50) PRIMARY KEY,
    description TEXT NOT NULL,
    CONSTRAINT CK_TRANSACTION_TYPES_CODE_NOT_EMPTY CHECK (btrim(code) <> ''),
    CONSTRAINT CK_TRANSACTION_TYPES_DESCRIPTION_NOT_EMPTY CHECK (btrim(description) <> '')
);

CREATE TABLE INVENTORY_TRANSACTIONS
(
    id                  BIGSERIAL PRIMARY KEY,
    transaction_type    VARCHAR(50) NOT NULL,
    created_by_employee BIGINT      NOT NULL,
    notes               TEXT,
    status              VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    created_at          TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_updated_by     BIGINT,
    accepted_by         BIGINT,
    packed_by           BIGINT,
    CONSTRAINT FK_INVENTORY_TRANSACTIONS_TYPE FOREIGN KEY (transaction_type) REFERENCES TRANSACTION_TYPES (code) ON DELETE RESTRICT,
    CONSTRAINT FK_INVENTORY_TRANSACTIONS_CREATED_BY FOREIGN KEY (created_by_employee) REFERENCES EMPLOYEES (id) ON DELETE RESTRICT,
    CONSTRAINT FK_INVENTORY_TRANSACTIONS_LAST_UPDATED_BY FOREIGN KEY (last_updated_by) REFERENCES EMPLOYEES (id) ON DELETE RESTRICT,
    CONSTRAINT FK_INVENTORY_TRANSACTIONS_ACCEPTED_BY FOREIGN KEY (accepted_by) REFERENCES EMPLOYEES (id) ON DELETE RESTRICT,
    CONSTRAINT FK_INVENTORY_TRANSACTIONS_PACKED_BY FOREIGN KEY (packed_by) REFERENCES EMPLOYEES (id) ON DELETE RESTRICT,
    CONSTRAINT CK_INVENTORY_TRANSACTIONS_STATUS CHECK (status IN ('PENDING', 'APPROVED', 'CANCELLED'))
);

CREATE TABLE INVENTORY_MOVEMENTS
(
    id                        BIGSERIAL PRIMARY KEY,
    product_variant_id        BIGINT    NOT NULL,
    from_bin_id               BIGINT,
    to_bin_id                 BIGINT,
    quantity                  INT       NOT NULL,
    created_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    inventory_transactions_id BIGINT    NOT NULL,
    CONSTRAINT FK_INVENTORY_MOVEMENTS_VARIANT FOREIGN KEY (product_variant_id) REFERENCES PRODUCT_VARIANTS (id) ON DELETE RESTRICT,
    CONSTRAINT FK_INVENTORY_MOVEMENTS_FROM_BIN FOREIGN KEY (from_bin_id) REFERENCES BINS (id) ON DELETE RESTRICT,
    CONSTRAINT FK_INVENTORY_MOVEMENTS_TO_BIN FOREIGN KEY (to_bin_id) REFERENCES BINS (id) ON DELETE RESTRICT,
    CONSTRAINT FK_INVENTORY_MOVEMENTS_TRANSACTION FOREIGN KEY (inventory_transactions_id) REFERENCES INVENTORY_TRANSACTIONS (id) ON DELETE RESTRICT,
    CONSTRAINT CK_INVENTORY_MOVEMENTS_QUANTITY_POSITIVE CHECK (quantity > 0),
    CONSTRAINT CK_INVENTORY_MOVEMENTS_AT_LEAST_ONE_BIN CHECK (from_bin_id IS NOT NULL OR to_bin_id IS NOT NULL),
    CONSTRAINT CK_INVENTORY_MOVEMENTS_DIFFERENT_BINS CHECK (from_bin_id IS NULL OR to_bin_id IS NULL OR from_bin_id <> to_bin_id)
);

CREATE TABLE DELIVERY_TRANSACTIONS
(
    delivery_note             TEXT,
    supplier_company          TEXT   NOT NULL,
    inventory_transactions_id BIGINT NOT NULL PRIMARY KEY,
    CONSTRAINT FK_DELIVERY_TRANSACTIONS_TRANSACTION FOREIGN KEY (inventory_transactions_id) REFERENCES INVENTORY_TRANSACTIONS (id) ON DELETE RESTRICT,
    CONSTRAINT CK_DELIVERY_TRANSACTIONS_SUPPLIER_NOT_EMPTY CHECK (btrim(supplier_company) <> '')
);

CREATE TABLE SHIPMENT_TRANSACTIONS
(
    shipment_number           BIGINT NOT NULL,
    destination_adress        TEXT   NOT NULL,
    inventory_transactions_id BIGINT NOT NULL PRIMARY KEY,
    CONSTRAINT FK_SHIPMENT_TRANSACTIONS_TRANSACTION FOREIGN KEY (inventory_transactions_id) REFERENCES INVENTORY_TRANSACTIONS (id) ON DELETE RESTRICT,
    CONSTRAINT CK_SHIPMENT_TRANSACTIONS_SHIPMENT_NUMBER_POSITIVE CHECK (shipment_number > 0),
    CONSTRAINT CK_SHIPMENT_TRANSACTIONS_DESTINATION_NOT_EMPTY CHECK (btrim(destination_adress) <> '')
);

CREATE TABLE ROLES
(
    id          SERIAL PRIMARY KEY,
    name        TEXT      NOT NULL,
    description TEXT,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT UQ_ROLES_NAME UNIQUE (name),
    CONSTRAINT CK_ROLES_NAME_NOT_EMPTY CHECK (btrim(name) <> '')
);

CREATE TABLE PERMISSIONS
(
    id          SERIAL PRIMARY KEY,
    name        TEXT      NOT NULL,
    description TEXT,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT UQ_PERMISSIONS_NAME UNIQUE (name),
    CONSTRAINT CK_PERMISSIONS_NAME_NOT_EMPTY CHECK (btrim(name) <> '')
);


--Junction tables have no meaning without either foreign keys
CREATE TABLE ROLES_EMPLOYEES
(
    roles_id     INT    NOT NULL,
    employees_id BIGINT NOT NULL,
    PRIMARY KEY (roles_id, employees_id),
    CONSTRAINT FK_ROLES_EMPLOYEES_ROLE FOREIGN KEY (roles_id) REFERENCES ROLES (id) ON DELETE CASCADE,
    CONSTRAINT FK_ROLES_EMPLOYEES_EMPLOYEE FOREIGN KEY (employees_id) REFERENCES EMPLOYEES (id) ON DELETE CASCADE
);


--Junction tables have no meaning without either foreign keys
CREATE TABLE PERMISSIONS_ROLES
(
    permissions_id INT NOT NULL,
    roles_id       INT NOT NULL,
    PRIMARY KEY (permissions_id, roles_id),
    CONSTRAINT FK_PERMISSIONS_ROLES_PERMISSION FOREIGN KEY (permissions_id) REFERENCES PERMISSIONS (id) ON DELETE CASCADE,
    CONSTRAINT FK_PERMISSIONS_ROLES_ROLE FOREIGN KEY (roles_id) REFERENCES ROLES (id) ON DELETE CASCADE
);