using IronSoftware.Drawing;
using IronXL;
using IronXL.DataValidations;
using IronXL.Options;
using System;
using System.Collections.Generic;
using System.Data;

// We are using your original namespace and class structure
namespace ConsoleApp2
{ 
    class Program
    {
        static void Main()
        {


            // Set your license key here if you have one
            // IronXL.License.LicenseKey = "YOUR-LICENSE-KEY";
            var workBook = WorkBook.Create(ExcelFileFormat.XLSX);

            var sheet = workBook.CreateWorkSheet("Validation Rule Example using");
            // Apply rules from row 2 down to row 500
            var validationRangeStart = "2";
            var validationRangeFinish = "500";

            // --- 1. Define All Column Headers ---
            sheet["A1"].Value = "String List Test";
            sheet["B1"].Value = "Formula List Test";
            sheet["A1:B1"].Style.Font.Bold = true;


            // 256
            string[] arrayString = new string[]
                                                {
                                                    "SKU-A01-R", "SKU-A02-R", "SKU-A03-R", "SKU-A04-R", "SKU-A05-R",
                                                    "SKU-B01-G", "SKU-B02-G", "SKU-B03-G", "SKU-B04-G", "SKU-B05-G",
                                                    "SKU-C01-B", "SKU-C02-B", "SKU-C03-B", "SKU-C04-B", "SKU-C05-B",
                                                    "SKU-D01-Y", "SKU-D02-Y", "SKU-D03-Y", "SKU-D04-Y", "SKU-D05-Y",
                                                    "END-001", "END-002", "END-003", "END-004", "END-005", "END-010", "END-001"
                                                }; // total char with seperator 257 (230 char + 26 sperator [,] )

            // no problem when write to a file
            var skuValidation = sheet.DataValidations.AddStringListRule(sheet[$"A{validationRangeStart}:A{validationRangeFinish}"].RangeAddress, arrayString);

            // set error box
            skuValidation.ShowErrorBox = true;
            skuValidation.ErrorBoxTitle = "Invalid SKU";
            skuValidation.ErrorBoxText = "Please select a SKU from the list.";
            skuValidation.ErrorAlert = ErrorAlert.Stop;

            //save file with name
            //var fileName = "CategoryProductCatalog.xlsx";
            //workBook.SaveAs(fileName);

            var hiddenSheet = workBook.CreateWorkSheet("hidden");

            // 700 char
            string[] arrayString2 = new string[] { 
                                                    // 6 * 100 + 1 + 99 (,)
                                                    "IRON01","IRON02","IRON03","IRON04","IRON05","IRON06","IRON07","IRON08","IRON09","IRON10",
                                                    "IRON11","IRON12","IRON13","IRON14","IRON15","IRON16","IRON17","IRON18","IRON19","IRON20",
                                                    "IRON21","IRON22","IRON23","IRON24","IRON25","IRON26","IRON27","IRON28","IRON29","IRON30",
                                                    "IRON31","IRON32","IRON33","IRON34","IRON35","IRON36","IRON37","IRON38","IRON39","IRON40",
                                                    "IRON41","IRON42","IRON43","IRON44","IRON45","IRON46","IRON47","IRON48","IRON49","IRON50",
                                                    "IRON51","IRON52","IRON53","IRON54","IRON55","IRON56","IRON57","IRON58","IRON59","IRON60",
                                                    "IRON61","IRON62","IRON63","IRON64","IRON65","IRON66","IRON67","IRON68","IRON69","IRON70",
                                                    "IRON71","IRON72","IRON73","IRON74","IRON75","IRON76","IRON77","IRON78","IRON79","IRON80",
                                                    "IRON81","IRON82","IRON83","IRON84","IRON85","IRON86","IRON87","IRON88","IRON89","IRON90",
                                                    "IRON91","IRON92","IRON93","IRON94","IRON95","IRON96","IRON97","IRON98","IRON99","IRON100"
                                                };


            //Write the contents of your array to column A of the data sheet.
            for (int i = 0; i < arrayString2.Length; i++)
            {
                // Note: IronXL row and cell indices are 1-based.
                hiddenSheet[$"A{i + 1}"].Value = arrayString2[i];
            }

            // Create the formula string that points to our data range.
            // This will look like: 'ValidationData'!$A$1:$A$100

            string formula = $"'{hiddenSheet.Name}'!$A$1:$A${arrayString2.Length}";
            Console.WriteLine($"Generated formula for validation: {formula}");

            var sku2Validation = sheet.DataValidations.AddFormulaListRule(sheet[$"B{validationRangeStart}:B{validationRangeFinish}"], formula);

            sku2Validation.ShowErrorBox = true;
            sku2Validation.ErrorBoxTitle = "Invalid SKU2";
            sku2Validation.ErrorBoxText = "Please select a SKU from the list.";
            sku2Validation.ErrorAlert = ErrorAlert.Stop;

            // option: make it hidden
            hiddenSheet.Hidden = HiddenState.Hidden;

            // --- Final Touches ---
            sheet.AutoSizeColumn(0, true);
            sheet.AutoSizeColumn(1, true);

            var fileName = "validationRuleExample.xlsx";
            workBook.SaveAs(fileName);
            
            
            
            Console.WriteLine($"Successfully created '{fileName}' with 2 validation rules example.");
        }

    }

    /// <summary>
    /// Helper extension methods to reduce repetitive code for configuring validations.
    /// </summary>
    //    public static class ValidationExtensions
    //    {
    //        public static void ConfigureError(this DataValidation validation, string title, string text, ErrorAlert style)
    //        {
    //            validation.ShowErrorBox = true;
    //            validation.ErrorBoxTitle = title;
    //            validation.ErrorBoxText = text;
    //            validation.ErrorAlert = style;
    //        }

    //        public static void ConfigureAsDropDownList(this DataValidation validation, string title, string text, ErrorAlert style)
    //        {
    //            validation.ShowDropDownList = true;
    //            validation.ConfigureError(title, text, style);
    //        }
    //    }
}
