using Autodesk.Revit.DB;
using Autodesk.Revit.UI;
using OfficeOpenXml;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Windows.Forms;

// Alias to avoid ambiguity with View
using RevitView = Autodesk.Revit.DB.View;

public enum SectionType
{
    Header,
    Body
}

internal class ScheduleExporter
{
    private readonly ViewSchedule viewSchedule;

    public ScheduleExporter(ViewSchedule input)
    {
        viewSchedule = input;
    }

    /// <summary>
    /// Exports the schedule data to a worksheet in the provided Excel package.
    /// </summary>
    public void ExportToExcel(ExcelPackage excelPackage)
    {
        var scheduleData = new
        {
            viewSchedule.Name,
            Header = GetSectionData(SectionType.Header),
            Body = GetSectionData(SectionType.Body)
        };

        // Create a worksheet for the schedule
        ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add(viewSchedule.Name);

        int row = 1;

        // Add the header
        if (scheduleData.Header != null)
        {
            for (int col = 0; col < scheduleData.Header.Count; col++)
            {
                worksheet.Cells[row, col + 1].Value = string.Join(", ", scheduleData.Header[col]);
            }
            row++;
        }

        // Add the body
        if (scheduleData.Body != null)
        {
            foreach (var bodyRow in scheduleData.Body)
            {
                for (int col = 0; col < bodyRow.Count; col++)
                {
                    worksheet.Cells[row, col + 1].Value = bodyRow[col];
                }
                row++;
            }
        }
    }

    /// <summary>
    /// Retrieves data for a specific section of the schedule.
    /// </summary>
    private List<List<string>> GetSectionData(SectionType sectionType)
    {
        var data = new List<List<string>>();
        TableSectionData sectionData = viewSchedule.GetTableData().GetSectionData((int)sectionType);

        int numberOfRows = sectionData.NumberOfRows;
        int numberOfColumns = sectionData.NumberOfColumns;

        for (int iRow = sectionData.FirstRowNumber; iRow < numberOfRows; iRow++)
        {
            var rowData = new List<string>();
            for (int iCol = sectionData.FirstColumnNumber; iCol < numberOfColumns; iCol++)
            {
                var cellValue = viewSchedule.GetCellText((Autodesk.Revit.DB.SectionType)sectionType, iRow, iCol);
                if (!string.IsNullOrEmpty(cellValue))
                {
                    rowData.Add(cellValue);
                }
            }
            data.Add(rowData);
        }

        return data;
    }
}

[Autodesk.Revit.Attributes.Transaction(Autodesk.Revit.Attributes.TransactionMode.Manual)]
public class ExportAllSchedulesCommand : IExternalCommand
{
    public Result Execute(
        ExternalCommandData commandData,
        ref string message,
        ElementSet elements)
    {
        UIApplication uiapp = commandData.Application;
        UIDocument uidoc = uiapp.ActiveUIDocument;
        Document doc = uidoc.Document;

        // Retrieve all ViewSchedules in the document
        List<ViewSchedule> schedules = new FilteredElementCollector(doc)
            .OfClass(typeof(ViewSchedule))
            .Cast<ViewSchedule>()
            .ToList();

        if (schedules.Count == 0)
        {
            message = "No schedules were found in the document.";
            return Result.Cancelled;
        }

        // Show save file dialog to select output file path
        SaveFileDialog saveFileDialog = new SaveFileDialog
        {
            Title = "Save Excel File",
            Filter = "Excel Files (*.xlsx;*.xlsm)|*.xlsx;*.xlsm",
            FileName = ReplaceInvalidCharacters(doc.Title) + "_Schedules.xlsm",
            OverwritePrompt = false
        };

        if (saveFileDialog.ShowDialog() != DialogResult.OK)
        {
            message = "The operation was cancelled by the user.";
            return Result.Cancelled;
        }

        string excelFilePath = saveFileDialog.FileName;

        try
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            // If the file exists, load it; otherwise, create a new file
            using (ExcelPackage excelPackage = File.Exists(excelFilePath)
                ? new ExcelPackage(new FileInfo(excelFilePath))
                : new ExcelPackage())
            {
                foreach (var schedule in schedules)
                {
                    ScheduleExporter exporter = new ScheduleExporter(schedule);
                    exporter.ExportToExcel(excelPackage);
                }

                // Save the Excel file
                FileInfo excelFile = new FileInfo(excelFilePath);
                excelPackage.SaveAs(excelFile);
            }

            // Open the Excel file automatically
            ProcessStartInfo processStartInfo = new ProcessStartInfo
            {
                FileName = excelFilePath,
                UseShellExecute = true
            };
            Process.Start(processStartInfo);

            MessageBox.Show($"All schedules have been exported to: {excelFilePath}");
            return Result.Succeeded;
        }
        catch (Exception ex)
        {
            MessageBox.Show($"An error occurred: {ex.Message}");
            return Result.Failed;
        }
    }

    /// <summary>
    /// Replaces invalid characters in file names with underscores.
    /// </summary>
    private static string ReplaceInvalidCharacters(string input)
    {
        char[] invalidChars = Path.GetInvalidFileNameChars();
        foreach (char ch in invalidChars)
        {
            input = input.Replace(ch, '_');
        }
        return input;
    }
}