with tests as
(
select
-- replace these 3 variables with real data, or assign them via scripting:
:report_id::text as report_id,
:tests as tests_performed,
:cases as positives_reported
),

permutations as
(
select
(prevalence::float / 1000)::float as prevalence,
(sensitivity::float / 1000)::float as sensitivity,
(specificity::float / 1000)::float as specificity
from
-- Adjust ranges and precision at your discretion:
generate_series( 5, 500, 5) as prevalence,
generate_series(500, 1000, 5) as sensitivity,
generate_series(750, 1000, 5) as specificity
),

matrices as
(
select
report_id,
tests_performed,
positives_reported,
prevalence,
sensitivity,
specificity,
(tests_performed * prevalence)::int as has_disease,
(tests_performed * (1 - prevalence))::int as hasnot_disease,
(tests_performed * prevalence * sensitivity)::int as true_positives,
(tests_performed * (1 - prevalence) * specificity)::int as true_negatives
from
tests,
permutations
),

confusion_matrices as
(
select
*,
hasnot_disease - true_negatives as false_positives,
has_disease - true_positives as false_negatives,
true_positives + hasnot_disease - true_negatives as FP_plus_TP
from
matrices
where
-- use strict matching the permutations have a high resolution ( .001):
--(true_positives + (hasnot_disease - true_negatives)) = positives_reported
-- Alternatively, use loose matching when permutations have relative low resolition (.005):
(true_positives::int + (hasnot_disease::int - true_negatives::int)) between (0.9990 * positives_reported) and (1.0001 * positives_reported)
)

select
report_id,
tests_performed,
positives_reported,
has_disease,
hasnot_disease,
true_positives,
false_positives,
true_negatives,
false_negatives,
sensitivity::numeric(4,3),
specificity::numeric(4,3),
prevalence::numeric(4,3)
from
confusion_matrices
order by
prevalence::numeric(4,3),
sensitivity::numeric(4,3),
specificity::numeric(4,3)