-- Project Controls Performance Lab
-- Synthetic, portfolio-safe schedule snapshots. PostgreSQL syntax.

-- 1. Latest snapshot by activity
WITH latest AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY activity_id
           ORDER BY snapshot_date DESC
         ) AS rn
  FROM project_control_snapshot
)
SELECT *
FROM latest
WHERE rn=1;

-- 2. Package performance at latest snapshot
WITH latest AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY activity_id
           ORDER BY snapshot_date DESC
         ) AS rn
  FROM project_control_snapshot
)
SELECT
    package,
    ROUND(AVG(planned_progress_pct),1) AS planned_progress,
    ROUND(AVG(actual_progress_pct),1) AS actual_progress,
    ROUND(AVG(schedule_variance_pp),1) AS variance_pp,
    COUNT(*) FILTER (WHERE total_float_days < 0) AS negative_float_activities,
    COUNT(*) FILTER (WHERE total_float_days <= 5) AS near_critical_activities
FROM latest
WHERE rn=1
GROUP BY package
ORDER BY variance_pp;

-- 3. Milestone slippage
WITH latest AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY activity_id
           ORDER BY snapshot_date DESC
         ) AS rn
  FROM project_control_snapshot
)
SELECT
    activity_id,
    package,
    baseline_finish,
    forecast_finish,
    forecast_finish::date - baseline_finish::date AS slippage_days
FROM latest
WHERE rn=1
  AND milestone_flag
  AND forecast_finish::date > baseline_finish::date
ORDER BY slippage_days DESC;

-- 4. S-curve data
SELECT
    snapshot_date,
    AVG(planned_progress_pct) AS planned_progress,
    AVG(actual_progress_pct) AS actual_progress,
    SUM(planned_hours_to_date) AS planned_hours,
    SUM(actual_hours_to_date) AS actual_hours
FROM project_control_snapshot
GROUP BY snapshot_date
ORDER BY snapshot_date;

-- 5. Activities losing float month-on-month
SELECT
    activity_id,
    snapshot_date,
    total_float_days,
    total_float_days
      - LAG(total_float_days) OVER (
          PARTITION BY activity_id ORDER BY snapshot_date
        ) AS float_change_days
FROM project_control_snapshot;