Attribute VB_Name = "modModel"
Option Explicit
'=====================================================================
' modModel : 요구서 + 장표 → 사업항목 단위 통합 모델(gItems)
'=====================================================================
Public gItems As Object     'Key -> clsProject (요구서/장표 통합)
Public gByB As Object       '장표키 -> clsProject
Public gStaff As Object     '성명 -> 팀
Public gBAgg As Object      '장표키 -> clsProject(집계용)

Public Function ModelReady() As Boolean
    ModelReady = Not (gItems Is Nothing)
End Function

'모델 전체 구축
Public Sub BuildModel()
    Dim ovr As Object
    Set gItems = CreateObject("Scripting.Dictionary")
    Set gByB = CreateObject("Scripting.Dictionary")
    Set gBAgg = CreateObject("Scripting.Dictionary")
    Set gStaff = ReadStaffTeams()
    Set ovr = ReadCardOverrides()

    If SheetExists(SH_REQ) Then ParseRequest ovr
    If SheetExists(SH_B) Then
        AggregateB
    ElseIf SheetExists(SH_A) Then
        AggregateA
    End If
    MatchAll ovr
    AssignTeams
End Sub

'---------------------------------------------------------------- 요구서 파싱
Private Sub ParseRequest(ovr As Object)
    Dim ws As Worksheet, hr As Long, hm As Object, arr As Variant
    Dim cLv As Long, cE As Long, cI As Long, cG As Long, cH As Long, cM As Long, cP As Long, cAC As Long, cQ As Long
    Dim r As Long, lr As Long, e As String, lv As String, m As String, nm As String, p As Long
    Dim cur As clsProject, k As String, seq As Long

    Set ws = BW().Worksheets(SH_REQ)
    hr = FindHeaderRow(ws, "예산요구액", 20)
    If hr = 0 Then Err.Raise vbObjectError + 1, , "요구서 헤더행(예산요구액)을 찾지 못했습니다."
    Set hm = HeaderMap(ws, hr)
    cLv = HCol(hm, "레벨"): cE = HCol(hm, "세세부사업"): cI = HCol(hm, "세부사업")
    cG = HCol(hm, "정책사업", False): cH = HCol(hm, "단위사업", False)
    cM = HCol(hm, "산출기초"): cP = HCol(hm, "예산요구액"): cAC = HCol(hm, "담당자")
    cQ = HCol(hm, "전년도예산액", False)

    lr = Application.WorksheetFunction.Max(lastRow(ws, cE), lastRow(ws, cM), lastRow(ws, cP))
    If lr <= hr Then Exit Sub
    arr = ws.Range(ws.cells(hr + 1, 1), ws.cells(lr, Application.WorksheetFunction.Max(cAC, cP, cM, cI))).Value2

    seq = 0
    For r = 1 To UBound(arr, 1)
        lv = CleanText(arr(r, cLv)): e = CleanText(arr(r, cE)): m = CleanText(arr(r, cM))
        If lv = "" And e <> "" And IsLevel1Name(e) Then
            '사업항목(1레벨) 시작
            p = InStr(e, ".")
            nm = Trim(Mid(e, p + 1))
            k = MakeKey(CleanText(arr(r, cI)), nm)
            If gItems.Exists(k) Then
                Set cur = gItems(k)
                cur.요구액 = cur.요구액 + NzD(arr(r, cP)) * 1000
                If cQ > 0 Then cur.전년도액 = cur.전년도액 + NzD(arr(r, cQ)) * 1000
            Else
                Set cur = New clsProject
                seq = seq + 1
                cur.key = k
                cur.순번 = seq
                cur.세부사업 = CleanText(arr(r, cI))
                cur.사업항목 = nm
                If cG > 0 Then cur.정책사업 = CleanText(arr(r, cG))
                If cH > 0 Then cur.단위사업 = CleanText(arr(r, cH))
                cur.요구액 = NzD(arr(r, cP)) * 1000
                If cQ > 0 Then cur.전년도액 = NzD(arr(r, cQ)) * 1000
                cur.매칭 = "요구서"
                ApplyOverride cur, ovr
                gItems.Add k, cur
            End If
            cur.담당자자동 = JoinUnique(cur.담당자자동, CleanText(arr(r, cAC)))
        ElseIf Not cur Is Nothing Then
            If m = "소계" Or lv = "합계" Then
                Set cur = Nothing
            Else
                cur.담당자자동 = JoinUnique(cur.담당자자동, CleanText(arr(r, cAC)))
            End If
        End If
    Next r
End Sub

'"1. 사업명" 형태인지
Private Function IsLevel1Name(ByVal e As String) As Boolean
    Dim p As Long
    p = InStr(e, ".")
    If p < 2 Or p > 4 Then IsLevel1Name = False: Exit Function
    IsLevel1Name = IsNumeric(Left(e, p - 1))
