using ClosedXML.Excel;
using System.Diagnostics;

namespace AiQ_GUI
{
    internal class Excel
    {
        public static void WriteTo(string FilePath)
        {
            if (File.Exists(FilePath))
            {
                XLWorkbook workbook = new(FilePath); // Open existing file
                IXLWorksheet worksheet = workbook.Worksheets.First();
                worksheet.Cell("A1").Value = "Hello World!";
                worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)";
                workbook.SaveAs(FilePath);
            }
            else
            {
                MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
            }
        }

        public static string ReadFrom(string FilePath)
        {
            if (File.Exists(FilePath))
            {
                XLWorkbook workbook = new(FilePath); // Open existing file
                IXLWorksheet worksheet = workbook.Worksheets.First();
                return worksheet.Cell("A1").GetString();
            }
            else
            {
                MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
            }
            return null;
        }

        public static int GetNextBlankRow(string FilePath)
        {
            if (!File.Exists(FilePath))
            {
                MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
                return -1;
            }

            using XLWorkbook workbook = new XLWorkbook(FilePath);
            IXLWorksheet worksheet = workbook.Worksheets.First();

            // Start from row 1 and check downwards
            int row = 1;
            while (!worksheet.Cell(row, 1).IsEmpty())
            {
                row++;
            }
            return row;
        }

        public static string UpdateSpreadSheetPreTest(string FilePath, Versions Vers, string CamDesc, string ModelOnTest)
        {
            try
            {
                if (!File.Exists(FilePath))
                {
                    MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
                    return "Spreadsheet not found";
                }

                using XLWorkbook workbook = new XLWorkbook(FilePath);
                IXLWorksheet worksheet = workbook.Worksheets.First();

                // Start from row 1 and check downwards to find next empty row in column B
                int row = 1;
                while (!worksheet.Cell(row, 2).IsEmpty()) // Column B = Serial numbers
                {
                    row++;
                }

                // Safety check to avoid invalid index
                if (row <= 1)
                {
                    return "Last serial number not found";
                }

                // Generate new serial number from previous
                string lastSerialNumber = worksheet.Cell(row - 1, 2).GetString(); // Column B
                if (string.IsNullOrWhiteSpace(lastSerialNumber) || !lastSerialNumber.StartsWith("K"))
                {
                    return "Invalid last serial number format";
                }

                int NewSerialNumberInt = Convert.ToInt32(lastSerialNumber.Substring(1)) + 1;
                string newSerialNumber = "K" + NewSerialNumberInt;

                // Write values to the corresponding columns
                worksheet.Cell(row, 1).Value = ModelOnTest; // Column A
                worksheet.Cell(row, 2).Value = newSerialNumber; // Column B
                worksheet.Cell(row, 3).Value = CamDesc; // Column C
                worksheet.Cell(row, 4).Value = "Pre Test: " + DateTime.Now.ToString("dd/MM/yyyy"); // Column D
                worksheet.Cell(row, 5).Value = Vers.version + " - " + Vers.revision + Environment.NewLine +
                                               Vers.buildtime + Environment.NewLine + Vers.proquint; // Column E
                worksheet.Cell(row, 8).Value = Vers.MAC; // Column H
                worksheet.Cell(row, 14).Value = $"GUI Version: {GUIUpdate.GUIVerShort}"; // Column N
                worksheet.Cell(row, 19).Value = "TRUE"; // Column S

                workbook.SaveAs(FilePath);

                return newSerialNumber;
            }
            catch (Exception ex)
            {
                MainForm.Instance.AddToActionsList("Error updating spreadsheet: " + ex.Message);
                return $"ERROR: {ex.Message}";
            }
        }

        public static string UpdateSpreadSheetRePreTest(string filePath, Versions Vers)
        {
            try
            {
                if (!File.Exists(filePath))
                {
                    MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
                    return "Spreadsheet not found";
                }

                using XLWorkbook workbook = new XLWorkbook(filePath);
                IXLWorksheet worksheet = workbook.Worksheets.First();

                // Find the row with the matching serial number in column B
                int row = 1;
                while (!worksheet.Cell(row, 2).IsEmpty())
                {
                    string cellSerial = worksheet.Cell(row, 2).GetString();
                    if (cellSerial.Contains(Vers.Serial))
                    {
                        // Update columns D-E
                        worksheet.Cell(row, 4).Value = "Pre Test: " + DateTime.Now.ToString("dd/MM/yyyy");
                        worksheet.Cell(row, 5).Value = Vers.version + " - " + Vers.revision + Environment.NewLine +
                                                       Vers.buildtime + Environment.NewLine + Vers.proquint;
                        // Update MAC to column H
                        worksheet.Cell(row, 8).Value = Vers.MAC;

                        // Update GUI Version to column N
                        worksheet.Cell(row, 14).Value = $"GUI Version: {GUIUpdate.GUIVerShort}";

                        // Write TRUE to WIP checkbox in column S
                        worksheet.Cell(row, 19).Value = "TRUE";

                        workbook.SaveAs(filePath);
                        return "OK";
                    }
                    row++;
                }

                return "Serial number not found";
            }
            catch (Exception ex)
            {
                MainForm.Instance.AddToActionsList("Error updating spreadsheet: " + ex.Message);
                return $"ERROR: {ex.Message}";
            }
        }

