import { Pool, PoolClient } from 'pg';

import pool from '../db';
import { NormalizedPlayerPropMarket } from './mlb-market-normalizer';

export const MLB_MARKET_COMPLETION_CRON = {
  name: 'MLB Market Completion Engine',
  slug: 'mlb_market_completion',
  schedule: '* * * * *',
  description: 'Detects incomplete MLB player prop markets and fills missing sides using secondary sources (FanDuel, DraftKings). Normalizes into two-sided markets for forecasting.',
  category: 'Market Integrity',
  priority: 'HIGH',
  status: 'ACTIVE',
  safe_mode: true,
  non_blocking: true,
  notes: [
    'No regression allowed',
    'Does not modify raw ingest',
    'Preserves source attribution',
    'Does not fabricate market sides',
    'Supports multi-source completion',
  ],
} as const;

type Db = Pool | PoolClient;

export interface MlbMarketCompletionSummary {
  totalMarkets: number;
  sourceComplete: number;
  multiSourceComplete: number;
  incomplete: number;
  gapFilled: number;
  underOnlyMarkets: number;
  overOnlyMarkets: number;
  twoWayMarkets: number;
  zeroGapFillEvents: number;
  completeRate: number;
  gapFillAttempts: number;
  gapFillSuccesses: number;
  gapFillSuccessRate: number;
  breakdownByPropType: Record<string, number>;
  sourceDistribution: Record<string, number>;
  eventBreakdown: MlbMarketCompletionEventSummary[];
}

export interface MlbMarketCompletionEventSummary {
  eventId: string;
  startsAt: string | null;
  homeTeam: string | null;
  awayTeam: string | null;
  totalMarkets: number;
  sourceComplete: number;
  multiSourceComplete: number;
  incomplete: number;
  gapFilledMarkets: number;
  underOnlyMarkets: number;
  overOnlyMarkets: number;
  twoWayMarkets: number;
  zeroGapFillEvent: boolean;
  completionRate: number;
}

type EventTelemetryInput = {
  eventId: string;
  startsAt: string | null;
  homeTeam: string | null;
  awayTeam: string | null;
  completenessStatus: string | null;
  isGapFilled: boolean;
  availableSides: string[];
};

function buildMlbMarketEventBreakdown(inputs: EventTelemetryInput[]): MlbMarketCompletionEventSummary[] {
  const byEvent = new Map<string, MlbMarketCompletionEventSummary>();

  for (const input of inputs) {
    const eventId = String(input.eventId || '').trim();
    if (!eventId) continue;

    let eventSummary = byEvent.get(eventId);
    if (!eventSummary) {
      eventSummary = {
        eventId,
        startsAt: input.startsAt || null,
        homeTeam: input.homeTeam || null,
        awayTeam: input.awayTeam || null,
        totalMarkets: 0,
        sourceComplete: 0,
        multiSourceComplete: 0,
        incomplete: 0,
        gapFilledMarkets: 0,
        underOnlyMarkets: 0,
        overOnlyMarkets: 0,
        twoWayMarkets: 0,
        zeroGapFillEvent: true,
        completionRate: 0,
      };
      byEvent.set(eventId, eventSummary);
    }

    eventSummary.totalMarkets += 1;
    if (input.completenessStatus === 'source_complete') eventSummary.sourceComplete += 1;
    if (input.completenessStatus === 'multi_source_complete') eventSummary.multiSourceComplete += 1;
    if (input.completenessStatus === 'incomplete') eventSummary.incomplete += 1;
    if (input.isGapFilled) eventSummary.gapFilledMarkets += 1;

    const availableSides = new Set(
      (Array.isArray(input.availableSides) ? input.availableSides : [])
        .map((side) => String(side || '').trim().toLowerCase())
        .filter(Boolean),
    );
    const hasOver = availableSides.has('over');
    const hasUnder = availableSides.has('under');
    if (hasOver && hasUnder) eventSummary.twoWayMarkets += 1;
    else if (hasUnder) eventSummary.underOnlyMarkets += 1;
    else if (hasOver) eventSummary.overOnlyMarkets += 1;
  }

  return [...byEvent.values()]
    .map((eventSummary) => {
      const completed = eventSummary.sourceComplete + eventSummary.multiSourceComplete;
      return {
        ...eventSummary,
        zeroGapFillEvent: eventSummary.gapFilledMarkets === 0,
        completionRate: eventSummary.totalMarkets > 0 ? completed / eventSummary.totalMarkets : 0,
      };
    })
    .sort((a, b) => {
      if (a.zeroGapFillEvent !== b.zeroGapFillEvent) return Number(b.zeroGapFillEvent) - Number(a.zeroGapFillEvent);
      if (a.completionRate !== b.completionRate) return a.completionRate - b.completionRate;
      if (a.underOnlyMarkets !== b.underOnlyMarkets) return b.underOnlyMarkets - a.underOnlyMarkets;
      return a.eventId.localeCompare(b.eventId);
    });
}