End Function

'---------------------------------------------------------------- 장표 102-B 집계 (세목이 있는 말단행 합산)
Private Sub AggregateB()
    Set gBAgg = AggregateFromSheet(BW().Worksheets(SH_B), "")
End Sub

'102-B 형식 시트에서 사업항목(1레벨) 집계. deptFilter 가 있으면 예산소관부서명에 포함된 행만.
Public Function AggregateFromSheet(ws As Worksheet, ByVal deptFilter As String) As Object
    Dim hr As Long, hm As Object, arr As Variant, r As Long, lr As Long, lc As Long, d As Object
    Dim cGb As Long, cUnit As Long, cPol As Long, cDan As Long, cSeb As Long, cN1 As Long, cCode As Long, cDept As Long
    Dim cHy As Long, cWon As Long, cJi As Long, cGyo As Long, cGeup As Long
    Dim k As String, a As clsProject, seq As Long

    Set d = CreateObject("Scripting.Dictionary")
    Set AggregateFromSheet = d
    hr = FindHeaderRow(ws, "회계연도", 10)
    If hr = 0 Then Exit Function
    Set hm = HeaderMap(ws, hr)
    If Not hm.Exists("사업항목명1") Then Exit Function
    cGb = HCol(hm, "집행예산구분"): cUnit = HCol(hm, "단위업무"): cPol = HCol(hm, "정책사업", False)
    cDan = HCol(hm, "단위사업", False): cSeb = HCol(hm, "세부사업"): cN1 = HCol(hm, "사업항목명1")
    cCode = HCol(hm, "세출세목코드"): cHy = HCol(hm, "예산현액"): cWon = HCol(hm, "원인행위금액")
    cJi = HCol(hm, "지출금액"): cGyo = HCol(hm, "일상경비교부액", False): cGeup = HCol(hm, "일상경비지급액", False)
    cDept = HCol(hm, "예산소관부서명", False)

    lr = lastRow(ws, 1): lc = ws.cells(hr, ws.Columns.Count).End(xlToLeft).Column
    If lr <= hr Then Exit Function
    arr = ws.Range(ws.cells(hr + 1, 1), ws.cells(lr, lc)).Value2

    For r = 1 To UBound(arr, 1)
        If CleanText(arr(r, cCode)) <> "" Then
            If deptFilter = "" Or cDept = 0 Or InStr(1, CleanText(arr(r, cDept)), deptFilter, vbTextCompare) > 0 Then
                k = MakeKey(CleanText(arr(r, cSeb)), CleanText(arr(r, cN1)))
                If d.Exists(k) Then
                    Set a = d(k)
                Else
                    Set a = New clsProject
                    seq = seq + 1
                    a.key = k: a.장표키 = k: a.순번 = seq
                    a.세부사업 = CleanText(arr(r, cSeb))
                    a.장표명 = CleanText(arr(r, cN1))
                    If a.장표명 = "" Then a.장표명 = "(사업항목 없음)"
                    a.사업항목 = a.장표명
                    If cPol > 0 Then a.정책사업 = CleanText(arr(r, cPol))
                    If cDan > 0 Then a.단위사업 = CleanText(arr(r, cDan))
                    d.Add k, a
                End If
                AddAmounts a, arr, r, cGb, cHy, cWon, cJi, cGyo, cGeup
                a.카드자동 = JoinUnique(a.카드자동, CleanText(arr(r, cUnit)))
            End If
        End If
    Next r
End Function

'102-A만 있는 경우 집계 (사업항목1레벨 × 세목 행)
Private Sub AggregateA()
    Dim ws As Worksheet, hr As Long, hm As Object, arr As Variant, r As Long, lr As Long, lc As Long
    Dim cGb As Long, cUnit As Long, cPol As Long, cDan As Long, cSeb As Long, cN1 As Long
    Dim cHy As Long, cWon As Long, cJi As Long, cGyo As Long, cGeup As Long
    Dim k As String, a As clsProject

    Set ws = BW().Worksheets(SH_A)
    hr = FindHeaderRow(ws, "회계연도", 10)
    If hr = 0 Then Exit Sub
    Set hm = HeaderMap(ws, hr)
    cGb = HCol(hm, "집행예산구분"): cUnit = HCol(hm, "단위업무"): cPol = HCol(hm, "정책사업", False)
    cDan = HCol(hm, "단위사업", False): cSeb = HCol(hm, "세부사업"): cN1 = HCol(hm, "사업항목1레벨")
    cHy = HCol(hm, "예산현액"): cWon = HCol(hm, "원인행위금액"): cJi = HCol(hm, "지출금액")
    cGyo = HCol(hm, "일상경비교부액", False): cGeup = HCol(hm, "일상경비지급액", False)

    lr = lastRow(ws, 1): lc = ws.cells(hr, ws.Columns.Count).End(xlToLeft).Column
    If lr <= hr Then Exit Sub
    arr = ws.Range(ws.cells(hr + 1, 1), ws.cells(lr, lc)).Value2
    For r = 1 To UBound(arr, 1)
        k = MakeKey(CleanText(arr(r, cSeb)), CleanText(arr(r, cN1)))
        If gBAgg.Exists(k) Then
            Set a = gBAgg(k)
        Else
            Set a = New clsProject
            a.key = k: a.장표키 = k
            a.세부사업 = CleanText(arr(r, cSeb))
            a.장표명 = CleanText(arr(r, cN1))
            If cPol > 0 Then a.정책사업 = CleanText(arr(r, cPol))
            If cDan > 0 Then a.단위사업 = CleanText(arr(r, cDan))
            gBAgg.Add k, a
        End If
        AddAmounts a, arr, r, cGb, cHy, cWon, cJi, cGyo, cGeup
        a.카드자동 = JoinUnique(a.카드자동, CleanText(arr(r, cUnit)))
    Next r
