-- ============================================================================
-- Migration: Unify timeframe enum across schemas
-- Date: 2026-02-03
-- Task: ST-2.1
-- Issue: DUP-001 - timeframe enum duplicated in trading and market_data
-- ============================================================================
--
-- SUMMARY:
-- This migration creates a unified timeframe enum in the public schema.
-- The enum is a superset of all values from:
--   - trading.timeframe: '1m', '5m', '15m', '30m', '1h', '4h', '1d', '1w', '1M'
--   - market_data.timeframe: '1m', '5m', '15m', '30m', '1h', '4h', '1d', '1w'
--
-- DIFFERENCE IDENTIFIED:
--   - trading.timeframe has '1M' (1 month)
--   - market_data.timeframe does NOT have '1M'
--   - Unified type includes ALL values (superset)
--
-- ============================================================================

-- Step 1: Create unified enum in public schema (superset of all values)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_type
        WHERE typname = 'trading_timeframe'
        AND typnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
    ) THEN
        CREATE TYPE public.trading_timeframe AS ENUM (
            '1m', '5m', '15m', '30m', '1h', '4h', '1d', '1w', '1M'
        );
    END IF;
END $$;

COMMENT ON TYPE public.trading_timeframe IS 'Unified timeframe enum for all trading-related tables. Replaces trading.timeframe and market_data.timeframe';

-- ============================================================================
-- Step 2: Migration plan for existing tables (NOT EXECUTED - requires planning)
-- ============================================================================
--
-- AFFECTED TABLES (to be migrated in future):
--
-- Schema: trading
--   - trading.trading_signals (column: timeframe)
--   - trading.bot_configurations (column: timeframes - array)
--   - Any other tables using trading.timeframe
--
-- Schema: market_data
--   - market_data.ohlcv_data (column: timeframe)
--   - market_data.symbols (column: supported_timeframes - array)
--   - Any other tables using market_data.timeframe
--
-- MIGRATION STEPS FOR EACH TABLE:
--
-- a) Add new column with public.trading_timeframe type:
--    ALTER TABLE schema.table ADD COLUMN timeframe_new public.trading_timeframe;
--
-- b) Copy data from old column:
--    UPDATE schema.table SET timeframe_new = timeframe::text::public.trading_timeframe;
--
-- c) Drop old column and rename new:
--    ALTER TABLE schema.table DROP COLUMN timeframe;
--    ALTER TABLE schema.table RENAME COLUMN timeframe_new TO timeframe;
--
-- d) After all tables migrated, drop schema-specific enum:
--    DROP TYPE trading.timeframe;
--    DROP TYPE market_data.timeframe;
--
-- WARNING: This is a breaking change that requires:
--   1. Backend code updates to use new type
--   2. Application downtime or blue-green deployment
--   3. Careful coordination with all services
--
-- ============================================================================