export function summarizeNormalizedMlbMarkets(markets: NormalizedPlayerPropMarket[]): MlbMarketCompletionSummary {
  const eventBreakdown = buildMlbMarketEventBreakdown(markets.map((market) => ({
    eventId: market.eventId,
    startsAt: market.startsAt || null,
    homeTeam: market.homeTeam || null,
    awayTeam: market.awayTeam || null,
    completenessStatus: market.completenessStatus,
    isGapFilled: Boolean(market.isGapFilled),
    availableSides: market.availableSides || [],
  })));
  const summary: MlbMarketCompletionSummary = {
    totalMarkets: markets.length,
    sourceComplete: 0,
    multiSourceComplete: 0,
    incomplete: 0,
    gapFilled: 0,
    underOnlyMarkets: 0,
    overOnlyMarkets: 0,
    twoWayMarkets: 0,
    zeroGapFillEvents: eventBreakdown.filter((event) => event.zeroGapFillEvent).length,
    completeRate: 0,
    gapFillAttempts: 0,
    gapFillSuccesses: 0,
    gapFillSuccessRate: 0,
    breakdownByPropType: {},
    sourceDistribution: {},
    eventBreakdown,
  };

  for (const market of markets) {
    if (market.completenessStatus === 'source_complete') summary.sourceComplete += 1;
    if (market.completenessStatus === 'multi_source_complete') summary.multiSourceComplete += 1;
    if (market.completenessStatus === 'incomplete') summary.incomplete += 1;
    if (market.isGapFilled) summary.gapFilled += 1;
    if (market.completenessStatus !== 'source_complete' && market.availableSides.length > 0) {
      summary.gapFillAttempts += 1;
    }
    if (market.isGapFilled || market.completenessStatus === 'multi_source_complete') summary.gapFillSuccesses += 1;
    const availableSides = new Set((market.availableSides || []).map((side) => String(side || '').trim().toLowerCase()));
    const hasOver = availableSides.has('over');
    const hasUnder = availableSides.has('under');
    if (hasOver && hasUnder) summary.twoWayMarkets += 1;
    else if (hasUnder) summary.underOnlyMarkets += 1;
    else if (hasOver) summary.overOnlyMarkets += 1;
    summary.breakdownByPropType[market.statType] = (summary.breakdownByPropType[market.statType] || 0) + 1;
    for (const source of market.sourceMap) {
      summary.sourceDistribution[source.source] = (summary.sourceDistribution[source.source] || 0) + 1;
    }
  }

  const completed = summary.sourceComplete + summary.multiSourceComplete;
  summary.completeRate = summary.totalMarkets > 0 ? completed / summary.totalMarkets : 0;
  summary.gapFillSuccessRate = summary.gapFillAttempts > 0 ? summary.gapFillSuccesses / summary.gapFillAttempts : 0;

  return summary;
}

