-- ============================================================================
-- Migration: Add symbol_id FK to trading.price_alerts
-- Date: 2026-02-03
-- Task: TASK-2026-02-03-ANALISIS-DDL-MODELADO / ST-1.3
-- Description: Ensures price_alerts has proper FK relationship to symbols
-- ============================================================================

-- Note: This migration is idempotent - safe to run multiple times
-- The DDL file (11-price_alerts.sql) already includes symbol_id with FK,
-- but this migration ensures existing databases are updated.

-- Step 1: Add column if not exists (for DBs created before FK was added to DDL)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'trading'
    AND table_name = 'price_alerts'
    AND column_name = 'symbol_id'
  ) THEN
    ALTER TABLE trading.price_alerts ADD COLUMN symbol_id UUID;
    RAISE NOTICE 'Column symbol_id added to trading.price_alerts';
  ELSE
    RAISE NOTICE 'Column symbol_id already exists in trading.price_alerts';
  END IF;
END $$;

-- Step 2: Add FK constraint if not exists
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints
    WHERE constraint_schema = 'trading'
    AND table_name = 'price_alerts'
    AND constraint_name = 'fk_price_alerts_symbol'
  ) AND NOT EXISTS (
    SELECT 1 FROM information_schema.table_constraints
    WHERE constraint_schema = 'trading'
    AND table_name = 'price_alerts'
    AND constraint_name = 'price_alerts_symbol_id_fkey'
  ) THEN
    ALTER TABLE trading.price_alerts
    ADD CONSTRAINT fk_price_alerts_symbol
    FOREIGN KEY (symbol_id) REFERENCES trading.symbols(id) ON DELETE CASCADE;
    RAISE NOTICE 'FK constraint fk_price_alerts_symbol added';
  ELSE
    RAISE NOTICE 'FK constraint for symbol_id already exists';
  END IF;
END $$;

-- Step 3: Create index if not exists
CREATE INDEX IF NOT EXISTS idx_price_alerts_symbol_id
ON trading.price_alerts(symbol_id);

-- Step 4: Migration script for existing data
-- If there's a legacy 'symbol' varchar column, migrate data to symbol_id
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'trading'
    AND table_name = 'price_alerts'
    AND column_name = 'symbol'
    AND data_type IN ('character varying', 'text')
  ) THEN
    -- Migrate data from symbol (varchar) to symbol_id (uuid)
    UPDATE trading.price_alerts pa
    SET symbol_id = s.id
    FROM trading.symbols s
    WHERE pa.symbol = s.symbol
    AND pa.symbol_id IS NULL;

    RAISE NOTICE 'Data migrated from symbol column to symbol_id';
  END IF;
END $$;

-- Step 5: Verify integrity after migration
DO $$
DECLARE
  orphan_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO orphan_count
  FROM trading.price_alerts
  WHERE symbol_id IS NULL;

  IF orphan_count > 0 THEN
    RAISE WARNING 'Found % price_alerts with NULL symbol_id - manual review required', orphan_count;
  ELSE
    RAISE NOTICE 'All price_alerts have valid symbol_id references';
  END IF;
END $$;

-- Optional: Make NOT NULL after confirming all data is migrated
-- Uncomment after verifying no orphan records:
-- ALTER TABLE trading.price_alerts ALTER COLUMN symbol_id SET NOT NULL;

-- ============================================================================
-- Verification query (run manually to confirm):
-- SELECT
--   COUNT(*) as total_alerts,
--   COUNT(symbol_id) as with_symbol_id,
--   COUNT(*) - COUNT(symbol_id) as missing_symbol_id
-- FROM trading.price_alerts;
-- ============================================================================