import pandas as pd, logging, time, os, sys
sys.path.append(r'modules')

import conversions as cv
from sqlite_addons import create_connection

sqlPath = r'C:/Users/jaredflake/Dropbox/Research/Data/CIQ_Transcripts/2024/output/wrds_ciq_2024.db'
ciqPath = r"C:/Users/jaredflake/Dropbox/Research/Data/CIQ_Transcripts/2024/output"

# Configure logging
log_file = rf"../logs/1_1_capiq_prep_files.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()

try:
    # Read in data
    log("Reading input data files...")
    capiq_events = pd.read_pickle(os.path.join(ciqPath,'wrds_transcript_detail.pkl'), compression='zip')
    wrds_gvkey = cv.readsas(os.path.join(ciqPath,'wrds_gvkey.sas7bdat'))
    wrds_professional_small = cv.readsas(os.path.join(ciqPath,'wrds_professional_small.sas7bdat'))
    ciqtranscriptperson = cv.readsas(os.path.join(ciqPath,'ciqtranscriptperson.sas7bdat'))
    log("Input data files read successfully.")

    # Filter transcripts
    log("Filtering for final presentation transcripts with valid company IDs...")
    capiq_events = capiq_events[
        (capiq_events['companyid'].notna()) & 
        (capiq_events['transcriptpresentationtypename'] == "Final")
    ]

    capiq_events['year'] = pd.to_datetime(capiq_events['mostimportantdateutc']).dt.year
    log("Extracted 'year' from 'mostimportantdateutc'")

    capiq_events = capiq_events.sort_values(
        by=['companyid', 'keydevid', 'transcriptcreationdate_utc', 'transcriptcreationtime_utc'],
        ascending=[True, True, False, False]
    )
    log("Sorted transcripts to keep newest version")

    capiq_events = capiq_events.drop_duplicates(subset=['companyid', 'keydevid'], keep='first')
    log("Dropped duplicate transcripts per event")

    capiq_events = capiq_events[['companyid', 'transcriptid', 'year', 'mostimportantdateutc']]
    capiq_events = capiq_events.merge(wrds_gvkey[['companyid', 'gvkey']], how='left', on='companyid')
    capiq_events = capiq_events.loc[capiq_events['gvkey'].notna(), :]
    log(f"Filtered events with valid gvkeys. Remaining rows: {len(capiq_events)}")

    transcriptids = capiq_events['transcriptid'].unique()
    # convert decimal to integer
    transcriptids = [int(x) for x in transcriptids]
    log(f"Number of unique transcript IDs: {len(transcriptids)}")

    # Query transcripts with analyst data
    log("Querying analyst data from database...")
    conn = create_connection(sqlPath)
    transcripts = pd.read_sql_query(
        f"""SELECT transcriptid, transcriptpersonid, proid, 
                   transcriptcomponenttypeid, speakertypeid 
            FROM wrds_transcript_person 
            WHERE transcriptid IN {tuple(transcriptids)} 
              AND transcriptcomponenttypeid=3 
              AND speakertypeid=3""", conn
    )
    conn.close()
    log(f"Retrieved {len(transcripts)} analyst records from database")

    capiq_events['transcriptid'] = capiq_events['transcriptid'].astype(int)
    analysts = capiq_events[['companyid','transcriptid','gvkey','year']].merge(transcripts, how='inner', on='transcriptid')
    length = len(analysts)
    analysts = analysts.drop_duplicates(subset=['transcriptid', 'transcriptpersonid'])
    log(f"Merged analyst data with event data and dropped duplicate analyst-transcript observations. Remaining rows: {len(analysts)} out of {length}")

    # Convert float to integer before merging
    ciqtranscriptperson.transcriptpersonid.astype('int')
    analysts = analysts.merge(
        ciqtranscriptperson[['transcriptpersonid', 'transcriptpersonname','companyname']],
        how='left', on='transcriptpersonid'
    )

    wrds_professional_small['proid'] = wrds_professional_small['proid'].astype('int')
    analysts['proid'] = analysts['proid'].replace('<NA>', pd.NA)
    analysts['proid'] = analysts['proid'].astype('Int64')
    analysts = analysts.merge(
        wrds_professional_small[['proid', 'companyname']],
        how='left', on='proid',
        suffixes=('','_pro')
    )

    del ciqtranscriptperson, wrds_professional_small
    log("Merged analyst/company names and cleaned up temp variables")

    analysts['companyofperson'] = analysts['companyname'].fillna(analysts['companyname_pro'])
    analysts = analysts.drop(columns=['companyname','companyname_pro'])
    log("Created 'companyofperson' and dropped redundant columns")

    # reduce to unique firm-analyst-year observations 
    length = len(analysts)
    analysts = analysts.drop(columns=['transcriptid']).drop_duplicates(
        subset=['gvkey', 'transcriptpersonid', 'companyofperson', 'year']
    )
    log(f"Reduced to unique firm-analyst-year observations. Remaining rows: {len(analysts)} out of {length}")

    analysts = analysts[
        ~analysts['transcriptpersonname'].isin(
            ["Unknown Analyst", "Unkown Analyst", "Unidentified Audience Member"]
        )
    ]
    log(f"Filtered out unidentified analysts. Remaining rows: {len(analysts)}")

    analysts.to_pickle('../data/CIQ/CIQAnalystFirmYear.pkl', compression='zip')
    log(f"Saved CIQAnalystFirmYear.pkl with {len(analysts)} rows and {len(analysts.columns)} columns")

    ciq_analysts = analysts[['transcriptpersonid', 'transcriptpersonname', 'companyofperson']].drop_duplicates()
    ciq_analysts.to_csv('../data/CIQ/CIQAnalysts.csv', index=False)
    log(f"Saved CIQAnalysts.csv with {len(ciq_analysts)} rows and {len(ciq_analysts.columns)} columns")

    ciq_brokers = analysts[['companyofperson']].drop_duplicates()
    ciq_brokers = ciq_brokers[ciq_brokers['companyofperson'] != ""]
    ciq_brokers.to_csv('../data/CIQ/CIQBrokers.csv', index=False)
    log(f"Saved CIQBrokers.csv with {len(ciq_brokers)} rows and {len(ciq_brokers.columns)} columns")

except Exception as e:
    logging.error(f"An error occurred: {e}", exc_info=True)