export async function ensureOperationalCronRegistration(db: Db = pool): Promise<void> {
  await db.query(
    `INSERT INTO rm_operational_crons
       (slug, name, schedule, description, category, priority, status, safe_mode, non_blocking, notes)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10::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()`,
    [
      MLB_MARKET_COMPLETION_CRON.slug,
      MLB_MARKET_COMPLETION_CRON.name,
      MLB_MARKET_COMPLETION_CRON.schedule,
      MLB_MARKET_COMPLETION_CRON.description,
      MLB_MARKET_COMPLETION_CRON.category,
      MLB_MARKET_COMPLETION_CRON.priority,
      MLB_MARKET_COMPLETION_CRON.status,
      MLB_MARKET_COMPLETION_CRON.safe_mode,
      MLB_MARKET_COMPLETION_CRON.non_blocking,
      JSON.stringify(MLB_MARKET_COMPLETION_CRON.notes),
    ],
  );
}

export async function createOperationalCronRun(db: Db = pool): Promise<string> {
  const { rows } = await db.query(
    `INSERT INTO rm_operational_cron_runs (cron_slug, status)
     VALUES ($1, 'RUNNING')
     RETURNING id`,
    [MLB_MARKET_COMPLETION_CRON.slug],
  );
  return String(rows[0]?.id);
}

export async function finalizeOperationalCronRun(
  db: Db,
  params: {
    runId: string;
    status: 'SUCCESS' | 'FAILED' | 'SKIPPED';
    durationMs: number;
    metrics: Record<string, any>;
    errorMessage?: string | null;
    logBlob?: string | null;
  },
): Promise<void> {
  await db.query(
    `UPDATE rm_operational_cron_runs
     SET completed_at = NOW(),
         status = $2,
         duration_ms = $3,
         metrics = $4::jsonb,
         error_message = $5,
         log_blob = $6
     WHERE id = $1`,
    [
      params.runId,
      params.status,
      Math.max(0, Math.round(params.durationMs)),
      JSON.stringify(params.metrics || {}),
      params.errorMessage || null,
      params.logBlob || null,
    ],
  );
}

export async function refreshOperationalCronMetrics(db: Db = pool): Promise<void> {
  await db.query(
    `UPDATE rm_operational_crons c
     SET last_run = stats.last_run,
         avg_runtime_ms = stats.avg_runtime_ms,
         error_rate = stats.error_rate,
         metrics = stats.metrics,
         updated_at = NOW()
     FROM (
       SELECT
         cron_slug,
         MAX(started_at) AS last_run,
         ROUND(AVG(duration_ms) FILTER (WHERE duration_ms IS NOT NULL))::int AS avg_runtime_ms,
         ROUND(
           100.0 * COUNT(*) FILTER (WHERE status = 'FAILED')
           / NULLIF(COUNT(*), 0),
           2
         ) AS error_rate,
         COALESCE(
           (
             SELECT r.metrics
             FROM rm_operational_cron_runs r
             WHERE r.cron_slug = runs.cron_slug
               AND r.status IN ('SUCCESS', 'FAILED', 'SKIPPED')
             ORDER BY r.started_at DESC
             LIMIT 1
           ),
           '{}'::jsonb
         ) AS metrics
       FROM rm_operational_cron_runs runs
       GROUP BY cron_slug
     ) stats
     WHERE c.slug = stats.cron_slug
       AND c.slug = $1`,
    [MLB_MARKET_COMPLETION_CRON.slug],
  );
}

export async function fetchOperationalCronSummaries(db: Db = pool): Promise<any[]> {
  const { rows } = await db.query(
    `SELECT slug, name, schedule, description, category, priority, status, safe_mode,
            non_blocking, last_run, avg_runtime_ms, error_rate, notes, metrics
     FROM rm_operational_crons
     ORDER BY category NULLS LAST, name ASC`,
  );
  return rows;
}

