WITH funnel_analysis AS (
  SELECT 
    p.name AS pipeline_name,
    s.name AS stage_name,
    s.order_nr,
    COUNT(DISTINCT d.id) AS deals_count,
    SUM(d.value) AS total_value,
    ROUND(AVG(d.probability), 2) AS avg_probability,
    COUNT(DISTINCT CASE WHEN d.status = 'won' THEN d.id END) AS won_deals,
    COUNT(DISTINCT CASE WHEN d.status = 'lost' THEN d.id END) AS lost_deals
  FROM nekt_raw.pipedrive_deals d
  JOIN nekt_raw.pipedrive_stages s ON d.stage_id = s.id
  JOIN nekt_raw.pipedrive_pipelines p ON d.pipeline_id = p.id
  WHERE d.add_time >= current_date - interval '90' day
    AND NOT d.is_deleted
  GROUP BY p.name, s.name, s.order_nr
),
conversion_rates AS (
  SELECT 
    pipeline_name,
    stage_name,
    order_nr,
    deals_count,
    total_value,
    avg_probability,
    won_deals,
    lost_deals,
    ROUND(
      CAST(won_deals AS DOUBLE) / NULLIF(deals_count, 0) * 100, 2
    ) AS win_rate_percentage,
    LAG(deals_count) OVER (
      PARTITION BY pipeline_name 
      ORDER BY order_nr
    ) AS previous_stage_deals
  FROM funnel_analysis
)
SELECT 
  pipeline_name,
  stage_name,
  order_nr,
  deals_count,
  total_value,
  avg_probability,
  win_rate_percentage,
  CASE 
    WHEN previous_stage_deals > 0 THEN
      ROUND(
        CAST(deals_count AS DOUBLE) / previous_stage_deals * 100, 2
      )
    ELSE NULL
  END AS stage_conversion_rate
FROM conversion_rates
ORDER BY pipeline_name, order_nr

WITH rep_metrics AS (
  SELECT 
    u.id AS user_id,
    u.name AS rep_name,
    u.email,
    COUNT(DISTINCT d.id) AS total_deals,
    COUNT(DISTINCT CASE WHEN d.status = 'won' THEN d.id END) AS won_deals,
    COUNT(DISTINCT CASE WHEN d.status = 'lost' THEN d.id END) AS lost_deals,
    COUNT(DISTINCT CASE WHEN d.status = 'open' THEN d.id END) AS open_deals,
    SUM(CASE WHEN d.status = 'won' THEN d.value ELSE 0 END) AS won_revenue,
    SUM(CASE WHEN d.status = 'open' THEN d.value ELSE 0 END) AS pipeline_value,
    AVG(CASE WHEN d.status = 'won' THEN d.value END) AS avg_deal_size,
    COUNT(DISTINCT d.person_id) AS unique_contacts,
    COUNT(DISTINCT d.org_id) AS unique_organizations
  FROM nekt_raw.pipedrive_users u
  LEFT JOIN nekt_raw.pipedrive_deals d ON u.id = d.owner_id
    AND d.add_time >= current_date - interval '90' day
  WHERE u.active_flag = true
    AND NOT COALESCE(d.is_deleted, false)
  GROUP BY u.id, u.name, u.email
),
team_benchmarks AS (
  SELECT 
    AVG(won_revenue) AS avg_team_revenue,
    AVG(CAST(won_deals AS DOUBLE) / NULLIF(total_deals, 0)) AS avg_team_win_rate,
    AVG(avg_deal_size) AS avg_team_deal_size
  FROM rep_metrics
  WHERE total_deals > 0
)
SELECT 
  rm.rep_name,
  rm.email,
  rm.total_deals,
  rm.won_deals,
  rm.lost_deals,
  rm.open_deals,
  rm.won_revenue AS won_revenue,
  rm.pipeline_value AS pipeline_value,
  ROUND(
    CAST(rm.won_deals AS DOUBLE) / NULLIF(rm.total_deals, 0) * 100, 2
  ) AS win_rate_percentage,
  rm.avg_deal_size AS avg_deal_size,
  rm.unique_contacts,
  rm.unique_organizations,
  CASE 
    WHEN rm.won_revenue > tb.avg_team_revenue THEN 'Above Average'
    WHEN rm.won_revenue = tb.avg_team_revenue THEN 'Average'
    ELSE 'Below Average'
  END AS revenue_performance,
  ROUND(
    rm.won_revenue / NULLIF(tb.avg_team_revenue, 0) * 100, 2
  ) AS revenue_vs_team_avg_percent
