import psycopg2 # driver 임포트
import time
from datetime import datetime, timedelta


class DB():
    def __init__(self):
        self.conn=psycopg2.connect(
            host='210.180.118.83',
            dbname='TRAFFICAGNET',
            user='takensoft',
            password='tts96314728!@',
            port='5432',
            options=f'-c search_path=trafficagent'
            ) # db에 접속
        self.conn.autocommit=True
        self.schema = 'trafficagent'
    '''
    def __init__(self):
        self.conn=psycopg2.connect(
            host='165.229.169.113',
            dbname='traffic_agent',
            user='takensoft',
            password='ts44301236!@',
            port='5432',
            options="-c search_path=traffic_agent_v1") # db에 접속
        self.conn.autocommit=True
        '''
    
        
    
    def db_check_id(self,id):
        cur = self.conn.cursor() # 커서를 생성한다

        cur.execute(f'''
        SELECT user_id
        FROM "{self.schema}".user_id
        Where user_id = '{id}';
        ''')
        result=cur.fetchone()
        cur.close()

        return result

    def db_login(self,id,pw):
        cur = self.conn.cursor() # 커서를 생성한다

        cur.execute(f'''
        SELECT user_id, user_pw, user_email, user_sex, user_phone, user_time_stamp
        FROM "{self.schema}".user_id
        Where user_id = '{id}' and user_pw='{pw}';
        ''')
        result=cur.fetchone()


        cur.close()

        return result

    def db_add_id(self,user_id,user_pw,user_email,user_sex,user_phone) :
        cur = self.conn.cursor() # 커서를 생성한다
        recievingtime=datetime.now()
        d = recievingtime.isoformat(sep=' ', timespec='milliseconds')
        # d=recievingtime.strftime("%Y-%m-%d %H:%M:%S.%f")
        cur.execute(f'''
        insert into "{self.schema}".user_id (user_id,user_pw,user_email,user_sex,user_phone,user_time_stamp)
        values ('{user_id}','{user_pw}','{user_email}','{user_sex}','{user_phone}','{d}')
        ''')
        
    def db_add_action(self,user_id,user_pw,user_email,user_sex,user_phone) :
        cur = self.conn.cursor() # 커서를 생성한다
        recievingtime=datetime.now()
        d = recievingtime.isoformat(sep=' ', timespec='milliseconds')
        # d=recievingtime.strftime("%Y-%m-%d %H:%M:%S.%f")
        cur.execute(f'''
        insert into "{self.schema}".user_id (user_id,user_pw,user_email,user_sex,user_phone,user_time_stamp)
        values ('{user_id}','{user_pw}','{user_email}','{user_sex}','{user_phone}','{d}')
        ''')


        cur.close()
    def db_delete_id(self,user_id) :
        cur = self.conn.cursor() # 커서를 생성한다
        cur.execute(f'''
        delete
        from "{self.schema}".user_id ui
        where user_id  = '{user_id}'
        ''')
        cur.close()

    def db_get_node(self):
        cur = self.conn.cursor() # 커서를 생성한다

        cur.execute('''
        select "index",source_x ,source_y,target_x,target_y," dist "
        from "{self.schema}".node n 
        where flcass != 'pedstrian'
        ''')
        result=cur.fetchall()

        return result
    
    def db_get_dest(self,dest1):
        cur = self.conn.cursor() # 커서를 생성한다

        cur.execute(f'''
        select j.q,li.q ,li.location_cen_x_4623,li.location_cen_y_4623,li.location_exit_x_4623 ,li.location_exit_y_4623 
        from "{self.schema}".jibun j, "{self.schema}".location_info li 
        where j.build_num =li.build_code and (j.q='{dest1}' or li.q='{dest1}' or li.build_name_city like '{dest1}')
        ''')
        result=cur.fetchone()

        return (float(result[4]),float(result[5]))
    
    def db_get_near_node(self,dest_x,dest_y,value):
        cur = self.conn.cursor() # 커서를 생성한다

        cur.execute(f'''
        select source_x, source_y 
        from "{self.schema}".node n 
        where {dest_x} > source_y  - {value} and {dest_x} <= source_y  + {value}
        and {dest_y} > source_x  - {value} and {dest_y} <= source_x + {value}

        ''')
        result=cur.fetchall()
        return result

    def db_get_address(self,dest1):
        cur = self.conn.cursor() # 커서를 생성한다

        cur.execute(f'''
        select j.q,li.q ,li.location_cen_x_4623,li.location_cen_y_4623,li.location_exit_x_4623 ,li.location_exit_y_4623 
        from "{self.schema}".jibun j, "{self.schema}".location_info li 
        where j.build_num =li.build_code and (j.q='{dest1}' or li.q='{dest1}' or li.build_name_city like '{dest1}')
        ''')
        result=cur.fetchone()
        print( (float(result[2]),float(result[3])))

        return (float(result[2]),float(result[3]))
    
    def db_add_report(self,report_id,report_x,report_y) :
        cur = self.conn.cursor() # 커서를 생성한다
        now=time.localtime()
        d=time.strftime('%Y-%m-%d %X', now)
        cur.execute(f'''
        insert into "{self.schema}".report (report_id,report_x,report_y,timestamp)
        values ('{report_id}','{report_x}','{report_y}','{d}')
        ''')
        
    def db_get_near_point(self,dest_x,dest_y):
        cur = self.conn.cursor() # 커서를 생성한다
        now=datetime.now() 
        d_plus=now +timedelta(hours=1)
        d_plus=str("'"+d_plus.strftime('%Y-%m-%d %X')+"'")
        d_minus=now -timedelta(hours=1)
        d_minus=str("'"+d_minus.strftime('%Y-%m-%d %X')+"'")
        cur.execute(f'''
        select report_x, report_y
        from "{self.schema}".report 
        where {dest_y} > report_y  - 0.000498 and {dest_y} <= report_y  + 0.000498
        and {dest_x} > report_x  - 0.000498 and {dest_x} <= report_x + 0.000498
        and timestamp between {d_minus} and {d_plus};


        ''')
        result=cur.fetchall()
        return result
    
    
    def db_add_pothole(self,pothole_id,pothole_location_x,pothole_location_y) :
        cur = self.conn.cursor() # 커서를 생성한다
        now=datetime.now() 
        d=now.strftime('%Y-%m-%d %X')
        cur.execute(f'''
        insert into "{self.schema}".pothole (pothole_id,pothole_location_x,pothole_location_y,timestamp)
        values ('{pothole_id}','{pothole_location_x}','{pothole_location_y}','{d}')
        ''')
        
    def db_delete_pothole(self,dest_x,dest_y) :
        cur = self.conn.cursor() # 커서를 생성한다
        now=datetime.now() 
        d_plus=now +timedelta(hours=1)
        d_plus=str("'"+d_plus.strftime('%Y-%m-%d %X')+"'")
        d_minus=now -timedelta(hours=1)
        d_minus=str("'"+d_minus.strftime('%Y-%m-%d %X')+"'")
        cur.execute(f'''
        delete from "{self.schema}".pothole 
        where {dest_y} > pothole_location_y  - 0.000498 and {dest_y} <= pothole_location_y  + 0.000498
        and {dest_x} > pothole_location_x  - 0.000498 and {dest_x} <= pothole_location_x + 0.000498
        and timestamp between {d_minus} and {d_plus};
        ''')
        
    def db_display_pothole(self) :
        cur = self.conn.cursor() # 커서를 생성한다
        cur.execute(f'''
        select report_x,report_y  from "{self.schema}".report 
        ''')
        result=cur.fetchall()
        return result

    def insert_gps_data(self, trip_id, location_x, location_y,user_id, timestamp):
        cur = self.conn.cursor()
        
        print(trip_id, location_x, location_y, user_id)
        trip_id = trip_id['current'] 
        cur.execute(f'''
        INSERT INTO "{self.schema}".gps_data (timestamp, trip_id, location_x, location_y,user_id)
        VALUES (%s, %s, %s, %s,%s);
        ''', (timestamp, trip_id, location_x, location_y,user_id))
        
        cur.close()
        return True