from db import fetch_db_rows_as_dicts
import google.generativeai as genai
import json
import os
import pandas as pd

GOOGLE_API_KEY= os.getenv('GEMINI_API_KEY')
genai.configure(api_key=GOOGLE_API_KEY)
model = genai.GenerativeModel(model_name = "gemini-pro")

def load_json_from_string(json_string):
    try:
        data = json.loads(json_string)
        return data
    except json.JSONDecodeError as e:
        print(f"Error decoding JSON: {e}")
    except Exception as e:
        print(f"An error occurred: {e}")

def concatenate_keys(keys):
    concatenated_string = ""
    for i, d in enumerate(keys, start=1):
        concatenated_string += f"{i}. {d}"
    print('##########################')
    print(concatenated_string.strip())
    return concatenated_string.strip()

def transform_to_dict_of_dicts(columns, rows):
    # Initialize the result dictionary
    result = {}

    # Iterate over each row
    for row in rows:
        #print(dict(row))
        # The first element of the row is the key for the outer dictionary
        outer_key = row[0].strip()
        
        # Initialize the inner dictionary
        inner_dict = {}
        
        # Iterate over the rest of the elements in the row
        for i, value in enumerate(row[1:], start=1):
            # The corresponding column name is the key for the inner dictionary
            inner_key = columns[i].strip()
            # Add the key-value pair to the inner dictionary
            inner_dict[inner_key] = value
        
        # Add the inner dictionary to the result dictionary with the outer key
        result[outer_key] = inner_dict

    return result


def transform_topologies_to_dict(columns, rows):
    # Initialize the result dictionary
    result = {}

    # Iterate over each row
    for row in rows:
        #print(dict(row))
        # The first element of the row is the key for the outer dictionary
        outer_key = row[0].strip()
        
        # Initialize the inner dictionary
        inner_dict = {}
        
        # Iterate over the rest of the elements in the row
        for i, value in enumerate(row[1:], start=1):
            # The corresponding column name is the key for the inner dictionary
            inner_key = columns[i].strip()
            # Add the key-value pair to the inner dictionary
            inner_dict[inner_key] = value
        
        # Add the inner dictionary to the result dictionary with the outer key
        result[outer_key] = inner_dict

    return result

def listNeeds(tableName, dbName='data.sqlite'):
    needs, rows = fetch_db_rows_as_dicts(dbName, tableName)
    needsDict = transform_to_dict_of_dicts(needs, rows)
    return list(needsDict.keys()), needsDict

def findTop3MoneyNeeds(proposition):
    moneyNeeds, rows = fetch_db_rows_as_dicts('data.sqlite', 'money_needs')
    moneyNeedsDict = transform_to_dict_of_dicts(moneyNeeds, rows)
    #print(list(moneyNeedsDict.keys()))
    needs = findTop3Needs(proposition, list(moneyNeedsDict.keys()))
    needDictIndexes = []
    for need in needs:
        needDictIndexes.append(moneyNeedsDict[need])

    #print(needDictIndexes)
    return needs, needDictIndexes

def findTop3CustomerExperienceNeeds(proposition):
    moneyNeeds, rows = fetch_db_rows_as_dicts('data.sqlite', 'customer_exp')
    moneyNeedsDict = transform_to_dict_of_dicts(moneyNeeds, rows)
    #print(list(moneyNeedsDict.keys()))
    needs = findTop3Needs(proposition, list(moneyNeedsDict.keys()))
    needDictIndexes = []
    for need in needs:
        needDictIndexes.append(moneyNeedsDict[need])

    #print(needDictIndexes)
    return needs, needDictIndexes


def findTop3SustainabilityNeeds(proposition):
    print(" Proposition sustain  = {}".format(proposition))
    allNeeds, rows = fetch_db_rows_as_dicts('data.sqlite', 'sustainability')
    needsDict = transform_to_dict_of_dicts(allNeeds, rows)
    
    needs = findTop3Needs(proposition, list(needsDict.keys()))
    needDictIndexes = []
    print(list(needsDict.keys()))
    for need in needs:
        needDictIndexes.append(needsDict[need])

    print(needDictIndexes)
    return needs, needDictIndexes


