using Sbroenne.ExcelMcp.ComInterop.Session;
using Sbroenne.ExcelMcp.Core.Attributes;
using Sbroenne.ExcelMcp.Core.Models;

namespace Sbroenne.ExcelMcp.Core.Commands.Table;

/// <summary>
/// Excel Tables (ListObjects) - lifecycle and data operations.
/// Tables provide structured references, automatic formatting, and Data Model integration.
///
/// BEST PRACTICE: Use 'list' to check existing tables before creating.
/// Prefer 'append'/'resize'/'rename' over delete+recreate to preserve references.
///
/// WARNING: Deleting tables used as PivotTable sources or in Data Model relationships will break those objects.
///
/// DATA MODEL WORKFLOW: To analyze worksheet data with DAX/Power Pivot:
/// 1. Create or identify an Excel Table on a worksheet
/// 2. Use 'add-to-datamodel' to add the table to Power Pivot
/// 3. Then use datamodel to create DAX measures on it
///
/// DAX-BACKED TABLES: Create tables populated by DAX EVALUATE queries:
/// - 'create-from-dax': Create a new table backed by a DAX query (e.g., SUMMARIZE, FILTER)
/// - 'update-dax': Update the DAX query for an existing DAX-backed table
/// - 'get-dax': Get the DAX query info for a table (check if it's DAX-backed)
///
/// Related: tablecolumn (filter/sort/columns), datamodel (DAX measures, evaluate queries)
/// </summary>
[ServiceCategory("table", "Table")]
[McpTool("table", Title = "Table Operations", Destructive = true, Category = "data",
    Description = "Excel Tables (ListObjects) - lifecycle and data operations. CONVERT TO TABLE: When user asks to 'format as table', 'create a table', 'put data in an Excel Table', or 'use a table' — use table(action: 'create') on the data range. Excel Tables provide built-in alternating row colors, automatic filter arrows, structured references, and automatic expansion. WORKFLOW: write data to range first, then table(action: 'create') to convert. STYLING: Pass tableStyle on create (TableStyleLight1-21, TableStyleMedium1-28, TableStyleDark1-11) or use set-style action later — these are the ONLY ways to style a table. Never apply range_format to table header or data rows — it conflicts with the table style system. BEST PRACTICE: List before creating, prefer append/resize/rename over delete+recreate. WARNING: Deleting tables used as PivotTable sources or in Data Model breaks those objects. DATA MODEL: add-to-datamodel to load into Power Pivot, then datamodel for DAX measures. APPEND: rows (inline JSON 2D array) or rowsFile (.json/.csv). Use table_column for filtering/sorting/columns.")]
public interface ITableCommands
{
    /// <summary>
    /// Lists all Excel Tables in the workbook
    /// </summary>
    [ServiceAction("list")]
    TableListResult List(IExcelBatch batch);

    /// <summary>
    /// Creates a new Excel Table from a range
    /// </summary>
    /// <param name="sheetName">Name of the worksheet to create the table on</param>
    /// <param name="tableName">Name for the new table (must be unique in workbook)</param>
    /// <param name="rangeAddress">Cell range address for the table (e.g., 'A1:D10')</param>
    /// <param name="hasHeaders">True if first row contains column headers (default: true)</param>
    /// <param name="tableStyle">Table style name (e.g., 'TableStyleMedium2', 'TableStyleLight1'). Optional.</param>
    /// <exception cref="InvalidOperationException">Sheet not found, table name already exists, or range invalid</exception>
    [ServiceAction("create")]
    OperationResult Create(IExcelBatch batch, string sheetName, string tableName, string rangeAddress, bool hasHeaders = true, string? tableStyle = null);

    /// <summary>
    /// Renames an Excel Table
    /// </summary>
    /// <param name="tableName">Current name of the table</param>
    /// <param name="newName">New name for the table (must be unique in workbook)</param>
    /// <exception cref="InvalidOperationException">Table not found or new name already exists</exception>
    [ServiceAction("rename")]
    OperationResult Rename(IExcelBatch batch, string tableName, string newName);

    /// <summary>
    /// Deletes an Excel Table (converts back to range)
    /// </summary>
    /// <param name="tableName">Name of the table to delete</param>
    /// <exception cref="InvalidOperationException">Table not found</exception>
    [ServiceAction("delete")]
    OperationResult Delete(IExcelBatch batch, string tableName);

    /// <summary>
    /// Gets detailed information about an Excel Table
    /// </summary>
    /// <param name="tableName">Name of the table</param>
    [ServiceAction("read")]
    TableInfoResult Read(IExcelBatch batch, string tableName);

    /// <summary>
    /// Resizes an Excel Table to a new range
    /// </summary>
    /// <param name="tableName">Name of the table to resize</param>
    /// <param name="newRange">New range address (e.g., 'A1:F20')</param>
    /// <exception cref="InvalidOperationException">Table not found or new range invalid</exception>
    [ServiceAction("resize")]
    OperationResult Resize(IExcelBatch batch, string tableName, string newRange);

    /// <summary>
    /// Toggles the totals row for an Excel Table
    /// </summary>
    /// <param name="tableName">Name of the table</param>
    /// <param name="showTotals">True to show totals row, false to hide</param>
    /// <exception cref="InvalidOperationException">Table not found</exception>
    [ServiceAction("toggle-totals")]
    OperationResult ToggleTotals(IExcelBatch batch, string tableName, bool showTotals);

