-- ============================================================
-- Migration 007: Trade Lifecycle Integrity Constraints
-- Date: 2026-02-15
-- Purpose:
-- 1) Normalize trade_id null handling policy
-- 2) Add non-blocking lifecycle constraints (NOT VALID)
-- 3) Add profile_id + trade_id indexes for deterministic traceability
-- ============================================================

-- Null handling policy:
-- - trade_id is allowed to be NULL for legacy/manual rows.
-- - blank/whitespace values are normalized to NULL.

UPDATE orders
SET trade_id = NULL
WHERE trade_id IS NOT NULL
  AND btrim(trade_id) = '';

UPDATE trade_history
SET trade_id = NULL
WHERE trade_id IS NOT NULL
  AND btrim(trade_id) = '';

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'chk_orders_action_lifecycle'
          AND conrelid = 'orders'::regclass
    ) THEN
        ALTER TABLE orders
            ADD CONSTRAINT chk_orders_action_lifecycle
            CHECK (action IS NULL OR action IN ('ENTRY', 'EXIT'))
            NOT VALID;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'chk_orders_trade_id_not_blank'
          AND conrelid = 'orders'::regclass
    ) THEN
        ALTER TABLE orders
            ADD CONSTRAINT chk_orders_trade_id_not_blank
            CHECK (trade_id IS NULL OR btrim(trade_id) <> '')
            NOT VALID;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'chk_trade_history_trade_id_not_blank'
          AND conrelid = 'trade_history'::regclass
    ) THEN
        ALTER TABLE trade_history
            ADD CONSTRAINT chk_trade_history_trade_id_not_blank
            CHECK (trade_id IS NULL OR btrim(trade_id) <> '')
            NOT VALID;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'chk_orders_trade_id_format'
          AND conrelid = 'orders'::regclass
    ) THEN
        ALTER TABLE orders
            ADD CONSTRAINT chk_orders_trade_id_format
            CHECK (trade_id IS NULL OR trade_id LIKE 'TRD-%')
            NOT VALID;
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conname = 'chk_trade_history_trade_id_format'
          AND conrelid = 'trade_history'::regclass
    ) THEN
        ALTER TABLE trade_history
            ADD CONSTRAINT chk_trade_history_trade_id_format
            CHECK (trade_id IS NULL OR trade_id LIKE 'TRD-%')
            NOT VALID;
    END IF;
END $$;

-- Lifecycle query indexes.
CREATE INDEX IF NOT EXISTS idx_orders_profile_trade_id_created
    ON orders (profile_id, trade_id, created_at DESC)
    WHERE trade_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_orders_profile_trade_id_action_created
    ON orders (profile_id, trade_id, action, created_at DESC)
    WHERE trade_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_orders_profile_symbol_status_created
    ON orders (profile_id, symbol, status, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_trade_history_profile_trade_id_created
    ON trade_history (profile_id, trade_id, created_at DESC)
    WHERE trade_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_trade_history_profile_symbol_created
    ON trade_history (profile_id, symbol, created_at DESC);

-- Post-migration tighten step (run after backfill + reconciliation):
--   ALTER TABLE orders VALIDATE CONSTRAINT chk_orders_action_lifecycle;
--   ALTER TABLE orders VALIDATE CONSTRAINT chk_orders_trade_id_not_blank;
--   ALTER TABLE orders VALIDATE CONSTRAINT chk_orders_trade_id_format;
--   ALTER TABLE trade_history VALIDATE CONSTRAINT chk_trade_history_trade_id_not_blank;
--   ALTER TABLE trade_history VALIDATE CONSTRAINT chk_trade_history_trade_id_format;
