try:

    from datetime import timedelta, datetime
    from airflow import DAG
    
    from airflow.operators.python_operator import PythonOperator
    from airflow.operators.http_operator import SimpleHttpOperator
    from datetime import datetime
    from pandas.io.json import json_normalize
    from airflow.operators.postgres_operator import PostgresOperator

    import pandas as pd
    import json
    import requests
    import numpy as np

    import psycopg2
    from sqlalchemy import create_engine
    #print("All Dag moudules are sucessfully imported")

except Exception as e:
    print("Error {} ".format(e))

dRoW_api_end_url = "https://uat2.drow.cloud"

def getDrowToken(**context):
    # response = SimpleHttpOperator(
    #     task_id="getDrowToken",
    #     http_conn_id="getDrowToken",
    #     endpoint="https://uat2.drow.cloud/api/auth/authenticate", 
    #     method="POST",
    #     data={
    #     "username": "icwp2@drow.cloud",
    #     "password": "dGVzdDAxQHRlc3QuY29t"
    #     },
    #     xcom_push=True,
    # )

    response = requests.post(
    url=f"{dRoW_api_end_url}/api/auth/authenticate",
    data={
    "username": "icwp2@drow.cloud",
    "password": "dGVzdDAxQHRlc3QuY29t"
    }
    ).json()
    context["ti"].xcom_push(key="token", value=response['token'])
    # return 'DLLM{}'.format(response)


