Attribute VB_Name = "DigitalOperationsCommandCenter"
Option Explicit

' Digital Operations Command Center
' Import this module into Sai_Prakash_Digital_Operations_Command_Center.xlsx,
' then save the workbook as .xlsm. All public records in the workbook are invented.

Private Const DASHBOARD_SHEET As String = "Executive Dashboard"
Private Const RECRUITMENT_SHEET As String = "Recruitment Pipeline"
Private Const ONBOARDING_SHEET As String = "Onboarding Tracker"
Private Const ADMIN_SHEET As String = "Admin Requests"
Private Const AUTOMATION_SHEET As String = "Automation Register"
Private Const FIRST_DATA_ROW As Long = 6

Public Sub RefreshCommandCenter()
    Dim priorCalculation As XlCalculation

    On Error GoTo CleanFail
    priorCalculation = Application.Calculation
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    ThisWorkbook.RefreshAll
    Application.CalculateFullRebuild
    ThisWorkbook.Worksheets(DASHBOARD_SHEET).Range("B3").Value = Now
    ThisWorkbook.Worksheets(DASHBOARD_SHEET).Range("B3").NumberFormat = "dd mmm yyyy hh:mm"
    ValidateControls False

CleanExit:
    Application.Calculation = priorCalculation
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub

CleanFail:
    MsgBox "Refresh stopped: " & Err.Description, vbExclamation, "Digital Operations Command Center"
    Resume CleanExit
End Sub

Public Sub ImportCandidateCSV()
    Dim selectedFile As Variant
    Dim sourceBook As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim sourceLastRow As Long
    Dim targetRow As Long
    Dim sourceRow As Long
    Dim candidateId As String
    Dim importedCount As Long
    Dim duplicateCount As Long

    selectedFile = Application.GetOpenFilename("CSV files (*.csv),*.csv", , "Select a sanitized candidate export")
    If VarType(selectedFile) = vbBoolean Then Exit Sub

    On Error GoTo ImportFail
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Set targetSheet = ThisWorkbook.Worksheets(RECRUITMENT_SHEET)
    Set sourceBook = Workbooks.Open(CStr(selectedFile), ReadOnly:=True)
    Set sourceSheet = sourceBook.Worksheets(1)
    sourceLastRow = sourceSheet.Cells(sourceSheet.Rows.Count, 1).End(xlUp).Row

    For sourceRow = 2 To sourceLastRow
        candidateId = Trim$(CStr(sourceSheet.Cells(sourceRow, 1).Value))
        If Len(candidateId) > 0 Then
            If Application.WorksheetFunction.CountIf(targetSheet.Columns(1), candidateId) = 0 Then
                targetRow = targetSheet.Cells(targetSheet.Rows.Count, 1).End(xlUp).Row + 1
                targetSheet.Range(targetSheet.Cells(targetRow, 1), targetSheet.Cells(targetRow, 11)).Value = _
                    sourceSheet.Range(sourceSheet.Cells(sourceRow, 1), sourceSheet.Cells(sourceRow, 11)).Value
                targetSheet.Cells(targetRow, 12).Formula = _
                    "=IF(OR(I" & targetRow & "=""Hired"",I" & targetRow & "=""Rejected""),K" & targetRow & "-E" & targetRow & ",TODAY()-E" & targetRow & ")"
                targetSheet.Cells(targetRow, 13).Formula = _
                    "=IF(OR(I" & targetRow & "=""Hired"",I" & targetRow & "=""Rejected""),""Closed"",IF(K" & targetRow & "<TODAY(),""Breach"",IF(K" & targetRow & "<=TODAY()+2,""Due soon"",""On track"")))"
                targetSheet.Cells(targetRow, 14).Formula = _
                    "=LOWER(TRIM(B" & targetRow & "))&""|""&LOWER(TRIM(C" & targetRow & "))"
                importedCount = importedCount + 1
            Else
                duplicateCount = duplicateCount + 1
            End If
        End If
    Next sourceRow

    sourceBook.Close SaveChanges:=False
    Set sourceBook = Nothing
    Application.CalculateFullRebuild
    MsgBox importedCount & " candidate(s) imported; " & duplicateCount & _
        " duplicate Candidate ID(s) skipped.", vbInformation, "Import complete"

