/**
 * CDP Edge — Bidding Recommendations ML (Fase 2)
 * Handlers das rotas /api/bidding/*
 */

import { tryParseJson } from '../utils.js';
import { Env } from '../../types.js';
import { ExecutionContext } from '@cloudflare/workers-types';

// ── Constantes de calibração ──────────────────────────────────────────────────
const PLATFORM_FACTORS: Record<string, number> = { meta: 0.85, google: 0.90, tiktok: 0.75 };

function getSegmentMultiplier(avgLtvClass: string | number, avgBehaviorScore: string | number): number {
  const ltv = parseFloat(String(avgLtvClass) || '0');
  const eng = parseFloat(String(avgBehaviorScore) || '0');
  if (ltv >= 0.7 && eng >= 0.7)  return 1.4;
  if (ltv >= 0.7 && eng >= 0.4)  return 1.2;
  if (ltv >= 0.4 && eng >= 0.7)  return 1.0;
  if (ltv >= 0.4 && eng >= 0.4)  return 0.8;
  return 0.6;
}

function getConfidenceAdjustment(confidence: number): number {
  if (confidence >= 0.7) return 1.00;
  if (confidence >= 0.4) return 0.85;
  return 0.70;
}

// ── POST /api/bidding/recommend ───────────────────────────────────────────────
export async function handleBiddingRecommend(env: Env, request: Request, headers: Headers): Promise<Response> {
  if (!env.DB) return new Response(JSON.stringify({ error: 'DB não configurado' }), { status: 503, headers });

  let body: any;
  try { body = await request.json(); }
  catch { return new Response(JSON.stringify({ error: 'JSON inválido no body' }), { status: 400, headers }); }

  const {
    vertical    = 'geral', platform    = 'meta',
    target_roi  = 3.5,    period_days = 30,
    campaign_id = null,   budget      = null,
  } = body;

  const platforms = platform === 'all'
    ? Object.keys(PLATFORM_FACTORS)
    : [platform].filter(p => PLATFORM_FACTORS[p]);

  if (platforms.length === 0) {
    return new Response(JSON.stringify({ error: 'platform deve ser: meta, google, tiktok ou all' }), { status: 400, headers });
  }
  if (target_roi < 1 || target_roi > 20) {
    return new Response(JSON.stringify({ error: 'target_roi deve estar entre 1 e 20' }), { status: 400, headers });
  }

  try {
    const segmentsRes = await env.DB.prepare(`
      SELECT ms.id AS segment_id, ms.cluster_name, ms.avg_ltv_class, ms.avg_behavior_score,
             ms.avg_engagement_score, ms.silhouette_score,
             COUNT(msm.id) AS member_count,
             AVG(l.predicted_ltv) AS real_avg_ltv,
             SUM(CASE WHEN l.event_name IN ('Purchase','CompletePayment') THEN 1 ELSE 0 END) AS conversions
      FROM ml_segments ms
      LEFT JOIN ml_segment_members msm ON msm.cluster_id = ms.id
      LEFT JOIN leads l ON CAST(msm.lead_id AS INTEGER) = l.id
        AND l.created_at >= datetime('now', '-' || ? || ' days')
      WHERE ms.is_active = 1 AND ms.client_vertical IN (?, 'general')
      GROUP BY ms.id
      HAVING member_count > 0
      ORDER BY real_avg_ltv DESC
      LIMIT 10
    `).bind(period_days, vertical).all();

    const segments = segmentsRes.results || [];

    let globalLtv = 0, globalLeads = 0, globalConversions = 0;
    if (segments.length === 0) {
      const globalRes = await env.DB.prepare(`
        SELECT COUNT(*) AS total_leads, AVG(predicted_ltv) AS avg_ltv,
               SUM(CASE WHEN event_name IN ('Purchase','CompletePayment') THEN 1 ELSE 0 END) AS conversions
        FROM leads
        WHERE created_at >= datetime('now', '-' || ? || ' days')
          AND (bot_score IS NULL OR bot_score < 2)
      `).bind(period_days).first();

      globalLeads       = Number((globalRes as any)?.total_leads)  || 0;
      globalLtv         = Number((globalRes as any)?.avg_ltv)       || 0;
      globalConversions = Number((globalRes as any)?.conversions)    || 0;

      if (globalLeads < 10) {
        return new Response(JSON.stringify({
          error: `Dados insuficientes. Apenas ${globalLeads} leads no período de ${period_days} dias. Mínimo: 10.`,
          leads_found: globalLeads, required: 10,
        }), { status: 400, headers });
      }
    }

    const now = new Date().toISOString();
    const recommendations: any[] = [];

    const targetSegments = segments.length > 0
      ? segments
      : [{ segment_id: null, cluster_name: 'Global (sem segmentação)', avg_ltv_class: 0.5, avg_behavior_score: 0.5, avg_engagement_score: 0.5, member_count: globalLeads, real_avg_ltv: globalLtv, conversions: globalConversions }];

    for (const seg of targetSegments) {
      const avgLtv     = parseFloat(String(seg.real_avg_ltv) || '0');
      const convs      = parseInt(String(seg.conversions || '0'));
      const confidence = Math.min(1, convs / 100);

      const estimatedLtv = avgLtv > 0 ? avgLtv :
        Number(seg.avg_ltv_class) >= 0.7 ? 497 : Number(seg.avg_ltv_class) >= 0.4 ? 297 : 97;

      const cpaTarget    = estimatedLtv / target_roi;
      const segMult      = getSegmentMultiplier(String(seg.avg_ltv_class), String(seg.avg_behavior_score));
      const confAdj      = getConfidenceAdjustment(confidence);
      const alertMsg     = convs < 30 ? `Atenção: apenas ${convs} conversões no período. Bid baseado em estimativa de LTV — aplique com cautela.` : null;

      for (const plat of platforms) {
        const platFactor     = PLATFORM_FACTORS[plat] || 0.8;
        const recommendedBid = Math.max(5, cpaTarget * platFactor * segMult * confAdj);
        const expectedRoi    = estimatedLtv / (recommendedBid / platFactor);

        const reasoning = `Segmento "${seg.cluster_name}": LTV=${estimatedLtv.toFixed(0)} BRL, ` +
          `CPA_alvo=${cpaTarget.toFixed(0)} BRL, fator_plataforma=${platFactor}, ` +
          `mult_segmento=${segMult}, ajuste_confiança=${confAdj}, ` +
          `base: ${convs} conversões em ${period_days} dias.`;

        try {
          await env.DB.prepare(`
            INSERT INTO bid_recommendations (
              generated_at, vertical, platform, period_days, target_roi,
              segment_id, segment_name, leads_analyzed, conversions_found,
              avg_ltv, cpa_target, recommended_bid, bid_currency,
              confidence, expected_roi, reasoning, ai_used, alert_message,
              platform_factor, confidence_adjustment, segment_multiplier, is_active
            ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0,?,?,?,?,1)
          `).bind(
            now, vertical, plat, period_days, target_roi,
            seg.segment_id || null, seg.cluster_name,
            seg.member_count || globalLeads, convs,
            estimatedLtv, cpaTarget, recommendedBid, 'BRL',
            confidence, expectedRoi, reasoning, alertMsg || null,
            platFactor, confAdj, segMult,
          ).run();
        } catch (e: any) { console.error('[Bidding] D1 insert error:', e?.message || String(e)); }

        recommendations.push({
          platform: plat, segment: seg.cluster_name, segment_id: seg.segment_id || null,
          avg_ltv: Math.round(estimatedLtv * 100) / 100,
          avg_ltv_class: Number(seg.avg_ltv_class) >= 0.7 ? 'High' : Number(seg.avg_ltv_class) >= 0.4 ? 'Medium' : 'Low',
          cpa_target: Math.round(cpaTarget * 100) / 100,
          recommended_bid: Math.round(recommendedBid * 100) / 100,
          bid_currency: 'BRL', confidence: Math.round(confidence * 100) / 100,
          expected_roi: Math.round(expectedRoi * 100) / 100, reasoning, alert: alertMsg,
        });
      }
    }

    await env.DB.prepare(`UPDATE bid_recommendations SET is_active = 0 WHERE vertical = ? AND generated_at < ? AND is_active = 1`).bind(vertical, now).run().catch(() => {});

    const avgConfidence = recommendations.length > 0
      ? recommendations.reduce((s, r) => s + r.confidence, 0) / recommendations.length
      : 0;

    return new Response(JSON.stringify({
      success: true, generated_at: now, vertical, period_days, target_roi,
      data_quality: {
        leads_analyzed: targetSegments.reduce((s: number, sg: any) => s + (sg.member_count || 0), 0),
        conversions_found: targetSegments.reduce((s: number, sg: any) => s + (sg.conversions || 0), 0),
        segments_active: segments.length, confidence: Math.round(avgConfidence * 100) / 100,
      },
      recommendations,
      global_summary: {
        total_recommendations: recommendations.length,
        avg_confidence: Math.round(avgConfidence * 100) / 100,
        expected_cost_reduction: avgConfidence >= 0.7 ? '-20%' : avgConfidence >= 0.4 ? '-10%' : 'indefinido (dados insuficientes)',
        segments_analyzed: segments.length,
      },
    }), { status: 200, headers });

  } catch (err: any) {
    console.error('[Bidding] recommend error:', err?.message || String(err));
    return new Response(JSON.stringify({ error: 'Erro ao gerar recomendações', message: err?.message || String(err) }), { status: 500, headers });
  }
}

