DO $$
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'sonex'
          AND table_name = 'products_images'
          AND column_name = 'relative_path'
    )
    AND NOT EXISTS
    (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'sonex'
          AND table_name = 'products_images'
          AND column_name = 'source_relative_path'
    )
    THEN
        ALTER TABLE sonex.products_images
            RENAME COLUMN relative_path TO source_relative_path;
    END IF;
END $$;

DO $$
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'sonex'
          AND table_name = 'products_images'
          AND column_name = 'file_name'
    )
    AND NOT EXISTS
    (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'sonex'
          AND table_name = 'products_images'
          AND column_name = 'source_file_name'
    )
    THEN
        ALTER TABLE sonex.products_images
            RENAME COLUMN file_name TO source_file_name;
    END IF;
END $$;

ALTER TABLE sonex.products_images
    ADD COLUMN IF NOT EXISTS image_no INTEGER;

ALTER TABLE sonex.products_images
    ADD COLUMN IF NOT EXISTS source_stem TEXT;

ALTER TABLE sonex.products_images
    ADD COLUMN IF NOT EXISTS source_extension TEXT;

UPDATE sonex.products_images
SET source_stem = regexp_replace(source_file_name, '\.[^.]+$', ''),
    source_extension = lower(COALESCE(substring(source_file_name FROM '\.[^.]+$'), ''))
WHERE source_stem IS NULL
   OR source_stem = ''
   OR source_extension IS NULL
   OR source_extension = '';

WITH ranked AS
(
    SELECT
        id,
        row_number() OVER
        (
            PARTITION BY article_number
            ORDER BY
                CASE
                    WHEN lower(source_stem) = lower(article_number) THEN 0
                    ELSE 1
                END,
                lower(source_stem),
                lower(source_file_name),
                lower(source_relative_path)
        ) - 1 AS new_image_no
    FROM sonex.products_images
)
UPDATE sonex.products_images AS target
SET image_no = ranked.new_image_no
FROM ranked
WHERE ranked.id = target.id
  AND target.image_no IS DISTINCT FROM ranked.new_image_no;

DO $$
BEGIN
    IF NOT EXISTS
    (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'sonex'
          AND table_name = 'products_images'
          AND column_name = 'target_file_name'
    )
    THEN
        ALTER TABLE sonex.products_images
            ADD COLUMN target_file_name TEXT GENERATED ALWAYS AS
            (
                article_number || '_' || image_no::text || '.jpg'
            ) STORED;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS
    (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'sonex'
          AND table_name = 'products_images'
          AND column_name = 'target_relative_path'
    )
    THEN
        ALTER TABLE sonex.products_images
            ADD COLUMN target_relative_path TEXT GENERATED ALWAYS AS
            (
                article_number || '/' || article_number || '_' || image_no::text || '.jpg'
            ) STORED;
    END IF;
END $$;

ALTER TABLE sonex.products_images
    ALTER COLUMN article_number SET NOT NULL;

ALTER TABLE sonex.products_images
    ALTER COLUMN image_no SET NOT NULL;

ALTER TABLE sonex.products_images
    ALTER COLUMN source_relative_path SET NOT NULL;

ALTER TABLE sonex.products_images
    ALTER COLUMN source_file_name SET NOT NULL;

ALTER TABLE sonex.products_images
    ALTER COLUMN source_stem SET NOT NULL;

ALTER TABLE sonex.products_images
    ALTER COLUMN source_extension SET NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS
    (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'ck_products_images_article_number_not_blank'
          AND conrelid = 'sonex.products_images'::regclass
    )
    THEN
        ALTER TABLE sonex.products_images
            ADD CONSTRAINT ck_products_images_article_number_not_blank
                CHECK (btrim(article_number) <> '');
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS
    (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'ck_products_images_image_no_non_negative'
          AND conrelid = 'sonex.products_images'::regclass
    )
    THEN
        ALTER TABLE sonex.products_images
            ADD CONSTRAINT ck_products_images_image_no_non_negative
                CHECK (image_no >= 0);
    END IF;
END $$;

DROP INDEX IF EXISTS sonex.ux_products_images_relative_path;
DROP INDEX IF EXISTS sonex.ix_products_images_article_number;
DROP INDEX IF EXISTS sonex.ix_products_images_article_number_file_name;

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));
