try:

    from datetime import timedelta
    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
    import os
    import re

    #print("All Dag moudules are sucessfully imported")

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

# dRoW_api_end_url = "http://172.16.200.123:8080"
dRoW_api_end_url = "https://uat2.drow.cloud/api"
CUR_DIR = os.path.abspath(os.path.dirname(__file__))


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 getReportAPIAndSendReq(**context):
    
    recordIdMap = open(f'{CUR_DIR}/data/workflowRecordIdMap.json')
    recordIdMapData = json.load(recordIdMap)

    modulesConfig =open(f'{CUR_DIR}/data/modulesConfigs.json')
    modulesConfigData = json.load(modulesConfig)

    
    for data in modulesConfigData:
        result=f'{dRoW_api_end_url}/api/module/reporting?report='
        if data['configs']['config'].get('outputType') != None:
            result += data['configs']['key'] + '_' + data['configs']['config']['outputType'] + '&' + 'workflow_id=' + data['relatedId']
        else:
            result += data['configs']['key'] + '&' + 'workflow_id=' + data['relatedId']
        recordId = next((_data for _data in recordIdMapData if _data.get('workflowId')==data['relatedId']), None)
        if recordId !=None:
            result += '&record_id=' + recordId['recordId']
        for _config in data['configs']['config'].keys():
            __config=re.sub('([a-z0-9])([A-Z])', r'\1_\2', _config).lower()
            result += '&' + __config + '=' + f"{data['configs']['config'][_config]}"
        result += '&year=2022&month=10&day=31'
            # result = result + _result
        #print(result)
        # token = context.get("ti").xcom_pull(key="token")
        # response = requests.get(
        # url=result,
        # headers={
        # "x-access-token": f"Bearer {token}",
        # }
        # )
        # print('got_data', response)

    # RISC_Data = json.loads(response.text)

    # token = context.get("ti").xcom_pull(key="token")
    # response = requests.get(
    # url=f"{dRoW_api_end_url}/api/module/nd201907/document-data?from=1666845580212&documentId=5fbb731fb52b8f7ffa7b5ff1",
    # headers={
    # "x-access-token": f"Bearer {token}",
    # }
    # )
    # print('got_data')
    # RISC_Data = json.loads(response.text)
    # Mapping= {"A10 - Request Submission Date Time" : "a10_request_submission_date_time", 
    # 'C02 - Inspect on Date Time' : 'c02_inspect_on_date_time',
    # # 'C02 - Inspect 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)
    # 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:
    #     for x in RISC_Data:
    #         print(x)
    #         df_nested_list = json_normalize(x['data'])
    #         print('process 1')
    #         df2 = df_nested_list.reindex(columns=Mapping.keys())
    #         df2["request_no"] = df2["A01 - Request No."].astype(str) + df2["A01a - Request No. Revision"]

    #         if(not df2["C02 - Inspect on Date Time"].empty and not df2["A10 - Request Submission Date Time"].empty and ((df2["C02 - Inspect on Date Time"].astype('datetime64[ns]') < df2["A10 - Request Submission Date Time"].astype('datetime64[ns]')).bool())):
    #             print((df2["C02 - Inspect on Date Time"].astype('datetime64[ns]') < df2["A10 - Request Submission Date Time"].astype('datetime64[ns]')))
    #         else:
    #             print((df2["C02 - Inspect on Date Time"].astype('datetime64[ns]') < df2["A10 - Request Submission Date Time"].astype('datetime64[ns]')))

    #         if (not df2["C02 - Inspect on Date Time"].empty and (not df2["A10 - Request Submission Date Time"].empty) and (df2["C02 - Inspect 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 - Inspect on Date Time"].empty and not df2["A10 - Request Submission Date Time"].empty and (((df2["C02 - Inspect 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'].empty and not df2['A10 - Request Submission Date Time'].empty):
    #             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, 'D'))
    #             if df2['elapsed_time'].empty:
    #                 df2['elapsed_time'] = 0
            
    #         if (not df2["C02 - Inspect on Date Time"].empty and not df2["D01 - Countersigned on Date Time"].empty and (((df2["D01 - Countersigned on Date Time"].astype('datetime64[ns]') - df2["C02 - Inspect 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 - Inspect on Date Time"].empty and not df2["C12 Time Pass to Senior or Contractor"].empty and (((df2["C12 Time Pass to Senior or Contractor"].astype('datetime64[ns]') - df2["C02 - Inspect 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"].empty and not df2["C02 - Inspect on Date Time"].empty and not df2["E01 - Received on behalf of Contractor on Date Time"].empty):
    #             df2['complete_incomplete_outstanding_report'] = 'complete'
    #         elif (df2["C12 Time Pass to Senior or Contractor"] == None and not df2["C02 - Inspect on Date Time"].empty and df2["E01 - Received on behalf of Contractor on Date Time'"] == None):
    #             df2['complete_incomplete_outstanding_report'] = 'in-complete'
    #         elif (not df2["C12 Time Pass to Senior or Contractor"].empty and df2["C02 - Inspect on Date Time"] == None and df2["E01 - Received on behalf of Contractor on Date Time'"] == None):
    #             df2['complete_incomplete_outstanding_report'] = 'outstanding'
            

    #         print('process 2')
    #         df2.rename(columns=Mapping, inplace=True)
    #         # df2.to_sql('table_temp', engine, if_exists='replace')
    #         # context["ti"].xcom_push(key="InsertData", value=df2)
    #         print('loading into DB')
    #         # our dataframe
    #         # data = {'Name': ['Tom', 'dick', 'harry'],
    #         #         'Age': [22, 21, 24]}

    #         # # Create DataFrame
    #         # df = pd.DataFrame(data)
    #         # conn = psycopg2.connect(conn_string
    #         #                         )
    #         # conn.autocommit = True
    #         # cursor = conn.cursor()
    #         df2.to_sql('nd201907_risc', con=conn, if_exists='append', index= False)
    
    # # sql1 = '''select * from ND201907_RISC_Data;'''
    # # cursor.execute(sql1)
    # # for i in cursor.fetchall():
    # #     print(1,i)
    
    # # conn.commit()
    # # conn.close()
    #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 - Inspect on Date Time' : 'c02_inspect_on_date_time',
    # # 'C02 - Inspect 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):

    # Create a connection object

    # Host of the MySQL database server (or ip)

# */2 * * * * Execute every two minute 
with DAG(
        dag_id="ReportAPIChecker",
        schedule_interval="@daily",
        default_args={
            "owner": "airflow",
            "retries": 1,
            "retry_delay": timedelta(minutes=5),
            "start_date": datetime(2022, 10, 24)
        },
        catchup=False) as f:
    
    getReportAPIAndSendReq = PythonOperator(
        task_id="getReportAPIAndSendReq",
        python_callable=getReportAPIAndSendReq,
        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"}
    # )

    # 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
getDrowToken >> getReportAPIAndSendReq