import sqlite3
from sqlite3 import Error
import socket
import threading
import pandas as pd
import yfinance as yf
import os

class Database:
    def __init__(self):
        self.conn = self.create_connection()

    def create_connection(self):
        conn = None
        try:
            conn = sqlite3.connect('clients.db')
            print(f'successful connection with sqlite version {sqlite3.version}')
        except Error as e:
            print(e)
        return conn

    def create_table(self, create_table_sql):
        try:
            c = self.conn.cursor()
            c.execute(create_table_sql)
        except Error as e:
            print(e)

    def setup_database(self):
        sql_create_user_table = """CREATE TABLE IF NOT EXISTS clients (
                                    client_id VARCHAR(20) NOT NULL PRIMARY KEY,
                                    password VARCHAR(255) NOT NULL,
                                    age INT,
                                    initial_investment FLOAT,
                                    investment_type VARCHAR(255)
                                    );"""
        self.create_table(sql_create_user_table)

    def create_user(self, user):
        sql = '''INSERT INTO clients(client_id, password, age, initial_investment, investment_type)
                 VALUES(?,?,?,?,?)'''
        cur = self.conn.cursor()
        cur.execute(sql, user)
        self.conn.commit()
        return cur.lastrowid

    def get_all_users(self):
        cur = self.conn.cursor()
        cur.execute("SELECT * FROM clients")
        return cur.fetchall()

    def user_exists(self, client_id, password):
        cur = self.conn.cursor()
        cur.execute("SELECT * FROM clients WHERE client_id = ? AND password = ?", (client_id, password))
        return len(cur.fetchall()) > 0

    def export_to_csv(self, filename):
        df = pd.read_sql_query("SELECT * FROM clients", self.conn)
        df.to_csv(filename, index=False)

    def import_from_csv(self, filename):
        df = pd.read_csv(filename)
        df.to_sql('clients', self.conn, if_exists='replace', index=False)

class AssetManager:
    @staticmethod
    def get_asset_prices():
        assets = ['ETH-USD', 'DOGE-USD', 'BTC-USD', 'GC=F', 'SI=F']
        prices = yf.download(assets, start='2023-03-18', end='2023-03-19')['Close']
        return prices.iloc[-1].to_dict()

class Server:
    def __init__(self, database, asset_manager):
        self.database = database
        self.asset_manager = asset_manager

    def handle_client(self, client_socket):
        while True:
            data = client_socket.recv(1024).decode("utf-8")
            if not data:
                break
            print(f"Received data: {data}")

        request_type, *request_data = data.split("|")

        if request_type == "signup":
            client_id, password, age, initial_investment, investment_type = request_data
            print(f"Signup data: {client_id}, {password}, {age}, {initial_investment}, {investment_type}")
            if not self.database.user_exists(client_id, password):
                self.database.create_user((client_id, password, age, initial_investment, investment_type))
                response = "signup_success|Account created successfully."
            else:
                response = "signup_failure|Account with this ID and password already exists."
        elif request_type == "login":
            client_id, password = request_data
            if self.database.user_exists(client_id, password):
                response = "login_success|Login successful."
            else:
                response = "login_failure|Invalid ID or password."
        elif request_type == "view_portfolio":
            asset_prices = self.asset_manager.get_asset_prices()
            response = "view_portfolio|"
            for asset, price in asset_prices.items():
                response += f"{asset}: {price}\n"

            all_users = self.database.get_all_users()
            response += "\nList of connected users:\n"
            for user in all_users:
                response += f"{user[0]}: {user[1]}\n"
            if request_type in ["signup", "login"]:
                if "success" in response:
                    self.database.export_to_csv('clients.csv')

            client_socket.sendall(response.encode("utf-8"))

    def start(self):
        if not os.path.exists('clients.csv'):
            self.database.export_to_csv('clients.csv')
        self.database.import_from_csv('clients.csv')

        server = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        server.bind(('localhost', 5000))
        server.listen(5)
        server.settimeout(5)
        print("Server started. Waiting for connections...")

        while True:
            try:
                client, address = server.accept()
                client.settimeout(5)  # Set the timeout for the new socket
                print(f"Connection from {address} has been established.")
                client_thread = threading.Thread(target=self.handle_client, args=(client,))
                client_thread.start()
            except socket.timeout:
                print("No new connection received. Continuing to wait...")

def main():
    db = Database()
    db.setup_database()
    am = AssetManager()
    server = Server(db, am)
    try:
        server.start()
    except KeyboardInterrupt:
        print("Server stopped. Exporting data to 'clients.csv'...")
        db.export_to_csv('clients.csv')
        print("Data exported successfully.")

if __name__ == "__main__":
    main()