using Microsoft.Graphics.Canvas;
using Microsoft.UI.Xaml;
using Microsoft.UI.Xaml.Controls;
using Microsoft.UI.Xaml.Controls.Primitives;
using Microsoft.UI.Xaml.Media;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using Windows.Storage.Pickers;
using WinRT.Interop;

namespace Sonex.Client.Controls.TimelineGrid;

public sealed partial class TimelineGrid
{
    public enum TimelineGridExportFormat
    {
        Csv,
        Xlsx,
        Tsv,
        Html
    }

    public enum TimelineGridExportRowScope
    {
        AllRows,
        SelectedRow
    }

    public sealed class TimelineGridExportOptions
    {
        public bool IncludeHeaders { get; set; } = true;
        public TimelineGridExportRowScope RowScope { get; set; } = TimelineGridExportRowScope.AllRows;
        public bool FallbackToAllRowsWhenNoSelection { get; set; } = true;
        public bool IncludeGapBars { get; set; } = true;
        public string CsvDelimiter { get; set; } = ",";
        public string? FileNamePrefix { get; set; }
        public string? NavigationName { get; set; }
        public bool OverwriteFile { get; set; } = true;
        public bool XlsxUseFormatting { get; set; } = true;
    }

    private enum ExportCellAlignment
    {
        Left = 0,
        Center = 1,
        Right = 2
    }

    private sealed class ExportSnapshot
    {
        public required List<ExportColumn> Columns { get; init; }
        public required List<ExportBarRow> Rows { get; init; }
    }

    private sealed class ExportColumn
    {
        public required string Key { get; init; }
        public required string Title { get; init; }
        public required ExportCellAlignment Alignment { get; init; }
        public required double WidthPixels { get; init; }
        public required bool IsNumeric { get; init; }
    }

    private sealed class ExportBarRow
    {
        public required TimelineGridRow Row { get; init; }
        public required GroupedTimelinePoint Segment { get; init; }
        public required int SegmentIndex { get; init; }
        public required string FunctionName { get; init; }
        public required double DurationMinutes { get; init; }
        public required double Quantity { get; init; }
        public required double? EffectivenessPercent { get; init; }
        public required double? ExpectedValue { get; init; }
    }

    private static readonly CultureInfo ExportCulture = CultureInfo.CurrentCulture;
    private static readonly NumberFormatInfo ExportNumberFormat = CreateExportNumberFormat();
    private static readonly IReadOnlyList<ExportColumn> ExportColumns = BuildExportColumns();

    public async Task<string?> Export(
        TimelineGridExportFormat format,
        string filePath,
        TimelineGridExportOptions? options = null)
    {
        try
        {
            return await ExportCore(format, filePath, options ?? new TimelineGridExportOptions());
        }
        catch (Exception ex)
        {
            Debug.WriteLine($"[TimelineGrid] Export failed: {ex}");
            return null;
        }
    }

    public async Task<string?> ExportWithDialog(
        TimelineGridExportFormat format,
        TimelineGridExportOptions? options = null)
    {
        try
        {
            var effectiveOptions = options ?? new TimelineGridExportOptions();
            var suggestedFileName = BuildAutoFileName(format, effectiveOptions);
            var filePath = await PickSavePathAsync(format, suggestedFileName);
            if (string.IsNullOrWhiteSpace(filePath))
            {
                return null;
            }

            return await ExportCore(format, filePath, effectiveOptions);
        }
        catch (Exception ex)
        {
            Debug.WriteLine($"[TimelineGrid] ExportWithDialog failed: {ex}");
            return null;
        }
    }

    public async Task<string?> ExportAutoName(
        TimelineGridExportFormat format,
        string targetFolderPath,
        TimelineGridExportOptions? options = null)
    {
        if (string.IsNullOrWhiteSpace(targetFolderPath))
        {
            return null;
        }

        try
        {
            var effectiveOptions = options ?? new TimelineGridExportOptions();
            var fileName = BuildAutoFileName(format, effectiveOptions);
            var filePath = Path.Combine(targetFolderPath, fileName);
            return await ExportCore(format, filePath, effectiveOptions);
        }
        catch (Exception ex)
        {
            Debug.WriteLine($"[TimelineGrid] ExportAutoName failed: {ex}");
            return null;
        }
    }

    public async Task<string?> ExportViewWithDialog()
    {
        try
        {
            var suggestedFileName = BuildViewAutoFileName();
            var picker = new FileSavePicker
            {
                SuggestedStartLocation = PickerLocationId.DocumentsLibrary,
                SuggestedFileName = Path.GetFileNameWithoutExtension(suggestedFileName)
            };

            picker.FileTypeChoices.Add("PNG", new List<string> { ".png" });

            var hwnd = GetPickerWindowHandle();
            if (hwnd != IntPtr.Zero)
            {
                InitializeWithWindow.Initialize(picker, hwnd);
            }

            var file = await picker.PickSaveFileAsync();
            if (file == null)
            {
                return null;
            }

            return await ExportView(file.Path);
        }
        catch (Exception ex)
        {
            Debug.WriteLine($"[TimelineGrid] ExportViewWithDialog failed: {ex}");
            return null;
        }
    }

