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


class DB():
    def __init__(self):
        self.conn=psycopg2.connect(
            host='192.168.0.169',
            dbname='postgres',
            user='postgres',
            password='1234',
            port='5432'
            ) # db에 접속
        self.conn.autocommit=True
    '''
    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 "TRAFFICAGENCY".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 "TRAFFICAGENCY".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() # 커서를 생성한다
        now=time.localtime()
        d=time.strftime('%Y-%m-%d %X', now)
        cur.execute(f'''
        insert into "TRAFFICAGENCY".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 "TRAFFICAGENCY".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 "TRAFFICAGENCY".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 "TRAFFICAGENCY".jibun j, "TRAFFICAGENCY".location_info li 
        where j.build_num =li.build_code and (j.q='{dest1}' or li.q='{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 "TRAFFICAGENCY".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 "TRAFFICAGENCY".jibun j, "TRAFFICAGENCY".location_info li 
        where j.build_num =li.build_code and (j.q='{dest1}' or li.q='{dest1}')
        ''')
        result=cur.fetchone()

        return (float(result[2]),float(result[3]))
    
    def db_add_report(self,report_id,report_x,report_y,z) :
        cur = self.conn.cursor() # 커서를 생성한다
        now=time.localtime()
        d=time.strftime('%Y-%m-%d %X', now)
        cur.execute(f'''
        insert into "TRAFFICAGENCY".report (report_id,report_x,report_y,timestamp,z)
        values ('{report_id}','{report_x}','{report_y}','{d}','{z}')
        ''')
        
    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 "TRAFFICAGENCY".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,z) :
        cur = self.conn.cursor() # 커서를 생성한다
        now=datetime.now() 
        d=now.strftime('%Y-%m-%d %X')
        cur.execute(f'''
        insert into "TRAFFICAGENCY".pothole (pothole_id,pothole_location_x,pothole_location_y,timestamp,z)
        values ('{pothole_id}','{pothole_location_x}','{pothole_location_y}','{d}','{z}')
        ''')
        
    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 "TRAFFICAGENCY".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,timestamp) :
        cur = self.conn.cursor() # 커서를 생성한다
        now=datetime.fromtimestamp(timestamp)
        d_plus=now +timedelta(hours=2)
        d_plus=str("'"+d_plus.strftime('%Y-%m-%d %H:%M:%S')+"'")
        d_minus=now -timedelta(hours=2)
        d_minus=str("'"+d_minus.strftime('%Y-%m-%d %H:%M:%S')+"'")
        cur.execute(f'''
        select pothole_location_x,pothole_location_y from "TRAFFICAGENCY".pothole 
        where timestamp between {d_minus} and {d_plus};
        ''')
        result=cur.fetchall()
        return result