using System;
using System.IO;
using System.Collections.Generic;
using System.Globalization;
using Spire.Xls;
using System.Reflection;
using System.Threading.Tasks;
using Renci.SshNet;

namespace bdf
{
    public class Taxes              // Stores by-province taxes for Invoices
    {
        public double PST { get; set; }
        public double GST { get; set; }
        public double HST { get; set; }

        public Taxes(double value1, double value2, double value3)
        {
            this.PST = value1;
            this.GST = value2;
            this.HST = value3;
        }

        public Taxes(Taxes tax, double value1, double value2, double value3)
        {
            this.PST = tax.PST + value1;
            this.GST = tax.GST + value2;
            this.HST = tax.HST + value3;
        }

        public double Totaled()
        {
            return this.PST + this.GST + this.HST;
        }

    }

    public class InvoiceCell       // Invoice Cells from Invoice Template
    {
        public String Account { get; set; } = "I2";         // "Account"+ (also I40)
        public String Date { get; set; } = "I3";            // I3 (also I41) 
        public String Invoice { get; set; } = "I4";         //I4 "Invoice" (also I42)
        public String Total_Charges { get; set; } = "I14";  //I14 (also I44)

        public String Account2 { get; set; } = "I40";       // "Account"+ 
        public String Date2 { get; set; } = "I41";          // I3  
        public String Invoice2 { get; set; } = "I42";       //I4 "Invoice" 
        public String Total_Charges2 { get; set; } = "I44"; //I14 

        public String Addr_Dept { get; set; } = "C7";
        public String Addr_Client { get; set; } = "C8";     //Maps to D7
        public String Addr_Street { get; set; } = "C9";     //Maps to D8
        public String Addr_City { get; set; } = "C10";      //Maps to D9
        public String Addr_PC { get; set; } = "C11";        //Maps to D10

        public String SrvAddr_1 { get; set; } = "D7";
        public String SrvAddr_2 { get; set; } = "D8";     //Maps to D7
        public String SrvAddr_3 { get; set; } = "D9";     //Maps to D8
        public String SrvAddr_4 { get; set; } = "D10";      //Maps to D9

        public String BackBill_Title { get; set; } = "C26";        //for SIP
        public String BackBill_Charges { get; set; } = "H26";//for SIP

        public String _Hidden_SO { get; set; } = "";        //for SIP
        public String _Hidden_Acct { get; set; } = "";        //for SIP

        public String _Hidden_Addr1 { get; set; } = "";        //for SIP
        public String _Hidden_Addr2 { get; set; } = "";        //for SIP
        public String _Hidden_Addr3 { get; set; } = "";        //for SIP
        public String _Hidden_Addr4 { get; set; } = "";        //for SIP

        public Dictionary<string, Taxes> TX { get; set; } = new Dictionary<string, Taxes> {};   // format of "ON" pst,gst,hst

        public String CCI { get; set; } = "C12";  // C12 "Canada Contract Identifier"

        public String PaperFee { get; set; } = "H17";
        public String LateCharge { get; set; } = "H18";
        public String Service_Charges { get; set; } = "I19";
        public String UsageCharges { get; set; } = "H20";
        public String TotalUsageCharges { get; set; } = "I21";
        public String DataMonthlyCharges { get; set; } = "H23";
        public String DataNon_RecurringCharges { get; set; } = "H24";
        public String DataTotalOtherCharges { get; set; } = "H25";
        public String Data_Services { get; set; } = "I27";
        public String TaxON_PST { get; set; } = "F29";
        public String TaxON_GST { get; set; } = "G29";
        public String TaxON_HST { get; set; } = "H29";
        public String TaxPQ_PST { get; set; } = "F30";
        public String TaxPQ_GST { get; set; } = "G30";
        public String TaxPQ_HST { get; set; } = "H30";
        public String Taxes { get; set; } = "I31";
    }

    public class PDF
    {

        public static Workbook LoadEmbeddedWorkbook(string resourceName)
        {
            // Get the assembly where the resource is stored
            Assembly asm = Assembly.GetExecutingAssembly();

            // Fully-qualified resource name (namespace + filename)
            Stream stream = asm.GetManifestResourceStream(resourceName);

            if (stream == null)
                throw new Exception("Embedded resource not found: " + resourceName);

            Workbook wb = new Workbook();
            wb.LoadFromStream(stream);   // FreeSpire.XLS supports this

            stream.Close();
            return wb;
        }

