-- Phase 1 compatibility migration for category tree decommission.
-- Keep both legacy and _v2 object families, but make legacy objects mirror _v2 content.

-- Ensure legacy categories has support column used by migration scripts.
ALTER TABLE categories ADD COLUMN IF NOT EXISTS is_v2 BOOLEAN NOT NULL DEFAULT FALSE;

-- Sync nested-set values from categories_v2_lft_rgt into categories.
INSERT INTO categories (id, name, lft, rgt, is_v2)
SELECT
    v2.id,
    COALESCE(v2.name, 'unknown'),
    v2.lft,
    v2.rgt,
    TRUE
FROM categories_v2_lft_rgt v2
ON CONFLICT (id) DO UPDATE
SET
    name = EXCLUDED.name,
    lft = EXCLUDED.lft,
    rgt = EXCLUDED.rgt,
    is_v2 = TRUE;

-- Remove legacy rows that have no counterpart in v2.
-- Must delete from dependent tables first to satisfy foreign key constraints.
DELETE FROM product_category
 WHERE NOT EXISTS (
   SELECT 1 FROM categories_v2_lft_rgt
    WHERE categories_v2_lft_rgt.id = product_category.category_id
 );

DELETE FROM category_keyword
 WHERE NOT EXISTS (
   SELECT 1 FROM categories_v2_lft_rgt
    WHERE categories_v2_lft_rgt.id = category_keyword.category_id
 );

DELETE FROM categorydata
 WHERE NOT EXISTS (
   SELECT 1 FROM categories_v2_lft_rgt
    WHERE categories_v2_lft_rgt.id = categorydata.category_id
 );

DELETE FROM category_images
 WHERE NOT EXISTS (
   SELECT 1 FROM categories_v2_lft_rgt
    WHERE categories_v2_lft_rgt.id = category_images.category_id
 );

DELETE FROM category_metadata
 WHERE NOT EXISTS (
   SELECT 1 FROM categories_v2_lft_rgt
    WHERE categories_v2_lft_rgt.id = category_metadata.category_id
 );

DELETE FROM category_texts
 WHERE NOT EXISTS (
   SELECT 1 FROM categories_v2_lft_rgt
    WHERE categories_v2_lft_rgt.id = category_texts.category_id
 );

DELETE FROM categories
 WHERE NOT EXISTS (
   SELECT 1 FROM categories_v2_lft_rgt
    WHERE categories_v2_lft_rgt.id = categories.id
 );

-- Keep sequence in sync after upsert.
SELECT setval('categories_id_seq', (SELECT COALESCE(MAX(id), 1) FROM categories), TRUE);

-- Sync category_texts from category_texts_v2 by business key (category_id, locale_id).
-- We intentionally exclude id so legacy table keeps its own sequence semantics.
-- Use delete+insert to avoid depending on a specific unique index definition.
DO $$
DECLARE
        insert_columns TEXT;
BEGIN
    SELECT string_agg(format('%I', c.column_name), ', ' ORDER BY c.ordinal_position)
    INTO insert_columns
    FROM information_schema.columns c
    WHERE c.table_schema = 'public'
      AND c.table_name = 'category_texts'
      AND c.column_name <> 'id';

        DELETE FROM category_texts t
        USING category_texts_v2 v2
        WHERE t.category_id = v2.category_id
            AND t.locale_id = v2.locale_id;

    EXECUTE format(
                'INSERT INTO category_texts (%1$s) SELECT %1$s FROM category_texts_v2 WHERE category_id IN (SELECT id FROM categories)',
                insert_columns
    );
END $$;

-- Remove legacy category_texts rows that have no counterpart in v2.
DELETE FROM category_texts
 WHERE NOT EXISTS (
   SELECT 1 FROM category_texts_v2
    WHERE category_texts_v2.category_id = category_texts.category_id
      AND category_texts_v2.locale_id = category_texts.locale_id
 );

-- Keep existing non-v2 view definitions as-is.
-- We only refresh legacy materialized views so they reflect synced base table data.
REFRESH MATERIALIZED VIEW v_categorytree;
REFRESH MATERIALIZED VIEW v_esales_categorytree_i18n;

-- Keep optional non-v2 materialized helper in sync if present.
REFRESH MATERIALIZED VIEW mv_category_locale_safexmlpath;

-- Replace legacy hidden-category dependent joins with safe ancestor join logic.
-- This is applied deterministically for the affected non-v2 views.
CREATE OR REPLACE VIEW v_product_categories AS
SELECT DISTINCT tree.id,
                tree.locale_id,
                getsafexmlpath(tree.path::character varying, 2) AS path,
                COALESCE(category_texts.display_name::varchar(255), category_texts.name) AS name,
                product_category.product_id
FROM product_category
JOIN categories ON product_category.category_id = categories.id
JOIN v_esales_categorytree_i18n_unmaterialized tree ON (
    ((tree.lft < categories.lft AND tree.rgt > categories.rgt) OR categories.id = tree.id)
    AND tree.id <> 1
)
JOIN category_texts ON tree.id = category_texts.category_id AND tree.locale_id = category_texts.locale_id;

CREATE OR REPLACE VIEW v_analytics_product_categories AS
SELECT DISTINCT tree.id AS category_id,
                getsafexmlpath(tree.path::character varying, 2) AS category_path,
                category_texts.name AS category_name,
                product_category.product_id,
                GREATEST(product_category.inserted, category_texts.updated) AS last_updated
FROM product_category
JOIN categories ON product_category.category_id = categories.id
JOIN v_esales_categorytree_i18n tree ON (
    ((tree.lft < categories.lft AND tree.rgt > categories.rgt) OR categories.id = tree.id)
    AND tree.id <> 1
)
JOIN category_texts ON tree.id = category_texts.category_id AND tree.locale_id = category_texts.locale_id
WHERE tree.locale_id = 3;