import pandas as pd, logging

# Configure logging
log_file = rf"../logs/0_2_combine_q_a.txt"
logging.basicConfig(
    filename=log_file, 
    filemode='w',  # This will replace the existing 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)

try:
    df_ans = pd.read_pickle(rf'../data/answers_parsed.pkl', compression='zip')
    df_ans = df_ans[['transcriptid', 'transcriptcomponentid', 'transcriptcomponenttypeid', 'word_count', 'group_id', 'nonanswer', 'refuse', 'unable', 'aftercall']]
    df_ans['word_count'] = df_ans['word_count'].replace('<NA>', pd.NA)
    df_ans['word_count'] = df_ans['word_count'].astype('Int64')
    log("Successfully loaded and filtered answers_parsed.pkl")
except Exception as e:
    log(f"Error loading answers_parsed.pkl: {e}")
    raise

try:
    df_q = pd.read_pickle(rf'../data/questions.pkl', compression='zip')
    df_q = df_q[['transcriptid', 'transcriptcomponentid', 'transcriptcomponenttypeid', 'word_count', 'group_id']]
    df_q['word_count'] = df_q['word_count'].replace('<NA>', pd.NA)
    df_q['word_count'] = df_q['word_count'].astype('Int64')
    log("Successfully loaded and filtered questions.pkl")
except Exception as e:
    log(f"Error loading questions.pkl: {e}")
    raise

try:
    df_t = pd.read_pickle(rf'../data/transcripts.pkl', compression='zip')
    cols = ['transcriptid', 'transcriptcomponentid', 'transcriptcomponenttypeid', 'transcriptpersonid', 'proid', 'word_count', 'mostimportantdateutc', 'companyname_x', 'companyid0', 'gvkey0']
    df_t = df_t[cols]
    df_t['word_count'] = df_t['word_count'].replace('<NA>', pd.NA)
    df_t['word_count'] = df_t['word_count'].astype('Int64')
    log("Successfully loaded and filtered transcripts.pkl")
except Exception as e:
    log(f"Error loading transcripts.pkl: {e}")
    raise

# Merge transcriptpersonid, proid, and to df_q and df_ans
log(f"Merging transcript details with questions and answers")
df_q = df_q.merge(df_t[['transcriptid','transcriptcomponentid','transcriptpersonid','proid']], on=['transcriptid','transcriptcomponentid'], how='left')
df_ans = df_ans.merge(df_t[['transcriptid','transcriptcomponentid','transcriptpersonid','proid']], on=['transcriptid','transcriptcomponentid'], how='left')
log(f"Merged data: df_q has {len(df_q)} rows, df_ans has {len(df_ans)} rows")

# Calculate word counts for Q&A session
log("Processing Q&A sections")
df_t = df_t.drop_duplicates(subset=['transcriptid','transcriptcomponentid'])
df_qa = df_t[~df_t['transcriptcomponenttypeid'].isin([1,2,5])]
df_qa = df_qa.groupby('transcriptid')['word_count'].sum().reset_index()
df_qa = df_qa.rename(columns={'word_count':'word_count_qa'})
df_t = df_t.merge(df_qa[['transcriptid','word_count_qa']], on='transcriptid', how='left')
df_e = df_t[['transcriptid','gvkey0','mostimportantdateutc','companyid0','word_count_qa']].drop_duplicates()
log(f"Processed transcript data: {len(df_t)} rows")

# Merge answers to questions
df = df_q.merge(df_ans, 'left', on=['transcriptid','group_id'], suffixes=('_q','_ans'))
df_glz = df_e.merge(df, 'left', on='transcriptid')

# Scale word count by Q&A word count
df_glz['word_count_ans_scaled'] = df_glz['word_count_ans']/df_glz['word_count_qa']
df_glz['qa_min'] = df_glz['word_count_qa'] / 150

df_glz[['word_count_ans','word_count_ans_scaled','qa_min']].describe()
log(f"Processed df_glz: {len(df_glz)} rows")
# Drop missing GVKEY
df_glz = df_glz.dropna(subset=['gvkey0'])
log(f"Processed df_glz: {len(df_glz)} rows")

# Save output
try:
    df_glz.to_csv(rf'../data/glz.csv', index=False)
    log("Successfully saved glz.csv")
except Exception as e:
    log(f"Error saving glz.csv: {e}")
    raise

log(f"{df_glz.info()}")
log("Script execution completed.")