        // this version creates  PDF invoice with 1 line per type of provincial taxes, first line is 29, line 30 in template must be handled and line 31 has the totals
        public static MemoryStream customInvoiceAsMS_taxes(ref Dictionary<string, string> CellData, ref InvoiceCell ic)
        {
            MemoryStream ms = new MemoryStream();
            Workbook workbook = new Workbook();

            string fontDirectory = AppDomain.CurrentDomain.BaseDirectory;   // for non Windows systems use the .exe folder
            string[] fontDirectories = { fontDirectory };
            /*
            foreach( var kvp in ic.TX)
            {
                Console.WriteLine("TX: {4} - {3} PST={0:N2} GST={1:N2} HST={2:N2}  TOTAL={5:N2}", kvp.Value.PST, kvp.Value.GST, kvp.Value.HST, kvp.Key, CellData[ic.Invoice],kvp.Value.Totaled());
            }
            */
            workbook = LoadEmbeddedWorkbook("BDF.Embedded.template.xlsx");

            int dpi = 600;// image conversion resolution
            workbook.ConverterSetting.XDpi = (dpi);
            workbook.ConverterSetting.YDpi = (dpi);
            workbook.ConverterSetting.JPEGQuality = 99;

            workbook.CustomFontFileDirectory = fontDirectories;

            foreach (var cell in CellData)
            {
                if (cell.Key != "")
                    workbook.Worksheets[0].Range[cell.Key].Text = cell.Value;

            } // end foreach

            // Now we modify the TAX lines starting on row 29.
            // Col C - DataPROV ColF/G/H - PST/GST/HST and Col I for total
            Worksheet sheet = workbook.Worksheets[0];
            double TotalTax = 0;
            int add_row = 29; // these are Excel 1-based
            int tot_row = 30;

            // Add in correct number of rows for pending data
            for (int i = 1; i<ic.TX.Count; i++)
            {
                Logger.Log(3,"add={0} tot={1} count={2} i={3}", add_row, tot_row, ic.TX.Count, i);
                InsertAndCopyRow(sheet, add_row, add_row + 1);
                tot_row++;
                sheet.DeleteRow(tot_row+1);
            }

            foreach (var prov in ic.TX)
            {
                sheet[add_row, 3].Value = "Data-" + prov.Key;
                sheet[add_row, 6].NumberValue = prov.Value.PST;
                sheet[add_row, 7].NumberValue = prov.Value.GST;
                sheet[add_row, 8].NumberValue = prov.Value.HST;
                TotalTax += prov.Value.Totaled();
                Logger.Log(3, "row={0} amt={1} tot={2}", add_row, TotalTax, tot_row);
                add_row++;
            }
            sheet[tot_row, 9].NumberValue = TotalTax;

            workbook.ConverterSetting.SheetFitToPage = true;

            ExcelDocumentProperties edp = new ExcelDocumentProperties { };
            edp.Title = "BDF Invoice for " + CellData[ic.Account];
            edp.Subject = CellData[ic.Invoice];
            edp.Author = bdf.fullUser;
            edp.Comments += "\n" + DateTime.Now.ToString();
            edp.ApplyTo(workbook);

            //Load ARIAL.TTF font to working dir if needed
            try
            {
                workbook.SaveToStream(ms, FileFormat.PDF);
            }
            catch (Exception e)
            {
                bdf.ExtractResourceToFile("BDF.Embedded.arial.ttf", (AppDomain.CurrentDomain.BaseDirectory + "./arial.ttf"));  // arial.ttf is needed to render the PDF
                workbook.SaveToStream(ms, FileFormat.PDF);
                Logger.Log(1, "Error Rendering PDF Invoice", e.Message);
            }
            return ms;
        }

