import { query, queryOne } from '../../config/database.js';
import { logger } from '../../shared/utils/logger.js';

// ============================================================================
// TYPES
// ============================================================================

export interface ForecastPeriod {
  period: string; // YYYY-MM or YYYY-QN
  expected_revenue: number;
  weighted_revenue: number;
  opportunity_count: number;
  avg_probability: number;
  won_revenue?: number;
  won_count?: number;
  lost_revenue?: number;
  lost_count?: number;
}

export interface SalesForecast {
  total_pipeline: number;
  weighted_pipeline: number;
  expected_close_this_month: number;
  expected_close_this_quarter: number;
  opportunities_count: number;
  avg_deal_size: number;
  avg_probability: number;
  periods: ForecastPeriod[];
}

export interface WinLossAnalysis {
  period: string;
  won_count: number;
  won_revenue: number;
  lost_count: number;
  lost_revenue: number;
  win_rate: number;
  avg_won_deal_size: number;
  avg_lost_deal_size: number;
}

export interface PipelineMetrics {
  total_opportunities: number;
  total_value: number;
  by_stage: {
    stage_id: string;
    stage_name: string;
    sequence: number;
    count: number;
    value: number;
    weighted_value: number;
    avg_probability: number;
  }[];
  by_user: {
    user_id: string;
    user_name: string;
    count: number;
    value: number;
    weighted_value: number;
  }[];
  avg_days_in_stage: number;
  avg_sales_cycle_days: number;
}

export interface ForecastFilters {
  company_id?: string;
  user_id?: string;
  sales_team_id?: string;
  date_from?: string;
  date_to?: string;
}

// ============================================================================
// SERVICE
// ============================================================================

class ForecastingService {
  /**
   * Get sales forecast for the pipeline
   */
  async getSalesForecast(
    tenantId: string,
    filters: ForecastFilters = {}
  ): Promise<SalesForecast> {
    const { company_id, user_id, sales_team_id } = filters;

    let whereClause = `WHERE o.tenant_id = $1 AND o.status = 'open'`;
    const params: any[] = [tenantId];
    let paramIndex = 2;

    if (company_id) {
      whereClause += ` AND o.company_id = $${paramIndex++}`;
      params.push(company_id);
    }

    if (user_id) {
      whereClause += ` AND o.user_id = $${paramIndex++}`;
      params.push(user_id);
    }

    if (sales_team_id) {
      whereClause += ` AND o.sales_team_id = $${paramIndex++}`;
      params.push(sales_team_id);
    }

    // Get overall metrics
    const metrics = await queryOne<{
      total_pipeline: string;
      weighted_pipeline: string;
      count: string;
      avg_probability: string;
    }>(
      `SELECT
         COALESCE(SUM(expected_revenue), 0) as total_pipeline,
         COALESCE(SUM(expected_revenue * probability / 100), 0) as weighted_pipeline,
         COUNT(*) as count,
         COALESCE(AVG(probability), 0) as avg_probability
       FROM crm.opportunities o
       ${whereClause}`,
      params
    );

    // Get expected close this month
    const thisMonthParams = [...params];
    const thisMonth = await queryOne<{ expected: string }>(
      `SELECT COALESCE(SUM(expected_revenue * probability / 100), 0) as expected
       FROM crm.opportunities o
       ${whereClause}
       AND date_deadline >= DATE_TRUNC('month', CURRENT_DATE)
       AND date_deadline < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'`,
      thisMonthParams
    );

    // Get expected close this quarter
    const thisQuarterParams = [...params];
    const thisQuarter = await queryOne<{ expected: string }>(
      `SELECT COALESCE(SUM(expected_revenue * probability / 100), 0) as expected
       FROM crm.opportunities o
       ${whereClause}
       AND date_deadline >= DATE_TRUNC('quarter', CURRENT_DATE)
       AND date_deadline < DATE_TRUNC('quarter', CURRENT_DATE) + INTERVAL '3 months'`,
      thisQuarterParams
    );

    // Get periods (next 6 months)
    const periods = await query<ForecastPeriod>(
      `SELECT
         TO_CHAR(DATE_TRUNC('month', COALESCE(date_deadline, CURRENT_DATE + INTERVAL '1 month')), 'YYYY-MM') as period,
         COALESCE(SUM(expected_revenue), 0) as expected_revenue,
         COALESCE(SUM(expected_revenue * probability / 100), 0) as weighted_revenue,
         COUNT(*) as opportunity_count,
         COALESCE(AVG(probability), 0) as avg_probability
       FROM crm.opportunities o
       ${whereClause}
       AND (date_deadline IS NULL OR date_deadline >= CURRENT_DATE)
       AND (date_deadline IS NULL OR date_deadline < CURRENT_DATE + INTERVAL '6 months')
       GROUP BY DATE_TRUNC('month', COALESCE(date_deadline, CURRENT_DATE + INTERVAL '1 month'))
       ORDER BY period`,
      params
    );

    const totalPipeline = parseFloat(metrics?.total_pipeline || '0');
    const count = parseInt(metrics?.count || '0', 10);

    return {
      total_pipeline: totalPipeline,
      weighted_pipeline: parseFloat(metrics?.weighted_pipeline || '0'),
      expected_close_this_month: parseFloat(thisMonth?.expected || '0'),
      expected_close_this_quarter: parseFloat(thisQuarter?.expected || '0'),
      opportunities_count: count,
      avg_deal_size: count > 0 ? totalPipeline / count : 0,
      avg_probability: parseFloat(metrics?.avg_probability || '0'),
      periods,
    };
  }

