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

namespace SwDAL.Query.DbModel;

// Required index: IX_MSWDBMODEL_TENANTID ON SW.MSWDBMODEL (TENANTID, STATUS)
public class DbModelQB : IQueryBuilder<DbModelListQuery, DbModelListDTO>
{
    // ── Sort allowlist ────────────────────────────────────────────────────
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["dbmodelcode"] = "m.DBMODELCODE",
            ["dbmodelname"] = "m.DBMODELNAME"
        };

    // ── IQueryBuilder implementation ──────────────────────────────────────
    public (string Sql, DynamicParameters Params) Build(DbModelListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort, "m.DBMODELID DESC");
        return BuildDbModelList(query.Criteria, dialect, orderBy);
    }

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

    public const string GET_BY_ID = @"
        SELECT m.DBMODELID      AS DbModelId,
               m.DBMODELCODE    AS DbModelCode,
               m.DBMODELNAME    AS DbModelName,
               m.DESCRIPTION    AS Description,
               m.DBSERVERID     AS DbServerId,
               m.DATABASENAME   AS DatabaseName,
               s.HOSTNAME       AS ServerHostName,
               m.VERSION,
               m.STATUS,
               m.SORTORDER      AS SortOrder,
               m.CREATEDBYID    AS CreatedById,
               m.CREATEDON      AS CreatedOn,
               m.MODIFIEDBYID   AS ModifiedById,
               m.MODIFIEDON     AS ModifiedOn,
               m.SOURCETYPE     AS SourceType,
               m.TENANTID       AS TenantId
        FROM   SW.MSWDBMODEL m
        LEFT JOIN SW.MSWDBSERVER s
               ON s.DBSERVERID = m.DBSERVERID AND s.TENANTID = m.TENANTID
        WHERE  m.DBMODELID = @DbModelId
          AND  m.TENANTID = @TenantId";

    public const string INSERT = @"
        INSERT INTO SW.MSWDBMODEL
            (DBMODELID, DBMODELCODE, DBMODELNAME, DESCRIPTION, DBSERVERID, DATABASENAME,
             VERSION, STATUS, SORTORDER,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES
            (@DbModelId, @DbModelCode, @DbModelName, @Description, @DbServerId, @DatabaseName,
             @Version, @Status, @SortOrder,
             @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string UPDATE = @"
        UPDATE SW.MSWDBMODEL SET
            DBMODELNAME  = @DbModelName,
            DESCRIPTION  = @Description,
            DBSERVERID   = @DbServerId,
            DATABASENAME = @DatabaseName,
            STATUS       = @Status,
            SORTORDER    = @SortOrder,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON   = @ModifiedOn
        WHERE DBMODELID = @DbModelId AND TENANTID = @TenantId";

    public const string DELETE = @"
        DELETE FROM SW.MSWDBMODEL
        WHERE DBMODELID = @DbModelId AND TENANTID = @TenantId AND STATUS = 1";

    public const string GET_SELECT_LIST = @"SELECT DBMODELID   AS DbModelId,
                                                   DBMODELCODE AS DbModelCode,
                                                   DBMODELNAME AS DbModelName
                                            FROM   SW.MSWDBMODEL
                                            WHERE  STATUS   = 1
                                              AND  TENANTID = @TenantId
                                            ORDER  BY DBMODELNAME
                                            OFFSET @firstnumber ROWS
                                            FETCH NEXT @maxresult ROWS ONLY;";

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

    public static (string Sql, DynamicParameters Params) BuildDbModelList(
        DbModelListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("dbmodel", "m");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            SqlClauses.ExactInt(ctx, c.DbServerId, "dbmodel", "DBSERVERID"),
            SqlClauses.DocumentSearch(ctx, c.SearchText),
        });

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

        string sql = $@"
            SELECT {d.TotalCountExpr()},
                   m.DBMODELID   AS DbModelId,
                   m.DBMODELCODE AS DbModelCode,
                   m.DBMODELNAME AS DbModelName,
                   m.TENANTID    AS TenantId
            FROM   SW.MSWDBMODEL m
            {whereSql}
            {pagingSql}";

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