        public static void InsertAndCopyRow(Worksheet sheet,int sourceRowIndex,int targetRowIndex)
        {
            sheet.InsertRow(targetRowIndex);

            int lastColumn = sheet.LastColumn;

            for (int col = 1; col <= lastColumn; col++)
            {
                CellRange sourceCell = sheet.Range[sourceRowIndex, col];
                CellRange targetCell = sheet.Range[targetRowIndex, col];

                // Copy value
                targetCell.Value2 = sourceCell.Value2;

                // Copy formula if present
                if (!string.IsNullOrEmpty(sourceCell.Formula))
                    targetCell.Formula = sourceCell.Formula;

                // Copy number format
                targetCell.NumberFormat = sourceCell.NumberFormat;

                // Copy font properties
                targetCell.Style.Font.FontName = sourceCell.Style.Font.FontName;
                targetCell.Style.Font.Size = sourceCell.Style.Font.Size;
                targetCell.Style.Font.IsBold = sourceCell.Style.Font.IsBold;
                targetCell.Style.Font.IsItalic = sourceCell.Style.Font.IsItalic;
                targetCell.Style.Font.Color = sourceCell.Style.Font.Color;

                // Copy fill
                targetCell.Style.Color = sourceCell.Style.Color;

                // Copy alignment
                targetCell.Style.HorizontalAlignment = sourceCell.Style.HorizontalAlignment;
                targetCell.Style.VerticalAlignment = sourceCell.Style.VerticalAlignment;

                // Copy borders
                targetCell.Style.Borders[BordersLineType.EdgeLeft].LineStyle =
                    sourceCell.Style.Borders[BordersLineType.EdgeLeft].LineStyle;

                targetCell.Style.Borders[BordersLineType.EdgeRight].LineStyle =
                    sourceCell.Style.Borders[BordersLineType.EdgeRight].LineStyle;

                targetCell.Style.Borders[BordersLineType.EdgeTop].LineStyle =
                    sourceCell.Style.Borders[BordersLineType.EdgeTop].LineStyle;

                targetCell.Style.Borders[BordersLineType.EdgeBottom].LineStyle =
                    sourceCell.Style.Borders[BordersLineType.EdgeBottom].LineStyle;
            }

            // Copy row height
            sheet.SetRowHeight(
            targetRowIndex,
            sheet.GetRowHeight(sourceRowIndex));
        }

        public static MemoryStream customInvoiceAsMS(ref Dictionary<string, string> CellData)
        {
            MemoryStream ms = new MemoryStream();
            Workbook workbook = new Workbook();

            string fontDirectory = AppDomain.CurrentDomain.BaseDirectory;   // for non Windows systems use the .exe folder
            string[] fontDirectories = { fontDirectory };

            workbook = LoadEmbeddedWorkbook("BDF.Embedded.template.xlsx");

            int dpi = 600;// image conversion resolution
            workbook.ConverterSetting.XDpi = (dpi);
            workbook.ConverterSetting.YDpi = (dpi);
            workbook.ConverterSetting.JPEGQuality = 99;

            workbook.CustomFontFileDirectory = fontDirectories;

            foreach (var cell in CellData)
            {
                if ( cell.Key != "" )
                    workbook.Worksheets[0].Range[cell.Key].Text = cell.Value;

            } // end foreach

            workbook.ConverterSetting.SheetFitToPage = true;

            //Load ARIAL.TTF font to working dir if needed
            try
            {
                workbook.SaveToStream(ms, FileFormat.PDF);
            }
            catch (Exception e)
            {
                bdf.ExtractResourceToFile("BDF.Embedded.arial.ttf", (AppDomain.CurrentDomain.BaseDirectory + "./arial.ttf"));  // arial.ttf is needed to render the PDF
                workbook.SaveToStream(ms, FileFormat.PDF);
                Logger.Log(1, "Error Rendering PDF Invoice", e.Message);
            }
            return ms;
        }

        public static void customInvoiceSaveFile(string type, string trace, MemoryStream ms)
        {
            ms.Position = 0;
            string svc_path = bdf.upload ? bdf.base_path + "BDF_Archive" + Path.DirectorySeparatorChar : "";
            string pdf_name = bdf.upload ? type.ToUpper() : "." + Path.DirectorySeparatorChar + type.ToUpper() + "_INV" + trace + ".pdf";
            if (!bdf.FileIsWritable(svc_path + pdf_name)) return;

            File.WriteAllBytes(svc_path + pdf_name, ms.ToArray());
            Logger.Log(0, "PDF written! ({0})", svc_path + pdf_name);
        }

