-- ============================================================
-- Migration 018: Orders order_id Uniqueness Repair
-- Date: 2026-02-22
-- Purpose:
--   1) Repair legacy duplicate orders.order_id rows without deleting data.
--   2) Create/ensure deterministic ON CONFLICT support for orders(order_id).
--   3) Keep full reversibility via audit trail.
-- ============================================================

-- -----------------------------------------------------------------------------
-- Duplicate-repair audit trail (non-destructive + reversible)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS orders_order_id_dedupe_audit (
    id bigserial PRIMARY KEY,
    migration_id text NOT NULL DEFAULT '018_orders_order_id_uniqueness_repair',
    occurred_at timestamptz NOT NULL DEFAULT now(),
    order_row_ref text NOT NULL,
    profile_id uuid,
    old_order_id text NOT NULL,
    new_order_id text NOT NULL,
    snapshot jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX IF NOT EXISTS idx_orders_order_id_dedupe_old
    ON orders_order_id_dedupe_audit (old_order_id, occurred_at DESC);

CREATE INDEX IF NOT EXISTS idx_orders_order_id_dedupe_new
    ON orders_order_id_dedupe_audit (new_order_id, occurred_at DESC);

CREATE INDEX IF NOT EXISTS idx_orders_order_id_dedupe_profile
    ON orders_order_id_dedupe_audit (profile_id, occurred_at DESC);

DO $$
DECLARE
    has_orders_table boolean;
    has_order_id boolean;
    has_id boolean;
    has_created_at boolean;
BEGIN
    SELECT EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = 'public'
          AND table_name = 'orders'
    ) INTO has_orders_table;

    IF NOT has_orders_table THEN
        RAISE NOTICE 'Skipping migration 018: orders table not found.';
        RETURN;
    END IF;

    SELECT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'orders'
          AND column_name = 'order_id'
    ) INTO has_order_id;

    IF NOT has_order_id THEN
        RAISE NOTICE 'Skipping migration 018: orders.order_id not found.';
        RETURN;
    END IF;

    SELECT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'orders'
          AND column_name = 'id'
    ) INTO has_id;

    SELECT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'orders'
          AND column_name = 'created_at'
    ) INTO has_created_at;

    IF has_id AND has_created_at THEN
        EXECUTE $sql$
            CREATE TEMP TABLE _orders_order_id_dupes ON COMMIT DROP AS
            WITH ranked AS (
                SELECT
                    o.ctid AS row_ctid,
                    o.id::text AS row_ref,
                    o.profile_id,
                    o.order_id,
                    row_to_json(o)::jsonb AS snapshot,
                    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
            )
            SELECT
                row_ctid,
                row_ref,
                profile_id,
                order_id AS old_order_id,
                left(order_id, 180) || '__DUP018__' || substr(md5(row_ref || ':' || order_id), 1, 16) AS new_order_id,
                snapshot
            FROM ranked
            WHERE rn > 1
        $sql$;
    ELSIF has_id THEN
        EXECUTE $sql$
            CREATE TEMP TABLE _orders_order_id_dupes ON COMMIT DROP AS
            WITH ranked AS (
                SELECT
                    o.ctid AS row_ctid,
                    o.id::text AS row_ref,
                    o.profile_id,
                    o.order_id,
                    row_to_json(o)::jsonb AS snapshot,
                    row_number() OVER (
                        PARTITION BY o.order_id
                        ORDER BY o.id DESC
                    ) AS rn
                FROM orders o
                WHERE o.order_id IS NOT NULL
            )
            SELECT
                row_ctid,
                row_ref,
                profile_id,
                order_id AS old_order_id,
                left(order_id, 180) || '__DUP018__' || substr(md5(row_ref || ':' || order_id), 1, 16) AS new_order_id,
                snapshot
            FROM ranked
            WHERE rn > 1
        $sql$;
    ELSE
        EXECUTE $sql$
            CREATE TEMP TABLE _orders_order_id_dupes ON COMMIT DROP AS
            WITH ranked AS (
                SELECT
                    o.ctid AS row_ctid,
                    o.ctid::text AS row_ref,
                    o.profile_id,
                    o.order_id,
                    row_to_json(o)::jsonb AS snapshot,
                    row_number() OVER (
                        PARTITION BY o.order_id
                        ORDER BY o.ctid DESC
                    ) AS rn
                FROM orders o
                WHERE o.order_id IS NOT NULL
            )
            SELECT
                row_ctid,
                row_ref,
                profile_id,
                order_id AS old_order_id,
                left(order_id, 180) || '__DUP018__' || substr(md5(row_ref || ':' || order_id), 1, 16) AS new_order_id,
                snapshot
            FROM ranked
            WHERE rn > 1
        $sql$;
    END IF;

    INSERT INTO orders_order_id_dedupe_audit (
        order_row_ref,
        profile_id,
        old_order_id,
        new_order_id,
        snapshot
    )
    SELECT
        d.row_ref,
        d.profile_id,
        d.old_order_id,
        d.new_order_id,
        d.snapshot
    FROM _orders_order_id_dupes d
    WHERE NOT EXISTS (
        SELECT 1
        FROM orders_order_id_dedupe_audit a
        WHERE a.order_row_ref = d.row_ref
          AND a.old_order_id = d.old_order_id
          AND a.new_order_id = d.new_order_id
    );

    UPDATE orders o
    SET order_id = d.new_order_id
    FROM _orders_order_id_dupes d
    WHERE o.ctid = d.row_ctid
      AND o.order_id = d.old_order_id;
END;
$$;

-- -----------------------------------------------------------------------------
-- Enforce idempotent upsert path for lifecycle/backfill inserts.
-- -----------------------------------------------------------------------------
CREATE UNIQUE INDEX IF NOT EXISTS uq_orders_order_id
    ON orders (order_id)
    WHERE order_id IS NOT NULL;

-- -----------------------------------------------------------------------------
-- Reversal note (manual):
--   1) Drop index:
--      DROP INDEX IF EXISTS uq_orders_order_id;
--   2) Restore remapped rows:
--      UPDATE orders o
--         SET order_id = a.old_order_id
--        FROM orders_order_id_dedupe_audit a
--       WHERE a.migration_id = '018_orders_order_id_uniqueness_repair'
--         AND o.order_id = a.new_order_id;
-- -----------------------------------------------------------------------------