    public async Task<string?> ExportView(string filePath)
    {
        try
        {
            if (_displayRows.Count == 0 && (Rows?.Count ?? 0) > 0)
            {
                RebuildRows();
            }

            if (_displayRows.Count == 0 || string.IsNullOrWhiteSpace(filePath))
            {
                return null;
            }

            var outputPath = NormalizeOutputPath(filePath, ".png", overwrite: true);
            if (string.IsNullOrWhiteSpace(outputPath))
            {
                return null;
            }

            var directory = Path.GetDirectoryName(outputPath);
            if (!string.IsNullOrWhiteSpace(directory))
            {
                Directory.CreateDirectory(directory);
            }

            var width = Math.Max(
                1f,
                (float)Math.Max(
                    _Canvas?.ActualWidth ?? 0d,
                    Math.Max(_Canvas?.Width ?? 0d, _CanvasHostBorder?.ActualWidth ?? 0d)));
            if (width <= 1f)
            {
                return null;
            }

            var widthPixels = Math.Max(1, (int)Math.Ceiling(width));
            if (!TryGetLayout(widthPixels, out var layout))
            {
                return null;
            }

            var device = _Canvas?.Device ?? CanvasDevice.GetSharedDevice();
            var exportedFiles = new List<string>();
            const int rowsPerFile = 18;
            var pageCount = Math.Max(1, (int)Math.Ceiling(_displayRows.Count / (double)rowsPerFile));

            var previousPanOffset = _panOffset;
            var previousSelectionMode = _selectionMode;
            var previousSelectedRowId = _selectedRowId;
            var previousSelectedRowIds = _selectedRowIds.ToArray();
            var previousSelectedSegmentKeys = _selectedSegmentKeys.ToArray();
            var previousRowSelectionAnchorIndex = _rowSelectionAnchorIndex;
            var previousHoverPointer = _hoverPointerPosition;
            try
            {
                _panOffset = 0d;
                _selectionMode = SelectionMode.None;
                _selectedRowId = null;
                _selectedRowIds.Clear();
                _selectedSegmentKeys.Clear();
                _rowSelectionAnchorIndex = null;
                _hoverPointerPosition = null;
                _projectedSegments.Clear();

                var palette = BuildPalette();
                var functionMap = BuildFunctionMap();
                var timelineRange = GetTimelineRangeFromData();
                var totalSeconds = Math.Max(1d, (timelineRange.End - timelineRange.Start).TotalSeconds);
                var fitVirtualWidth = Math.Max(1f, layout.TimelinePlotWidth);
                var timelineContext = new TimelineRenderContext(
                    timelineRange.Start,
                    timelineRange.End,
                    timelineRange.Start,
                    timelineRange.End,
                    totalSeconds,
                    fitVirtualWidth);

                for (var pageIndex = 0; pageIndex < pageCount; pageIndex++)
                {
                    var pageStartRow = pageIndex * rowsPerFile;
                    var rowsInPage = Math.Min(rowsPerFile, _displayRows.Count - pageStartRow);
                    if (rowsInPage <= 0)
                    {
                        continue;
                    }

                    var pageHeight = Math.Max(1f, (float)(HeaderHeight + (rowsInPage * RowHeight)));
                    var pageHeightPixels = Math.Max(1, (int)Math.Ceiling(pageHeight));
                    using var renderTarget = new CanvasRenderTarget(device, widthPixels, pageHeightPixels, 96f);
                    {
                        using var drawingSession = renderTarget.CreateDrawingSession();
                        _projectedSegments.Clear();

                        DrawHeader(drawingSession, layout, palette);
                        DrawRuler(drawingSession, layout, palette, timelineContext);

                        var rowsClipTop = (float)HeaderHeight;
                        var rowsClipHeight = Math.Max(0f, pageHeight - rowsClipTop);
                        if (rowsClipHeight > 0f)
                        {
                            using var rowsLayer = drawingSession.CreateLayer(1f, new Windows.Foundation.Rect(0f, rowsClipTop, widthPixels, rowsClipHeight));
                            var pageVerticalOffset = pageStartRow * RowHeight;
                            for (var rowIndex = pageStartRow; rowIndex < pageStartRow + rowsInPage; rowIndex++)
                            {
                                DrawRow(
                                    drawingSession,
                                    layout,
                                    palette,
                                    functionMap,
                                    timelineContext,
                                    _displayRows[rowIndex],
                                    rowIndex,
                                    widthPixels,
                                    pageHeightPixels,
                                    pageVerticalOffset);
                            }
                        }

                        DrawHeaderBottomLine(drawingSession, layout, palette);
                    }

                    var pagePath = pageIndex == 0
                        ? outputPath
                        : BuildPagedImagePath(outputPath, pageIndex + 1);
                    await renderTarget.SaveAsync(pagePath, CanvasBitmapFileFormat.Png);
                    exportedFiles.Add(pagePath);
                }
            }
            finally
            {
                _panOffset = previousPanOffset;
                _selectionMode = previousSelectionMode;
                _selectedRowId = previousSelectedRowId;
                _selectedRowIds.Clear();
                foreach (var rowId in previousSelectedRowIds)
                {
                    _selectedRowIds.Add(rowId);
                }
                _selectedSegmentKeys.Clear();
                foreach (var selectedSegmentKey in previousSelectedSegmentKeys)
                {
                    _selectedSegmentKeys.Add(selectedSegmentKey);
                }
                _rowSelectionAnchorIndex = previousRowSelectionAnchorIndex;
                _hoverPointerPosition = previousHoverPointer;
            }

            return exportedFiles.FirstOrDefault();
        }
        catch (Exception ex)
        {
            Debug.WriteLine($"[TimelineGrid] ExportView failed: {ex}");
            return null;
        }
    }

    private static string BuildPagedImagePath(string basePath, int partNumber)
    {
        var folder = Path.GetDirectoryName(basePath) ?? string.Empty;
        var fileName = Path.GetFileNameWithoutExtension(basePath);
        var extension = Path.GetExtension(basePath);
        return Path.Combine(folder, $"{fileName}_part_{partNumber:000}{extension}");
    }

    public void ShowExportMenu(FrameworkElement placementTarget)
    {
        if (placementTarget == null)
        {
            return;
        }

        var hasData = (_displayRows.Count > 0 || (Rows?.Count ?? 0) > 0);
        var menu = new MenuFlyout();
        menu.Items.Add(CreateExportMenuItem("Export CSV (.csv)", TimelineGridExportFormat.Csv, hasData));
        menu.Items.Add(CreateExportMenuItem("Export Excel (.xlsx)", TimelineGridExportFormat.Xlsx, hasData));
        menu.Items.Add(CreateExportMenuItem("Export Excel (.xlsx) without formatting", TimelineGridExportFormat.Xlsx, hasData, xlsxWithFormatting: false));
        menu.Items.Add(CreateExportMenuItem("Export TSV (.tsv)", TimelineGridExportFormat.Tsv, hasData));
        menu.Items.Add(CreateExportMenuItem("Export HTML (.html)", TimelineGridExportFormat.Html, hasData));
        menu.ShowAt(placementTarget, new FlyoutShowOptions
        {
            Placement = FlyoutPlacementMode.Bottom
        });
    }

