Attribute VB_Name = "TeachSpeak"
'
' TeachSpeak - ConvertTableToComments Macro
' Version: 2.0.0
' Created by: TeachSpeak (https://teachspeak.foldingthoughts.com)
'
' PURPOSE:
'   Converts a feedback table (generated by TeachSpeak) into
'   Word margin comments anchored to specific phrases in the
'   student's document. After inserting comments, the macro
'   deletes the feedback table and appends overall feedback
'   at the end of the document.
'
'   On first run, automatically installs an "Insert Comments"
'   button in Word's Add-Ins tab for one-click access.
'
' TABLE FORMAT EXPECTED:
'   Row 1 (Header): "Anchor Phrase" | "Feedback Comment"
'   Row 2+:          <phrase>        | <feedback text>
'
' OVERALL FEEDBACK:
'   If the document contains a paragraph starting with
'   "Overall Feedback" followed by numbered points, they
'   are preserved after the table is removed.
'
' USAGE:
'   1. Open the student's original document in Word
'   2. Paste the TeachSpeak feedback table at the END of the document
'   3. Click "Insert Comments" in the Add-Ins tab
'      (or press Alt+F8, select "ConvertTableToComments", click Run)
'   4. Comments appear in the margins, table is removed
'
' COMPATIBILITY:
'   - Microsoft Word 2016+ (Windows)
'   - Microsoft Word 2016+ (macOS)
'   - Word for Microsoft 365
'
' =====================================================

Option Explicit

' =====================================================
' BUTTON INSTALLATION
' =====================================================

Sub InstallTeachSpeak()
    '
    ' InstallTeachSpeak
    ' Manually install the "Insert Comments" button.
    ' Run this via Alt+F8 if the button is missing.
    '
    EnsureButtonInstalled
    MsgBox "TeachSpeak 'Insert Comments' button installed!" & vbCrLf & vbCrLf & _
           "Look for it in the Add-Ins tab on the ribbon.", _
           vbInformation, "TeachSpeak"
End Sub

Sub UninstallTeachSpeak()
    '
    ' UninstallTeachSpeak
    ' Remove the TeachSpeak button from Word.
    '
    On Error Resume Next
    Application.CommandBars("TeachSpeak").Delete
    On Error GoTo 0
    MsgBox "TeachSpeak button removed.", vbInformation, "TeachSpeak"
End Sub

Private Sub EnsureButtonInstalled()
    '
    ' EnsureButtonInstalled
    ' Creates a "TeachSpeak" toolbar with an "Insert Comments" button.
    ' In Ribbon-era Word (2007+), this appears in the Add-Ins tab.
    ' Runs silently -- only creates if not already present.
    '
    Dim bar As CommandBar
    Dim btn As CommandBarButton

    ' Check if toolbar already exists
    On Error Resume Next
    Set bar = Application.CommandBars("TeachSpeak")
    On Error GoTo 0

    If Not bar Is Nothing Then
        ' Already installed -- just make sure it's visible
        bar.Visible = True
        Exit Sub
    End If

    ' Create the toolbar (persistent across sessions)
    Set bar = Application.CommandBars.Add( _
        Name:="TeachSpeak", _
        Position:=msoBarTop, _
        Temporary:=False)
    bar.Visible = True

    ' Add the button
    Set btn = bar.Controls.Add(Type:=msoControlButton)
    With btn
        .Caption = "Insert Comments"
        .Style = msoButtonIconAndCaption
        .FaceId = 282   ' Flowchart/org-chart icon (connected boxes)
        .OnAction = "ConvertTableToComments"
        .Tag = "TeachSpeakInsertComments"
        .TooltipText = "Convert TeachSpeak feedback table to margin comments"
    End With
End Sub

' =====================================================
' MAIN MACRO
' =====================================================

