import pandas as pd
import numpy as np
from rank_bm25 import BM25Okapi
import re
from nltk.stem import WordNetLemmatizer, PorterStemmer
from datetime import datetime

lemmatizer = WordNetLemmatizer()

threshold = 11.6  # Threshold score for employer match

def clean_text(text):
    """
    Cleans and normalizes the input text by performing the following operations:
    - Lowercases the text
    - Removes special characters and digits
    - Replaces abbreviations with full words (e.g., 'pvt' -> 'private', 'ltd' -> 'limited')
    - Lemmatizes the words for normalization

    Parameters:
    text (str): The input text string to be cleaned.

    Returns:
    str: The cleaned and lemmatized text.
    """
    cleaned_text = text.lower()
    cleaned_text = re.sub(r'[^A-Za-z0-9\s./]', ' ', cleaned_text)  # Remove special characters
    cleaned_text = re.sub(r'\.', '', cleaned_text)  # Remove periods
    cleaned_text = re.sub(r'\/', '', cleaned_text)  # Remove slashes
    cleaned_text = re.sub(r'\d{3,}', '', cleaned_text)  # Remove numbers with more than 3 digits
    cleaned_text = re.sub('pvt', 'private', cleaned_text)  # Replace 'pvt' with 'private'
    cleaned_text = re.sub('ltd', 'limited', cleaned_text)  # Replace 'ltd' with 'limited'
    cleaned_text = re.sub(r'(?<!\w)dev(?!\w)', 'development', cleaned_text)  # Replace 'dev' with 'development'
    cleaned_text = re.sub(r'(?<!\w)co(?!\w)', 'corporation', cleaned_text)  # Replace 'co' with 'corporation'
    cleaned_text = re.sub(r'\s+', ' ', cleaned_text)  # Remove extra spaces
    cleaned_text = ' '.join([lemmatizer.lemmatize(word) for word in cleaned_text.split()])  # Lemmatize the words
    return cleaned_text.strip()

def fetch_empno(text):
    """
    Extracts 6-digit employee numbers from the input text using a regular expression.

    Parameters:
    text (str): The input text from which to extract employee numbers.

    Returns:
    list: A list of extracted 6-digit employee numbers.
    """
    return re.findall(r'\b\d{6}\b', text)

def preprocess_query(query):
    """
    Preprocesses the input query by cleaning and extracting the meaningful part of the text.
    - Removes extra data from query if certain characters ('||', '-') are present
    - Cleans the query using the `clean_text` function

    Parameters:
    query (str): The raw query text to preprocess.

    Returns:
    str: The cleaned and processed query text.
    """
    new_query = query
    # Extract part of the query after '||' or '-'
    if '||' in query:
        ind = query.find('||')
        new_query = query[ind + 2:]
    elif '-' in query:
        ind = query.find('-')
        new_query = query[ind:]
    if len(new_query) < 20:
        new_query = query  # Restore original query if extracted part is too short
    new_query = clean_text(new_query)
    return new_query

def parse_date(date_str):
    """
    Parses a date string and converts it to the format 'DD/MM/YYYY'.
    Handles multiple input date formats.

    Parameters:
    date_str (str): The input date string.

    Returns:
    str: The date formatted as 'DD/MM/YYYY'.
    """
    try:
        return datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S').strftime('%d/%m/%Y')
    except ValueError:
        try:
            return datetime.strptime(date_str, '%m/%d/%Y').strftime('%d/%m/%Y')
        except ValueError:
            return date_str.strftime('%m/%d/%Y')  # Return original string if parsing fails

