using Microsoft.Extensions.Logging;
using Sbroenne.ExcelMcp.ComInterop;
using Sbroenne.ExcelMcp.Core.Models;

namespace Sbroenne.ExcelMcp.Core.Commands.PivotTable;

/// <summary>
/// Strategy for OLAP (Online Analytical Processing) PivotTable field operations.
/// Uses CubeFields API for Data Model-based PivotTables.
///
/// CRITICAL: In OLAP PivotTables, PivotFields do not exist until the corresponding
/// CubeField is added to the PivotTable. Must call CreatePivotFields() first.
/// Reference: https://learn.microsoft.com/en-us/office/vba/api/excel.cubefield.createpivotfields
/// </summary>
public class OlapPivotTableFieldStrategy : IPivotTableFieldStrategy
{
    private static readonly char[] FieldNameSeparators = ['[', ']', '.'];
    /// <inheritdoc/>
    public bool CanHandle(dynamic pivot)
    {
        // OLAP/Data Model PivotTables have CubeFields collection
        return PivotTableHelpers.IsOlapPivotTable(pivot);
    }

    /// <inheritdoc/>
    public dynamic GetFieldForManipulation(dynamic pivot, string fieldName)
    {
        dynamic? cubeFields = null;
        dynamic? cubeField = null;
        try
        {
            cubeFields = pivot.CubeFields;

            // EXACT MATCH ONLY - no partial matching to avoid disambiguation bugs
            // The LLM knows exact field names, so partial matching only causes problems
            // (e.g., "ACR" incorrectly matching "[DisambiguationTable].[ACRTypeKey]")
            try
            {
                cubeField = cubeFields.Item(fieldName);
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                // Field not found by exact name - return null to trigger error
                cubeField = null;
            }

            if (cubeField == null)
            {
                throw new InvalidOperationException($"Field '{fieldName}' not found in OLAP PivotTable. Use the exact CubeField name (e.g., '[Measures].[ACR]' or '[TableName].[ColumnName]').");
            }

            // CreatePivotFields() initializes PivotFields for fields not yet in the PivotTable.
            // It may throw if PivotFields already exist (field already in Values area).
            // Safe to ignore error - if PivotFields exist, we're good; if they don't and this fails,
            // subsequent operations will provide a more specific error.
            // Reference: https://learn.microsoft.com/en-us/office/vba/api/excel.cubefield.createpivotfields
            try
            {
                cubeField.CreatePivotFields();
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                // PivotFields may already exist (field already added to PivotTable)
            }

            return cubeField; // Return CubeField, not PivotField
        }
        catch (Exception ex) when (cubeField == null)
        {
            throw new InvalidOperationException($"Field '{fieldName}' not found in OLAP PivotTable. Use the exact CubeField name (e.g., '[Measures].[ACR]' or '[TableName].[ColumnName]').", ex);
        }
        finally
        {
            ComUtilities.Release(ref cubeFields);
            // Note: Don't release cubeField - we're returning it
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldListResult ListFields(dynamic pivot, string workbookPath)
    {
        var fields = new List<PivotFieldInfo>();
        dynamic? cubeFields = null;

        try
        {
            cubeFields = pivot.CubeFields;
            int fieldCount = cubeFields.Count;

            for (int i = 1; i <= fieldCount; i++)
            {
                dynamic? cubeField = null;
                try
                {
                    cubeField = cubeFields.Item(i);
                    int orientation = Convert.ToInt32(cubeField.Orientation);

                    // Skip hidden fields
                    if (orientation == XlPivotFieldOrientation.xlHidden)
                        continue;

                    var fieldInfo = new PivotFieldInfo
                    {
                        Name = cubeField.Name?.ToString() ?? $"Field{i}",
                        CustomName = cubeField.Caption?.ToString() ?? "",
                        Area = (PivotFieldArea)orientation,
                        DataType = "Cube" // OLAP fields are always Cube type
                    };

                    // OLAP doesn't support AvailableValues like Regular PivotTables
                    // Values come from OLAP dimension hierarchies

                    fields.Add(fieldInfo);
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    // Skip field if COM access fails - continue with other fields
                }
                finally
                {
                    ComUtilities.Release(ref cubeField);
                }
            }

            return new PivotFieldListResult
            {
                Success = true,
                Fields = fields,
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref cubeFields);
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldResult AddRowField(dynamic pivot, string fieldName, int? position, string workbookPath)
    {
        dynamic? cubeField = null;
        try
        {
            cubeField = GetFieldForManipulation(pivot, fieldName);

            // Check if field is already placed
            int currentOrientation = Convert.ToInt32(cubeField.Orientation);
            if (currentOrientation != XlPivotFieldOrientation.xlHidden)
            {
                throw new InvalidOperationException($"Field '{fieldName}' is already placed in {PivotTableHelpers.GetAreaName(currentOrientation)} area. Remove it first.");
            }

            // CRITICAL: Set Orientation on CubeField, NOT on PivotField
            cubeField.Orientation = XlPivotFieldOrientation.xlRowField;
            if (position.HasValue)
            {
                cubeField.Position = (double)position.Value;
            }

            // NOTE: No RefreshTable() needed - orientation change takes effect immediately
            // RefreshTable() causes RPC disconnection on rapid operations (issue #426)

            if (cubeField.Orientation != XlPivotFieldOrientation.xlRowField)
            {
                throw new InvalidOperationException($"Field '{fieldName}' was not successfully added to Row area.");
            }

            return new PivotFieldResult
            {
                Success = true,
                FieldName = fieldName,
                CustomName = cubeField.Caption?.ToString() ?? fieldName,
                Area = PivotFieldArea.Row,
                Position = Convert.ToInt32(cubeField.Position),
                DataType = "Cube",
                AvailableValues = [],
                FilePath = workbookPath
            };
        }
        catch (Exception ex)
        {
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = $"Failed to add OLAP row field: {ex.Message}",
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref cubeField);
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldResult AddColumnField(dynamic pivot, string fieldName, int? position, string workbookPath)
    {
        dynamic? cubeField = null;
        try
        {
            cubeField = GetFieldForManipulation(pivot, fieldName);

            int currentOrientation = Convert.ToInt32(cubeField.Orientation);
            if (currentOrientation != XlPivotFieldOrientation.xlHidden)
            {
                throw new InvalidOperationException($"Field '{fieldName}' is already placed in {PivotTableHelpers.GetAreaName(currentOrientation)} area. Remove it first.");
            }

            cubeField.Orientation = XlPivotFieldOrientation.xlColumnField;
            if (position.HasValue)
            {
                cubeField.Position = (double)position.Value;
            }

            // NOTE: No RefreshTable() needed - orientation change takes effect immediately
            // RefreshTable() causes RPC disconnection on rapid operations (issue #426)

            if (cubeField.Orientation != XlPivotFieldOrientation.xlColumnField)
            {
                throw new InvalidOperationException($"Field '{fieldName}' was not successfully added to Column area.");
            }

            return new PivotFieldResult
            {
                Success = true,
                FieldName = fieldName,
                CustomName = cubeField.Caption?.ToString() ?? fieldName,
                Area = PivotFieldArea.Column,
                Position = Convert.ToInt32(cubeField.Position),
                DataType = "Cube",
                AvailableValues = [],
                FilePath = workbookPath
            };
        }
        catch (Exception ex)
        {
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = $"Failed to add OLAP column field: {ex.Message}",
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref cubeField);
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldResult AddValueField(dynamic pivot, string fieldName, AggregationFunction aggregationFunction, string? customName, string workbookPath)
    {
        dynamic? cubeField = null;
        dynamic? workbook = null;
        dynamic? model = null;
        dynamic? modelTables = null;
        dynamic? table = null;
        dynamic? measures = null;
        dynamic? newMeasure = null;
        dynamic? formatObject;

        try
        {
            // TWO MODES:
            // MODE 1: Add pre-existing measure (fieldName starts with [Measures]. or already exists in Data Model)
            // MODE 2: Auto-create DAX measure from column (legacy behavior)

            // Get workbook and model
            workbook = pivot.Parent.Parent; // PivotTable -> Worksheet -> Workbook
            model = workbook.Model;

            if (model == null)
            {
                throw new InvalidOperationException(
                    $"Cannot add value field '{fieldName}' to OLAP PivotTable - workbook has no Data Model");
            }

            // MODE 1: Check if this is a pre-existing measure
            if (IsExistingMeasure(model, fieldName, out string? existingMeasureName))
            {
                // Find the measure's CubeField and add it to values area
                // IMPORTANT: Use exact match to avoid disambiguation bugs (e.g., "ACR" matching "ACRTypeKey")
                dynamic? cubeFields = null;
                try
                {
                    cubeFields = pivot.CubeFields;
                    for (int i = 1; i <= cubeFields.Count; i++)
                    {
                        dynamic? cf = null;
                        try
                        {
                            cf = cubeFields.Item(i);
                            string cfName = cf.Name?.ToString() ?? "";
                            int cubeFieldType = Convert.ToInt32(cf.CubeFieldType);

                            // Only match measures (CubeFieldType=2), not hierarchies (CubeFieldType=1)
                            // This prevents "ACR" from matching "[DisambiguationTable].[ACRTypeKey]"
                            if (cubeFieldType != XlCubeFieldType.xlMeasure)
                                continue;

                            // Check for exact match: [Measures].[MeasureName]
                            string expectedCubeFieldName = $"[Measures].[{existingMeasureName}]";
                            if (cfName.Equals(expectedCubeFieldName, StringComparison.OrdinalIgnoreCase) ||
                                cfName.Equals(existingMeasureName, StringComparison.OrdinalIgnoreCase) ||
                                cfName.Equals(fieldName, StringComparison.OrdinalIgnoreCase))
                            {
                                cubeField = cf;
                                cf = null; // Transfer ownership
                                break;
                            }
                        }
                        finally
                        {
                            if (cf != null)
                                ComUtilities.Release(ref cf);
                        }
                    }
                }
                finally
                {
                    ComUtilities.Release(ref cubeFields);
                }

                if (cubeField == null)
                {
                    throw new InvalidOperationException(
                        $"Measure '{existingMeasureName}' exists in Data Model but not found in PivotTable CubeFields. Try refreshing the PivotTable.");
                }

                // Check if measure is already in values area
                int currentOrientation = Convert.ToInt32(cubeField.Orientation);
                if (currentOrientation == XlPivotFieldOrientation.xlDataField)
                {
                    return new PivotFieldResult
                    {
                        Success = true,
                        FieldName = existingMeasureName!,
                        CustomName = cubeField.Caption?.ToString() ?? existingMeasureName!,
                        Area = PivotFieldArea.Value,
                        DataType = "Cube",
                        FilePath = workbookPath
                    };
                }

                // Add to values area using AddDataField (more reliable than setting Orientation directly)
                // Setting cubeField.Orientation = xlDataField can fail with E_INVALIDARG (0x80070057)
                // for CubeFields in certain states, while AddDataField works consistently
                pivot.AddDataField(cubeField);

                return new PivotFieldResult
                {
                    Success = true,
                    FieldName = existingMeasureName!,
                    CustomName = customName ?? cubeField.Caption?.ToString() ?? existingMeasureName!,
                    Area = PivotFieldArea.Value,
                    Function = aggregationFunction,
                    DataType = "Cube",
                    FilePath = workbookPath
                };
            }

            // MODE 2: Create new measure from column (legacy auto-create behavior)
            // Find the source table and column for this field
            var tableAndColumn = FindTableAndColumn(pivot, fieldName);
            string tableName = tableAndColumn.Item1;
            string columnName = tableAndColumn.Item2;

            if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(columnName))
            {
                throw new InvalidOperationException(
                    $"Cannot determine table and column for field '{fieldName}'. " +
                    "Field must reference a Data Model table column (e.g., 'Sales' from 'SalesTable[Sales]') " +
                    "OR an existing measure (e.g., '[Measures].[Total Sales]')");
            }

            // Generate DAX formula and measure name
            string daxFormula = GenerateDaxFormula(tableName, columnName, aggregationFunction);
            string measureName = customName ?? $"{columnName} {GetFunctionName(aggregationFunction)}";

            // Find the table in the Data Model
            modelTables = model.ModelTables;
            for (int i = 1; i <= modelTables.Count; i++)
            {
                dynamic? t = null;
                try
                {
                    t = modelTables.Item(i);
                    string tName = t.Name?.ToString() ?? "";
                    if (tName.Equals(tableName, StringComparison.OrdinalIgnoreCase))
                    {
                        table = t;
                        t = null; // Transfer ownership
                        break;
                    }
                }
                finally
                {
                    if (t != null)
                        ComUtilities.Release(ref t);
                }
            }

            if (table == null)
            {
                throw new InvalidOperationException($"Table '{tableName}' not found in Data Model");
            }

            // Get ModelMeasures collection and create the measure
            measures = model.ModelMeasures;
            formatObject = GetDefaultFormatObject(model);

            newMeasure = measures.Add(
                measureName,
                table,
                daxFormula,
                formatObject,
                Type.Missing  // description
            );

            // Refresh the PivotTable connection to make the measure available in CubeFields
            pivot.RefreshTable();

            // Find the measure in CubeFields - measures appear with [Measures]. prefix
            // Use CubeFieldType to ensure we only match measures, not hierarchies
            dynamic? cubeFieldsForNewMeasure = null;
            try
            {
                cubeFieldsForNewMeasure = pivot.CubeFields;
                for (int i = 1; i <= cubeFieldsForNewMeasure.Count; i++)
                {
                    dynamic? cf = null;
                    try
                    {
                        cf = cubeFieldsForNewMeasure.Item(i);
                        string cfName = cf.Name?.ToString() ?? "";
                        int cubeFieldType = Convert.ToInt32(cf.CubeFieldType);

                        // Only match measures (CubeFieldType=2)
                        if (cubeFieldType != XlCubeFieldType.xlMeasure)
                            continue;

                        // Check for exact match: [Measures].[MeasureName]
                        string expectedCubeFieldName = $"[Measures].[{measureName}]";
                        if (cfName.Equals(expectedCubeFieldName, StringComparison.OrdinalIgnoreCase) ||
                            cfName.Equals(measureName, StringComparison.OrdinalIgnoreCase))
                        {
                            cubeField = cf;
                            cf = null; // Transfer ownership
                            break;
                        }
                    }
                    finally
                    {
                        if (cf != null)
                            ComUtilities.Release(ref cf);
                    }
                }
            }
            finally
            {
                ComUtilities.Release(ref cubeFieldsForNewMeasure);
            }

            if (cubeField == null)
            {
                throw new InvalidOperationException($"Measure '{measureName}' created but not found in PivotTable CubeFields after refresh");
            }

            // Add to values area using AddDataField (more reliable than setting Orientation directly)
            pivot.AddDataField(cubeField);

            return new PivotFieldResult
            {
                Success = true,
                FieldName = measureName,
                CustomName = customName ?? measureName,
                Area = PivotFieldArea.Value,
                Function = aggregationFunction,
                DataType = "Cube",
                FilePath = workbookPath
            };
        }
        catch (Exception ex)
        {
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = ex.Message,
                FilePath = workbookPath
            };
        }
        finally
        {
            // Don't release formatObject - it's owned by the model
            ComUtilities.Release(ref newMeasure);
            ComUtilities.Release(ref measures);
            ComUtilities.Release(ref table);
            ComUtilities.Release(ref modelTables);
            ComUtilities.Release(ref model);
            ComUtilities.Release(ref workbook);
            ComUtilities.Release(ref cubeField);
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldResult AddFilterField(dynamic pivot, string fieldName, string workbookPath)
    {
        dynamic? cubeField = null;
        try
        {
            cubeField = GetFieldForManipulation(pivot, fieldName);

            int currentOrientation = Convert.ToInt32(cubeField.Orientation);
            if (currentOrientation != XlPivotFieldOrientation.xlHidden)
            {
                throw new InvalidOperationException($"Field '{fieldName}' is already placed in {PivotTableHelpers.GetAreaName(currentOrientation)} area. Remove it first.");
            }

            cubeField.Orientation = XlPivotFieldOrientation.xlPageField;

            // NOTE: No RefreshTable() needed - orientation change takes effect immediately
            // RefreshTable() causes RPC disconnection on rapid operations (issue #426)

            if (cubeField.Orientation != XlPivotFieldOrientation.xlPageField)
            {
                throw new InvalidOperationException($"Field '{fieldName}' was not successfully added to Filter area.");
            }

            return new PivotFieldResult
            {
                Success = true,
                FieldName = fieldName,
                CustomName = cubeField.Caption?.ToString() ?? fieldName,
                Area = PivotFieldArea.Filter,
                Position = Convert.ToInt32(cubeField.Position),
                DataType = "Cube",
                AvailableValues = [],
                FilePath = workbookPath
            };
        }
        catch (Exception ex)
        {
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = $"Failed to add OLAP filter field: {ex.Message}",
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref cubeField);
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldResult RemoveField(dynamic pivot, string fieldName, string workbookPath)
    {
        dynamic? cubeField = null;
        try
        {
            cubeField = GetFieldForManipulation(pivot, fieldName);

            int currentOrientation = Convert.ToInt32(cubeField.Orientation);
            if (currentOrientation == XlPivotFieldOrientation.xlHidden)
            {
                throw new InvalidOperationException($"Field '{fieldName}' is not currently placed in any area");
            }

            cubeField.Orientation = XlPivotFieldOrientation.xlHidden;

            // NOTE: No RefreshTable() needed - orientation change takes effect immediately
            // RefreshTable() causes RPC disconnection on rapid operations (issue #426)

            return new PivotFieldResult
            {
                Success = true,
                FieldName = fieldName,
                Area = PivotFieldArea.Hidden,
                FilePath = workbookPath
            };
        }
        catch (Exception ex)
        {
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = $"Failed to remove OLAP field: {ex.Message}",
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref cubeField);
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldResult SetFieldName(dynamic pivot, string fieldName, string customName, string workbookPath)
    {
        dynamic? cubeField = null;
        try
        {
            // OLAP limitation: Cannot set Caption on CubeFields via COM
            throw new InvalidOperationException(
                $"Cannot rename OLAP field '{fieldName}' to '{customName}'. " +
                "Field names in OLAP PivotTables are derived from the Data Model definition. " +
                "To change field names: (1) Open Data Model in Excel, (2) Rename the dimension/hierarchy, (3) Refresh the PivotTable. " +
                "Reference: https://learn.microsoft.com/en-us/excel/vba/api/excel.cubefield.caption");
        }
        catch (Exception ex)
        {
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = ex.Message,
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref cubeField);
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldResult SetFieldFunction(dynamic pivot, string fieldName, AggregationFunction aggregationFunction, string workbookPath)
    {
        dynamic? workbook = null;
        dynamic? model = null;
        dynamic? measures = null;
        dynamic? measure = null;
        try
        {
            // For OLAP PivotTables, we need to update the DAX measure in the Data Model
            // Get workbook and model
            workbook = pivot.Parent.Parent;
            model = workbook.Model;

            if (model == null)
            {
                throw new InvalidOperationException(
                    $"Cannot update measure '{fieldName}' - workbook has no Data Model");
            }

            // Normalize field name - extract measure name from [Measures].[Name] format if present
            string targetMeasureName = NormalizeMeasureName(fieldName);

            // Find the measure by name
            measures = model.ModelMeasures;
            for (int i = 1; i <= measures.Count; i++)
            {
                dynamic? m = null;
                try
                {
                    m = measures.Item(i);
                    string mName = m.Name?.ToString() ?? "";
                    if (mName.Equals(targetMeasureName, StringComparison.OrdinalIgnoreCase))
                    {
                        measure = m;
                        m = null; // Transfer ownership
                        break;
                    }
                }
                finally
                {
                    if (m != null)
                        ComUtilities.Release(ref m);
                }
            }

            if (measure == null)
            {
                throw new InvalidOperationException($"Measure '{fieldName}' not found in Data Model");
            }

            // Parse the current formula to extract table and column
            string currentFormula = measure.Formula?.ToString() ?? "";
            var parsedFormula = ParseDaxFormula(currentFormula);

            if (string.IsNullOrEmpty(parsedFormula.tableName) || string.IsNullOrEmpty(parsedFormula.columnName))
            {
                throw new InvalidOperationException(
                    $"Cannot update measure '{fieldName}' - unable to parse current formula: {currentFormula}");
            }

            // Generate new DAX formula with the new aggregation function
            string newFormula = GenerateDaxFormula(parsedFormula.tableName, parsedFormula.columnName, aggregationFunction);

            // Update the measure's formula
            measure.Formula = newFormula;

            // NOTE: No RefreshTable() needed - formula change takes effect immediately
            // RefreshTable() causes RPC disconnection on rapid operations (issue #426)

            return new PivotFieldResult
            {
                Success = true,
                FieldName = fieldName,
                Function = aggregationFunction,
                DataType = "Cube",
                FilePath = workbookPath
            };
        }
        catch (Exception ex)
        {
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = ex.Message,
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref measure);
            ComUtilities.Release(ref measures);
            ComUtilities.Release(ref model);
            ComUtilities.Release(ref workbook);
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldResult SetFieldFormat(dynamic pivot, string fieldName, string numberFormat, string workbookPath)
    {
        dynamic? cubeField = null;
        dynamic? pivotFields = null;
        dynamic? pivotField = null;
        try
        {
            // For OLAP PivotTables, find the CubeField and set NumberFormat on its PivotField
            // This works for measures in the Values area (including [Measures].[Name] format)
            cubeField = GetFieldForManipulation(pivot, fieldName);

            // Verify the field is in the Values area (only data fields can have number formats)
            int orientation = Convert.ToInt32(cubeField.Orientation);
            if (orientation != XlPivotFieldOrientation.xlDataField)
            {
                throw new InvalidOperationException(
                    $"Field '{fieldName}' is not in the Values area (Orientation={orientation}). " +
                    "Only value fields can have number formats.");
            }

            // Access the PivotFields collection to set the NumberFormat
            // OLAP CubeFields expose their formatting through PivotFields
            pivotFields = cubeField.PivotFields;
            if (pivotFields == null || pivotFields.Count == 0)
            {
                throw new InvalidOperationException(
                    $"Cannot format OLAP field '{fieldName}' - PivotFields not available. " +
                    "Ensure the field has been added to the Values area.");
            }

            // Get the first (and typically only) PivotField and set its NumberFormat
            pivotField = pivotFields.Item(1);
            pivotField.NumberFormat = numberFormat;

            // NOTE: No RefreshTable() needed - NumberFormat is a visual-only property
            // RefreshTable() would re-query the Data Model which is very slow for OLAP PivotTables

            // Read back the format to verify it was set
            string? appliedFormat = null;
            try
            {
                appliedFormat = pivotField.NumberFormat?.ToString();
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                // If we can't read it back, use what we set
                appliedFormat = numberFormat;
            }

            return new PivotFieldResult
            {
                Success = true,
                FieldName = fieldName,
                CustomName = cubeField.Caption?.ToString() ?? fieldName,
                Area = PivotFieldArea.Value,
                NumberFormat = appliedFormat,
                DataType = "Cube",
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref pivotField);
            ComUtilities.Release(ref pivotFields);
            ComUtilities.Release(ref cubeField);
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldFilterResult SetFieldFilter(dynamic pivot, string fieldName, List<string> filterValues, string workbookPath)
    {
        dynamic? cubeField = null;
        dynamic? pivotFields = null;
        dynamic? pivotField = null;
        dynamic? pivotItems = null;
        try
        {
            // OLAP limitation: Cannot set Visible property on OLAP PivotItems
            throw new InvalidOperationException(
                $"Cannot filter OLAP field '{fieldName}' via PivotItem.Visible property. " +
                "OLAP PivotItems do not support the Visible property. " +
                "To filter OLAP data: (1) Use PivotTable's built-in filter buttons in Excel, (2) Use OLAP Slicers for interactive filtering, or (3) Modify the source Data Model. " +
                "Reference: https://learn.microsoft.com/en-us/excel/vba/api/excel.pivotitem.visible");
        }
        catch (Exception ex)
        {
            return new PivotFieldFilterResult
            {
                Success = false,
                ErrorMessage = ex.Message,
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref pivotItems);
            ComUtilities.Release(ref pivotField);
            ComUtilities.Release(ref pivotFields);
            ComUtilities.Release(ref cubeField);
        }
    }
    /// <inheritdoc/>

    /// <inheritdoc/>
    public PivotFieldResult SortField(dynamic pivot, string fieldName, SortDirection direction, string workbookPath)
    {
        dynamic? cubeField = null;
        dynamic? pivotFields = null;
        dynamic? pivotField = null;
        try
        {
            cubeField = GetFieldForManipulation(pivot, fieldName);

            // OLAP sorting works through PivotField, not CubeField
            pivotFields = cubeField.PivotFields;
            if (pivotFields == null || pivotFields.Count == 0)
            {
                throw new InvalidOperationException($"Cannot sort OLAP field '{fieldName}' - PivotFields not available");
            }

            pivotField = pivotFields.Item(1);

            int sortOrder = direction == SortDirection.Ascending
                ? XlSortOrder.xlAscending
                : XlSortOrder.xlDescending;

            pivotField.AutoSort(sortOrder, fieldName);

            // NOTE: No RefreshTable() needed - Sorting is a visual-only operation

            return new PivotFieldResult
            {
                Success = true,
                FieldName = fieldName,
                CustomName = cubeField.Caption?.ToString() ?? fieldName,
                Area = (PivotFieldArea)cubeField.Orientation,
                FilePath = workbookPath
            };
        }
        catch (Exception ex)
        {
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = $"Failed to sort OLAP field: {ex.Message}",
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref pivotField);
            ComUtilities.Release(ref pivotFields);
            ComUtilities.Release(ref cubeField);
        }
    }

    /// <summary>
    /// Group a date/time field by the specified interval (Month, Quarter, Year).
    /// OLAP CubeFields automatically create date hierarchies from Data Model columns.
    /// Manual grouping via Group() is NOT supported for OLAP PivotTables.
    /// </summary>
    public PivotFieldResult GroupByDate(dynamic pivot, string fieldName, DateGroupingInterval interval, string workbookPath, ILogger? logger = null)
    {
        dynamic? cubeField = null;
        try
        {
            cubeField = GetFieldForManipulation(pivot, fieldName);

            // OLAP PivotTables do not support manual date grouping via LabelRange.Group()
            // Date hierarchies are defined in the Data Model and automatically available
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = $"Manual date grouping is not supported for OLAP PivotTables. " +
                              $"Date hierarchies must be defined in the Data Model. " +
                              $"Use Power Pivot to create date hierarchies (Year > Quarter > Month > Day) on the '{fieldName}' column.",
                FieldName = fieldName,
                FilePath = workbookPath,
                WorkflowHint = "For OLAP PivotTables: 1) Open Power Pivot, 2) Create date hierarchy on date column, " +
                               "3) Use RemoveField/AddField to place hierarchy levels in PivotTable areas."
            };
        }
        catch (Exception ex)
        {
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = $"Failed to access OLAP field '{fieldName}': {ex.Message}",
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref cubeField);
        }
    }

    /// <inheritdoc/>
    public PivotFieldResult GroupByNumeric(dynamic pivot, string fieldName, double? start, double? endValue, double intervalSize, string workbookPath, ILogger? logger = null)
    {
        dynamic? cubeField = null;
        try
        {
            cubeField = GetFieldForManipulation(pivot, fieldName);

            // OLAP PivotTables do not support manual numeric grouping via LabelRange.Group()
            // Numeric grouping must be done in the source data or Data Model
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = $"Manual numeric grouping is not supported for OLAP PivotTables. " +
                              $"Numeric grouping must be defined in the Data Model. " +
                              $"Use Power Pivot to create calculated columns with range logic on the '{fieldName}' column.",
                FieldName = fieldName,
                FilePath = workbookPath,
                WorkflowHint = "For OLAP PivotTables: 1) Open Power Pivot, 2) Create calculated column with range logic " +
                               "(e.g., IF([Sales]<100, \"0-100\", IF([Sales]<200, \"100-200\", ...))), 3) Use that calculated column in PivotTable."
            };
        }
        catch (Exception ex)
        {
            return new PivotFieldResult
            {
                Success = false,
                ErrorMessage = $"Failed to access OLAP field '{fieldName}': {ex.Message}",
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref cubeField);
        }
    }

    /// <inheritdoc/>
    public PivotFieldResult CreateCalculatedField(dynamic pivot, string fieldName, string formula, string workbookPath, ILogger? logger = null)
    {
        // CRITICAL: OLAP PivotTables do NOT support CalculatedFields collection
        // The CalculatedFields collection returns Nothing for OLAP PivotTables
        // OLAP uses CalculatedMembers with MDX/DAX formulas instead
        //
        // Reference: https://learn.microsoft.com/en-us/office/vba/api/excel.pivottable.calculatedfields
        // "For OLAP data sources, you cannot set this collection, and it always returns Nothing"
        return new PivotFieldResult
        {
            Success = false,
            FieldName = fieldName,
            Formula = formula,
            ErrorMessage = "Calculated fields are not supported for OLAP PivotTables. " +
                          "OLAP PivotTables use CalculatedMembers with MDX/DAX formulas instead. " +
                          "For Data Model PivotTables, use DAX measures via datamodel tool.",
            FilePath = workbookPath,
            WorkflowHint = "For OLAP/Data Model PivotTables: " +
                          "1) Use datamodel tool to create DAX measures with formulas, " +
                          "2) Refresh PivotTable to see new measures in field list, " +
                          "3) Add measure to Values area with AddValueField. " +
                          "Example DAX: Profit = SUM('Sales'[Revenue]) - SUM('Sales'[Cost])"
        };
    }
    /// <inheritdoc/>
    public OperationResult SetLayout(dynamic pivot, int rowLayout, string workbookPath, ILogger? logger = null)
    {
        // OLAP PivotTables support all three layout forms
        // xlCompactRow=0, xlTabularRow=1, xlOutlineRow=2
        pivot.RowAxisLayout(rowLayout);

        // NOTE: No RefreshTable() needed - Layout is a visual-only property

        if (logger?.IsEnabled(LogLevel.Information) is true)
        {
            logger.LogInformation("Set OLAP PivotTable layout to {LayoutType}", rowLayout);
        }

        return new OperationResult
        {
            Success = true,
            FilePath = workbookPath
        };
    }
    /// <inheritdoc/>
    public PivotFieldResult SetSubtotals(
        dynamic pivot,
        string fieldName,
        bool showSubtotals,
        string workbookPath,
        ILogger? logger = null)
    {
        dynamic? field = null;
        try
        {
            // Get the field - for OLAP, use PivotFields (not CubeFields)
            dynamic pivotFields = pivot.PivotFields;
            field = pivotFields.Item(fieldName);

            // OLAP PivotTables only support Automatic subtotals (index 1)
            // Other subtotal types not available for OLAP data sources
            field.Subtotals[1] = showSubtotals;

            // NOTE: No RefreshTable() needed - Subtotals is a visual-only property

            if (logger?.IsEnabled(LogLevel.Information) is true)
            {
                logger.LogInformation("Set OLAP subtotals for field {FieldName} to {ShowSubtotals}", fieldName, showSubtotals);
            }

            return new PivotFieldResult
            {
                Success = true,
                FieldName = fieldName,
                FilePath = workbookPath,
                WorkflowHint = showSubtotals
                    ? "Subtotals enabled for OLAP field. Only Automatic function supported (OLAP limitation)."
                    : "Subtotals disabled for OLAP field."
            };
        }
        catch (Exception ex)
        {
            if (logger?.IsEnabled(LogLevel.Error) is true)
            {
                logger.LogError(ex, "SetSubtotals failed for OLAP field {FieldName}", fieldName);
            }
            return new PivotFieldResult
            {
                Success = false,
                FieldName = fieldName,
                ErrorMessage = $"Failed to set OLAP subtotals: {ex.Message}",
                FilePath = workbookPath
            };
        }
        finally
        {
            ComUtilities.Release(ref field);
        }
    }
    /// <inheritdoc/>
    public OperationResult SetGrandTotals(dynamic pivot, bool showRowGrandTotals, bool showColumnGrandTotals, string workbookPath, ILogger? logger = null)
    {
        pivot.RowGrand = showRowGrandTotals;
        pivot.ColumnGrand = showColumnGrandTotals;

        // NOTE: No RefreshTable() needed - GrandTotals are visual-only properties

        if (logger is not null && logger.IsEnabled(LogLevel.Information))
        {
            logger.LogInformation("Set OLAP grand totals: Row={RowGrand}, Column={ColumnGrand}", showRowGrandTotals, showColumnGrandTotals);
        }

        return new OperationResult
        {
            Success = true,
            FilePath = workbookPath
        };
    }

    #region Helper Methods

    /// <summary>
    /// Find the source table and column for a CubeField in the Data Model.
    /// OLAP CubeFields reference Data Model columns in format: [TableName].[ColumnName]
    /// NOTE: This only searches hierarchy fields (CubeFieldType=1), not measures.
    /// </summary>
    private static (string tableName, string columnName) FindTableAndColumn(dynamic pivot, string fieldName)
    {
        dynamic? cubeFields = null;
        try
        {
            cubeFields = pivot.CubeFields;

            // Try to find the CubeField matching fieldName
            // Only look at hierarchies (table columns), not measures
            for (int i = 1; i <= cubeFields.Count; i++)
            {
                dynamic? cf = null;
                try
                {
                    cf = cubeFields.Item(i);
                    string cfName = cf.Name?.ToString() ?? "";
                    int cubeFieldType = Convert.ToInt32(cf.CubeFieldType);

                    // Skip measures - we're looking for table columns only
                    if (cubeFieldType == XlCubeFieldType.xlMeasure)
                        continue;

                    // EXACT MATCH ONLY - no partial matching
                    if (cfName.Equals(fieldName, StringComparison.OrdinalIgnoreCase))
                    {
                        // Parse hierarchical name format: [TableName].[ColumnName]
                        // Example: "[RegionalSalesTable].[Sales]" -> table="RegionalSalesTable", column="Sales"
                        if (cfName.Contains('[') && cfName.Contains(']'))
                        {
                            var parts = cfName.Split(FieldNameSeparators, StringSplitOptions.RemoveEmptyEntries);
                            if (parts.Length >= 2)
                            {
                                return (parts[0], parts[1]);
                            }
                        }

                        // Fallback: If no hierarchical format, assume fieldName is the column
                        // and try to infer table from the CubeField's SourceName property
                        try
                        {
                            string sourceName = cf.SourceName?.ToString() ?? "";
                            if (!string.IsNullOrEmpty(sourceName) && sourceName.Contains('['))
                            {
                                var sourceParts = sourceName.Split(FieldNameSeparators, StringSplitOptions.RemoveEmptyEntries);
                                if (sourceParts.Length >= 2)
                                {
                                    return (sourceParts[0], sourceParts[1]);
                                }
                            }
                        }
                        catch (System.Runtime.InteropServices.COMException)
                        {
                            // SourceName property might not be available for this CubeField type
                        }
                    }
                }
                finally
                {
                    ComUtilities.Release(ref cf);
                }
            }

            return (string.Empty, string.Empty);
        }
        finally
        {
            ComUtilities.Release(ref cubeFields);
        }
    }

    /// <summary>
    /// Generate DAX formula for a measure based on aggregation function.
    /// Examples:
    /// - SUM: SUM('TableName'[ColumnName])
    /// - COUNT: COUNT('TableName'[ColumnName])
    /// - AVERAGE: AVERAGE('TableName'[ColumnName])
    /// </summary>
    private static string GenerateDaxFormula(string tableName, string columnName, AggregationFunction function)
    {
        string daxFunction = function switch
        {
            AggregationFunction.Sum => "SUM",
            AggregationFunction.Count => "COUNT",
            AggregationFunction.Average => "AVERAGE",
            AggregationFunction.Max => "MAX",
            AggregationFunction.Min => "MIN",
            AggregationFunction.CountNumbers => "COUNT",
            AggregationFunction.StdDev => "STDEV.S",
            AggregationFunction.StdDevP => "STDEV.P",
            AggregationFunction.Var => "VAR.S",
            AggregationFunction.VarP => "VAR.P",
            _ => throw new InvalidOperationException($"Unsupported aggregation function for DAX: {function}")
        };

        // DAX syntax: FUNCTION('TableName'[ColumnName])
        return $"{daxFunction}('{tableName}'[{columnName}])";
    }

    /// <summary>
    /// Get friendly function name for measure naming.
    /// </summary>
    private static string GetFunctionName(AggregationFunction function)
    {
        return function switch
        {
            AggregationFunction.Sum => "Sum",
            AggregationFunction.Count => "Count",
            AggregationFunction.Average => "Average",
            AggregationFunction.Max => "Max",
            AggregationFunction.Min => "Min",
            AggregationFunction.CountNumbers => "Count",
            AggregationFunction.StdDev => "StdDev",
            AggregationFunction.StdDevP => "StdDevP",
            AggregationFunction.Var => "Var",
            AggregationFunction.VarP => "VarP",
            _ => function.ToString()
        };
    }

    /// <summary>
    /// Get default format object from Data Model.
    /// Returns ModelFormatGeneral for standard numeric display.
    /// </summary>
    private static dynamic GetDefaultFormatObject(dynamic model)
    {
        // Get default format - ModelFormatGeneral is always available
        dynamic formats = model.ModelFormatGeneral;
        return formats;
    }

    /// <summary>
    /// Parse DAX formula to extract table and column names.
    /// Handles formats like: SUM('TableName'[ColumnName]), COUNT('Table'[Column]), etc.
    /// </summary>
    private static (string tableName, string columnName) ParseDaxFormula(string daxFormula)
    {
        // Expected format: FUNCTION('TableName'[ColumnName])
        // Extract table name from single quotes
        int tableStart = daxFormula.IndexOf('\'');
        int tableEnd = daxFormula.IndexOf('\'', tableStart + 1);

        if (tableStart == -1 || tableEnd == -1)
        {
            return (string.Empty, string.Empty);
        }

        string tableName = daxFormula.Substring(tableStart + 1, tableEnd - tableStart - 1);

        // Extract column name from square brackets
        int columnStart = daxFormula.IndexOf('[', tableEnd);
        int columnEnd = daxFormula.IndexOf(']', columnStart + 1);

        if (columnStart == -1 || columnEnd == -1)
        {
            return (string.Empty, string.Empty);
        }

        string columnName = daxFormula.Substring(columnStart + 1, columnEnd - columnStart - 1);

        return (tableName, columnName);
    }

    /// <summary>
    /// Parse number format string and create appropriate ModelFormat object.
    /// Supports: currency, percentage, decimal, whole number, general.
    /// </summary>
    private static dynamic? GetModelFormatObject(dynamic model, string numberFormat)
    {
        // Currency formats: $#,##0.00, $#,##0, etc.
        if (numberFormat.Contains('$'))
        {
            dynamic? currencyFormat = null;
            try
            {
                currencyFormat = model.ModelFormatCurrency;

                // Parse decimal places from format string
                int decimalIndex = numberFormat.IndexOf('.');
                if (decimalIndex >= 0)
                {
                    // Count zeros after decimal point
                    int decimalPlaces = 0;
                    for (int i = decimalIndex + 1; i < numberFormat.Length && numberFormat[i] == '0'; i++)
                    {
                        decimalPlaces++;
                    }
                    currencyFormat.DecimalPlaces = decimalPlaces;
                }
                else
                {
                    currencyFormat.DecimalPlaces = 0;
                }

                currencyFormat.Symbol = "$";
                return currencyFormat;
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                if (currencyFormat != null)
                    ComUtilities.Release(ref currencyFormat);
                throw;
            }
        }

        // Percentage formats: 0.00%, 0%, etc.
        if (numberFormat.Contains('%'))
        {
            dynamic? percentFormat = null;
            try
            {
                percentFormat = model.ModelFormatPercentageNumber;

                // Parse decimal places
                int decimalIndex = numberFormat.IndexOf('.');
                if (decimalIndex >= 0)
                {
                    int decimalPlaces = 0;
                    for (int i = decimalIndex + 1; i < numberFormat.Length && numberFormat[i] == '0'; i++)
                    {
                        decimalPlaces++;
                    }
                    percentFormat.DecimalPlaces = decimalPlaces;
                }
                else
                {
                    percentFormat.DecimalPlaces = 0;
                }

                return percentFormat;
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                if (percentFormat != null)
                    ComUtilities.Release(ref percentFormat);
                throw;
            }
        }

        // Decimal number formats: 0.00, #,##0.00, etc.
        if (numberFormat.Contains('.'))
        {
            dynamic? decimalFormat = null;
            try
            {
                decimalFormat = model.ModelFormatDecimalNumber;

                // Parse decimal places
                int decimalIndex = numberFormat.IndexOf('.');
                int decimalPlaces = 0;
                for (int i = decimalIndex + 1; i < numberFormat.Length && (numberFormat[i] == '0' || numberFormat[i] == '#'); i++)
                {
                    decimalPlaces++;
                }
                decimalFormat.DecimalPlaces = decimalPlaces;

                // Check for thousand separator
                if (numberFormat.Contains(','))
                {
                    decimalFormat.UseThousandSeparator = true;
                }

                return decimalFormat;
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                if (decimalFormat != null)
                    ComUtilities.Release(ref decimalFormat);
                throw;
            }
        }

        // Whole number formats: 0, #,##0, etc.
        if (numberFormat.Contains('0') || numberFormat.Contains('#'))
        {
            dynamic? wholeFormat = null;
            try
            {
                wholeFormat = model.ModelFormatWholeNumber;

                // Check for thousand separator
                if (numberFormat.Contains(','))
                {
                    wholeFormat.UseThousandSeparator = true;
                }

                return wholeFormat;
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                if (wholeFormat != null)
                    ComUtilities.Release(ref wholeFormat);
                throw;
            }
        }

        // Default: General format
        return model.ModelFormatGeneral;
    }

    /// <summary>
    /// Modify an existing format object's properties based on the format string.
    /// The format object is already attached to a measure and we modify it in place.
    /// </summary>
    private static void ModifyFormatObject(dynamic formatObject, string numberFormat)
    {
        // Try to determine the format type and modify accordingly
        // Currency format
        if (numberFormat.Contains('$'))
        {
            try
            {
                // Parse decimal places
                int decimalIndex = numberFormat.IndexOf('.');
                if (decimalIndex >= 0)
                {
                    int decimalPlaces = 0;
                    for (int i = decimalIndex + 1; i < numberFormat.Length && numberFormat[i] == '0'; i++)
                    {
                        decimalPlaces++;
                    }
                    formatObject.DecimalPlaces = decimalPlaces;
                }
                else
                {
                    formatObject.DecimalPlaces = 0;
                }

                formatObject.Symbol = "$";
                return;
            }
            catch (Exception ex) when (ex is System.Runtime.InteropServices.COMException or Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
            {
                // Format object doesn't support currency properties - fall through to try other types
            }
        }

        // Percentage format
        if (numberFormat.Contains('%'))
        {
            try
            {
                int decimalIndex = numberFormat.IndexOf('.');
                if (decimalIndex >= 0)
                {
                    int decimalPlaces = 0;
                    for (int i = decimalIndex + 1; i < numberFormat.Length && numberFormat[i] == '0'; i++)
                    {
                        decimalPlaces++;
                    }
                    formatObject.DecimalPlaces = decimalPlaces;
                }
                else
                {
                    formatObject.DecimalPlaces = 0;
                }
                return;
            }
            catch (Exception ex) when (ex is System.Runtime.InteropServices.COMException or Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
            {
                // Format object doesn't support percentage properties - fall through
            }
        }

        // Decimal number format
        if (numberFormat.Contains('.'))
        {
            try
            {
                int decimalIndex = numberFormat.IndexOf('.');
                int decimalPlaces = 0;
                for (int i = decimalIndex + 1; i < numberFormat.Length && (numberFormat[i] == '0' || numberFormat[i] == '#'); i++)
                {
                    decimalPlaces++;
                }
                formatObject.DecimalPlaces = decimalPlaces;

                if (numberFormat.Contains(','))
                {
                    formatObject.UseThousandSeparator = true;
                }
                return;
            }
            catch (Exception ex) when (ex is System.Runtime.InteropServices.COMException or Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
            {
                // Format object doesn't support decimal properties - fall through
            }
        }

        // Whole number format
        if (numberFormat.Contains('0') || numberFormat.Contains('#'))
        {
            try
            {
                if (numberFormat.Contains(','))
                {
                    formatObject.UseThousandSeparator = true;
                }
                return;
            }
            catch (Exception ex) when (ex is System.Runtime.InteropServices.COMException or Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
            {
                // Format object doesn't support whole number properties - fall through
            }
        }

        // If we get here, it's probably ModelFormatGeneral which has no configurable properties
    }

    /// <summary>
    /// Normalize measure name by extracting it from [Measures].[Name] format if present.
    /// Returns the bare measure name (e.g., "Total Sales" from "[Measures].[Total Sales]").
    /// </summary>
    private static string NormalizeMeasureName(string fieldName)
    {
        if (fieldName.StartsWith("[Measures].", StringComparison.OrdinalIgnoreCase))
        {
            // Remove [Measures]. prefix and extract name from brackets
            var parts = fieldName.Split(FieldNameSeparators, StringSplitOptions.RemoveEmptyEntries);
            if (parts.Length >= 2)
            {
                return parts[1]; // "Total Sales" from "[Measures].[Total Sales]"
            }
        }
        return fieldName;
    }

    /// <summary>
    /// Check if fieldName refers to an existing measure in the Data Model.
    /// Returns true if the measure exists, and outputs the measure name.
    /// Handles formats: "[Measures].[Name]" or "Name" (exact match only).
    /// </summary>
    private static bool IsExistingMeasure(dynamic model, string fieldName, out string? measureName)
    {
        measureName = null;
        dynamic? measures = null;
        try
        {
            measures = model.ModelMeasures;
            if (measures == null || measures.Count == 0)
            {
                return false;
            }

            // Extract measure name from [Measures].[Name] format if present
            string searchName = NormalizeMeasureName(fieldName);

            // Search for measure by name (EXACT match only - no partial matching)
            // Partial matching causes disambiguation bugs where "ACR" could match "ACRTypeKey"
            for (int i = 1; i <= measures.Count; i++)
            {
                dynamic? measure = null;
                try
                {
                    measure = measures.Item(i);
                    string mName = measure.Name?.ToString() ?? "";

                    // Exact match only - no Contains() to avoid false positives
                    if (mName.Equals(searchName, StringComparison.OrdinalIgnoreCase) ||
                        mName.Equals(fieldName, StringComparison.OrdinalIgnoreCase))
                    {
                        measureName = mName;
                        return true;
                    }
                }
                finally
                {
                    ComUtilities.Release(ref measure);
                }
            }

            return false;
        }
        finally
        {
            ComUtilities.Release(ref measures);
        }
    }

    #endregion
}



