SELECT
	coalesce(s.slug, t.slug, d.slug) AS identifier,
	DATE(r.ended_at) AS date,
	count(r.id) AS total_runs,
	sum(r.duration_seconds) / 60 AS duration_minutes
FROM
	"nekt_raw"."nekt_runs" r
	LEFT JOIN "nekt_raw"."nekt_sources" s ON r.source.id = s.id
	LEFT JOIN "nekt_raw"."nekt_destinations" d ON r.destination.id = d.id
	LEFT JOIN "nekt_raw"."nekt_transformations" t ON r.transformation.id = t.id
WHERE
	r.credit_charge_status = 'charged'
GROUP BY
	coalesce(s.slug, t.slug, d.slug),
	DATE(r.ended_at)
ORDER BY
	DATE(r.ended_at) DESC

WITH
	run_metrics AS (
		SELECT
			r.source.slug AS source_name,
			r.status,
			r.full_sync,
			r.duration_seconds,
			r.started_at,
			r.ended_at,
			r.credit_charge_status,
			DATE_TRUNC ('day', r.started_at) AS run_date,
			CASE
				WHEN r.status = 'success' THEN 1
				ELSE 0
			END AS success_flag,
			CASE
				WHEN r.status = 'failed' THEN 1
				ELSE 0
			END AS failure_flag
		FROM
			nekt_raw.nekt_runs r
		WHERE
			r.started_at >= CURRENT_DATE - INTERVAL '30' DAY
			AND r.started_at IS NOT NULL
	),
	daily_summary AS (
		SELECT
			run_date,
			source_name,
			COUNT(*) AS total_runs,
			SUM(success_flag) AS successful_runs,
			SUM(failure_flag) AS failed_runs,
			AVG(duration_seconds) AS avg_duration_seconds,
			APPROX_PERCENTILE (duration_seconds, 0.5) AS median_duration_seconds,
			MAX(duration_seconds) AS max_duration_seconds,
			COUNT(
				CASE
					WHEN full_sync = TRUE THEN 1
				END
			) AS full_sync_runs
		FROM
			run_metrics
		WHERE
			source_name IS NOT NULL
		GROUP BY
			run_date,
			source_name
	),
	source_reliability AS (
		SELECT
			source_name,
			SUM(total_runs) AS total_runs_30d,
			SUM(successful_runs) AS total_successful_runs,
			SUM(failed_runs) AS total_failed_runs,
			ROUND(
				CAST(SUM(successful_runs) AS DOUBLE) / NULLIF(SUM(total_runs), 0) * 100,
				2
			) AS success_rate_percentage,
			ROUND(AVG(avg_duration_seconds) / 60.0, 2) AS avg_duration_minutes,
			ROUND(AVG(median_duration_seconds) / 60.0, 2) AS median_duration_minutes,
			COUNT(DISTINCT run_date) AS active_days
		FROM
			daily_summary
		GROUP BY
			source_name
	)
SELECT
	source_name,
	total_runs_30d,
	total_successful_runs,
	total_failed_runs,
	success_rate_percentage,
	avg_duration_minutes,
	median_duration_minutes,
	active_days,
	CASE
		WHEN success_rate_percentage >= 95 THEN 'Excellent'
		WHEN success_rate_percentage >= 90 THEN 'Good'
		WHEN success_rate_percentage >= 80 THEN 'Needs Attention'
		ELSE 'Critical'
	END AS reliability_status,
	CASE
		WHEN avg_duration_minutes <= 5 THEN 'Fast'
		WHEN avg_duration_minutes <= 15 THEN 'Normal'
		WHEN avg_duration_minutes <= 60 THEN 'Slow'
		ELSE 'Very Slow'
	END AS performance_status
FROM
	source_reliability
WHERE
	total_runs_30d > 0
ORDER BY
	total_runs_30d DESC,
	success_rate_percentage DESC

