namespace PartnerDAL.Query.PartnerApiKey
{
    public static class PartnerApiKeyQB
    {
        // Index: IX_TPARTNERAPIKEY_PRODUCT on (PARTNERPRODUCTID) INCLUDE (KEYNAME, KEYHINT, SCOPES, EXPIRESON, STATUS, LASTUSEDUTC)
        public const string GET_PARTNER_API_KEY_LIST = @"
            SELECT APIKEYID            AS ApiKeyId,
                   PARTNERPRODUCTID    AS PartnerProductId,
                   KEYNAME             AS KeyName,
                   KEYHINT             AS KeyHint,
                   SCOPES              AS Scopes,
                   RATELIMITPERMINUTE  AS RateLimitPerMinute,
                   EXPIRESON           AS ExpiresOn,
                   LASTUSEDUTC         AS LastUsedUtc,
                   STATUS              AS Status,
                   CREATEDON           AS CreatedOn
            FROM   TPARTNERAPIKEY
            WHERE  PARTNERPRODUCTID = @PartnerProductId
            ORDER  BY SORTORDER, APIKEYID;";

        // Index: IX_TPARTNERAPIKEY_HASH on (HASHEDKEY) WHERE STATUS = 1
        // PARTNERID is denormalized directly onto TPARTNERAPIKEY (resolved once at save time —
        // see GET_PARTNERID_BY_PARTNERPRODUCTID) instead of joining TPARTNERPRODUCT here.
        // TPARTNERAPIKEY must be centrally resolvable with no tenant/ConnectionName context (an
        // API key alone has to self-identify its partner), so this query runs against GB5System
        // only — TPARTNERPRODUCT lives per-tenant and is NOT reachable from that connection.
        public const string GET_API_KEY_BY_HASH = @"
            SELECT AK.APIKEYID           AS ApiKeyId,
                   AK.PARTNERPRODUCTID   AS PartnerProductId,
                   AK.PARTNERID          AS PartnerId,
                   AK.KEYNAME            AS KeyName,
                   AK.KEYHINT            AS KeyHint,
                   AK.SCOPES             AS Scopes,
                   AK.RATELIMITPERMINUTE AS RateLimitPerMinute,
                   AK.EXPIRESON          AS ExpiresOn,
                   AK.STATUS             AS Status
            FROM   TPARTNERAPIKEY AK
            WHERE  AK.HASHEDKEY = @HashedKey
              AND  AK.STATUS    = 1
              AND  (AK.EXPIRESON IS NULL OR AK.EXPIRESON > GETUTCDATE());";

        // Hop 1 for SavePartnerApiKey — resolves PartnerId from PartnerProductId inside the
        // TENANT's own database (via the caller's normal LoginDTO, where TPARTNERPRODUCT
        // actually lives), before the key row itself is written centrally to GB5System.
        public const string GET_PARTNERID_BY_PARTNERPRODUCTID = @"
            SELECT PARTNERID FROM TPARTNERPRODUCT WHERE PARTNERPRODUCTID = @PartnerProductId";

        public const string SAVE_PARTNER_API_KEY = @"
            INSERT INTO TPARTNERAPIKEY
                (PARTNERPRODUCTID, PARTNERID, KEYNAME, HASHEDKEY, KEYHINT, SCOPES,
                 RATELIMITPERMINUTE, EXPIRESON, STATUS, VERSION, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@PartnerProductId, @PartnerId, @KeyName, @HashedKey, @KeyHint, @Scopes,
                 @RateLimitPerMinute, @ExpiresOn, @Status, @Version, @SortOrder, @SourceType,
                 @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);
            SELECT SCOPE_IDENTITY();";

        public const string DELETE_PARTNER_API_KEY = @"
            UPDATE TPARTNERAPIKEY
            SET    STATUS       = 0,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  APIKEYID     = @ApiKeyId;";

        public const string UPDATE_API_KEY_LAST_USED = @"
            UPDATE TPARTNERAPIKEY
            SET    LASTUSEDUTC = GETUTCDATE()
            WHERE  APIKEYID   = @ApiKeyId;";
    }
}