Fact tables — production runs, issues, and shipments

-- 1) Weekly production summary by line (planned vs actual + downtime)
SELECT
  cw.week_label,
  pl.line_name,
  SUM(pr.units_planned) AS units_planned,
  SUM(pr.units_actual) AS units_actual,
  SUM(pr.downtime_minutes) AS downtime_minutes
FROM production_runs pr
JOIN production_lines pl ON pl.production_line_id = pr.production_line_id
LEFT JOIN calendar_weeks cw ON cw.calendar_week_id = pr.calendar_week_id
GROUP BY cw.week_label, pl.line_name
ORDER BY cw.week_label, pl.line_name;

-- 2) Top issue types in the last 30 days (relative to max run_date in table)
WITH bounds AS (
  SELECT (MAX(run_date) - INTERVAL '30 days')::date AS start_date
  FROM production_runs
)
SELECT
  it.issue_type_name,
  COUNT(*) AS issue_count
FROM production_issues pi
JOIN issue_types it ON it.issue_type_id = pi.issue_type_id
JOIN production_runs pr ON pr.production_run_id = pi.production_run_id
JOIN bounds b ON pr.run_date >= b.start_date
GROUP BY it.issue_type_name
ORDER BY issue_count DESC, it.issue_type_name;

-- 6) Average downtime by line + shift
SELECT
  pl.line_name,
  sh.shift_name,
  AVG(pr.downtime_minutes)::numeric(10,2) AS avg_downtime_minutes,
  COUNT(*) AS run_count
FROM production_runs pr
JOIN production_lines pl ON pl.production_line_id = pr.production_line_id
JOIN shifts sh ON sh.shift_id = pr.shift_id
GROUP BY pl.line_name, sh.shift_name
ORDER BY pl.line_name, sh.shift_name;