ImportExit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub

ImportFail:
    If Not sourceBook Is Nothing Then sourceBook.Close SaveChanges:=False
    MsgBox "Import stopped: " & Err.Description, vbExclamation, "Candidate import"
    Resume ImportExit
End Sub

Public Sub AdvanceSelectedCandidate()
    Dim sheet As Worksheet
    Dim selectedRow As Long
    Dim nextStage As String
    Dim nextAction As String

    Set sheet = ThisWorkbook.Worksheets(RECRUITMENT_SHEET)
    If Not ActiveSheet Is sheet Then
        MsgBox "Select a candidate row on the Recruitment Pipeline sheet first.", vbInformation
        Exit Sub
    End If

    selectedRow = ActiveCell.Row
    If selectedRow < FIRST_DATA_ROW Or Len(Trim$(CStr(sheet.Cells(selectedRow, 1).Value))) = 0 Then
        MsgBox "Select a populated candidate row first.", vbInformation
        Exit Sub
    End If

    nextStage = Trim$(InputBox("Enter one stage: Applied, Screening, Shortlisted, Interview, Offer, Hired, Rejected", _
        "Advance candidate", CStr(sheet.Cells(selectedRow, 9).Value)))
    If Len(nextStage) = 0 Then Exit Sub
    If Not IsAllowedStage(nextStage) Then
        MsgBox "The stage is not in the approved list.", vbExclamation
        Exit Sub
    End If

    Select Case LCase$(nextStage)
        Case "applied": nextAction = "CV review"
        Case "screening": nextAction = "Phone or technical screen"
        Case "shortlisted": nextAction = "Book interview"
        Case "interview": nextAction = "Panel decision"
        Case "offer": nextAction = "Prepare or review offer"
        Case "hired": nextAction = "Create onboarding"
        Case "rejected": nextAction = "Close record and retain per policy"
    End Select

    sheet.Cells(selectedRow, 9).Value = CanonicalStage(nextStage)
    sheet.Cells(selectedRow, 10).Value = nextAction
    sheet.Cells(selectedRow, 11).Value = Date + StageSlaDays(nextStage)
    Application.CalculateFullRebuild
End Sub

Public Sub CreateOnboardingFromSelectedCandidate()
    Dim recruitmentSheet As Worksheet
    Dim onboardingSheet As Worksheet
    Dim selectedRow As Long
    Dim newRow As Long
    Dim candidateId As String

    Set recruitmentSheet = ThisWorkbook.Worksheets(RECRUITMENT_SHEET)
    Set onboardingSheet = ThisWorkbook.Worksheets(ONBOARDING_SHEET)
    If Not ActiveSheet Is recruitmentSheet Then
        MsgBox "Select a hired candidate on the Recruitment Pipeline sheet first.", vbInformation
        Exit Sub
    End If

    selectedRow = ActiveCell.Row
    If selectedRow < FIRST_DATA_ROW Then Exit Sub
    If LCase$(Trim$(CStr(recruitmentSheet.Cells(selectedRow, 9).Value))) <> "hired" Then
        MsgBox "Onboarding can be created only after the stage is Hired.", vbExclamation
        Exit Sub
    End If
    If LCase$(Trim$(CStr(recruitmentSheet.Cells(selectedRow, 8).Value))) <> "yes" Then
        MsgBox "Consent is not confirmed. The automation will not continue.", vbCritical
        Exit Sub
    End If

    candidateId = CStr(recruitmentSheet.Cells(selectedRow, 1).Value)
    If Application.WorksheetFunction.CountIf(onboardingSheet.Columns(1), candidateId) > 0 Then
        MsgBox "An onboarding row already exists for " & candidateId & ".", vbInformation
        Exit Sub
    End If

    newRow = onboardingSheet.Cells(onboardingSheet.Rows.Count, 1).End(xlUp).Row + 1
    onboardingSheet.Cells(newRow, 1).Value = candidateId
    onboardingSheet.Cells(newRow, 2).Value = recruitmentSheet.Cells(selectedRow, 2).Value
    onboardingSheet.Cells(newRow, 3).Value = RoleToDepartment(CStr(recruitmentSheet.Cells(selectedRow, 3).Value))
    onboardingSheet.Cells(newRow, 4).Value = Date + 14
    onboardingSheet.Range(onboardingSheet.Cells(newRow, 5), onboardingSheet.Cells(newRow, 11)).Value = "No"
    onboardingSheet.Cells(newRow, 12).Formula = "=COUNTIF(E" & newRow & ":K" & newRow & ",""Yes"")/7"
    onboardingSheet.Cells(newRow, 13).Formula = "=IF(L" & newRow & "=1,""Ready"",IF(D" & newRow & "<=TODAY()+7,""Blocked"",""In progress""))"
    onboardingSheet.Cells(newRow, 14).Value = recruitmentSheet.Cells(selectedRow, 6).Value
    Application.CalculateFullRebuild
    MsgBox "Onboarding row created for " & candidateId & ".", vbInformation