FROM rep_metrics rm
CROSS JOIN team_benchmarks tb
WHERE rm.total_deals > 0
ORDER BY rm.won_revenue DESC

WITH
	channel_mapping AS (
		SELECT
			CAST(option.id AS INT) AS channel_id,
			option.label AS channel_name
		FROM
			nekt_raw.pipedrive_deal_fields pdf,
			UNNEST (pdf.options) AS t (option)
		WHERE
			pdf.key = 'channel'
	),
	lead_performance AS (
		SELECT
			COALESCE(d.origin, 'Unknown') AS lead_source,
			COALESCE(d.channel, 0) AS channel_id,
			COUNT(DISTINCT d.id) AS total_leads,
			COUNT(
				DISTINCT CASE
					WHEN d.status = 'won' THEN d.id
				END
			) AS won_deals,
			COUNT(
				DISTINCT CASE
					WHEN d.status = 'lost' THEN d.id
				END
			) AS lost_deals,
			COUNT(
				DISTINCT CASE
					WHEN d.status = 'open' THEN d.id
				END
			) AS open_deals,
			SUM(d.value) AS total_pipeline_value,
			SUM(
				CASE
					WHEN d.status = 'won' THEN d.value
					ELSE 0
				END
			) AS won_revenue,
			SUM(
				CASE
					WHEN d.status = 'open' THEN d.value
					ELSE 0
				END
			) AS open_pipeline_value,
			AVG(
				CASE
					WHEN d.status = 'won' THEN d.value
				END
			) AS avg_won_deal_size,
			AVG(
				CASE
					WHEN d.status IN ('won', 'lost') THEN DATE_DIFF (
						'day',
						d.add_time,
						COALESCE(d.won_time, d.lost_time)
					)
				END
			) AS avg_sales_cycle_days,
			MIN(d.add_time) AS first_lead_date,
			MAX(d.add_time) AS last_lead_date
		FROM
			nekt_raw.pipedrive_deals d
		WHERE
			d.add_time >= CURRENT_DATE - interval '180' DAY
			AND NOT d.is_deleted
		GROUP BY
			COALESCE(d.origin, 'Unknown'),
			COALESCE(d.channel, 0)
	),
	source_metrics AS (
		SELECT
			lead_source,
			channel_id,
			total_leads,
			won_deals,
			lost_deals,
			open_deals,
			ROUND(
				CAST(won_deals AS DOUBLE) / NULLIF(total_leads, 0) * 100,
				2
			) AS conversion_rate_percent,
			ROUND(
				CAST(won_deals AS DOUBLE) / NULLIF(won_deals + lost_deals, 0) * 100,
				2
			) AS win_rate_percent,
			total_pipeline_value,
			won_revenue,
			open_pipeline_value,
			avg_won_deal_size,
			avg_sales_cycle_days,
			ROUND(won_revenue / NULLIF(total_leads, 0), 2) AS revenue_per_lead,
			DATE_FORMAT (first_lead_date, '%Y-%m-%d') AS first_lead_date,
			DATE_FORMAT (last_lead_date, '%Y-%m-%d') AS last_lead_date
		FROM
			lead_performance
	)
SELECT
	sm.lead_source,
	sm.channel_id,
	COALESCE(cm.channel_name, 'Unknown') AS channel_name,
	sm.total_leads,
	sm.won_deals,
	sm.lost_deals,
	sm.open_deals,
	sm.conversion_rate_percent,
	sm.win_rate_percent,
	FORMAT ('$%,.0f', CAST(sm.won_revenue AS DOUBLE)) AS won_revenue,
	FORMAT ('$%,.0f', CAST(sm.open_pipeline_value AS DOUBLE)) AS open_pipeline_value,
	FORMAT ('$%,.0f', CAST(sm.avg_won_deal_size AS DOUBLE)) AS avg_won_deal_size,
	sm.avg_sales_cycle_days,
	FORMAT ('$%,.2f', CAST(sm.revenue_per_lead AS DOUBLE)) AS revenue_per_lead,
	sm.first_lead_date,
	sm.last_lead_date,
	RANK() OVER (
		ORDER BY
			sm.conversion_rate_percent DESC
	) AS conversion_rank,
	RANK() OVER (
		ORDER BY
			sm.revenue_per_lead DESC
	) AS revenue_efficiency_rank,
	CASE
		WHEN sm.total_leads >= 50
		AND sm.conversion_rate_percent >= 15 THEN 'High Performance'
		WHEN sm.total_leads >= 20
		AND sm.conversion_rate_percent >= 10 THEN 'Good Performance'
		WHEN sm.total_leads >= 10
		AND sm.conversion_rate_percent >= 5 THEN 'Average Performance'
		ELSE 'Needs Improvement'
	END AS performance_category