End Sub

Private Sub AddAmounts(a As clsProject, arr As Variant, ByVal r As Long, ByVal cGb As Long, ByVal cHy As Long, _
                       ByVal cWon As Long, ByVal cJi As Long, ByVal cGyo As Long, ByVal cGeup As Long)
    Dim hy As Double, gb As String
    hy = NzD(arr(r, cHy))
    a.예산현액 = a.예산현액 + hy
    a.원인행위 = a.원인행위 + NzD(arr(r, cWon))
    a.집행액 = a.집행액 + ExecAmount(arr, r, cJi, cGyo, cGeup)
    gb = CleanText(arr(r, cGb))
    If InStr(gb, "예산") > 0 Then a.본예산현액 = a.본예산현액 + hy Else a.이월현액 = a.이월현액 + hy
End Sub

'실집행액 = 지출금액 - 일상경비교부액 + 일상경비지급액
Public Function ExecAmount(arr As Variant, ByVal r As Long, ByVal cJi As Long, ByVal cGyo As Long, ByVal cGeup As Long) As Double
    Dim v As Double
    v = NzD(arr(r, cJi))
    If cGyo > 0 And cGeup > 0 Then v = v - NzD(arr(r, cGyo)) + NzD(arr(r, cGeup))
    ExecAmount = v
End Function

'---------------------------------------------------------------- 매칭
Private Sub MatchAll(ovr As Object)
    Dim k As Variant, it As clsProject, a As clsProject, bk As String, used As Object, req As Object
    Dim rq As clsProject
    Set used = CreateObject("Scripting.Dictionary")
    Set req = gItems                      '요구서 항목 (담당자 정보용)
    Set gItems = CreateObject("Scripting.Dictionary")

    If gBAgg.Count = 0 Then
        '장표가 없으면 요구서 항목으로 보드 구성 (요구액 = 예산현액)
        For Each k In req.keys
            Set it = req(k)
            it.예산현액 = it.요구액: it.본예산현액 = it.요구액: it.매칭 = "요구서"
            gItems.Add k, it
        Next k
        Exit Sub
    End If

    '1) 요구서 항목의 장표 키 결정 (직접지정 > 완전일치 > 유사)
    For Each k In req.keys
        Set rq = req(k)
        bk = ""
        If Trim(rq.장표지정) <> "" Then bk = FindBKey(rq.세부사업, rq.장표지정, True)
        If bk = "" Then
            If gBAgg.Exists(rq.key) Then bk = rq.key Else bk = FindBKey(rq.세부사업, rq.사업항목, False)
        End If
        If bk <> "" Then If Not used.Exists(bk) Then used.Add bk, rq
    Next k

    '2) 장표 항목이 기준 (장표 순서). 요구서가 있으면 담당자·요구액·전년도 붙임
    For Each k In gBAgg.keys
        Set a = gBAgg(k)
        Set it = New clsProject
        it.key = a.key: it.순번 = a.순번
        it.세부사업 = a.세부사업: it.사업항목 = a.장표명: it.장표명 = a.장표명: it.장표키 = k
        it.정책사업 = a.정책사업: it.단위사업 = a.단위사업
        it.예산현액 = a.예산현액: it.원인행위 = a.원인행위: it.집행액 = a.집행액
        it.본예산현액 = a.본예산현액: it.이월현액 = a.이월현액
        it.카드자동 = a.카드자동
        If used.Exists(k) Then
            Set rq = used(k)
            it.담당자자동 = rq.담당자자동: it.요구액 = rq.요구액: it.전년도액 = rq.전년도액
            it.매칭 = "일치"
        Else
            it.매칭 = IIf(req.Count = 0, "장표", "요구서없음")
        End If
        ApplyOverride it, ovr
        gItems.Add it.key, it
        gByB.Add k, it
    Next k
