-- ============================================================
-- Migration 019: Orders ON CONFLICT(order_id) Compatibility Fix
-- Date: 2026-02-22
-- Purpose:
--   Ensure Postgres can infer a unique index for:
--     ON CONFLICT (order_id)
--   by using a non-partial unique index on orders(order_id).
-- ============================================================

-- -----------------------------------------------------------------------------
-- Safety: if any duplicate non-null order_id rows still exist, deterministically
-- remap tail rows before creating the full unique index.
-- -----------------------------------------------------------------------------
DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM (
            SELECT order_id
            FROM orders
            WHERE order_id IS NOT NULL
            GROUP BY order_id
            HAVING COUNT(*) > 1
        ) dupes
    ) THEN
        WITH ranked AS (
            SELECT
                o.ctid AS row_ctid,
                o.id::text AS row_ref,
                o.order_id,
                row_number() OVER (
                    PARTITION BY o.order_id
                    ORDER BY o.created_at DESC NULLS LAST, o.id DESC
                ) AS rn
            FROM orders o
            WHERE o.order_id IS NOT NULL
        ),
        to_rewrite AS (
            SELECT
                row_ctid,
                order_id AS old_order_id,
                left(order_id, 180) || '__DUP019__' || substr(md5(row_ref || ':' || order_id), 1, 16) AS new_order_id
            FROM ranked
            WHERE rn > 1
        )
        UPDATE orders o
        SET order_id = r.new_order_id
        FROM to_rewrite r
        WHERE o.ctid = r.row_ctid
          AND o.order_id = r.old_order_id;
    END IF;
END;
$$;

-- -----------------------------------------------------------------------------
-- Replace partial index with full unique index so ON CONFLICT(order_id) resolves.
-- PostgreSQL unique indexes allow multiple NULL values, so legacy NULL order_id
-- rows remain valid.
-- -----------------------------------------------------------------------------
DROP INDEX IF EXISTS uq_orders_order_id;
CREATE UNIQUE INDEX IF NOT EXISTS uq_orders_order_id
    ON orders (order_id);

-- -----------------------------------------------------------------------------
-- Reversal (manual):
--   DROP INDEX IF EXISTS uq_orders_order_id;
--   CREATE UNIQUE INDEX IF NOT EXISTS uq_orders_order_id
--     ON orders (order_id)
--     WHERE order_id IS NOT NULL;
-- -----------------------------------------------------------------------------