FROM
	source_metrics sm
	LEFT JOIN channel_mapping cm ON sm.channel_id = cm.channel_id
WHERE
	sm.total_leads >= 5
ORDER BY
	sm.revenue_per_lead DESC,
	sm.conversion_rate_percent DESC

WITH
	customer_journey AS (
		SELECT
			d.id AS deal_id,
			d.title AS deal_title,
			d.value AS deal_value,
			d.status,
			p.name AS contact_name,
			o.name AS organization_name,
			u.name AS owner_name,
			d.add_time AS deal_created,
			d.won_time,
			d.lost_time,
			COALESCE(d.won_time, d.lost_time, d.update_time) AS deal_closed,
			DATE_DIFF (
				'day',
				d.add_time,
				COALESCE(d.won_time, d.lost_time, CURRENT_TIMESTAMP)
			) AS days_in_pipeline,
			COUNT(a.id) AS total_activities,
			COUNT(
				CASE
					WHEN a.done = TRUE THEN a.id
				END
			) AS completed_activities,
			COUNT(n.id) AS total_notes,
			COUNT(
				CASE
					WHEN a.type = 'call'
					AND a.done = TRUE THEN a.id
				END
			) AS calls_made,
			COUNT(
				CASE
					WHEN a.type = 'meeting'
					AND a.done = TRUE THEN a.id
				END
			) AS meetings_held,
			MIN(a.add_time) AS first_activity,
			MAX(a.add_time) AS last_activity
		FROM
			nekt_raw.pipedrive_deals d
			LEFT JOIN nekt_raw.pipedrive_persons p ON d.person_id = p.id
			LEFT JOIN nekt_raw.pipedrive_organizations o ON d.org_id = o.id
			LEFT JOIN nekt_raw.pipedrive_users u ON d.owner_id = u.id
			LEFT JOIN nekt_raw.pipedrive_activities a ON d.id = a.deal_id
			AND NOT a.is_deleted
			LEFT JOIN nekt_raw.pipedrive_notes n ON d.id = n.deal_id
			AND n.active_flag = TRUE
		WHERE
			d.add_time >= CURRENT_DATE - interval '180' DAY
			AND NOT d.is_deleted
		GROUP BY
			d.id,
			d.title,
			d.value,
			d.status,
			p.name,
			o.name,
			u.name,
			d.add_time,
			d.won_time,
			d.lost_time,
			d.update_time
	),
	journey_segments AS (
		SELECT
			*,
			CASE
				WHEN days_in_pipeline <= 7 THEN 'Quick (≤7 days)'
				WHEN days_in_pipeline <= 30 THEN 'Standard (8-30 days)'
				WHEN days_in_pipeline <= 90 THEN 'Extended (31-90 days)'
				ELSE 'Long (>90 days)'
			END AS sales_cycle_segment,
			CASE
				WHEN total_activities = 0 THEN 'No Engagement'
				WHEN total_activities <= 5 THEN 'Low Engagement'
				WHEN total_activities <= 15 THEN 'Medium Engagement'
				ELSE 'High Engagement'
			END AS engagement_level
		FROM
			customer_journey
	)
SELECT
	deal_title,
	contact_name,
	organization_name,
	owner_name,
	FORMAT ('$%,.0f', CAST(deal_value AS DOUBLE)) AS deal_value_formatted,
	status,
	sales_cycle_segment,
	engagement_level,
	days_in_pipeline,
	total_activities,
	completed_activities,
	calls_made,
	meetings_held,
	total_notes,
	ROUND(
		CAST(completed_activities AS DOUBLE) / NULLIF(total_activities, 0) * 100,
		2
	) AS activity_completion_rate,
	DATE_FORMAT (deal_created, '%Y-%m-%d') AS deal_created_date,
	DATE_FORMAT (first_activity, '%Y-%m-%d') AS first_activity_date,
	DATE_FORMAT (last_activity, '%Y-%m-%d') AS last_activity_date
