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


class DB():
    def __init__(self):
        self.conn=psycopg2.connect(
            host='localhost',
            dbname='postgres',
            user='postgres',
            password='ts4430!@',
            port='5432'
            ) # db에 접속
        self.conn.autocommit=True

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

        cur.execute(f'''
        SELECT user_id
        FROM rds.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 rds.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 rds.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 rds.user_id ui
        where user_id  = '{user_id}'
        ''')
        cur.close()
    

    
    def db_add_action(self,action_id,lat,lon,user_id,action_success) :
        cur = self.conn.cursor() # 커서를 생성한다
        now=datetime.now() 
        d=now.strftime('%Y-%m-%d %X')
        cur.execute(f'''
        insert into rds.action (action_id,lat,lon,action_time_stamp,user_id,action_success)
        values ('{action_id}','{lat}','{lon}','{d}','{user_id}','{action_success}')
        ''')
        
        
    def db_display_action(self,timestamp) :
        cur = self.conn.cursor() # 커서를 생성한다
        now=timestamp
        d_plus=now +timedelta(hours=2)
        d_plus=str("'"+d_plus.strftime('%Y-%m-%d %X')+"'")
        d_minus=now -timedelta(hours=2)
        d_minus=str("'"+d_minus.strftime('%Y-%m-%d %X')+"'")
        cur.execute(f'''
        select * from rds.pothole 
        where timestamp between {d_minus} and {d_plus};
        ''')
        result=cur.fetchall()
        return result