-- MLB market completion storage + operational cron tracking
-- Safe, additive migration.

BEGIN;

CREATE TABLE IF NOT EXISTS rm_operational_crons (
  slug VARCHAR(100) PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  schedule VARCHAR(100) NOT NULL,
  description TEXT,
  category VARCHAR(100),
  priority VARCHAR(20) NOT NULL DEFAULT 'NORMAL',
  status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
  safe_mode BOOLEAN NOT NULL DEFAULT TRUE,
  non_blocking BOOLEAN NOT NULL DEFAULT TRUE,
  last_run TIMESTAMPTZ,
  avg_runtime_ms INTEGER,
  error_rate NUMERIC(6,2),
  notes JSONB NOT NULL DEFAULT '[]'::jsonb,
  metrics JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT rm_operational_crons_priority_check
    CHECK (priority IN ('LOW', 'NORMAL', 'HIGH', 'CRITICAL')),
  CONSTRAINT rm_operational_crons_status_check
    CHECK (status IN ('ACTIVE', 'PAUSED', 'ERROR', 'DISABLED'))
);

CREATE TABLE IF NOT EXISTS rm_operational_cron_runs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  cron_slug VARCHAR(100) NOT NULL REFERENCES rm_operational_crons(slug) ON DELETE CASCADE,
  started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  status VARCHAR(20) NOT NULL DEFAULT 'RUNNING',
  duration_ms INTEGER,
  metrics JSONB NOT NULL DEFAULT '{}'::jsonb,
  error_message TEXT,
  log_blob TEXT,
  CONSTRAINT rm_operational_cron_runs_status_check
    CHECK (status IN ('RUNNING', 'SUCCESS', 'FAILED', 'SKIPPED'))
);

CREATE INDEX IF NOT EXISTS idx_rm_operational_cron_runs_slug_started
  ON rm_operational_cron_runs(cron_slug, started_at DESC);

CREATE INDEX IF NOT EXISTS idx_rm_operational_cron_runs_status
  ON rm_operational_cron_runs(status, started_at DESC);

CREATE TABLE IF NOT EXISTS rm_mlb_normalized_player_prop_markets (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  event_id TEXT NOT NULL,
  league VARCHAR(20) NOT NULL DEFAULT 'mlb',
  starts_at TIMESTAMPTZ,
  home_team VARCHAR(20),
  away_team VARCHAR(20),
  player_id TEXT NOT NULL,
  player_name VARCHAR(255) NOT NULL,
  team_short VARCHAR(20),
  stat_type TEXT NOT NULL,
  normalized_stat_type TEXT NOT NULL,
  line NUMERIC(10,3) NOT NULL,
  market_name TEXT,
  primary_source VARCHAR(100),
  completion_method VARCHAR(32) NOT NULL DEFAULT 'none',
  completeness_status VARCHAR(32) NOT NULL DEFAULT 'incomplete',
  is_gap_filled BOOLEAN NOT NULL DEFAULT FALSE,
  available_sides TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
  over_payload JSONB,
  under_payload JSONB,
  source_map JSONB NOT NULL DEFAULT '[]'::jsonb,
  raw_market_count INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT rm_mlb_normalized_player_prop_markets_completion_method_check
    CHECK (completion_method IN ('none', 'source', 'multi_source')),
  CONSTRAINT rm_mlb_normalized_player_prop_markets_completeness_check
    CHECK (completeness_status IN ('source_complete', 'multi_source_complete', 'incomplete')),
  CONSTRAINT rm_mlb_normalized_player_prop_markets_unique_market
    UNIQUE (event_id, player_id, stat_type, line)
);

CREATE INDEX IF NOT EXISTS idx_rm_mlb_normalized_markets_event
  ON rm_mlb_normalized_player_prop_markets(event_id, completeness_status);

CREATE INDEX IF NOT EXISTS idx_rm_mlb_normalized_markets_updated
  ON rm_mlb_normalized_player_prop_markets(updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_rm_mlb_normalized_markets_player
  ON rm_mlb_normalized_player_prop_markets(player_id, stat_type, line);

INSERT INTO rm_operational_crons (
  slug,
  name,
  schedule,
  description,
  category,
  priority,
  status,
  safe_mode,
  non_blocking,
  notes
)
VALUES (
  'mlb_market_completion',
  'MLB Market Completion Engine',
  '* * * * *',
  'Detects incomplete MLB player prop markets and fills missing sides using secondary real sources before normalizing them for forecast and API consumption.',
  'Market Integrity',
  'HIGH',
  'ACTIVE',
  TRUE,
  TRUE,
  '[
    "No regression allowed",
    "Does not modify raw ingest",
    "Preserves source attribution",
    "Does not fabricate market sides",
    "Supports multi-source completion"
  ]'::jsonb
)
ON CONFLICT (slug) DO UPDATE SET
  name = EXCLUDED.name,
  schedule = EXCLUDED.schedule,
  description = EXCLUDED.description,
  category = EXCLUDED.category,
  priority = EXCLUDED.priority,
  status = EXCLUDED.status,
  safe_mode = EXCLUDED.safe_mode,
  non_blocking = EXCLUDED.non_blocking,
  notes = EXCLUDED.notes,
  updated_at = NOW();

COMMIT;
