import psycopg2 # driver 임포트 import json import bcrypt from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes from cryptography.hazmat.primitives import padding from cryptography.hazmat.backends import default_backend import re import os from io import StringIO from datetime import datetime, timedelta config_file_path = "database/db_config.json" class DB(): def __init__(self): # Load the database configuration from the JSON file self.db_config = self.load_db_config(config_file_path) # Initialize database connection self.conn = psycopg2.connect( host=self.db_config['host'], dbname=self.db_config['dbname'], user=self.db_config['user'], password=self.db_config['password'], port=self.db_config['port'], # options=self.db_config['options'] ) self.schema = self.db_config["schema"] self.conn.autocommit=True self.cur = self.conn.cursor() # yeah, that double quotation is absolutely needed (to distinguish capital letters) self.cur.execute("SET search_path TO " + f'"{self.schema}"') with open("database/keys/encryption_key2025-04-03_14:33:09", "rb") as f: self.encryption_key = f.read() def load_db_config(self, config_file_path): """ Loads database configuration from a JSON file. """ with open(config_file_path, 'r') as config_file: return json.load(config_file) def encrypt_aes(self, plain_text): iv = os.urandom(16) # AES block size is 16 bytes cipher = Cipher(algorithms.AES(self.encryption_key), modes.CBC(iv), backend=default_backend()) encryptor = cipher.encryptor() # Pad the plaintext to be a multiple of 16 bytes padder = padding.PKCS7(algorithms.AES.block_size).padder() padded_data = padder.update(plain_text.encode('utf-8')) + padder.finalize() encrypted_data = encryptor.update(padded_data) + encryptor.finalize() return encrypted_data, iv def decrypt_aes(self, encrypted_data, iv): cipher = Cipher(algorithms.AES(self.encryption_key), modes.CBC(iv), backend=default_backend()) decryptor = cipher.decryptor() decrypted_data = decryptor.update(encrypted_data) + decryptor.finalize() # Remove padding after decryption unpadder = padding.PKCS7(algorithms.AES.block_size).unpadder() unpadded_data = unpadder.update(decrypted_data) + unpadder.finalize() return unpadded_data.decode('utf-8') def cleanse_and_validate_input(self, data): """ Cleanses input by removing leading/trailing spaces and validates the data. Returns cleansed data and an error message if validation fails. """ username = data.get('username', '').strip() password = data.get('password', '').strip() email = data.get('email', '').strip() phone = data.get('phone', '').strip() phone = phone.replace("-","") sex = data.get('sex', '').strip() # Validate username if not username: return None, "Username is required." if len(username) > 26: return None, "Username must not exceed 26 characters." # Validate password if not password: return None, "Password is required." if len(password) < 8: return None, "Password must be at least 8 characters long." # Validate email format if not email or not re.fullmatch(r"[^@]+@[^@]+\.[^@]+", email): return None, "Invalid email address." # Validate phone number format if not re.fullmatch(r'010\d{8}', phone): return None, "Phone number must be in the format 010XXXXXXXX where X are digits." # Validate sex input # if not sex: # return None, "Sex is required." # if sex not in ['Male', 'Female', 'Non-binary', 'Other']: # return None, "Invalid value for sex." sex = "WHATEVER" return { 'username': username, 'password': password, 'email': email, 'phone': phone, 'sex': sex }, None def register_user(self, data): data, error = self.cleanse_and_validate_input(data) if error: return {'status': 'error', 'message': error}, 400 username = data['username'] password = data['password'] email = data['email'] phone = data['phone'] sex = data['sex'] # Hash the password with bcrypt, which automatically handles the salt hashed_pw = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()) # Encrypt email, phone, and sex with AES encrypted_email, email_iv = self.encrypt_aes(email) encrypted_phone, phone_iv = self.encrypt_aes(phone) encrypted_sex, sex_iv = self.encrypt_aes(sex) # Insert the user into the database try: self.cur.execute(f""" INSERT INTO users (username, user_pw, user_email, email_iv, user_phone, phone_iv, user_time_stamp) VALUES (%s, %s, %s, %s, %s, %s, %s) """, ( username, psycopg2.Binary(hashed_pw), psycopg2.Binary(encrypted_email), psycopg2.Binary(email_iv), psycopg2.Binary(encrypted_phone), psycopg2.Binary(phone_iv), datetime.now() # Correct way to insert current timestamp with timezone ) ) self.conn.commit() return {'status': 'success', 'message': f'user {username} registered successfully'}, 200 except psycopg2.Error as e: self.conn.rollback() return {'status': 'error', 'message': str(e)}, 400 def login_user(self, data): username = data.get('username', '').strip() password = data.get('password', '').strip() # Validate input if not username or not password: return {'status': 'error', 'message': 'Username and password are required.'}, 400 # Retrieve the user's hashed password from the database self.cur.execute("SELECT user_pw FROM users WHERE username = %s", (username,)) user = self.cur.fetchone() if user is None: return {'status': 'error', 'message': 'Invalid username or password'}, 401 hashed_pw = bytes(user[0]) # Convert the retrieved hashed password to bytes # Check if the provided password matches the stored hashed password if bcrypt.checkpw(password.encode('utf-8'), hashed_pw): return {'status': 'success', 'message': 'Logged in successfully'}, 200 else: return {'status': 'error', 'message': 'Invalid username or password'}, 401 def get_phone_number(self, data): username = data.get('username', '').strip() if not username: return {'status': 'error', 'message': 'Username is required.'}, 400 # Retrieve the encrypted phone number and IV from the database self.cur.execute("SELECT user_phone, phone_iv FROM users WHERE username = %s", (username,)) user = self.cur.fetchone() if user is None: return {'status': 'error', 'message': 'User not found'}, 404 encrypted_phone, phone_iv = user # Decrypt the phone number decrypted_phone = self.decrypt_aes(encrypted_phone, phone_iv) return {'status': 'success', 'phone_number': decrypted_phone}, 200 def get_email(self, data): username = data.get('username', '').strip() if not username: return {'status': 'error', 'message': 'Username is required.'}, 400 # Retrieve the encrypted phone number and IV from the database self.cur.execute("SELECT user_email, email_iv FROM users WHERE username = %s", (username,)) user = self.cur.fetchone() if user is None: return {'status': 'error', 'message': 'User not found'}, 404 encrypted_phone, phone_iv = user # Decrypt the phone number decrypted_phone = self.decrypt_aes(encrypted_phone, phone_iv) return {'status': 'success', 'phone_number': decrypted_phone}, 200 def insert_gps_data(self, csv_block, columns): cur = self.conn.cursor() data = StringIO(csv_block) # using COPY instead of INSERT to do even less operation per data. cur.copy_from(data, 'gps_data', sep=',', columns=columns) self.conn.commit() cur.close() return True def insert_accel_data(self, csv_block, columns): cur = self.conn.cursor() try: data = StringIO(csv_block) # Create StringIO internally cur.copy_from(data, f'accel_data', sep=',', columns=columns) self.conn.commit() success = True except Exception as e: self.conn.rollback() print(f"DB Error inserting Accel: {e}") success = False finally: if cur: cur.close() return success def insert_gyro_data(self, csv_block, columns): cur = self.conn.cursor() try: data = StringIO(csv_block) # Create StringIO internally cur.copy_from(data, f'gyro_data', sep=',', columns=columns) self.conn.commit() success = True except Exception as e: self.conn.rollback() print(f"DB Error inserting Gyro: {e}") success = False finally: if cur: cur.close() return success def insert_motion_data(self, csv_block, columns): cur = self.conn.cursor() try: data = StringIO(csv_block) # Create StringIO internally cur.copy_from(data, f'motion_data', sep=',', columns=columns) self.conn.commit() success = True except Exception as e: self.conn.rollback() print(f"DB Error inserting Motion: {e}") success = False finally: if cur: cur.close() return success def insert_azimuth_data(self, csv_block, columns): cur = self.conn.cursor() try: data = StringIO(csv_block) # Create StringIO internally cur.copy_from(data, f'azimuth_data', sep=',', columns=columns) self.conn.commit() success = True except Exception as e: self.conn.rollback() print(f"DB Error inserting Azimuth: {e}") success = False finally: if cur: cur.close() return success def insert_trip_data( self, username, trip_id, total_distance_m, total_time_s, abrupt_start_count, abrupt_stop_count, abrupt_acceleration_count, abrupt_deceleration_count, helmet_on, turn_noslow_cnt, final_score ): self.cur.execute(f""" INSERT INTO trip_log (user_id, trip_id, timestamp, total_distance_m, total_time_s, abrupt_start_count, abrupt_stop_count, abrupt_acceleration_count, abrupt_deceleration_count, helmet_on, turn_noslow_cnt, final_score) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """, ( username, trip_id, datetime.now(), total_distance_m, total_time_s, abrupt_start_count, abrupt_stop_count, abrupt_acceleration_count, abrupt_deceleration_count, helmet_on, turn_noslow_cnt, final_score ) ) 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 get_history(self, user_name): """ Retrieves all trip logs for the specified user within the last month and returns them in JSON format. [ { "trip_id": "trip_001", "timestamp": "2024-09-01 12:45:00", "total_distance_m": 1000.5, "total_time_s": 600, "abrupt_start_count": 3, "abrupt_stop_count": 2, "abrupt_acceleration_count": 1, "abrupt_deceleration_count": 1, "helmet_on": true, "final_score": 85.5 }, { "trip_id": "trip_002", "timestamp": "2024-09-02 14:30:00", "total_distance_m": 1500.0, "total_time_s": 720, "abrupt_start_count": 2, "abrupt_stop_count": 3, "abrupt_acceleration_count": 1, "abrupt_deceleration_count": 2, "turn_noslow_cnt":3 "helmet_on": false, "final_score": 90.0 } ] """ try: # Execute the query to retrieve logs within the last month self.cur.execute(""" SELECT trip_id, timestamp, total_distance_m, total_time_s, abrupt_start_count, abrupt_stop_count, abrupt_acceleration_count, abrupt_deceleration_count, helmet_on, turn_noslow_cnt, final_score FROM trip_log WHERE user_id = %s AND timestamp >= NOW() - INTERVAL '1 month' """, (user_name,)) # Fetch all results rows = self.cur.fetchall() # Format the results into a list of dictionaries result = [] for row in rows: trip_log = { "trip_id": row[0], "timestamp": row[1].strftime("%Y-%m-%d %H:%M:%S"), # Format timestamp "total_distance_m": row[2], "total_time_s": row[3], "abrupt_start_count": row[4], "abrupt_stop_count": row[5], "abrupt_acceleration_count": row[6], "abrupt_deceleration_count": row[7], "helmet_on": bool(row[8]), # Convert INT to Boolean "turn_noslow_cnt" : row[9], "final_score": row[10] } result.append(trip_log) # Convert the result list to JSON format return json.dumps(result), 200 except psycopg2.Error as e: self.conn.rollback() return {'status': 'error', 'message': str(e)}, 500 def get_history_main(self, user_name): try: # 최신 기록 1건 조회 self.cur.execute(""" SELECT trip_id, timestamp, total_distance_m, total_time_s, abrupt_start_count, abrupt_stop_count, abrupt_acceleration_count, abrupt_deceleration_count, helmet_on, turn_noslow_cnt, final_score FROM trip_log WHERE user_id = %s ORDER BY timestamp DESC LIMIT 1 """, (user_name,)) row = self.cur.fetchone() if not row: return json.dumps({"message": "No records found", "data": []}), 200 latest_record = { "trip_id": row[0], "timestamp": row[1].strftime("%Y-%m-%d %H:%M:%S"), "total_distance_m": row[2], "total_time_s": row[3], "abrupt_start_count": row[4], "abrupt_stop_count": row[5], "abrupt_acceleration_count": row[6], "abrupt_deceleration_count": row[7], "helmet_on": bool(row[8]), "turn_noslow_cnt": row[9], "final_score": row[10] } # 전체 기록 수 조회 self.cur.execute("SELECT COUNT(*) FROM trip_log WHERE user_id = %s", (user_name,)) total_count = self.cur.fetchone()[0] # final_score가 95점 이상인 기록 수 조회 self.cur.execute("SELECT COUNT(*) FROM trip_log WHERE user_id = %s AND final_score >= 95", (user_name,)) count_above_95 = self.cur.fetchone()[0] # 평균 final_score 조회 self.cur.execute("SELECT AVG(final_score) FROM trip_log WHERE user_id = %s", (user_name,)) avg_final_score = self.cur.fetchone()[0] avg_final_score = round(avg_final_score, 2) # 응답 데이터 구성 response_data = { "latest_record": latest_record, "total_count": total_count, "count_above_95": count_above_95, "average_final_score": avg_final_score } return json.dumps(response_data), 200 except psycopg2.Error as e: self.conn.rollback() return {'status': 'error', 'message': str(e)}, 500 def get_history_by_period(self, user_name, start_date, end_date): try: # Execute the query to retrieve logs within the specified period self.cur.execute(""" SELECT trip_id, timestamp, total_distance_m, total_time_s, abrupt_start_count, abrupt_stop_count, abrupt_acceleration_count, abrupt_deceleration_count, helmet_on, turn_noslow_cnt, final_score FROM trip_log WHERE user_id = %s AND timestamp BETWEEN %s AND %s """, (user_name, start_date, end_date)) # Fetch all results rows = self.cur.fetchall() # Format the results into a list of dictionaries result = [] final_scores = [] daily_scores = {} for row in rows: trip_log = { "trip_id": row[0], "timestamp": row[1].strftime("%Y-%m-%d %H:%M:%S"), # Format timestamp "total_distance_m": row[2], "total_time_s": row[3], "abrupt_start_count": row[4], "abrupt_stop_count": row[5], "abrupt_acceleration_count": row[6], "abrupt_deceleration_count": row[7], "helmet_on": bool(row[8]), # Convert INT to Boolean "turn_noslow_cnt" : row[9], "final_score": row[10] } result.append(trip_log) final_scores.append(row[10]) # 날짜별 평균 계산을 위한 데이터 정리 date_key = row[1].strftime("%Y-%m-%d") if date_key not in daily_scores: daily_scores[date_key] = [] daily_scores[date_key].append(row[10]) # 전체 기간의 final_score 평균 계산 overall_avg_score = round(sum(final_scores) / len(final_scores), 2) if final_scores else 0 # start_date와 end_date를 datetime 객체로 변환 (이미 datetime이라면 그대로 사용) if isinstance(start_date, str): start_dt = datetime.strptime(start_date, "%Y-%m-%d") else: start_dt = start_date if isinstance(end_date, str): end_dt = datetime.strptime(end_date, "%Y-%m-%d") else: end_dt = end_date # start_date부터 end_date까지 모든 날짜에 대해 평균 점수를 계산 (데이터가 없으면 0) daily_avg_scores = {} current_dt = start_dt while current_dt <= end_dt: day_key = current_dt.day # 예: 1, 2, 3 등 full_date_key = current_dt.strftime("%Y-%m-%d") scores = daily_scores.get(full_date_key, []) daily_avg_scores[day_key] = round(sum(scores) / len(scores), 2) if scores else 0 current_dt += timedelta(days=1) # Convert the result list to JSON format response_data = { "history": result, "overall_avg_score": overall_avg_score, "daily_avg_scores": daily_avg_scores } return json.dumps(response_data), 200 except psycopg2.Error as e: self.conn.rollback() return {'status': 'error', 'message': str(e)}, 500 def close_connection(self): cur = self.cur cur.close() return True