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

namespace PIEDAL.Query.Variant;

// Required indexes:
//   ix_pie_variant_tenant       ON pie.MPIEVARIANT(TENANTID)
//   ix_pie_variant_module       ON pie.MPIEVARIANT(PIEMODULEID) WHERE STATUS = 1
//   ix_pie_variant_impl         ON pie.MPIEVARIANTIMPLICATION(PIEVARIANTID)
//   ix_pie_variant_impl_tenant  ON pie.MPIEVARIANTIMPLICATION(TENANTID)

public sealed class PIEVariantQB : IQueryBuilder<PIEVariantListQuery, PIEVariantListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["variantcode"]  = "v.PIEVARIANTCODE",
            ["variantname"]  = "v.PIEVARIANTNAME",
            ["displayorder"] = "v.SORTORDER",
            ["isdefault"]    = "v.ISDEFAULT"
        };

    public (string Sql, DynamicParameters Params) Build(PIEVariantListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort,
            "v.ISDEFAULT DESC, v.SORTORDER ASC, v.PIEVARIANTCODE ASC");
        return BuildVariantList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildVariantList(
        PIEVariantListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("variant", "v", schema: "pie");

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

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("v.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactGuid(ctx, c.ModuleId,  "variant", "PIEMODULEID"),
            SqlClauses.ExactShort(ctx, c.IsDefault, "variant", "ISDEFAULT"),
            SqlClauses.ExactShort(ctx, c.IsActive,  "variant", "STATUS"),
            SqlClauses.CodeNameSearch(ctx, c.SearchText, "variant", "PIEVARIANTCODE", "PIEVARIANTNAME"),
        });

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

        string sql = $@"
SELECT {d.TotalCountExpr()},
       v.PIEVARIANTID  AS VariantId,
       v.PIEMODULEID   AS ModuleId,
       v.PIEVARIANTCODE AS VariantCode,
       v.PIEVARIANTNAME AS VariantName,
       v.ISDEFAULT     AS IsDefault,
       v.STATUS        AS IsActive,
       v.SORTORDER     AS DisplayOrder
