using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using SwDAL.DTO.SyncGroup;

namespace SwDAL.Query.SyncGroup;

// Required index: IX_MSWSYNCGROUP_TENANTID ON SW.MSWSYNCGROUP (TENANTID, STATUS, DBMODELID)
public class SyncGroupQB : IQueryBuilder<SyncGroupListQuery, SyncGroupListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["syncgroupname"] = "sg.SYNCGROUPNAME",
            ["syncgroupcode"] = "sg.SYNCGROUPCODE"
        };

    public (string Sql, DynamicParameters Params) Build(SyncGroupListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort, "sg.SYNCGROUPID DESC");
        return BuildSyncGroupList(query.Criteria, dialect, orderBy);
    }

    // ── CRUD SQL constants ────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT sg.SYNCGROUPID    AS SyncGroupId,
               sg.SYNCGROUPCODE  AS SyncGroupCode,
               sg.SYNCGROUPNAME  AS SyncGroupName,
               sg.DBMODELID      AS DbModelId,
               m.DBMODELNAME     AS DbModelName,
               sg.DESCRIPTION    AS Description,
               sg.VERSION,
               sg.STATUS,
               sg.SORTORDER      AS SortOrder,
               sg.CREATEDBYID    AS CreatedById,
               sg.CREATEDON      AS CreatedOn,
               sg.MODIFIEDBYID   AS ModifiedById,
               sg.MODIFIEDON     AS ModifiedOn,
               sg.SOURCETYPE     AS SourceType,
               sg.TENANTID       AS TenantId
        FROM   SW.MSWSYNCGROUP sg
        INNER JOIN SW.MSWDBMODEL m ON m.DBMODELID = sg.DBMODELID AND m.TENANTID = sg.TENANTID
        WHERE  sg.SYNCGROUPID = @SyncGroupId
          AND  sg.TENANTID    = @TenantId";

    public const string GET_TABLES = @"
        SELECT t.SYNCTABLEID   AS SyncTableId,
               t.SYNCGROUPID   AS SyncGroupId,
               t.TABLENAME     AS TableName,
               t.SCHEMANAME    AS SchemaName,
               t.SYNCBEHAVIOR  AS SyncBehavior,
               t.FILTERCLAUSE  AS FilterClause,
               t.TENANTID      AS TenantId
        FROM   SW.MSWSYNCTABLE t
        WHERE  t.SYNCGROUPID = @SyncGroupId
          AND  t.TENANTID    = @TenantId";

    public const string INSERT = @"
        INSERT INTO SW.MSWSYNCGROUP
            (SYNCGROUPID, SYNCGROUPCODE, SYNCGROUPNAME, DBMODELID, DESCRIPTION,
             VERSION, STATUS, SORTORDER,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES
            (@SyncGroupId, @SyncGroupCode, @SyncGroupName, @DbModelId, @Description,
             @Version, @Status, @SortOrder,
             @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string UPDATE = @"
        UPDATE SW.MSWSYNCGROUP SET
            SYNCGROUPCODE  = @SyncGroupCode,
            SYNCGROUPNAME  = @SyncGroupName,
            DBMODELID      = @DbModelId,
            DESCRIPTION    = @Description,
            STATUS         = @Status,
            SORTORDER      = @SortOrder,
            MODIFIEDBYID   = @ModifiedById,
            MODIFIEDON     = @ModifiedOn
        WHERE SYNCGROUPID = @SyncGroupId AND TENANTID = @TenantId";

    public const string DELETE = @"
        DELETE FROM SW.MSWSYNCGROUP
        WHERE SYNCGROUPID = @SyncGroupId AND TENANTID = @TenantId AND STATUS = 1";

    public const string DELETE_TABLES_BY_GROUP = @"
        DELETE FROM SW.MSWSYNCTABLE
        WHERE SYNCGROUPID = @SyncGroupId AND TENANTID = @TenantId";

    public const string INSERT_TABLE = @"
        INSERT INTO SW.MSWSYNCTABLE
            (SYNCGROUPID, TABLENAME, SCHEMANAME, SYNCBEHAVIOR, FILTERCLAUSE, TENANTID)
        VALUES
            (@SyncGroupId, @TableName, @SchemaName, @SyncBehavior, @FilterClause, @TenantId)";

    public const string DELETE_TABLE_BY_ID = @"
        DELETE FROM SW.MSWSYNCTABLE
        WHERE SYNCTABLEID = @SyncTableId AND TENANTID = @TenantId";

    public const string CHECK_IN_CLIENTDB = @"
        SELECT COUNT(1) FROM SW.MSWCLIENTDBSYNCGROUP
        WHERE SYNCGROUPID = @SyncGroupId AND TENANTID = @TenantId";

    public const string GET_SELECT_LIST = @"SELECT
                                                SYNCGROUPID AS SyncGroupId,
                                                SYNCGROUPNAME AS SyncGroupName
                                            FROM SW.MSWSYNCGROUP
                                            WHERE STATUS = 1
                                              AND TENANTID = @TenantId
                                            ORDER BY SYNCGROUPNAME
                                            OFFSET @firstnumber ROWS
                                            FETCH NEXT @maxresult ROWS ONLY";

    // ── List builder ──────────────────────────────────────────────────────

    public static (string Sql, DynamicParameters Params) BuildSyncGroupList(
        SyncGroupListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("syncgroup", "sg");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            SqlClauses.ExactInt(ctx, c.DbModelId,   "syncgroup", "DBMODELID"),
            SqlClauses.DocumentSearch(ctx, c.SearchText),
        });

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

        string sql = $@"
            SELECT {d.TotalCountExpr()},
                   sg.SYNCGROUPID    AS SyncGroupId,
                   sg.SYNCGROUPCODE  AS SyncGroupCode,
                   sg.SYNCGROUPNAME  AS SyncGroupName,
                   sg.DBMODELID      AS DbModelId,
                   sg.TENANTID       AS TenantId
            FROM   SW.MSWSYNCGROUP sg
            {whereSql}
            {pagingSql}";

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