def getMongoDB(**context):
    token = context.get("ti").xcom_pull(key="token")
    response = requests.get(
    url=f"{dRoW_api_end_url}/api/module/nd201907/document-data?from=1665845580212&documentId=5fc46755b52b8f7ffa7b5ff7",
    headers={
    "x-access-token": f"Bearer {token}",
    "ICWPxAccessKey": "nd@201907ICWP_[1AG:4UdI){n=b~"
    }
    )
    #print('got_data')
    RISC_Data = json.loads(response.text)
    Mapping= {
    "A01 Date" : "a01_date",

    # "1. General_compelete": "general_complete",
    # "1. General_incompelete": "general_incomplete",
    # "2. Flammable Liquids / Gases_compelete": "flammable_liquids_gases_complete",
    # "2. Flammable Liquids / Gases_incompelete": "flammable_liquids_gases_incomplete",
    # "3. Hazardous Substances_compelete": "general_complete",
    # "3. Hazardous Substances_incompelete": "general_incomplete",
    }
    # safety_cats = [
    #     '2. Reportable Accident',
    #     '12. Unsafe conditions identified during inspections',
    #     '13. Near-miss Reports',
    #     '14. Incident Reports',
    #     '15. LD/MD Improvement and Suspension Notice',
    #     '16. Safety Convictions Records'
    # ]

    #print('start transform')
    host                  = 'drowdatewarehouse.crlwwhgepgi7.ap-east-1.rds.amazonaws.com'  

        # User name of the database server
    dbUserName            = 'dRowAdmin'  

    # Password for the database user
    dbUserPassword        = 'drowsuper'  

    # Name of the database 
    database              = 'drowDateWareHouse'

    # Character set
    charSet               = "utf8mb4"  

    port                  = "5432"

    # #cursor Type
    # cusrsorType            = pymysql.cursors.DictCursor



    #create_engine('mysql+mysqldb://root:password@localhost:3306/mydbname', echo = False)
    conn_string = ('postgres://' +
                           dbUserName + ':' + 
                           dbUserPassword +
                           '@' + host + ':' + port +
                           '/' + database)

    # df = context.get("ti").xcom_pull(key="InsertData")
    # print(df)
    # conn_string = 'postgres://user:password@host/data1'
    
    db = create_engine(conn_string)
    conn = db.connect()
    #print('db connected')
    with conn as conn:
        df3=pd.read_sql("SELECT * FROM public.nd201907_site_diary_activities_general_count;",
            conn,
            parse_dates=["a01_date"])
        df = pd.DataFrame()
        _df = pd.DataFrame()
        for x in RISC_Data:
            df_nested_list = json_normalize(x['data'])
            #print('process 1')
            df2 = df_nested_list.reindex(columns=Mapping.keys())
            date = datetime.strptime(x['data']['A01 Date'], '%Y-%m-%dT%H:%M:%S.%f%z')
            no_of_site_activities = df3.query('a01_date == @date')
            # if(not pd.isna(no_of_site_activities['Count'][0])):
            if(len(no_of_site_activities['Count'])!= 0):
                df2['no_of_site_activities'] = no_of_site_activities['Count'].iloc[0]
            else:
                df2['no_of_site_activities'] = 0
            # df2=df_nested_list
            if (datetime.strptime(x['data']['A01 Date'], '%Y-%m-%dT%H:%M:%S.%f%z') < datetime.strptime('2022-10-01T00:00:00.000Z', '%Y-%m-%dT%H:%M:%S.%f%z')):
                df2['complete_or_incomplete'] = 'complete'
            else :
                if x['Status'] == 'completed' :
                    df2['complete_or_incomplete'] = 'complete'
                else:
                    df2['complete_or_incomplete'] = 'incomplete'

            if len(x['ApproveLogSummary']) > 0:
                pmd_sign_date = [data for data in x['ApproveLogSummary'] if data.get('statusName')=="D : Sent to SupD"]
                contractor_sign_date = [data for data in x['ApproveLogSummary'] if data.get('statusName')=="C : Sent to Contractor"]
                supervisor_sign_date = [data for data in x['ApproveLogSummary'] if data.get('statusName')=="B : IOW Vet SD"]
            if len(pmd_sign_date) > 0 and ('to' in pmd_sign_date[len(pmd_sign_date)-1]):
                pmd_receive_time = pmd_sign_date[len(pmd_sign_date)-1]['from']
                pmd_sign_time = pmd_sign_date[len(pmd_sign_date)-1]['to']
                df2['pmd_sign_time'] = pmd_sign_date[len(pmd_sign_date)-1]['to']
                df2['Overdue_PMD']= (pmd_sign_time != '' and pmd_receive_time != '' and (datetime.strptime(pmd_sign_time, '%Y-%m-%dT%H:%M:%S.%f%z') -  datetime.strptime(pmd_receive_time, '%Y-%m-%dT%H:%M:%S.%f%z')).days > 7)
            else: 
                df2['pmd_sign_time'] = None
                df2['Overdue_PMD'] = None

            if len(contractor_sign_date) > 0 and ('to' in contractor_sign_date[len(contractor_sign_date)-1]):
                cr_receive_time = contractor_sign_date[len(contractor_sign_date)-1]['from']
                cr_sign_time = contractor_sign_date[len(contractor_sign_date)-1]['to']
                df2['cr_sign_time'] = contractor_sign_date[len(contractor_sign_date)-1]['to']
                df2['Overdue_CR']= (cr_sign_time != '' and cr_receive_time != '' and (datetime.strptime(cr_sign_time, '%Y-%m-%dT%H:%M:%S.%f%z') -  datetime.strptime(cr_receive_time, '%Y-%m-%dT%H:%M:%S.%f%z')).days > 7)
            else: 
                df2['cr_sign_time'] = None
                df2['Overdue_CR'] = None

            if len(supervisor_sign_date) > 0 and ('to' in supervisor_sign_date[len(supervisor_sign_date)-1]):
                sup_receive_time = supervisor_sign_date[len(supervisor_sign_date)-1]['from']
                sup_sign_time = supervisor_sign_date[len(supervisor_sign_date)-1]['to']
                df2['sup_sign_time'] = supervisor_sign_date[len(supervisor_sign_date)-1]['to']
                df2['Overdue_SUP']= (sup_sign_time != '' and sup_receive_time != '' and (datetime.strptime(sup_sign_time, '%Y-%m-%dT%H:%M:%S.%f%z') -  datetime.strptime(sup_receive_time, '%Y-%m-%dT%H:%M:%S.%f%z')).days > 7)
            else: 
                df2['sup_sign_time'] = None
                df2['Overdue_SUP'] = None
            
            #print('process 2')

            #print('loading into DB')
            # if len(x['data']["A04 Contractor's Management Team"]) > 0:
            #     for c in x['data']["A04 Contractor's Management Team"]:
            #         labourName = str(c['A04.1 Ctr Post'])
            #         labourNum = 0
            #         if ('A04.2 Ctr No.' in c) and not c['A04.2 Ctr No.'] is None:
            #             labourNum = c['A04.2 Ctr No.']
            #         else:
            #             labourNum = 0
            #         if not ((str('A04.1 Ctr Post' + '_' + labourName)) in df2.columns):
            #             df2.at[0, str('A04.1 Ctr Post' + '_' + labourName)] = labourNum
            #         else:
            #             df2.at[0, str('A04.1 Ctr Post' + '_' + labourName)] = df2[str('A04.1 Ctr Post' + '_' + labourName)].values[0] + labourNum

            df6 = df_nested_list[Mapping.keys()]
            df4 = pd.DataFrame()
            if len(x['data']["A04 Contractor's Management Team"]) > 0:
                _df3 = df6.copy()
                for c in x['data']["A04 Contractor's Management Team"]:
                    labourName = str(c['A04.1 Ctr Post'])
                    labourNum = 0
                    if ('A04.2 Ctr No.' in c) and not c['A04.2 Ctr No.'] is None:
                        labourNum = c['A04.2 Ctr No.']
                    else:
                        labourNum = 0
                    _df3['contractor_management_post_name'] = labourName
                    _df3['contractor_management_number'] = labourNum
                    df4 = df4.append(_df3)
            
            df2.rename(columns=Mapping, inplace=True)
            df4.rename(columns=Mapping, inplace=True)
            df2.columns = df2.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent').str.replace('/', '_').str.replace('__', '_')
            df4.columns = df4.columns.str.replace(' ', '_').str.replace('.', '_').str.replace('(', '_').str.replace(')', '').str.replace('%', 'percent').str.replace('/', '_').str.replace('__', '_')
            # df2.drop(columns=['2_Reportable_Accident', '16_Safety_Convictions_Records', '14_Incident_Reports', '15_LD_MD_Improvement_and_Suspension_Notice', '13_Near-miss_Reports','12_Unsafe_conditions_identified_during_inspections'], axis=1)
            df = df.append(df2)
            _df = _df.append(df4) 
            # _df = _df.append(df4)
        df['a01_date']=df['a01_date'].apply(pd.to_datetime)
        _df['a01_date']=_df['a01_date'].apply(pd.to_datetime)
        df.to_sql('nd201907_site_diary_general', con=conn, if_exists='replace', index= False)
        _df.to_sql('nd201907_site_diary_general_contractor_management', con=conn, if_exists='replace', index= False)
    #print("success")

    # context["ti"].xcom_push(key="dataResponse", value=response.text)

