namespace EntitlementDAL.QueryBuilders;

// SQL for the MENTITLEMENT table (per-subscription resolved feature grants).
// Named "EntitlementGrantQB" (not "EntitlementQB") to avoid confusion with the project
// namespace EntitlementDAL and the top-level "Entitlement" concept used throughout the module.
//
// Required indexes:
//   MENTITLEMENT: UX (SUBSCRIPTIONID, FEATUREID)
public static class EntitlementGrantQB
{
    private const string COLUMNS = @"
       ENTITLEMENTID     AS EntitlementId,
       SUBSCRIPTIONID    AS SubscriptionId,
       SLNO              AS SlNo,
       FEATUREID         AS FeatureId,
       ISENABLED         AS IsEnabled,
       FEATUREVALUE      AS FeatureValue,
       ENTITLEMENTSOURCE AS EntitlementSource,
       VALIDFROM         AS ValidFrom,
       VALIDUNTIL        AS ValidUntil";

    public const string GET_BY_ID = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENT
WHERE  ENTITLEMENTID = @EntitlementId";

    public const string GET_BY_SUBSCRIPTION = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENT
WHERE  SUBSCRIPTIONID = @SubscriptionId
ORDER BY SLNO";

    public const string GET_BY_SUBSCRIPTION_AND_FEATURE = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENT
WHERE  SUBSCRIPTIONID = @SubscriptionId
  AND  FEATUREID      = @FeatureId";

    // Resolution-layer lookup — joins to the active subscription for a client + a feature code.
    // Used by IEntitlementService.IsFeatureEnabledAsync (Layer 2: "not licensed" check).
    public const string GET_BY_CLIENT_AND_FEATURE_CODE = @"
SELECT E.ENTITLEMENTID     AS EntitlementId,
       E.SUBSCRIPTIONID    AS SubscriptionId,
       E.SLNO              AS SlNo,
       E.FEATUREID         AS FeatureId,
       E.ISENABLED         AS IsEnabled,
       E.FEATUREVALUE      AS FeatureValue,
       E.ENTITLEMENTSOURCE AS EntitlementSource,
       E.VALIDFROM         AS ValidFrom,
       E.VALIDUNTIL        AS ValidUntil
FROM   MENTITLEMENT E
INNER JOIN MENTITLEMENTSUBSCRIPTION S ON S.SUBSCRIPTIONID = E.SUBSCRIPTIONID
INNER JOIN MENTITLEMENTFEATURE F      ON F.FEATUREID = E.FEATUREID
WHERE  S.CLIENTID   = @ClientId
  AND  F.FEATURECODE = @FeatureCode";

    // Admin list view (Entitlement.svc/GetList). Joins to MENTITLEMENTSUBSCRIPTION so the list can
    // be filtered by client. Filters: @ClientId (-1 = any), @FeatureId (-1 = any),
    // @EntitlementSource (-1 = any), @IsEnabled (-1 = any, else 0/1) — same -1-means-any convention
    // as SubscriptionQB.GET_LIST_PAGED. Paged via QueryPagedAsync (OFFSET/FETCH).
    public const string GET_LIST_PAGED = @"
SELECT E.ENTITLEMENTID     AS EntitlementId,
       E.SUBSCRIPTIONID    AS SubscriptionId,
       E.SLNO              AS SlNo,
       E.FEATUREID         AS FeatureId,
       E.ISENABLED         AS IsEnabled,
       E.FEATUREVALUE      AS FeatureValue,
       E.ENTITLEMENTSOURCE AS EntitlementSource,
       E.VALIDFROM         AS ValidFrom,
       E.VALIDUNTIL        AS ValidUntil
FROM   MENTITLEMENT E
INNER JOIN MENTITLEMENTSUBSCRIPTION S ON S.SUBSCRIPTIONID = E.SUBSCRIPTIONID
WHERE  (@ClientId          = -1 OR S.CLIENTID        = @ClientId)
  AND  (@FeatureId         = -1 OR E.FEATUREID       = @FeatureId)
  AND  (@EntitlementSource = -1 OR E.ENTITLEMENTSOURCE = @EntitlementSource)
  AND  (@IsEnabled         = -1 OR E.ISENABLED       = @IsEnabled)
ORDER BY E.ENTITLEMENTID
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

    public const string INSERT = @"
INSERT INTO MENTITLEMENT
    (ENTITLEMENTID, SUBSCRIPTIONID, SLNO, FEATUREID, ISENABLED, FEATUREVALUE, ENTITLEMENTSOURCE, VALIDFROM, VALIDUNTIL)
VALUES
    (@EntitlementId, @SubscriptionId, @SlNo, @FeatureId, @IsEnabled, @FeatureValue, @EntitlementSource, @ValidFrom, @ValidUntil)";

    public const string UPDATE = @"
UPDATE MENTITLEMENT SET
    ISENABLED         = @IsEnabled,
    FEATUREVALUE      = @FeatureValue,
    ENTITLEMENTSOURCE = @EntitlementSource,
    VALIDFROM         = @ValidFrom,
    VALIDUNTIL        = @ValidUntil
WHERE  ENTITLEMENTID = @EntitlementId";

    public const string DELETE = @"
DELETE FROM MENTITLEMENT WHERE ENTITLEMENTID = @EntitlementId";
}