Untitled

    [HttpGet("send-email-to-owner-and-reviewer")]
    public IActionResult TestSendEmailByType(string type)
    {
        // Pengaturan SMTP server
        string smtpAddress = "mail.tmt.co.id";
        int portNumber = 25;
        bool enableSSL = true; // (tidak dipakai langsung; kita pakai SecureSocketOptions)
        string emailFrom = "[email protected]";

        try
        {
            // Tentukan rentang tanggal untuk bulan sebelumnya
            DateTime currentDate = DateTime.Now;
            DateTime startDate = new DateTime(currentDate.Year, currentDate.Month, 1).AddMonths(-1); // Awal bulan sebelumnya
            DateTime endDate = startDate.AddMonths(1).AddSeconds(-1); // Akhir bulan sebelumnya

            // Tanggal batas konfirmasi (contoh: tgl 13 bulan berjalan)
            DateTime emailSubmissionDeadline = new DateTime(currentDate.Year, currentDate.Month, 13);

            // Ambil base URL PSCT
            string sqlUrlQuery = @"
            SELECT Url_Value
            FROM mt_url
            WHERE Url_Type = 'header'
              AND Url_Name = 'URL PSCT'";
            var rsUrl = ExecuteRows(sqlUrlQuery);
            if (!rsUrl.Any())
                return BadRequest("No URL found for the selected environment.");

            string baseUrl = rsUrl[0]["Url_Value"].ToString();
            string urlPSCT = rsUrl[0]["Url_Value"].ToString();

            // Tentukan query form & tujuan list page
            string tableName;
            string sqlFormQuery;
            if (type == "Upload Form")
            {
                tableName = "tr_claim_upload_form";
                baseUrl += "trclaimuploadformlist";
                sqlFormQuery = $@"
                WITH internal_agg AS (
                    SELECT 
                        Program_Code,
                        SUM(Trans_Unit_Price_USD_Extd) AS Total_Trans_Unit_Price
                    FROM tr_internal_form
                    WHERE Program_Code IS NOT NULL
                      AND Repair_Invoice_Date BETWEEN '{startDate:yyyy-MM-dd}' AND '{endDate:yyyy-MM-dd}'
                      AND Status = 'Eligible'
                    GROUP BY Program_Code
                ),
                claim_agg AS (
                    SELECT 
                        Program_Code,
                        SUM(CAT_Support_Amount) AS Total_CAT_Support_Amount
                    FROM tr_claim_upload_form
                    WHERE Repair_Invoice_Date BETWEEN '{startDate:yyyy-MM-dd}' AND '{endDate:yyyy-MM-dd}'
                    GROUP BY Program_Code
                )
                SELECT
                    i.Program_Code,
                    ISNULL(c.Total_CAT_Support_Amount, 0) AS Total_CAT_Support_Amount,
                    ISNULL(i.Total_Trans_Unit_Price,   0) AS Total_Trans_Unit_Price
                FROM internal_agg AS i
                LEFT JOIN claim_agg    AS c
                       ON i.Program_Code = c.Program_Code
                ORDER BY i.Program_Code;";
            }
            else if (type == "Manual Form")
            {
                tableName = "tr_claim_manual_form";
                baseUrl += "trclaimmanualformlist";
                // FIX: hilangkan koma sisa setelah SUM(...)
                sqlFormQuery = $@"
                SELECT 
                    Program_Code,
                    SUM(CAT_Support_Amount) AS Total_CAT_Support_Amount
                FROM {tableName}
                WHERE Repair_Invoice_Date BETWEEN '{startDate:yyyy-MM-dd}' AND '{endDate:yyyy-MM-dd}'
                GROUP BY Program_Code";
            }
            else
            {
                return BadRequest("Invalid type. Only 'Upload Form' and 'Manual Form' are supported.");
            }

            var rsForm = ExecuteRows(sqlFormQuery);
            if (!rsForm.Any())
                return BadRequest("No program codes found for the given date range.");

            // Kumpulkan nilai per Program_Code
            var programData = new Dictionary<string, (string TotalValue, string TotalTransactionValue)>();
            foreach (var row in rsForm)
            {
                var programCode = row["Program_Code"]?.ToString();
                if (string.IsNullOrWhiteSpace(programCode)) continue;

                var totalValueDecimal = decimal.TryParse(row["Total_CAT_Support_Amount"]?.ToString(), out var parsedTotalValue)
                    ? parsedTotalValue : 0m;
                var totalValue = totalValueDecimal.ToString("#,##0.00");

                var totalTransactionValue = type == "Upload Form" &&
                    decimal.TryParse(row["Total_Trans_Unit_Price"]?.ToString(), out var parsedTransactionValue)
                    ? parsedTransactionValue.ToString("#,##0.00")
                    : "N/A";

                if (!programData.ContainsKey(programCode))
                    programData[programCode] = (totalValue, totalTransactionValue);
            }

            // Ambil master program untuk mapping owner & reviewer
            string sqlMtProgram = $@"
            SELECT 
                p.Program_Code,
                p.Program_Name,
                p.Program_Desc,
                (
                    SELECT DISTINCT STUFF((
                        SELECT DISTINCT ',' + Reviewer
                        FROM mt_program AS subP
                        WHERE subP.Program_Code = p.Program_Code AND subP.Status = 1
                        FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
                ) AS Reviewer,
                (
                    SELECT DISTINCT STUFF((
                        SELECT DISTINCT ',' + Program_Owner
                        FROM mt_program AS subP
                        WHERE subP.Program_Code = p.Program_Code AND subP.Status = 1
                        FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
                ) AS Program_Owner
            FROM mt_program p
            WHERE p.Program_Code IN ('{string.Join("','", programData.Keys)}')
              AND p.Status = 1
              AND p.Program_Owner IS NOT NULL
            GROUP BY p.Program_Code, p.Program_Name, p.Program_Desc;";
            var rsMtProgram = ExecuteRows(sqlMtProgram);

            // Buat email mapping: email -> list program
            var emailProgramMapping = new Dictionary<string, List<(string ProgramCode, string ProgramName, string SalesRevenue, string CATSupport)>>();

            foreach (var row in rsMtProgram)
            {
                var programCode = row["Program_Code"]?.ToString();
                var programName = row["Program_Name"]?.ToString();
                var salesRevenue = type == "Upload Form" ? programData[programCode].TotalTransactionValue : "N/A";
                var catSupport = programData[programCode].TotalValue;

                var reviewers = (row["Reviewer"]?.ToString() ?? "")
                    .Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                var owners = (row["Program_Owner"]?.ToString() ?? "")
                    .Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                var userXUPJs = reviewers.Concat(owners)
                                         .Where(x => !string.IsNullOrWhiteSpace(x))
                                         .Distinct();

                string sqlUserQuery = $@"
                SELECT DISTINCT UserEmail
                FROM mt_user
                WHERE UserXUPJ IN ('{string.Join("','", userXUPJs)}')";
                var rsUserEmails = ExecuteRows(sqlUserQuery);

                foreach (var userRow in rsUserEmails)
                {
                    var email = userRow["UserEmail"]?.ToString();
                    if (string.IsNullOrWhiteSpace(email)) continue;

                    if (!emailProgramMapping.ContainsKey(email))
                        emailProgramMapping[email] = new List<(string, string, string, string)>();

                    emailProgramMapping[email].Add((programCode, programName, salesRevenue, catSupport));
                }
            }

            // Tambah global email (menerima semua program)
            string sqlEmailGlobal = @"
            SELECT Url_Value 
            FROM mt_url 
            WHERE Url_Type = 'email' 
              AND Url_Name = 'email for all program'";
            var rsEmailGlobal = ExecuteRows(sqlEmailGlobal);
            if (rsEmailGlobal.Any())
            {
                var globalEmailsRaw = rsEmailGlobal[0]["Url_Value"]?.ToString();
                if (!string.IsNullOrWhiteSpace(globalEmailsRaw))
                {
                    var globalEmails = globalEmailsRaw.Split(',')
                                                      .Select(e => e.Trim())
                                                      .Where(e => !string.IsNullOrWhiteSpace(e))
                                                      .Distinct();
                    var allProgramDetails = new List<(string, string, string, string)>();
                    foreach (var row in rsMtProgram)
                    {
                        var programCode = row["Program_Code"].ToString();
                        var programName = row["Program_Name"].ToString();
                        if (!programData.ContainsKey(programCode)) continue;

                        var salesRevenue = type == "Upload Form" ? programData[programCode].TotalTransactionValue : "N/A";
                        var catSupport = programData[programCode].TotalValue;
                        allProgramDetails.Add((programCode, programName, salesRevenue, catSupport));
                    }

                    foreach (var email in globalEmails)
                    {
                        if (!emailProgramMapping.ContainsKey(email))
                            emailProgramMapping[email] = new List<(string, string, string, string)>();
                        emailProgramMapping[email].AddRange(allProgramDetails);
                    }
                }
            }

            // Deduplicate per email
            foreach (var email in emailProgramMapping.Keys.ToList())
            {
                var uniqueSet = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
                var filteredList = new List<(string ProgramCode, string ProgramName, string SalesRevenue, string CATSupport)>();
                foreach (var prog in emailProgramMapping[email])
                {
                    string uniqueKey = $"{prog.ProgramCode}|{prog.ProgramName}|{prog.SalesRevenue}";
                    if (uniqueSet.Add(uniqueKey))
                        filteredList.Add(prog);
                }
                emailProgramMapping[email] = filteredList;
            }

            // === NEW: ambil template dari mt_url ===
            // Silakan sesuaikan Type/Name/Status sesuai kesepakatan di DB
            string sqlTemplate = @"
            SELECT TOP 1 Url_Value
            FROM mt_url
            WHERE Url_Type = 'template'
              AND Url_Name = 'PSCT_EMAIL_SUMMARY'
              AND ISNULL(Url_Status, 1) = 1
            ORDER BY id DESC";
            var rsTemplate = ExecuteRows(sqlTemplate);

            // Template default (fallback) kalau tidak ketemu di DB
            string fallbackTemplate = @"
<p>{{GREETING}}</p>
<p>Berikut summary dari masing-masing program yang eligible untuk di-claim ke Caterpillar sesuai master program yang sudah diinput pada PS Claim Tools, periode invoice date <strong>{{PERIOD}}</strong>.</p>
<p>Mohon konfirmasinya paling lambat <strong>{{DEADLINE}}</strong>, sebelum dilanjutkan ke proses submission claim ke PSCS.</p>
<table border='1' style='border-collapse: collapse; width: 100%;'>
  <thead>
    <tr>
      <th>No.</th>
      <th>Program ID</th>
      <th>Program Name</th>
      <th>Sales Volume</th>
      <th>Estimate CAT Support</th>
    </tr>
  </thead>
  <tbody>
    {{ROWS}}
  </tbody>
</table>
<p>Informasi dan transaksi lebih detail silakan mengecek pada aplikasi PS Claim Tools.</p>
<p><a href='{{LINK}}'>link PS Claim Tools</a></p>";

            string templateHtml = rsTemplate.Any()
                ? (rsTemplate[0]["Url_Value"]?.ToString() ?? fallbackTemplate)
                : fallbackTemplate;

            using (var client = new SmtpClient())
            {
                client.Connect(smtpAddress, portNumber, MailKit.Security.SecureSocketOptions.StartTls);

                foreach (var mapEntry in emailProgramMapping)
                {
                    string email = mapEntry.Key;
                    var programs = mapEntry.Value;

                    // Buat rows <tr>...</tr>
                    var sbRows = new System.Text.StringBuilder();
                    int counter = 1;
                    foreach (var program in programs)
                    {
                        sbRows.AppendLine($@"
<tr>
  <td style='text-align:center;'>{counter}</td>
  <td style='text-align:left;'>{program.ProgramCode}</td>
  <td style='text-align:left;'>{program.ProgramName}</td>
  <td style='text-align:right;'>{(program.SalesRevenue == "N/A" ? "-" : "$" + program.SalesRevenue)}</td>
  <td style='text-align:right;'>${program.CATSupport}</td>
</tr>");
                        counter++;
                    }

                    // Render template
                    string emailBody = templateHtml
                        .Replace("{{GREETING}}", "Dear User,")
                        .Replace("{{PERIOD}}", startDate.ToString("MMMM yyyy"))
                        .Replace("{{DEADLINE}}", emailSubmissionDeadline.ToString("dd MMMM yyyy"))
                        .Replace("{{ROWS}}", sbRows.ToString())
                        .Replace("{{LINK}}", urlPSCT);

                    // Kirim email
                    var message = new MimeMessage();
                    message.From.Add(new MailboxAddress("No Reply", emailFrom));
                    message.To.Add(new MailboxAddress("", email));
                    message.Subject = $"Program Summary for {type} - {startDate:MMMM yyyy}";
                    var bodyBuilder = new BodyBuilder { HtmlBody = emailBody };
                    message.Body = bodyBuilder.ToMessageBody();
                    client.Send(message);
                }

                client.Disconnect(true);
            }

            return Ok(new { Message = "Emails successfully sent to Owner and Reviewer using template." });
        }
        catch (Exception ex)
        {
            return StatusCode(500, new { Message = "Terjadi kesalahan: " + ex.Message });
        }
    }