-- Engineering Information Performance Lab
-- Portfolio-safe synthetic data. PostgreSQL syntax.

-- 1. Open RFI ageing by package
SELECT
    package,
    COUNT(*) FILTER (WHERE record_type='RFI' AND status='Open') AS open_rfis,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
      ORDER BY (CURRENT_DATE-created_date::date)
    ) FILTER (WHERE record_type='RFI' AND status='Open') AS median_open_age_days,
    PERCENTILE_CONT(0.9) WITHIN GROUP (
      ORDER BY (CURRENT_DATE-created_date::date)
    ) FILTER (WHERE record_type='RFI' AND status='Open') AS p90_open_age_days
FROM engineering_information_event
GROUP BY package
ORDER BY p90_open_age_days DESC NULLS LAST;

-- 2. Submittal first-pass approval
SELECT
    discipline,
    COUNT(*) FILTER (WHERE record_type='SUBMITTAL') AS submittals,
    ROUND(
      100.0*AVG(CASE WHEN first_pass_approved THEN 1 ELSE 0 END)
      FILTER (WHERE record_type='SUBMITTAL'),1
    ) AS first_pass_approval_pct
FROM engineering_information_event
GROUP BY discipline
ORDER BY first_pass_approval_pct;

-- 3. Overdue/open workload
SELECT
    responsible_party,
    COUNT(*) FILTER (
      WHERE status='Open' AND due_date::date < CURRENT_DATE
    ) AS overdue_open_items,
    COUNT(*) FILTER (WHERE status='Open') AS all_open_items
FROM engineering_information_event
GROUP BY responsible_party
ORDER BY overdue_open_items DESC;

-- 4. Closed-cycle distribution
SELECT
    record_type,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cycle_days) AS median_cycle_days,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY cycle_days) AS p90_cycle_days
FROM engineering_information_event
WHERE status <> 'Open'
GROUP BY record_type;