export async function fetchOperationalCronSummary(slug: string, db: Db = pool): Promise<any | null> {
  const { rows } = await db.query(
    `SELECT slug, name, schedule, description, category, priority, status, safe_mode,
            non_blocking, last_run, avg_runtime_ms, error_rate, notes, metrics
     FROM rm_operational_crons
     WHERE slug = $1
     LIMIT 1`,
    [slug],
  );
  return rows[0] || null;
}

export async function fetchOperationalCronRuns(slug: string, limit = 20, db: Db = pool): Promise<any[]> {
  const { rows } = await db.query(
    `SELECT id, cron_slug, started_at, completed_at, status, duration_ms, metrics, error_message
     FROM rm_operational_cron_runs
     WHERE cron_slug = $1
     ORDER BY started_at DESC
     LIMIT $2`,
    [slug, Math.max(1, Math.min(limit, 100))],
  );
  return rows;
}

export async function fetchStoredMlbMarketRows(filters: {
  eventId?: string | null;
  completenessStatus?: string | null;
  limit?: number | null;
}, db: Db = pool): Promise<any[]> {
  const conditions = ['league = \'mlb\''];
  const params: any[] = [];

  if (filters.eventId) {
    params.push(filters.eventId);
    conditions.push(`event_id = $${params.length}`);
  }
  if (filters.completenessStatus) {
    params.push(filters.completenessStatus);
    conditions.push(`completeness_status = $${params.length}`);
  }

  const limit = typeof filters.limit === 'number' && Number.isFinite(filters.limit)
    ? Math.max(1, Math.min(filters.limit, 1000))
    : null;

  const sql = (
    `SELECT event_id, starts_at, home_team, away_team, player_id, player_name, team_short,
            stat_type, normalized_stat_type, line, market_name, primary_source, completion_method,
            completeness_status, is_gap_filled, available_sides, over_payload, under_payload,
            source_map, raw_market_count, updated_at
     FROM rm_mlb_normalized_player_prop_markets
     WHERE ${conditions.join(' AND ')}
     ORDER BY event_id ASC, player_name ASC, stat_type ASC, line ASC`
  );

  if (limit != null) {
    params.push(limit);
  }

  const { rows } = await db.query(
    `${sql}${limit != null ? ` LIMIT $${params.length}` : ''}`,
    params,
  );

  return rows;
}

