Option Explicit

Public Sub Build_Working_Sheet_Stage1()
    Build_PNM_Private_Light_Upgrades
End Sub

Public Sub Build_PNM_Private_Light_Upgrades()

    Dim wb As Workbook
    Dim wsSource As Worksheet
    Dim wsWorking As Worksheet
    Dim wsBilling As Worksheet
    
    Dim sourceSheetName As String
    Dim workingSheetName As String
    Dim billingSheetName As String
    
    Dim colCompletionDate As Long
    Dim colFacilityID As Long
    Dim colWorkTasks As Long
    Dim colTroubleshootingHrs As Long
    Dim colNoIssueFound As Long
    Dim colJobNotes As Long
    
    Dim lastRow As Long
    Dim outputCol As Long
    Dim lastWorkingCol As Long
    
    Dim r As Long
    Dim p As Long
    Dim billingRow As Long
    Dim billingLastDataRow As Long
    Dim billingTotalRow As Long
    
    Dim taskParts As Variant
    Dim taskText As String
    Dim taskValue As String
    Dim taskHeaders As Variant
    Dim billingHeaders As Variant
    
    Dim priceGeneralTroubleshooting As Currency
    Dim priceArmInstallation As Currency
    Dim priceFixtureInstallation As Currency
    Dim pricePhotocellShortingCap As Currency
    
    sourceSheetName = "pnm_private_light_upgrades"
    workingSheetName = "Working"
    billingSheetName = "Billing"
    
    taskHeaders = Array( _
        "General Troubleshooting", _
        "Arm Installation/Replacement", _
        "Fixture Installation/Replacement", _
        "Photocell/Shorting Cap Installation/Replacement" _
    )
    
    priceGeneralTroubleshooting = 189.18
    priceArmInstallation = 189.18
    priceFixtureInstallation = 113.51
    pricePhotocellShortingCap = 60.54
    
    Set wb = ActiveWorkbook
    
    If wb Is Nothing Then
        MsgBox "No active workbook found.", vbCritical
        Exit Sub
    End If
    
    If UCase$(wb.Name) = "PERSONAL.XLSB" Then
        MsgBox "The active workbook is PERSONAL.XLSB." & vbCrLf & _
               "Click into the workbook you want to transform, then run the macro again.", vbCritical
        Exit Sub
    End If
    
    On Error Resume Next
    Set wsSource = wb.Worksheets(sourceSheetName)
    On Error GoTo 0
    
    If wsSource Is Nothing Then
        MsgBox "Source sheet not found in active workbook: " & sourceSheetName & vbCrLf & _
               "Active workbook: " & wb.Name, vbCritical
        Exit Sub
    End If
    
    lastRow = GetLastUsedRow(wsSource)
    
    If lastRow < 2 Then
        MsgBox "No data rows found on source sheet.", vbCritical
        Exit Sub
    End If
    
    colCompletionDate = FindHeaderColumn(wsSource, "Completion Date")
    colFacilityID = FindHeaderColumn(wsSource, "Facility ID")
    colWorkTasks = FindHeaderColumn(wsSource, "Work Tasks")
    colTroubleshootingHrs = FindHeaderColumn(wsSource, "Troubleshooting Hrs")
    colNoIssueFound = FindHeaderColumn(wsSource, "No Issue Found")
    colJobNotes = FindHeaderColumn(wsSource, "Job Notes")
    
    If colCompletionDate = 0 Then
        MsgBox "Required header not found: Completion Date", vbCritical
        Exit Sub
    End If
    
    If colFacilityID = 0 Then
        MsgBox "Required header not found: Facility ID", vbCritical
        Exit Sub
    End If
    
    If colWorkTasks = 0 Then
        MsgBox "Required header not found: Work Tasks", vbCritical
        Exit Sub
    End If
    
    If colTroubleshootingHrs = 0 Then
        MsgBox "Required header not found: Troubleshooting Hrs", vbCritical
        Exit Sub
    End If
    
    If colNoIssueFound = 0 Then
        MsgBox "Required header not found: No Issue Found", vbCritical
        Exit Sub
    End If
    
    If colJobNotes = 0 Then
        MsgBox "Required header not found: Job Notes", vbCritical
        Exit Sub
    End If
    
    Set wsWorking = GetOrCreateWorksheet(wb, workingSheetName)
    
    With wsWorking
        .Cells.UnMerge
        .Cells.Clear
    End With
    
    outputCol = 1
    
    wsWorking.Cells(1, outputCol).Value = "Completion Date"
    wsWorking.Range(wsWorking.Cells(2, outputCol), wsWorking.Cells(lastRow, outputCol)).Value = _
        wsSource.Range(wsSource.Cells(2, colCompletionDate), wsSource.Cells(lastRow, colCompletionDate)).Value
    outputCol = outputCol + 1
    
    wsWorking.Cells(1, outputCol).Value = "Facility ID"
    wsWorking.Range(wsWorking.Cells(2, outputCol), wsWorking.Cells(lastRow, outputCol)).Value = _
        wsSource.Range(wsSource.Cells(2, colFacilityID), wsSource.Cells(lastRow, colFacilityID)).Value
    outputCol = outputCol + 1
    
    For p = LBound(taskHeaders) To UBound(taskHeaders)
    
        wsWorking.Cells(1, outputCol).Value = taskHeaders(p)
        outputCol = outputCol + 1
    
        wsWorking.Cells(1, outputCol).Value = "Qty"
        outputCol = outputCol + 1
    
        If p = LBound(taskHeaders) Then
            wsWorking.Cells(1, outputCol).Value = "Troubleshooting Hrs"
            outputCol = outputCol + 1
        End If
    
        wsWorking.Cells(1, outputCol).Value = "Price"
        outputCol = outputCol + 1
    
        If p = LBound(taskHeaders) Then
            wsWorking.Cells(1, outputCol).Value = "Total"
            outputCol = outputCol + 1
        End If
    
    Next p
    
    For r = 2 To lastRow
        
        taskText = CStr(wsSource.Cells(r, colWorkTasks).Value)
        taskParts = Split(taskText, ",")
        
        For p = LBound(taskParts) To UBound(taskParts)
            
            taskValue = Trim$(CStr(taskParts(p)))
            
            Select Case UCase$(taskValue)
                
                Case UCase$("General Troubleshooting")
                    wsWorking.Cells(r, 3).Value = "General Troubleshooting"
                    
                Case UCase$("Arm Installation/Replacement")
                    wsWorking.Cells(r, 8).Value = "Arm Installation/Replacement"
                    
                Case UCase$("Fixture Installation/Replacement")
                    wsWorking.Cells(r, 11).Value = "Fixture Installation/Replacement"
                    
                Case UCase$("Photocell/Shorting Cap Installation/Replacement")
                    wsWorking.Cells(r, 14).Value = "Photocell/Shorting Cap Installation/Replacement"
                    
            End Select
            
        Next p
        
    Next r
    
    For r = 2 To lastRow
    
        If Len(Trim$(CStr(wsWorking.Cells(r, 3).Value))) > 0 Then
            wsWorking.Cells(r, 4).Value = 1
            wsWorking.Cells(r, 6).Value = priceGeneralTroubleshooting
        End If
    
        If Len(Trim$(CStr(wsWorking.Cells(r, 8).Value))) > 0 Then
            wsWorking.Cells(r, 9).Value = 1
            wsWorking.Cells(r, 10).Value = priceArmInstallation
        End If
    
        If Len(Trim$(CStr(wsWorking.Cells(r, 11).Value))) > 0 Then
            wsWorking.Cells(r, 12).Value = 1
            wsWorking.Cells(r, 13).Value = priceFixtureInstallation
        End If
    
        If Len(Trim$(CStr(wsWorking.Cells(r, 14).Value))) > 0 Then
            wsWorking.Cells(r, 15).Value = 1
            wsWorking.Cells(r, 16).Value = pricePhotocellShortingCap
        End If
    
    Next r
    
    For r = 2 To lastRow
        If Len(Trim$(CStr(wsSource.Cells(r, colTroubleshootingHrs).Value))) > 0 Then
            wsWorking.Cells(r, 5).Value = 0.5
        End If
    Next r
    
    wsWorking.Cells(1, outputCol).Value = "No Issue Found"
    wsWorking.Range(wsWorking.Cells(2, outputCol), wsWorking.Cells(lastRow, outputCol)).Value = _
        wsSource.Range(wsSource.Cells(2, colNoIssueFound), wsSource.Cells(lastRow, colNoIssueFound)).Value
    outputCol = outputCol + 1
    
    wsWorking.Cells(1, outputCol).Value = "Job Notes"
    wsWorking.Range(wsWorking.Cells(2, outputCol), wsWorking.Cells(lastRow, outputCol)).Value = _
        wsSource.Range(wsSource.Cells(2, colJobNotes), wsSource.Cells(lastRow, colJobNotes)).Value
    
    lastWorkingCol = wsWorking.Cells(1, wsWorking.Columns.Count).End(xlToLeft).Column
    
    With wsWorking.Sort
        .SortFields.Clear
        .SortFields.Add Key:=wsWorking.Range("A2:A" & lastRow), _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
    
        .SetRange wsWorking.Range(wsWorking.Cells(1, 1), wsWorking.Cells(lastRow, lastWorkingCol))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    
    For r = 2 To lastRow
        wsWorking.Cells(r, 7).Formula = "=IF(OR(E" & r & "="""",F" & r & "=""""),"""",IFERROR(E" & r & "*F" & r & ",""""))"
    Next r
    
    With wsWorking
        .Cells.WrapText = False
        .Rows.RowHeight = 15
        .Range("E2:E" & lastRow).NumberFormat = "0.0"
        .Range("F2:F" & lastRow).NumberFormat = "$#,##0.00"
        .Range("G2:G" & lastRow).NumberFormat = "$#,##0.00"
        .Range("J2:J" & lastRow).NumberFormat = "$#,##0.00"
        .Range("M2:M" & lastRow).NumberFormat = "$#,##0.00"
        .Range("P2:P" & lastRow).NumberFormat = "$#,##0.00"
        .Columns.AutoFit
    End With
    
    Set wsBilling = GetOrCreateWorksheet(wb, billingSheetName)
    
    With wsBilling
        .Cells.UnMerge
        .Cells.Clear
    End With
    
    billingHeaders = Array( _
        "Completion Date", _
        "Facility ID", _
        "Qty", _
        "Hrs", _
        "Price", _
        "Total", _
        "Qty", _
        "Price", _
        "Qty", _
        "Price", _
        "Qty", _
        "Price", _
        "Total", _
        "Job Notes" _
    )
    
    For p = LBound(billingHeaders) To UBound(billingHeaders)
        wsBilling.Cells(2, p + 1).Value = billingHeaders(p)
    Next p
    
    With wsBilling
        .Range("C1").Value = "Unit Pricing - Line 1 General Troubleshooting"
        .Range("G1").Value = "Unit Pricing - Line 7 Arm Installation/Replacement"
        .Range("I1").Value = "Unit Pricing - Line 2 Fixture Installation/Replacement"
        .Range("K1").Value = "Unit Pricing - Line 4 Photocell/Shorting Cap Installation/Replacement"
    End With
    
    For r = 2 To lastRow
        
        billingRow = r + 1
        
        wsBilling.Cells(billingRow, 1).Value = wsWorking.Cells(r, 1).Value
        wsBilling.Cells(billingRow, 2).Value = wsWorking.Cells(r, 2).Value
        
        wsBilling.Cells(billingRow, 3).Value = wsWorking.Cells(r, 4).Value
        wsBilling.Cells(billingRow, 4).Value = wsWorking.Cells(r, 5).Value
        wsBilling.Cells(billingRow, 5).Value = wsWorking.Cells(r, 6).Value
        wsBilling.Cells(billingRow, 6).Value = wsWorking.Cells(r, 7).Value
        
        wsBilling.Cells(billingRow, 7).Value = wsWorking.Cells(r, 9).Value
        wsBilling.Cells(billingRow, 8).Value = wsWorking.Cells(r, 10).Value
        
        wsBilling.Cells(billingRow, 9).Value = wsWorking.Cells(r, 12).Value
        wsBilling.Cells(billingRow, 10).Value = wsWorking.Cells(r, 13).Value
        
        wsBilling.Cells(billingRow, 11).Value = wsWorking.Cells(r, 15).Value
        wsBilling.Cells(billingRow, 12).Value = wsWorking.Cells(r, 16).Value
        
        wsBilling.Cells(billingRow, 13).Formula = "=SUM(F" & billingRow & ",H" & billingRow & ",J" & billingRow & ",L" & billingRow & ")"
        
        If UCase$(Trim$(CStr(wsWorking.Cells(r, 3).Value))) = UCase$("General Troubleshooting") Then
            wsBilling.Cells(billingRow, 14).Value = wsWorking.Cells(r, 18).Value
        End If
        
    Next r
    
    billingLastDataRow = lastRow + 1
    billingTotalRow = billingLastDataRow + 1
    
    wsBilling.Cells(billingTotalRow, 12).Value = "Grand Total"
    wsBilling.Cells(billingTotalRow, 13).Formula = "=SUM(M3:M" & billingLastDataRow & ")"
    
    With wsBilling
        .Cells.WrapText = False
        .Rows.RowHeight = 15
        .Rows(1).RowHeight = 52
        .Rows(2).RowHeight = 32
        
        .Range("C1:D1").Merge
        .Range("G1:H1").Merge
        .Range("I1:J1").Merge
        .Range("K1:L1").Merge
        
        .Range("C1:D1").HorizontalAlignment = xlCenter
        .Range("G1:H1").HorizontalAlignment = xlCenter
        .Range("I1:J1").HorizontalAlignment = xlCenter
        .Range("K1:L1").HorizontalAlignment = xlCenter
        
        .Range("C1:D1").VerticalAlignment = xlCenter
        .Range("G1:H1").VerticalAlignment = xlCenter
        .Range("I1:J1").VerticalAlignment = xlCenter
        .Range("K1:L1").VerticalAlignment = xlCenter
        
        .Range("C1:D1").WrapText = True
        .Range("G1:H1").WrapText = True
        .Range("I1:J1").WrapText = True
        .Range("K1:L1").WrapText = True
        
        .Columns("A").ColumnWidth = 12
        .Columns("C").ColumnWidth = 8
        .Columns("D").ColumnWidth = 8
        .Columns("G").ColumnWidth = 8
        .Columns("H").ColumnWidth = 15
        .Columns("I").ColumnWidth = 8
        .Columns("J").ColumnWidth = 15
        .Columns("K").ColumnWidth = 8
        .Columns("L").ColumnWidth = 15
        .Columns("N").ColumnWidth = 50
        
        .Columns("A").WrapText = True
        .Columns("D").WrapText = True
        .Columns("N").WrapText = True
        
        .Range("C1:F" & billingTotalRow).Interior.Color = RGB(221, 235, 247)
        .Range("I1:J" & billingTotalRow).Interior.Color = RGB(221, 235, 247)
        .Range("M1:M" & billingTotalRow).Interior.Color = RGB(221, 235, 247)
        
        .Range("D3:D" & billingLastDataRow).NumberFormat = "0.0"
        .Range("E3:F" & billingLastDataRow).NumberFormat = "$#,##0.00"
        .Range("H3:H" & billingLastDataRow).NumberFormat = "$#,##0.00"
        .Range("J3:J" & billingLastDataRow).NumberFormat = "$#,##0.00"
        .Range("L3:M" & billingTotalRow).NumberFormat = "$#,##0.00"
        
        .Rows(1).Font.Bold = True
        .Rows(2).Font.Bold = True
        .Rows(billingTotalRow).Font.Bold = True
        
        With .Range("A1:N" & billingTotalRow).Borders
            .LineStyle = xlContinuous
            .Color = RGB(217, 217, 217)
            .Weight = xlThin
        End With
    End With
    
    Application.CutCopyMode = False
    
    MsgBox "Build completed successfully." & vbCrLf & _
           "Active workbook: " & wb.Name & vbCrLf & _
           "Working rows: " & lastRow & vbCrLf & _
           "Billing data rows: " & lastRow - 1 & vbCrLf & _
           "Billing total row: " & billingTotalRow, vbInformation

End Sub

Private Function GetOrCreateWorksheet(ByVal wb As Workbook, ByVal sheetName As String) As Worksheet

    Dim ws As Worksheet
    
    On Error Resume Next
    Set ws = wb.Worksheets(sheetName)
    On Error GoTo 0
    
    If ws Is Nothing Then
        Set ws = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        ws.Name = sheetName
    End If
    
    Set GetOrCreateWorksheet = ws

End Function

Private Function FindHeaderColumn(ByVal ws As Worksheet, ByVal headerName As String) As Long

    Dim foundCell As Range
    
    Set foundCell = ws.Rows(1).Find( _
        What:=headerName, _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        MatchCase:=False _
    )
    
    If foundCell Is Nothing Then
        FindHeaderColumn = 0
    Else
        FindHeaderColumn = foundCell.Column
    End If

End Function

Private Function GetLastUsedRow(ByVal ws As Worksheet) As Long

    Dim foundCell As Range
    
    Set foundCell = ws.Cells.Find( _
        What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False _
    )
    
    If foundCell Is Nothing Then
        GetLastUsedRow = 0
    Else
        GetLastUsedRow = foundCell.Row
    End If

End Function