/**
 * BidAnalyticsService - Analisis y Reportes de Licitaciones
 *
 * Estadisticas, tendencias y analisis de competitividad.
 * Proporciona dashboards consolidados, win rates, analisis de competidores y pipeline.
 *
 * @module Bidding (MAI-018)
 */

import { Repository } from 'typeorm';
import { ServiceContext } from '../../../shared/services/base.service';
import { Tender, TenderStatus, TenderType } from '../entities/tender.entity';
import { Opportunity, OpportunitySource, OpportunityStatus, OpportunityPriority } from '../entities/opportunity.entity';
import { Proposal } from '../entities/proposal.entity';

// Interfaces for method results
export interface WinRateResult {
  won: number;
  lost: number;
  rate: number;
}

export interface PipelineValueResult {
  totalValue: number;
  byStatus: { status: OpportunityStatus; value: number; count: number }[];
  byPriority: { priority: OpportunityPriority; value: number; count: number }[];
}

export interface CompetitorFrequency {
  companyName: string;
  rfc?: string;
  appearances: number;
  winsAgainstUs: number;
  lossesAgainstUs: number;
  avgProposalDifference: number;
}

export interface AveragesResult {
  avgBidSize: number;
  avgTimeToAward: number;
  avgProposalCount: number;
}

export interface DateRange {
  dateFrom?: Date;
  dateTo?: Date;
}

export class BidAnalyticsService {
  constructor(
    private readonly tenderRepository: Repository<Tender>,
    private readonly opportunityRepository: Repository<Opportunity>,
    private readonly proposalRepository: Repository<Proposal>
  ) {}