    private MenuFlyoutItem CreateExportMenuItem(
        string text,
        TimelineGridExportFormat format,
        bool isEnabled,
        bool xlsxWithFormatting = true)
    {
        var item = new MenuFlyoutItem
        {
            Text = text,
            IsEnabled = isEnabled
        };

        item.Click += async (_, _) =>
        {
            var exportedPath = await ExportWithDialog(format, new TimelineGridExportOptions
            {
                IncludeHeaders = true,
                XlsxUseFormatting = xlsxWithFormatting
            });
            OpenExportedFile(exportedPath);
        };

        return item;
    }

    private static void OpenExportedFile(string? filePath)
    {
        if (string.IsNullOrWhiteSpace(filePath) || !File.Exists(filePath))
        {
            return;
        }

        try
        {
            Process.Start(new ProcessStartInfo
            {
                FileName = filePath,
                UseShellExecute = true
            });
        }
        catch (Exception ex)
        {
            Debug.WriteLine($"[TimelineGrid] Failed to open exported file: {ex}");
        }
    }

    private async Task<string?> ExportCore(
        TimelineGridExportFormat format,
        string filePath,
        TimelineGridExportOptions options)
    {
        var snapshot = CreateExportSnapshot(options);
        if (snapshot.Columns.Count == 0 || snapshot.Rows.Count == 0)
        {
            return null;
        }

        var outputPath = NormalizeOutputPath(filePath, format, options.OverwriteFile);
        if (string.IsNullOrWhiteSpace(outputPath))
        {
            return null;
        }

        var directory = Path.GetDirectoryName(outputPath);
        if (!string.IsNullOrWhiteSpace(directory))
        {
            Directory.CreateDirectory(directory);
        }

        switch (format)
        {
            case TimelineGridExportFormat.Csv:
                await WriteDelimitedAsync(outputPath, snapshot, options.IncludeHeaders, options.CsvDelimiter);
                break;
            case TimelineGridExportFormat.Tsv:
                await WriteDelimitedAsync(outputPath, snapshot, options.IncludeHeaders, "\t");
                break;
            case TimelineGridExportFormat.Html:
                await WriteHtmlAsync(outputPath, snapshot, options.IncludeHeaders);
                break;
            case TimelineGridExportFormat.Xlsx:
                await WriteXlsxAsync(outputPath, snapshot, options.IncludeHeaders, options.XlsxUseFormatting);
                break;
            default:
                return null;
        }

        return outputPath;
    }

    private ExportSnapshot CreateExportSnapshot(TimelineGridExportOptions options)
    {
        var functionMap = BuildFunctionMap();
        var sourceRows = GetRowsForExport(options);
        var exportRows = new List<ExportBarRow>();

        for (var rowIndex = 0; rowIndex < sourceRows.Count; rowIndex++)
        {
            var row = sourceRows[rowIndex];
            var groupedPoints = GetGroupedBars(row, functionMap);
            var segmentIndex = 1;

            for (var i = 0; i < groupedPoints.Count; i++)
            {
                var segment = groupedPoints[i];
                if (segment.IsGap && !options.IncludeGapBars)
                {
                    continue;
                }

                var functionName = string.Empty;
                if (!segment.IsGap && functionMap.TryGetValue(segment.FunctionId, out var definition))
                {
                    functionName = definition.Name ?? string.Empty;
                }

                if (!segment.IsGap && string.IsNullOrWhiteSpace(functionName))
                {
                    functionName = $"Function {segment.FunctionId.ToString(CultureInfo.InvariantCulture)}";
                }

                var effectivenessPercent = segment.IsGap
                    ? null
                    : CalculatePointEffectivenessPercent(segment, functionMap);

                double? expectedValue = null;
                if (!segment.IsGap && TryGetExpectedValue(segment.FunctionId, functionMap, out var expected))
                {
                    expectedValue = expected;
                }

                exportRows.Add(new ExportBarRow
                {
                    Row = row,
                    Segment = segment,
                    SegmentIndex = segmentIndex++,
                    FunctionName = functionName,
                    DurationMinutes = GetPointDurationMinutes(segment),
                    Quantity = segment.IsGap ? 0d : GetPointQuantity(segment),
                    EffectivenessPercent = effectivenessPercent,
                    ExpectedValue = expectedValue
                });
            }
        }

        return new ExportSnapshot
        {
            Columns = ExportColumns.ToList(),
            Rows = exportRows
        };
    }

    private List<TimelineGridRow> GetRowsForExport(TimelineGridExportOptions options)
    {
        var rows = _displayRows.Count > 0 ? _displayRows : GetSortedRows();
        if (options.RowScope != TimelineGridExportRowScope.SelectedRow)
        {
            return rows.ToList();
        }

        if (_selectedRowIds.Count > 0)
        {
            var selectedRows = rows
                .Where(row => !string.IsNullOrEmpty(row.Id) && _selectedRowIds.Contains(row.Id))
                .ToList();
            if (selectedRows.Count > 0)
            {
                return selectedRows;
            }
        }

        var selected = rows.FirstOrDefault(row => string.Equals(row.Id, _selectedRowId, StringComparison.Ordinal));
        if (selected != null)
        {
            return [selected];
        }

        return options.FallbackToAllRowsWhenNoSelection ? rows.ToList() : [];
    }