export async function fetchMlbMarketCompletionInsights(filters: {
  eventId?: string | null;
  completenessStatus?: string | null;
}, db: Db = pool): Promise<{
  incompleteWithAlternateLines: number;
  incompleteWithoutAlternateLines: number;
  exactSideBreakdown: {
    underOnly: number;
    overOnly: number;
    bothExactSidesPresent: number;
  };
}> {
  const conditions = [`league = 'mlb'`];
  const params: any[] = [];

  if (filters.eventId) {
    params.push(filters.eventId);
    conditions.push(`event_id = $${params.length}`);
  }
  if (filters.completenessStatus) {
    params.push(filters.completenessStatus);
    conditions.push(`completeness_status = $${params.length}`);
  }

  const { rows } = await db.query(
    `WITH scoped AS (
       SELECT event_id, player_id, stat_type, line, completeness_status
       FROM rm_mlb_normalized_player_prop_markets
       WHERE ${conditions.join(' AND ')}
     ),
     events AS (
       SELECT event_id, starts_at, home_short, away_short
       FROM rm_events
       WHERE event_id IN (SELECT DISTINCT event_id FROM scoped)
     ),
     raw_grouped AS (
       SELECT
         e.event_id,
         ppl."playerExternalId" AS player_id,
         ppl."propType" AS stat_type,
         COALESCE(
           NULLIF(ppl.raw #>> '{sportsgameodds,bookOverUnder}', '')::numeric,
           NULLIF(ppl.raw #>> '{sportsgameodds,openBookOverUnder}', '')::numeric,
           NULLIF(ppl.raw #>> '{sportsgameodds,fairOverUnder}', '')::numeric,
           CASE
             WHEN regexp_replace(lower(COALESCE(ppl.raw->>'bookmaker', ppl.raw #>> '{sportsgameodds,bookmaker}', ppl.vendor, '')), '[^a-z0-9]+', '', 'g') = 'fanduel'
               THEN COALESCE(ppl."fdLine", ppl."lineValue", ppl."dkLine")
             WHEN regexp_replace(lower(COALESCE(ppl.raw->>'bookmaker', ppl.raw #>> '{sportsgameodds,bookmaker}', ppl.vendor, '')), '[^a-z0-9]+', '', 'g') IN ('draftkings', 'draftking')
               THEN COALESCE(ppl."dkLine", ppl."lineValue", ppl."fdLine")
             ELSE COALESCE(ppl."lineValue", ppl."fdLine", ppl."dkLine")
           END
         ) AS line,
         COUNT(*) FILTER (
           WHERE lower(COALESCE(NULLIF(ppl.raw #>> '{side}', ''), NULLIF(ppl.raw #>> '{sportsgameodds,sideID}', ''))) = 'over'
         ) AS over_rows,
         COUNT(*) FILTER (
           WHERE lower(COALESCE(NULLIF(ppl.raw #>> '{side}', ''), NULLIF(ppl.raw #>> '{sportsgameodds,sideID}', ''))) = 'under'
         ) AS under_rows
       FROM "PlayerPropLine" ppl
       JOIN events e
         ON (ppl."gameStart")::timestamptz BETWEEN (e.starts_at - INTERVAL '3 hours') AND (e.starts_at + INTERVAL '3 hours')
        AND COALESCE(ppl."homeTeam", '') IN (e.home_short, e.away_short)
        AND COALESCE(ppl."awayTeam", '') IN (e.home_short, e.away_short)
       WHERE ppl.league = 'mlb'
         AND COALESCE(ppl."marketScope", 'full_game') = 'full_game'
       GROUP BY 1, 2, 3, 4
     ),
     alternate_lines AS (
       SELECT s.event_id, s.player_id, s.stat_type, s.line
       FROM scoped s
       JOIN raw_grouped r
         ON r.event_id = s.event_id
        AND r.player_id = s.player_id
        AND r.stat_type = s.stat_type
        AND r.line IS NOT NULL
        AND r.line <> s.line
       WHERE s.completeness_status = 'incomplete'
       GROUP BY 1, 2, 3, 4
     )
     SELECT
       COUNT(*) FILTER (
         WHERE s.completeness_status = 'incomplete'
           AND a.player_id IS NOT NULL
       )::int AS incomplete_with_alternate_lines,
       COUNT(*) FILTER (
         WHERE s.completeness_status = 'incomplete'
           AND a.player_id IS NULL
       )::int AS incomplete_without_alternate_lines,
       COUNT(*) FILTER (
         WHERE s.completeness_status = 'incomplete'
           AND COALESCE(r.over_rows, 0) = 0
           AND COALESCE(r.under_rows, 0) > 0
       )::int AS incomplete_under_only_exact,
       COUNT(*) FILTER (
         WHERE s.completeness_status = 'incomplete'
           AND COALESCE(r.over_rows, 0) > 0
           AND COALESCE(r.under_rows, 0) = 0
       )::int AS incomplete_over_only_exact,
       COUNT(*) FILTER (
         WHERE s.completeness_status = 'incomplete'
           AND COALESCE(r.over_rows, 0) > 0
           AND COALESCE(r.under_rows, 0) > 0
       )::int AS incomplete_with_both_exact_sides
     FROM scoped s
     LEFT JOIN raw_grouped r
       ON r.event_id = s.event_id
      AND r.player_id = s.player_id
      AND r.stat_type = s.stat_type
      AND r.line = s.line
     LEFT JOIN alternate_lines a
       ON a.event_id = s.event_id
      AND a.player_id = s.player_id
      AND a.stat_type = s.stat_type
      AND a.line = s.line`,
    params,
  );

  const row = rows[0] || {};
  return {
    incompleteWithAlternateLines: Number(row.incomplete_with_alternate_lines || 0),
    incompleteWithoutAlternateLines: Number(row.incomplete_without_alternate_lines || 0),
    exactSideBreakdown: {
      underOnly: Number(row.incomplete_under_only_exact || 0),
      overOnly: Number(row.incomplete_over_only_exact || 0),
      bothExactSidesPresent: Number(row.incomplete_with_both_exact_sides || 0),
    },
  };
}

