namespace AdminDAL.Query.Subscription;

public static class SubscriptionQB
{
    // Requires index: IX_MSUBSCRIPTION_OBJECTTYPE (OBJECTTYPEID, OBJECTID)
    public const string GET_SUBSCRIPTIONS_BY_OBJECT = @"
        SELECT
            S.SUBSCRIPTIONID    AS SubscriptionId,
            S.OBJECTTYPEID      AS SubscriptionObjectTypeId,
            S.OBJECTID          AS SubscriptionObjectId,
            S.OUID              AS OUId,
            OU.ORGANIZATIONUNITCODE AS OUCode,
            OU.ORGANIZATIONUNITNAME AS OUName,
            S.BIZTRANSACTIONID  AS BIZTransactionId,
            BT.BIZTRANSACTIONCODE AS BIZTransactionCode,
            BT.BIZTRANSACTIONNAME AS BIZTransactionName,
            S.PARTICULARS       AS SubscriptionParticulars,
            S.USERIDS           AS SubscriptionUserIds,
            S.SORTORDER         AS SubscriptionSortOrder,
            S.VERSION           AS SubscriptionVersion,
            S.STATUS            AS SubscriptionStatus,
            S.SOURCETYPE        AS SubscriptionSourceType,
            S.CREATEDBYID       AS SubscriptionCreatedById,
            U1.USERNAME         AS SubscriptionCreatedByName,
            S.CREATEDON         AS SubscriptionCreatedOn,
            S.MODIFIEDBYID      AS SubscriptionModifiedById,
            S.MODIFIEDON        AS SubscriptionModifiedOn
        FROM   MSUBSCRIPTION S
        JOIN   MORGANIZATIONUNIT OU ON S.OUID = OU.OUID
        JOIN   MBIZTRANSACTION BT   ON S.BIZTRANSACTIONID = BT.BIZTRANSACTIONID
        LEFT JOIN MUSER U1          ON S.CREATEDBYID = U1.USERID
        WHERE  S.OBJECTTYPEID = @ObjectTypeId
          AND  S.OBJECTID     = @ObjectId
          AND  S.STATUS       = 1";

    public const string GET_SUBSCRIPTION = @"
        SELECT
            S.SUBSCRIPTIONID    AS SubscriptionId,
            S.OBJECTTYPEID      AS SubscriptionObjectTypeId,
            S.OBJECTID          AS SubscriptionObjectId,
            S.OUID              AS OUId,
            OU.ORGANIZATIONUNITCODE AS OUCode,
            OU.ORGANIZATIONUNITNAME AS OUName,
            S.BIZTRANSACTIONID  AS BIZTransactionId,
            BT.BIZTRANSACTIONCODE AS BIZTransactionCode,
            BT.BIZTRANSACTIONNAME AS BIZTransactionName,
            S.PARTICULARS       AS SubscriptionParticulars,
            S.USERIDS           AS SubscriptionUserIds,
            S.SORTORDER         AS SubscriptionSortOrder,
            S.VERSION           AS SubscriptionVersion,
            S.STATUS            AS SubscriptionStatus,
            S.SOURCETYPE        AS SubscriptionSourceType,
            S.CREATEDBYID       AS SubscriptionCreatedById,
            S.CREATEDON         AS SubscriptionCreatedOn,
            S.MODIFIEDBYID      AS SubscriptionModifiedById,
            S.MODIFIEDON        AS SubscriptionModifiedOn
        FROM   MSUBSCRIPTION S
        JOIN   MORGANIZATIONUNIT OU ON S.OUID = OU.OUID
        JOIN   MBIZTRANSACTION BT   ON S.BIZTRANSACTIONID = BT.BIZTRANSACTIONID
        WHERE  S.SUBSCRIPTIONID = @SubscriptionId";

    public const string SAVE_SUBSCRIPTION = @"
        INSERT INTO MSUBSCRIPTION (
            OBJECTTYPEID, OBJECTID, OUID, BIZTRANSACTIONID,
            PARTICULARS, USERIDS, SORTORDER, VERSION, STATUS,
            CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE
        ) OUTPUT Inserted.SUBSCRIPTIONID
        VALUES (
            @SubscriptionObjectTypeId, @SubscriptionObjectId, @OUId, @BIZTransactionId,
            @SubscriptionParticulars, @SubscriptionUserIds, @SubscriptionSortOrder,
            @SubscriptionVersion, @SubscriptionStatus,
            @SubscriptionCreatedById, @SubscriptionCreatedOn,
            @SubscriptionModifiedById, @SubscriptionModifiedOn, @SubscriptionSourceType
        )";

    public const string SAVE_SUBSCRIPTION_POSTGRESQL = @"
        INSERT INTO MSUBSCRIPTION (
            OBJECTTYPEID, OBJECTID, OUID, BIZTRANSACTIONID,
            PARTICULARS, USERIDS, SORTORDER, VERSION, STATUS,
            CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE
        ) VALUES (
            @SubscriptionObjectTypeId, @SubscriptionObjectId, @OUId, @BIZTransactionId,
            @SubscriptionParticulars, @SubscriptionUserIds, @SubscriptionSortOrder,
            @SubscriptionVersion, @SubscriptionStatus,
            @SubscriptionCreatedById, @SubscriptionCreatedOn,
            @SubscriptionModifiedById, @SubscriptionModifiedOn, @SubscriptionSourceType
        ) RETURNING SUBSCRIPTIONID";

    public const string UPDATE_SUBSCRIPTION = @"
        UPDATE MSUBSCRIPTION SET
            OBJECTTYPEID      = @SubscriptionObjectTypeId,
            OBJECTID          = @SubscriptionObjectId,
            OUID              = @OUId,
            BIZTRANSACTIONID  = @BIZTransactionId,
            PARTICULARS       = @SubscriptionParticulars,
            USERIDS           = @SubscriptionUserIds,
            SORTORDER         = @SubscriptionSortOrder,
            VERSION           = @SubscriptionVersion + 1,
            STATUS            = @SubscriptionStatus,
            MODIFIEDBYID      = @SubscriptionModifiedById,
            MODIFIEDON        = @SubscriptionModifiedOn,
            SOURCETYPE        = @SubscriptionSourceType
        WHERE SUBSCRIPTIONID = @SubscriptionId";

    public const string DELETE_SUBSCRIPTION = @"
        DELETE FROM MSUBSCRIPTION
        WHERE SUBSCRIPTIONID = @SubscriptionId";

    // Used by ObservabilityAlertEvaluatorJob to resolve recipients for a given alert
    public const string GET_USERIDS_FOR_ALERT = @"
        SELECT S.USERIDS
        FROM   MSUBSCRIPTION S
        WHERE  S.OBJECTTYPEID = 1            -- ObjectType 1 = DataAlert
          AND  S.OBJECTID     = @DataAlertId
          AND  S.STATUS       = 1";
}