End Sub

Public Sub CreateOverdueSummary()
    Dim summarySheet As Worksheet
    Dim nextRow As Long

    On Error Resume Next
    Set summarySheet = ThisWorkbook.Worksheets("Overdue Summary")
    On Error GoTo 0
    If summarySheet Is Nothing Then
        Set summarySheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        summarySheet.Name = "Overdue Summary"
    Else
        summarySheet.Cells.Clear
    End If

    summarySheet.Range("A1:F1").Value = Array("Source", "Record ID", "Owner", "Status", "Due date", "Required action")
    nextRow = 2
    nextRow = CopyBreaches(ThisWorkbook.Worksheets(RECRUITMENT_SHEET), 1, 6, 13, 11, 10, "Recruitment", summarySheet, nextRow)
    nextRow = CopyBreaches(ThisWorkbook.Worksheets(ADMIN_SHEET), 1, 7, 10, 6, 11, "Admin request", summarySheet, nextRow)
    summarySheet.Rows(1).Font.Bold = True
    summarySheet.Rows(1).Interior.Color = RGB(18, 48, 74)
    summarySheet.Rows(1).Font.Color = RGB(255, 255, 255)
    summarySheet.Columns("A:F").AutoFit
    summarySheet.Activate
End Sub

Public Sub ExportWeeklyManagementPDF()
    Dim outputPath As String

    If Len(ThisWorkbook.Path) = 0 Then
        MsgBox "Save the workbook before exporting.", vbExclamation
        Exit Sub
    End If
    outputPath = ThisWorkbook.Path & Application.PathSeparator & _
        "Digital_Operations_Management_Summary_" & Format$(Date, "yyyymmdd") & ".pdf"
    ThisWorkbook.Worksheets(DASHBOARD_SHEET).ExportAsFixedFormat _
        Type:=xlTypePDF, Filename:=outputPath, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    MsgBox "Management PDF created:" & vbCrLf & outputPath, vbInformation
End Sub

Public Sub ValidateControls(Optional ByVal showMessage As Boolean = True)
    Dim recruitmentSheet As Worksheet
    Dim onboardingSheet As Worksheet
    Dim adminSheet As Worksheet
    Dim rowNumber As Long
    Dim lastRow As Long
    Dim issueCount As Long

    Set recruitmentSheet = ThisWorkbook.Worksheets(RECRUITMENT_SHEET)
    Set onboardingSheet = ThisWorkbook.Worksheets(ONBOARDING_SHEET)
    Set adminSheet = ThisWorkbook.Worksheets(ADMIN_SHEET)

    lastRow = recruitmentSheet.Cells(recruitmentSheet.Rows.Count, 1).End(xlUp).Row
    For rowNumber = FIRST_DATA_ROW To lastRow
        recruitmentSheet.Rows(rowNumber).Interior.Pattern = xlNone
        If Len(Trim$(CStr(recruitmentSheet.Cells(rowNumber, 1).Value))) = 0 Or _
           Len(Trim$(CStr(recruitmentSheet.Cells(rowNumber, 3).Value))) = 0 Or _
           Len(Trim$(CStr(recruitmentSheet.Cells(rowNumber, 9).Value))) = 0 Or _
           LCase$(Trim$(CStr(recruitmentSheet.Cells(rowNumber, 8).Value))) <> "yes" Then
            recruitmentSheet.Range(recruitmentSheet.Cells(rowNumber, 1), recruitmentSheet.Cells(rowNumber, 14)).Interior.Color = RGB(253, 236, 236)
            issueCount = issueCount + 1
        End If
    Next rowNumber

    lastRow = onboardingSheet.Cells(onboardingSheet.Rows.Count, 1).End(xlUp).Row
    For rowNumber = FIRST_DATA_ROW To lastRow
        If onboardingSheet.Cells(rowNumber, 13).Value = "Blocked" Then issueCount = issueCount + 1
    Next rowNumber

    lastRow = adminSheet.Cells(adminSheet.Rows.Count, 1).End(xlUp).Row
    For rowNumber = FIRST_DATA_ROW To lastRow
        If adminSheet.Cells(rowNumber, 10).Value = "Breach" Then issueCount = issueCount + 1
    Next rowNumber

    ThisWorkbook.Worksheets(DASHBOARD_SHEET).Range("E3").Value = _
        IIf(issueCount = 0, "Ready for review", CStr(issueCount) & " control issue(s)")
    If showMessage Then MsgBox issueCount & " control issue(s) found.", vbInformation, "Validation complete"