  /**
   * Dashboard general de licitaciones
   */
  async getDashboard(ctx: ServiceContext): Promise<BidDashboard> {
    const now = new Date();

    // Oportunidades activas
    const activeOpportunities = await this.opportunityRepository.count({
      where: {
        tenantId: ctx.tenantId,
        deletedAt: undefined,
        status: 'go' as OpportunityStatus,
      },
    });

    // Licitaciones activas
    const activeTenders = await this.tenderRepository.count({
      where: {
        tenantId: ctx.tenantId,
        deletedAt: undefined,
        status: 'published' as TenderStatus,
      },
    });

    // Valor del pipeline
    const pipelineValue = await this.opportunityRepository
      .createQueryBuilder('o')
      .select('SUM(CAST(o.estimated_amount AS DECIMAL))', 'value')
      .where('o.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('o.deleted_at IS NULL')
      .andWhere('o.status NOT IN (:...closedStatuses)', { closedStatuses: ['converted', 'no_go'] })
      .getRawOne();

    // Proximas fechas limite
    const upcomingDeadlines = await this.tenderRepository
      .createQueryBuilder('t')
      .where('t.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('t.deleted_at IS NULL')
      .andWhere('t.status IN (:...activeStatuses)', { activeStatuses: ['draft', 'published', 'receiving', 'evaluating'] })
      .andWhere('t.proposal_deadline >= :now', { now })
      .orderBy('t.proposal_deadline', 'ASC')
      .take(5)
      .getMany();

    // Win rate ultimos 12 meses
    const yearAgo = new Date();
    yearAgo.setFullYear(yearAgo.getFullYear() - 1);

    const winRateStats = await this.tenderRepository
      .createQueryBuilder('t')
      .select('t.status', 'status')
      .addSelect('COUNT(*)', 'count')
      .where('t.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('t.deleted_at IS NULL')
      .andWhere('t.status IN (:...closedStatuses)', { closedStatuses: ['awarded', 'cancelled'] })
      .andWhere('t.award_date >= :yearAgo', { yearAgo })
      .groupBy('t.status')
      .getRawMany();

    const awarded = winRateStats.find((s) => s.status === 'awarded')?.count || 0;
    const cancelled = winRateStats.find((s) => s.status === 'cancelled')?.count || 0;
    const totalClosed = parseInt(awarded) + parseInt(cancelled);
    const winRate = totalClosed > 0 ? (parseInt(awarded) / totalClosed) * 100 : 0;

    // Valor ganado este ano
    const startOfYear = new Date(now.getFullYear(), 0, 1);
    const wonValue = await this.tenderRepository
      .createQueryBuilder('t')
      .select('SUM(CAST(t.reference_amount AS DECIMAL))', 'value')
      .where('t.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('t.deleted_at IS NULL')
      .andWhere('t.status = :status', { status: 'awarded' })
      .andWhere('t.award_date >= :startOfYear', { startOfYear })
      .getRawOne();

    return {
      activeOpportunities,
      activeBids: activeTenders,
      pipelineValue: parseFloat(pipelineValue?.value) || 0,
      upcomingDeadlines: upcomingDeadlines.map((t) => ({
        id: t.id,
        name: t.title,
        deadline: t.proposalDeadline,
        status: t.status,
      })),
      winRate,
      wonValueYTD: parseFloat(wonValue?.value) || 0,
    };
  }

  /**
   * Analisis de pipeline por fuente
   */
  async getPipelineBySource(ctx: ServiceContext): Promise<PipelineBySource[]> {
    const result = await this.opportunityRepository
      .createQueryBuilder('o')
      .select('o.source', 'source')
      .addSelect('COUNT(*)', 'count')
      .addSelect('SUM(CAST(o.estimated_amount AS DECIMAL))', 'totalValue')
      .where('o.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('o.deleted_at IS NULL')
      .andWhere('o.status NOT IN (:...closedStatuses)', { closedStatuses: ['converted', 'no_go'] })
      .groupBy('o.source')
      .orderBy('SUM(CAST(o.estimated_amount AS DECIMAL))', 'DESC')
      .getRawMany();

    return result.map((r) => ({
      source: r.source as OpportunitySource,
      count: parseInt(r.count),
      totalValue: parseFloat(r.totalValue) || 0,
    }));
  }

  /**
   * Analisis de win rate por tipo de licitacion
   */
  async getWinRateByType(ctx: ServiceContext, months = 12): Promise<WinRateByType[]> {
    const fromDate = new Date();
    fromDate.setMonth(fromDate.getMonth() - months);

    const result = await this.tenderRepository
      .createQueryBuilder('t')
      .select('t.type', 'type')
      .addSelect('COUNT(*) FILTER (WHERE t.status = \'awarded\')', 'won')
      .addSelect('COUNT(*) FILTER (WHERE t.status IN (\'awarded\', \'cancelled\'))', 'total')
      .addSelect('SUM(CASE WHEN t.status = \'awarded\' THEN CAST(t.reference_amount AS DECIMAL) ELSE 0 END)', 'wonValue')
      .where('t.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('t.deleted_at IS NULL')
      .andWhere('t.award_date >= :fromDate', { fromDate })
      .groupBy('t.type')
      .getRawMany();

    return result.map((r) => ({
      bidType: r.type as TenderType,
      won: parseInt(r.won) || 0,
      total: parseInt(r.total) || 0,
      winRate: parseInt(r.total) > 0 ? (parseInt(r.won) / parseInt(r.total)) * 100 : 0,
      wonValue: parseFloat(r.wonValue) || 0,
    }));
  }

  /**
   * Tendencia mensual de oportunidades
   */
  async getMonthlyTrend(ctx: ServiceContext, months = 12): Promise<MonthlyTrend[]> {
    const fromDate = new Date();
    fromDate.setMonth(fromDate.getMonth() - months);

    const result = await this.opportunityRepository
      .createQueryBuilder('o')
      .select("TO_CHAR(o.created_at, 'YYYY-MM')", 'month')
      .addSelect('COUNT(*)', 'identified')
      .addSelect('COUNT(*) FILTER (WHERE o.status = \'converted\')', 'won')
      .addSelect('COUNT(*) FILTER (WHERE o.status = \'no_go\')', 'lost')
      .addSelect('SUM(CASE WHEN o.status = \'converted\' THEN CAST(o.estimated_amount AS DECIMAL) ELSE 0 END)', 'wonValue')
      .where('o.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('o.deleted_at IS NULL')
      .andWhere('o.created_at >= :fromDate', { fromDate })
      .groupBy("TO_CHAR(o.created_at, 'YYYY-MM')")
      .orderBy("TO_CHAR(o.created_at, 'YYYY-MM')", 'ASC')
      .getRawMany();

    return result.map((r) => ({
      month: r.month,
      identified: parseInt(r.identified) || 0,
      won: parseInt(r.won) || 0,
      lost: parseInt(r.lost) || 0,
      wonValue: parseFloat(r.wonValue) || 0,
    }));
  }

  /**
   * Analisis de competidores
   */
  async getCompetitorAnalysis(ctx: ServiceContext): Promise<CompetitorAnalysis[]> {
    const result = await this.proposalRepository
      .createQueryBuilder('p')
      .leftJoin('p.vendor', 'v')
      .select('v.business_name', 'companyName')
      .addSelect('COUNT(*)', 'encounters')
      .addSelect('SUM(CASE WHEN p.status = \'winner\' THEN 1 ELSE 0 END)', 'theirWins')
      .addSelect('SUM(CASE WHEN p.status != \'winner\' AND p.status = \'qualified\' THEN 1 ELSE 0 END)', 'ourWins')
      .addSelect('AVG(CAST(p.proposed_amount AS DECIMAL))', 'avgProposedAmount')
      .where('p.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('p.deleted_at IS NULL')
      .groupBy('v.business_name')
      .having('COUNT(*) >= 2')
      .orderBy('COUNT(*)', 'DESC')
      .take(20)
      .getRawMany();

    return result.map((r) => ({
      companyName: r.companyName,
      encounters: parseInt(r.encounters) || 0,
      theirWins: parseInt(r.theirWins) || 0,
      ourWins: parseInt(r.ourWins) || 0,
      winRateAgainst: parseInt(r.encounters) > 0
        ? (parseInt(r.ourWins) / parseInt(r.encounters)) * 100
        : 0,
      avgProposedAmount: parseFloat(r.avgProposedAmount) || 0,
    }));
  }

  /**
   * Analisis de conversion del funnel
   */
  async getFunnelAnalysis(ctx: ServiceContext, months = 12): Promise<FunnelAnalysis> {
    const fromDate = new Date();
    fromDate.setMonth(fromDate.getMonth() - months);

    const baseQuery = this.opportunityRepository
      .createQueryBuilder('o')
      .where('o.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('o.deleted_at IS NULL')
      .andWhere('o.created_at >= :fromDate', { fromDate });

    const identified = await baseQuery.clone().getCount();

    const evaluating = await baseQuery.clone()
      .andWhere('o.status NOT IN (:...earlyStatuses)', { earlyStatuses: ['registered'] })
      .getCount();

    const go = await baseQuery.clone()
      .andWhere('o.status IN (:...goStatuses)', { goStatuses: ['go', 'preparing', 'converted'] })
      .getCount();

    const preparing = await baseQuery.clone()
      .andWhere('o.status IN (:...prepStatuses)', { prepStatuses: ['preparing', 'converted'] })
      .getCount();

    const converted = await baseQuery.clone()
      .andWhere('o.status = :status', { status: 'converted' })
      .getCount();

    return {
      identified,
      qualified: evaluating,
      pursuing: go,
      bidSubmitted: preparing,
      won: converted,
      conversionRates: {
        identifiedToQualified: identified > 0 ? (evaluating / identified) * 100 : 0,
        qualifiedToPursuing: evaluating > 0 ? (go / evaluating) * 100 : 0,
        pursuingToSubmitted: go > 0 ? (preparing / go) * 100 : 0,
        submittedToWon: preparing > 0 ? (converted / preparing) * 100 : 0,
        overallConversion: identified > 0 ? (converted / identified) * 100 : 0,
      },
    };
  }

  /**
   * Analisis de tiempos de ciclo
   */
  async getCycleTimeAnalysis(ctx: ServiceContext, months = 12): Promise<CycleTimeAnalysis> {
    const fromDate = new Date();
    fromDate.setMonth(fromDate.getMonth() - months);

    const result = await this.opportunityRepository
      .createQueryBuilder('o')
      .select('AVG(EXTRACT(DAY FROM (o.updated_at - o.created_at)))', 'avgDays')
      .addSelect('MIN(EXTRACT(DAY FROM (o.updated_at - o.created_at)))', 'minDays')
      .addSelect('MAX(EXTRACT(DAY FROM (o.updated_at - o.created_at)))', 'maxDays')
      .where('o.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('o.deleted_at IS NULL')
      .andWhere('o.status IN (:...closedStatuses)', { closedStatuses: ['converted', 'no_go'] })
      .andWhere('o.created_at >= :fromDate', { fromDate })
      .getRawOne();

    const byOutcome = await this.opportunityRepository
      .createQueryBuilder('o')
      .select('o.status', 'outcome')
      .addSelect('AVG(EXTRACT(DAY FROM (o.updated_at - o.created_at)))', 'avgDays')
      .where('o.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('o.deleted_at IS NULL')
      .andWhere('o.status IN (:...closedStatuses)', { closedStatuses: ['converted', 'no_go'] })
      .andWhere('o.created_at >= :fromDate', { fromDate })
      .groupBy('o.status')
      .getRawMany();

    return {
      overall: {
        avgDays: Math.round(parseFloat(result?.avgDays) || 0),
        minDays: Math.round(parseFloat(result?.minDays) || 0),
        maxDays: Math.round(parseFloat(result?.maxDays) || 0),
      },
      byOutcome: byOutcome.map((r) => ({
        outcome: r.outcome === 'converted' ? 'won' : 'lost',
        avgDays: Math.round(parseFloat(r.avgDays) || 0),
      })),
    };
  }

  /**
   * Obtener win rate con filtros de fecha opcionales
   */
  async getWinRate(ctx: ServiceContext, filters?: DateRange): Promise<WinRateResult> {
    const qb = this.tenderRepository
      .createQueryBuilder('t')
      .select('t.status', 'status')
      .addSelect('COUNT(*)', 'count')
      .where('t.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('t.deleted_at IS NULL')
      .andWhere('t.status IN (:...closedStatuses)', { closedStatuses: ['awarded', 'cancelled'] });

    if (filters?.dateFrom) {
      qb.andWhere('t.award_date >= :dateFrom', { dateFrom: filters.dateFrom });
    }
    if (filters?.dateTo) {
      qb.andWhere('t.award_date <= :dateTo', { dateTo: filters.dateTo });
    }

    const results = await qb.groupBy('t.status').getRawMany();

    const won = parseInt(results.find(r => r.status === 'awarded')?.count || '0', 10);
    const lost = parseInt(results.find(r => r.status === 'cancelled')?.count || '0', 10);
    const total = won + lost;

    return {
      won,
      lost,
      rate: total > 0 ? (won / total) * 100 : 0,
    };
  }

  /**
   * Obtener valor del pipeline por status y prioridad
   */
  async getPipelineValue(ctx: ServiceContext): Promise<PipelineValueResult> {
    const activeStatuses = ['registered', 'evaluating', 'go', 'preparing'];

    // Total value
    const totalResult = await this.opportunityRepository
      .createQueryBuilder('o')
      .select('SUM(CAST(o.estimated_amount AS DECIMAL))', 'totalValue')
      .where('o.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('o.deleted_at IS NULL')
      .andWhere('o.status IN (:...statuses)', { statuses: activeStatuses })
      .getRawOne();

    // By status
    const byStatusResults = await this.opportunityRepository
      .createQueryBuilder('o')
      .select('o.status', 'status')
      .addSelect('SUM(CAST(o.estimated_amount AS DECIMAL))', 'value')
      .addSelect('COUNT(*)', 'count')
      .where('o.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('o.deleted_at IS NULL')
      .andWhere('o.status IN (:...statuses)', { statuses: activeStatuses })
      .groupBy('o.status')
      .getRawMany();

    // By priority
    const byPriorityResults = await this.opportunityRepository
      .createQueryBuilder('o')
      .select('o.priority', 'priority')
      .addSelect('SUM(CAST(o.estimated_amount AS DECIMAL))', 'value')
      .addSelect('COUNT(*)', 'count')
      .where('o.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('o.deleted_at IS NULL')
      .andWhere('o.status IN (:...statuses)', { statuses: activeStatuses })
      .groupBy('o.priority')
      .getRawMany();

    return {
      totalValue: parseFloat(totalResult?.totalValue) || 0,
      byStatus: byStatusResults.map(r => ({
        status: r.status as OpportunityStatus,
        value: parseFloat(r.value) || 0,
        count: parseInt(r.count, 10),
      })),
      byPriority: byPriorityResults.map(r => ({
        priority: r.priority as OpportunityPriority,
        value: parseFloat(r.value) || 0,
        count: parseInt(r.count, 10),
      })),
    };
  }

  /**
   * Analisis de competidores mas frecuentes
   */
  async getCompetitorFrequency(ctx: ServiceContext, filters?: DateRange): Promise<CompetitorFrequency[]> {
    const qb = this.proposalRepository
      .createQueryBuilder('p')
      .leftJoin('p.vendor', 'v')
      .select('v.business_name', 'companyName')
      .addSelect('v.rfc', 'rfc')
      .addSelect('COUNT(*)', 'appearances')
      .addSelect("SUM(CASE WHEN p.status = 'winner' THEN 1 ELSE 0 END)", 'winsAgainstUs')
      .addSelect("SUM(CASE WHEN p.status != 'winner' AND p.status = 'qualified' THEN 1 ELSE 0 END)", 'lossesAgainstUs')
      .addSelect('AVG(CAST(p.proposed_amount AS DECIMAL))', 'avgProposedAmount')
      .where('p.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('p.deleted_at IS NULL');

    if (filters?.dateFrom || filters?.dateTo) {
      qb.innerJoin('p.tender', 't');
      if (filters.dateFrom) {
        qb.andWhere('t.created_at >= :dateFrom', { dateFrom: filters.dateFrom });
      }
      if (filters.dateTo) {
        qb.andWhere('t.created_at <= :dateTo', { dateTo: filters.dateTo });
      }
    }

    const results = await qb
      .groupBy('v.business_name')
      .addGroupBy('v.rfc')
      .orderBy('COUNT(*)', 'DESC')
      .limit(20)
      .getRawMany();

    return results.map(r => ({
      companyName: r.companyName,
      rfc: r.rfc,
      appearances: parseInt(r.appearances, 10),
      winsAgainstUs: parseInt(r.winsAgainstUs, 10),
      lossesAgainstUs: parseInt(r.lossesAgainstUs, 10),
      avgProposalDifference: 0,
    }));
  }

  /**
   * Obtener promedios de licitaciones
   */
  async getAverages(ctx: ServiceContext): Promise<AveragesResult> {
    // Average bid size
    const bidSizeResult = await this.tenderRepository
      .createQueryBuilder('t')
      .select('AVG(CAST(t.reference_amount AS DECIMAL))', 'avgBidSize')
      .where('t.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('t.deleted_at IS NULL')
      .andWhere('t.reference_amount IS NOT NULL')
      .getRawOne();

    // Average time to award (from proposal deadline to award)
    const timeToAwardResult = await this.tenderRepository
      .createQueryBuilder('t')
      .select('AVG(EXTRACT(DAY FROM (t.award_date - t.proposal_deadline)))', 'avgDays')
      .where('t.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('t.deleted_at IS NULL')
      .andWhere('t.status IN (:...closedStatuses)', { closedStatuses: ['awarded', 'cancelled'] })
      .andWhere('t.award_date IS NOT NULL')
      .getRawOne();

    // Average proposal count per tender
    const proposalCountResult = await this.proposalRepository
      .createQueryBuilder('p')
      .select('AVG(cnt)', 'avgCount')
      .from(subQuery => {
        return subQuery
          .select('p2.tender_id', 'tenderId')
          .addSelect('COUNT(*)', 'cnt')
          .from('bidding.proposals', 'p2')
          .where('p2.tenant_id = :tenantId', { tenantId: ctx.tenantId })
          .andWhere('p2.deleted_at IS NULL')
          .groupBy('p2.tender_id');
      }, 'counts')
      .getRawOne();

    return {
      avgBidSize: parseFloat(bidSizeResult?.avgBidSize) || 0,
      avgTimeToAward: Math.round(parseFloat(timeToAwardResult?.avgDays) || 0),
      avgProposalCount: parseFloat(proposalCountResult?.avgCount) || 0,
    };
  }

  /**
   * Obtener oportunidades por fuente
   */
  async getOpportunitiesBySource(
    ctx: ServiceContext,
    dateFrom?: Date,
    dateTo?: Date
  ): Promise<{ source: OpportunitySource; count: number; value: number; winRate: number }[]> {
    const qb = this.opportunityRepository
      .createQueryBuilder('o')
      .select('o.source', 'source')
      .addSelect('COUNT(*)', 'count')
      .addSelect('SUM(CAST(o.estimated_amount AS DECIMAL))', 'value')
      .addSelect("COUNT(*) FILTER (WHERE o.status = 'converted')", 'won')
      .addSelect("COUNT(*) FILTER (WHERE o.status IN ('converted', 'no_go'))", 'closed')
      .where('o.tenant_id = :tenantId', { tenantId: ctx.tenantId })
      .andWhere('o.deleted_at IS NULL');

    if (dateFrom) {
      qb.andWhere('o.created_at >= :dateFrom', { dateFrom });
    }
    if (dateTo) {
      qb.andWhere('o.created_at <= :dateTo', { dateTo });
    }

    const results = await qb.groupBy('o.source').getRawMany();

    return results.map(r => ({
      source: r.source as OpportunitySource,
      count: parseInt(r.count, 10),
      value: parseFloat(r.value) || 0,
      winRate: parseInt(r.closed, 10) > 0
        ? (parseInt(r.won, 10) / parseInt(r.closed, 10)) * 100
        : 0,
    }));
  }
}

// Types
export interface BidDashboard {
  activeOpportunities: number;
  activeBids: number;
  pipelineValue: number;
  upcomingDeadlines: { id: string; name: string; deadline: Date; status: TenderStatus }[];
  winRate: number;
  wonValueYTD: number;
}

export interface PipelineBySource {
  source: OpportunitySource;
  count: number;
  totalValue: number;
}

export interface WinRateByType {
  bidType: TenderType;
  won: number;
  total: number;
  winRate: number;
  wonValue: number;
}

export interface MonthlyTrend {
  month: string;
  identified: number;
  won: number;
  lost: number;
  wonValue: number;
}

export interface CompetitorAnalysis {
  companyName: string;
  encounters: number;
  theirWins: number;
  ourWins: number;
  winRateAgainst: number;
  avgProposedAmount: number;
}

export interface FunnelAnalysis {
  identified: number;
  qualified: number;
  pursuing: number;
  bidSubmitted: number;
  won: number;
  conversionRates: {
    identifiedToQualified: number;
    qualifiedToPursuing: number;
    pursuingToSubmitted: number;
    submittedToWon: number;
    overallConversion: number;
  };
}

export interface CycleTimeAnalysis {
  overall: {
    avgDays: number;
    minDays: number;
    maxDays: number;
  };
  byOutcome: {
    outcome: 'won' | 'lost';
    avgDays: number;
  }[];
}