WITH
  user_activities AS (
    SELECT
      a.created_by.email AS user_email,
      a.created_by.first_name || ' ' || a.created_by.last_name AS user_name,
      a.created_by.role AS user_role,
      a.created_by.functional_area AS functional_area,
      a.activity_type,
      a.created_at,
      a.created_by_system,
      DATE_TRUNC('day', a.created_at) AS activity_date,
      DATE_TRUNC('hour', a.created_at) AS activity_hour,
      CASE
        WHEN a.source IS NOT NULL THEN 'source'
        WHEN a.destination IS NOT NULL THEN 'destination'
        WHEN a.transformation IS NOT NULL THEN 'transformation'
        WHEN a.visualization IS NOT NULL THEN 'visualization'
        WHEN a.run IS NOT NULL THEN 'run'
        ELSE 'other'
      END AS entity_type
    FROM nekt_raw.nekt_activities a
    WHERE a.created_at >= CURRENT_DATE - INTERVAL '30' DAY
      AND a.created_by_system = false
      AND a.created_by.email IS NOT NULL
  ),
  user_summary AS (
    SELECT
      user_email,
      user_name,
      user_role,
      functional_area,
      COUNT(*) AS total_activities,
      COUNT(DISTINCT activity_date) AS active_days,
      COUNT(DISTINCT activity_type) AS unique_activity_types,
      COUNT(DISTINCT entity_type) AS entity_types_used,
      MIN(created_at) AS first_activity,
      MAX(created_at) AS last_activity,
      ARBITRARY(activity_type) AS most_common_activity,
      ARBITRARY(entity_type) AS most_used_entity_type
    FROM user_activities
    GROUP BY user_email, user_name, user_role, functional_area
  ),
  hourly_patterns AS (
    SELECT
      user_email,
      EXTRACT(hour FROM activity_hour) AS hour_of_day,
      COUNT(*) AS activities_count
    FROM user_activities
    GROUP BY user_email, EXTRACT(hour FROM activity_hour)
  ),
  peak_hours AS (
    SELECT
      user_email,
      hour_of_day,
      activities_count,
      ROW_NUMBER() OVER (PARTITION BY user_email ORDER BY activities_count DESC) AS hour_rank
    FROM hourly_patterns
  ),
  team_collaboration AS (
    SELECT
      functional_area,
      COUNT(DISTINCT user_email) AS team_size,
      SUM(total_activities) AS team_total_activities,
      AVG(total_activities) AS avg_activities_per_user,
      AVG(active_days) AS avg_active_days_per_user,
      SUM(CASE WHEN active_days >= 20 THEN 1 ELSE 0 END) AS highly_active_users
    FROM user_summary
    WHERE functional_area IS NOT NULL
    GROUP BY functional_area
  )
SELECT
  us.user_name,
  us.user_email,
  us.user_role,
  us.functional_area,
  us.total_activities,
  us.active_days,
  ROUND(
    CAST(us.active_days AS DOUBLE) / 30.0 * 100,
    1
  ) AS engagement_percentage,
  us.unique_activity_types,
  us.entity_types_used,
  us.most_common_activity,
  us.most_used_entity_type,
  ph.hour_of_day AS peak_activity_hour,
  us.first_activity,
  us.last_activity,
  tc.team_size,
  ROUND(tc.avg_activities_per_user, 1) AS team_avg_activities,
  CASE
    WHEN us.active_days >= 25 THEN 'Highly Active'
    WHEN us.active_days >= 15 THEN 'Active'
    WHEN us.active_days >= 5 THEN 'Moderate'
    ELSE 'Low Activity'
  END AS activity_level,
  CASE
    WHEN us.entity_types_used >= 4 THEN 'Power User'
    WHEN us.entity_types_used >= 2 THEN 'Regular User'
    ELSE 'Basic User'
  END AS user_type
FROM user_summary us
LEFT JOIN peak_hours ph ON us.user_email = ph.user_email AND ph.hour_rank = 1
LEFT JOIN team_collaboration tc ON us.functional_area = tc.functional_area
ORDER BY 
  us.total_activities DESC,
  us.active_days DESC