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

namespace SwDAL.Query.ObjectGroup;

// Required index: IX_MSWOBJECTGROUP_TENANTID ON SW.MSWOBJECTGROUP (TENANTID, STATUS)
public class ObjectGroupQB : IQueryBuilder<ObjectGroupListQuery, ObjectGroupListDTO>
{
    // ── Sort allowlist ────────────────────────────────────────────────────
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["groupcode"] = "g.GROUPCODE",
            ["groupname"] = "g.GROUPNAME"
        };

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

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

    public const string GET_BY_ID = @"
        SELECT g.OBJECTGROUPID  AS ObjectGroupId,
               g.GROUPCODE      AS GroupCode,
               g.GROUPNAME      AS GroupName,
               g.DESCRIPTION    AS Description,
               g.VERSION,
               g.STATUS,
               g.SORTORDER      AS SortOrder,
               g.CREATEDBYID    AS CreatedById,
               g.CREATEDON      AS CreatedOn,
               g.MODIFIEDBYID   AS ModifiedById,
               g.MODIFIEDON     AS ModifiedOn,
               g.SOURCETYPE     AS SourceType,
               g.TENANTID       AS TenantId
        FROM   SW.MSWOBJECTGROUP g
        WHERE  g.OBJECTGROUPID = @ObjectGroupId
          AND  g.TENANTID = @TenantId";

    public const string INSERT = @"
        INSERT INTO SW.MSWOBJECTGROUP
            (OBJECTGROUPID, GROUPCODE, GROUPNAME, DESCRIPTION,
             VERSION, STATUS, SORTORDER,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES
            (@ObjectGroupId, @GroupCode, @GroupName, @Description,
             @Version, @Status, @SortOrder,
             @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string UPDATE = @"
        UPDATE SW.MSWOBJECTGROUP SET
            GROUPNAME    = @GroupName,
            DESCRIPTION  = @Description,
            STATUS       = @Status,
            SORTORDER    = @SortOrder,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON   = @ModifiedOn
        WHERE OBJECTGROUPID = @ObjectGroupId AND TENANTID = @TenantId";

    public const string DELETE = @"
        DELETE FROM SW.MSWOBJECTGROUP
        WHERE OBJECTGROUPID = @ObjectGroupId AND TENANTID = @TenantId AND STATUS = 1";

    public const string GET_DETAILS = @"
        SELECT d.OBJECTREFERENCEID AS ObjectReferenceId,
               d.OBJECTGROUPID     AS ObjectGroupId,
               d.OBJECTTYPE        AS ObjectType,
               d.TENANTID          AS TenantId
        FROM   SW.MSWOBJECTGROUPDETAIL d
        WHERE  d.OBJECTGROUPID = @ObjectGroupId
          AND  d.TENANTID      = @TenantId";

    public const string INSERT_DETAIL = @"
        INSERT INTO SW.MSWOBJECTGROUPDETAIL (OBJECTGROUPID, OBJECTTYPE, TENANTID)
        VALUES (@ObjectGroupId, @ObjectType, @TenantId)";

    public const string DELETE_DETAIL_BY_ID = @"
        DELETE FROM SW.MSWOBJECTGROUPDETAIL
        WHERE OBJECTREFERENCEID = @ObjectReferenceId AND TENANTID = @TenantId";

    public const string DELETE_DETAILS_BY_GROUP = @"
        DELETE FROM SW.MSWOBJECTGROUPDETAIL
        WHERE OBJECTGROUPID = @ObjectGroupId AND TENANTID = @TenantId";

    public const string GET_SELECT_LIST = @"SELECT 
                                        OBJECTGROUPID AS ObjectGroupId,
                                        GROUPCODE AS GroupCode,
                                        GROUPNAME AS GroupName
                                    FROM SW.MSWOBJECTGROUP
                                    WHERE STATUS = 1 
                                      AND TENANTID = @TenantId
                                    ORDER BY GROUPNAME
                                    OFFSET @firstnumber ROWS
                                    FETCH NEXT @maxresult ROWS ONLY";

    public const string GET_OBJECTGROUP_WITH_DETAIL = @"SELECT
                                                g.OBJECTGROUPID      AS ObjectGroupId,
                                                g.GROUPCODE          AS GroupCode,
                                                g.GROUPNAME          AS GroupName,
                                                g.DESCRIPTION        AS Description,
                                                g.VERSION            AS Version,
                                                g.STATUS             AS Status,
                                                g.SORTORDER          AS SortOrder,
                                                g.CREATEDBYID        AS CreatedById,
                                                g.CREATEDON          AS CreatedOn,
                                                g.MODIFIEDBYID       AS ModifiedById,
                                                g.MODIFIEDON         AS ModifiedOn,
                                                g.SOURCETYPE         AS SourceType,
                                                g.TENANTID           AS TenantId,

                                                d.OBJECTREFERENCEID  AS ObjectReferenceId,
                                                d.OBJECTGROUPID      AS ObjectGroupId,
                                                g.DESCRIPTION        AS ObjectGroupDescription,
                                                g.GROUPNAME          AS ObjectGroupName,
                                                d.OBJECTTYPE         AS ObjectType

                                            FROM SW.MSWOBJECTGROUP g
                                            LEFT JOIN SW.MSWOBJECTGROUPDETAIL d
                                                ON d.OBJECTGROUPID = g.OBJECTGROUPID
                                            WHERE g.OBJECTGROUPID = @ObjectGroupId
                                              AND g.TENANTID = @TenantId;";

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

    public static (string Sql, DynamicParameters Params) BuildObjectGroupList(
        ObjectGroupListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("objectgroup", "g");

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

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

        string sql = $@"
            SELECT {d.TotalCountExpr()},
                   g.OBJECTGROUPID AS ObjectGroupId,
                   g.GROUPCODE     AS GroupCode,
                   g.GROUPNAME     AS GroupName,
                   g.TENANTID      AS TenantId
            FROM   SW.MSWOBJECTGROUP g
            {whereSql}
            {pagingSql}";

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