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

namespace SwDAL.Query.ScriptBranch;

// Required index: IX_MSWSCRIPTBRANCH_TENANTID ON SW.MSWSCRIPTBRANCH (TENANTID, STATUS)
public class ScriptBranchQB : IQueryBuilder<ScriptBranchListQuery, ScriptBranchListDTO>
{
    // ── Sort allowlist ────────────────────────────────────────────────────
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["branchcode"] = "b.BRANCHCODE",
            ["branchname"] = "b.BRANCHNAME"
        };

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

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

    // DbModelName is a read-only JOIN for the FE's DbModel picklist display (tracker §41's
    // screen audit — the FE form had no DbModelId field at all despite ScriptBranchBLL.Save's
    // own `if (dto.DbModelId <= 0) throw` guard, meaning every save from this screen failed).
    public const string GET_BY_ID = @"
        SELECT b.BRANCHID        AS BranchId,
               b.DBMODELID       AS DbModelId,
               m.DBMODELNAME     AS DbModelName,
               b.BRANCHCODE      AS BranchCode,
               b.BRANCHNAME      AS BranchName,
               b.BRANCHTYPE      AS BranchType,
               b.BRANCHSTATUS    AS BranchStatus,
               b.PARENTBRANCHID  AS ParentBranchId,
               b.DESCRIPTION     AS Description,
               b.VERSION,
               b.STATUS,
               b.SORTORDER       AS SortOrder,
               b.CREATEDBYID     AS CreatedById,
               b.CREATEDON       AS CreatedOn,
               b.MODIFIEDBYID    AS ModifiedById,
               b.MODIFIEDON      AS ModifiedOn,
               b.SOURCETYPE      AS SourceType,
               b.TENANTID        AS TenantId
        FROM   SW.MSWSCRIPTBRANCH b
        LEFT JOIN SW.MSWDBMODEL m ON m.DBMODELID = b.DBMODELID
        WHERE  b.BRANCHID = @BranchId
          AND  b.TENANTID = @TenantId";

    public const string INSERT = @"
        INSERT INTO SW.MSWSCRIPTBRANCH
            (BRANCHID, DBMODELID, BRANCHCODE, BRANCHNAME, BRANCHTYPE, BRANCHSTATUS, PARENTBRANCHID,
             DESCRIPTION, VERSION, STATUS, SORTORDER,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES
            (@BranchId, @DbModelId, @BranchCode, @BranchName, @BranchType, @BranchStatus, @ParentBranchId,
             @Description, @Version, @Status, @SortOrder,
             @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string UPDATE = @"
        UPDATE SW.MSWSCRIPTBRANCH SET
            BRANCHNAME    = @BranchName,
            BRANCHTYPE    = @BranchType,
            BRANCHSTATUS  = @BranchStatus,
            PARENTBRANCHID = @ParentBranchId,
            DESCRIPTION   = @Description,
            STATUS        = @Status,
            SORTORDER     = @SortOrder,
            MODIFIEDBYID  = @ModifiedById,
            MODIFIEDON    = @ModifiedOn
        WHERE BRANCHID = @BranchId AND TENANTID = @TenantId";

    public const string DELETE = @"
        DELETE FROM SW.MSWSCRIPTBRANCH
        WHERE BRANCHID = @BranchId AND TENANTID = @TenantId AND STATUS = 1";

    public const string GET_SELECT_LIST = @"SELECT
                                            BRANCHID   AS BranchId,
                                            BRANCHCODE AS BranchCode,
                                            BRANCHNAME AS BranchName
                                        FROM SW.MSWSCRIPTBRANCH
                                        WHERE STATUS = 1
                                        ORDER BY BRANCHNAME
                                        OFFSET @firstnumber ROWS
                                        FETCH NEXT @maxresult ROWS ONLY";

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

    public static (string Sql, DynamicParameters Params) BuildScriptBranchList(
        ScriptBranchListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("branch", "b");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            SqlClauses.ExactInt(ctx, c.DbModelId,     "branch", "DBMODELID"),
            SqlClauses.ExactByte(ctx, c.BranchType,   "branch", "BRANCHTYPE"),
            SqlClauses.ExactByte(ctx, c.BranchStatus, "branch", "BRANCHSTATUS"),
            SqlClauses.DocumentSearch(ctx, c.SearchText),
        });

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

        string sql = $@"
            SELECT {d.TotalCountExpr()},
                   b.BRANCHID     AS BranchId,
                   b.DBMODELID    AS DbModelId,
                   b.BRANCHCODE   AS BranchCode,
                   b.BRANCHNAME   AS BranchName,
                   b.BRANCHTYPE   AS BranchType,
                   b.BRANCHSTATUS AS BranchStatus,
                   b.TENANTID     AS TenantId
            FROM   SW.MSWSCRIPTBRANCH b
            {whereSql}
            {pagingSql}";

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