namespace EntitlementDAL.QueryBuilders;

// Required indexes:
//   MENTITLEMENTBETACLIENT: UX (CLIENTID, BETAPROGRAMNAME)
public static class BetaClientQB
{
    private const string COLUMNS = @"
       BETACLIENTID    AS BetaClientId,
       CLIENTID        AS ClientId,
       BETAPROGRAMNAME AS BetaProgramName,
       VALIDFROM       AS ValidFrom,
       VALIDUNTIL      AS ValidUntil,
       REMARKS         AS Remarks,
       VERSION         AS Version,
       STATUS          AS Status,
       SORTORDER       AS SortOrder,
       CREATEDBYID     AS CreatedById,
       CREATEDON       AS CreatedOn,
       MODIFIEDBYID    AS ModifiedById,
       MODIFIEDON      AS ModifiedOn,
       SOURCETYPE      AS SourceType,
       TENANTID        AS TenantId";

    public const string GET_LIST = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTBETACLIENT
WHERE  STATUS != 0
ORDER BY BETAPROGRAMNAME, CLIENTID";

    // Client-facing "my beta enrolments" — unlike GET_LIST, scoped to one client.
    public const string GET_LIST_BY_CLIENT = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTBETACLIENT
WHERE  CLIENTID = @ClientId
  AND  STATUS  != 0
ORDER BY BETAPROGRAMNAME";

    public const string GET_OPEN_PROGRAMS = @"
SELECT DISTINCT BETAPROGRAMNAME AS BetaProgramName
FROM   MENTITLEMENTBETACLIENT
WHERE  STATUS != 0
  AND  VALIDUNTIL >= @Now
ORDER BY BETAPROGRAMNAME";

    // Membership check used by Layer 5 of the resolution algorithm (ROLLOUTTYPE=1 BetaClients).
    public const string IS_CLIENT_IN_PROGRAM = @"
SELECT COUNT(*)
FROM   MENTITLEMENTBETACLIENT
WHERE  CLIENTID        = @ClientId
  AND  BETAPROGRAMNAME = @BetaProgramName
  AND  STATUS          != 0
  AND  VALIDFROM       <= @Now
  AND  VALIDUNTIL       >= @Now";

    public const string INSERT = @"
INSERT INTO MENTITLEMENTBETACLIENT
    (BETACLIENTID, CLIENTID, BETAPROGRAMNAME, VALIDFROM, VALIDUNTIL, REMARKS,
     VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
VALUES
    (@BetaClientId, @ClientId, @BetaProgramName, @ValidFrom, @ValidUntil, @Remarks,
     @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string DELETE = @"
UPDATE MENTITLEMENTBETACLIENT SET STATUS = 0, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
WHERE  BETACLIENTID = @BetaClientId";

    // Expired beta enrolments still marked Active — used by BetaClientExpiryJob.
    public const string GET_EXPIRED_ACTIVE = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTBETACLIENT
WHERE  STATUS     != 0
  AND  VALIDUNTIL  < @Now";

    // No STATUS filter — UX_MENTITLEMENTBETACLIENT (CLIENTID, BETAPROGRAMNAME) is NOT filtered by
    // STATUS, so a soft-deleted (opted-out) row still occupies the unique key. Re-enrolling must
    // find and reactivate that row rather than insert a duplicate.
    public const string GET_BY_CLIENT_AND_PROGRAM = "SELECT" + COLUMNS + @"
FROM   MENTITLEMENTBETACLIENT
WHERE  CLIENTID        = @ClientId
  AND  BETAPROGRAMNAME = @BetaProgramName";

    public const string REACTIVATE = @"
UPDATE MENTITLEMENTBETACLIENT SET
    STATUS       = 1,
    VALIDFROM    = @ValidFrom,
    VALIDUNTIL   = @ValidUntil,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE  BETACLIENTID = @BetaClientId";
}