def generate_df(master_data, df, employer_names):
    """
    Generates a DataFrame by combining employer information from the master data
    with transaction data from the input DataFrame.

    Parameters:
    master_data (DataFrame): The master data containing employer information.
    df (DataFrame): The input data with transaction details.
    employer_names (list): List of employer names to be matched with master data.

    Returns:
    DataFrame: A DataFrame combining transaction details with corresponding employer information.
    """
    dates = [datetime.strptime(date_str, '%d%m%y').strftime('%d/%m/%Y') for date_str in df[4]]
    bank_desc = list(df[9])
    accounts = ['NASA' if i == '713' else 'EDAS' if i == '068' else None for i in df[0]]
    credits = list(df[7])

    # Initialize lists for employer-related fields
    employer_codes, bank_statemnt_ref, account_mgr = [], [], []
    emp_province, region, industry, contributing_stts = [], [], [], []
    date_joined, termination_date, email_addr = [], [], []

    # Iterate through each employer name and retrieve details from the master data
    for name in employer_names:
        if name == "NOT FOUND":
            employer_codes.append(np.nan)
            bank_statemnt_ref.append(np.nan)
            account_mgr.append(np.nan)
            emp_province.append(np.nan)
            region.append(np.nan)
            industry.append(np.nan)
            contributing_stts.append(np.nan)
            date_joined.append(np.nan)
            termination_date.append(np.nan)
            email_addr.append(np.nan)
        else:
            tmp = master_data[master_data['Employer Name'] == name]
            if tmp.empty:
                employer_codes.append(np.nan)
                bank_statemnt_ref.append(np.nan)
                account_mgr.append(np.nan)
                emp_province.append(np.nan)
                region.append(np.nan)
                industry.append(np.nan)
                contributing_stts.append(np.nan)
                date_joined.append(np.nan)
                termination_date.append(np.nan)
                email_addr.append(np.nan)
            else:
                employer_codes.append(list(tmp['cfcf'])[-1])
                bank_statemnt_ref.append(list(tmp['Bank Statement Reference'])[-1])
                account_mgr.append(list(tmp['NASFUNDContact'])[-1])
                emp_province.append(list(tmp['Employer Province'])[-1])
                region.append(list(tmp['Region'])[-1])
                industry.append(list(tmp['Industry'])[-1])
                contributing_stts.append(list(tmp['Contributing Status'])[-1])
                date = str(list(tmp['Date Joined Plan'])[-1])
                date_joined.append(parse_date(date))
                termination_date.append(list(tmp['Termination Date'])[-1])
                email_addr.append(list(tmp['Email Addresses'])[-1])

    # Construct the final DataFrame
    res_df = pd.DataFrame({
        'Receipt Date': dates,
        'Bank Description': bank_desc,
        'Account': accounts,
        '  Credit  ': credits,
        'Employer Code': employer_codes,
        'Employer Name': employer_names,
        'Bank Statement Reference': bank_statemnt_ref,
        'Account Manager': account_mgr,
        'Employer Province': emp_province,
        'Region': region,
        'Industry': industry,
        'Contributing Status': contributing_stts,
        'Date Joined Plan': date_joined,
        'Termination Date': termination_date,
        'Email Addresses': email_addr,
        'First Name': np.nan,
        'Surname': np.nan,
        'Membership#': np.nan
    })

    return res_df

def get_res_df(master_data, df):
    """
    Retrieves the result DataFrame by matching employer names using BM25 algorithm
    and employee numbers.

    Parameters:
    master_data (DataFrame): The master data containing employer information.
    df (DataFrame): The input data with transaction details.

    Returns:
    DataFrame: A DataFrame containing matched employer data and transaction details.
    """
    # Preprocess master data
    corpus = list(master_data['Employer Name'])
    lower_case_corpus = [clean_text(name) for name in corpus]
    corpus = corpus[1:]  # Exclude the first row if it's a header
    lower_case_corpus = lower_case_corpus[1:]
    tokenized_corpus = [doc.split(' ') for doc in lower_case_corpus]
    
    bm25 = BM25Okapi(tokenized_corpus)  # BM25 model for employer name matching

    # Preprocess queries from transaction data
    queries = list(df[9])
    queries = [query[:query.rindex('-')] for query in queries]  # Extract part of the query before '-'
    empnos = [fetch_empno(text) for text in queries]
    new_queries = [preprocess_query(query) for query in queries]

    res_names, scores = [], []
    
    # Match each query to an employer
    for query, empno_arr in zip(new_queries, empnos):
        name = ""
        if len(empno_arr) != 0:
            # Try to find an employer using the employee number
            for empno in empno_arr:
                names = list(master_data[master_data['cfcf'] == empno]['Employer Name'])
                if len(names) != 0:
                    name = names[0]
                    scores.append(100)  # Perfect match with employee number
                    res_names.append(name)
                    break
        if name == "":
            # Fall back to BM25 matching if employee number fails
            tokenized_query = query.split(" ")
            name = bm25.get_top_n(tokenized_query, corpus, n=1)
            doc_score = max(bm25.get_scores(tokenized_query))
            scores.append(doc_score)
            res_names.append(name[0] if doc_score > threshold else "NOT FOUND")

    # Count the number of unmatched results
    not_found = sum(score < threshold for score in scores)

    # Generate the final result DataFrame
    res_df = generate_df(master_data=master_data, df=df, employer_names=res_names)

    return res_df

