CREATE SCHEMA IF NOT EXISTS sonex;

CREATE TABLE IF NOT EXISTS sonex.products_images
(
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    article_number TEXT NOT NULL,
    image_no INTEGER NOT NULL,
    source_relative_path TEXT NOT NULL,
    source_file_name TEXT NOT NULL,
    source_stem TEXT NOT NULL,
    source_extension TEXT NOT NULL,
    target_file_name TEXT GENERATED ALWAYS AS
    (
        article_number || '_' || image_no::text || '.jpg'
    ) STORED,
    target_relative_path TEXT GENERATED ALWAYS AS
    (
        article_number || '/' || article_number || '_' || image_no::text || '.jpg'
    ) STORED,
    source_length BIGINT NOT NULL,
    source_last_write_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL
        DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'Europe/Amsterdam'),
    CONSTRAINT ck_products_images_article_number_not_blank
        CHECK (btrim(article_number) <> ''),
    CONSTRAINT ck_products_images_image_no_non_negative
        CHECK (image_no >= 0),
    CONSTRAINT ck_products_images_source_relative_path_not_blank
        CHECK (btrim(source_relative_path) <> ''),
    CONSTRAINT ck_products_images_source_file_name_not_blank
        CHECK (btrim(source_file_name) <> ''),
    CONSTRAINT ck_products_images_source_stem_not_blank
        CHECK (btrim(source_stem) <> ''),
    CONSTRAINT ck_products_images_source_extension_not_blank
        CHECK (btrim(source_extension) <> '')
);

CREATE UNIQUE INDEX IF NOT EXISTS ux_products_images_source_relative_path_ci
    ON sonex.products_images (lower(source_relative_path));

CREATE UNIQUE INDEX IF NOT EXISTS ux_products_images_article_image_no_ci
    ON sonex.products_images (lower(article_number), image_no);

CREATE UNIQUE INDEX IF NOT EXISTS ux_products_images_target_relative_path_ci
    ON sonex.products_images (lower(target_relative_path));

CREATE INDEX IF NOT EXISTS ix_products_images_article_number_ci
    ON sonex.products_images (lower(article_number));

CREATE INDEX IF NOT EXISTS ix_products_images_article_source_stem_ci
    ON sonex.products_images (lower(article_number), lower(source_stem));
