-- WebSync V2 schema migration (faster / lower-lock variant).
-- This script avoids:
-- 1) full-table rewrite from ALTER COLUMN TYPE ... USING
-- 2) unconditional UPDATE of all rows
-- 3) regular CREATE INDEX on hot table
--
-- Run index script separately:
--   sonex.products.alter.websync_v2.indexes.concurrent.sql

ALTER TABLE IF EXISTS sonex.products
    DROP COLUMN IF EXISTS website_store_stock,
    DROP COLUMN IF EXISTS website_store_only,
    DROP COLUMN IF EXISTS website_online_stock,
    DROP COLUMN IF EXISTS website_online_only,
    DROP COLUMN IF EXISTS website_sold_out,
    DROP COLUMN IF EXISTS website_temporarily_not_sellable,
    DROP COLUMN IF EXISTS website_coming_soon_online;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'sonex'
          AND table_name = 'products'
          AND column_name = 'website_trending'
    ) THEN
        EXECUTE 'ALTER TABLE sonex.products RENAME COLUMN website_trending TO is_trending';
    END IF;
END $$;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'sonex'
          AND table_name = 'products'
          AND column_name = 'website_nieuw'
    ) THEN
        EXECUTE 'ALTER TABLE sonex.products RENAME COLUMN website_nieuw TO is_nieuw';
    END IF;
END $$;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'sonex'
          AND table_name = 'products'
          AND column_name = 'website_acties'
    ) THEN
        EXECUTE 'ALTER TABLE sonex.products RENAME COLUMN website_acties TO is_acties';
    END IF;
END $$;

-- Convert online_only to boolean without ALTER COLUMN TYPE rewrite.
DO $$
DECLARE
    v_online_only_type text;
BEGIN
    SELECT c.data_type
    INTO v_online_only_type
    FROM information_schema.columns c
    WHERE c.table_schema = 'sonex'
      AND c.table_name = 'products'
      AND c.column_name = 'online_only'
    LIMIT 1;

    IF v_online_only_type IS NULL THEN
        EXECUTE 'ALTER TABLE sonex.products ADD COLUMN online_only boolean';
    ELSIF v_online_only_type <> 'boolean' THEN
        IF EXISTS (
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = 'sonex'
              AND table_name = 'products'
              AND column_name = 'online_only_legacy'
        ) THEN
            EXECUTE 'ALTER TABLE sonex.products DROP COLUMN online_only_legacy';
        END IF;

        EXECUTE 'ALTER TABLE sonex.products RENAME COLUMN online_only TO online_only_legacy';
        EXECUTE 'ALTER TABLE sonex.products ADD COLUMN online_only boolean';

        UPDATE sonex.products
        SET online_only =
            CASE
                WHEN online_only_legacy IS NULL THEN false
                WHEN lower(trim(online_only_legacy::text)) IN ('ja', 'true', 't', '1', 'yes', 'y') THEN true
                ELSE false
            END
        WHERE online_only IS NULL;

        EXECUTE 'ALTER TABLE sonex.products DROP COLUMN online_only_legacy';
    END IF;
END $$;

ALTER TABLE IF EXISTS sonex.products
    ADD COLUMN IF NOT EXISTS can_order boolean;

-- Update only rows that actually need normalization.
UPDATE sonex.products
SET
    online_only = COALESCE(online_only, false),
    can_order = COALESCE(can_order, false)
WHERE online_only IS NULL
   OR can_order IS NULL;

ALTER TABLE IF EXISTS sonex.products
    ALTER COLUMN online_only SET DEFAULT false,
    ALTER COLUMN can_order SET DEFAULT false;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'products_online_only_nn'
          AND conrelid = 'sonex.products'::regclass
    ) THEN
        EXECUTE 'ALTER TABLE sonex.products ADD CONSTRAINT products_online_only_nn CHECK (online_only IS NOT NULL) NOT VALID';
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'products_can_order_nn'
          AND conrelid = 'sonex.products'::regclass
    ) THEN
        EXECUTE 'ALTER TABLE sonex.products ADD CONSTRAINT products_can_order_nn CHECK (can_order IS NOT NULL) NOT VALID';
    END IF;
END $$;

ALTER TABLE sonex.products VALIDATE CONSTRAINT products_online_only_nn;
ALTER TABLE sonex.products VALIDATE CONSTRAINT products_can_order_nn;

ALTER TABLE IF EXISTS sonex.products
    ALTER COLUMN online_only SET NOT NULL,
    ALTER COLUMN can_order SET NOT NULL;

DROP INDEX IF EXISTS sonex.ix_products_website_trending;
DROP INDEX IF EXISTS sonex.ix_products_website_nieuw;
DROP INDEX IF EXISTS sonex.ix_products_website_acties;