FROM
	journey_segments
ORDER BY
	deal_value DESC,
	days_in_pipeline DESC

WITH
	monthly_forecast AS (
		SELECT
			DATE_TRUNC ('month', d.expected_close_date) AS forecast_month,
			COUNT(DISTINCT d.id) AS deals_count,
			SUM(d.value) AS total_pipeline_value,
			SUM(d.value * d.probability / 100.0) AS weighted_forecast,
			AVG(d.probability) AS avg_probability,
			COUNT(
				CASE
					WHEN d.probability >= 80 THEN d.id
				END
			) AS high_probability_deals,
			COUNT(
				CASE
					WHEN d.probability BETWEEN 50 AND 79  THEN d.id
				END
			) AS medium_probability_deals,
			COUNT(
				CASE
					WHEN d.probability < 50 THEN d.id
				END
			) AS low_probability_deals,
			SUM(
				CASE
					WHEN d.probability >= 80 THEN d.value
					ELSE 0
				END
			) AS high_prob_value,
			SUM(
				CASE
					WHEN d.probability BETWEEN 50 AND 79  THEN d.value
					ELSE 0
				END
			) AS medium_prob_value,
			SUM(
				CASE
					WHEN d.probability < 50 THEN d.value
					ELSE 0
				END
			) AS low_prob_value
		FROM
			nekt_raw.pipedrive_deals d
		WHERE
			d.status = 'open'
			AND NOT d.is_deleted
			AND d.expected_close_date IS NOT NULL
			AND d.expected_close_date >= CURRENT_DATE
			AND d.expected_close_date <= CURRENT_DATE + interval '12' MONTH
		GROUP BY
			DATE_TRUNC ('month', d.expected_close_date)
	),
	historical_performance AS (
		SELECT
			DATE_TRUNC ('month', d.won_time) AS won_month,
			COUNT(DISTINCT d.id) AS historical_won_deals,
			SUM(d.value) AS historical_won_revenue,
			AVG(d.value) AS avg_historical_deal_size
		FROM
			nekt_raw.pipedrive_deals d
		WHERE
			d.status = 'won'
			AND NOT d.is_deleted
			AND d.won_time >= CURRENT_DATE - interval '12' MONTH
		GROUP BY
			DATE_TRUNC ('month', d.won_time)
	)
SELECT
	DATE_FORMAT (mf.forecast_month, '%Y-%m') AS MONTH,
	mf.deals_count,
	FORMAT ('$%,.0f', CAST(mf.total_pipeline_value AS DOUBLE)) AS total_pipeline_value,
	FORMAT ('$%,.0f', CAST(mf.weighted_forecast AS DOUBLE)) AS weighted_forecast,
	ROUND(mf.avg_probability, 2) AS avg_probability_percent,
	mf.high_probability_deals,
	mf.medium_probability_deals,
	mf.low_probability_deals,
	FORMAT ('$%,.0f', CAST(mf.high_prob_value AS DOUBLE)) AS high_prob_value,
	FORMAT ('$%,.0f', CAST(mf.medium_prob_value AS DOUBLE)) AS medium_prob_value,
	FORMAT ('$%,.0f', CAST(mf.low_prob_value AS DOUBLE)) AS low_prob_value,
	COALESCE(hp.historical_won_deals, 0) AS last_year_won_deals,
	FORMAT (
		'$%,.0f',
		CAST(COALESCE(hp.historical_won_revenue, 0) AS DOUBLE)
	) AS last_year_won_revenue,
	CASE
		WHEN hp.historical_won_revenue > 0 THEN ROUND(
			mf.weighted_forecast / hp.historical_won_revenue * 100,
			2
		)
		ELSE NULL
	END AS forecast_vs_historical_percent
FROM
	monthly_forecast mf
	LEFT JOIN historical_performance hp ON mf.forecast_month = hp.won_month + interval '12' MONTH
ORDER BY
	mf.forecast_month