  /**
   * Get win/loss analysis for reporting
   */
  async getWinLossAnalysis(
    tenantId: string,
    filters: ForecastFilters = {},
    periodType: 'month' | 'quarter' | 'year' = 'month'
  ): Promise<WinLossAnalysis[]> {
    const { company_id, user_id, sales_team_id, date_from, date_to } = filters;

    let whereClause = `WHERE o.tenant_id = $1 AND o.status IN ('won', 'lost')`;
    const params: any[] = [tenantId];
    let paramIndex = 2;

    if (company_id) {
      whereClause += ` AND o.company_id = $${paramIndex++}`;
      params.push(company_id);
    }

    if (user_id) {
      whereClause += ` AND o.user_id = $${paramIndex++}`;
      params.push(user_id);
    }

    if (sales_team_id) {
      whereClause += ` AND o.sales_team_id = $${paramIndex++}`;
      params.push(sales_team_id);
    }

    if (date_from) {
      whereClause += ` AND o.date_closed >= $${paramIndex++}`;
      params.push(date_from);
    }

    if (date_to) {
      whereClause += ` AND o.date_closed <= $${paramIndex++}`;
      params.push(date_to);
    }

    const periodTrunc = periodType === 'year' ? 'year' : periodType === 'quarter' ? 'quarter' : 'month';
    const periodFormat = periodType === 'year' ? 'YYYY' : periodType === 'quarter' ? 'YYYY-"Q"Q' : 'YYYY-MM';

    return query<WinLossAnalysis>(
      `SELECT
         TO_CHAR(DATE_TRUNC('${periodTrunc}', date_closed), '${periodFormat}') as period,
         COUNT(*) FILTER (WHERE status = 'won') as won_count,
         COALESCE(SUM(expected_revenue) FILTER (WHERE status = 'won'), 0) as won_revenue,
         COUNT(*) FILTER (WHERE status = 'lost') as lost_count,
         COALESCE(SUM(expected_revenue) FILTER (WHERE status = 'lost'), 0) as lost_revenue,
         CASE
           WHEN COUNT(*) > 0
           THEN ROUND(COUNT(*) FILTER (WHERE status = 'won')::numeric / COUNT(*) * 100, 2)
           ELSE 0
         END as win_rate,
         CASE
           WHEN COUNT(*) FILTER (WHERE status = 'won') > 0
           THEN COALESCE(SUM(expected_revenue) FILTER (WHERE status = 'won'), 0) / COUNT(*) FILTER (WHERE status = 'won')
           ELSE 0
         END as avg_won_deal_size,
         CASE
           WHEN COUNT(*) FILTER (WHERE status = 'lost') > 0
           THEN COALESCE(SUM(expected_revenue) FILTER (WHERE status = 'lost'), 0) / COUNT(*) FILTER (WHERE status = 'lost')
           ELSE 0
         END as avg_lost_deal_size
       FROM crm.opportunities o
       ${whereClause}
       GROUP BY DATE_TRUNC('${periodTrunc}', date_closed)
       ORDER BY period DESC`,
      params
    );
  }

