using System;
using System.IO;
using System.Data;
using System.Linq;
using System.Windows.Forms;
//using Microsoft.Extensions.Logging;
using OfficeOpenXml;

namespace EssentialAccessibility
{
    public class EASync
    {
        private static DataTable sourceDataTable = new DataTable();
        private static DataTable targetDataTable = new DataTable();

        internal static DataTable syncedDataTable = new DataTable(); //data table with synced data
        internal static DataTable mergedDataTable = new DataTable(); //data table used to add data to DGV

        protected static void InitDataTable()
        {
            sourceDataTable.Columns.Add("Finding ID", typeof(string));
            sourceDataTable.Columns.Add("Status", typeof(string));

            targetDataTable.Columns.Add("Finding ID", typeof(string));
            targetDataTable.Columns.Add("Status", typeof(string));

            syncedDataTable.Columns.Add("Finding ID", typeof(string));
            syncedDataTable.Columns.Add("Status", typeof(string));
        }

        public static void ReadAndSyncData(string aSourceFile, string aTargetFile, ProgressBar aProgressBar)
        {
            InitDataTable();

            //READ FILES
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage sourcePackage = new ExcelPackage(new FileInfo(aSourceFile)))
            using (ExcelPackage targetPackage = new ExcelPackage(new FileInfo(aTargetFile)))
            {
                ExcelWorksheet sourceWorksheet = sourcePackage.Workbook.Worksheets[0];
                ExcelWorksheet targetWorksheet = targetPackage.Workbook.Worksheets[Properties.Settings.Default.targetWorksheetName];

                //find column indexes for source file;
                int sourceFindingIDColumnIndex = sourceWorksheet.Cells["1:1"]
                    .First(cell => cell.Value
                    ?.ToString() == Properties.Settings.Default.sourceFindingIDCol)
                    ?.Start
                    .Column ?? -1;

                int sourceStatusColumnIndex = sourceWorksheet.Cells["1:1"]
                    .First(cell => cell.Value
                    ?.ToString() == Properties.Settings.Default.sourceStatusCol)
                    ?.Start
                    .Column ?? -1;

                // Find column indexes for target file
                int targetSummaryColumnIndex = targetWorksheet.Cells["1:1"]
                    .First(cell => cell.Value
                    ?.ToString() == Properties.Settings.Default.targetFindingIDCol)
                    ?.Start
                    .Column ?? -1;

                int targetStatusColumnIndex = targetWorksheet.Cells["1:1"]
                    .First(cell => cell.Value
                    ?.ToString() == Properties.Settings.Default.targetStatusCol)
                    ?.Start
                    .Column ?? -1;

                //load data from source and target files;
                for (int rowIndex = 2; rowIndex <= sourceWorksheet.Dimension.End.Row; rowIndex++)
                {
                    string findingID = sourceWorksheet.Cells[rowIndex, sourceFindingIDColumnIndex].Value?.ToString();
                    string status = sourceWorksheet.Cells[rowIndex, sourceStatusColumnIndex].Value?.ToString();

                    if (!string.IsNullOrEmpty(findingID) && !string.IsNullOrEmpty(status))
                    {
                        DataRow row = sourceDataTable.NewRow();
                        row["Finding ID"] = findingID;
                        row["Status"] = status;
                        sourceDataTable.Rows.Add(row);
                    }
                }

                for (int rowIndex = 2; rowIndex <= targetWorksheet.Dimension.End.Row; rowIndex++)
                {
                    string FindingID = targetWorksheet.Cells[rowIndex, targetSummaryColumnIndex].Value?.ToString();
                    string targetStatus = targetWorksheet.Cells[rowIndex, targetStatusColumnIndex].Value?.ToString();

                    if (!string.IsNullOrEmpty(FindingID) && !string.IsNullOrEmpty(targetStatus))
                    {
                        DataRow row = targetDataTable.NewRow();
                        row["Finding ID"] = FindingID;
                        row["Status"] = targetStatus;
                        targetDataTable.Rows.Add(row);
                    }
                }

                //save initial target worksheet as a backup -
                string backupFilePath = Path.Combine(Path.GetDirectoryName(aTargetFile),
                                                     Path.GetFileNameWithoutExtension(aTargetFile)
                                                     + "_backup"
                                                     + Path.GetExtension(aTargetFile));

                //File.Copy(aTargetFile, backupFilePath, true);
                //targetPackage.SaveAs(new FileInfo(backupFilePath));

                sourcePackage.Dispose();

                //SYNC FILES;
                //find status column in the worksheet
                int statusColumnNum = targetWorksheet.Cells
                                      .First(col => col.Value?.ToString().Trim()
                                      .Equals(Properties.Settings.Default.targetStatusCol, StringComparison.OrdinalIgnoreCase) ?? false)
                                      ?.Start?.Column
                                      ?? throw new ArgumentException($"Status column not found in {aTargetFile}");

                for (int i = 0; i < sourceDataTable.Rows.Count; i++)
                {
                    string findingId = sourceDataTable.Rows[i].Field<string>("Finding ID");
                    string updatedStatus = sourceDataTable.Rows[i].Field<string>("Status");

                    //sync excel file;
                    int targetRowNum = targetWorksheet.Cells
                                        .First(col => col.Value?.ToString().Trim()
                                        .Equals(findingId) ?? false)
                                        .Start.Row;

                    ExcelRange statusCell = targetWorksheet.Cells[targetRowNum, statusColumnNum];

                    if (!statusCell.Value.Equals(updatedStatus))
                    {
                        targetWorksheet.Cells[targetRowNum, statusColumnNum].Value = updatedStatus;
                    }

                    var targetRows = from DataRow targetRow in targetDataTable.Rows
                                     where targetRow.Field<string>("Finding ID") == findingId
                                     select targetRow;

                    // Iterate over the matching rows and update their Status value
                    foreach (DataRow targetRow in targetRows)
                    {
                        targetRow.SetField("Status", updatedStatus);

                        DataRow updatedRow = syncedDataTable.NewRow();
                        updatedRow["Finding ID"] = findingId;
                        updatedRow["Status"] = updatedStatus;
                        syncedDataTable.Rows.Add(updatedRow);

                        //add data from source, target and updated data to DGV in Form
                        DataRow mergedRow = mergedDataTable.NewRow();
                        mergedRow["Finding ID"] = findingId;
                        mergedRow["eA State"] = sourceDataTable.Rows[i].Field<string>("Status");
                        mergedRow["old PS A11Y State"] = targetDataTable.Rows[i].Field<string>("Status");
                        mergedRow["new PS A11Y State"] = updatedStatus;
                        mergedDataTable.Rows.Add(mergedRow);
                    }

                    aProgressBar.Value = (int)((double)(i + 1) / sourceDataTable.Rows.Count * 100);
                }

                targetPackage.Save();

            }
        }
    }

}