// ── GET /api/bidding/history ──────────────────────────────────────────────────
export async function handleBiddingHistory(env: Env, request: Request, headers: Headers): Promise<Response> {
  if (!env.DB) return new Response(JSON.stringify({ error: 'DB não configurado' }), { status: 503, headers });

  const url      = new URL(request.url);
  const vertical = url.searchParams.get('vertical') || null;
  const platform = url.searchParams.get('platform') || null;
  const limit    = Math.min(parseInt(url.searchParams.get('limit') || '20'), 100);

  try {
    const conditions: string[] = [];
    const bindings: (string | number)[]   = [];
    if (vertical) { conditions.push('vertical = ?');  bindings.push(vertical); }
    if (platform) { conditions.push('platform = ?');  bindings.push(platform); }
    const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

    const result = await env.DB.prepare(`
      SELECT id, generated_at, vertical, platform, period_days, target_roi,
             segment_name, leads_analyzed, conversions_found, avg_ltv, cpa_target,
             recommended_bid, bid_currency, confidence, expected_roi,
             reasoning, alert_message, ai_used, is_active,
             applied_at, applied_campaign, applied_result
      FROM bid_recommendations
      ${where}
      ORDER BY generated_at DESC
      LIMIT ?
    `).bind(...bindings, limit).all();

    const items = (result.results || []).map((r: any) => ({ ...r, applied_result: tryParseJson(r.applied_result, null) }));
    return new Response(JSON.stringify({ success: true, total: items.length, history: items }), { status: 200, headers });
  } catch (err: any) {
    console.error('[Bidding] history error:', err?.message || String(err));
    return new Response(JSON.stringify({ error: err?.message || String(err) }), { status: 500, headers });
  }
}

