trading-platform-database-v2/ddl/schemas/ml/tables/12-prediction_overlays.sql

-- =====================================================
-- ML SCHEMA - PREDICTION OVERLAYS TABLE
-- =====================================================
-- Description: Complex overlay configurations for chart visualization
-- Schema: ml
-- Author: Database Agent
-- Date: 2026-02-03
-- Task: TASK-2026-02-03-ANALISIS-DDL-MODELADO / ST-1.4
-- Module: OQI-006-senales-ml
-- =====================================================

-- ===========================================
-- ENUM: Overlay types for chart visualization
-- ===========================================
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'overlay_type' AND typnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'ml')) THEN
    CREATE TYPE ml.overlay_type AS ENUM (
      'support_resistance',  -- Horizontal support/resistance levels
      'trend_line',          -- Diagonal trend lines
      'zone',                -- Price zones (supply/demand, liquidity)
      'arrow',               -- Direction arrows on chart
      'label',               -- Text annotations
      'fibonacci',           -- Fibonacci retracements/extensions
      'order_block',         -- ICT Order Blocks
      'fair_value_gap',      -- FVG/Imbalances
      'liquidity_level',     -- Liquidity pools
      'ict_killzone'         -- ICT Killzone time highlighting
    );
  END IF;
END $$;

-- ===========================================
-- TABLE: ml.prediction_overlays
-- ===========================================
CREATE TABLE IF NOT EXISTS ml.prediction_overlays (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Reference to parent prediction
  prediction_id UUID NOT NULL REFERENCES ml.predictions(id) ON DELETE CASCADE,

  -- Overlay type classification
  overlay_type ml.overlay_type NOT NULL,

  -- Label for display
  label VARCHAR(100),

  -- Price levels (for support/resistance, zones, etc.)
  price_levels DECIMAL(18,8)[] DEFAULT '{}',

  -- Time range for time-bounded overlays
  time_range TSTZRANGE,

  -- Specific time points (for arrows, labels)
  time_point TIMESTAMPTZ,

  -- Price point (for single-point overlays)
  price_point DECIMAL(18,8),

  -- Coordinates for complex shapes (trend lines, etc.)
  -- Format: [{"time": "ISO8601", "price": number}, ...]
  coordinates JSONB DEFAULT '[]',

  -- Style configuration
  style_config JSONB DEFAULT '{
    "color": "#4CAF50",
    "line_width": 1,
    "line_style": "solid",
    "fill_opacity": 0.2,
    "text_color": "#FFFFFF",
    "font_size": 12
  }',

  -- Additional metadata (e.g., Fibonacci levels, zone strength)
  metadata JSONB DEFAULT '{}',

  -- Display control
  is_active BOOLEAN NOT NULL DEFAULT true,
  display_priority INTEGER DEFAULT 0,
  z_index INTEGER DEFAULT 0,

  -- Timestamps
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ
);

-- ===========================================
-- INDEXES
-- ===========================================

-- Primary lookup by prediction
CREATE INDEX IF NOT EXISTS idx_prediction_overlays_prediction
  ON ml.prediction_overlays(prediction_id);

-- Active overlays only
CREATE INDEX IF NOT EXISTS idx_prediction_overlays_active
  ON ml.prediction_overlays(prediction_id, is_active)
  WHERE is_active = true;

-- By type for filtering
CREATE INDEX IF NOT EXISTS idx_prediction_overlays_type
  ON ml.prediction_overlays(overlay_type);

-- Time range queries (for time-bounded overlays)
CREATE INDEX IF NOT EXISTS idx_prediction_overlays_time_range
  ON ml.prediction_overlays USING GIST (time_range)
  WHERE time_range IS NOT NULL;

-- Display ordering
CREATE INDEX IF NOT EXISTS idx_prediction_overlays_priority
  ON ml.prediction_overlays(display_priority DESC, z_index DESC)
  WHERE is_active = true;

-- Expiration cleanup
CREATE INDEX IF NOT EXISTS idx_prediction_overlays_expires
  ON ml.prediction_overlays(expires_at)
  WHERE expires_at IS NOT NULL AND is_active = true;

