Attribute VB_Name = "COA_EmailAlerts"
Option Explicit

' === CONFIG ===
Const SHEET_NAME As String = "COA"
Const HDR_PROJECT_ID As String = "รหัสโครงการ"
Const HDR_APPROVED As String = "วันที่รับรอง"
Const HDR_EXPIRES As String = "วันที่หมดอายุ "
Const HDR_SENT As String = "ส่งเมลเตือนแล้ว"

' เลือกเงื่อนไขการแจ้งเตือน (ตั้ง TRUE/ FALSE ตามต้องการ)
Const ALERT_IF_SINCE_APPROVAL_GE_6M As Boolean = True    ' ถ้าอนุมัติแล้วเกิน/ครบ 6 เดือน
Const ALERT_IF_WITHIN_6M_TO_EXPIRY As Boolean = False     ' ถ้าเหลือ <= 6 เดือนจะหมดอายุ

' อีเมลผู้รับค่าเริ่มต้น (ถ้าไม่มี column ผู้รับในชีตนี้ให้ใช้ค่านี้)
Const DEFAULT_MAIL_TO As String = ""      ' ใส่อีเมลถาวร เช่น "qa@example.com"

Sub Send_COA_Alerts_R1()
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(SHEET_NAME)
    On Error GoTo 0
    If ws Is Nothing Then
        MsgBox "ไม่พบแผ่นงานชื่อ " & SHEET_NAME, vbCritical: Exit Sub
    End If

    Dim hdrRow As Long: hdrRow = 1
    Dim lastRow As Long: lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    If lastRow < hdrRow + 1 Then
        MsgBox "ไม่พบข้อมูลในแผ่นงาน " & SHEET_NAME, vbExclamation: Exit Sub
    End If

    ' หา index ของหัวคอลัมน์สำคัญแบบยืดหยุ่น
    Dim colProject As Long: colProject = FindColumn(ws, HDR_PROJECT_ID, hdrRow)
    Dim colApproved As Long: colApproved = FindColumn(ws, HDR_APPROVED, hdrRow)
    Dim colExpires As Long: colExpires = FindColumn(ws, HDR_EXPIRES, hdrRow)
    Dim colSent As Long: colSent = FindColumn(ws, HDR_SENT, hdrRow)

    If colApproved = 0 And colExpires = 0 Then
        MsgBox "ไม่พบคอลัมน์ """ & HDR_APPROVED & """ หรือ """ & HDR_EXPIRES & """", vbCritical: Exit Sub
    End If
    If colSent = 0 Then
        ' ถ้าไม่มีคอลัมน์ส่งเมล ให้สร้างเพิ่มท้ายตาราง
        colSent = ws.Cells(hdrRow, ws.Columns.Count).End(xlToLeft).Column + 1
        ws.Cells(hdrRow, colSent).Value = HDR_SENT
    End If

    ' เตรียม Outlook
    Dim olApp As Object, olMail As Object
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then Set olApp = CreateObject("Outlook.Application")
    On Error GoTo 0
    If olApp Is Nothing Then
        MsgBox "ไม่พบ Outlook ในเครื่องนี้", vbCritical: Exit Sub
    End If

    Dim i As Long, projId As String
    Dim dApproved As Variant, dExpire As Variant
    Dim monthsSince As Long, daysToExpiry As Long
    Dim doAlert As Boolean, alreadySent As String
    Dim subjectText As String, bodyText As String
    Dim recipients As String

    Dim alertCount As Long: alertCount = 0

    For i = hdrRow + 1 To lastRow
        doAlert = False
        projId = Trim(CStr(ws.Cells(i, colProject).Value))

        ' อนุญาตให้แผงลิสต์บางแถวว่างได้
        dApproved = IIf(colApproved > 0, ws.Cells(i, colApproved).Value, Empty)
        dExpire = IIf(colExpires > 0, ws.Cells(i, colExpires).Value, Empty)
        alreadySent = UCase$(Trim(CStr(ws.Cells(i, colSent).Value)))

        ' ข้ามถ้าเคยส่งแล้ว
        If alreadySent = "1" Or alreadySent = "YES" Or alreadySent = "Y" Then GoTo NextRow

        ' เงื่อนไข A: ผ่านไป >= 6 เดือน นับจากวันที่รับรอง
        If ALERT_IF_SINCE_APPROVAL_GE_6M Then
            If IsDate(dApproved) Then
                monthsSince = DateDiff("m", CDate(dApproved), Date)
                If monthsSince >= 6 Then doAlert = True
            End If
        End If

        ' เงื่อนไข B: จะหมดอายุภายใน <= 6 เดือน (≈ 183 วัน)
        If Not doAlert And ALERT_IF_WITHIN_6M_TO_EXPIRY Then
            If IsDate(dExpire) Then
                daysToExpiry = DateDiff("d", Date, CDate(dExpire))
                If daysToExpiry <= 183 Then doAlert = True
            End If
        End If

        If doAlert Then
            ' ผู้รับ: ใช้ DEFAULT หรือจะเพิ่มคอลัมน์ email ในชีตแล้วแก้ตรงนี้
            recipients = DEFAULT_MAIL_TO
            If Len(recipients) = 0 Then
                ' ถ้าไม่ได้ตั้ง DEFAULT และไม่มีคอลัมน์ email ให้หยุด
                MsgBox "ไม่ได้กำหนดอีเมลผู้รับ (DEFAULT_MAIL_TO ว่าง) ที่แถว " & i & " โปรดตั้งค่าแล้วรันใหม่", vbExclamation
                Exit Sub
            End If

            ' สร้างหัวเรื่อง/เนื้อหาเมลแบบย่อ
            subjectText = "[COA แจ้งเตือน] " & IIf(Len(projId) > 0, projId & " - ", "") & "ครบ/เกิน 6 เดือน"
            bodyText = "โครงการ: " & projId & vbCrLf & _
                       "วันที่รับรอง: " & IIf(IsDate(dApproved), Format(CDate(dApproved), "dd mmm yyyy"), "-") & vbCrLf & _
                       "วันที่หมดอายุ: " & IIf(IsDate(dExpire), Format(CDate(dExpire), "dd mmm yyyy"), "-") & vbCrLf & _
                       "เงื่อนไขแจ้งเตือน: " & IIf(ALERT_IF_SINCE_APPROVAL_GE_6M, "ผ่านไป ≥ 6 เดือนจากวันที่รับรอง", "เหลือ ≤ 6 เดือนก่อนหมดอายุ")

            Set olMail = olApp.CreateItem(0)
            With olMail
                .To = recipients
                .Subject = subjectText
                .Body = bodyText
                .Send
            End With
            alertCount = alertCount + 1

            ' ติ๊กสถานะส่งแล้ว
            ws.Cells(i, colSent).Value = 1
        End If
NextRow:
    Next i

    MsgBox "ส่งอีเมลแจ้งเตือนแล้ว " & alertCount & " รายการ", vbInformation
End Sub

Private Function FindColumn(ws As Worksheet, headerText As String, headerRow As Long) As Long
    Dim lastCol As Long: lastCol = ws.Cells(headerRow, ws.Columns.Count).End(xlToLeft).Column
    Dim c As Long
    For c = 1 To lastCol
        If Trim(CStr(ws.Cells(headerRow, c).Value)) = headerText Then
            FindColumn = c
            Exit Function
        End If
    Next c
    FindColumn = 0
End Function