// ── GET /api/bidding/status ───────────────────────────────────────────────────
export async function handleBiddingStatus(env: Env, request: Request, headers: Headers): Promise<Response> {
  if (!env.DB) return new Response(JSON.stringify({ error: 'DB não configurado' }), { status: 503, headers });

  const url      = new URL(request.url);
  const vertical = url.searchParams.get('vertical') || null;

  try {
    let query = `
      SELECT platform, vertical, MAX(generated_at) as last_generated,
             AVG(confidence) as avg_confidence, AVG(recommended_bid) as avg_bid,
             COUNT(*) as recommendations_count,
             SUM(CASE WHEN alert_message IS NOT NULL THEN 1 ELSE 0 END) as alerts_count
      FROM bid_recommendations
      WHERE is_active = 1
    `;
    const bindings: (string | number)[] = [];
    if (vertical) { query += ' AND vertical = ?'; bindings.push(vertical); }
    query += ' GROUP BY platform, vertical ORDER BY last_generated DESC';

    const result = await env.DB.prepare(query).bind(...bindings).all();
    return new Response(JSON.stringify({ success: true, status: result.results || [] }), { status: 200, headers });
  } catch (err: any) {
    console.error('[Bidding] status error:', err?.message || String(err));
    return new Response(JSON.stringify({ error: err?.message || String(err) }), { status: 500, headers });
  }
}