WITH
  cycle_metrics AS (
    SELECT
      c.id AS cycle_id,
      c.name AS cycle_name,
      c.number AS cycle_number,
      c.team.name AS team_name,
      c.startsAt AS cycle_start,
      c.endsAt AS cycle_end,
      c.progress AS cycle_progress,
      DATE_DIFF('day', c.startsAt, c.endsAt) AS cycle_duration_days,
      CASE 
        WHEN c.completedAt IS NOT NULL THEN 'Completed'
        WHEN c.endsAt < CURRENT_TIMESTAMP THEN 'Overdue'
        WHEN c.startsAt <= CURRENT_TIMESTAMP THEN 'Active'
        ELSE 'Planned'
      END AS cycle_status
    FROM nekt_raw.linear_cycles c
    WHERE c.startsAt >= CURRENT_DATE - INTERVAL '6' MONTH
  ),
  issue_metrics AS (
    SELECT
      i.cycle.id AS cycle_id,
      COUNT(*) AS total_issues,
      COUNT(CASE WHEN i.completedAt IS NOT NULL THEN 1 END) AS completed_issues,
      SUM(COALESCE(i.estimate, 0)) AS total_story_points,
      SUM(CASE WHEN i.completedAt IS NOT NULL THEN COALESCE(i.estimate, 0) ELSE 0 END) AS completed_story_points,
      AVG(DATE_DIFF('hour', i.createdAt, COALESCE(i.completedAt, CURRENT_TIMESTAMP))) AS avg_completion_time_hours
    FROM nekt_raw.linear_issues i
    WHERE i.cycle.id IS NOT NULL
    GROUP BY i.cycle.id
  )
SELECT
  cm.team_name,
  cm.cycle_name,
  cm.cycle_number,
  cm.cycle_start,
  cm.cycle_end,
  cm.cycle_duration_days,
  cm.cycle_status,
  ROUND(cm.cycle_progress * 100, 2) AS progress_percentage,
  COALESCE(im.total_issues, 0) AS total_issues,
  COALESCE(im.completed_issues, 0) AS completed_issues,
  ROUND(
    CAST(COALESCE(im.completed_issues, 0) AS DOUBLE) / NULLIF(im.total_issues, 0) * 100,
    2
  ) AS completion_rate_percentage,
  COALESCE(im.total_story_points, 0) AS total_story_points,
  COALESCE(im.completed_story_points, 0) AS completed_story_points,
  ROUND(im.avg_completion_time_hours / 24.0, 2) AS avg_completion_time_days
FROM cycle_metrics cm
LEFT JOIN issue_metrics im ON cm.cycle_id = im.cycle_id
ORDER BY cm.team_name, cm.cycle_start DESC

WITH
  issue_analysis AS (
    SELECT
      i.id,
      i.identifier,
      i.title,
      i.priority,
      i.priorityLabel,
      i.team.name AS team_name,
      i.assignee.displayName AS assignee_name,
      i.state.name AS current_state,
      i.state.type AS state_type,
      i.createdAt,
      i.startedAt,
      i.completedAt,
      i.estimate,
      DATE_DIFF('day', i.createdAt, COALESCE(i.completedAt, CURRENT_TIMESTAMP)) AS age_days,
      CASE
        WHEN i.completedAt IS NOT NULL THEN 
          DATE_DIFF('day', i.createdAt, i.completedAt)
      END AS resolution_time_days,
      CASE
        WHEN i.completedAt IS NOT NULL THEN 'Completed'
        WHEN i.startedAt IS NOT NULL THEN 'In Progress'
        ELSE 'Not Started'
      END AS issue_status,
      CASE
        WHEN i.priority = 1 THEN 'Urgent'
        WHEN i.priority = 2 THEN 'High'
        WHEN i.priority = 3 THEN 'Medium'
        WHEN i.priority = 4 THEN 'Low'
        ELSE 'No Priority'
      END AS priority_category
    FROM nekt_raw.linear_issues i
    WHERE i.createdAt >= CURRENT_DATE - INTERVAL '3' MONTH
  ),
  team_summary AS (
    SELECT
      team_name,
      priority_category,
      COUNT(*) AS total_issues,
      COUNT(CASE WHEN issue_status = 'Completed' THEN 1 END) AS completed_issues,
      AVG(resolution_time_days) AS avg_resolution_days,
      PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY resolution_time_days) AS median_resolution_days,
      COUNT(CASE WHEN age_days > 30 AND issue_status != 'Completed' THEN 1 END) AS stale_issues
    FROM issue_analysis
    GROUP BY team_name, priority_category
  )
