# SPHF Pending and Completed Stage Conditions (Code-Aligned)

## Pending Mode: Inclusion Filter

| Mode | Inclusion Condition (from sphf_normalized_kpi SQL path) |
|---|---|
| Pending | p_valid = true AND UPPER(TRIM(p_stage)) != 'COMPLETED' |

## Pending Mode: Stage Case Conditions

| Case ID | Returned Stage | Pair Key | Date Condition |
|---|---|---|---|
| case_1 | PLINTH | tranche_1_to_plinth | first_date exists AND plinth/second/lintel/third/roof/fourth all missing and count first_date to current date |
| case_2 | 2ND TRANCHE | plinth_to_tranche_2 | first_date + plinth exist AND second/lintel/third/roof/fourth missing and count plinth_date to current date |
| case_3 | LINTEL | tranche_2_to_lintel | first_date + plinth + second exist AND lintel/third/roof/fourth missing and count second_date to current date |
| case_4 | 3RD TRANCHE | lintel_to_tranche_3 | first_date + plinth + second + lintel exist AND third/roof/fourth missing and count lintel_date to current date |
| case_5 | ROOF | tranche_3_to_roof | first_date + plinth + second + lintel + third exist AND roof/fourth missing  and count third_date to current date |
| case_6 | 4TH TRANCHE | roof_to_tranche_4 | first_date + plinth + second + lintel + third + roof exist AND fourth missing and count roof_date to current date|
| case_7_pending_overall | OVERALL | overall_1st_to_roof | first_date + plinth + second + lintel + third + roof exists and count first_date to current date |

## Pending Mode: Stage Threshold Mapping

| Pending Stage | Threshold Source Key | Green | Yellow | Orange | Red |
|---|---|---|---|---|---|
| PLINTH | 1st_tranche_to_plinth | 0-45 | 46-60 | 61-75 | 76+ |
| 2ND TRANCHE | plinth_to_2nd_tranche | 0-30 | 31-45 | 46-60 | 61+ |
| LINTEL | 2nd_tranche_to_lintel | 0-30 | 31-45 | 46-60 | 61+ |
| 3RD TRANCHE | lintel_to_3rd_tranche | 0-30 | 31-45 | 46-60 | 61+ |
| ROOF | 3rd_tranche_to_roof | 0-30 | 31-45 | 46-60 | 61+ |
| 4TH TRANCHE | roof_to_4th_tranche | 0-15 | 16-30 | 31-45 | 46+ |
| OVERALL | overall_1st_tranche_to_roof | 0-180 | 181-270 | 271-360 | 361+ |
| COMPLETED | overall_1st_tranche_to_roof | 0-180 | 181-270 | 271-360 | 361+ |
| N/A or unknown stage | 1st_tranche_to_plinth (default) | 0-45 | 46-60 | 61-75 | 76+ |

## Completed Mode: Inclusion Filter

| Mode | Inclusion Condition (from sphf_normalized_kpi SQL path) |
|---|---|
| Completed | c_stage IS NOT NULL AND UPPER(TRIM(c_stage)) != 'N/A' |

## Completed Mode: Stage Case Conditions

| Case ID | Returned Stage | Pair Key | Date Condition |
|---|---|---|---|
| completed_stage_6 | 4TH TRANCHE | roof_to_tranche_4 | first_date + plinth + second + lintel + third + roof + fourth exist |
| completed_stage_7 | COMPLETED | overall_1st_to_roof | first_date + plinth + second + lintel + third + roof exist |
| completed_stage_3 | LINTEL | tranche_2_to_lintel | first_date + plinth + second + lintel + roof exist |
| completed_stage_2 | 2ND TRANCHE | plinth_to_tranche_2 | first_date + plinth + second + roof exist |
| completed_stage_1 | PLINTH | tranche_1_to_plinth | first_date + plinth + roof exist |
| no_action | N/A | not_completed_route | fallback when none of the completed-route cases match |

## Completed Mode: Stage Threshold Mapping

| Completed Stage | Threshold Source Key | Green | Yellow | Orange | Red |
|---|---|---|---|---|---|
| PLINTH | 1st_tranche_to_plinth | 0-45 | 46-60 | 61-75 | 76+ |
| 2ND TRANCHE | plinth_to_2nd_tranche | 0-30 | 31-45 | 46-60 | 61+ |
| LINTEL | 2nd_tranche_to_lintel | 0-30 | 31-45 | 46-60 | 61+ |
| 4TH TRANCHE | roof_to_4th_tranche | 0-15 | 16-30 | 31-45 | 46+ |
| COMPLETED | overall_1st_tranche_to_roof | 0-180 | 181-270 | 271-360 | 361+ |
| N/A or unknown stage | 1st_tranche_to_plinth (default) | 0-45 | 46-60 | 61-75 | 76+ |