  /**
   * Get pipeline metrics for dashboard
   */
  async getPipelineMetrics(
    tenantId: string,
    filters: ForecastFilters = {}
  ): Promise<PipelineMetrics> {
    const { company_id, user_id, sales_team_id } = filters;

    let whereClause = `WHERE o.tenant_id = $1 AND o.status = 'open'`;
    const params: any[] = [tenantId];
    let paramIndex = 2;

    if (company_id) {
      whereClause += ` AND o.company_id = $${paramIndex++}`;
      params.push(company_id);
    }

    if (user_id) {
      whereClause += ` AND o.user_id = $${paramIndex++}`;
      params.push(user_id);
    }

    if (sales_team_id) {
      whereClause += ` AND o.sales_team_id = $${paramIndex++}`;
      params.push(sales_team_id);
    }

    // Get totals
    const totals = await queryOne<{ count: string; total: string }>(
      `SELECT COUNT(*) as count, COALESCE(SUM(expected_revenue), 0) as total
       FROM crm.opportunities o ${whereClause}`,
      params
    );

    // Get by stage
    const byStage = await query<{
      stage_id: string;
      stage_name: string;
      sequence: number;
      count: string;
      value: string;
      weighted_value: string;
      avg_probability: string;
    }>(
      `SELECT
         s.id as stage_id,
         s.name as stage_name,
         s.sequence,
         COUNT(o.id) as count,
         COALESCE(SUM(o.expected_revenue), 0) as value,
         COALESCE(SUM(o.expected_revenue * o.probability / 100), 0) as weighted_value,
         COALESCE(AVG(o.probability), 0) as avg_probability
       FROM crm.stages s
       LEFT JOIN crm.opportunities o ON o.stage_id = s.id AND o.status = 'open' AND o.tenant_id = $1
       WHERE s.tenant_id = $1 AND s.active = true
       GROUP BY s.id, s.name, s.sequence
       ORDER BY s.sequence`,
      [tenantId]
    );

    // Get by user
    const byUser = await query<{
      user_id: string;
      user_name: string;
      count: string;
      value: string;
      weighted_value: string;
    }>(
      `SELECT
         u.id as user_id,
         u.name as user_name,
         COUNT(o.id) as count,
         COALESCE(SUM(o.expected_revenue), 0) as value,
         COALESCE(SUM(o.expected_revenue * o.probability / 100), 0) as weighted_value
       FROM crm.opportunities o
       JOIN auth.users u ON o.user_id = u.id
       ${whereClause}
       GROUP BY u.id, u.name
       ORDER BY weighted_value DESC`,
      params
    );

    // Get average sales cycle
    const cycleStats = await queryOne<{ avg_days: string }>(
      `SELECT AVG(EXTRACT(EPOCH FROM (date_closed - created_at)) / 86400) as avg_days
       FROM crm.opportunities o
       WHERE o.tenant_id = $1 AND o.status = 'won' AND date_closed IS NOT NULL`,
      [tenantId]
    );

    return {
      total_opportunities: parseInt(totals?.count || '0', 10),
      total_value: parseFloat(totals?.total || '0'),
      by_stage: byStage.map(s => ({
        stage_id: s.stage_id,
        stage_name: s.stage_name,
        sequence: s.sequence,
        count: parseInt(s.count, 10),
        value: parseFloat(s.value),
        weighted_value: parseFloat(s.weighted_value),
        avg_probability: parseFloat(s.avg_probability),
      })),
      by_user: byUser.map(u => ({
        user_id: u.user_id,
        user_name: u.user_name,
        count: parseInt(u.count, 10),
        value: parseFloat(u.value),
        weighted_value: parseFloat(u.weighted_value),
      })),
      avg_days_in_stage: 0, // Would need stage history tracking
      avg_sales_cycle_days: parseFloat(cycleStats?.avg_days || '0'),
    };
  }

