Attribute VB_Name = "Módulo1"
Option Explicit

'====================================================================================
' Aplicacao: Dashboard de Aprovacao Documental | SLA, idade e Evidencias Contratuais
' Versao: v6
' Objetivo:
'   1) Atualizar a camada Working com base no Log_Documentos
'   2) Criar/validar Log_Evidencias para registros contemporaneos
'   3) Cruzar documentos atrasados com cartas, RDOs e demais evidencias
'   4) Recalcular KPIs do Dashboard, incluindo exposicao a pleitos
'   5) Aplicar validacoes, formatacoes e alertas gerenciais
'
' Como usar:
'   - Salvar o arquivo Excel como .XLSM
'   - ALT+F11 > Arquivo > Importar arquivo > selecionar este .bas
'   - Executar: Atualizar_Dashboard_Documental
'   - Ivaldo@gsmanagement.com.br 21 999787152
'====================================================================================

Private Const SH_LOG As String = "Log_Documentos"
Private Const SH_EVID As String = "Log_Evidencias"
Private Const SH_WORK As String = "Working"
Private Const SH_DASH As String = "Dashboard"
Private Const SH_LISTAS As String = "Listas"

Private Const FIRST_DATA_ROW As Long = 2
Private Const MAX_DEFAULT_ROW As Long = 5000

'====================================================================================
' MACRO PRINCIPAL
'====================================================================================
Public Sub Atualizar_Dashboard_Documental()
    On Error GoTo TratarErro

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual

    ValidarEstruturaMinima
    PrepararLogEvidencias
    PrepararListasApoio
    AplicarValidacoesLog
    AplicarValidacoesEvidencias
    AtualizarWorking
    AtualizarDashboard
    AplicarFormatacaoBase
    GerarAlertaExposicaoContratual

    Application.Calculation = xlCalculationAutomatic
    Application.CalculateFull
    Application.ScreenUpdating = True
    Application.EnableEvents = True

    MsgBox "Dashboard documental e matriz de evidencias atualizados com sucesso.", vbInformation, "Controle Documental | SLA + Evidencias"
    Exit Sub

TratarErro:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    MsgBox "Erro na atualizacao do dashboard: " & Err.Number & " - " & Err.Description, vbCritical, "Erro VBA"
End Sub

'====================================================================================
' ESTRUTURA BASICA
'====================================================================================
Private Sub ValidarEstruturaMinima()
    Dim arr, i As Long
    arr = Array(SH_LOG, SH_WORK, SH_DASH, SH_LISTAS)

    For i = LBound(arr) To UBound(arr)
        If Not WorksheetExists(CStr(arr(i))) Then
            Err.Raise vbObjectError + 100, , "A aba obrigatoria nao foi encontrada: " & CStr(arr(i))
        End If
    Next i

    Call ObterOuCriarAba(SH_EVID)
End Sub