# def reformData(**context):
    # dataResponse = context.get("ti").xcom_pull(key="dataResponse")
    # RISC_Data = json.loads(dataResponse)
    # Mapping= {"A10 - Request Submission Date Time" : "a10_request_submission_date_time", 
    # 'C02 - Survey Checked on Date Time' : 'c02_inspect_on_date_time',
    # # 'C02 - Survey Checked on Date Time' : 'Inspection/Survey Inspection date',
    # "E01 - Received on behalf of Contractor on Date Time" : "e01_received_on_behalf_of_contractor_on_date_time",
    # 'A01a - Request No. Revision': "a01a_request_no_revision",
    # "A01 - Request No.": "a01_request_no",
    # "C12 Time Pass to Senior or Contractor" : 'c12_time_pass_to_senior_or_contractor',
    # "D01 - Countersigned on Date Time" : 'd01_countersigned_on_date_time',
    # 'C03 - Approval given?' : "c03_approval_given",
    # "A01b - Work Category" : "a01b_work_category"
    # # 'C12 Time Pass to Senior or Contractor' : 'Sign time by manager'
    # }
    # # df = pd.DataFrame.from_dict([RISC_Data['data'],RISC_Data['data']])
    # # print(df)
    # df_nested_list = json_normalize(
    # RISC_Data['data']
    # )
    # print (df_nested_list)
    # df2 = df_nested_list.reindex(columns=Mapping.keys())
    # df2["request_no"] = df2["A01 - Request No."].astype(str) + df2["A01a - Request No. Revision"]
    # print (df2)
    # df2.rename(columns=Mapping, inplace=True)
    # # df2.to_sql('table_temp', engine, if_exists='replace')
    # context["ti"].xcom_push(key="InsertData", value=df2)
    # insert_data_sql_query = """

    # """
    # return "Done"

# def insertData(**context):

    # supeate a connection object

    # Host of the MySQL database server (or ip)


# id SERIAL
# create_table_sql_query = """ 
#     CREATE TABLE IF NOT EXISTS nd201907_cleansing (
#     f2_checked_by_supd_on_date TIMESTAMP,
#     a01a_inspection_date TIMESTAMP,
#     d4_submission_date TIMESTAMP,
#     a2_daily_or_weekly VARCHAR (100),
#     report_name VARCHAR (100),
#     report_complete_or_incomplete VARCHAR (100),
#     nc_report BOOLEAN,
#     complete_time_in_days NUMERIC(10,2)
#     );
#     """

