# Parse analyst names from Capital IQ and IBES datasets and reformat to prepare to merge on analyst name

import pandas as pd
import re, sys, string, os
import numpy as np
import logging, time
sys.path.append(r'modules')
import conversions as cv

# Configure logging
log_file = rf"../logs/1_2_ibes_capiq_format_names.txt"
logging.basicConfig(
    filename=log_file, 
    level=logging.INFO, 
    format='%(asctime)s - %(levelname)s - %(message)s',
    datefmt='%Y-%m-%d %H:%M:%S'
)

def log(msg):
    print(msg)
    logging.info(msg)

def print_time(start):
    secs = time.time() - start
    s = int(secs % 60)
    m = int(secs / 60 % 60)
    h = int(secs / 3600)
    return f"{h:0>2}:{m:0>2}:{s:0>2}"

start = time.time()

# Define functions to clean IBES and CIQ analyst names to prepare for match
def get_names_ciq(s,last=True):
    delim = re.compile(r'( of )|(;)')
    rmv = re.compile(r'[\(?\)-]|III|II|IV|Jr\.|Sr\.|,.*|from.*|with.*|–.*| - .*')
    dblnms = re.compile(r'(Van Den|Van|[dD][ei]n?)\s+\w*')
    s = re.sub(r'\d+', '', str(s)) 
    if str(s).strip().endswith("."):
        s = re.sub(r'\.',"",s)
    else:
        s = s
    nm = re.sub(rmv,"",str(s))
    nm2 = re.split(delim,nm)[0].split(" for ")[-1]
    try:
        if last:
            if re.search(dblnms,nm2):
                ln = re.search(dblnms,nm2).group()
            else:
                if re.search(r'\.',nm2):
                    nm2 = nm2.split(".")[-1]
                else:
                    nm2 = nm2.translate(str.maketrans('', '', string.punctuation))
                ln = nm2.split()[-1]
            ln = re.sub(r"\W","",ln)
            ln = ln.encode("ascii", errors="ignore").decode()
            return ln.upper()
        else:
            fn = nm2.split()[0]
            fn = re.sub(r"\W","",fn)
            fn = fn.encode("ascii", errors="ignore").decode()
            return fn.upper()
    except Exception as e:
        log(f"Error processing CIQ name '{s}': {e}")
        return "Error"
    
def get_names_ibes(s):
    rmv = re.compile(r'(,|III|II|IV|JR|SR|\s{3,}\w).*')
    try:
        if len(s.split("/")) > 1:
            strs = s.split("/")
            s1 = re.sub(rmv,"",strs[0])
            s2 = re.sub(rmv,"",strs[1])
            return s1.upper(),s2.upper()
        else:
            if len(s.split()) == 1: 
                s = s
            else: 
                s = s[:-1].strip()
            s = re.sub(rmv,"",s)
            s = re.sub(r"\W","",s)
            return s.upper(),""
    except Exception as e:
        log(f"Error processing IBES name '{s}': {e}")
        return "Error","Error"

# Define file paths
filePath = r"../data/CIQ/"
ciqFileName = filePath + "CIQAnalystFirmYear.pkl"
ibesFileName = filePath + "ibesanalystfirmyear.sas7bdat"

# Read in files
try:
    log("Loading CIQ and IBES datasets...")
    ciq = pd.read_pickle(ciqFileName, compression='zip')
    log(f"Loaded CIQ dataset with {len(ciq)} rows.")
except Exception as e:
    log(f"Error loading CIQ dataset: {e}")
    sys.exit(1)

try:
    log("Loading CIQ and IBES datasets...")
    ibes = cv.readsas(ibesFileName)
    log(f"Loaded IBES dataset with {len(ibes)} rows.")
except Exception as e:
    log(f"Error loading IBES dataset: {e}")
    sys.exit(1)