Private Function WorksheetExists(ByVal sheetName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    WorksheetExists = Not ws Is Nothing
    On Error GoTo 0
End Function

Private Function ObterOuCriarAba(ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0

    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        ws.Name = sheetName
    End If

    Set ObterOuCriarAba = ws
End Function

Private Function UltimaLinha(ByVal ws As Worksheet, ByVal colRef As String) As Long
    Dim lr As Long
    lr = ws.Cells(ws.Rows.Count, colRef).End(xlUp).Row
    If lr < FIRST_DATA_ROW Then lr = FIRST_DATA_ROW
    UltimaLinha = lr
End Function

Private Function ExisteCabecalho(ByVal ws As Worksheet, ByVal headerText As String) As Boolean
    ExisteCabecalho = Not ws.Rows(1).Find(What:=headerText, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing
End Function

Private Sub LimparFaixaSegura(ByVal rng As Range)
    'Evita o erro 1004: Nao podemos fazer isto em uma celula mesclada.
    'Antes de limpar a faixa, remove mesclas existentes dentro da area-alvo.
    On Error Resume Next
    rng.UnMerge
    On Error GoTo 0
    rng.ClearContents
End Sub

'====================================================================================
' LOG DE EVIDENCIAS
'====================================================================================
Private Sub PrepararLogEvidencias()
    Dim ws As Worksheet
    Set ws = ObterOuCriarAba(SH_EVID)

    If Len(Trim(ws.Range("A1").Value)) = 0 Then
        ws.Range("A1:M1").Value = Array( _
            "ID Evidencia", "Tipo", "Data", "N Documento", "Origem", "Assunto", _
            "Documento Relacionado", "Disciplina", "Responsavel", "Evento Registrado", _
            "Impacto Potencial", "Status Evidencia", "Link/Arquivo")
    End If

    FormatarCabecalho ws.Range("A1:M1")
    ws.Columns("A:M").ColumnWidth = 18
    ws.Columns("F:F").ColumnWidth = 32
    ws.Columns("J:J").ColumnWidth = 42
    ws.Columns("M:M").ColumnWidth = 36
    ws.Columns("A:M").WrapText = True
    ws.Range("C:C").NumberFormat = "dd/mm/yyyy"
End Sub

Private Sub PrepararListasApoio()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(SH_LISTAS)

    'A lista principal ja existia. As novas listas sao gravadas em colunas auxiliares para validacao.
    ws.Range("D1:D8").Value = Application.Transpose(Array("Tipo Evidencia", "Carta", "RDO", "Ata", "E-mail", "Relatorio", "Notificacao", "Outro"))
    ws.Range("E1:E7").Value = Application.Transpose(Array("Origem", "Contratante", "Contratada", "Fiscalizacao", "Gerenciadora", "Projetista", "Outro"))
    ws.Range("F1:F7").Value = Application.Transpose(Array("Impacto", "Prazo", "Custo", "Escopo", "Qualidade", "Pleito", "Sem impacto"))
    ws.Range("G1:G6").Value = Application.Transpose(Array("Status Evidencia", "Aberta", "Pendente", "Em tratamento", "Tratada", "Encerrada"))

    FormatarCabecalho ws.Range("D1:G1")
    ws.Columns("D:G").AutoFit
End Sub

'====================================================================================
' VALIDACOES
'====================================================================================
Private Sub AplicarValidacoesLog()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets(SH_LOG)
    lastRow = Application.Max(UltimaLinha(ws, "A") + 200, 300)

    AplicarLista ws.Range("B2:B" & lastRow), "=" & SH_LISTAS & "!$A$19:$A$24" 'Categoria
    AplicarLista ws.Range("C2:C" & lastRow), "=" & SH_LISTAS & "!$A$10:$A$16" 'Disciplina
    AplicarLista ws.Range("I2:I" & lastRow), "=" & SH_LISTAS & "!$A$2:$A$7"   'Status
    AplicarLista ws.Range("M2:M" & lastRow), "SIM,NAO"                         'Critico?
End Sub

Private Sub AplicarValidacoesEvidencias()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets(SH_EVID)
    lastRow = Application.Max(UltimaLinha(ws, "A") + 200, 300)

    AplicarLista ws.Range("B2:B" & lastRow), "=" & SH_LISTAS & "!$D$2:$D$8" 'Tipo
    AplicarLista ws.Range("E2:E" & lastRow), "=" & SH_LISTAS & "!$E$2:$E$7" 'Origem
    AplicarLista ws.Range("H2:H" & lastRow), "=" & SH_LISTAS & "!$A$10:$A$16" 'Disciplina
    AplicarLista ws.Range("K2:K" & lastRow), "=" & SH_LISTAS & "!$F$2:$F$7" 'Impacto
    AplicarLista ws.Range("L2:L" & lastRow), "=" & SH_LISTAS & "!$G$2:$G$6" 'Status
End Sub

Private Sub AplicarLista(ByVal rng As Range, ByVal lista As String)
    On Error Resume Next
    rng.Validation.Delete
    On Error GoTo 0

    With rng.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=lista
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "Selecionar valor"
        .ErrorTitle = "Valor invalido"
        .InputMessage = "Selecione um valor da lista."
        .ErrorMessage = "Use apenas valores previstos na lista de validacao."
    End With
End Sub

'====================================================================================
' CAMADA WORKING
'====================================================================================
Private Sub AtualizarWorking()
    Dim wsLog As Worksheet, wsW As Worksheet
    Dim lastLog As Long, lastWork As Long

    Set wsLog = ThisWorkbook.Worksheets(SH_LOG)
    Set wsW = ThisWorkbook.Worksheets(SH_WORK)

    lastLog = UltimaLinha(wsLog, "A")
    lastWork = Application.Max(lastLog, FIRST_DATA_ROW)

    LimparFaixaSegura wsW.Range("A2:U" & MAX_DEFAULT_ROW)

    wsW.Range("A1:U1").Value = Array( _
        "ID Documento", "Categoria", "Disciplina", "Responsavel", "Data Submissao", _
        "Data Limite", "Data Aprovacao", "Status", "Dias Decorridos", "Dias Atraso SLA", _
        "Faixa Aging", "Aberto?", "Aprovado?", "Critico?", "Impacto Potencial", _
        "Qtd Evidencias", "Qtd Cartas", "Qtd RDOs", "Possui Evidencia?", _
        "Atrasado Sem Evidencia?", "Risco Contratual")

    If lastLog < FIRST_DATA_ROW Or Len(wsLog.Range("A2").Value) = 0 Then Exit Sub

    With wsW
        .Range("A2:A" & lastWork).Formula = "=IF(" & SH_LOG & "!A2="""",""""," & SH_LOG & "!A2)"
        .Range("B2:B" & lastWork).Formula = "=IF(" & SH_LOG & "!B2="""",""""," & SH_LOG & "!B2)"
        .Range("C2:C" & lastWork).Formula = "=IF(" & SH_LOG & "!C2="""",""""," & SH_LOG & "!C2)"
        .Range("D2:D" & lastWork).Formula = "=IF(" & SH_LOG & "!D2="""",""""," & SH_LOG & "!D2)"
        .Range("E2:E" & lastWork).Formula = "=IF(" & SH_LOG & "!E2="""",""""," & SH_LOG & "!E2)"
        .Range("F2:F" & lastWork).Formula = "=IF(" & SH_LOG & "!G2="""",""""," & SH_LOG & "!G2)"
        .Range("G2:G" & lastWork).Formula = "=IF(" & SH_LOG & "!H2="""",""""," & SH_LOG & "!H2)"
        .Range("H2:H" & lastWork).Formula = "=IF(" & SH_LOG & "!I2="""",""""," & SH_LOG & "!I2)"

        .Range("I2:I" & lastWork).Formula = "=IF(A2="""","""",IF(G2="""",TODAY()-E2,G2-E2))"
        .Range("J2:J" & lastWork).Formula = "=IF(A2="""","""",MAX(0,IF(G2="""",TODAY()-F2,G2-F2)))"
        .Range("K2:K" & lastWork).Formula = _
            "=IF(A2="""","""",IF(J2<=10,""0_10 DIAS DELAY"",IF(J2<=20,""10_20 DIAS DELAY"",IF(J2<=30,""20_30 DIAS DELAY"",IF(J2<=40,""30_40 DIAS DELAY"",IF(J2<=50,""40_50 DIAS DELAY"",""50_ABOVE 50 DAYS DELAY""))))))"
        .Range("L2:L" & lastWork).Formula = "=IF(A2="""","""",IF(OR(H2=""Submitted"",H2=""Under Review""),1,0))"
        .Range("M2:M" & lastWork).Formula = "=IF(A2="""","""",IF(OR(H2=""Approved"",H2=""Approved with Comments""),1,0))"
        .Range("N2:N" & lastWork).Formula = "=IF(" & SH_LOG & "!M2="""",""""," & SH_LOG & "!M2)"
        .Range("O2:O" & lastWork).Formula = "=IF(" & SH_LOG & "!N2="""",""""," & SH_LOG & "!N2)"

        'Novos campos: cruzamento com Log_Evidencias pela coluna G = Documento Relacionado.
        .Range("P2:P" & lastWork).Formula = "=IF(A2="""","""",COUNTIF(" & SH_EVID & "!$G:$G,A2))"
        .Range("Q2:Q" & lastWork).Formula = "=IF(A2="""","""",COUNTIFS(" & SH_EVID & "!$G:$G,A2," & SH_EVID & "!$B:$B,""Carta""))"
        .Range("R2:R" & lastWork).Formula = "=IF(A2="""","""",COUNTIFS(" & SH_EVID & "!$G:$G,A2," & SH_EVID & "!$B:$B,""RDO""))"
        .Range("S2:S" & lastWork).Formula = "=IF(A2="""","""",IF(P2>0,""SIM"",""NAO""))"
        .Range("T2:T" & lastWork).Formula = "=IF(A2="""","""",IF(AND(J2>0,P2=0),""SIM"",""NAO""))"
        .Range("U2:U" & lastWork).Formula = _
            "=IF(A2="""","""",IF(AND(J2>30,P2=0),""ALTO - Atraso sem evidencia"",IF(AND(J2>30,P2>0),""ALTO - Atraso com evidencia"",IF(AND(J2>0,P2=0),""MEDIO - Sem contraprova"",IF(AND(J2>0,P2>0),""MEDIO - Evidenciado"",""BAIXO"")))))"

        .Range("E:G").NumberFormat = "dd/mm/yyyy"
        .Range("I:J,P:R").NumberFormat = "0"
    End With
End Sub

'====================================================================================
' DASHBOARD E INDICADORES
'====================================================================================
Private Sub AtualizarDashboard()
    Dim wsD As Worksheet, wsW As Worksheet, wsE As Worksheet
    Dim lastW As Long, lastE As Long

    Set wsD = ThisWorkbook.Worksheets(SH_DASH)
    Set wsW = ThisWorkbook.Worksheets(SH_WORK)
    Set wsE = ThisWorkbook.Worksheets(SH_EVID)

    lastW = UltimaLinha(wsW, "A")
    lastE = UltimaLinha(wsE, "A")

    LimparFaixaSegura wsD.Range("A1:H35")

    wsD.Range("A1").Value = "Dashboard de Aprovacao Documental, SLA e Evidencias Contratuais"
    wsD.Range("H3").Formula = "=TODAY()"
    wsD.Range("G3").Value = "Data de status"

    'KPIs principais
    wsD.Range("A5:H5").Value = Array("Total Docs", "Em Aberto", "Aprovados", "% Aprovacao", "Docs Atrasados", "Criticos Atrasados", "Com Evidencia", "Atrasados sem Evidencia")
    wsD.Range("A6").Formula = "=COUNTA(" & SH_WORK & "!$A$2:$A$" & lastW & ")"
    wsD.Range("B6").Formula = "=SUM(" & SH_WORK & "!$L$2:$L$" & lastW & ")"
    wsD.Range("C6").Formula = "=SUM(" & SH_WORK & "!$M$2:$M$" & lastW & ")"
    wsD.Range("D6").Formula = "=IFERROR(C6/A6,0)"
    wsD.Range("E6").Formula = "=COUNTIF(" & SH_WORK & "!$J$2:$J$" & lastW & ","">0"")"
    wsD.Range("F6").Formula = "=COUNTIFS(" & SH_WORK & "!$N$2:$N$" & lastW & ",""SIM""," & SH_WORK & "!$J$2:$J$" & lastW & ","">0"")"
    wsD.Range("G6").Formula = "=COUNTIF(" & SH_WORK & "!$S$2:$S$" & lastW & ",""SIM"")"
    wsD.Range("H6").Formula = "=COUNTIF(" & SH_WORK & "!$T$2:$T$" & lastW & ",""SIM"")"

    'Resumo por faixa de atraso
    wsD.Range("A9:C9").Value = Array("Faixa Aging", "Qtd", "%")
    wsD.Range("A10:A15").Value = Application.Transpose(Array("0_10 DIAS DELAY", "10_20 DIAS DELAY", "20_30 DIAS DELAY", "30_40 DIAS DELAY", "40_50 DIAS DELAY", "50_ABOVE 50 DAYS DELAY"))
    wsD.Range("B10:B15").Formula = "=COUNTIF(" & SH_WORK & "!$K$2:$K$" & lastW & ",A10)"
    wsD.Range("C10:C15").Formula = "=IFERROR(B10/$A$6,0)"

    'Resumo por tipo de evidencia
    wsD.Range("E9:G9").Value = Array("Tipo Evidencia", "Qtd", "% Evidencias")
    wsD.Range("E10:E16").Value = Application.Transpose(Array("Carta", "RDO", "Ata", "E-mail", "Relatorio", "Notificacao", "Outro"))
    wsD.Range("F10:F16").Formula = "=COUNTIF(" & SH_EVID & "!$B$2:$B$" & lastE & ",E10)"
    wsD.Range("G10:G16").Formula = "=IFERROR(F10/SUM($F$10:$F$16),0)"

    'Indicadores contratuais adicionais
    wsD.Range("A18:C18").Value = Array("Indicador Contratual", "Valor", "Comentario")
    wsD.Range("A19").Value = "Documentos atrasados com evidencia"
    wsD.Range("B19").Formula = "=COUNTIFS(" & SH_WORK & "!$J$2:$J$" & lastW & ","">0""," & SH_WORK & "!$S$2:$S$" & lastW & ",""SIM"")"
    wsD.Range("C19").Value = "Atrasos com registro contemporaneo associado."

    wsD.Range("A20").Value = "Documentos atrasados sem evidencia"
    wsD.Range("B20").Formula = "=COUNTIF(" & SH_WORK & "!$T$2:$T$" & lastW & ",""SIM"")"
    wsD.Range("C20").Value = "Fragilidade de contraprova e maior exposicao a pleitos."

    wsD.Range("A21").Value = "RDOs vinculados a pendencias documentais"
    wsD.Range("B21").Formula = "=COUNTIF(" & SH_EVID & "!$B$2:$B$" & lastE & ",""RDO"")"
    wsD.Range("C21").Value = "Registros diarios vinculados a documentos, restricoes ou interferencias."

    wsD.Range("A22").Value = "Cartas emitidas por impacto de prazo/custo/pleito"
    wsD.Range("B22").Formula = "=COUNTIFS(" & SH_EVID & "!$B$2:$B$" & lastE & ",""Carta""," & SH_EVID & "!$K$2:$K$" & lastE & ",""<>Sem impacto"")"
    wsD.Range("C22").Value = "Cartas com potencial relevancia contratual."

    wsD.Range("A23").Value = "Pendencias criticas sem resposta/evidencia"
    wsD.Range("B23").Formula = "=COUNTIFS(" & SH_WORK & "!$N$2:$N$" & lastW & ",""SIM""," & SH_WORK & "!$J$2:$J$" & lastW & ","">0""," & SH_WORK & "!$P$2:$P$" & lastW & ",0)"
    wsD.Range("C23").Value = "Priorizar cobranca formal e recuperacao de evidencias."

    wsD.Range("A24").Value = "Potencial exposicao a pleitos"
    wsD.Range("B24").Formula = "=IFERROR((B20+B23)/MAX(1,E6),0)"
    wsD.Range("C24").Value = "Percentual de atrasos sem suporte documental adequado."

    'Resumo de risco contratual
    wsD.Range("E18:F18").Value = Array("Risco Contratual", "Qtd")
    wsD.Range("E19:E23").Value = Application.Transpose(Array("ALTO - Atraso sem evidencia", "ALTO - Atraso com evidencia", "MEDIO - Sem contraprova", "MEDIO - Evidenciado", "BAIXO"))
    wsD.Range("F19:F23").Formula = "=COUNTIF(" & SH_WORK & "!$U$2:$U$" & lastW & ",E19)"

    wsD.Range("D6,C10:C15,G10:G16,B24").NumberFormat = "0.0%"
    wsD.Range("A6:C6,E6:H6,B19:B23,F19:F23").NumberFormat = "0"
End Sub

'====================================================================================
' FORMATACAO E SEMAFOROS
'====================================================================================
Private Sub AplicarFormatacaoBase()
    Dim wsLog As Worksheet, wsW As Worksheet, wsD As Worksheet, wsE As Worksheet
    Dim lastLog As Long, lastW As Long, lastE As Long

    Set wsLog = ThisWorkbook.Worksheets(SH_LOG)
    Set wsW = ThisWorkbook.Worksheets(SH_WORK)
    Set wsD = ThisWorkbook.Worksheets(SH_DASH)
    Set wsE = ThisWorkbook.Worksheets(SH_EVID)

    lastLog = Application.Max(UltimaLinha(wsLog, "A"), 300)
    lastW = Application.Max(UltimaLinha(wsW, "A"), 300)
    lastE = Application.Max(UltimaLinha(wsE, "A"), 300)

    FormatarCabecalho wsLog.Range("A1:O1")
    FormatarCabecalho wsE.Range("A1:M1")
    FormatarCabecalho wsW.Range("A1:U1")
    FormatarCabecalho wsD.Range("A5:H5")
    FormatarCabecalho wsD.Range("A9:C9")
    FormatarCabecalho wsD.Range("E9:G9")
    FormatarCabecalho wsD.Range("A18:C18")
    FormatarCabecalho wsD.Range("E18:F18")

    With wsD
        .Range("A1:H1").UnMerge
        .Range("A1:H1").Merge
        .Range("A1").Font.Bold = True
        .Range("A1").Font.Size = 16
        .Range("A1").Interior.Color = RGB(31, 78, 121)
        .Range("A1").Font.Color = RGB(255, 255, 255)
        .Range("A1").HorizontalAlignment = xlCenter
        .Range("A6:H6").Font.Bold = True
        .Range("A6:H6").Font.Size = 13
        .Range("A6:H6").Interior.Color = RGB(221, 235, 247)
        .Range("A18:C24").WrapText = True
        .Columns("A:H").AutoFit
        .Columns("C:C").ColumnWidth = 44
    End With

    'Semaforo atraso SLA
    With wsW.Range("J2:J" & lastW)
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="10"
        .FormatConditions(1).Interior.Color = RGB(198, 239, 206)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="11", Formula2:="30"
        .FormatConditions(2).Interior.Color = RGB(255, 235, 156)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="30"
        .FormatConditions(3).Interior.Color = RGB(255, 199, 206)
    End With

    'Documento critico
    With wsW.Range("N2:N" & lastW)
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=" & Chr$(34) & "SIM" & Chr$(34)
        .FormatConditions(1).Interior.Color = RGB(255, 199, 206)
        .FormatConditions(1).Font.Bold = True
    End With

    'Atrasado sem evidencia
    With wsW.Range("T2:T" & lastW)
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=" & Chr$(34) & "SIM" & Chr$(34)
        .FormatConditions(1).Interior.Color = RGB(255, 199, 206)
        .FormatConditions(1).Font.Bold = True
    End With

    'Risco contratual
    With wsW.Range("U2:U" & lastW)
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlTextString, String:="ALTO", TextOperator:=xlContains
        .FormatConditions(1).Interior.Color = RGB(255, 199, 206)
        .FormatConditions(1).Font.Bold = True
        .FormatConditions.Add Type:=xlTextString, String:="MEDIO", TextOperator:=xlContains
        .FormatConditions(2).Interior.Color = RGB(255, 235, 156)
        .FormatConditions.Add Type:=xlTextString, String:="BAIXO", TextOperator:=xlContains
        .FormatConditions(3).Interior.Color = RGB(198, 239, 206)
    End With

    wsLog.Columns("A:O").AutoFit
    wsE.Columns("A:M").AutoFit
    wsW.Columns("A:U").AutoFit
End Sub

Private Sub FormatarCabecalho(ByVal rng As Range)
    With rng
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(31, 78, 121)
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
    End With
End Sub

'====================================================================================
' ALERTA GERENCIAL / CONTRATUAL
'====================================================================================
Private Sub GerarAlertaExposicaoContratual()
    Dim wsW As Worksheet
    Dim lastW As Long
    Dim qtdCriticosAtrasados As Long
    Dim qtdAcima30 As Long
    Dim qtdSemEvidencia As Long
    Dim qtdAltoSemEvidencia As Long
    Dim msg As String

    Set wsW = ThisWorkbook.Worksheets(SH_WORK)
    lastW = UltimaLinha(wsW, "A")

    qtdCriticosAtrasados = Application.WorksheetFunction.CountIfs(wsW.Range("N2:N" & lastW), "SIM", wsW.Range("J2:J" & lastW), ">0")
    qtdAcima30 = Application.WorksheetFunction.CountIfs(wsW.Range("J2:J" & lastW), ">30")
    qtdSemEvidencia = Application.WorksheetFunction.CountIfs(wsW.Range("T2:T" & lastW), "SIM")
    qtdAltoSemEvidencia = Application.WorksheetFunction.CountIfs(wsW.Range("U2:U" & lastW), "ALTO - Atraso sem evidencia")

    If qtdCriticosAtrasados > 0 Or qtdAcima30 > 0 Or qtdSemEvidencia > 0 Then
        msg = "Atencao: ha exposicao contratual relevante no controle documental." & vbCrLf & vbCrLf & _
              "Documentos criticos atrasados: " & qtdCriticosAtrasados & vbCrLf & _
              "Documentos com atraso superior a 30 dias: " & qtdAcima30 & vbCrLf & _
              "Documentos atrasados sem evidencia associada: " & qtdSemEvidencia & vbCrLf & _
              "Risco ALTO por atraso sem evidencia: " & qtdAltoSemEvidencia & vbCrLf & vbCrLf & _
              "Recomendacao: emitir cobranca formal, vincular cartas/RDOs aos documentos impactados e preservar registros contemporaneos para protecao contra pleitos."
        MsgBox msg, vbExclamation, "Alerta de SLA / Evidencias / Pleitos"
    End If
End Sub

'====================================================================================
' MACROS AUXILIARES
'====================================================================================
Public Sub Exportar_Dashboard_PDF()
    On Error GoTo TratarErro

    Dim wsD As Worksheet
    Dim caminho As String
    Dim nomeArquivo As String

    Set wsD = ThisWorkbook.Worksheets(SH_DASH)
    nomeArquivo = "Dashboard_Aprovacao_Documental_Evidencias_" & Format(Date, "yyyymmdd") & ".pdf"
    caminho = ThisWorkbook.Path & "\" & nomeArquivo

    With wsD.PageSetup
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = "$A$1:$H$35"
    End With

    wsD.ExportAsFixedFormat Type:=xlTypePDF, Filename:=caminho, Quality:=xlQualityStandard, _
                            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

    MsgBox "PDF gerado em:" & vbCrLf & caminho, vbInformation, "Exportacao concluida"
    Exit Sub

TratarErro:
    MsgBox "Erro ao exportar PDF: " & Err.Number & " - " & Err.Description, vbCritical, "Erro VBA"
End Sub

Public Sub Limpar_Base_Log_Documentos()
    Dim wsLog As Worksheet
    Dim resposta As VbMsgBoxResult

    resposta = MsgBox("Deseja limpar os registros da aba Log_Documentos? Os cabecalhos serao preservados.", vbQuestion + vbYesNo, "Limpar base")
    If resposta <> vbYes Then Exit Sub

    Set wsLog = ThisWorkbook.Worksheets(SH_LOG)
    LimparFaixaSegura wsLog.Range("A2:O" & MAX_DEFAULT_ROW)

    MsgBox "Base de documentos limpa. Insira novos registros e execute Atualizar_Dashboard_Documental.", vbInformation, "Base limpa"
End Sub

Public Sub Limpar_Base_Log_Evidencias()
    Dim wsE As Worksheet
    Dim resposta As VbMsgBoxResult

    resposta = MsgBox("Deseja limpar os registros da aba Log_Evidencias? Os cabecalhos serao preservados.", vbQuestion + vbYesNo, "Limpar evidencias")
    If resposta <> vbYes Then Exit Sub

    PrepararLogEvidencias
    Set wsE = ObterOuCriarAba(SH_EVID)
    LimparFaixaSegura wsE.Range("A2:M" & MAX_DEFAULT_ROW)

    MsgBox "Base de evidencias limpa.", vbInformation, "Base limpa"
End Sub

Public Sub Criar_Exemplo_Log_Evidencias()
    Dim ws As Worksheet

    PrepararLogEvidencias
    Set ws = ObterOuCriarAba(SH_EVID)

    ws.Range("A2:M6").Value = Array( _
        Array("EVD-001", "Carta", Date - 20, "CT-001/2026", "Contratante", "Cobranca de documento pendente", "DOC-001", "Civil", "Engenharia", "Documento nao aprovado dentro do SLA contratual.", "Prazo", "Aberta", ""), _
        Array("EVD-002", "RDO", Date - 18, "RDO-145", "Fiscalizacao", "Restricao por pendencia de engenharia", "DOC-002", "Mecanica", "Fiscalizacao", "Equipe registrou impossibilidade de avancar por ausencia de liberacao tecnica.", "Prazo", "Pendente", ""), _
        Array("EVD-003", "Ata", Date - 15, "ATA-012", "Gerenciadora", "Reuniao de alinhamento documental", "DOC-003", "Eletrica", "Gerenciadora", "Pendencia classificada como critica para suprimentos.", "Custo", "Em tratamento", ""), _
        Array("EVD-004", "E-mail", Date - 10, "EMAIL-078", "Contratada", "Solicitacao de posicionamento", "DOC-004", "Civil", "Contratada", "Contratada sinalizou risco de impacto no prazo por falta de aprovacao.", "Pleito", "Aberta", ""), _
        Array("EVD-005", "Relatorio", Date - 5, "REL-003", "Contratante", "Resumo semanal de restricoes", "DOC-005", "Instrumentacao", "PMO", "Relatorio consolidou pendencias documentais em aberto.", "Prazo", "Tratada", "") _
    )

    MsgBox "Exemplos inseridos em Log_Evidencias. Ajuste os codigos DOC-001 etc. para os documentos reais.", vbInformation, "Exemplos criados"
End Sub
