/**
 * CDP Edge — ROAS Feedback Loop (Fase 7)
 *
 * Cruza dados de leads (UTM) com compras confirmadas (Purchase events) no D1
 * para calcular qualidade real de cada campanha:
 *   → revenue_per_lead, conversion_rate, ltv_accuracy
 *   → alimenta bidding.ts com dados reais de ROAS
 *   → relatório semanal via CallMeBot
 */

import { Env } from '../../types.js';
import { sendIntelligenceAlert } from '../intelligence.js';

// ── Tipos ─────────────────────────────────────────────────────────────────────

export interface CampaignRoas {
  utm_source:       string;
  utm_campaign:     string;
  utm_content:      string;   // origem do lead: quiz_*, video_*, landing_*, ctwa_*
  total_leads:      number;
  confirmed_buyers: number;
  conversion_rate:  number;   // 0.0–1.0
  total_revenue:    number;   // soma de value dos Purchase events
  revenue_per_lead: number;   // total_revenue / total_leads
  avg_ltv_score:    number;   // média do ltvScore predito (valida accuracy do modelo)
  ltv_accuracy:     number;   // % de leads com ltvClass=High que realmente compraram
  top_qualification: string;  // qualificação quiz mais frequente nessa campanha
  bid_recommendation: 'increase' | 'maintain' | 'decrease' | 'pause';
}

export interface RoasReport {
  generated_at:    string;
  period_days:     number;
  campaigns:       CampaignRoas[];
  total_revenue:   number;
  total_leads:     number;
  best_campaign:   string | null;
  worst_campaign:  string | null;
  model_accuracy:  number;   // % geral do modelo LTV
}

// ── computeRoasFeedback — calcula ROAS por campanha ───────────────────────────

export async function computeRoasFeedback(
  env: Env,
  periodDays = 30,
): Promise<RoasReport | null> {
  if (!env.DB) return null;

  try {
    // Leads com UTM no período + seu LTV predito
    const leadsRows = await env.DB.prepare(`
      SELECT
        COALESCE(l.utm_source,   'direct')   AS utm_source,
        COALESCE(l.utm_campaign, 'unknown')  AS utm_campaign,
        COALESCE(l.utm_content,  'unknown')  AS utm_content,
        l.user_id,
        l.value                              AS predicted_value,
        l.intention_level,
        up.predicted_ltv_class,
        qs.qualification                     AS quiz_qualification
      FROM leads l
      LEFT JOIN user_profiles up ON up.user_id = l.user_id
      LEFT JOIN quiz_sessions  qs ON qs.user_id = l.user_id
      WHERE l.created_at >= datetime('now', '-' || ? || ' days')
        AND l.event_name IN ('Lead','Contact','QuizComplete','CompleteRegistration')
      ORDER BY l.created_at DESC
    `).bind(periodDays).all();

    // Compras confirmadas no mesmo período — JOIN por user_id
    const purchaseRows = await env.DB.prepare(`
      SELECT
        l.utm_source,
        l.utm_campaign,
        l.utm_content,
        l.user_id,
        e.value  AS purchase_value
      FROM events e
      JOIN leads l ON l.user_id = e.user_id
      WHERE e.event_name IN ('Purchase','purchase')
        AND e.created_at >= datetime('now', '-' || ? || ' days')
        AND l.event_name IN ('Lead','Contact','QuizComplete','CompleteRegistration')
    `).bind(periodDays).all();

    const leads    = (leadsRows.results    || []) as any[];
    const purchases = (purchaseRows.results || []) as any[];

    if (leads.length === 0) return null;

    // Indexa compras por user_id para lookup O(1)
    const buyerMap = new Map<string, number>(); // user_id → purchase_value
    for (const p of purchases) {
      const existing = buyerMap.get(p.user_id) || 0;
      buyerMap.set(p.user_id, existing + (parseFloat(String(p.purchase_value || 0))));
    }

    // Agrupa por campanha
    const campaignMap = new Map<string, {
      leads: any[];
      buyers: Set<string>;
      revenue: number;
      highLtvLeads: number;
      highLtvBuyers: number;
      qualifications: Record<string, number>;
    }>();

    for (const lead of leads) {
      const key = `${lead.utm_source}|||${lead.utm_campaign}|||${lead.utm_content || 'unknown'}`;
      if (!campaignMap.has(key)) {
        campaignMap.set(key, {
          leads: [], buyers: new Set(), revenue: 0,
          highLtvLeads: 0, highLtvBuyers: 0,
          qualifications: {},
        });
      }
      const c = campaignMap.get(key)!;
      c.leads.push(lead);

      if (lead.predicted_ltv_class === 'High') c.highLtvLeads++;

      const purchaseValue = buyerMap.get(lead.user_id);
      if (purchaseValue !== undefined) {
        c.buyers.add(lead.user_id);
        c.revenue += purchaseValue;
        if (lead.predicted_ltv_class === 'High') c.highLtvBuyers++;
      }

      const qual = lead.quiz_qualification || lead.intention_level || 'unknown';
      c.qualifications[qual] = (c.qualifications[qual] || 0) + 1;
    }

    // Monta relatório por campanha
    const campaigns: CampaignRoas[] = [];
    for (const [key, c] of campaignMap.entries()) {
      const [utm_source, utm_campaign, utm_content] = key.split('|||');
      const total_leads      = c.leads.length;
      const confirmed_buyers = c.buyers.size;
      const conversion_rate  = total_leads > 0 ? confirmed_buyers / total_leads : 0;
      const revenue_per_lead = total_leads > 0 ? c.revenue / total_leads : 0;
      const avg_ltv_score    = c.leads.reduce((s: number, l: any) => s + parseFloat(String(l.predicted_value || 0)), 0) / total_leads;
      const ltv_accuracy     = c.highLtvLeads > 0 ? c.highLtvBuyers / c.highLtvLeads : 0;

      const top_qualification = Object.entries(c.qualifications)
        .sort((a, b) => b[1] - a[1])[0]?.[0] || 'unknown';

      // Recomendação de bid baseada em conversão + accuracy
      let bid_recommendation: CampaignRoas['bid_recommendation'];
      if (conversion_rate >= 0.15 && ltv_accuracy >= 0.6)      bid_recommendation = 'increase';
      else if (conversion_rate >= 0.05 && ltv_accuracy >= 0.3)  bid_recommendation = 'maintain';
      else if (conversion_rate > 0 && confirmed_buyers > 0)      bid_recommendation = 'decrease';
      else                                                        bid_recommendation = 'pause';

      campaigns.push({
        utm_source, utm_campaign, utm_content,
        total_leads, confirmed_buyers,
        conversion_rate: Math.round(conversion_rate * 10000) / 10000,
        total_revenue: Math.round(c.revenue * 100) / 100,
        revenue_per_lead: Math.round(revenue_per_lead * 100) / 100,
        avg_ltv_score: Math.round(avg_ltv_score * 100) / 100,
        ltv_accuracy: Math.round(ltv_accuracy * 10000) / 10000,
        top_qualification,
        bid_recommendation,
      });
    }

    // Ordena por revenue total desc
    campaigns.sort((a, b) => b.total_revenue - a.total_revenue);

    const total_revenue  = campaigns.reduce((s, c) => s + c.total_revenue, 0);
    const total_leads    = campaigns.reduce((s, c) => s + c.total_leads, 0);
    // model_accuracy: média ponderada de ltv_accuracy por campanha (High LTV que realmente compraram)
    const model_accuracy = campaigns.length > 0
      ? campaigns.reduce((s, c) => s + c.ltv_accuracy * c.total_leads, 0) / Math.max(total_leads, 1)
      : 0;

    const best_campaign  = campaigns.find(c => c.bid_recommendation === 'increase')
      ? `${campaigns[0].utm_source} / ${campaigns[0].utm_campaign}`
      : null;
    const worst_campaign = campaigns.find(c => c.bid_recommendation === 'pause')
      ? (() => { const w = campaigns.find(c => c.bid_recommendation === 'pause')!; return `${w.utm_source} / ${w.utm_campaign}`; })()
      : null;

    // Persiste no D1 para histórico
    await _persistRoasReport(env, campaigns, periodDays);

    return {
      generated_at:   new Date().toISOString(),
      period_days:    periodDays,
      campaigns,
      total_revenue:  Math.round(total_revenue * 100) / 100,
      total_leads,
      best_campaign,
      worst_campaign,
      model_accuracy: Math.round(model_accuracy * 10000) / 10000,
    };

  } catch (err: any) {
    console.error('[ROAS] computeRoasFeedback error:', err?.message || String(err));
    return null;
  }
}