        public static void customInvoiceSFTPUpload(string type, string trace, MemoryStream ms)
        {
            //string pdf_name = bdf.upload ? type.ToUpper() : "." + Path.DirectorySeparatorChar + type.ToUpper() + "_INV" + trace + ".pdf";
            string pdf_name = type.ToUpper() + Path.DirectorySeparatorChar + type.ToUpper() + "_INV" + trace + ".pdf";
            {
                Logger.Log(0, " Initiating {0}-INV upload to EFT server...", type);
                try
                {
                    //using (var ms = new MemoryStream())
                    {
                        try
                        {
                            sftp.Upload_Stream(ms, "Outbox" + Path.DirectorySeparatorChar + pdf_name, bdf.sshDetails);
                        }
                        catch (Exception e)
                        {
                            Logger.Log(0, "INV SFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
                            return;
                        }
                        //Logger.Log("  Invoice uploaded.");
                    }
                }
                catch (Exception e)
                {
                    Logger.Log(0, "INVSFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
                    return;
                }
            }

        }

        public static void customInvoice_orig(string type, string trace, ref Dictionary<string, string> CellData)
        {
            //string fontDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "");
            string fontDirectory = AppDomain.CurrentDomain.BaseDirectory;   // for non Windows systems use the .exe folder
            //string fontDirectory2 = @"C:\\Windows\\Fonts\\";
            //string[] fontDirectories = { fontDirectory, fontDirectory2 };
            string[] fontDirectories = { fontDirectory };

            Workbook workbook = new Workbook();

            workbook = LoadEmbeddedWorkbook("BDF.Embedded.template.xlsx");

            int dpi = 600;// image conversion resolution
            workbook.ConverterSetting.XDpi = (dpi);
            workbook.ConverterSetting.YDpi = (dpi);
            workbook.ConverterSetting.JPEGQuality = 99;

            // Create PdfSaveOptions instance
            //PdfSaveOptions options = new Converter.PdfSaveOptions();

            // Set a higher image resolution (e.g., 300 dpi for print quality)
            //options.ImageResolution = 600; // You can experiment with values like 144, 220, or 300

            workbook.CustomFontFileDirectory = fontDirectories;

            // 4. (Optional) Work with the workbook, e.g., modify a cell
            foreach (var cell in CellData)
            {
                // workbook.Worksheets[0].Range["A1"].Text = "Data loaded from stream!";
                workbook.Worksheets[0].Range[cell.Key].Text = cell.Value;

            } // end foreach

            //Spire.Xls.Worksheet sheet = workbook.Worksheets[0];
            //sheet.PageSetup.PrintArea = "B1:E6";

            // Optional: Set worksheets to fit to page when converting
            workbook.ConverterSetting.SheetFitToPage = true;

            InvoiceCell ic = new InvoiceCell { };
            //string prefix = CellData[ic.Invoice].Substring(0, 16).Replace(' ', '_');
            //string pdf_name = bdf.output_path + type.ToUpper() + "_" + prefix + trace + ".pdf";
            string svc_path = bdf.upload ? bdf.base_path + "BDF_Archive" + Path.DirectorySeparatorChar : "." + Path.DirectorySeparatorChar;
            string pdf_name = bdf.upload ? type.ToUpper() : "." + Path.DirectorySeparatorChar + type.ToUpper() + "_INV" + trace + ".pdf";
            if (!bdf.FileIsWritable(svc_path+pdf_name)) return;

            workbook.SaveToFile(svc_path+pdf_name, FileFormat.PDF);
            Logger.Log(0, "PDF written! ({0})", svc_path+pdf_name);

            // are we uploading to EFT SFTP server?
            if (bdf.upload)
            {
                Logger.Log(0, " Initiating {0}-INV upload to EFT server...", type);
                try
                {
                    using (var ms = new MemoryStream())
                    {
                        try
                        {
                            workbook.SaveToStream(ms, FileFormat.PDF);  //Save as PDF
                        }
                        catch (Exception e)
                        {
                            Logger.Log("  Invoice failed to format for SFTP. ({0})", e.Message);
                            return;
                        }

                        try
                        {
                            sftp.Upload_Stream(ms, "Outbox" + Path.DirectorySeparatorChar + pdf_name, bdf.sshDetails);
                        }
                        catch (Exception e)
                        {
                            Logger.Log(0, "INV SFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
                            return;
                        }
                        //Logger.Log("  Invoice uploaded.");
                    }
                }
                catch (Exception e)
                {
                    Logger.Log(0, "INVSFTP Upload Exception: {0}:{1}", e.Message, e.InnerException.Message);
                    return;
                }
            }

        }

        public static string dateFormal(int dateInt)
        {
            //int dateInt = 20251130;
            string dateString = dateInt.ToString();

            // 1. Define the input format ("yyyyMMdd")
            string inputFormat = "yyyyMMdd";

            // 2. Parse the string into a DateTime object

            DateTime dateTimeObject = DateTime.ParseExact(
                dateString,
                inputFormat,
                CultureInfo.InvariantCulture
            );

            // 3. Define the desired output format ("MMMM dd, yyyy")
            // MMMM = Full month name
            // dd   = Day of the month
            // yyyy = Year

            string outputFormat = "MMMM d, yyyy";
            string result = dateTimeObject.ToString(outputFormat);

            return result;
        }

    }
}