# Validate function to properly format names - extract only the last name and make it all capitalized to match analysts' last name in IBES.
log("Testing name parsing functions with sample names.")
ciq_nms = ['Alex Brand','(Actavian Popescu?)','Adam B. Frisch',
           'Analyst for Bev Dijinson','Bonnie K.Wachtel','Brooks O’Neil',
           'John','Mark R. Gulley','P. J. Juvekar','Patrick J. Forkin III',
           'K.C. Ambrecht','Vern Essi, Jr.','Judson\xa0E.\xa0Bailey',
           'E. Russell McAllister from Merriman','Joseph B. Nadol, III',
           'Eric J. Grubelich, CFA','Robert McCarthy, Jr.', 'J. Van Rhee',
           'C. Todd Maiden','John Yurning(?)','Kim De Paoli',
           'Morton Oâ€™Tool Smith',"Shannon O'Callaghan",
           "Daniel Oâ€™Sullivan","Allan Gould for Jeff Shelton",
          "Oscar Cabrera with Goldman Sachs","Irit Elrad-Jakoby",
          "Erika Penala -","William Di Tullio","Operator.	",
          "Saravanan V.N.","Dmitry Trembovolsky – Goldman Sachs Group Inc....	",
          "Spencer Wang – Credit Suisse	","[ph] Sutanto Widjaja (58:02)	",
          "Jason Late - Ares Management Limited; Managing...	",
          "Robert Van Den Broek","Jason L.;Loews Corporation;Analyst"]
ibes_nms = ["PRICE/PRICE   J","GARCIA-CANTER J","DE VERTEUIL   I",
            "DEATHERAGE/MASSICK","BOSSHARD      E","MCALENNEY,JR. E",
            "WELLS III     H","NICKLIN","LEISURE-TOURI","HUNTER BYRNE  M",
            "WOLFENBERGER  M","VAN DEN BROEK R","YOUNG/C. BARN",
            "O'BRIEN       K","WARNER-WILKE  C","MOUSSATOS, PH.D.   L"]

print([get_names_ciq(s,last=True) for s in ciq_nms])
log(f"CIQ test names (last names): {[get_names_ciq(s,last=True) for s in ciq_nms]}")
print([get_names_ibes(s) for s in ibes_nms])
log(f"IBES test names: {[get_names_ibes(s) for s in ibes_nms]}")


# Reformat analysts' names to prepare for match between Capital IQ and IBES
log("Extracting names from CIQ and IBES datasets...")
ciq['fname'] = ciq.transcriptpersonname.apply(lambda x: get_names_ciq(x,last=False))
ciq['lname'] = ciq.transcriptpersonname.apply(get_names_ciq)
ibes['lname1'] = ibes.analyst.apply(lambda x: get_names_ibes(x)[0])
ibes['lname2'] = ibes.analyst.apply(lambda x: get_names_ibes(x)[1])

ciq2 = ciq[['transcriptpersonid','proid','companyofperson','year','gvkey','fname','lname']]
ibes2 = ibes[['ticker','estimid','amaskcd','year','permno','gvkey','analyst','lname1','lname2']]

log("Writing formatted name files to CSV...")
ciq2.to_csv(filePath+r"ciqAFY_FmtdNms.csv",index=False,encoding='utf8')
ibes2.to_csv(filePath+r"ibesAFY_FmtdNms.csv",index=False,encoding='Latin-1')

# Print variable lengths for merge back in SAS.
measurer = np.vectorize(len)
ciqLen = measurer(ciq2.values.astype(str)).max(axis=0)
ibesLen = measurer(ibes2.values.astype(str)).max(axis=0)

log("CIQ column lengths:")
for l,col in zip(ciqLen,ciq2.columns):
    print(col,": ",l)
    
log("IBES column lengths:")
for l,col in zip(ibesLen,ibes2.columns):
    print(col,": ",l)

error_count_ciq = len(ciq2[ciq2.lname == "Error"])
log(f"Number of CIQ names that failed to parse: {error_count_ciq}")
error_count_ibes = len(ibes2[(ibes2.lname1 == "Error") | (ibes2.lname2 == "Error")])
log(f"Number of IBES names that failed to parse: {error_count_ibes}")

log(f"Total runtime: {print_time(start)}")