Sub ConvertTableToComments()
    '
    ' ConvertTableToComments
    ' Converts TeachSpeak feedback table to margin comments
    '

    ' Auto-install button on first run (silent, no popup)
    EnsureButtonInstalled

    Dim tbl As Table
    Dim tblIndex As Long
    Dim anchorPhrase As String
    Dim feedbackText As String
    Dim rng As Range
    Dim searchRange As Range
    Dim i As Long
    Dim commentsInserted As Long
    Dim commentsNotFound As Long
    Dim totalRows As Long
    Dim tblRange As Range

    ' ---- Pre-flight checks ----

    ' Check if document has any tables
    If ActiveDocument.Tables.Count = 0 Then
        MsgBox "No table found in this document." & vbCrLf & vbCrLf & _
               "Please paste the TeachSpeak feedback table into " & _
               "this document first, then run the macro again.", _
               vbExclamation, "TeachSpeak"
        Exit Sub
    End If

    ' Find the feedback table (look for one with header "Anchor Phrase")
    tblIndex = 0
    Dim t As Long
    For t = 1 To ActiveDocument.Tables.Count
        Dim headerText As String
        headerText = CleanCellText(ActiveDocument.Tables(t).Cell(1, 1).Range.Text)
        If LCase(headerText) = "anchor phrase" Then
            tblIndex = t
            Exit For
        End If
    Next t

    ' If no table with matching header found, use the last table
    If tblIndex = 0 Then
        tblIndex = ActiveDocument.Tables.Count
    End If

    Set tbl = ActiveDocument.Tables(tblIndex)

    ' Validate table structure: must have exactly 2 columns
    If tbl.Columns.Count <> 2 Then
        MsgBox "The feedback table should have exactly 2 columns:" & vbCrLf & _
               "  Column 1: Anchor Phrase" & vbCrLf & _
               "  Column 2: Feedback Comment" & vbCrLf & vbCrLf & _
               "The selected table has " & tbl.Columns.Count & " columns.", _
               vbExclamation, "TeachSpeak"
        Exit Sub
    End If

    ' Check table has data rows (more than just header)
    totalRows = tbl.Rows.Count
    If totalRows < 2 Then
        MsgBox "The feedback table has no data rows." & vbCrLf & _
               "It should have a header row plus one or more " & _
               "feedback rows.", _
               vbExclamation, "TeachSpeak"
        Exit Sub
    End If

    ' Store the table range before we start modifying document
    Set tblRange = tbl.Range.Duplicate

    ' ---- Process feedback rows ----

    ' Turn off screen updating for performance
    Application.ScreenUpdating = False

    commentsInserted = 0
    commentsNotFound = 0

    ' Loop through data rows (skip row 1 = header)
    For i = 2 To totalRows
        ' Extract and clean cell text
        anchorPhrase = CleanCellText(tbl.Cell(i, 1).Range.Text)
        feedbackText = CleanCellText(tbl.Cell(i, 2).Range.Text)

        ' Skip empty rows
        If Len(Trim(anchorPhrase)) = 0 Or Len(Trim(feedbackText)) = 0 Then
            GoTo NextRow
        End If

        ' Search for anchor phrase in document
        ' We search from the beginning of the document up to the table
        ' to avoid matching text inside the table itself
        Set searchRange = ActiveDocument.Range( _
            Start:=ActiveDocument.Range.Start, _
            End:=tblRange.Start)

        With searchRange.Find
            .Text = anchorPhrase
            .Forward = True
            .Wrap = wdFindStop
            .MatchCase = False
            .MatchWholeWord = False
            .MatchWildcards = False
            .MatchSoundsLike = False
            .MatchAllWordForms = False

            If .Execute Then
                ' Found the anchor phrase - insert comment
                searchRange.Comments.Add _
                    Range:=searchRange, _
                    Text:=feedbackText
                commentsInserted = commentsInserted + 1
            Else
                ' Anchor phrase not found in document body
                ' Try a fuzzy search: trim extra whitespace, normalize
                Dim fuzzyPhrase As String
                fuzzyPhrase = NormalizeWhitespace(anchorPhrase)

                Set searchRange = ActiveDocument.Range( _
                    Start:=ActiveDocument.Range.Start, _
                    End:=tblRange.Start)

                .Text = fuzzyPhrase

                If .Execute Then
                    searchRange.Comments.Add _
                        Range:=searchRange, _
                        Text:=feedbackText
                    commentsInserted = commentsInserted + 1
                Else
                    ' Still not found - add comment at document start with note
                    Dim notFoundRange As Range
                    Set notFoundRange = ActiveDocument.Range( _
                        Start:=0, End:=1)
                    notFoundRange.Comments.Add _
                        Range:=notFoundRange, _
                        Text:="[Anchor not found: """ & Left(anchorPhrase, 50) & _
                              """] " & feedbackText
                    commentsNotFound = commentsNotFound + 1
                End If
            End If
        End With

NextRow:
    Next i

    ' ---- Delete the feedback table ----
    tbl.Delete

    ' ---- Restore screen updating ----
    Application.ScreenUpdating = True

    ' ---- Show results ----
    Dim resultMsg As String
    resultMsg = "TeachSpeak feedback processed!" & vbCrLf & vbCrLf

    If commentsInserted > 0 Then
        resultMsg = resultMsg & commentsInserted & " comment(s) inserted successfully."
    End If

    If commentsNotFound > 0 Then
        resultMsg = resultMsg & vbCrLf & vbCrLf & _
                    commentsNotFound & " anchor phrase(s) could not be found " & _
                    "in the document. These comments were added at the " & _
                    "beginning of the document with a note." & vbCrLf & _
                    "You may need to move them manually."
    End If

    If commentsInserted = 0 And commentsNotFound = 0 Then
        resultMsg = "No feedback comments were found in the table."
    End If

    MsgBox resultMsg, vbInformation, "TeachSpeak"

End Sub

' =====================================================
' HELPER FUNCTIONS
' =====================================================

Private Function CleanCellText(ByVal cellText As String) As String
    '
    ' CleanCellText
    ' Removes Word table cell markers and trims whitespace
    '
    ' Word table cells contain hidden characters:
    '   Chr(13) = carriage return / paragraph mark
    '   Chr(7)  = cell end marker (bell character)
    '   Chr(11) = vertical tab / soft return
    '

    Dim cleaned As String
    cleaned = cellText

    ' Remove cell end marker (Chr(7))
    cleaned = Replace(cleaned, Chr(7), "")

    ' Remove paragraph marks (Chr(13))
    cleaned = Replace(cleaned, Chr(13), "")

    ' Remove vertical tabs / soft returns (Chr(11))
    cleaned = Replace(cleaned, Chr(11), " ")

    ' Normalize smart quotes to straight quotes for matching
    ' Left double quote (Unicode 8220)
    cleaned = Replace(cleaned, ChrW(8220), """")
    ' Right double quote (Unicode 8221)
    cleaned = Replace(cleaned, ChrW(8221), """")
    ' Left single quote (Unicode 8216)
    cleaned = Replace(cleaned, ChrW(8216), "'")
    ' Right single quote (Unicode 8217)
    cleaned = Replace(cleaned, ChrW(8217), "'")

    ' Trim leading/trailing whitespace
    cleaned = Trim(cleaned)

    CleanCellText = cleaned
End Function

Private Function NormalizeWhitespace(ByVal text As String) As String
    '
    ' NormalizeWhitespace
    ' Collapses multiple spaces, tabs, and line breaks into single spaces
    ' Useful for fuzzy matching when original document has different spacing
    '

    Dim result As String
    Dim prevChar As String
    Dim currChar As String
    Dim i As Long

    result = ""
    prevChar = ""

    For i = 1 To Len(text)
        currChar = Mid(text, i, 1)

        ' Replace tabs and line breaks with spaces
        If currChar = vbTab Or currChar = vbCr Or currChar = vbLf Then
            currChar = " "
        End If

        ' Skip duplicate spaces
        If currChar = " " And prevChar = " " Then
            GoTo NextChar
        End If

        result = result & currChar
        prevChar = currChar
NextChar:
    Next i

    NormalizeWhitespace = Trim(result)
End Function