# create_table_sql_query = """ 
#     CREATE TABLE IF NOT EXISTS nd201907_risc (id INT NOT NULL, 
    # a10_request_submission_date_time TIMESTAMP, 
    # b01_request_received_date_time TIMESTAMP,
    # c02_inspect_or_survey_on_date_time TIMESTAMP,
    # a05_proposed_inspection_or_survey_date_time TIMESTAMP,
    # e01_received_on_behalf_of_contractor_on_date_time TIMESTAMP,
    # a01a_request_no_revision VARCHAR (50),
    # a01_request_no VARCHAR (50),
    # c12_time_pass_to_senior_or_contractor TIMESTAMP,
    # d01_countersigned_on_date_time TIMESTAMP,
    # c03_approval_given VARCHAR (50),
    # request_no VARCHAR (50),
    # a01b_work_category VARCHAR (50),
    # nc_report BOOLEAN,
    # urgent_report BOOLEAN,
    # elapsed_time NUMERIC(10,2),
    # overdue_report BOOLEAN,
    # delayed_approval_report BOOLEAN,
    # fail_in_first_inspection BOOLEAN,
    # complete_incomplete_outstanding_report VARCHAR(50)
#     );
#     """

# */2 * * * * Execute every two minute 
with DAG(
        dag_id="nd201907_site_diary_general",
        schedule_interval="0 4,10,16,22 * * *",
        default_args={
            "owner": "airflow",
            "retries": 1,
            "retry_delay": timedelta(minutes=5),
            "start_date": datetime(2023, 1, 17)
        },
        catchup=False) as f:
    
    getMongoDB = PythonOperator(
        task_id="getMongoDB",
        python_callable=getMongoDB,
        op_kwargs={"name": "Dylan"},
        provide_context=True,
    )

    # reformData = PythonOperator(
    #     task_id="reformData",
    #     python_callable=reformData,
    #     provide_context=True,
    #     # op_kwargs={"name": "Dylan"}
    # )

    getDrowToken = PythonOperator(
        task_id="getDrowToken",
        python_callable=getDrowToken,
        provide_context=True,
        # op_kwargs={"name": "Dylan"}
    )

    # insertData = PythonOperator(
    #     task_id="insetDateToPG",
    #     python_callable=insertData,
    #     provide_context=True,
    #     # op_kwargs={"name": "Dylan"}
    # )

    # create_table = PostgresOperator(
    #     sql = create_table_sql_query,
    #     task_id = "create_table_task",
    #     postgres_conn_id = "postgres_rds",
    # )

    # insert_data = PostgresOperator(
    #     sql = insert_data_sql_query,
    #     task_id = "insertData_sql_query_task",
    #     postgres_conn_id = "postgres_rds",
    # )



