Option Explicit

' EAHP BOOST workshop: synthetic demonstration data only.
' Reads the active data sheet; creates a new worksheet without changing source data.
' Review locally before running any generated or shared macro.
Public Sub SummariseSyntheticReviews()
    Dim source As Worksheet, report As Worksheet
    Dim idCol As Variant, periodCol As Variant, resultCol As Variant
    Dim lastRow As Long, r As Long, i As Long, outRow As Long
    Dim periodText As String, resultText As String
    Dim yesCount(1 To 2) As Long, noCount(1 To 2) As Long
    Dim missingCount(1 To 2) As Long, invalidCount(1 To 2) As Long
    Dim otherPeriod As Long, missingID As Long, validN As Long
    Dim existing As Worksheet

    Set source = ActiveSheet
    If source.Name = "Review summary" Then
        MsgBox "Select the source data sheet and try again.", vbExclamation
        Exit Sub
    End If
    On Error Resume Next
    Set existing = source.Parent.Worksheets("Review summary")
    On Error GoTo 0
    If Not existing Is Nothing Then
        MsgBox "A sheet called Review summary already exists. Rename or remove it before running again.", vbExclamation
        Exit Sub
    End If

    idCol = Application.Match("Episode_ID", source.Rows(1), 0)
    periodCol = Application.Match("Period", source.Rows(1), 0)
    resultCol = Application.Match("Review_72h", source.Rows(1), 0)
    If IsError(idCol) Or IsError(periodCol) Or IsError(resultCol) Then
        MsgBox "Required headers not found: Episode_ID, Period, Review_72h.", vbExclamation
        Exit Sub
    End If

    ' Check the entire used range so that missing IDs are not silently ignored.
    If Application.WorksheetFunction.CountA(source.Cells) = 0 Then Exit Sub
    lastRow = source.Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For r = 2 To lastRow
        If Application.WorksheetFunction.CountA(source.Rows(r)) > 0 Then
            If Len(Trim$(CStr(source.Cells(r, CLng(idCol)).Value))) = 0 Then
                missingID = missingID + 1
            Else
                periodText = UCase$(Trim$(CStr(source.Cells(r, CLng(periodCol)).Value)))
                resultText = UCase$(Trim$(CStr(source.Cells(r, CLng(resultCol)).Value)))
                i = 0
                If periodText = "BEFORE" Then i = 1
                If periodText = "AFTER" Then i = 2
                If i = 0 Then
                    otherPeriod = otherPeriod + 1
                Else
                    Select Case resultText
                        Case "YES": yesCount(i) = yesCount(i) + 1
                        Case "NO": noCount(i) = noCount(i) + 1
                        Case "": missingCount(i) = missingCount(i) + 1
                        Case Else: invalidCount(i) = invalidCount(i) + 1
                    End Select
                End If
            End If
        End If
    Next r

    Set report = source.Parent.Worksheets.Add(After:=source.Parent.Worksheets(source.Parent.Worksheets.Count))
    report.Name = "Review summary"
    report.Range("A1").Value = "Synthetic data: descriptive review summary"
    report.Range("A2").Value = "Valid denominator = Yes + No. Missing and invalid outcomes excluded."
    report.Range("A4:H4").Value = Array("Period", "Yes", "No", "Missing", "Invalid", "Valid n", "Yes / valid n", "Total episodes")
    For i = 1 To 2
        outRow = i + 4
        If i = 1 Then report.Cells(outRow, 1).Value = "Before" Else report.Cells(outRow, 1).Value = "After"
        report.Cells(outRow, 2).Value = yesCount(i)
        report.Cells(outRow, 3).Value = noCount(i)
        report.Cells(outRow, 4).Value = missingCount(i)
        report.Cells(outRow, 5).Value = invalidCount(i)
        validN = yesCount(i) + noCount(i)
        report.Cells(outRow, 6).Value = validN
        If validN > 0 Then report.Cells(outRow, 7).Value = yesCount(i) / validN
        report.Cells(outRow, 8).Value = validN + missingCount(i) + invalidCount(i)
    Next i
    report.Range("G5:G6").NumberFormat = "0.0%"
    report.Range("A8").Value = "After minus before (percentage points)"
    If (yesCount(1) + noCount(1) > 0) And (yesCount(2) + noCount(2) > 0) Then
        report.Range("B8").Value = 100 * (yesCount(2) / (yesCount(2) + noCount(2)) - yesCount(1) / (yesCount(1) + noCount(1)))
        report.Range("B8").NumberFormat = "0.0"
    End If
    report.Range("A10").Value = "Rows with an unrecognised or missing period"
    report.Range("B10").Value = otherPeriod
    report.Range("A11").Value = "Non-empty rows missing Episode_ID"
    report.Range("B11").Value = missingID
    report.Range("A13").Value = "Descriptive only. Missing data and before/after confounding limit interpretation."
    report.Range("A1:H1").Font.Bold = True
    report.Range("A4:H4").Font.Bold = True
    report.Columns("A:H").AutoFit
    MsgBox "Summary created. Check its counts and exclusions against the source data.", vbInformation
End Sub