CommunityRuleChatBot/chatbot/routes.py

import pandas as pd
import mysql.connector
import os
from flask_cors import CORS
from flask import (
    Blueprint, request, jsonify
)
conn = None

bp = Blueprint('routes', __name__,)

@bp.route('/chat/request', methods=('GET', 'POST'))
def handle_request():

    user_input = request.args.get('question')
    top_matches = find_closest_match(user_input)
    
    recommendations = []
    for similarity, name, ruleId in top_matches:
        finalValue = f"https://communityrule.info/create/?r={ruleId}"
        recommendations.append({
            'community': name,
            'link': finalValue,
        })

    return jsonify(recommendations)

def get_db_connection():
    global conn
    if conn is None or not conn.is_connected():
        conn = mysql.connector.connect(
            host=os.getenv('CLOUDRON_MYSQL_HOST'),
            user=os.getenv('CLOUDRON_MYSQL_USERNAME'),
            port=os.getenv('CLOUDRON_MYSQL_PORT'),
            password=os.getenv('CLOUDRON_MYSQL_PASSWORD'),
            database=os.getenv('CLOUDRON_MYSQL_DATABASE')
        )
        print("Database connection was successful")

    return conn


def find_closest_match(user_input):

    conn = get_db_connection()
    max_similarity_curr = -1
    closest_value = None
    rule_id = None
    user_tokens = user_input.split()
    results = []
    cursor = conn.cursor()
    cursor.execute("SELECT summary, modules, name, rule_id FROM rules WHERE deleted = 0")
    rows = cursor.fetchall()
    cursor.close()

    # Compare user input with each value in the compare columns
    for compare_value1, compare_value2, return_value, ruleId in rows:
        # Convert compare_value2 to string if it's a float
        if isinstance(compare_value1, float):
            compare_value1 = str(compare_value1)
        if isinstance(compare_value2, float):
            compare_value2 = str(compare_value2)

        # Split compare_value2 into tokens
        compare_tokens1 = compare_value1.split()
        compare_tokens2 = compare_value2.split()

        # Calculate similarity between user input and compare_value1 and compare_value2
        similarity1 = similarity_score(user_tokens, compare_tokens1)
        similarity2 = similarity_score(user_tokens, compare_tokens2)

        # Take the maximum similarity between similarity1 and similarity2
        max_similarity_curr = max(similarity1, similarity2)

        # Update closest value if current similarity is greater
        #if max_similarity_curr > max_similarity:
            #max_similarity = max_similarity_curr
            #closest_value = return_value
            #rule_id = ruleId
        results.append((max_similarity_curr, return_value, ruleId))
        # print(results)
    results.sort(reverse=True, key=lambda x: x[0])
    return results[:5]

def similarity_score(set1, set2):
    # Calculate the intersection of the two sets
    intersection = len(set(set1).intersection(set(set2)))
    
    # Calculate the union of the two sets
    union = len(set(set1).union(set(set2)))
    
    # Calculate the Jaccard similarity coefficient
    if union == 0:
        return 0
    else:
        return intersection / union