  /**
   * Get user performance metrics
   */
  async getUserPerformance(
    tenantId: string,
    userId: string,
    dateFrom?: string,
    dateTo?: string
  ): Promise<{
    open_opportunities: number;
    pipeline_value: number;
    won_deals: number;
    won_revenue: number;
    lost_deals: number;
    win_rate: number;
    activities_done: number;
    avg_deal_size: number;
  }> {
    let whereClause = `WHERE o.tenant_id = $1 AND o.user_id = $2`;
    const params: any[] = [tenantId, userId];
    let paramIndex = 3;

    if (dateFrom) {
      whereClause += ` AND o.created_at >= $${paramIndex++}`;
      params.push(dateFrom);
    }

    if (dateTo) {
      whereClause += ` AND o.created_at <= $${paramIndex++}`;
      params.push(dateTo);
    }

    const metrics = await queryOne<{
      open_count: string;
      pipeline: string;
      won_count: string;
      won_revenue: string;
      lost_count: string;
    }>(
      `SELECT
         COUNT(*) FILTER (WHERE status = 'open') as open_count,
         COALESCE(SUM(expected_revenue) FILTER (WHERE status = 'open'), 0) as pipeline,
         COUNT(*) FILTER (WHERE status = 'won') as won_count,
         COALESCE(SUM(expected_revenue) FILTER (WHERE status = 'won'), 0) as won_revenue,
         COUNT(*) FILTER (WHERE status = 'lost') as lost_count
       FROM crm.opportunities o
       ${whereClause}`,
      params
    );

    // Get activities count
    let activityWhere = `WHERE tenant_id = $1 AND user_id = $2 AND status = 'done'`;
    const activityParams: any[] = [tenantId, userId];
    let actParamIndex = 3;

    if (dateFrom) {
      activityWhere += ` AND date_done >= $${actParamIndex++}`;
      activityParams.push(dateFrom);
    }

    if (dateTo) {
      activityWhere += ` AND date_done <= $${actParamIndex++}`;
      activityParams.push(dateTo);
    }

    const activityCount = await queryOne<{ count: string }>(
      `SELECT COUNT(*) as count FROM crm.activities ${activityWhere}`,
      activityParams
    );

    const wonCount = parseInt(metrics?.won_count || '0', 10);
    const lostCount = parseInt(metrics?.lost_count || '0', 10);
    const wonRevenue = parseFloat(metrics?.won_revenue || '0');
    const totalDeals = wonCount + lostCount;

    return {
      open_opportunities: parseInt(metrics?.open_count || '0', 10),
      pipeline_value: parseFloat(metrics?.pipeline || '0'),
      won_deals: wonCount,
      won_revenue: wonRevenue,
      lost_deals: lostCount,
      win_rate: totalDeals > 0 ? (wonCount / totalDeals) * 100 : 0,
      activities_done: parseInt(activityCount?.count || '0', 10),
      avg_deal_size: wonCount > 0 ? wonRevenue / wonCount : 0,
    };
  }
}

export const forecastingService = new ForecastingService();