def findTop3Needs(proposition, needs):
    
    needsString = concatenate_keys(needs)

    prompt = '''You have this comma separated listed needs of customers
    {}

    Now given a proposition 
    "{}" 
    
    Find the best 3 strings out of the above numbered list which best matches this proposition. Return in output only the number next to the matching string strictly only in json under a list called matches
    '''

    needsPrompt = prompt.format(needsString, proposition)
    print(needsPrompt)
    response = model.generate_content([needsPrompt])
    output = response.text
    output = output.replace('```json', '')
    output = output.replace('```', '')
    obj = load_json_from_string(output)
    print(obj)

    needsIndexes = [needs[int(idx)-1] for idx in obj['matches']]
    return needsIndexes #obj['matches']


def findTop3Topologies(proposition, demographic):

    topologies = pd.read_csv('topologies_desc.csv',  encoding = "ISO-8859-1")

    topologies = topologies.dropna(axis=1, how='all')
    
    topologyAttributes = topologies['Column1']
    topologyNames = list(topologies.columns)
    topologyNames.remove('Column1')

    #print(" topologyNames = {} ", topologyNames)
    
    topologyDetails = {}

    for name in topologyNames:
        topologyDetails[name] = {}
        for attribute in topologyAttributes:
            topologyDetails[name][attribute] = topologies[name][pd.Index(topologies['Column1']).get_loc(attribute)]
            
    prompt = '''You have these listed topology names of a demographic in comma separated values below
    {}

    Now for each of these above topologies here are the details
    {}

    Now given a proposition details below
    
    {}

    and given a demographic details below

    {}
    
    Find the best 3 common strings out of the topology names which matches the proposition and the demographic the most. Return output strictly only in json under a list called matches
    '''

    topologyPrompt = prompt.format(", ".join(topologyNames), str(topologyDetails), proposition, demographic)
    response = model.generate_content([topologyPrompt])
    output = response.text
    output = output.replace('```json', '')
    output = output.replace('```', '')
    obj = load_json_from_string(output)
    print(obj)
    return obj['matches'], topologyDetails


def generatePropositionExample(productName, selectedProduct, moneyNeeds, customerExperience, sutainabilityNeeds):

    proposal = '''You are a business sales professional who can form propostion summary of 100 words based upon the details.
    Please take the below details and summarize a propostion in less than 100 words.

    product name = {}
    
    product type = {}

    money needs of customer which this product is supposed to target = {}

    Customer experience needs which our company will provide = {}

    Sustainability needs which our product takes care of = {}
    '''
    proposal = proposal.format(productName, selectedProduct, moneyNeeds, customerExperience, sutainabilityNeeds)
    response = model.generate_content([proposal])
    return response.text


# def findTop3Needs(proposition, moneyNeeds):
    
#     moneyNeedsString = concatenate_keys(moneyNeeds)
#     print(moneyNeedsString)

#     prompt = '''You have these listed needs of customers
#     {}

#     Now given a proposition 
#     "{}" 
    
#     Find the best 3 strings out of the list which matches this proposition. Return output strictly only in json under a list called matches
#     '''

#     moneyNeedsPrompt = prompt.format(moneyNeedsString, proposition)
#     response = model.generate_content([moneyNeedsPrompt])
#     output = response.text
#     output = output.replace('```json', '')
#     output = output.replace('```', '')
#     obj = load_json_from_string(output)
#     print(obj)
#     return obj['matches']


# findTop3Topologies('We have a product for family people giving them discounts and low interest loans for home appliances. They can pay us back in small instalments over the course of 4 years',
#                    'CharlesTown city people are young families people mostly with a population of 20000. Out of this 65% are between the age of 30-45. Most of them have kids aged between 0-15')

#findTop3SustainabilityNeeds('We support Home appliances are all electric and use no fuel based energy')

#We provide a credit card which gives 10% discount on purchasing home appliances and also provides low interest rates based loans

#customer need - We provide our customer with utmost comfort and at home service

# subscriber take out

# 250 and below with a negative factor of 2.0
# 260 with a negative factor of 1.8
# 270 with a negative factor of 1.6
# 280 with a negative factor of 1.0
# 300 with a factor of 1
# 310 with a factor of 1.2
# 320 with a factor of 1.4
# 340 with a factor or 1.5
# 360+ with a factor of 2.0