using DocumentFormat.OpenXml.Vml.Spreadsheet;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Windows.Forms.DataVisualization.Charting;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace ExcelAnalyzer
{
    public partial class Form1 : Form
    {
        // Dictionary keyed by date with total usage (kWh)
        private Dictionary<DateTime, double> dataFile1 = new();
        private Dictionary<DateTime, double> dataFile2 = new();
        private StatusStrip statusStrip;
        private ToolStripStatusLabel statusLabel;
        private Dictionary<DateTime, (double Usage, double Cost)> data1;
        private Dictionary<DateTime, (double Usage, double Cost)> data2;
        // Pricing tiers data structure
        private class TierPricing
        {
            public double StartThreshold { get; set; } // inclusive
            public double EndThreshold { get; set; }   // exclusive
            public double Peak { get; set; }
            public double OffPeak { get; set; }
            public double SuperOffPeak { get; set; }
        }

        // Define tiers for 2024 and 2025 (example for 2024, modify as needed)
        // Tier 1: 0 - 600
        // Tier 2: 601 - 1000
        // Tier 3: 1001+
        private List<TierPricing> tiers2024 = new()
        {
            new TierPricing { StartThreshold = 0,    EndThreshold = 600,  Peak = 0.13, OffPeak = 0.12, SuperOffPeak = 0.11 },
            new TierPricing { StartThreshold = 600,  EndThreshold = 1000, Peak = 0.13, OffPeak = 0.12, SuperOffPeak = 0.11 },
            new TierPricing { StartThreshold = 1000, EndThreshold = double.MaxValue, Peak = 0.14, OffPeak = 0.13, SuperOffPeak = 0.12 }
        };


        private List<TierPricing> tiers2025 = new()
        {
            new TierPricing { StartThreshold = 600, Peak = 0.14, OffPeak = 0.13, SuperOffPeak = 0.12 },
            new TierPricing { StartThreshold = 1000, Peak = 0.14, OffPeak = 0.13, SuperOffPeak = 0.12 },
            new TierPricing { StartThreshold = double.MaxValue, Peak = 0.15, OffPeak = 0.14, SuperOffPeak = 0.13 }
        };


        // Assume the year of data for tier selection, for demo pick 2024
        private List<TierPricing> currentTiers => DateTime.Now.Year == 2025 ? tiers2025 : tiers2024;

        public Form1()
        {

            // Initialize status strip
            panel1.Controls.Add(this.chart1);
            chart1.Dock = DockStyle.Fill;

            panel2.Controls.Add(this.chart2);
            chart2.Dock = DockStyle.Fill;

            statusStrip = new StatusStrip();
            statusLabel = new ToolStripStatusLabel
            {
                Text = "Ready",
                Spring = true,
                TextAlign = ContentAlignment.MiddleLeft
            };

            statusStrip.Items.Add(statusLabel);
            this.Controls.Add(statusStrip);

            SetupStatusStrip();
            // Update the license configuration to use the new EPPlus 8 licensing model.
            EPPlusLicense license = new EPPlusLicense();
            license.SetNonCommercialPersonal("WTWOps");

            // Initialize charts and panels

            //this.chart1 = new System.Windows.Forms.DataVisualization.Charting.Chart();
            //this.chart2 = new System.Windows.Forms.DataVisualization.Charting.Chart();




            // Initialize data dictionaries
            data1 = new Dictionary<DateTime, (double Usage, double Cost)>();
            data2 = new Dictionary<DateTime, (double Usage, double Cost)>();
        }
        private void UpdateStatus(string message)
        {
            statusLabel.Text = message;
        }
        private void SetupStatusStrip()
        {
            statusStrip = new StatusStrip();
            statusLabel = new ToolStripStatusLabel
            {
                Text = "Ready",
                Spring = true, // makes it stretch and align left
                TextAlign = ContentAlignment.MiddleLeft
            };

            statusStrip.Dock = DockStyle.Bottom; // This makes it span the bottom
            statusStrip.Items.Add(statusLabel);
            this.Controls.Add(statusStrip);
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // Initialize controls, load data, etc.
            UpdateStatus("Form loaded. Please select files to analyze.");
            // Set default values for date pickers
            dateTimePickerStart.Value = new DateTime(2025, 6, 15);
            dateTimePickerEnd.Value = new DateTime(2025, 6, 22);
        }
        private void numScale_ValueChanged(object sender, EventArgs e)
        {
            // TODO: Apply scaling logic
        }
        private void btnSelectFile1_Click(object sender, EventArgs e)
        {
            var file = SelectSingleExcelFile();
            if (file == null) return;

            dataFile1 = LoadExcelData(file);
            UpdateChartAndGrid();
            statusLabel.Text = "Working...";
            statusStrip.Refresh(); // Forces redraw
            statusLabel.Text = "File 1 loaded successfully. Please select File 2.";
        }

        private void btnSelectFile2_Click(object sender, EventArgs e)
        {
            var file = SelectSingleExcelFile();
            if (file == null) return;

            dataFile2 = LoadExcelData(file);
            UpdateChartAndGrid();
            statusLabel.Text = "Working...";
            statusStrip.Refresh(); // Forces redraw
            statusLabel.Text = "File 2 loaded successfully. Data ready for analysis.";

        }

        private string? SelectSingleExcelFile()
        {
            using OpenFileDialog dlg = new()
            {
                Filter = "Excel Files (*.xlsx)|*.xlsx",
                Multiselect = false
            };

            statusLabel.Text = "Working...";
            statusStrip.Refresh(); // Forces redraw

            if (dlg.ShowDialog() != DialogResult.OK)
            {
                statusLabel.Text = "File selection cancelled.";
                return null;
            }

            // Validate file extension
            if (Path.GetExtension(dlg.FileName).ToLower() != ".xlsx")
            {
                MessageBox.Show("Please select a valid Excel file (.xlsx).", "Invalid File", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }

            // Check if file exists and has valid size
            FileInfo fileInfo = new(dlg.FileName);
            if (!fileInfo.Exists)
            {
                MessageBox.Show("Selected file does not exist.", "File Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }

            if (fileInfo.Length == 0)
            {
                MessageBox.Show("Selected file is empty.", "Empty File", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }

            if (fileInfo.Length < 1024) // less than 1KB
            {
                MessageBox.Show("Selected file is too small to be valid.", "Invalid File", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }

            // Everything is okay
            return dlg.FileName;
        }



        private double CalculateCostByTiers(double usage, ref double cumulativeUsage, Func<TierPricing, double> getPrice)
        {
            double cost = 0;
            foreach (var tier in currentTiers.OrderBy(t => t.StartThreshold))
            {
                if (cumulativeUsage >= tier.StartThreshold + tier.EndThreshold)
                {
                    continue;
                }

                double tierAvailable = (tier.StartThreshold + tier.EndThreshold) - cumulativeUsage;
                double tierUsage = Math.Min(tierAvailable, usage);

                if (tierUsage > 0)
                {
                    cost += tierUsage * getPrice(tier);
                    cumulativeUsage += tierUsage;
                    usage -= tierUsage;
                }

                if (usage <= 0) break;
            }

            statusLabel.Text = "Working...";
            statusStrip.Refresh(); // Forces redraw
            statusLabel.Text = "Cost calculation completed.";
            return cost;
        }


        private Dictionary<DateTime, double> LoadExcelData(string filePath)
        {
            var result = new Dictionary<DateTime, double>();
            try
            {
                using var package = new ExcelPackage(new FileInfo(filePath));
                var worksheet = package.Workbook.Worksheets.FirstOrDefault();
                if (worksheet == null)
                {
                    MessageBox.Show("Worksheet not found.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return result;
                }

                int headerRow = 7;
                int dateCol = GetColumnByName(worksheet, "DATE", headerRow);
                int usageCol = GetColumnByName(worksheet, "USAGE (kWh)", headerRow);

                if (dateCol == -1 || usageCol == -1)
                {
                    MessageBox.Show("Required columns not found.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return result;
                }

                int row = headerRow + 1;
                while (true)
                {
                    var dateCell = worksheet.Cells[row, dateCol].Text;
                    var usageCell = worksheet.Cells[row, usageCol].Text;

                    if (string.IsNullOrWhiteSpace(dateCell) && string.IsNullOrWhiteSpace(usageCell))
                        break;

                    if (DateTime.TryParse(dateCell, out var date) && double.TryParse(usageCell, out var usage))
                    {
                        if (!result.ContainsKey(date))
                            result[date] = 0;
                        result[date] += usage;  // overwrite if duplicate date
                    }

                    row++;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Failed to load Excel file:\n{ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            statusLabel.Text = "Working...";
            statusStrip.Refresh(); // Forces redraw
            statusLabel.Text = "Data loaded successfully.";
            return result;
        }

        // Helper to get column index by header name
        private int GetColumnByName(OfficeOpenXml.ExcelWorksheet worksheet, string headerName, int headerRow)
        {
            for (int col = 1; col <= worksheet.Dimension.End.Column; col++)
            {
                var cellText = worksheet.Cells[headerRow, col].Text?.Trim();
                if (string.Equals(cellText, headerName, StringComparison.OrdinalIgnoreCase))
                    return col;
            }
            return -1;

        }
        private Dictionary<DateTime, double> GetDailyTotals(ExcelWorksheet worksheet, int dateCol, int usageCol, int startRow)
        {
            var dailyTotals = new Dictionary<DateTime, double>();
            double totalUsage = 0;
            int endRow = worksheet.Dimension.End.Row;

            for (int row = startRow; row <= endRow; row++)
            {
                string dateText = worksheet.Cells[row, dateCol].Text;
                string usageText = worksheet.Cells[row, usageCol].Text;

                if (DateTime.TryParse(dateText, out DateTime date) &&
                    double.TryParse(usageText, out double usage))
                {
                    totalUsage += usage;
                }
            }

            for (int row = startRow; row <= worksheet.Dimension.End.Row; row++)
            {
                var dateText = worksheet.Cells[row, dateCol].Text;
                var usageText = worksheet.Cells[row, usageCol].Text;


                if (DateTime.TryParse(dateText, out DateTime date) &&
                    double.TryParse(usageText, out double usage))
                {
                    var day = date.Date;
                    if (!dailyTotals.ContainsKey(day))
                        dailyTotals[day] = 0;

                    dailyTotals[day] += usage;
                }
            }
            return dailyTotals;
        }
        // Split daily usage into time periods by hour proportion
        // Peak: 4pm-8pm (4 hours)
        // OffPeak: 6am-4pm + 8pm-12am (14 hours)
        // SuperOffPeak: 12am-6am (6 hours)
        private (double Peak, double OffPeak, double SuperOffPeak) SplitUsage(double totalUsage)
        {

            double peakHours = 4;
            double offPeakHours = 14;
            double superOffPeakHours = 6;
            double totalHours = peakHours + offPeakHours + superOffPeakHours;
            statusLabel.Text = "Working...";
            statusStrip.Refresh(); // Forces redraw
            return (
                    Peak: totalUsage * peakHours / totalHours,
                    OffPeak: totalUsage * offPeakHours / totalHours,
                    SuperOffPeak: totalUsage * superOffPeakHours / totalHours

                    );
        }
        private double CalculateCost(double dailyUsage, List<TierPricing> tiers)
        {
            double remaining = dailyUsage;
            double totalCost = 0;

            double peakUsage = dailyUsage * (4.0 / 24.0);
            double offPeakUsage = dailyUsage * (14.0 / 24.0);
            double superOffPeakUsage = dailyUsage * (6.0 / 24.0);

            foreach (var tier in tiers)
            {
                double tierRange = tier.EndThreshold - tier.StartThreshold;

                if (remaining <= 0)
                    break;

                double usageInTier = Math.Min(remaining, tierRange);

                double ratio = usageInTier / dailyUsage;

                totalCost += (peakUsage * ratio * tier.Peak)
                           + (offPeakUsage * ratio * tier.OffPeak)
                           + (superOffPeakUsage * ratio * tier.SuperOffPeak);
                remaining -= usageInTier;
            }
            statusLabel.Text = "Working...";
            statusStrip.Refresh(); // Forces redraw
            statusLabel.Text = "Cost calculation completed.";
            return totalCost;
        }

        // Update chart and data grid with usage and cost calculations
        private void UpdateChartAndGrid()
        {
            // chart1 setup
            chart1.Series.Clear();
            chart1.ChartAreas.Clear();
            chart1.Titles.Clear();
            chart2.Series.Clear();
            chart2.ChartAreas.Clear();
            chart1.Titles.Add("Power Usage & Cost Comparison");

            ChartArea usageArea = new ChartArea("UsageArea");
            usageArea.Position.Width = 100; // Full width
            usageArea.InnerPlotPosition = new ElementPosition(40, 40, 85, 85);
            usageArea.Position.Height = 100; //in %
            usageArea.AxisX.LabelStyle.Angle = -45;
            usageArea.AxisX.Interval = 1;
            usageArea.AxisX.Title = "Date (MM-DD)";
            usageArea.AxisY.Title = "kWh Used";
            usageArea.BorderColor = Color.Black;
            usageArea.BorderDashStyle = ChartDashStyle.Solid;
            usageArea.BorderWidth = 1;
            chart1.ChartAreas.Add(usageArea);

            // Add usage series
            Series file1 = new Series("File 1 Usage")
            {
                ChartType = SeriesChartType.Column,
                Color = Color.Blue,
                ChartArea = "UsageArea",
                BorderWidth = 3,           // Border thickness
                BorderColor = Color.Black,  // Border color
                IsVisibleInLegend = false
            };
            Series file2 = new Series("File 2 Usage")
            {
                ChartType = SeriesChartType.Column,
                Color = Color.Red,
                ChartArea = "UsageArea",
                BorderWidth = 3,
                BorderColor = Color.Black,  // Border color
                IsVisibleInLegend = false
            };
            // Pricing tier bands
            StripLine tier1 = new StripLine()
            {
                IntervalOffset = 0,
                StripWidth = 500,
                BackColor = Color.Purple,
                Text = "Tier 1",
                TextAlignment = StringAlignment.Near,
                Interval = 0
            };

            StripLine tier2 = new StripLine()
            {
                IntervalOffset = 500,
                StripWidth = 400,
                BackColor = Color.MediumPurple,
                Text = "Tier 2",
                TextAlignment = StringAlignment.Near,
                Interval = 0
            };

            StripLine tier3 = new StripLine()
            {
                IntervalOffset = 900,
                StripWidth = 1000,
                BackColor = Color.Orange,
                Text = "Tier 3",
                TextAlignment = StringAlignment.Near,
                Interval = 0
            };
            chart1.Series.Add(file1);
            usageArea.AxisY.StripLines.Add(tier1);
            usageArea.AxisY.StripLines.Add(tier2);
            usageArea.AxisY.StripLines.Add(tier3);


            // Merge all dates from both files, ordered
            var allDates = dataFile1.Keys
            .Concat(dataFile2.Keys)
            .Select(d => d.Date)
            .Select(d => new DateTime(2000, d.Month, d.Day)) // Normalize to same year
            .Distinct()
            .OrderBy(d => d)
            .ToList();

            // Build DataTable for grid
            var table = new System.Data.DataTable();

            table.Columns.Add("DATE", typeof(string));

            // File 1 usage & cost columns
            table.Columns.Add("File1 Usage (kWh)", typeof(double));
            table.Columns.Add("File1 Peak Usage (kWh)", typeof(double));
            table.Columns.Add("File1 Off-Peak Usage (kWh)", typeof(double));
            table.Columns.Add("File1 Super Off-Peak Usage (kWh)", typeof(double));
            table.Columns.Add("File1 Cost ($)", typeof(double));

            // File 2 usage & cost columns
            table.Columns.Add("File2 Usage (kWh)", typeof(double));
            table.Columns.Add("File2 Peak Usage (kWh)", typeof(double));
            table.Columns.Add("File2 Off-Peak Usage (kWh)", typeof(double));
            table.Columns.Add("File2 Super Off-Peak Usage (kWh)", typeof(double));
            table.Columns.Add("File2 Cost ($)", typeof(double));


            // Difference columns (usage and cost)
            table.Columns.Add("Usage Difference (kWh)", typeof(double));
            table.Columns.Add("Cost Difference ($)", typeof(double));

            // Prepare series for chart (usage lines for file1 and file2)
            var series1 = new Series("File1 Total Usage")
            {
                ChartType = SeriesChartType.Column,
                Color = Color.Transparent, // prevent default color override
                XValueType = ChartValueType.DateTime
            };
            var series2 = new Series("File2 Total Usage")
            {
                ChartType = SeriesChartType.Column,
                Color = Color.Transparent, // prevent default color override
                XValueType = ChartValueType.DateTime
            };



            data1.Clear();
            data2.Clear();


            double cumulativeFile1 = 0;
            double cumulativeFile2 = 0;

            ChartArea cumulativeArea = new ChartArea("CumulativeArea");
            cumulativeArea.Position = new ElementPosition(0, 50, 100, 50);
            cumulativeArea.InnerPlotPosition = new ElementPosition(10, 10, 85, 85);
            cumulativeArea.Position.Height = 100;
            cumulativeArea.AxisX.LabelStyle.Angle = -45;
            cumulativeArea.AxisX.Interval = 1;
            cumulativeArea.AxisX.Title = "Date (MM-DD)";
            cumulativeArea.AxisY.Title = "Cumulative Usage (kWh)";
            cumulativeArea.AxisX.IsMarginVisible = true;
            cumulativeArea.BorderColor = Color.Red;
            cumulativeArea.BorderDashStyle = ChartDashStyle.Solid;
            cumulativeArea.BorderWidth = 1;
            chart2.ChartAreas.Add(cumulativeArea);
            chart2.ResetAutoValues();

            Series cumulativeLine1 = new Series("Cumulative File 1")
            {
                ChartType = SeriesChartType.Line,
                Color = Color.Blue,
                BorderWidth = 2,
                XValueType = ChartValueType.String,
                ChartArea = "CumulativeArea"
            };

            Series cumulativeLine2 = new Series("Cumulative File 2")
            {
                ChartType = SeriesChartType.Line,
                Color = Color.DarkRed,
                BorderWidth = 2,
                XValueType = ChartValueType.String,
                ChartArea = "CumulativeArea"
            };

            foreach (var dayOnly in allDates)
            {
                // Match any date from either dictionary that has the same MM-dd
                var usage1 = dataFile1
                    .Where(kvp => kvp.Key.Month == dayOnly.Month && kvp.Key.Day == dayOnly.Day)
                    .Select(kvp => kvp.Value)
                    .FirstOrDefault();

                var usage2 = dataFile2
                    .Where(kvp => kvp.Key.Month == dayOnly.Month && kvp.Key.Day == dayOnly.Day)
                    .Select(kvp => kvp.Value)
                    .FirstOrDefault();
                var date = new DateTime(2000, dayOnly.Month, dayOnly.Day); // Normalize to same year for charting

                var split1 = SplitUsage(usage1);
                var split2 = SplitUsage(usage2);

                double cost1 =
                    CalculateCostByTiers(split1.Peak, ref cumulativeFile1, tier => tier.Peak) +
                    CalculateCostByTiers(split1.OffPeak, ref cumulativeFile1, tier => tier.OffPeak) +
                    CalculateCostByTiers(split1.SuperOffPeak, ref cumulativeFile1, tier => tier.SuperOffPeak);

                double cost2 =
                    CalculateCostByTiers(split2.Peak, ref cumulativeFile2, tier => tier.Peak) +
                    CalculateCostByTiers(split2.OffPeak, ref cumulativeFile2, tier => tier.OffPeak) +
                    CalculateCostByTiers(split2.SuperOffPeak, ref cumulativeFile2, tier => tier.SuperOffPeak);

                data1[date] = (usage1, cost1);
                data2[date] = (usage2, cost2);


                // Add data row
                table.Rows.Add(
                    date.ToString("MM-dd"),
                    usage1, split1.Peak, split1.OffPeak, split1.SuperOffPeak, cost1,
                    usage2, split2.Peak, split2.OffPeak, split2.SuperOffPeak, cost2,
                    usage2 - usage1,
                    cost2 - cost1
                    );
                // Add points to chart series
                // Determine tier color based on cumulative usage

                // Add to cumulative line
                cumulativeFile1 += usage1;
                cumulativeFile2 += usage2;

                Color GetTierColor(double cumulativeFile1)
                {
                    if (cumulativeFile1 <= 600) return Color.Green;        // Tier 1
                    if (cumulativeFile1 <= 1000) return Color.MediumPurple; // Tier 2
                    return Color.DarkBlue;                             // Tier 3
                }
                ;

                // For File1
                var point1 = new DataPoint();
                point1.AxisLabel = date.ToString("MM-dd");
                point1.YValues = new[] { usage1 };
                point1.Color = GetTierColor(cumulativeFile1); // Tier color
                series1.Points.Add(point1);

                // For File2
                var point2 = new DataPoint();
                point2.AxisLabel = date.ToString("MM-dd");
                point2.YValues = new[] { usage2 };
                point2.Color = GetTierColor(cumulativeFile2); // Tier color
                series2.Points.Add(point2);

                cumulativeLine1.Points.AddXY(date.ToString("MM-dd"), cumulativeFile1);
                cumulativeLine2.Points.AddXY(date.ToString("MM-dd"), cumulativeFile2);
            }
            dataGridView1.DataSource = table;
            chart2.Series.Add(cumulativeLine1);
            chart2.Series.Add(cumulativeLine2);

            // Set chart1 legend
            chart1.Legends.Clear();
            Legend tierLegend = new Legend("TierLegend")
            {
                Docking = Docking.Bottom,
                Alignment = StringAlignment.Center,
                Font = new Font("Segoe UI", 9, FontStyle.Bold),
                IsDockedInsideChartArea = false,
                BorderColor = Color.Black,
                BorderWidth = 1,
                BorderDashStyle = ChartDashStyle.Solid
            };
            chart1.Legends.Add(tierLegend);

            // Remove any previous dummy series first if needed
            // Then add dummy series just for the legend with tier colors
            void AddTierLegendItem(string label, Color color)
            {
                var dummySeries = new Series
                {
                    Name = label,
                    Color = color,
                    IsVisibleInLegend = true,
                    LegendText = label,
                    ChartType = SeriesChartType.Column,
                    BorderWidth = 1,
                    BorderColor = Color.Black,
                    Enabled = false // Don't show on chart
                };
                chart1.Series.Add(dummySeries);
            }

            AddTierLegendItem("Tier 1 (0 – 600 kWh)", Color.Green);
            AddTierLegendItem("Tier 2 (601 – 1000 kWh)", Color.MediumPurple);
            AddTierLegendItem("Tier 3 (1001+ kWh)", Color.DarkBlue);

            if (chart1.ChartAreas.Count > 0)
            {
                var area = chart1.ChartAreas[0];
                area.Position.Height = 100;
                area.Position.Width = 100;
                chart1.Series[0].XValueType = ChartValueType.String;
                area.AxisX.Title = "Date (MM-DD)";
                area.AxisX.LabelStyle.Format = "";
                area.AxisX.Interval = 1;
                area.AxisX.LabelStyle.Angle = -45;

                area.AxisY.Title = "Usage (kWh)";
                area.AxisY.MajorGrid.LineColor = System.Drawing.Color.LightGray;
                chart1.Series.Add(series1);
                chart1.Series.Add(series2);

                // Add shaded background tiers to chart1's Y-axis
                void AddTierShadingToChart1(Chart chart)
                {
                    var area = chart.ChartAreas[0];
                    area.AxisY.StripLines.Clear(); // Clear previous strip lines if any

                    // Tier 1: 0–600 kWh (Green)
                    StripLine tier1 = new StripLine
                    {
                        IntervalOffset = 0,
                        StripWidth = 300,
                        BackColor = Color.FromArgb(40, Color.Green) // semi-transparent
                    };
                    area.AxisY.StripLines.Add(tier1);

                    // Tier 2: 600–1000 kWh (Purple)
                    StripLine tier2 = new StripLine
                    {
                        IntervalOffset = 600,
                        StripWidth = 400,
                        BackColor = Color.FromArgb(40, Color.MediumPurple)
                    };
                    area.AxisY.StripLines.Add(tier2);

                    // Tier 3: 1000+ kWh (Orange)
                    StripLine tier3 = new StripLine
                    {
                        IntervalOffset = 1000,
                        StripWidth = 5000, // Arbitrary large value to cover high usage
                        BackColor = Color.FromArgb(40, Color.Orange)
                    };
                    area.AxisY.StripLines.Add(tier3);

                }
                var area1 = chart1.ChartAreas[0];
                area1.InnerPlotPosition = new ElementPosition(15, 10, 75, 80); // left, top, width, height in %

                var area2 = chart2.ChartAreas[0];
                area2.InnerPlotPosition = new ElementPosition(15, 10, 75, 80);

                // Call after chart1 setup
                AddTierShadingToChart1(chart1);
            }
        }
        private void buttonSummary_Click(object sender, EventArgs e)
        {
            ShowSummary();
        }

        private void ShowSummary()
        {
            double file1Total = 0, file1Peak = 0, file1Off = 0, file1Super = 0, file1Cost = 0;
            double file2Total = 0, file2Peak = 0, file2Off = 0, file2Super = 0, file2Cost = 0;

            // Calculate totals for each file

            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.IsNewRow) continue;

                // File 1
                double.TryParse(row.Cells["File1 Usage"].Value?.ToString(), out double f1Total);
                double.TryParse(row.Cells["File1 Peak Usage"].Value?.ToString(), out double f1Peak);
                double.TryParse(row.Cells["File1 Off-Peak"].Value?.ToString(), out double f1Off);
                double.TryParse(row.Cells["File1 Super"].Value?.ToString(), out double f1Super);
                double.TryParse(row.Cells["File1 Cost ($)"].Value?.ToString(), out double f1Cost);

                // File 2
                double.TryParse(row.Cells["File2 Usage"].Value?.ToString(), out double f2Total);
                double.TryParse(row.Cells["File2 Peak Usage"].Value?.ToString(), out double f2Peak);
                double.TryParse(row.Cells["File2 Off-Peak"].Value?.ToString(), out double f2Off);
                double.TryParse(row.Cells["File2 Super"].Value?.ToString(), out double f2Super);
                double.TryParse(row.Cells["File2 Cost ($)"].Value?.ToString(), out double f2Cost);

                // Add to totals
                file1Total += f1Total; file1Peak += f1Peak; file1Off += f1Off; file1Super += f1Super; file1Cost += f1Cost;
                file2Total += f2Total; file2Peak += f2Peak; file2Off += f2Off; file2Super += f2Super; file2Cost += f2Cost;
            }

            string summary =
                $"{"Category",-20}{"File1",-15}{"File2",-15}{"Difference",-15}\n" +
                $"{"".PadRight(60, '-')}\n" +
                $"{"Total Usage",-20}{file1Total,15:F2}{file2Total,15:F2}{(file2Total - file1Total),15:F2}\n" +
                $"{"Peak Usage",-20}{file1Peak,15:F2}{file2Peak,15:F2}{(file2Peak - file1Peak),15:F2}\n" +
                $"{"Off-Peak Usage",-20}{file1Off,15:F2}{file2Off,15:F2}{(file2Off - file1Off),15:F2}\n" +
                $"{"Super Off-Peak",-20}{file1Super,15:F2}{file2Super,15:F2}{(file2Super - file1Super),15:F2}\n" +
                $"{"Cost ($)",-20}{file1Cost,15:C2}{file2Cost,15:C2}{(file2Cost - file1Cost),15:C2}\n";

            // Output it
            MessageBox.Show(summary, "Summary");
        }
        private void btnLoadData_Click(object sender, EventArgs e)
        {
            if (dataFile1.Count == 0 || dataFile2.Count == 0)
            {
                MessageBox.Show("Please load both files before analyzing.", "Missing Data", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            statusLabel.Text = "Analyzing data...";
            statusStrip.Refresh();
            try
            {
                UpdateChartAndGrid();
                statusLabel.Text = "Data loaded successfully.";
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error loading data: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                statusLabel.Text = "Data load failed.";
            }
        }
        private void btnClear_Click(object sender, EventArgs e)
        {
            dataFile1.Clear();
            dataFile2.Clear();
            dataGridView1.DataSource = null;
            chart1.Series.Clear();
            chart2.Series.Clear();
            statusLabel.Text = "Data cleared. Please load new files.";
        }
        private void btnReset_Click(object sender, EventArgs e)
        {
            // Reset all controls to initial state
            dataFile1.Clear();
            dataFile2.Clear();
            dataGridView1.DataSource = null;
            chart1.Series.Clear();
            chart2.Series.Clear();
            statusLabel.Text = "Ready. Please select files to analyze.";
            txtTierDifferences.Clear();
            textBoxStartDate.Clear();
            textBoxEndDate.Clear();
        }
        private void btnExport_Click(object sender, EventArgs e)
        {
            if (dataFile1.Count == 0 || dataFile2.Count == 0)
            {
                MessageBox.Show("Please load both files before exporting.", "Missing Data", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            using SaveFileDialog saveDlg = new()
            {
                Filter = "Excel Files (*.xlsx)|*.xlsx",
                FileName = "ComparisonResults.xlsx"
            };
            if (saveDlg.ShowDialog() != DialogResult.OK) return;
            try
            {
                using var package = new ExcelPackage();
                var worksheet = package.Workbook.Worksheets.Add("Comparison Results");
                // Write headers
                worksheet.Cells[1, 1].Value = "Date";
                worksheet.Cells[1, 2].Value = "File 1 Usage (kWh)";
                worksheet.Cells[1, 3].Value = "File 1 Peak Usage (kWh)";
                worksheet.Cells[1, 4].Value = "File 1 Off-Peak Usage (kWh)";
                worksheet.Cells[1, 5].Value = "File 1 Super Off-Peak Usage (kWh)";
                worksheet.Cells[1, 6].Value = "File 1 Cost ($)";
                worksheet.Cells[1, 7].Value = "File 2 Usage (kWh)";
                worksheet.Cells[1, 8].Value = "File 2 Peak Usage (kWh)";
                worksheet.Cells[1, 9].Value = "File 2 Off-Peak Usage (kWh)";
                worksheet.Cells[1, 10].Value = "File 2 Super Off-Peak Usage (kWh)";
                worksheet.Cells[1, 11].Value = "File 2 Cost ($)";
                worksheet.Cells[1, 12].Value = "Usage Difference (kWh)";
                worksheet.Cells[1, 13].Value = "Cost Difference ($)";
                // Write data
                int rowIndex = 2;
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    if (row.IsNewRow) continue; // Skip new row placeholder
                    for (int colIndex = 0; colIndex < row.Cells.Count; colIndex++)
                    {
                        worksheet.Cells[rowIndex, colIndex + 1].Value = row.Cells[colIndex].Value;
                    }
                    rowIndex++;
                }
                // Auto-fit columns
                worksheet.Cells.AutoFitColumns();
                // Save the package
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Failed to export data: {ex.Message}", "Export Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            try
            {
                Package.SaveAs(new FileInfo(saveDlg.FileName));
                MessageBox.Show("Data exported successfully.", "Export Complete", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Failed to save file: {ex.Message}", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        // Helper: Split daily usage into tier buckets
        private Dictionary<string, double> SplitDailyUsageIntoTiers(double usage)
        {
            var tierUsages = new Dictionary<string, double> { { "Tier1", 0 }, { "Tier2", 0 }, { "Tier3", 0 } };

            double remaining = usage;

            for (int i = 0; i < currentTiers.Count; i++)
            {
                var tier = currentTiers[i];
                double tierCapacity = tier.EndThreshold - tier.StartThreshold;
                double useInTier = Math.Min(remaining, tierCapacity);
                tierUsages["Tier" + (i + 1)] = useInTier;
                remaining -= useInTier;
                if (remaining <= 0) break;
            }

            return tierUsages;
        }

        // Helper: Calculate cost per tier from tier usage
        private Dictionary<string, double> CalculateTierCosts(Dictionary<string, double> tierUsages)
        {
            var tierCosts = new Dictionary<string, double>();

            for (int i = 0; i < currentTiers.Count; i++)
            {
                var tier = currentTiers[i];
                var tierName = "Tier" + (i + 1);
                double usage = tierUsages.ContainsKey(tierName) ? tierUsages[tierName] : 0;

                // Approximate average price per kWh for tier (you can tweak this)
                double price = (tier.Peak + tier.OffPeak + tier.SuperOffPeak) / 3;

                tierCosts[tierName] = usage * price;
            }

            return tierCosts;
        }
        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            var selectedDates = dataGridView1.SelectedRows
            .Cast<DataGridViewRow>()
            .Select(r => (DateTime)r.Cells["Date"].Value)
            .ToList();

            ShowTierDifferencesForDates(selectedDates);

        }

        private void btnCompare_Click(object sender, EventArgs e)
        {
            if (dataFile1 == null || dataFile2 == null)
            {
                MessageBox.Show("Please load both files before comparing.", "Missing Data", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                statusLabel.Text = "Both files must be loaded.";
                return;
            }

            statusLabel.Text = "Analyzing data...";
            statusStrip.Refresh();

            try
            {
                PerformAnalysis();
                statusLabel.Text = "Analysis complete.";
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error during analysis: {ex.Message}", "Analysis Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                statusLabel.Text = "Analysis failed.";
            }
        }
        private void ShowTierDifferencesForDates(List<DateTime> selectedDates)
        {
            var sb = new StringBuilder();

            foreach (var date in selectedDates)
            {
                // Find matching entries from each file for this specific date
                var usage1 = dataFile1.Where(entry => entry.Key.Date == date.Date);
                var usage2 = dataFile2.Where(entry => entry.Key.Date == date.Date);

                if (usage1.Any() && usage2.Any())
                {
                    // Sum up the kWh usage for the day (in case there are multiple entries per day)
                    double totalUsage1 = usage1.Sum(entry => entry.Value);
                    double totalUsage2 = usage2.Sum(entry => entry.Value);


                    // Initialize cumulative usage for cost calculation
                    double cumulativeCost1 = 0;
                    double cumulativeCost2 = 0;

                    // Calculate costs by splitting usage into time periods and then applying tiers
                    var splitUsage1 = SplitUsage(totalUsage1);
                    var cost1 = CalculateCostByTiers(splitUsage1.Peak, ref cumulativeCost1, tier => tier.Peak) + CalculateCostByTiers(splitUsage1.OffPeak, ref cumulativeCost1, tier => tier.OffPeak) + CalculateCostByTiers(splitUsage1.SuperOffPeak, ref cumulativeCost1, tier => tier.SuperOffPeak);
                    var splitUsage2 = SplitUsage(totalUsage2);
                    var cost2 = CalculateCostByTiers(splitUsage2.Peak, ref cumulativeCost2, tier => tier.Peak) + CalculateCostByTiers(splitUsage2.OffPeak, ref cumulativeCost2, tier => tier.OffPeak) + CalculateCostByTiers(splitUsage2.SuperOffPeak, ref cumulativeCost2, tier => tier.SuperOffPeak);

                    sb.AppendLine($"Date: {date:MM/dd/yyyy}");
                    sb.AppendLine($"  File 1: {totalUsage1:F2} kWh = ${cost1:F2}");
                    sb.AppendLine($"  File 2: {totalUsage2:F2} kWh = ${cost2:F2}");
                    sb.AppendLine($"  Δ Usage: {(totalUsage2 - totalUsage1):F2} kWh");
                    sb.AppendLine($"  Δ Cost: ${(cost2 - cost1):F2}");
                    sb.AppendLine();
                }
                else
                {
                    sb.AppendLine($"Date: {date:MM/dd/yyyy} — data missing from one or both files.");
                }
                txtTierDifferences.Text = sb.ToString();
            }
        }
        private void buttonSearch_Click(object sender, EventArgs e)
        {
            if (!DateTime.TryParseExact(textBoxStartDate.Text.Trim(), "MM-dd", null, System.Globalization.DateTimeStyles.None, out DateTime start) ||
                !DateTime.TryParseExact(textBoxEndDate.Text.Trim(), "MM-dd", null, System.Globalization.DateTimeStyles.None, out DateTime end))
            {
                MessageBox.Show("Please enter dates in MM-dd format.");

                return;

            }

            // Normalize dates to same year for comparison
            start = new DateTime(2000, start.Month, start.Day);
            end = new DateTime(2000, end.Month, end.Day);

            if (end < start)
            {
                MessageBox.Show("End date must be after start date.");
                return;
            }

            // Get the indexes of the required columns
            int dateIndex = -1, usageDeltaIndex = -1, costDeltaIndex = -1;

            foreach (DataGridViewColumn col in dataGridView1.Columns)
            {
                if (col.HeaderText.Equals("DATE", StringComparison.OrdinalIgnoreCase)) dateIndex = col.Index;
                else if (col.HeaderText.Equals("Usage Difference (kWh)", StringComparison.OrdinalIgnoreCase)) usageDeltaIndex = col.Index;
                else if (col.HeaderText.Equals("Cost Difference ($)", StringComparison.OrdinalIgnoreCase)) costDeltaIndex = col.Index;

            }

            if (dateIndex == -1 || usageDeltaIndex == -1 || costDeltaIndex == -1)
            {
                MessageBox.Show("Missing one or more required columns: Date, Usage Δ, Cost Δ");
                return;
            }

            double totalUsageDelta = 0;
            double totalCostDelta = 0;
            int matchedRows = 0;

            RunSearch();

            dataGridView1.ClearSelection();

            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                var row = dataGridView1.Rows[i];
                if (row.IsNewRow) continue;

                string? dateStr = row.Cells[dateIndex].Value?.ToString();
                if (dateStr == null) continue;

                if (DateTime.TryParseExact(dateStr, "MM-dd", null, System.Globalization.DateTimeStyles.None, out DateTime rowDate))
                {
                    rowDate = new DateTime(2000, rowDate.Month, rowDate.Day);
                    if (rowDate >= start && rowDate <= end)
                    {
                        if (double.TryParse(row.Cells[usageDeltaIndex].Value?.ToString(), out double usageDelta))
                            totalUsageDelta += usageDelta;

                        if (double.TryParse(row.Cells[costDeltaIndex].Value?.ToString(), out double costDelta))
                            totalCostDelta += costDelta;


                        row.Selected = true;
                        matchedRows++;
                    }
                }
            }

            MessageBox.Show("Calculation triggered");
            if (matchedRows > 0)
            {
                labelResult.Text = $"Total Usage Difference: {totalUsageDelta:F2} kWh\n" +
                                   $"Total Cost Difference: ${totalCostDelta:F2}\n" +
                                   $"Matching Days: {matchedRows}";
            }
            else
            {
                labelResult.Text = "No matching dates found.";
            }
        }
        private void btnCompare_Click2(object sender, EventArgs e)
        {
            if (dataFile1 == null || dataFile2 == null)
            {
                MessageBox.Show("Please load both files before comparing.", "Missing Data", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                statusLabel.Text = "Both files must be loaded.";
                return;
            }

            statusLabel.Text = "Analyzing data...";
            statusStrip.Refresh();

            try
            {
                statusLabel.Text = "Analysis complete.";
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error during analysis: {ex.Message}", "Analysis Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                statusLabel.Text = "Analysis failed.";
            }
        }
        private void RunSearch()
        {
            if (!DateTime.TryParseExact(textBoxStartDate.Text.Trim(), "MM-dd", null, System.Globalization.DateTimeStyles.None, out DateTime start) ||
                !DateTime.TryParseExact(textBoxEndDate.Text.Trim(), "MM-dd", null, System.Globalization.DateTimeStyles.None, out DateTime end))
            {
                MessageBox.Show("Please enter dates in MM-dd format.");
                return;
            }

            start = new DateTime(2000, start.Month, start.Day);
            end = new DateTime(2000, end.Month, end.Day);

            if (end < start)
            {
                MessageBox.Show("End date must be after start date.");
                return;
            }

            int dateIndex = -1, usageDeltaIndex = -1, costDeltaIndex = -1;

            foreach (DataGridViewColumn col in dataGridView1.Columns)
            {
                if (col.HeaderText.Equals("DATE", StringComparison.OrdinalIgnoreCase)) dateIndex = col.Index;
                else if (col.HeaderText.Equals("Usage Difference (kWh)", StringComparison.OrdinalIgnoreCase)) usageDeltaIndex = col.Index;
                else if (col.HeaderText.Equals("Cost Difference ($)", StringComparison.OrdinalIgnoreCase)) costDeltaIndex = col.Index;
            }

            if (dateIndex == -1 || usageDeltaIndex == -1 || costDeltaIndex == -1)
            {
                MessageBox.Show("Missing one or more required columns: Date, Usage Δ, Cost Δ");
                return;
            }


            double totalUsageDelta = 0;
            double totalCostDelta = 0;
            int matchedRows = 0;

            dataGridView1.ClearSelection();

            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                var row = dataGridView1.Rows[i];
                if (row.IsNewRow) continue;

                string? dateStr = row.Cells[dateIndex].Value?.ToString();
                if (DateTime.TryParseExact(dateStr, "MM-dd", null, System.Globalization.DateTimeStyles.None, out DateTime rowDate))
                {
                    rowDate = new DateTime(2000, rowDate.Month, rowDate.Day);
                    if (rowDate >= start && rowDate <= end)
                    {
                        if (double.TryParse(row.Cells[usageDeltaIndex].Value?.ToString(), out double usageDelta))
                            totalUsageDelta += usageDelta;

                        if (double.TryParse(row.Cells[costDeltaIndex].Value?.ToString(), out double costDelta))
                            totalCostDelta += costDelta;

                        row.Selected = true;
                        matchedRows++;
                    }
                }
            }

            MessageBox.Show("Calculation triggered");

            labelResult.Text = matchedRows > 0
                ? $"Total Usage Difference: {totalUsageDelta:F2} kWh\n" +
                  $"Total Cost Difference: ${totalCostDelta:F2}\n" +
                  $"Matching Days: {matchedRows}"
                : "No matching dates found.";
        }

        private void LoadData()
        {

            data1 = new Dictionary<DateTime, (double Usage, double Cost)>();
            data2 = new Dictionary<DateTime, (double Usage, double Cost)>();

            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.IsNewRow) continue;

                if (DateTime.TryParse(row.Cells["Date"].Value?.ToString(), out DateTime date) &&
                    double.TryParse(row.Cells["Usage1"].Value?.ToString(), out double usage1) &&
                    double.TryParse(row.Cells["Cost1"].Value?.ToString(), out double cost1) &&
                    double.TryParse(row.Cells["Usage2"].Value?.ToString(), out double usage2) &&
                    double.TryParse(row.Cells["Cost2"].Value?.ToString(), out double cost2))
                {
                    var normalizedDate = new DateTime(2000, date.Month, date.Day); // normalize year, strip time
                    data1[normalizedDate] = (usage1, cost1);
                    data2[normalizedDate] = (usage2, cost2);
                }
            }
            var normalizedKeysData1 = data1.Keys.Select(d => new DateTime(2000, d.Month, d.Day)).ToList();
            var normalizedKeysData2 = data2.Keys.Select(d => new DateTime(2000, d.Month, d.Day)).ToList();

            var commonNormalizedDates = normalizedKeysData1.Intersect(normalizedKeysData2).ToList();

            MessageBox.Show($"Common dates (normalized): {commonNormalizedDates.Count}");
        }
        private void buttonLoadData_Click(object sender, EventArgs e)
        {
            LoadData();
        }

        private void buttonAnalyze_Click(object sender, EventArgs e)
        {
            PerformAnalysis();
        }



        private void dateTimePickerStart_ValueChanged(object sender, EventArgs e)
        {
            FilterDataByDateRange();
            DateTime start = new DateTime(2000, dateTimePickerStart.Value.Month, dateTimePickerStart.Value.Day);
            DateTime end = new DateTime(2000, dateTimePickerEnd.Value.Month, dateTimePickerEnd.Value.Day);

        }

        private void dateTimePickerEnd_ValueChanged(object sender, EventArgs e)
        {
            FilterDataByDateRange();
        }
        private void RunYourCalculation()
        {
            // This method will be called when the dropdown selection changes or button is clicked
            FilterDataByDateRange();
        }
        private void comboBoxSelection_SelectedIndexChanged(object sender, EventArgs e)
        {
            MessageBox.Show("Dropdown changed!"); // Just to test
            RunYourCalculation();
        }

        private void buttonCalculate_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Button clicked!");
            RunYourCalculation();
        }

        private void FilterDataByDateRange()
        {
            // Normalize the dates to the same year, for consistent comparison
            DateTime start = new DateTime(2000, dateTimePickerStart.Value.Month, dateTimePickerStart.Value.Day);
            DateTime end = new DateTime(2000, dateTimePickerEnd.Value.Month, dateTimePickerEnd.Value.Day);

            if (end < start)
            {
                MessageBox.Show("End date must be after start date.");
                return;
            }

            // Now, filter your data dictionaries and update the UI accordingly
            // Example: find all dates between start and end in your dataFile1 and dataFile2 dictionaries

            var filteredDates = dataFile1.Keys
                .Concat(dataFile2.Keys)
                .Select(d => new DateTime(2000, d.Month, d.Day))
                .Where(d => d >= start && d <= end)
                .Distinct()
                .OrderBy(d => d)
                .ToList();

            // You can now update your dataGridView or output box with this filtered data
            // Example: Show summary in a TextBox or update grid rows selection

            StringBuilder sb = new StringBuilder();

            foreach (var date in filteredDates)
            {
                var usage1 = dataFile1.Where(kvp => kvp.Key.Month == date.Month && kvp.Key.Day == date.Day).Sum(kvp => kvp.Value);
                var usage2 = dataFile2.Where(kvp => kvp.Key.Month == date.Month && kvp.Key.Day == date.Day).Sum(kvp => kvp.Value);

                // Calculate cost (you can reuse your cost calculation logic)
                double cumulative1 = 0, cumulative2 = 0;

                var split1 = SplitUsage(usage1);
                var cost1 = CalculateCostByTiers(split1.Peak, ref cumulative1, tier => tier.Peak)
                          + CalculateCostByTiers(split1.OffPeak, ref cumulative1, tier => tier.OffPeak)
                          + CalculateCostByTiers(split1.SuperOffPeak, ref cumulative1, tier => tier.SuperOffPeak);

                var split2 = SplitUsage(usage2);
                var cost2 = CalculateCostByTiers(split2.Peak, ref cumulative2, tier => tier.Peak)
                          + CalculateCostByTiers(split2.OffPeak, ref cumulative2, tier => tier.OffPeak)
                          + CalculateCostByTiers(split2.SuperOffPeak, ref cumulative2, tier => tier.SuperOffPeak);

                sb.AppendLine($"Date: {date:MM-dd}");
                sb.AppendLine($"  File 1 Usage: {usage1:F2} kWh, Cost: ${cost1:F2}");
                sb.AppendLine($"  File 2 Usage: {usage2:F2} kWh, Cost: ${cost2:F2}");
                sb.AppendLine($"  Difference: {(usage2 - usage1):F2} kWh, Cost Diff: ${(cost2 - cost1):F2}");
                sb.AppendLine();
            }

            // For example, display this in a multiline TextBox named txtTierDifferences
            txtTierDifferences.Text = sb.ToString();
        }
        private void PerformAnalysis()
        {
            if (data1 == null || data2 == null)
            {
                MessageBox.Show("Data is not initialized.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            double totalUsageDelta = 0;
            double totalCostDelta = 0;

            // Example: assuming data1 and data2 are class-level fields or accessible here
            var commonDates = data1.Keys.Intersect(data2.Keys).ToList();

            MessageBox.Show($"Number of common dates: {commonDates.Count}");
            if (commonDates.Count == 0)
            {
                MessageBox.Show("No matching dates found in both datasets.");
                return;
            }

            foreach (var date in commonDates)
            {
                var usage1 = data1[date].Usage;
                var cost1 = data1[date].Cost;

                var usage2 = data2[date].Usage;
                var cost2 = data2[date].Cost;

                double usageDelta = usage2 - usage1;
                double costDelta = cost2 - cost1;

                totalUsageDelta += usageDelta;
                totalCostDelta += costDelta;

                // You can append this info to labelResult or other UI elements if you want
            }
            foreach (var date in commonDates.Take(5))
            {
                var usage1 = data1[date].Usage;
                var cost1 = data1[date].Cost;
                var usage2 = data2[date].Usage;
                var cost2 = data2[date].Cost;

                MessageBox.Show($"Date: {date.ToShortDateString()}\n" +
                                $"Data1 Usage: {usage1}, Cost: {cost1}\n" +
                                $"Data2 Usage: {usage2}, Cost: {cost2}");
            }

            labelResult.Text = $"Total Usage Delta: {totalUsageDelta:F2}\nTotal Cost Delta: {totalCostDelta:C2}";
        }

        // Add a field to store summary data
        private object summaryData;

        // Update the buttonShowSummary_Click method to pass the correct type to SummaryForm
            private void InitializeComponent()   
        {
            // Assuming summaryData needs to be populated with some calculated data
            summaryData = CalculateSummaryData(); // Replace with actual logic to populate summaryData

            // Ensure summaryData is of the correct type: Dictionary<string, (double usageDiff, double costDiff)>
            if (summaryData is Dictionary<string, (double usageDiff, double costDiff)> validSummaryData)
            {
                var summaryForm = new SummaryForm(validSummaryData); // Pass the data to the new form
                summaryForm.Show(); // or ShowDialog() if you want it modal
            }
            else
            {
                MessageBox.Show("Summary data is not in the expected format.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        // Update the CalculateSummaryData method to return the correct type
        private Dictionary<string, (double usageDiff, double costDiff)> CalculateSummaryData()
        {
            // Replace this with the actual logic to calculate or retrieve the summary data
            return new Dictionary<string, (double usageDiff, double costDiff)>
            {
                { "ExampleKey", (usageDiff: 100.0, costDiff: 50.0) } // Example placeholder data
            };
        }

        private void buttonShowSummary_Click(object sender, EventArgs e)
        {
            // Replace with your actual summary generation method
            var summaryData = CalculateSummaryData();

            if (summaryData is Dictionary<string, (double usageDiff, double costDiff)> validSummaryData)
            {
                var summaryForm = new SummaryForm(validSummaryData);
                summaryForm.Show(); // or ShowDialog();
            }
            else
            {
                MessageBox.Show("Summary data is not in the expected format.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


        private void button1_Click_1(object sender, EventArgs e)
        {
            // Clear previous chart data
            chart1.Series.Clear();
            chart1.ChartAreas[0].AxisX.LabelStyle.Angle = -45;
            chart1.ChartAreas[0].AxisX.Interval = 1;

            // Series for usage and cost difference
            Series usageSeries = new Series("Usage Difference");
            Series costSeries = new Series("Cost Difference");

            usageSeries.ChartType = SeriesChartType.Column;
            costSeries.ChartType = SeriesChartType.Column;

            // Loop through common dates
            var commonDates = data1.Keys.Intersect(data2.Keys).OrderBy(d => d);
            foreach (var date in commonDates)
            {
                var (usage1, cost1) = data1[date];
                var (usage2, cost2) = data2[date];

                double usageDiff = usage2 - usage1;
                double costDiff = cost2 - cost1;

                usageSeries.Points.AddXY(date, usageDiff);
                costSeries.Points.AddXY(date, costDiff);
            }

            // Add series to chart
            chart1.Series.Add(usageSeries);
            chart1.Series.Add(costSeries);
        }


        internal record struct NewStruct(double Peak, object Item2, double OffPeak, object Item4, double SuperOffPeak)
        {
            public static implicit operator (double Peak, object, double OffPeak, object, double SuperOffPeak)(NewStruct value)
            {
                return (value.Peak, value.Item2, value.OffPeak, value.Item4, value.SuperOffPeak);
            }

            public static implicit operator NewStruct((double Peak, object, double OffPeak, object, double SuperOffPeak) value)
            {
                return new NewStruct(value.Peak, value.Item2, value.OffPeak, value.Item4, value.SuperOffPeak);
            }
        }
    }

    internal class Package
    {
        internal static void SaveAs(FileInfo fileInfo)
        {
            throw new NotImplementedException();
        }
    }
}