-- GIN index for metadata queries
CREATE INDEX IF NOT EXISTS idx_prediction_overlays_metadata
  ON ml.prediction_overlays USING GIN (metadata)
  WHERE metadata IS NOT NULL AND metadata != '{}';

-- ===========================================
-- TRIGGERS
-- ===========================================

-- Auto-update updated_at timestamp
CREATE OR REPLACE FUNCTION ml.update_prediction_overlays_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trigger_prediction_overlays_updated_at ON ml.prediction_overlays;
CREATE TRIGGER trigger_prediction_overlays_updated_at
  BEFORE UPDATE ON ml.prediction_overlays
  FOR EACH ROW
  EXECUTE FUNCTION ml.update_prediction_overlays_updated_at();

-- ===========================================
-- COMMENTS
-- ===========================================

COMMENT ON TABLE ml.prediction_overlays IS
'Complex overlay configurations for chart visualization.
Stores detailed overlay data that can be rendered on trading charts
to visualize ML predictions, support/resistance levels, zones, and annotations.';

COMMENT ON COLUMN ml.prediction_overlays.id IS 'Unique identifier for the overlay';
COMMENT ON COLUMN ml.prediction_overlays.prediction_id IS 'Reference to parent ML prediction';
COMMENT ON COLUMN ml.prediction_overlays.overlay_type IS 'Type of overlay: support_resistance, trend_line, zone, arrow, label, fibonacci, order_block, fair_value_gap, liquidity_level, ict_killzone';
COMMENT ON COLUMN ml.prediction_overlays.label IS 'Optional text label to display on chart';
COMMENT ON COLUMN ml.prediction_overlays.price_levels IS 'Array of price levels for horizontal overlays';
COMMENT ON COLUMN ml.prediction_overlays.time_range IS 'Time range for time-bounded overlays (e.g., killzones)';
COMMENT ON COLUMN ml.prediction_overlays.time_point IS 'Specific timestamp for point-based overlays (arrows, labels)';
COMMENT ON COLUMN ml.prediction_overlays.price_point IS 'Specific price for point-based overlays';
COMMENT ON COLUMN ml.prediction_overlays.coordinates IS 'JSON array of {time, price} coordinates for complex shapes';
COMMENT ON COLUMN ml.prediction_overlays.style_config IS 'Visual styling: color, line_width, line_style, fill_opacity, etc.';
COMMENT ON COLUMN ml.prediction_overlays.metadata IS 'Additional overlay-specific data (e.g., Fibonacci ratios, zone strength)';
COMMENT ON COLUMN ml.prediction_overlays.is_active IS 'Whether overlay should be displayed';
COMMENT ON COLUMN ml.prediction_overlays.display_priority IS 'Ordering priority (higher = more prominent)';
COMMENT ON COLUMN ml.prediction_overlays.z_index IS 'Z-order for overlapping overlays';
COMMENT ON COLUMN ml.prediction_overlays.expires_at IS 'Optional expiration timestamp for auto-cleanup';

-- ===========================================
-- EXAMPLE DATA STRUCTURES
-- ===========================================

COMMENT ON COLUMN ml.prediction_overlays.coordinates IS
'JSON array of coordinate points. Example for trend line:
[
  {"time": "2026-02-01T10:00:00Z", "price": 1840.00},
  {"time": "2026-02-03T10:00:00Z", "price": 1870.00}
]

Example for Fibonacci:
[
  {"time": "2026-02-01T08:00:00Z", "price": 1800.00, "label": "0%"},
  {"time": "2026-02-02T14:00:00Z", "price": 1880.00, "label": "100%"}
]';

COMMENT ON COLUMN ml.prediction_overlays.metadata IS
'Additional overlay-specific metadata. Examples:

For Fibonacci:
{
  "levels": [0, 0.236, 0.382, 0.5, 0.618, 0.786, 1],
  "extension_levels": [1.272, 1.618, 2.0]
}

For Order Block:
{
  "ob_type": "bullish",
  "mitigated": false,
  "strength": 0.85
}

For Fair Value Gap:
{
  "fvg_type": "bullish",
  "filled_percent": 0.35,
  "high": 1875.50,
  "low": 1872.00
}';