FROM   {ctx.Table("variant", "MPIEVARIANT")} v
{whereSql}
{pagingSql}";

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

    // ── Variant CRUD ──────────────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT v.PIEVARIANTID        AS VariantId,
               v.PIEMODULEID         AS ModuleId,
               m.PIEMODULENAME       AS ModuleName,
               v.PIEVARIANTCODE      AS VariantCode,
               v.PIEVARIANTNAME      AS VariantName,
               v.description         AS Description,
               v.PLAINLANGUAGEDESC   AS PlainLanguageDesc,
               v.ISDEFAULT           AS IsDefault,
               v.SORTORDER           AS DisplayOrder,
               v.STATUS              AS IsActive,
               v.CMSCONTENTREF       AS CmsContentRef,
               v.CREATEDON           AS CreatedAt,
               v.CREATEDBYID         AS CreatedBy,
               v.MODIFIEDON          AS UpdatedAt,
               v.MODIFIEDBYID        AS UpdatedBy,
               v.TENANTID            AS TenantId
        FROM   pie.MPIEVARIANT v
        JOIN   pie.MPIEMODULE m ON m.PIEMODULEID = v.PIEMODULEID AND m.TENANTID = v.TENANTID
        WHERE  v.PIEVARIANTID = @VariantId
          AND  v.TENANTID     = @TenantId";

    public const string GET_IMPLICATIONS = @"
        SELECT vi.PIEVARIANTIMPLICATIONID AS ImplicationId,
               vi.PIEVARIANTID            AS VariantId,
               vi.IMPLICATIONTYPE         AS ImplicationType,
               vi.REFID                   AS RefId,
               vi.REFCODE                 AS RefCode,
               vi.EFFORTDELTAHOURS        AS EffortDeltaHours,
               vi.description             AS Description,
               vi.RISKNOTE                AS RiskNote,
               vi.TENANTID                AS TenantId
        FROM   pie.MPIEVARIANTIMPLICATION vi
        WHERE  vi.PIEVARIANTID = @VariantId
          AND  vi.TENANTID     = @TenantId
        ORDER BY vi.IMPLICATIONTYPE";

    public const string GET_ACTIVE_BY_MODULE = @"
        SELECT v.PIEVARIANTID   AS VariantId,
               v.PIEMODULEID    AS ModuleId,
               v.PIEVARIANTCODE AS VariantCode,
               v.PIEVARIANTNAME AS VariantName,
               v.ISDEFAULT      AS IsDefault
        FROM   pie.MPIEVARIANT v
        WHERE  v.PIEMODULEID = @ModuleId
          AND  v.STATUS      = 1
          AND  v.TENANTID    = @TenantId
        ORDER BY v.ISDEFAULT DESC, v.SORTORDER, v.PIEVARIANTCODE";

    public const string INSERT = @"
        INSERT INTO pie.MPIEVARIANT
            (PIEVARIANTID, PIEMODULEID, PIEVARIANTCODE, PIEVARIANTNAME, description, PLAINLANGUAGEDESC,
             ISDEFAULT, SORTORDER, STATUS, CMSCONTENTREF,
             CREATEDON, CREATEDBYID, MODIFIEDON, MODIFIEDBYID, TENANTID)
        VALUES
            (@VariantId, @ModuleId, @VariantCode, @VariantName, @Description, @PlainLanguageDesc,
             @IsDefault, @DisplayOrder, @IsActive, @CmsContentRef,
             @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy, @TenantId)";

    public const string UPDATE = @"
        UPDATE pie.MPIEVARIANT SET
            PIEVARIANTCODE    = @VariantCode,
            PIEVARIANTNAME    = @VariantName,
            description       = @Description,
            PLAINLANGUAGEDESC = @PlainLanguageDesc,
            ISDEFAULT         = @IsDefault,
            SORTORDER         = @DisplayOrder,
            STATUS            = @IsActive,
            CMSCONTENTREF     = @CmsContentRef,
            MODIFIEDON        = @UpdatedAt,
            MODIFIEDBYID      = @UpdatedBy
        WHERE PIEVARIANTID = @VariantId AND TENANTID = @TenantId";

    public const string SOFT_DELETE = @"
        UPDATE pie.MPIEVARIANT SET
            STATUS       = 0,
            MODIFIEDON   = @UpdatedAt,
            MODIFIEDBYID = @UpdatedBy
        WHERE PIEVARIANTID = @VariantId AND TENANTID = @TenantId";

    // ── Implication CRUD ──────────────────────────────────────────────────────

    public const string INSERT_IMPLICATION = @"
        INSERT INTO pie.MPIEVARIANTIMPLICATION
            (PIEVARIANTIMPLICATIONID, PIEVARIANTID, IMPLICATIONTYPE, REFID, REFCODE,
             EFFORTDELTAHOURS, description, RISKNOTE, TENANTID)
        VALUES
            (@ImplicationId, @VariantId, @ImplicationType, @RefId, @RefCode,
             @EffortDeltaHours, @Description, @RiskNote, @TenantId)";

    public const string DELETE_IMPLICATIONS = @"
        DELETE FROM pie.MPIEVARIANTIMPLICATION
        WHERE PIEVARIANTID = @VariantId AND TENANTID = @TenantId";

    // ── Select list ───────────────────────────────────────────────────────────

    public const string GET_SELECT_LIST_BY_MODULE = @"
        SELECT v.PIEVARIANTID   AS VariantId,
               v.PIEVARIANTCODE AS VariantCode,
               v.PIEVARIANTNAME AS VariantName,
               v.PIEMODULEID    AS ModuleId,
               v.ISDEFAULT      AS IsDefault
        FROM   pie.MPIEVARIANT v
        WHERE  v.PIEMODULEID = @ModuleId
          AND  v.STATUS      = 1
          AND  v.TENANTID    = @TenantId
        ORDER BY v.ISDEFAULT DESC, v.SORTORDER, v.PIEVARIANTCODE";
}