// ── sendRoasAlert — relatório via CallMeBot ───────────────────────────────────

export async function sendRoasAlert(env: Env, report: RoasReport): Promise<void> {
  const top3 = report.campaigns.slice(0, 3);
  const lines = top3.map(c =>
    `• ${c.utm_source}/${c.utm_campaign}/${c.utm_content}: ${c.confirmed_buyers} compradores, R$${c.total_revenue.toLocaleString('pt-BR')} (${(c.conversion_rate * 100).toFixed(1)}% conv) → ${c.bid_recommendation.toUpperCase()}`
  ).join('\n');

  const pauseCount   = report.campaigns.filter(c => c.bid_recommendation === 'pause').length;
  const increaseCount = report.campaigns.filter(c => c.bid_recommendation === 'increase').length;

  const details = [
    `📅 Período: últimos ${report.period_days} dias`,
    `💰 Receita total: R$${report.total_revenue.toLocaleString('pt-BR')}`,
    `👥 Total leads: ${report.total_leads}`,
    `📈 Top campanhas por receita:\n${lines}`,
    increaseCount > 0 ? `✅ ${increaseCount} campanha(s) recomendada(s) para AUMENTAR bid` : '',
    pauseCount > 0    ? `⛔ ${pauseCount} campanha(s) recomendada(s) para PAUSAR` : '',
    report.best_campaign  ? `🏆 Melhor: ${report.best_campaign}` : '',
    report.worst_campaign ? `🔻 Pior:   ${report.worst_campaign}` : '',
  ].filter(Boolean).join('\n');

  await sendIntelligenceAlert(env, 'info', 'ROAS Feedback Semanal', details);
}

// ── _persistRoasReport — salva snapshot no D1 ────────────────────────────────

async function _persistRoasReport(
  env: Env,
  campaigns: CampaignRoas[],
  periodDays: number,
): Promise<void> {
  if (!env.DB) return;
  try {
    for (const c of campaigns) {
      await env.DB.prepare(`
        INSERT INTO roas_reports (
          utm_source, utm_campaign, utm_content, period_days,
          total_leads, confirmed_buyers, conversion_rate,
          total_revenue, revenue_per_lead, ltv_accuracy,
          top_qualification, bid_recommendation, created_at
        ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,datetime('now'))
      `).bind(
        c.utm_source, c.utm_campaign, c.utm_content, periodDays,
        c.total_leads, c.confirmed_buyers, c.conversion_rate,
        c.total_revenue, c.revenue_per_lead, c.ltv_accuracy,
        c.top_qualification, c.bid_recommendation,
      ).run();
    }
  } catch (err: any) {
    console.error('[ROAS] persist error:', err?.message || String(err));
  }
}