SELECT
  team_name,
  priority_category,
  total_issues,
  completed_issues,
  ROUND(
    CAST(completed_issues AS DOUBLE) / NULLIF(total_issues, 0) * 100,
    2
  ) AS completion_rate_percentage,
  ROUND(avg_resolution_days, 2) AS avg_resolution_days,
  ROUND(median_resolution_days, 2) AS median_resolution_days,
  stale_issues,
  ROUND(
    CAST(stale_issues AS DOUBLE) / NULLIF(total_issues, 0) * 100,
    2
  ) AS stale_issue_percentage
FROM team_summary
WHERE total_issues > 0
ORDER BY team_name, 
  CASE priority_category 
    WHEN 'Urgent' THEN 1 
    WHEN 'High' THEN 2 
    WHEN 'Medium' THEN 3 
    WHEN 'Low' THEN 4 
    ELSE 5 
  END

WITH
  project_metrics AS (
    SELECT
      p.id AS project_id,
      p.name AS project_name,
      p.state AS project_state,
      p.priority AS project_priority,
      p.progress AS project_progress,
      p.createdAt AS project_created,
      p.startedAt AS project_started,
      p.completedAt AS project_completed,
      p.targetDate AS target_date,
      p.lead.displayName AS project_lead,
      CASE
        WHEN p.completedAt IS NOT NULL THEN 'Completed'
        WHEN p.targetDate < CURRENT_DATE AND p.completedAt IS NULL THEN 'Overdue'
        WHEN p.startedAt IS NOT NULL THEN 'In Progress'
        ELSE 'Not Started'
      END AS project_status,
      CASE
        WHEN p.targetDate IS NOT NULL THEN
          DATE_DIFF('day', CURRENT_DATE, p.targetDate)
      END AS days_to_target
    FROM nekt_raw.linear_projects p
    WHERE p.archivedAt IS NULL
  ),
  project_issues AS (
    SELECT
      i.project.id AS project_id,
      COUNT(*) AS total_issues,
      COUNT(CASE WHEN i.completedAt IS NOT NULL THEN 1 END) AS completed_issues,
      COUNT(CASE WHEN i.startedAt IS NOT NULL AND i.completedAt IS NULL THEN 1 END) AS in_progress_issues,
      COUNT(CASE WHEN i.startedAt IS NULL THEN 1 END) AS not_started_issues,
      SUM(COALESCE(i.estimate, 0)) AS total_story_points,
      SUM(CASE WHEN i.completedAt IS NOT NULL THEN COALESCE(i.estimate, 0) ELSE 0 END) AS completed_story_points,
      COUNT(DISTINCT i.assignee.id) AS unique_assignees,
      COUNT(DISTINCT i.team.id) AS teams_involved
    FROM nekt_raw.linear_issues i
    WHERE i.project.id IS NOT NULL
    GROUP BY i.project.id
  )
SELECT
  pm.project_name,
  pm.project_state,
  pm.project_status,
  pm.project_lead,
  pm.project_created,
  pm.project_started,
  pm.target_date,
  pm.days_to_target,
  ROUND(pm.project_progress * 100, 2) AS progress_percentage,
  COALESCE(pi.total_issues, 0) AS total_issues,
  COALESCE(pi.completed_issues, 0) AS completed_issues,
  COALESCE(pi.in_progress_issues, 0) AS in_progress_issues,
  COALESCE(pi.not_started_issues, 0) AS not_started_issues,
  ROUND(
    CAST(COALESCE(pi.completed_issues, 0) AS DOUBLE) / NULLIF(pi.total_issues, 0) * 100,
    2
  ) AS issue_completion_rate,
  COALESCE(pi.total_story_points, 0) AS total_story_points,
  COALESCE(pi.completed_story_points, 0) AS completed_story_points,
  ROUND(
    CAST(COALESCE(pi.completed_story_points, 0) AS DOUBLE) / NULLIF(pi.total_story_points, 0) * 100,
    2
  ) AS story_points_completion_rate,
  COALESCE(pi.unique_assignees, 0) AS team_members_involved,
  COALESCE(pi.teams_involved, 0) AS teams_involved,
  CASE
    WHEN pm.project_status = 'Overdue' THEN 'At Risk'
    WHEN pm.days_to_target <= 7 AND pm.project_progress < 0.8 THEN 'At Risk'
    WHEN pm.project_progress >= 0.8 THEN 'On Track'
    ELSE 'Monitor'
  END AS health_status
FROM project_metrics pm
LEFT JOIN project_issues pi ON pm.project_id = pi.project_id
ORDER BY 
  CASE pm.project_status 
    WHEN 'Overdue' THEN 1 
    WHEN 'In Progress' THEN 2 
    ELSE 3 
  END,
  pm.days_to_target ASC NULLS LAST