# getDrowToken >> getMongoDB >> reformData >> create_table
# create_table >> getDrowToken >> getMongoDB >> reformData >> insertData
# getDrowToken >> getMongoDB >> reformData >> insertData
# create_table >> 
getDrowToken >> getMongoDB
    
            # if df2['F2. 監理代表 Checked by SupD on Date'].isnull().bool():
            #     df2['report_complete_or_incomplete'] = 'incomplete'
            # else:
            #     df2['report_complete_or_incomplete'] = 'complete'
            # if not df2['F2. 監理代表 Checked by SupD on Date'].isnull().bool() and not df2['D4 Submission Date'].isnull().bool() and (df2["F2. 監理代表 Checked by SupD on Date"].astype('datetime64[ns]') < df2["D4 Submission Date"].astype('datetime64[ns]')).bool():
            #     df2['nc_report'] = True
            # else:
            #     df2['nc_report'] = False
            # if (not df2['F2. 監理代表 Checked by SupD on Date'].isnull().bool() and not df2['A1a 巡查日期 Inspection Date'].isnull().bool()):
            #     df2['complete_time_in_days'] = (((df2['F2. 監理代表 Checked by SupD on Date'].astype('datetime64[ns]') - 
            #     df2['A1a 巡查日期 Inspection Date'].astype('datetime64[ns]'))/ np.timedelta64(1, 'h'))/24).round(2)
            #     if df2['complete_time_in_days'].isnull().bool() or df2['complete_time_in_days'].lt(0).bool():
            #         df2['complete_time_in_days'] = 0
            # else:
            #     df2['complete_time_in_days'] = 0
            # if(not df2["C02 - Survey Checked on Date Time"].isnull().bool() and not df2["A10 - Request Submission Date Time"].isnull().bool() and ((df2["C02 - Survey Checked on Date Time"].astype('datetime64[ns]') < df2["A10 - Request Submission Date Time"].astype('datetime64[ns]')).bool())):
            #     print((df2["C02 - Survey Checked on Date Time"].astype('datetime64[ns]') < df2["A10 - Request Submission Date Time"].astype('datetime64[ns]')))
            # else:
            #     print((df2["C02 - Survey Checked on Date Time"].astype('datetime64[ns]') < df2["A10 - Request Submission Date Time"].astype('datetime64[ns]')))

            # if (not df2["C02 - Survey Checked on Date Time"].isnull().bool() and (not df2["A10 - Request Submission Date Time"].isnull().bool()) and (df2["C02 - Survey Checked on Date Time"].astype('datetime64[ns]') < df2["A10 - Request Submission Date Time"].astype('datetime64[ns]')).bool()):
            #     df2['nc_report'] = True
            # else:
            #     df2['nc_report'] = False

            # if (not df2["C02 - Survey Checked on Date Time"].isnull().bool() and not df2["A10 - Request Submission Date Time"].isnull().bool() and (((df2["C02 - Survey Checked on Date Time"].astype('datetime64[ns]') - df2["A10 - Request Submission Date Time"].astype('datetime64[ns]'))) < pd.Timedelta(24, unit='h')).bool()):
            #     df2['urgent_report'] = True
            # else:
            #     df2['urgent_report'] = False

            # if (not df2['E01 - Received on behalf of Contractor on Date Time'].isnull().bool() and not df2['A10 - Request Submission Date Time'].isnull().bool()):
            #     df2['elapsed_time'] = (((df2['E01 - Received on behalf of Contractor on Date Time'].astype('datetime64[ns]') - 
            #     df2['A10 - Request Submission Date Time'].astype('datetime64[ns]'))/ np.timedelta64(1, 'h'))/24).round(2)
            #     if df2['elapsed_time'].isnull().bool() or df2['elapsed_time'].lt(0).bool():
            #         df2['elapsed_time'] = 0
            # else:
            #     df2['elapsed_time'] = 0
            
            # if (not df2["C02 - Survey Checked on Date Time"].isnull().bool() and not df2["D01 - Countersigned on Date Time"].isnull().bool() and (((df2["D01 - Countersigned on Date Time"].astype('datetime64[ns]') - df2["C02 - Survey Checked on Date Time"].astype('datetime64[ns]')))>= pd.Timedelta(24, unit='h')).bool()):
            #     df2['overdue_report'] = True
            # else:
            #     df2['overdue_report'] = False
            
            # if (not df2["C02 - Survey Checked on Date Time"].isnull().bool() and not df2["C12 Time Pass to Senior or Contractor"].isnull().bool() and (((df2["C12 Time Pass to Senior or Contractor"].astype('datetime64[ns]') - df2["C02 - Survey Checked on Date Time"].astype('datetime64[ns]')))>= pd.Timedelta(24, unit='h')).bool()):
            #     df2['delayed_approval_report'] = True
            # else:
            #     df2['delayed_approval_report'] = False

            # if ((df2['A01a - Request No. Revision']=="-A").bool()):
            #     df2['fail_in_first_inspection'] = True
            # else:
            #     df2['fail_in_first_inspection'] = False
            
            # if (not df2["C12 Time Pass to Senior or Contractor"].isnull().bool() and not df2["C02 - Survey Checked on Date Time"].isnull().bool() and not df2["E01 - Received on behalf of Contractor on Date Time"].isnull().bool()):
            #     df2['complete_incomplete_outstanding_report'] = 'complete'
            # elif ((df2["C12 Time Pass to Senior or Contractor"].isnull().bool() or df2["E01 - Received on behalf of Contractor on Date Time"].isnull().bool()) and not df2["C02 - Survey Checked on Date Time"].isnull().bool()):
            #     df2['complete_incomplete_outstanding_report'] = 'in-complete'
            # elif ((not df2["C12 Time Pass to Senior or Contractor"].isnull().bool() or df2["E01 - Received on behalf of Contractor on Date Time"].isnull().bool()) and df2["C02 - Survey Checked on Date Time"].isnull().bool()):
            #     df2['complete_incomplete_outstanding_report'] = 'outstanding'
            # else:
            #     df2['complete_incomplete_outstanding_report'] = 'outstanding'