    /// <summary>
    /// Sets the totals function for a specific column in an Excel Table
    /// </summary>
    /// <param name="tableName">Name of the table</param>
    /// <param name="columnName">Name of the column to set total function on</param>
    /// <param name="totalFunction">Totals function name: Sum, Count, Average, Min, Max, CountNums, StdDev, Var, None</param>
    /// <exception cref="InvalidOperationException">Table or column not found</exception>
    [ServiceAction("set-column-total")]
    OperationResult SetColumnTotal(IExcelBatch batch, string tableName, string columnName, string totalFunction);

    /// <summary>
    /// Appends rows to an Excel Table (table auto-expands).
    /// Provide EITHER rows (inline JSON 2D array) OR rowsFile (path to .json or .csv file), not both.
    /// </summary>
    /// <param name="tableName">Name of the table to append to (table auto-expands)</param>
    /// <param name="rows">2D array of row data to append - column order must match table columns. Optional if rowsFile is provided.</param>
    /// <param name="rowsFile">Path to a JSON or CSV file containing the rows to append. JSON: 2D array. CSV: rows/columns. Alternative to inline rows parameter.</param>
    /// <exception cref="InvalidOperationException">Table not found or append failed</exception>
    [ServiceAction("append")]
    OperationResult Append(IExcelBatch batch, string tableName, List<List<object?>>? rows = null, string? rowsFile = null);

    /// <summary>
    /// Retrieves data rows from a table, optionally limited to currently visible rows.
    /// </summary>
    /// <param name="batch">Excel batch session</param>
    /// <param name="tableName">Name of the table to read data from</param>
    /// <param name="visibleOnly">True to return only visible (non-filtered) rows; false for all rows (default: false)</param>
    /// <exception cref="InvalidOperationException">Table not found</exception>
    [ServiceAction("get-data")]
    TableDataResult GetData(IExcelBatch batch, string tableName, bool visibleOnly = false);

    /// <summary>
    /// Changes the style of an Excel Table
    /// </summary>
    /// <param name="tableName">Name of the table to style</param>
    /// <param name="tableStyle">Table style name (e.g., 'TableStyleMedium2', 'TableStyleLight1', 'TableStyleDark1')</param>
    /// <exception cref="InvalidOperationException">Table not found or invalid style</exception>
    [ServiceAction("set-style")]
    OperationResult SetStyle(IExcelBatch batch, string tableName, string tableStyle);

    /// <summary>
    /// Adds an Excel Table to the Power Pivot Data Model.
    /// When column names contain literal bracket characters (e.g., [ACR_CM1]), those columns cannot
    /// be referenced in DAX formulas. Use stripBracketColumnNames=true to automatically rename such
    /// columns in the source table (removing brackets) before adding to the Data Model.
    /// When stripBracketColumnNames=false (default), bracket column names are reported in BracketColumnsFound.
    /// </summary>
    /// <param name="tableName">Name of the table to add</param>
    /// <param name="stripBracketColumnNames">When true, renames source table columns that contain literal bracket characters (removes brackets) before adding to the Data Model. This modifies the Excel table column headers in the worksheet.</param>
    /// <exception cref="InvalidOperationException">Table not found or model not available</exception>
    [ServiceAction("add-to-data-model")]
    AddToDataModelResult AddToDataModel(IExcelBatch batch, string tableName, bool stripBracketColumnNames = false);

    // === DAX-BACKED TABLE OPERATIONS ===

    /// <summary>
    /// Creates a new Excel Table backed by a DAX EVALUATE query.
    /// The table will be connected to the Data Model and refresh when the model refreshes.
    /// Uses Model.CreateModelWorkbookConnection + xlCmdDAX + ListObjects.Add pattern.
    /// </summary>
    /// <param name="batch">Excel batch session</param>
    /// <param name="sheetName">Target worksheet name for the new table</param>
    /// <param name="tableName">Name for the new DAX-backed table</param>
    /// <param name="daxQuery">DAX EVALUATE query (e.g., 'EVALUATE Sales' or 'EVALUATE SUMMARIZE(...)')</param>
    /// <param name="targetCell">Target cell address for table placement (default: 'A1')</param>
    /// <exception cref="ArgumentException">Thrown when required parameters are missing</exception>
    /// <exception cref="InvalidOperationException">Sheet not found, table name exists, or no Data Model</exception>
    [ServiceAction("create-from-dax")]
    OperationResult CreateFromDax(IExcelBatch batch, string sheetName, string tableName, string daxQuery, string? targetCell = null);

    /// <summary>
    /// Updates the DAX query for an existing DAX-backed Excel Table.
    /// The table must have been created with CreateFromDax or manually connected to a DAX query.
    /// </summary>
    /// <param name="batch">Excel batch session</param>
    /// <param name="tableName">Name of the DAX-backed table to update</param>
    /// <param name="daxQuery">New DAX EVALUATE query (e.g., 'EVALUATE SUMMARIZE(...)')</param>
    /// <exception cref="ArgumentException">Thrown when required parameters are missing</exception>
    /// <exception cref="InvalidOperationException">Table not found or table is not DAX-backed</exception>
    [ServiceAction("update-dax")]
    OperationResult UpdateDax(IExcelBatch batch, string tableName, string daxQuery);

    /// <summary>
    /// Gets the DAX query and connection information for a DAX-backed Excel Table.
    /// Returns empty query info if table is not backed by a DAX query.
    /// </summary>
    /// <param name="batch">Excel batch session</param>
    /// <param name="tableName">Name of the table</param>
    /// <returns>Result containing DAX query info (if any)</returns>
    /// <exception cref="ArgumentException">Thrown when tableName is missing</exception>
    /// <exception cref="InvalidOperationException">Table not found</exception>
    [ServiceAction("get-dax")]
    TableDaxInfoResult GetDax(IExcelBatch batch, string tableName);
}