        public static string UpdateSpreadSheetFinalTest(string FilePath, Diags DiagsAPI, SSHData sshData, int RMANum)
        {
            try
            {
                if (!File.Exists(FilePath))
                {
                    MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
                    return "Spreadsheet not found";
                }

                using XLWorkbook workbook = new XLWorkbook(FilePath);
                IXLWorksheet worksheet = workbook.Worksheets.First();

                // Find the row with the matching serial number in column B
                int row = 1;
                while (!worksheet.Cell(row, 2).IsEmpty())
                {
                    string serial = worksheet.Cell(row, 2).GetString();
                    if (serial == DiagsAPI.serialNumber)
                    {
                        // Update column D with test date
                        string existingDate = worksheet.Cell(row, 4).GetString(); // Column D
                        string newDate = (RMANum != 0 ? "RMA Test: " : "Final Test: ") + DateTime.Now.ToString("dd/MM/yyyy");
                        worksheet.Cell(row, 4).Value = existingDate + Environment.NewLine + newDate;

                        // Update columns F-G
                        worksheet.Cell(row, 6).Value = DiagsAPI.licenses.raptorKeyID; // Column F
                        worksheet.Cell(row, 7).Value = sshData.packages;              // Column G

                        // Update columns N-S
                        worksheet.Cell(row, 14).Value = $"GUI Version: {GUIUpdate.GUIVerShort}"; // Column N
                        worksheet.Cell(row, 15).Value = DiagsAPI.licenses.saf1;     // Column O
                        worksheet.Cell(row, 16).Value = DiagsAPI.licenses.audit;    // Column P
                        worksheet.Cell(row, 17).Value = DiagsAPI.licenses.stream;   // Column Q
                        worksheet.Cell(row, 18).Value = sshData.tailscale;          // Column R
                        worksheet.Cell(row, 19).Value = "FALSE";                    // Column S (WIP checkbox)

                        workbook.SaveAs(FilePath);
                        return "OK";
                    }
                    row++;
                }

                return "Serial number not found";
            }
            catch (Exception ex)
            {
                MainForm.Instance.AddToActionsList("Error updating spreadsheet: " + ex.Message);
                return "Failed to update spreadsheet data, please check manually: " + ex.Message;
            }
        }

        public static string UpdateSpreadSheetVaxtor(string FilePath, VaxtorLic VaxtorLicResp, string serial, string model)
        {
            try
            {
                if (!File.Exists(FilePath))
                {
                    MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
                    return "Spreadsheet not found";
                }

                using XLWorkbook workbook = new XLWorkbook(FilePath);
                IXLWorksheet worksheet = workbook.Worksheets.First();

                // Find next free row by checking column C
                int row = 2;
                while (!worksheet.Cell(row, 3).IsEmpty()) // Column C
                {
                    row++;
                }

                // Write model, serial, date, and protectionKeyId to columns C–F
                worksheet.Cell(row, 3).Value = model;                               // Column C
                worksheet.Cell(row, 4).Value = serial;                              // Column D
                worksheet.Cell(row, 5).Value = DateTime.Now.ToString("dd/MM/yyyy"); // Column E
                worksheet.Cell(row, 6).Value = VaxtorLicResp.protectionKeyId;       // Column F

                // Write "PROD" to column H
                worksheet.Cell(row, 8).Value = "PROD"; // Column H

                workbook.SaveAs(FilePath);
                return string.Empty;
            }
            catch (Exception ex)
            {
                MainForm.Instance.AddToActionsList("Error updating Vaxtor spreadsheet: " + ex.Message);
                return "Failed to update spreadsheet data, please check manually: " + ex.Message;
            }
        }

        public static int CheckRMANum(string filePath, string serial, string model)
        {
            try
            {
                if (!File.Exists(filePath))
                {
                    MainForm.Instance.AddToActionsList("Could not find RMA Control spreadsheet :(");
                    return 0;
                }

                using XLWorkbook workbook = new XLWorkbook(filePath);
                IXLWorksheet worksheet = workbook.Worksheets.First();

                int row = 2; // Start from row 2
                while (!worksheet.Cell(row, 8).IsEmpty() || !worksheet.Cell(row, 9).IsEmpty()) // Columns H (8) and I (9)
                {
                    try
                    {
                        string sheetSerial = worksheet.Cell(row, 8).GetString();
                        string sheetModel = worksheet.Cell(row, 9).GetString();

                        if (sheetSerial.Contains(serial) && sheetModel.Contains(model))
                        {
                            string rmaStr = worksheet.Cell(row, 1).GetString(); // Column A
                            if (int.TryParse(rmaStr, out int rmaNumber))
                            {
                                return rmaNumber;
                            }
                        }
                    }
                    catch { /* Safe to ignore bad row */ }

                    row++;
                }
            }
            catch (Exception ex)
            {
                MainForm.Instance.AddToActionsList("Error reading RMA Control spreadsheet: " + ex.Message);
            }

            return 0; // Default if not found
        }