    private async Task WriteDelimitedAsync(string outputPath, ExportSnapshot snapshot, bool includeHeaders, string delimiter)
    {
        if (string.IsNullOrWhiteSpace(delimiter))
        {
            delimiter = ",";
        }

        var sb = new StringBuilder(capacity: Math.Max(1024, snapshot.Rows.Count * Math.Max(1, snapshot.Columns.Count) * 14));

        if (includeHeaders)
        {
            AppendDelimitedRow(sb, snapshot.Columns.Select(column => column.Title), delimiter);
        }

        foreach (var row in snapshot.Rows)
        {
            var values = snapshot.Columns.Select(column => GetExportValue(row, column));
            AppendDelimitedRow(sb, values, delimiter);
        }

        await File.WriteAllTextAsync(outputPath, sb.ToString(), new UTF8Encoding(encoderShouldEmitUTF8Identifier: true));
    }

    private static void AppendDelimitedRow(StringBuilder sb, IEnumerable<string> values, string delimiter)
    {
        var first = true;
        foreach (var value in values)
        {
            if (!first)
            {
                sb.Append(delimiter);
            }

            var normalized = delimiter == "\t" ? NormalizeTsvCell(value ?? string.Empty) : (value ?? string.Empty);
            sb.Append(EscapeDelimitedCell(normalized, delimiter));
            first = false;
        }

        sb.Append("\r\n");
    }

    private static string EscapeDelimitedCell(string value, string delimiter)
    {
        var shouldQuote =
            value.Contains('"')
            || value.Contains('\r')
            || value.Contains('\n')
            || (!string.IsNullOrEmpty(delimiter) && value.Contains(delimiter, StringComparison.Ordinal));

        if (!shouldQuote)
        {
            return value;
        }

        return "\"" + value.Replace("\"", "\"\"", StringComparison.Ordinal) + "\"";
    }

    private async Task WriteHtmlAsync(string outputPath, ExportSnapshot snapshot, bool includeHeaders)
    {
        var sb = new StringBuilder(capacity: Math.Max(2048, snapshot.Rows.Count * Math.Max(1, snapshot.Columns.Count) * 30));
        sb.AppendLine("<!DOCTYPE html>");
        sb.AppendLine("<html lang=\"en\">");
        sb.AppendLine("<head>");
        sb.AppendLine("  <meta charset=\"utf-8\" />");
        sb.AppendLine("  <meta name=\"viewport\" content=\"width=device-width, initial-scale=1\" />");
        sb.AppendLine("  <title>TimelineGrid Export</title>");
        sb.AppendLine("  <style>");
        sb.AppendLine("    body { font-family: 'Segoe UI', Arial, sans-serif; margin: 16px; color: #000000; background: #ffffff; }");
        sb.AppendLine("    h3 { margin: 0 0 12px 0; font-size: 16px; font-weight: 600; }");
        sb.AppendLine("    .table-wrap { max-height: 78vh; overflow: auto; border: 1px solid #E3E3E3; border-radius: 6px; }");
        sb.AppendLine("    table { width: 100%; border-collapse: collapse; background: #ffffff; }");
        sb.AppendLine("    th, td { border: 1px solid #E3E3E3; padding: 8px 10px; font-size: 12px; text-align: left; white-space: nowrap; }");
        sb.AppendLine("    thead th { position: sticky; top: 0; z-index: 2; background: #F9F9F9; color: #626262; font-weight: 400; border-bottom: 2px solid #FFD700; }");
        sb.AppendLine("    td.align-right { text-align: right; }");
        sb.AppendLine("    td.align-center { text-align: center; }");
        sb.AppendLine("    tbody tr:nth-child(even) td { background: #FCFCFC; }");
        sb.AppendLine("  </style>");
        sb.AppendLine("</head>");
        sb.AppendLine("<body>");
        sb.AppendLine($"  <h3>Timeline export ({DateTime.Now:yyyy-MM-dd HH:mm:ss})</h3>");
        sb.AppendLine("  <div class=\"table-wrap\">");
        sb.AppendLine("    <table>");

        if (includeHeaders)
        {
            sb.AppendLine("      <thead><tr>");
            foreach (var column in snapshot.Columns)
            {
                sb.Append("        <th>")
                  .Append(System.Net.WebUtility.HtmlEncode(column.Title))
                  .AppendLine("</th>");
            }

            sb.AppendLine("      </tr></thead>");
        }

        sb.AppendLine("      <tbody>");
        foreach (var row in snapshot.Rows)
        {
            sb.AppendLine("        <tr>");
            foreach (var column in snapshot.Columns)
            {
                var value = GetExportValue(row, column);
                var cellClass = column.Alignment switch
                {
                    ExportCellAlignment.Right => "align-right",
                    ExportCellAlignment.Center => "align-center",
                    _ => string.Empty
                };

                if (string.IsNullOrWhiteSpace(cellClass))
                {
                    sb.Append("          <td>")
                      .Append(System.Net.WebUtility.HtmlEncode(value))
                      .AppendLine("</td>");
                }
                else
                {
                    sb.Append("          <td class=\"")
                      .Append(cellClass)
                      .Append("\">")
                      .Append(System.Net.WebUtility.HtmlEncode(value))
                      .AppendLine("</td>");
                }
            }

            sb.AppendLine("        </tr>");
        }

        sb.AppendLine("      </tbody>");
        sb.AppendLine("    </table>");
        sb.AppendLine("  </div>");
        sb.AppendLine("</body>");
        sb.AppendLine("</html>");

        await File.WriteAllTextAsync(outputPath, sb.ToString(), new UTF8Encoding(encoderShouldEmitUTF8Identifier: true));
    }

    private async Task WriteXlsxAsync(string outputPath, ExportSnapshot snapshot, bool includeHeaders, bool withFormatting)
    {
        await Task.Run(() =>
        {
            using var stream = File.Create(outputPath);
            using var archive = new ZipArchive(stream, ZipArchiveMode.Create);

            var sheetXml = BuildWorksheetXml(snapshot, includeHeaders, withFormatting);

            WriteZipEntry(archive, "[Content_Types].xml", BuildContentTypesXml(withFormatting));
            WriteZipEntry(archive, "_rels/.rels", BuildRootRelationshipsXml());
            WriteZipEntry(archive, "xl/workbook.xml", BuildWorkbookXml());
            WriteZipEntry(archive, "xl/_rels/workbook.xml.rels", BuildWorkbookRelationshipsXml(withFormatting));
            if (withFormatting)
            {
                WriteZipEntry(archive, "xl/styles.xml", BuildStylesXml());
            }

            WriteZipEntry(archive, "xl/worksheets/sheet1.xml", sheetXml);
        });
    }

