-- =====================================================
-- MIGRATION: Add overlay columns to ml.predictions
-- =====================================================
-- Gap: GAP-DDL-006
-- Task: TASK-2026-02-03-ANALISIS-DDL-MODELADO / ST-1.4
-- Created: 2026-02-03
-- Updated: 2026-02-03
-- Description: Complete overlay structure for ML predictions visualization on trading charts
-- =====================================================

-- ===========================================
-- 1. ADD OVERLAY COLUMNS
-- ===========================================

-- Add overlay visualization data
ALTER TABLE ml.predictions
  ADD COLUMN IF NOT EXISTS overlay_data JSONB DEFAULT '{}';

-- Add chart configuration
ALTER TABLE ml.predictions
  ADD COLUMN IF NOT EXISTS chart_config JSONB DEFAULT '{
    "show_on_chart": true,
    "color": "#4CAF50",
    "line_style": "dashed",
    "opacity": 0.8
  }';

-- Add display priority for ordering multiple predictions
ALTER TABLE ml.predictions
  ADD COLUMN IF NOT EXISTS display_priority INTEGER DEFAULT 0;

-- ===========================================
-- 2. CREATE INDEXES FOR OVERLAY QUERIES
-- ===========================================

-- Index for overlay queries - predictions visible on chart ordered by priority
CREATE INDEX IF NOT EXISTS idx_predictions_overlay
  ON ml.predictions(symbol, timeframe, display_priority DESC)
  WHERE (chart_config->>'show_on_chart')::boolean = true;

-- Index for fetching overlay data efficiently
CREATE INDEX IF NOT EXISTS idx_predictions_overlay_data
  ON ml.predictions USING GIN (overlay_data)
  WHERE overlay_data IS NOT NULL AND overlay_data != '{}';

-- ===========================================
-- 3. COLUMN COMMENTS
-- ===========================================

COMMENT ON COLUMN ml.predictions.overlay_data IS
'JSON data for chart overlay visualization. Example:
{
  "price_levels": [1850.00, 1865.50, 1880.25],
  "zones": [
    {"type": "support", "low": 1845.00, "high": 1850.00, "color": "#4CAF50"},
    {"type": "resistance", "low": 1875.00, "high": 1880.00, "color": "#F44336"}
  ],
  "trend_lines": [
    {"start": {"time": "2026-02-01T10:00:00Z", "price": 1840.00}, "end": {"time": "2026-02-03T10:00:00Z", "price": 1870.00}}
  ],
  "annotations": [
    {"time": "2026-02-02T14:00:00Z", "text": "Breakout signal", "position": "above"}
  ]
}';

COMMENT ON COLUMN ml.predictions.chart_config IS
'Configuration for how to display this prediction on charts. Example:
{
  "show_on_chart": true,
  "color": "#4CAF50",
  "line_style": "dashed",
  "opacity": 0.8,
  "label_visible": true,
  "z_index": 10
}';

COMMENT ON COLUMN ml.predictions.display_priority IS
'Priority for ordering when multiple predictions exist (higher = more prominent).
Default: 0, Range: -100 to 100.
Use negative values for background overlays, positive for foreground.';