        public static int CheckSerialNumRow(string filePath, string serial)
        {
            try
            {
                if (!File.Exists(filePath))
                {
                    MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
                    return 0;
                }

                using XLWorkbook workbook = new XLWorkbook(filePath);
                IXLWorksheet worksheet = workbook.Worksheets.First();

                int row = 1;
                while (!worksheet.Cell(row, 2).IsEmpty()) // Column B = 2
                {
                    try
                    {
                        string cellSerial = worksheet.Cell(row, 2).GetString();
                        if (cellSerial.Contains(serial))
                        {
                            return row;
                        }
                    }
                    catch { /* Ignore malformed rows */ }

                    row++;
                }
            }
            catch (Exception ex)
            {
                MainForm.Instance.AddToActionsList("Error checking serial number row: " + ex.Message);
            }

            return 0;
        }

        public static int CheckNextFree(string filePath)
        {
            try
            {
                if (!File.Exists(filePath))
                {
                    MainForm.Instance.AddToActionsList("Could not find spreadsheet :(");
                    return -1;
                }

                using XLWorkbook workbook = new XLWorkbook(filePath);
                IXLWorksheet worksheet = workbook.Worksheets.First();

                int row = 2; // Start from C2
                while (!worksheet.Cell(row, 3).IsEmpty()) // Column C = 3
                {
                    row++;
                }

                return row;
            }
            catch (Exception ex)
            {
                MainForm.Instance.AddToActionsList("Error checking next free row: " + ex.Message);
                return -1;
            }
        }

        //***** TESTING *****\\
        public static void TestAllExcelFunctions()
        {
            string filePath = @"C:\Users\BradleyRelyea\OneDrive - MAV Systems Ltd\MAV R&D - General\ModelsInfo.xlsx";
            // 1. Ensure spreadsheet exists and has valid starting data
            if (!File.Exists(filePath))
            {
                XLWorkbook workbook = new XLWorkbook();
                IXLWorksheet ws = workbook.Worksheets.Add("Sheet1");

                // Pre-fill first row with dummy data for serial
                ws.Cell("A1").Value = "InitialModel";
                ws.Cell("B1").Value = "K1000"; // Starting serial
                ws.Cell("C2").Value = "Existing"; // Simulate used row in Vaxtor (for CheckNextFree)
                workbook.SaveAs(filePath);
            }

            // 2. Run Pre-Test update
            Versions fakeVersion = new Versions
            {
                version = "1.6.4",
                revision = "bf16134",
                buildtime = "2025-07-21 10:00",
                proquint = "bexog-ludeg-zokud-huqer",
                MAC = "00:1A:2B:3C:4D:5E"
            };

            string preTestResult = Excel.UpdateSpreadSheetPreTest(filePath, fakeVersion, "Fake Cam", "TestModel");
            Debug.WriteLine("Pre-Test Result: " + preTestResult);

            // 3. Run Final Test update
            Diags fakeDiags = new Diags
            {
                serialNumber = preTestResult, // Newly generated serial
                licenses = new Licenses
                {
                    raptorKeyID = "9999999999",
                    saf1 = true,
                    audit = true,
                    stream = true
                }
            };

            SSHData fakeSSH = new SSHData
            {
                packages = "\r\nlibvaxtorocr10 - 8.4.20-1\r\nvaxtorocrdatacpu3 - 8.4.20-1",
                tailscale = true
            };

            string finalTestResult = Excel.UpdateSpreadSheetFinalTest(filePath, fakeDiags, fakeSSH, 0);
            Debug.WriteLine("Final-Test Result: " + finalTestResult);

            //    // 4. Run Vaxtor update
            //    var fakeVaxtorLic = new VaxtorLic
            //    {
            //        protectionKeyId = "VKID-555-ALPHA"
            //    };

            //    string vaxtorResult = Excel.UpdateSpreadSheetVaxtor(filePath, fakeVaxtorLic, "VX-999", "VXModel");
            //    Debug.WriteLine("Vaxtor Result: " + vaxtorResult);

            //    // 5. Check Serial Row
            //    int serialRow = Excel.CheckSerialNumRow(filePath, preTestResult);
            //    Debug.WriteLine("Serial row found at: " + serialRow);

            //    // 6. Check Next Free Vaxtor Row
            //    int nextFree = Excel.CheckNextFree(filePath);
            //    Debug.WriteLine("Next free row in Vaxtor sheet (Column C): " + nextFree);
            //}

        }
    }
}