# import pandas as pd
# import numpy as np
# from rank_bm25 import BM25Okapi
# import re
# from nltk.stem import WordNetLemmatizer,PorterStemmer
# from datetime import datetime
# lemmatizer = WordNetLemmatizer()

# threshold = 11

# def clean_text(text):
#     cleaned_text = text.lower()
#     cleaned_text = re.sub(r'[^A-Za-z0-9\s./]', ' ', cleaned_text)
#     cleaned_text = re.sub(r'\.', '', cleaned_text)
#     cleaned_text = re.sub(r'\/', '', cleaned_text)
#     cleaned_text = re.sub(r'\d{3,}', '', cleaned_text)
#     cleaned_text = re.sub('pvt','private',cleaned_text)
#     cleaned_text = re.sub('ltd','limited',cleaned_text)
#     cleaned_text = re.sub(r'(?<!\w)dev(?!\w)', 'development',cleaned_text)
#     cleaned_text = re.sub(r'(?<!\w)co(?!\w)', 'corporation',cleaned_text)
#     cleaned_text = re.sub(r'\s+', ' ', cleaned_text)
#     cleaned_text = ' '.join([lemmatizer.lemmatize(word) for word in cleaned_text.split()])
#     # cleaned_text = ' '.join([stemmer.stem(word) for word in cleaned_text.split()])
#     return cleaned_text.strip()

# def fetch_empno(text):
#     return re.findall(r'\b\d{6}\b', text)

# def preprocess_query(query):
#     new_query = query
#     if '||' in query:
#         ind = query.find('||')
#         new_query=query[ind+2:]
#     elif '-' in query:
#         ind = query.find('-')
#         new_query=query[ind:]
#     if len(new_query) < 20:
#         new_query = query
#     new_query = clean_text(new_query)
#     return new_query

# def parse_date(date_str):
#     try:
#         return datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S').strftime('%d/%m/%Y')
#     except ValueError:
#         try:
#             return datetime.strptime(date_str, '%m/%d/%Y').strftime('%d/%m/%Y')
#         except ValueError:
#             return date_str.strftime('%m/%d/%Y')
        