export function summarizeStoredMlbMarketRows(rows: any[]): {
  totalMarkets: number;
  sourceComplete: number;
  multiSourceComplete: number;
  incomplete: number;
  gapFilled: number;
  underOnlyMarkets: number;
  overOnlyMarkets: number;
  twoWayMarkets: number;
  zeroGapFillEvents: number;
  byStatType: Record<string, number>;
  byAvailableSides: Record<string, number>;
  bySource: Record<string, number>;
  bySourceCount: Record<string, number>;
  eventBreakdown: MlbMarketCompletionEventSummary[];
} {
  const eventBreakdown = buildMlbMarketEventBreakdown(rows.map((row) => ({
    eventId: String(row.event_id || ''),
    startsAt: row.starts_at || null,
    homeTeam: row.home_team || null,
    awayTeam: row.away_team || null,
    completenessStatus: row.completeness_status || null,
    isGapFilled: Boolean(row.is_gap_filled),
    availableSides: Array.isArray(row.available_sides) ? row.available_sides : [],
  })));
  let sourceComplete = 0;
  let multiSourceComplete = 0;
  let incomplete = 0;
  let gapFilled = 0;
  let underOnlyMarkets = 0;
  let overOnlyMarkets = 0;
  let twoWayMarkets = 0;
  const byStatType: Record<string, number> = {};
  const byAvailableSides: Record<string, number> = {};
  const bySource: Record<string, number> = {};
  const bySourceCount: Record<string, number> = {};

  for (const row of rows) {
    if (row.completeness_status === 'source_complete') sourceComplete += 1;
    if (row.completeness_status === 'multi_source_complete') multiSourceComplete += 1;
    if (row.completeness_status === 'incomplete') incomplete += 1;
    if (row.is_gap_filled) gapFilled += 1;
    const availableSides = new Set(
      (Array.isArray(row.available_sides) ? row.available_sides : [])
        .map((side: unknown) => String(side || '').trim().toLowerCase())
        .filter(Boolean),
    );
    const hasOver = availableSides.has('over');
    const hasUnder = availableSides.has('under');
    if (hasOver && hasUnder) twoWayMarkets += 1;
    else if (hasUnder) underOnlyMarkets += 1;
    else if (hasOver) overOnlyMarkets += 1;
    const statType = String(row.stat_type || 'unknown');
    byStatType[statType] = (byStatType[statType] || 0) + 1;

    const sides = Array.isArray(row.available_sides) ? row.available_sides.join('|') : 'none';
    byAvailableSides[sides || 'none'] = (byAvailableSides[sides || 'none'] || 0) + 1;

    const sourceMap = Array.isArray(row.source_map) ? row.source_map : [];
    bySourceCount[String(sourceMap.length)] = (bySourceCount[String(sourceMap.length)] || 0) + 1;
    for (const source of sourceMap) {
      const key = String(source?.source || 'unknown');
      bySource[key] = (bySource[key] || 0) + 1;
    }
  }

  return {
    totalMarkets: rows.length,
    sourceComplete,
    multiSourceComplete,
    incomplete,
    gapFilled,
    underOnlyMarkets,
    overOnlyMarkets,
    twoWayMarkets,
    zeroGapFillEvents: eventBreakdown.filter((event) => event.zeroGapFillEvent).length,
    byStatType,
    byAvailableSides,
    bySource,
    bySourceCount,
    eventBreakdown,
  };
}