from config.db_config import get_db_connection
from decimal import Decimal

# Portfolio Functions
def add_to_portfolio(user_id, symbol, quantity, purchase_price):
		connection = get_db_connection()
		try:
				
				user_portfolios = get_portfolio_by_symbol(user_id, symbol)
				
				old_total = 0
				old_quantity = 0

				if user_portfolios is not None:
					old_total = user_portfolios[3]
					old_quantity = user_portfolios[4]
												
				total_cost = old_total + Decimal(purchase_price * quantity)
				total_quantity = old_quantity + quantity
				average_purchase_price = total_cost / total_quantity
						
				with connection.cursor() as cursor:
						cursor.execute("""
								INSERT INTO portfolio (user_id, symbol, quantity, purchase_price, average_purchase_price)
								VALUES (%s, %s, %s, %s, %s)
								ON CONFLICT (user_id, symbol) DO UPDATE
								SET quantity = portfolio.quantity + EXCLUDED.quantity,
										purchase_price = (portfolio.purchase_price * portfolio.quantity + EXCLUDED.purchase_price * EXCLUDED.quantity) / (portfolio.quantity + EXCLUDED.quantity);
						""", (user_id, symbol, quantity, purchase_price, average_purchase_price))
				connection.commit()
		finally:
				connection.close()

def remove_from_portfolio(user_id, symbol):
		connection = get_db_connection()
		try:
				with connection.cursor() as cursor:
						cursor.execute("""
								DELETE FROM portfolio
								WHERE user_id = %s AND symbol = %s;
						""", (user_id, symbol))
				connection.commit()
		finally:
				connection.close()

def get_portfolio(user_id):
		connection = get_db_connection()
		try:
				with connection.cursor() as cursor:
						cursor.execute("""
								SELECT symbol, quantity, purchase_price, average_purchase_price FROM portfolio
								WHERE user_id = %s;
						""", (user_id,))
						rows = cursor.fetchall()
						return [{"symbol": row[0], "quantity": row[1], "purchase_price": row[2], "average_purchase_price": row[3]} for row in rows]
		finally:
				connection.close()

def get_portfolio_by_symbol(user_id, symbol):
		connection = get_db_connection()
		try:
				with connection.cursor() as cursor:
						cursor.execute("""
								SELECT user_id, symbol, quantity, purchase_price, average_purchase_price FROM portfolio
								WHERE user_id = %s AND symbol = %s;
						""", (user_id,symbol,))
						row = cursor.fetchone()
						return row
		finally:
				connection.close()