# def generate_df(master_data, df, employer_names):
#     dates = [datetime.strptime(date_str, '%d%m%y').strftime('%d/%m/%Y') for date_str in df[4]]
#     bank_desc = list(df[9])
#     accounts = ['NASA' if i == '713' else 'EDAS' if i == '068' else None for i in df[0]]
#     credits = list(df[7])
#     employer_codes = []
#     bank_statemnt_ref = []
#     account_mgr = []
#     emp_province = []
#     region = []
#     industry = []
#     contributing_stts = []
#     date_joined = []
#     termination_date = []
#     email_addr = []
#     for name in employer_names:
#         if name=="NOT FOUND":
#             employer_codes.append(np.nan)
#             bank_statemnt_ref.append(np.nan)
#             account_mgr.append(np.nan)
#             emp_province.append(np.nan)
#             region.append(np.nan)
#             industry.append(np.nan)
#             contributing_stts.append(np.nan)
#             date_joined.append(np.nan)
#             termination_date.append(np.nan)
#             email_addr.append(np.nan)
#         else:
#             tmp = master_data[master_data['Employer Name']==name]
#             if tmp.empty:
#                 employer_codes.append(np.nan)
#                 bank_statemnt_ref.append(np.nan)
#                 account_mgr.append(np.nan)
#                 emp_province.append(np.nan)
#                 region.append(np.nan)
#                 industry.append(np.nan)
#                 contributing_stts.append(np.nan)
#                 date_joined.append(np.nan)
#                 termination_date.append(np.nan)
#                 email_addr.append(np.nan)
#             else:
#                 employer_codes.append(list(tmp['cfcf'])[-1])
#                 bank_statemnt_ref.append(list(tmp['Bank Statement Reference'])[-1])
#                 account_mgr.append(list(tmp['NASFUNDContact'])[-1])
#                 emp_province.append(list(tmp['Employer Province'])[-1])
#                 region.append(list(tmp['Region'])[-1])
#                 industry.append(list(tmp['Industry'])[-1])
#                 contributing_stts.append(list(tmp['Contributing Status'])[-1])
#                 date = str(list(tmp['Date Joined Plan'])[-1])
#                 date_joined.append(parse_date(date))
#                 termination_date.append(list(tmp['Termination Date'])[-1])
#                 email_addr.append(list(tmp['Email Addresses'])[-1])
    
#     res_df  = pd.DataFrame()
#     res_df['Receipt Date'] = dates
#     res_df['Bank Description'] = bank_desc
#     res_df['Account'] = accounts
#     res_df['  Credit  '] = credits
#     res_df['Employer Code'] = employer_codes
#     res_df['Employer Name'] = employer_names
#     res_df['Bank Statement Reference'] = bank_statemnt_ref
#     res_df['Account Manager'] = account_mgr
#     res_df['Employer Province'] = emp_province
#     res_df['Region'] = region
#     res_df['Industry'] = industry
#     res_df['Contributing Status'] = contributing_stts
#     res_df['Date Joined Plan'] = date_joined
#     res_df['Termination Date'] = termination_date
#     res_df['Email Addresses'] = email_addr
#     res_df['First Name'] = np.nan
#     res_df['Surname'] = np.nan
#     res_df['Membership#'] = np.nan

#     return res_df

# def get_res_df(master_data,df):
#     corpus = list(master_data['Employer Name'])
#     lower_case_corpus = [clean_text(name) for name in corpus]
#     corpus = corpus[1:]
#     lower_case_corpus = lower_case_corpus[1:]
#     tokenized_corpus = [doc.split(' ') for doc in lower_case_corpus]
#     bm25 = BM25Okapi(tokenized_corpus)
#     queries = list(df[9])
#     queries = [query[:query.rindex('-')] for query in queries]
#     empnos = [fetch_empno(text) for text in queries]
#     new_queries = [preprocess_query(query) for query in queries]

#     res_names = []
#     scores = []
#     for query,empno_arr in zip(new_queries,empnos):
#         name = ""
#         if len(empno_arr) != 0:
#             for empno in empno_arr:
#                 names = list(master_data[master_data['cfcf']==empno]['Employer Name'])
#                 if len(names)!=0:
#                     name=names[0]
#                     scores.append(100)
#                     res_names.append(name)
#                     break
#         if name=="":
#             tokenized_query = query.split(" ")
#             name = bm25.get_top_n(tokenized_query, corpus, n=1)
#             doc_score = max(bm25.get_scores(tokenized_query))
#             scores.append(doc_score)
#             res_names.append(name[0] if doc_score>threshold else "NOT FOUND")
#     not_found=0
#     for score in scores:
#         if score<threshold:
#             not_found+=1
#     res_df = generate_df(master_data=master_data,df=df,employer_names=res_names)

#     return res_df