from sqlalchemy import create_engine
import pandas as pd, sys, glob
import logging, time

log_file = rf"add_to_sqlite_db.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}"

engine = create_engine('sqlite:///../output/wrds_ciq_2024.db')

def add_to_sql(df,sqlEngine,sqlTable):
    try:
        with sqlEngine.begin() as connection:
            df.to_sql(sqlTable, con=connection, if_exists='append',chunksize=100000,index=False)
        log(f"Successfully added {sqlTable} to SQL database")
    except Exception as e:
        log(f"Error adding {sqlTable} to SQL databse: {e}")
        
start = time.time()
# Read in Transcript detail data
try:
    wrds_transcript_detail = pd.read_pickle("../output/wrds_transcript_detail.pkl", compression='zip')
    log("Successfully read ../output/wrds_transcript_detail.pkl.")
    wrds_gvkey = pd.read_sas("../output/wrds_gvkey.sas7bdat")
    log("Successfully read ../output/wrds_gvkey.sas7bdat.")
except Exception as e:
    log(f"Error reading file: {e}")
    sys.exit(1)

#
wrds_transcript_detail = wrds_transcript_detail[['companyid', 'keydevid', 'transcriptid', 'mostimportantdateutc', 'keydeveventtypeid', 'transcriptcollectiontypeid', 'transcriptpresentationtypeid', 'transcriptcreationdate_utc', 'transcriptcreationtime_utc']]
cols = ['companyid', 'keydevid', 'transcriptid', 
        'keydeveventtypeid', 'transcriptcollectiontypeid', 'transcriptpresentationtypeid']
for col in cols:
    try:
        wrds_transcript_detail[col] = wrds_transcript_detail[col].astype('int')
        log(f"Converted {col} to interger.")
    except:
        wrds_transcript_detail[col] = wrds_transcript_detail[col].astype('str')
        log(f"Converted {col} to string.")

# Add wrds_transcript_detail to SQL database
sqlTable = "wrds_transcript_detail"
add_to_sql(wrds_transcript_detail,engine,sqlTable)
log(wrds_transcript_detail.info())

sqlTable = "wrds_gkvey"
add_to_sql(wrds_gvkey,engine,sqlTable)
log(wrds_gvkey.info())

# Add wrds_transcript_person to SQL database
person_files = glob.glob(r'../output/wrds_transcript_person*')
sqlTable = "wrds_transcript_person"
for f in person_files:
    wrds_transcript_person = pd.read_pickle(f,compression='zip')
    wrds_transcript_person.info()
    wrds_transcript_person = wrds_transcript_person[['transcriptid', 'transcriptcomponentid', 'componentorder',
       'transcriptcomponenttypeid',
       'transcriptpersonid', 'transcriptpersonname', 'proid',
       'companyofperson', 'speakertypeid', 'word_count']]
    cols = ['transcriptid', 'transcriptcomponentid', 'componentorder',
              'transcriptcomponenttypeid', 'transcriptpersonid', 'proid', 'speakertypeid', 'word_count']
    for c in cols:
        try:
            wrds_transcript_person[c] = wrds_transcript_person[c].astype('int')
            log(f"Converted {c} to interger.")
        except:
            wrds_transcript_person[c] = wrds_transcript_person[c].astype('Int64')
            wrds_transcript_person[c] = wrds_transcript_person[c].astype('str')
            log(f"Converted {col} to string.")
    
    add_to_sql(wrds_transcript_person,engine,sqlTable)
    log(wrds_transcript_person.info())
    del wrds_transcript_person

# Add ciqtranscriptcomponent to SQL database
component_files = glob.glob(r'../output/ciqtranscriptcomponent*')
sqlTable = "ciqtranscriptcomponent"
for f in component_files:
    ciqtranscriptcomponent = pd.read_pickle(f,compression='zip')    
    add_to_sql(ciqtranscriptcomponent,engine,sqlTable)
    log(ciqtranscriptcomponent.info())
    del ciqtranscriptcomponent

# Check completeness of files
# from sqlite3 import connect
# conn = connect('../output/wrds_ciq_2024.db')
# df1 = pd.read_sql('SELECT * FROM wrds_transcript_detail', conn)
# conn.close()
log(f"Script execution completed in {print_time(start)}.")
