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

namespace Sbroenne.ExcelMcp.Core.Commands;

/// <summary>
/// Data Model relationships - link tables for cross-table DAX calculations.
///
/// CRITICAL: Deleting or recreating tables removes ALL their relationships.
/// Use list-relationships before table operations to backup,
/// then recreate relationships after schema changes.
///
/// RELATIONSHIP REQUIREMENTS:
/// - Both tables must exist in the Data Model first
/// - Columns must have compatible data types
/// - fromTable/fromColumn = many-side (detail table, foreign key)
/// - toTable/toColumn = one-side (lookup table, primary key)
///
/// ACTIVE VS INACTIVE:
/// - Only ONE active relationship can exist between two tables
/// - Use active=false when creating alternative paths
/// - DAX USERELATIONSHIP() activates inactive relationships
/// </summary>
[ServiceCategory("datamodelrel", "DataModelRel")]
[McpTool("datamodel_relationship", Title = "Data Model Relationship Operations", Destructive = true, Category = "analysis",
    Description = "Data Model relationships - link tables for cross-table DAX calculations. CRITICAL: Deleting/recreating tables removes ALL their relationships. Use list before table operations to backup. REQUIREMENTS: Both tables in Data Model, compatible column types. From=many-side (detail), To=one-side (lookup). ACTIVE VS INACTIVE: One active relationship per table pair. Use DAX USERELATIONSHIP() for inactive. TIMEOUT: 2 min. Use datamodel for tables and DAX measures.")]
public interface IDataModelRelCommands
{
    /// <summary>
    /// Lists all table relationships in the model
    /// </summary>
    /// <param name="batch">Excel batch context for accessing workbook</param>
    /// <returns>Result containing list of relationships</returns>
    [ServiceAction("list-relationships")]
    DataModelRelationshipListResult ListRelationships(IExcelBatch batch);

    /// <summary>
    /// Gets a specific relationship by its table/column identifiers
    /// </summary>
    /// <param name="batch">Excel batch context for accessing workbook</param>
    /// <param name="fromTable">Source table name</param>
    /// <param name="fromColumn">Source column name</param>
    /// <param name="toTable">Target table name</param>
    /// <param name="toColumn">Target column name</param>
    /// <returns>Result containing relationship details</returns>
    [ServiceAction("read-relationship")]
    DataModelRelationshipViewResult ReadRelationship(
        IExcelBatch batch,
        [RequiredParameter] string fromTable,
        [RequiredParameter] string fromColumn,
        [RequiredParameter] string toTable,
        [RequiredParameter] string toColumn);

    /// <summary>
    /// Creates a new relationship between two tables in the Data Model.
    /// Uses Excel COM API: ModelRelationships.Add method (Office 2016+)
    /// </summary>
    /// <param name="batch">Excel batch context for accessing workbook</param>
    /// <param name="fromTable">Source table name</param>
    /// <param name="fromColumn">Source column name</param>
    /// <param name="toTable">Target table name</param>
    /// <param name="toColumn">Target column name</param>
    /// <param name="active">Whether the relationship should be active (default: true)</param>
    /// <exception cref="ArgumentException">Thrown when parameters are invalid</exception>
    /// <exception cref="InvalidOperationException">Thrown when tables/columns not found or creation fails</exception>
    [ServiceAction("create-relationship")]
    OperationResult CreateRelationship(
        IExcelBatch batch,
        [RequiredParameter] string fromTable,
        [RequiredParameter] string fromColumn,
        [RequiredParameter] string toTable,
        [RequiredParameter] string toColumn,
        bool active = true);

    /// <summary>
    /// Updates an existing relationship's active state in the Data Model.
    /// Uses Excel COM API: ModelRelationship.Active property (Read/Write)
    /// </summary>
    /// <param name="batch">Excel batch context for accessing workbook</param>
    /// <param name="fromTable">Source table name</param>
    /// <param name="fromColumn">Source column name</param>
    /// <param name="toTable">Target table name</param>
    /// <param name="toColumn">Target column name</param>
    /// <param name="active">New active state for the relationship</param>
    /// <exception cref="ArgumentException">Thrown when parameters are invalid</exception>
    /// <exception cref="InvalidOperationException">Thrown when relationship not found or update fails</exception>
    [ServiceAction("update-relationship")]
    OperationResult UpdateRelationship(
        IExcelBatch batch,
        [RequiredParameter] string fromTable,
        [RequiredParameter] string fromColumn,
        [RequiredParameter] string toTable,
        [RequiredParameter] string toColumn,
        [RequiredParameter] bool active);

    /// <summary>
    /// Deletes a relationship from the Data Model
    /// </summary>
    /// <param name="batch">Excel batch context for accessing workbook</param>
    /// <param name="fromTable">Source table name</param>
    /// <param name="fromColumn">Source column name</param>
    /// <param name="toTable">Target table name</param>
    /// <param name="toColumn">Target column name</param>
    /// <exception cref="ArgumentException">Thrown when parameters are invalid</exception>
    /// <exception cref="InvalidOperationException">Thrown when relationship not found or deletion fails</exception>
    [ServiceAction("delete-relationship")]
    OperationResult DeleteRelationship(
        IExcelBatch batch,
        [RequiredParameter] string fromTable,
        [RequiredParameter] string fromColumn,
        [RequiredParameter] string toTable,
        [RequiredParameter] string toColumn);
}