End Sub

Public Sub ClearWorkbookFilters()
    Dim sheet As Worksheet
    On Error Resume Next
    For Each sheet In ThisWorkbook.Worksheets
        If sheet.FilterMode Then sheet.ShowAllData
    Next sheet
    On Error GoTo 0
End Sub

Private Function CopyBreaches(ByVal sourceSheet As Worksheet, ByVal idColumn As Long, _
    ByVal ownerColumn As Long, ByVal statusColumn As Long, ByVal dueColumn As Long, _
    ByVal actionColumn As Long, ByVal sourceLabel As String, ByVal targetSheet As Worksheet, _
    ByVal firstTargetRow As Long) As Long

    Dim sourceRow As Long
    Dim lastRow As Long
    Dim targetRow As Long

    targetRow = firstTargetRow
    lastRow = sourceSheet.Cells(sourceSheet.Rows.Count, idColumn).End(xlUp).Row
    For sourceRow = FIRST_DATA_ROW To lastRow
        If LCase$(Trim$(CStr(sourceSheet.Cells(sourceRow, statusColumn).Value))) = "breach" Then
            targetSheet.Cells(targetRow, 1).Value = sourceLabel
            targetSheet.Cells(targetRow, 2).Value = sourceSheet.Cells(sourceRow, idColumn).Value
            targetSheet.Cells(targetRow, 3).Value = sourceSheet.Cells(sourceRow, ownerColumn).Value
            targetSheet.Cells(targetRow, 4).Value = sourceSheet.Cells(sourceRow, statusColumn).Value
            targetSheet.Cells(targetRow, 5).Value = sourceSheet.Cells(sourceRow, dueColumn).Value
            targetSheet.Cells(targetRow, 6).Value = sourceSheet.Cells(sourceRow, actionColumn).Value
            targetRow = targetRow + 1
        End If
    Next sourceRow
    CopyBreaches = targetRow
End Function

Private Function IsAllowedStage(ByVal value As String) As Boolean
    Select Case LCase$(Trim$(value))
        Case "applied", "screening", "shortlisted", "interview", "offer", "hired", "rejected"
            IsAllowedStage = True
        Case Else
            IsAllowedStage = False
    End Select
End Function

Private Function CanonicalStage(ByVal value As String) As String
    CanonicalStage = UCase$(Left$(Trim$(value), 1)) & LCase$(Mid$(Trim$(value), 2))
End Function

Private Function StageSlaDays(ByVal stage As String) As Long
    Select Case LCase$(Trim$(stage))
        Case "applied", "screening": StageSlaDays = 2
        Case "shortlisted", "interview": StageSlaDays = 3
        Case "offer": StageSlaDays = 2
        Case Else: StageSlaDays = 0
    End Select
End Function

Private Function RoleToDepartment(ByVal roleName As String) As String
    If InStr(1, roleName, "HR", vbTextCompare) > 0 Then
        RoleToDepartment = "Administration"
    ElseIf InStr(1, roleName, "IT", vbTextCompare) > 0 Then
        RoleToDepartment = "Digital"
    ElseIf InStr(1, roleName, "Data", vbTextCompare) > 0 Then
        RoleToDepartment = "Digital"
    Else
        RoleToDepartment = "Operations"
    End If
End Function