End Sub

'장표 키 탐색: exact=True 는 정규화 완전일치(세부사업 무시 허용), False 는 포함관계(같은 세부사업 내)
Private Function FindBKey(ByVal 세부사업 As String, ByVal nm As String, ByVal exact As Boolean) As String
    Dim k As Variant, nk As String, sk As String, parts As Variant, best As String, bestLen As Long
    nk = NormKey(nm): sk = NormKey(세부사업)
    If nk = "" Then Exit Function
    If gBAgg.Exists(sk & "|" & nk) Then FindBKey = sk & "|" & nk: Exit Function
    For Each k In gBAgg.keys
        parts = Split(k, "|")
        If exact Then
            If parts(1) = nk Then FindBKey = k: Exit Function
        Else
            If parts(0) = sk Then
                If InStr(parts(1), nk) > 0 Or InStr(nk, parts(1)) > 0 Then
                    If Len(parts(1)) > bestLen Then best = k: bestLen = Len(parts(1))
                End If
            End If
        End If
    Next k
    FindBKey = best
End Function

Private Sub ApplyOverride(it As clsProject, ovr As Object)
    Dim v As Variant
    If ovr Is Nothing Then Exit Sub
    If ovr.Exists(it.key) Then
        v = ovr(it.key)
        it.담당자수정 = v(0): it.카드수정 = v(1): it.장표지정 = v(2)
    End If
End Sub

'담당자 → 팀 배정 (복수 담당자면 팀도 중복 없이 연결)
Private Sub AssignTeams()
    Dim k As Variant, it As clsProject, names As Variant, i As Long, T As String, nm As String
    For Each k In gItems.keys
        Set it = gItems(k)
        T = ""
        names = Split(it.담당자, ",")
        For i = LBound(names) To UBound(names)
            nm = CleanText(names(i))
            If nm <> "" Then If gStaff.Exists(nm) Then T = JoinUnique(T, gStaff(nm))
        Next i
        it.팀 = T
    Next k
End Sub

'---------------------------------------------------------------- 정렬된 키 목록 (팀 → 담당자 → 순번)
Public Function SortedKeys() As Collection
    Dim n As Long, keys() As String, sk() As String, i As Long, j As Long, tmpS As String, tmpK As String
    Dim k As Variant, it As clsProject, col As New Collection
    n = gItems.Count
    If n = 0 Then Set SortedKeys = col: Exit Function
    ReDim keys(1 To n): ReDim sk(1 To n)
    i = 0
    For Each k In gItems.keys
        i = i + 1
        Set it = gItems(k)
        keys(i) = k
        sk(i) = Format(it.순번, "00000")      '장표 순서
    Next k
    '삽입 정렬 (항목 수 수백 건 수준)
    For i = 2 To n
        tmpS = sk(i): tmpK = keys(i): j = i - 1
        Do While j >= 1
            If StrComp(sk(j), tmpS, vbTextCompare) <= 0 Then Exit Do
            sk(j + 1) = sk(j): keys(j + 1) = keys(j): j = j - 1
        Loop
        sk(j + 1) = tmpS: keys(j + 1) = tmpK
    Next i
    For i = 1 To n: col.Add keys(i): Next i
    Set SortedKeys = col
End Function

'담당자 목록(중복 제거, 정렬)
Public Function StaffNames() As Collection
    Dim d As Object, k As Variant, it As clsProject, names As Variant, i As Long, nm As String
    Dim arr() As String, n As Long, j As Long, T As String, col As New Collection
    Set d = CreateObject("Scripting.Dictionary")
    If Not gStaff Is Nothing Then
        For Each k In gStaff.keys: d(k) = 1: Next k
    End If
    If Not gItems Is Nothing Then
        For Each k In gItems.keys
            Set it = gItems(k)
            names = Split(it.담당자, ",")
            For i = LBound(names) To UBound(names)
                nm = CleanText(names(i))
                If nm <> "" Then d(nm) = 1
            Next i
        Next k
    End If
    n = d.Count
    If n = 0 Then Set StaffNames = col: Exit Function
    ReDim arr(1 To n): i = 0
    For Each k In d.keys: i = i + 1: arr(i) = k: Next k
    For i = 2 To n
        T = arr(i): j = i - 1
        Do While j >= 1
            If StrComp(arr(j), T, vbTextCompare) <= 0 Then Exit Do
            arr(j + 1) = arr(j): j = j - 1
        Loop
        arr(j + 1) = T
    Next i
    For i = 1 To n: col.Add arr(i): Next i
    Set StaffNames = col
End Function