    private string BuildWorksheetXml(ExportSnapshot snapshot, bool includeHeaders, bool withFormatting)
    {
        var sb = new StringBuilder(capacity: Math.Max(4096, snapshot.Rows.Count * Math.Max(1, snapshot.Columns.Count) * 36));
        sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
        sb.Append("<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
        AppendWorksheetColumns(sb, snapshot.Columns);
        sb.Append("<sheetData>");

        uint? headerStyleIndex = withFormatting ? 1u : null;
        uint? dataStyleIndex = withFormatting ? 2u : null;

        var rowNumber = 1;
        if (includeHeaders)
        {
            sb.Append("<row r=\"").Append(rowNumber).Append("\">");
            for (var colIndex = 0; colIndex < snapshot.Columns.Count; colIndex++)
            {
                AppendInlineStringCellXml(sb, colIndex + 1, rowNumber, snapshot.Columns[colIndex].Title, headerStyleIndex);
            }

            sb.Append("</row>");
            rowNumber++;
        }

        foreach (var row in snapshot.Rows)
        {
            sb.Append("<row r=\"").Append(rowNumber).Append("\">");
            for (var colIndex = 0; colIndex < snapshot.Columns.Count; colIndex++)
            {
                var column = snapshot.Columns[colIndex];
                AppendWorksheetDataCellXml(
                    sb,
                    colIndex + 1,
                    rowNumber,
                    row,
                    column,
                    dataStyleIndex);
            }

            sb.Append("</row>");
            rowNumber++;
        }

        sb.Append("</sheetData>");
        sb.Append("</worksheet>");
        return sb.ToString();
    }

    private static void AppendWorksheetColumns(StringBuilder sb, IReadOnlyList<ExportColumn> columns)
    {
        sb.Append("<cols>");
        for (var i = 0; i < columns.Count; i++)
        {
            var index = i + 1;
            var excelWidth = ConvertPixelsToExcelWidth(columns[i].WidthPixels);

            sb.Append("<col min=\"")
              .Append(index)
              .Append("\" max=\"")
              .Append(index)
              .Append("\" width=\"")
              .Append(excelWidth.ToString("0.##", CultureInfo.InvariantCulture))
              .Append("\" customWidth=\"1\"/>");
        }

        sb.Append("</cols>");
    }

    private static double ConvertPixelsToExcelWidth(double pixels)
    {
        if (double.IsNaN(pixels) || double.IsInfinity(pixels) || pixels <= 0d)
        {
            pixels = 88d;
        }

        var width = (pixels - 5d) / 7d;
        width = Math.Clamp(width, 1d, 255d);
        return Math.Round(width, 2);
    }

    private void AppendWorksheetDataCellXml(
        StringBuilder sb,
        int excelColumnIndex,
        int excelRowIndex,
        ExportBarRow sourceRow,
        ExportColumn column,
        uint? styleIndex)
    {
        var reference = GetExcelCellReference(excelColumnIndex, excelRowIndex);
        var styleAttr = GetStyleAttr(styleIndex);
        var rawValue = GetRawExportValue(sourceRow, column);

        if (rawValue == null)
        {
            var textValue = GetExportValue(sourceRow, column);
            if (string.IsNullOrEmpty(textValue))
            {
                sb.Append("<c r=\"").Append(reference).Append("\"").Append(styleAttr).Append("/>");
                return;
            }

            AppendInlineStringCellXml(sb, excelColumnIndex, excelRowIndex, textValue, styleIndex);
            return;
        }

        if (rawValue is bool b)
        {
            sb.Append("<c r=\"").Append(reference).Append("\"").Append(styleAttr).Append(" t=\"b\"><v>").Append(b ? "1" : "0").Append("</v></c>");
            return;
        }

        if (TryConvertToInvariantNumber(rawValue, out var number))
        {
            sb.Append("<c r=\"").Append(reference).Append("\"").Append(styleAttr).Append("><v>").Append(number).Append("</v></c>");
            return;
        }

        AppendInlineStringCellXml(sb, excelColumnIndex, excelRowIndex, GetExportValue(sourceRow, column), styleIndex);
    }

    private static void AppendInlineStringCellXml(
        StringBuilder sb,
        int excelColumnIndex,
        int excelRowIndex,
        string text,
        uint? styleIndex)
    {
        var reference = GetExcelCellReference(excelColumnIndex, excelRowIndex);
        var escaped = EscapeXml(text);
        var needsPreserve = text.Length > 0 && (char.IsWhiteSpace(text[0]) || char.IsWhiteSpace(text[^1]));
        var styleAttr = GetStyleAttr(styleIndex);

        sb.Append("<c r=\"").Append(reference).Append("\"").Append(styleAttr).Append(" t=\"inlineStr\"><is><t");
        if (needsPreserve)
        {
            sb.Append(" xml:space=\"preserve\"");
        }

        sb.Append(">").Append(escaped).Append("</t></is></c>");
    }

    private static string GetStyleAttr(uint? styleIndex)
    {
        return styleIndex.HasValue
            ? $" s=\"{styleIndex.Value.ToString(CultureInfo.InvariantCulture)}\""
            : string.Empty;
    }

    private static string EscapeXml(string text)
    {
        return System.Security.SecurityElement.Escape(text) ?? string.Empty;
    }

    private static bool TryConvertToInvariantNumber(object value, out string number)
    {
        switch (value)
        {
            case byte b8:
                number = b8.ToString(CultureInfo.InvariantCulture);
                return true;
            case sbyte sb8:
                number = sb8.ToString(CultureInfo.InvariantCulture);
                return true;
            case short s16:
                number = s16.ToString(CultureInfo.InvariantCulture);
                return true;
            case ushort u16:
                number = u16.ToString(CultureInfo.InvariantCulture);
                return true;
            case int i32:
                number = i32.ToString(CultureInfo.InvariantCulture);
                return true;
            case uint u32:
                number = u32.ToString(CultureInfo.InvariantCulture);
                return true;
            case long i64:
                number = i64.ToString(CultureInfo.InvariantCulture);
                return true;
            case ulong u64:
                number = u64.ToString(CultureInfo.InvariantCulture);
                return true;
            case float f32:
                number = f32.ToString("0.################", CultureInfo.InvariantCulture);
                return true;
            case double f64:
                number = f64.ToString("0.################", CultureInfo.InvariantCulture);
                return true;
            case decimal dec:
                number = dec.ToString("0.################", CultureInfo.InvariantCulture);
                return true;
            default:
                number = string.Empty;
                return false;
        }
    }

    private string GetExportValue(ExportBarRow row, ExportColumn column)
    {
        return column.Key switch
        {
            "employee_id" => row.Row.Id,
            "employee" => row.Row.HeaderText,
            "employee_subtext" => row.Row.HeaderSubtext,
            "unit" => row.Row.Unit,
            "segment_type" => row.Segment.IsGap ? "Gap" : "Work",
            "segment_index" => row.SegmentIndex.ToString(CultureInfo.InvariantCulture),
            "function_id" => row.Segment.IsGap ? string.Empty : row.Segment.FunctionId.ToString(CultureInfo.InvariantCulture),
            "function_name" => row.FunctionName,
            "start_time" => row.Segment.StartTime.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture),
            "end_time" => row.Segment.EndTime.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture),
            "duration_min" => row.DurationMinutes.ToString("0.###", ExportNumberFormat),
            "avg_value" => row.Segment.IsGap ? string.Empty : row.Segment.Value.ToString("0.###", ExportNumberFormat),
            "start_value" => row.Segment.IsGap ? string.Empty : row.Segment.StartValue.ToString("0.###", ExportNumberFormat),
            "end_value" => row.Segment.IsGap ? string.Empty : row.Segment.EndValue.ToString("0.###", ExportNumberFormat),
            "quantity" => row.Segment.IsGap ? string.Empty : row.Quantity.ToString("0.###", ExportNumberFormat),
            "expected_value" => row.ExpectedValue.HasValue ? row.ExpectedValue.Value.ToString("0.###", ExportNumberFormat) : string.Empty,
            "effectiveness_percent" => row.EffectivenessPercent.HasValue ? row.EffectivenessPercent.Value.ToString("0.###", ExportNumberFormat) : string.Empty,
            "line_count" => row.Segment.Count.ToString(CultureInfo.InvariantCulture),
            _ => string.Empty
        };
    }

    private object? GetRawExportValue(ExportBarRow row, ExportColumn column)
    {
        return column.Key switch
        {
            "segment_index" => row.SegmentIndex,
            "function_id" => row.Segment.IsGap ? null : row.Segment.FunctionId,
            "duration_min" => row.DurationMinutes,
            "avg_value" => row.Segment.IsGap ? null : row.Segment.Value,
            "start_value" => row.Segment.IsGap ? null : row.Segment.StartValue,
            "end_value" => row.Segment.IsGap ? null : row.Segment.EndValue,
            "quantity" => row.Segment.IsGap ? null : row.Quantity,
            "expected_value" => row.ExpectedValue,
            "effectiveness_percent" => row.EffectivenessPercent,
            "line_count" => row.Segment.Count,
            _ => null
        };
    }

    private static IReadOnlyList<ExportColumn> BuildExportColumns()
    {
        return
        [
            new ExportColumn { Key = "employee_id", Title = "Employee ID", Alignment = ExportCellAlignment.Left, WidthPixels = 120d, IsNumeric = false },
            new ExportColumn { Key = "employee", Title = "Employee", Alignment = ExportCellAlignment.Left, WidthPixels = 160d, IsNumeric = false },
            new ExportColumn { Key = "employee_subtext", Title = "Employee Details", Alignment = ExportCellAlignment.Left, WidthPixels = 180d, IsNumeric = false },
            new ExportColumn { Key = "unit", Title = "Unit", Alignment = ExportCellAlignment.Left, WidthPixels = 80d, IsNumeric = false },
            new ExportColumn { Key = "segment_type", Title = "Segment Type", Alignment = ExportCellAlignment.Center, WidthPixels = 100d, IsNumeric = false },
            new ExportColumn { Key = "segment_index", Title = "Segment #", Alignment = ExportCellAlignment.Right, WidthPixels = 90d, IsNumeric = true },
            new ExportColumn { Key = "function_id", Title = "Function ID", Alignment = ExportCellAlignment.Right, WidthPixels = 90d, IsNumeric = true },
            new ExportColumn { Key = "function_name", Title = "Function", Alignment = ExportCellAlignment.Left, WidthPixels = 150d, IsNumeric = false },
            new ExportColumn { Key = "start_time", Title = "Start Time", Alignment = ExportCellAlignment.Left, WidthPixels = 150d, IsNumeric = false },
            new ExportColumn { Key = "end_time", Title = "End Time", Alignment = ExportCellAlignment.Left, WidthPixels = 150d, IsNumeric = false },
            new ExportColumn { Key = "duration_min", Title = "Duration (min)", Alignment = ExportCellAlignment.Right, WidthPixels = 110d, IsNumeric = true },
            new ExportColumn { Key = "avg_value", Title = "Avg Value", Alignment = ExportCellAlignment.Right, WidthPixels = 100d, IsNumeric = true },
            new ExportColumn { Key = "start_value", Title = "Start Value", Alignment = ExportCellAlignment.Right, WidthPixels = 100d, IsNumeric = true },
            new ExportColumn { Key = "end_value", Title = "End Value", Alignment = ExportCellAlignment.Right, WidthPixels = 100d, IsNumeric = true },
            new ExportColumn { Key = "quantity", Title = "Quantity", Alignment = ExportCellAlignment.Right, WidthPixels = 100d, IsNumeric = true },
            new ExportColumn { Key = "expected_value", Title = "Expected Value", Alignment = ExportCellAlignment.Right, WidthPixels = 120d, IsNumeric = true },
            new ExportColumn { Key = "effectiveness_percent", Title = "Effectiveness (%)", Alignment = ExportCellAlignment.Right, WidthPixels = 120d, IsNumeric = true },
            new ExportColumn { Key = "line_count", Title = "Line Count", Alignment = ExportCellAlignment.Right, WidthPixels = 90d, IsNumeric = true }
        ];
    }

    private static NumberFormatInfo CreateExportNumberFormat()
    {
        var nfi = (NumberFormatInfo)ExportCulture.NumberFormat.Clone();
        nfi.NumberGroupSeparator = string.Empty;
        nfi.NumberGroupSizes = [0];
        return nfi;
    }

    private static string NormalizeTsvCell(string text)
    {
        return text.Replace("\t", " ", StringComparison.Ordinal)
            .Replace("\r", " ", StringComparison.Ordinal)
            .Replace("\n", " ", StringComparison.Ordinal)
            .Trim();
    }

    private async Task<string?> PickSavePathAsync(TimelineGridExportFormat format, string suggestedFileName)
    {
        var extension = "." + GetFileExtension(format);
        var picker = new FileSavePicker
        {
            SuggestedStartLocation = PickerLocationId.DocumentsLibrary,
            SuggestedFileName = Path.GetFileNameWithoutExtension(suggestedFileName)
        };

        picker.FileTypeChoices.Add(format.ToString().ToUpperInvariant(), new List<string> { extension });

        var hwnd = GetPickerWindowHandle();
        if (hwnd != IntPtr.Zero)
        {
            InitializeWithWindow.Initialize(picker, hwnd);
        }

        var file = await picker.PickSaveFileAsync();
        if (file == null)
        {
            return null;
        }

        return EnsureExtension(file.Path, extension);
    }

    private static string? NormalizeOutputPath(string filePath, TimelineGridExportFormat format, bool overwrite)
    {
        if (string.IsNullOrWhiteSpace(filePath))
        {
            return null;
        }

        var extension = "." + GetFileExtension(format);
        return NormalizeOutputPath(filePath, extension, overwrite);
    }

    private static string? NormalizeOutputPath(string filePath, string extensionWithDot, bool overwrite)
    {
        if (string.IsNullOrWhiteSpace(filePath))
        {
            return null;
        }

        var normalized = EnsureExtension(filePath, extensionWithDot);
        if (string.IsNullOrWhiteSpace(normalized))
        {
            return null;
        }

        if (overwrite || !File.Exists(normalized))
        {
            return normalized;
        }

        var folder = Path.GetDirectoryName(normalized) ?? string.Empty;
        var fileName = Path.GetFileNameWithoutExtension(normalized);
        var ext = Path.GetExtension(normalized);

        var suffix = 1;
        string candidate;
        do
        {
            candidate = Path.Combine(folder, $"{fileName}_{suffix}{ext}");
            suffix++;
        } while (File.Exists(candidate));

        return candidate;
    }

    private string BuildAutoFileName(TimelineGridExportFormat format, TimelineGridExportOptions options)
    {
        var baseName = ResolveExportContextName(options.NavigationName, options.FileNamePrefix);
        var stamp = DateTime.Now.ToString("yyyyMMdd_HHmmss", CultureInfo.InvariantCulture);
        var ext = GetFileExtension(format);
        return $"{baseName}_{stamp}.{ext}";
    }

    private string BuildViewAutoFileName()
    {
        var baseName = ResolveExportContextName(null, "TimelineGrid_View");
        var stamp = DateTime.Now.ToString("yyyyMMdd_HHmmss", CultureInfo.InvariantCulture);
        return $"{baseName}_{stamp}.png";
    }

    private string ResolveExportContextName(string? navigationName, string? fileNamePrefix)
    {
        if (!string.IsNullOrWhiteSpace(fileNamePrefix))
        {
            return SanitizeFileName(fileNamePrefix);
        }

        if (!string.IsNullOrWhiteSpace(navigationName))
        {
            return SanitizeFileName(navigationName);
        }

        var page = FindAncestorInVisualTree<Page>(this);
        var pageName = page?.Frame?.SourcePageType?.Name ?? page?.GetType().Name;
        if (string.IsNullOrWhiteSpace(pageName))
        {
            pageName = "TimelineGrid";
        }

        if (pageName.EndsWith("Page", StringComparison.OrdinalIgnoreCase))
        {
            pageName = pageName[..^4];
        }

        return SanitizeFileName(pageName);
    }

    private static string SanitizeFileName(string fileName)
    {
        var result = fileName.Trim();
        foreach (var invalid in Path.GetInvalidFileNameChars())
        {
            result = result.Replace(invalid, '_');
        }

        while (result.Contains("  ", StringComparison.Ordinal))
        {
            result = result.Replace("  ", " ", StringComparison.Ordinal);
        }

        if (string.IsNullOrWhiteSpace(result))
        {
            return "TimelineGrid";
        }

        return result.Replace(' ', '_');
    }

    private static string EnsureExtension(string path, string extensionWithDot)
    {
        if (string.IsNullOrWhiteSpace(path))
        {
            return string.Empty;
        }

        if (string.IsNullOrWhiteSpace(Path.GetExtension(path)))
        {
            return path + extensionWithDot;
        }

        return path;
    }

    private static string GetFileExtension(TimelineGridExportFormat format)
    {
        return format switch
        {
            TimelineGridExportFormat.Csv => "csv",
            TimelineGridExportFormat.Xlsx => "xlsx",
            TimelineGridExportFormat.Tsv => "tsv",
            TimelineGridExportFormat.Html => "html",
            _ => "txt"
        };
    }

    private static T? FindAncestorInVisualTree<T>(DependencyObject? start) where T : DependencyObject
    {
        var current = start;
        while (current != null)
        {
            if (current is T target)
            {
                return target;
            }

            current = VisualTreeHelper.GetParent(current);
        }

        return null;
    }

    private static IntPtr GetPickerWindowHandle()
    {
        try
        {
            var hwnd = GetActiveWindow();
            if (hwnd != IntPtr.Zero)
            {
                return hwnd;
            }

            return GetForegroundWindow();
        }
        catch
        {
            return IntPtr.Zero;
        }
    }

    private static string GetExcelCellReference(int columnIndex, int rowIndex)
    {
        return GetExcelColumnName(columnIndex) + rowIndex.ToString(CultureInfo.InvariantCulture);
    }

    private static string GetExcelColumnName(int columnIndex)
    {
        if (columnIndex <= 0)
        {
            return "A";
        }

        var sb = new StringBuilder();
        var n = columnIndex;
        while (n > 0)
        {
            n--;
            sb.Insert(0, (char)('A' + (n % 26)));
            n /= 26;
        }

        return sb.ToString();
    }

    private static string BuildContentTypesXml(bool withFormatting)
    {
        var sb = new StringBuilder();
        sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
        sb.Append("<Types xmlns=\"http://schemas.openxmlformats.org/package/2006/content-types\">");
        sb.Append("<Default Extension=\"rels\" ContentType=\"application/vnd.openxmlformats-package.relationships+xml\"/>");
        sb.Append("<Default Extension=\"xml\" ContentType=\"application/xml\"/>");
        sb.Append("<Override PartName=\"/xl/workbook.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml\"/>");
        sb.Append("<Override PartName=\"/xl/worksheets/sheet1.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml\"/>");
        if (withFormatting)
        {
            sb.Append("<Override PartName=\"/xl/styles.xml\" ContentType=\"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml\"/>");
        }

        sb.Append("</Types>");
        return sb.ToString();
    }

    private static string BuildRootRelationshipsXml()
    {
        return
            "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" +
            "<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">" +
            "<Relationship Id=\"rId1\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument\" Target=\"xl/workbook.xml\"/>" +
            "</Relationships>";
    }

    private static string BuildWorkbookXml()
    {
        return
            "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" +
            "<workbook xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" " +
            "xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\">" +
            "<sheets><sheet name=\"Timeline\" sheetId=\"1\" r:id=\"rId1\"/></sheets>" +
            "</workbook>";
    }

    private static string BuildWorkbookRelationshipsXml(bool withFormatting)
    {
        var sb = new StringBuilder();
        sb.Append("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>");
        sb.Append("<Relationships xmlns=\"http://schemas.openxmlformats.org/package/2006/relationships\">");
        sb.Append("<Relationship Id=\"rId1\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet\" Target=\"worksheets/sheet1.xml\"/>");
        if (withFormatting)
        {
            sb.Append("<Relationship Id=\"rId2\" Type=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles\" Target=\"styles.xml\"/>");
        }

        sb.Append("</Relationships>");
        return sb.ToString();
    }

    private static string BuildStylesXml()
    {
        return
            "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>" +
            "<styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" +
            "<fonts count=\"2\">" +
            "<font><sz val=\"11\"/><color rgb=\"FF000000\"/><name val=\"Segoe UI\"/><family val=\"2\"/></font>" +
            "<font><sz val=\"11\"/><color rgb=\"FF626262\"/><name val=\"Segoe UI\"/><family val=\"2\"/></font>" +
            "</fonts>" +
            "<fills count=\"3\">" +
            "<fill><patternFill patternType=\"none\"/></fill>" +
            "<fill><patternFill patternType=\"gray125\"/></fill>" +
            "<fill><patternFill patternType=\"solid\"><fgColor rgb=\"FFF9F9F9\"/><bgColor indexed=\"64\"/></patternFill></fill>" +
            "</fills>" +
            "<borders count=\"2\">" +
            "<border><left/><right/><top/><bottom/><diagonal/></border>" +
            "<border><left style=\"thin\"><color rgb=\"FFE3E3E3\"/></left><right style=\"thin\"><color rgb=\"FFE3E3E3\"/></right><top style=\"thin\"><color rgb=\"FFE3E3E3\"/></top><bottom style=\"thin\"><color rgb=\"FFE3E3E3\"/></bottom><diagonal/></border>" +
            "</borders>" +
            "<cellStyleXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\"/></cellStyleXfs>" +
            "<cellXfs count=\"3\">" +
            "<xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"0\" xfId=\"0\"/>" +
            "<xf numFmtId=\"0\" fontId=\"1\" fillId=\"2\" borderId=\"1\" xfId=\"0\" applyFont=\"1\" applyFill=\"1\" applyBorder=\"1\" applyAlignment=\"1\"><alignment horizontal=\"left\" vertical=\"center\"/></xf>" +
            "<xf numFmtId=\"0\" fontId=\"0\" fillId=\"0\" borderId=\"1\" xfId=\"0\" applyBorder=\"1\"/>" +
            "</cellXfs>" +
            "<cellStyles count=\"1\"><cellStyle name=\"Normal\" xfId=\"0\" builtinId=\"0\"/></cellStyles>" +
            "</styleSheet>";
    }

    private static void WriteZipEntry(ZipArchive archive, string entryPath, string content)
    {
        var entry = archive.CreateEntry(entryPath, CompressionLevel.Fastest);
        using var stream = entry.Open();
        using var writer = new StreamWriter(stream, new UTF8Encoding(encoderShouldEmitUTF8Identifier: false));
        writer.Write(content);
    }

    [DllImport("user32.dll")]
    private static extern IntPtr GetActiveWindow();

    [DllImport("user32.dll")]
    private static extern IntPtr GetForegroundWindow();
}