namespace SwDAL.Query.ClientDatabaseKey;

// Required index: IX_MSWCLIENTDATABASEKEY_CLIENTDB ON SW.MSWCLIENTDATABASEKEY (CLIENTDBID, KEYSTATUS)
public static class ClientDatabaseKeyQB
{
    public const string COLUMNS = @"
               CLIENTDATABASEKEYID AS ClientDatabaseKeyId,
               CLIENTDBID          AS ClientDbId,
               PUBLICKEYPEM        AS PublicKeyPem,
               KEYSTATUS           AS KeyStatus,
               REGISTEREDON        AS RegisteredOn,
               SUPERSEDEDON        AS SupersededOn,
               VERSION,
               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_ACTIVE_BY_CLIENTDB = "SELECT " + COLUMNS + @"
        FROM   SW.MSWCLIENTDATABASEKEY
        WHERE  CLIENTDBID = @ClientDbId
          AND  KEYSTATUS  = 0
          AND  TENANTID   = @TenantId";

    public const string GET_BY_ID = "SELECT " + COLUMNS + @"
        FROM   SW.MSWCLIENTDATABASEKEY
        WHERE  CLIENTDATABASEKEYID = @ClientDatabaseKeyId
          AND  TENANTID            = @TenantId";

    public const string INSERT = @"
        INSERT INTO SW.MSWCLIENTDATABASEKEY
            (CLIENTDATABASEKEYID, CLIENTDBID, PUBLICKEYPEM, KEYSTATUS, REGISTEREDON, SUPERSEDEDON,
             VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
             SOURCETYPE, TENANTID)
        VALUES
            (@ClientDatabaseKeyId, @ClientDbId, @PublicKeyPem, @KeyStatus, @RegisteredOn, @SupersededOn,
             @Version, @Status, @SortOrder, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn,
             @SourceType, @TenantId)";

    // Marks whatever key currently sits Active for this client database as Superseded — called
    // immediately before inserting a new Active one, so "exactly one Active key per ClientDbId"
    // holds without a DB-level constraint.
    public const string SUPERSEDE_ACTIVE = @"
        UPDATE SW.MSWCLIENTDATABASEKEY SET
            KEYSTATUS    = 1,
            SUPERSEDEDON = @SupersededOn,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON   = @ModifiedOn
        WHERE  CLIENTDBID = @ClientDbId
          AND  KEYSTATUS  = 0
          AND  TENANTID   = @TenantId";

    public const string REVOKE = @"
        UPDATE SW.MSWCLIENTDATABASEKEY SET
            KEYSTATUS    = 2,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON   = @ModifiedOn
        WHERE  CLIENTDATABASEKEYID = @ClientDatabaseKeyId
          AND  TENANTID            = @TenantId";
}