import pyodbc
import configparser
import sys
from psycopg2 import connect
from psycopg2.extras import execute_values

#ESTRAZIONE DATI QHST
def getQhst(connpg, conndb2, clientname):
    print("Estrazione voci da QHST")

    #PREAPARO PARAMETRI PER ESTRAZIONE
    msgids=sys.argv[2].upper().replace(",","','")
    msgids="'"+msgids+"'"

    print(f"Estrazione voci da QHST con MSGID {msgids}")

    cursorpg=connpg.cursor()
    cursordb2=conndb2.cursor()

    #VERIFICO CHE IL CLIENT SIA REGISTRATO, SE NON LO E' INTERROMPO
    sql=f"""select id_client from clients where clientname='{clientname}'"""
    cursorpg.execute(sql)
    rows=cursorpg.fetchone()

    if not rows:
        print("Client non registrato")
        return
    else:
        id_client=rows[0];

    #VERIFICO SE L'ESTRAZIONE E' GIA' STATA AVVIATA O SE NUOVA
    sql=f"""select count(*) from last_check where idf_Client='{id_client}' and isjrn=false"""
    cursorpg.execute(sql)
    rows=cursorpg.fetchone()

    if rows[0]==0:
        print(f"L'estrazione richiesta è nuova")

        #ESTRAGGO ULTIMO MSGID
        sql=f"""SELECT MAX(MESSAGE_TIMESTAMP) FROM TABLE(QSYS2.HISTORY_LOG_INFO()) X"""
        cursordb2.execute(sql)
        rows=cursordb2.fetchone()
        
        end=rows[0]

        #COMPONGO ED ESEGUO QUERY PER ESTRAZIONE VOCI
        sql=f"""SELECT {id_client} as idf_client,syslog_facility as facility, syslog_severity as severity, syslog_event as detail, 
            MESSAGE_ID as entry_msgid, MESSAGE_TIMESTAMP as entry_timestamp
            FROM TABLE(QSYS2.HISTORY_LOG_INFO(GENERATE_SYSLOG =>'RFC5424')) X 
            WHERE MESSAGE_TIMESTAMP<='{end}'"""
        
        print(f"Timestamp finale: {end}")

    else:
        print(f"Prosecuzione estrazione")

        #ESTRAGGO TIMESTAMP PARTENZA
        sql=f"""select last_ts from last_check where idf_Client='{id_client}' and isjrn=false"""
        cursorpg.execute(sql)
        rows=cursorpg.fetchone()
        begin=rows[0]

        #ESTRAGGO TIMESTAMP ARRIVO
        sql=f"""SELECT MAX(MESSAGE_TIMESTAMP) FROM TABLE(QSYS2.HISTORY_LOG_INFO()) X"""
        cursordb2.execute(sql)
        rows=cursordb2.fetchone()
        
        end=rows[0]

        #COMPONGO ED ESEGUO QUERY PER ESTRAZIONE VOCI
        sql=f"""SELECT {id_client} as idf_client,syslog_facility as facility, syslog_severity as severity, syslog_event as detail, 
            MESSAGE_ID as entry_msgid, MESSAGE_TIMESTAMP as entry_timestamp
            FROM TABLE(QSYS2.HISTORY_LOG_INFO(GENERATE_SYSLOG =>'RFC5424')) X 
            WHERE MESSAGE_TIMESTAMP<='{end}' and MESSAGE_TIMESTAMP>'{begin}'"""

        print(f"Timestamp di avvio: {begin} -> Timestamp finale: {end}")

    if sys.argv[2].upper()!='*ALL':
        sql=sql+f" and MESSAGE_ID IN ({msgids}) ";

    cursordb2.execute(sql)
    rows=cursordb2.fetchall()

    #COMPONGO QUERY PER INSERIMENTO POSTGRESQL
    sql = "INSERT INTO history_events (idf_client, facility, severity, detail, entry_msgid, entry_timestamp) VALUES %s"
    execute_values(cursorpg, sql, rows)

    #AGGIORNO TABELLA CON ULTIME LETTURE
    sql = f"""
        INSERT INTO last_check (idf_client, isjrn, last_ts)
        VALUES ('{id_client}',false,'{end}')
        ON CONFLICT (idf_client, isjrn)
        DO UPDATE SET
            last_ts = EXCLUDED.last_ts
        """
    cursorpg.execute(sql);
    connpg.commit()

    print(f"Estrazione voci da QHST con MSGID {msgids} completata correttamente")


