using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using PIEDAL.DTO.ConfigItem;

namespace PIEDAL.Query.ConfigItem;

// Required indexes:
//   ix_pie_config_item_tenant   ON pie.MPIECONFIGITEM(TENANTID)
//   ix_pie_config_item_module   ON pie.MPIECONFIGITEM(PIEMODULEID) WHERE STATUS = 1
//   ix_pie_config_item_cat      ON pie.MPIECONFIGITEM(PIEMODULEID, ITEMCATEGORY)
//   ix_pie_training_topic_tenant ON pie.MPIETRAININGTOPIC(TENANTID)
//   ix_pie_uat_scenario_tenant  ON pie.MPIEUATSCENARIO(TENANTID)
//   ix_pie_migration_object_tenant ON pie.MPIEMIGRATIONOBJECT(TENANTID)

// ── MPIECONFIGITEM list ───────────────────────────────────────────────────────

public sealed class PIEConfigItemQB : IQueryBuilder<PIEConfigItemListQuery, PIEConfigItemListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["itemcode"]       = "ci.PIECONFIGITEMCODE",
            ["itemname"]       = "ci.PIECONFIGITEMNAME",
            ["itemcategory"]   = "ci.ITEMCATEGORY",
            ["displayorder"]   = "ci.SORTORDER",
            ["estimatedhours"] = "ci.ESTIMATEDHOURS"
        };

    public (string Sql, DynamicParameters Params) Build(PIEConfigItemListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort,
            "ci.ITEMCATEGORY ASC, ci.SORTORDER ASC, ci.PIECONFIGITEMCODE ASC");
        return BuildConfigItemList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildConfigItemList(
        PIEConfigItemListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("configitem", "ci", schema: "pie");

        var tenantParams = new DynamicParameters();
        tenantParams.Add("TenantId", login.ClientId);

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("ci.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactGuid(ctx,   c.ModuleId,     "configitem", "PIEMODULEID"),
            SqlClauses.ExactString(ctx, c.ItemCategory, "configitem", "ITEMCATEGORY"),
            SqlClauses.ExactShort(ctx,  c.IsMandatory,  "configitem", "ISMANDATORY"),
            SqlClauses.ExactShort(ctx,  c.IsActive,     "configitem", "STATUS"),
            SqlClauses.CodeNameSearch(ctx, c.SearchText, "configitem", "PIECONFIGITEMCODE", "PIECONFIGITEMNAME"),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
SELECT {d.TotalCountExpr()},
       ci.PIECONFIGITEMID    AS ConfigItemId,
       ci.PIEMODULEID        AS ModuleId,
       ci.PIECONFIGITEMCODE  AS ItemCode,
       ci.PIECONFIGITEMNAME  AS ItemName,
       ci.ITEMCATEGORY       AS ItemCategory,
       ci.ISMANDATORY        AS IsMandatory,
       ci.STATUS             AS IsActive,
       ci.ESTIMATEDHOURS     AS EstimatedHours
FROM   {ctx.Table("configitem", "MPIECONFIGITEM")} ci
{whereSql}
{pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }

    // ── Config item CRUD ──────────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT ci.PIECONFIGITEMID    AS ConfigItemId,
               ci.PIEMODULEID        AS ModuleId,
               m.PIEMODULENAME       AS ModuleName,
               ci.PIECONFIGITEMCODE  AS ItemCode,
               ci.PIECONFIGITEMNAME  AS ItemName,
               ci.ITEMCATEGORY       AS ItemCategory,
               ci.description        AS Description,
               ci.ISMANDATORY        AS IsMandatory,
               ci.DEFAULTVALUE       AS DefaultValue,
               ci.ESTIMATEDHOURS     AS EstimatedHours,
               ci.SORTORDER          AS DisplayOrder,
               ci.STATUS             AS IsActive,
               ci.CMSCONTENTREF      AS CmsContentRef,
               ci.CREATEDON          AS CreatedAt,
               ci.CREATEDBYID        AS CreatedBy,
               ci.MODIFIEDON         AS UpdatedAt,
               ci.MODIFIEDBYID       AS UpdatedBy,
               ci.TENANTID           AS TenantId
        FROM   pie.MPIECONFIGITEM ci
        JOIN   pie.MPIEMODULE m ON m.PIEMODULEID = ci.PIEMODULEID AND m.TENANTID = ci.TENANTID
        WHERE  ci.PIECONFIGITEMID = @ConfigItemId
          AND  ci.TENANTID        = @TenantId";

    public const string GET_BY_MODULE = @"
        SELECT ci.PIECONFIGITEMID    AS ConfigItemId,
               ci.PIEMODULEID        AS ModuleId,
               ci.PIECONFIGITEMCODE  AS ItemCode,
               ci.PIECONFIGITEMNAME  AS ItemName,
               ci.ITEMCATEGORY       AS ItemCategory,
               ci.ISMANDATORY        AS IsMandatory,
               ci.STATUS             AS IsActive,
               ci.TENANTID           AS TenantId
        FROM   pie.MPIECONFIGITEM ci
        WHERE  ci.PIEMODULEID = @ModuleId
          AND  ci.TENANTID    = @TenantId
        ORDER BY ci.ITEMCATEGORY, ci.SORTORDER, ci.PIECONFIGITEMCODE";

    /// <summary>Used by IDMS module content seeding — returns active items only.</summary>
    public const string GET_ACTIVE_BY_MODULE_IDS = @"
        SELECT ci.PIECONFIGITEMID    AS ConfigItemId,
               ci.PIEMODULEID        AS ModuleId,
               ci.PIECONFIGITEMCODE  AS ItemCode,
               ci.PIECONFIGITEMNAME  AS ItemName,
               ci.ITEMCATEGORY       AS ItemCategory,
               ci.ISMANDATORY        AS IsMandatory,
               ci.ESTIMATEDHOURS     AS EstimatedHours,
               ci.TENANTID           AS TenantId
        FROM   pie.MPIECONFIGITEM ci
        WHERE  ci.PIEMODULEID = ANY(@ModuleIds)
          AND  ci.STATUS      = 1
          AND  ci.TENANTID    = @TenantId
        ORDER BY ci.PIEMODULEID, ci.ITEMCATEGORY, ci.SORTORDER";

    public const string INSERT = @"
        INSERT INTO pie.MPIECONFIGITEM
            (PIECONFIGITEMID, PIEMODULEID, PIECONFIGITEMCODE, PIECONFIGITEMNAME, ITEMCATEGORY, description,
             ISMANDATORY, DEFAULTVALUE, ESTIMATEDHOURS, SORTORDER, STATUS, CMSCONTENTREF,
             CREATEDON, CREATEDBYID, MODIFIEDON, MODIFIEDBYID, TENANTID)
        VALUES
            (@ConfigItemId, @ModuleId, @ItemCode, @ItemName, @ItemCategory, @Description,
             @IsMandatory, @DefaultValue, @EstimatedHours, @DisplayOrder, @IsActive, @CmsContentRef,
             @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy, @TenantId)";

    public const string UPDATE = @"
        UPDATE pie.MPIECONFIGITEM SET
            PIECONFIGITEMCODE = @ItemCode,
            PIECONFIGITEMNAME = @ItemName,
            ITEMCATEGORY      = @ItemCategory,
            description       = @Description,
            ISMANDATORY       = @IsMandatory,
            DEFAULTVALUE      = @DefaultValue,
            ESTIMATEDHOURS    = @EstimatedHours,
            SORTORDER         = @DisplayOrder,
            STATUS            = @IsActive,
            CMSCONTENTREF     = @CmsContentRef,
            MODIFIEDON        = @UpdatedAt,
            MODIFIEDBYID      = @UpdatedBy
        WHERE PIECONFIGITEMID = @ConfigItemId AND TENANTID = @TenantId";

    public const string SOFT_DELETE = @"
        UPDATE pie.MPIECONFIGITEM SET
            STATUS       = 0,
            MODIFIEDON   = @UpdatedAt,
            MODIFIEDBYID = @UpdatedBy
        WHERE PIECONFIGITEMID = @ConfigItemId AND TENANTID = @TenantId";
}

// ── MPIETRAININGTOPIC list ────────────────────────────────────────────────────

public sealed class PIETrainingTopicQB : IQueryBuilder<PIETrainingTopicListQuery, PIETrainingTopicListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["topiccode"]        = "tt.TOPICCODE",
            ["topicname"]        = "tt.TOPICNAME",
            ["sequenceorder"]    = "tt.SEQUENCEORDER",
            ["estimatedminutes"] = "tt.ESTIMATEDMINUTES"
        };

    public (string Sql, DynamicParameters Params) Build(PIETrainingTopicListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort,
            "tt.SEQUENCEORDER ASC, tt.TOPICCODE ASC");
        return BuildTrainingTopicList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildTrainingTopicList(
        PIETrainingTopicListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("trainingtopic", "tt", schema: "pie");

        var tenantParams = new DynamicParameters();
        tenantParams.Add("TenantId", login.ClientId);

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("tt.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactGuid(ctx,   c.ModuleId,      "trainingtopic", "PIEMODULEID"),
            SqlClauses.ExactString(ctx, c.TopicCategory, "trainingtopic", "TOPICCATEGORY"),
            SqlClauses.ExactShort(ctx,  c.IsMandatory,   "trainingtopic", "ISMANDATORY"),
            SqlClauses.ExactShort(ctx,  c.IsActive,      "trainingtopic", "STATUS"),
            SqlClauses.CodeNameSearch(ctx, c.SearchText, "trainingtopic", "TOPICCODE", "TOPICNAME"),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
SELECT {d.TotalCountExpr()},
       tt.PIETRAININGTOPICID AS TopicId,
       tt.PIEMODULEID        AS ModuleId,
       tt.TOPICCODE          AS TopicCode,
       tt.TOPICNAME          AS TopicName,
       tt.TOPICCATEGORY      AS TopicCategory,
       tt.ESTIMATEDMINUTES   AS EstimatedMinutes,
       tt.ISMANDATORY        AS IsMandatory,
       tt.STATUS             AS IsActive
FROM   {ctx.Table("trainingtopic", "MPIETRAININGTOPIC")} tt
{whereSql}
{pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }

    // ── Training topic CRUD ───────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT tt.PIETRAININGTOPICID AS TopicId,
               tt.PIEMODULEID        AS ModuleId,
               m.PIEMODULENAME       AS ModuleName,
               tt.TOPICCODE          AS TopicCode,
               tt.TOPICNAME          AS TopicName,
               tt.MENUPATH           AS MenuPath,
               tt.TOPICCATEGORY      AS TopicCategory,
               tt.ESTIMATEDMINUTES   AS EstimatedMinutes,
               tt.SEQUENCEORDER      AS SequenceOrder,
               tt.ISMANDATORY        AS IsMandatory,
               tt.STATUS             AS IsActive,
               tt.CMSCONTENTREF      AS CmsContentRef,
               tt.CREATEDON          AS CreatedAt,
               tt.CREATEDBYID        AS CreatedBy,
               tt.MODIFIEDON         AS UpdatedAt,
               tt.MODIFIEDBYID       AS UpdatedBy,
               tt.TENANTID           AS TenantId
        FROM   pie.MPIETRAININGTOPIC tt
        JOIN   pie.MPIEMODULE m ON m.PIEMODULEID = tt.PIEMODULEID AND m.TENANTID = tt.TENANTID
        WHERE  tt.PIETRAININGTOPICID = @TopicId
          AND  tt.TENANTID           = @TenantId";

    public const string GET_ACTIVE_BY_MODULE_IDS = @"
        SELECT tt.PIETRAININGTOPICID AS TopicId,
               tt.PIEMODULEID        AS ModuleId,
               tt.TOPICCODE          AS TopicCode,
               tt.TOPICNAME          AS TopicName,
               tt.TOPICCATEGORY      AS TopicCategory,
               tt.ESTIMATEDMINUTES   AS EstimatedMinutes,
               tt.ISMANDATORY        AS IsMandatory,
               tt.TENANTID           AS TenantId
        FROM   pie.MPIETRAININGTOPIC tt
        WHERE  tt.PIEMODULEID = ANY(@ModuleIds)
          AND  tt.STATUS      = 1
          AND  tt.TENANTID    = @TenantId
        ORDER BY tt.PIEMODULEID, tt.SEQUENCEORDER";

    public const string INSERT = @"
        INSERT INTO pie.MPIETRAININGTOPIC
            (PIETRAININGTOPICID, PIEMODULEID, TOPICCODE, TOPICNAME, MENUPATH, TOPICCATEGORY,
             ESTIMATEDMINUTES, SEQUENCEORDER, ISMANDATORY, STATUS, CMSCONTENTREF,
             CREATEDON, CREATEDBYID, MODIFIEDON, MODIFIEDBYID, TENANTID)
        VALUES
            (@TopicId, @ModuleId, @TopicCode, @TopicName, @MenuPath, @TopicCategory,
             @EstimatedMinutes, @SequenceOrder, @IsMandatory, @IsActive, @CmsContentRef,
             @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy, @TenantId)";

    public const string UPDATE = @"
        UPDATE pie.MPIETRAININGTOPIC SET
            TOPICCODE        = @TopicCode,
            TOPICNAME        = @TopicName,
            MENUPATH         = @MenuPath,
            TOPICCATEGORY    = @TopicCategory,
            ESTIMATEDMINUTES = @EstimatedMinutes,
            SEQUENCEORDER    = @SequenceOrder,
            ISMANDATORY      = @IsMandatory,
            STATUS           = @IsActive,
            CMSCONTENTREF    = @CmsContentRef,
            MODIFIEDON       = @UpdatedAt,
            MODIFIEDBYID     = @UpdatedBy
        WHERE PIETRAININGTOPICID = @TopicId AND TENANTID = @TenantId";

    public const string SOFT_DELETE = @"
        UPDATE pie.MPIETRAININGTOPIC SET
            STATUS       = 0,
            MODIFIEDON   = @UpdatedAt,
            MODIFIEDBYID = @UpdatedBy
        WHERE PIETRAININGTOPICID = @TopicId AND TENANTID = @TenantId";
}

// ── MPIEUATSCENARIO list ──────────────────────────────────────────────────────

public sealed class PIEUATScenarioQB : IQueryBuilder<PIEUATScenarioListQuery, PIEUATScenarioListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["scenariocode"]     = "us.SCENARIOCODE",
            ["scenariotitle"]    = "us.SCENARIOTITLE",
            ["complexity"]       = "us.COMPLEXITY",
            ["estimatedminutes"] = "us.ESTIMATEDMINUTES"
        };

    public (string Sql, DynamicParameters Params) Build(PIEUATScenarioListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort,
            "us.SCENARIOCODE ASC");
        return BuildUATScenarioList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildUATScenarioList(
        PIEUATScenarioListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("uatscenario", "us", schema: "pie");

        var tenantParams = new DynamicParameters();
        tenantParams.Add("TenantId", login.ClientId);

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("us.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactGuid(ctx,  c.ModuleId,    "uatscenario", "PIEMODULEID"),
            SqlClauses.ExactShort(ctx, c.Complexity,  "uatscenario", "COMPLEXITY"),
            SqlClauses.ExactShort(ctx, c.IsMandatory, "uatscenario", "ISMANDATORY"),
            SqlClauses.ExactShort(ctx, c.IsActive,    "uatscenario", "STATUS"),
            SqlClauses.CodeNameSearch(ctx, c.SearchText, "uatscenario", "SCENARIOCODE", "SCENARIOTITLE"),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
SELECT {d.TotalCountExpr()},
       us.PIEUATSCENARIOID  AS ScenarioId,
       us.PIEMODULEID       AS ModuleId,
       us.SCENARIOCODE      AS ScenarioCode,
       us.SCENARIOTITLE     AS ScenarioTitle,
       us.COMPLEXITY        AS Complexity,
       us.ESTIMATEDMINUTES  AS EstimatedMinutes,
       us.ISMANDATORY       AS IsMandatory,
       us.STATUS            AS IsActive
FROM   {ctx.Table("uatscenario", "MPIEUATSCENARIO")} us
{whereSql}
{pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }

    // ── UAT scenario CRUD ─────────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT us.PIEUATSCENARIOID  AS ScenarioId,
               us.PIEMODULEID       AS ModuleId,
               m.PIEMODULENAME      AS ModuleName,
               us.SCENARIOCODE      AS ScenarioCode,
               us.SCENARIOTITLE     AS ScenarioTitle,
               us.TRANSACTIONTYPE   AS TransactionType,
               us.PRECONDITIONS     AS Preconditions,
               us.STEPS             AS Steps,
               us.EXPECTEDRESULT    AS ExpectedResult,
               us.ESTIMATEDMINUTES  AS EstimatedMinutes,
               us.COMPLEXITY        AS Complexity,
               us.ISMANDATORY       AS IsMandatory,
               us.STATUS            AS IsActive,
               us.CMSCONTENTREF     AS CmsContentRef,
               us.CREATEDON         AS CreatedAt,
               us.CREATEDBYID       AS CreatedBy,
               us.MODIFIEDON        AS UpdatedAt,
               us.MODIFIEDBYID      AS UpdatedBy,
               us.TENANTID          AS TenantId
        FROM   pie.MPIEUATSCENARIO us
        JOIN   pie.MPIEMODULE m ON m.PIEMODULEID = us.PIEMODULEID AND m.TENANTID = us.TENANTID
        WHERE  us.PIEUATSCENARIOID = @ScenarioId
          AND  us.TENANTID         = @TenantId";

    public const string GET_ACTIVE_BY_MODULE_IDS = @"
        SELECT us.PIEUATSCENARIOID  AS ScenarioId,
               us.PIEMODULEID       AS ModuleId,
               us.SCENARIOCODE      AS ScenarioCode,
               us.SCENARIOTITLE     AS ScenarioTitle,
               us.COMPLEXITY        AS Complexity,
               us.ESTIMATEDMINUTES  AS EstimatedMinutes,
               us.ISMANDATORY       AS IsMandatory,
               us.TENANTID          AS TenantId
        FROM   pie.MPIEUATSCENARIO us
        WHERE  us.PIEMODULEID = ANY(@ModuleIds)
          AND  us.STATUS      = 1
          AND  us.TENANTID    = @TenantId
        ORDER BY us.PIEMODULEID, us.SCENARIOCODE";

    public const string INSERT = @"
        INSERT INTO pie.MPIEUATSCENARIO
            (PIEUATSCENARIOID, PIEMODULEID, SCENARIOCODE, SCENARIOTITLE, TRANSACTIONTYPE, PRECONDITIONS,
             STEPS, EXPECTEDRESULT, ESTIMATEDMINUTES, COMPLEXITY, ISMANDATORY, STATUS,
             CMSCONTENTREF, CREATEDON, CREATEDBYID, MODIFIEDON, MODIFIEDBYID, TENANTID)
        VALUES
            (@ScenarioId, @ModuleId, @ScenarioCode, @ScenarioTitle, @TransactionType, @Preconditions,
             @Steps, @ExpectedResult, @EstimatedMinutes, @Complexity, @IsMandatory, @IsActive,
             @CmsContentRef, @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy, @TenantId)";

    public const string UPDATE = @"
        UPDATE pie.MPIEUATSCENARIO SET
            SCENARIOCODE     = @ScenarioCode,
            SCENARIOTITLE    = @ScenarioTitle,
            TRANSACTIONTYPE  = @TransactionType,
            PRECONDITIONS    = @Preconditions,
            STEPS            = @Steps,
            EXPECTEDRESULT   = @ExpectedResult,
            ESTIMATEDMINUTES = @EstimatedMinutes,
            COMPLEXITY       = @Complexity,
            ISMANDATORY      = @IsMandatory,
            STATUS           = @IsActive,
            CMSCONTENTREF    = @CmsContentRef,
            MODIFIEDON       = @UpdatedAt,
            MODIFIEDBYID     = @UpdatedBy
        WHERE PIEUATSCENARIOID = @ScenarioId AND TENANTID = @TenantId";

    public const string SOFT_DELETE = @"
        UPDATE pie.MPIEUATSCENARIO SET
            STATUS       = 0,
            MODIFIEDON   = @UpdatedAt,
            MODIFIEDBYID = @UpdatedBy
        WHERE PIEUATSCENARIOID = @ScenarioId AND TENANTID = @TenantId";
}

// ── MPIEMIGRATIONOBJECT list ──────────────────────────────────────────────────

public sealed class PIEMigrationObjectQB : IQueryBuilder<PIEMigrationObjectListQuery, PIEMigrationObjectListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["objectcode"]    = "mo.OBJECTCODE",
            ["objectname"]    = "mo.OBJECTNAME",
            ["datacategory"]  = "mo.DATACATEGORY",
            ["sequenceorder"] = "mo.SEQUENCEORDER"
        };

    public (string Sql, DynamicParameters Params) Build(PIEMigrationObjectListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort,
            "mo.DATACATEGORY ASC, mo.SEQUENCEORDER ASC, mo.OBJECTCODE ASC");
        return BuildMigrationObjectList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildMigrationObjectList(
        PIEMigrationObjectListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("migobj", "mo", schema: "pie");

        var tenantParams = new DynamicParameters();
        tenantParams.Add("TenantId", login.ClientId);

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("mo.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactGuid(ctx,   c.ModuleId,     "migobj", "PIEMODULEID"),
            SqlClauses.ExactString(ctx, c.DataCategory, "migobj", "DATACATEGORY"),
            SqlClauses.ExactShort(ctx,  c.IsMandatory,  "migobj", "ISMANDATORY"),
            SqlClauses.ExactShort(ctx,  c.IsActive,     "migobj", "STATUS"),
            SqlClauses.CodeNameSearch(ctx, c.SearchText, "migobj", "OBJECTCODE", "OBJECTNAME"),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
SELECT {d.TotalCountExpr()},
       mo.PIEMIGRATIONOBJECTID AS MigrationObjectId,
       mo.PIEMODULEID          AS ModuleId,
       mo.OBJECTCODE           AS ObjectCode,
       mo.OBJECTNAME           AS ObjectName,
       mo.DATACATEGORY         AS DataCategory,
       mo.ISMANDATORY          AS IsMandatory,
       mo.STATUS               AS IsActive
FROM   {ctx.Table("migobj", "MPIEMIGRATIONOBJECT")} mo
{whereSql}
{pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }

    // ── Migration object CRUD ─────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT mo.PIEMIGRATIONOBJECTID AS MigrationObjectId,
               mo.PIEMODULEID          AS ModuleId,
               m.PIEMODULENAME         AS ModuleName,
               mo.OBJECTCODE           AS ObjectCode,
               mo.OBJECTNAME           AS ObjectName,
               mo.DATACATEGORY         AS DataCategory,
               mo.description          AS Description,
               mo.TEMPLATEREF          AS TemplateRef,
               mo.ISMANDATORY          AS IsMandatory,
               mo.SEQUENCEORDER        AS SequenceOrder,
               mo.STATUS               AS IsActive,
               mo.CREATEDON            AS CreatedAt,
               mo.CREATEDBYID          AS CreatedBy,
               mo.MODIFIEDON           AS UpdatedAt,
               mo.MODIFIEDBYID         AS UpdatedBy,
               mo.TENANTID             AS TenantId
        FROM   pie.MPIEMIGRATIONOBJECT mo
        JOIN   pie.MPIEMODULE m ON m.PIEMODULEID = mo.PIEMODULEID AND m.TENANTID = mo.TENANTID
        WHERE  mo.PIEMIGRATIONOBJECTID = @MigrationObjectId
          AND  mo.TENANTID             = @TenantId";

    public const string GET_ACTIVE_BY_MODULE_IDS = @"
        SELECT mo.PIEMIGRATIONOBJECTID AS MigrationObjectId,
               mo.PIEMODULEID          AS ModuleId,
               mo.OBJECTCODE           AS ObjectCode,
               mo.OBJECTNAME           AS ObjectName,
               mo.DATACATEGORY         AS DataCategory,
               mo.ISMANDATORY          AS IsMandatory,
               mo.TENANTID             AS TenantId
        FROM   pie.MPIEMIGRATIONOBJECT mo
        WHERE  mo.PIEMODULEID = ANY(@ModuleIds)
          AND  mo.STATUS      = 1
          AND  mo.TENANTID    = @TenantId
        ORDER BY mo.PIEMODULEID, mo.DATACATEGORY, mo.SEQUENCEORDER";

    public const string INSERT = @"
        INSERT INTO pie.MPIEMIGRATIONOBJECT
            (PIEMIGRATIONOBJECTID, PIEMODULEID, OBJECTCODE, OBJECTNAME, DATACATEGORY, description,
             TEMPLATEREF, ISMANDATORY, SEQUENCEORDER, STATUS,
             CREATEDON, CREATEDBYID, MODIFIEDON, MODIFIEDBYID, TENANTID)
        VALUES
            (@MigrationObjectId, @ModuleId, @ObjectCode, @ObjectName, @DataCategory, @Description,
             @TemplateRef, @IsMandatory, @SequenceOrder, @IsActive,
             @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy, @TenantId)";

    public const string UPDATE = @"
        UPDATE pie.MPIEMIGRATIONOBJECT SET
            OBJECTCODE    = @ObjectCode,
            OBJECTNAME    = @ObjectName,
            DATACATEGORY  = @DataCategory,
            description   = @Description,
            TEMPLATEREF   = @TemplateRef,
            ISMANDATORY   = @IsMandatory,
            SEQUENCEORDER = @SequenceOrder,
            STATUS        = @IsActive,
            MODIFIEDON    = @UpdatedAt,
            MODIFIEDBYID  = @UpdatedBy
        WHERE PIEMIGRATIONOBJECTID = @MigrationObjectId AND TENANTID = @TenantId";

    public const string SOFT_DELETE = @"
        UPDATE pie.MPIEMIGRATIONOBJECT SET
            STATUS       = 0,
            MODIFIEDON   = @UpdatedAt,
            MODIFIEDBYID = @UpdatedBy
        WHERE PIEMIGRATIONOBJECTID = @MigrationObjectId AND TENANTID = @TenantId";
}