#ESTRAZIONE VOCI JRN
def getJrn(connpg, conndb2, idrs, ci):
    print("Estrazione voci da journal")

    #PREAPARO PARAMETRI PER ESTRAZIONE
    entrytype=sys.argv[2].upper().replace(",","','")
    entrytype="'"+entrytype+"'"

    print(f"Estrazione voci da journal QSYS/QAUDJRN di tipo {entrytype}")

    cursorpg=connpg.cursor()
    cursordb2=conndb2.cursor()

    #VERIFICO CHE IL CLIENT SIA REGISTRATO, SE NON LO E' INTERROMPO
    sql=f"""select id_client from clients where clientname='{clientname}'"""
    cursorpg.execute(sql)
    rows=cursorpg.fetchone()

    if not rows:
        print("Client non registrato")
        return
    else:
        id_client=rows[0];

    #VERIFICO SE L'ESTRAZIONE E' GIA' STATA AVVIATA O SE NUOVA
    sql=f"""select count(*) from last_check where idf_Client='{id_client}' and isjrn=true"""
    cursorpg.execute(sql)
    rows=cursorpg.fetchone()

    if rows[0]==0:
        print(f"L'estrazione richiesta è nuova")

        #ESTRAGGO PRIMA E ULTIMA SEQUENZA ULTIMO JRNRCV
        sql=f"""select current timestamp from sysibm.sysdummy1"""
        cursordb2.execute(sql)
        rows=cursordb2.fetchone()
        
        end=rows[0]

        #COMPONGO ED ESEGUO QUERY PER ESTRAZIONE VOCI
        sql=f"""select {id_client} as idf_client, syslog_facility as facility, syslog_severity as severity, syslog_event as detail, 
            journal_entry_type as entry_type, ENTRY_TIMESTAMP
            FROM TABLE (QSYS2.DISPLAY_JOURNAL('QSYS', 'QAUDJRN', GENERATE_SYSLOG =>'RFC5424', ENDING_TIMESTAMP => '{end}', STARTING_RECEIVER_NAME=> '*CURRENT')) AS X  
            WHERE syslog_event IS NOT NULL"""
        print(f"Timestamp finale: {end}")

    else:
        print(f"Prosecuzione estrazione")

        #ESTRAGGO SEQUENZA DI PARTENZA
        sql=f"""select last_ts from last_check where idf_Client='{id_client}' and isjrn=true"""
        cursorpg.execute(sql)
        rows=cursorpg.fetchone()
        begin=rows[0]

        #ESTRAGGO ULTIMA SEQUENZA ULTIMO JRNRCV
        sql=f"""select current timestamp from sysibm.sysdummy1"""
        cursordb2.execute(sql)
        rows=cursordb2.fetchone()
        end=rows[0]

        #COMPONGO ED ESEGUO QUERY PER ESTRAZIONE VOCI
        sql=f"""select {id_client} as idf_client, syslog_facility as facility, syslog_severity as severity, syslog_event as detail, 
            journal_entry_type as entry_type, ENTRY_TIMESTAMP
            FROM TABLE (QSYS2.DISPLAY_JOURNAL('QSYS', 'QAUDJRN', GENERATE_SYSLOG =>'RFC5424', STARTING_TIMESTAMP => '{begin}', 
            ENDING_TIMESTAMP => '{end}', STARTING_RECEIVER_NAME=> '*CURCHAIN')) AS X  
            WHERE syslog_event IS NOT NULL and ENTRY_TIMESTAMP>'{begin}'"""
        print(f"Timestamp di avvio: {begin} -> Timestamp finale: {end}")

    if sys.argv[2].upper()!='*ALL':
        sql=sql+f" and JOURNAL_ENTRY_TYPE in ({entrytype})";

    cursordb2.execute(sql)
    rows=cursordb2.fetchall()

    #COMPONGO QUERY PER INSERIMENTO POSTGRESQL
    sql = "INSERT INTO audit_events (idf_client, facility, severity, detail, entry_type, entry_timestamp) VALUES %s"
    execute_values(cursorpg, sql, rows)

    #AGGIORNO TABELLA CON ULTIME LETTURE
    sql = f"""
        INSERT INTO last_check (idf_client, isjrn, last_ts)
        VALUES ('{id_client}',true,'{end}')
        ON CONFLICT (idf_client, isjrn)
        DO UPDATE SET
            last_ts = EXCLUDED.last_ts
        """
    cursorpg.execute(sql);
    connpg.commit()

    print(f"Estrazione voci da journal QSYS/QAUDJRN di tipo {entrytype} completata correttamente")


#LETTURA CONFIGURAZIONE
config = configparser.ConfigParser()
config.read('config.ini')

db_config = config['database']
anag = config['anag']

#CONNESSIONE A DB2
conn_str = (
    f"DRIVER={{{db_config['driver']}}};"
    f"SYSTEM={db_config['system']};"
    f"PORT={db_config['port']};"
    f"UID={db_config['uid']};"
    f"PWD={db_config['pwd']};"
    f"DATABASE={db_config['database']};"
)

try:
    conndb2 = pyodbc.connect(conn_str)
except Exception as e:
    print("❌ Errore di connessione DB2:", e)
    exit(1)

#CONNESSIONE A POSTGRESQL
try:
    connpg = connect(
    dbname = db_config['postgre_dbname'],
    user=db_config['postgre_user'],
    password=db_config['postgre_password'],
    host=db_config['postgre_host'],
    port=db_config['postgre_port']
)
except Exception as e:
    print("❌ Errore di connessionen PG:", e)
    exit(1)

if sys.argv[1].upper()=="LOG":
    getQhst(connpg, conndb2, anag['clientname'])
else:
    getJrn(connpg, conndb2,  anag['clientname'])

#CHIUSURA CONNESSIONI DATABASE
connpg.close()
conndb2.close()