import asyncio
import websockets
import ssl
import time
import json
import logging
import mysql.connector
import math
from datetime import datetime
from flask import Flask, request, jsonify

#######################################
# CONFIGURATION
#######################################

# Set up logging
logger = logging.getLogger(__name__)
logger.setLevel(logging.INFO)

# Create a file handler
file_handler = logging.FileHandler('/home/magsbae/projects/stocks/v1_0/bot_logs.log')  # Logs will be written to this file
file_handler.setLevel(logging.INFO)

# Create a logging format
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
file_handler.setFormatter(formatter)

# Add the file handler to the logger
logger.addHandler(file_handler)

app = Flask(__name__)

# XTB API server URL for demo
XTB_DEMO_SERVER = 'wss://ws.xtb.com/demo'
ssl_context = ssl.create_default_context()

# Hardcoded credentials (for testing only!)
USER_ID = "17242117"
PASSWORD = "Xtb@2022!!"


# # MySQL Database Configuration
# db_config = {
#     "host": "localhost",
#     "port": 3306,
#     "user": "root",
#     "password": "My@2022!!",  # Replace with your MySQL password
#     "database": "trading_system"
# }

# MySQL Database Configuration prod stock
db_config = {
    "host": "d1282.lon1.mysecurecloudhost.com",
    "port": 3306,
    "user": "magsbae_it",
    "password": "OandA@2025",  # Replace with your MySQL password
    "database": "magsbae_stock_system"
}

def get_xtb_symbol(tradingview_symbol):
    """Fetch the corresponding XTB symbol for a given TradingView symbol from the database."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Query the symbol_mapping table
        cursor.execute("SELECT xtb_symbol FROM xtb_symbol_mapping WHERE tradingview_symbol = %s", (tradingview_symbol,))
        result = cursor.fetchone()

        # Close the connection
        conn.close()

        # Return the XTB symbol if found, otherwise None
        return result['xtb_symbol'] if result else None
    except Exception as e:
        logger.error(f"Error fetching XTB symbol for {tradingview_symbol}: {e}")
        return None


# Helper Functions
def get_db_connection():
    """Establish a connection to the MySQL database."""
    try:
        conn = mysql.connector.connect(**db_config)
        logger.info("DB Connection Success")
        return conn
    except mysql.connector.Error as err:
        print(f"Error connecting to the database: {err}")
        return None
    
def authenticate_user(api_key):
    """Authenticate a user based on the API key."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        query = "SELECT * FROM users WHERE api_key = %s"
        cursor.execute(query, (api_key,))
        user = cursor.fetchone()
        conn.close()

        if user:
            print(f"User authenticated: {user['email']}")
            return user
        else:
            print("Invalid API key.")
            return None
    except mysql.connector.Error as err:
        print(f"Database error during authentication: {err}")
        return None
    
#######################################
# LOGIN FUNCTION
#######################################

async def xtb_login(user_id, password):
    """Log in to XTB WebSocket and return the WebSocket connection."""
    try:
        websocket = await websockets.connect(XTB_DEMO_SERVER, ssl=ssl_context)
        
        login_request = {
            "command": "login",
            "arguments": {
                "userId": user_id,
                "password": password
            }
        }
        await websocket.send(json.dumps(login_request))
        response = await websocket.recv()
        response_data = json.loads(response)

        if response_data.get('status'):
            logger.info("Login successful.")
            return websocket
        else:
            error_descr = response_data.get('errorDescr', 'No error description provided')
            logger.error(f"Login failed: {error_descr}")
            await websocket.close()
            return None
    except Exception as e:
        logger.exception("An exception occurred during login")
        return None

async def fetch_balance():
    """Fetch the free margin balance from the XTB API."""
    websocket = await xtb_login(USER_ID, PASSWORD)
    if not websocket:
        return {"status": "error", "message": "Login failed"}
    try:
        # Request margin details
        balance_payload = {"command": "getMarginLevel"}
        await websocket.send(json.dumps(balance_payload))
        response = await websocket.recv()
        data = json.loads(response)

        if data.get("status"):
            margin_free = data["returnData"].get("margin_free")
            if margin_free is not None:
                logger.info(f"Margin Free: {margin_free}")
                print(f"Margin Free: {margin_free}")
                return {"status": "success", "margin_free": margin_free}
            else:
                logger.error("Margin free value not found in response.")
                print("Margin free value not found in response.")
                return {"status": "error", "message": "Margin free value not found"}
        else:
            error_descr = data.get('errorDescr', 'No error description provided')
            logger.error(f"Failed to fetch margin level: {error_descr}")
            print(f"Failed to fetch margin level: {error_descr}")
            return {"status": "error", "message": error_descr}
    except Exception as e:
        logger.exception("An error occurred while fetching balance.")
        print("An error occurred while fetching balance.")
        return {"status": "error", "message": str(e)}
    finally:
        await websocket.close()

#######################################
# TRADE EXECUTION FUNCTION
#######################################
async def execute_trade(websocket, symbol, action):
    """Execute a trade or place a pending order if the market is closed."""
    try:    
        # Fetch symbol price and market status
        price_request = {"command": "getSymbol", "arguments": {"symbol": symbol}}
        await websocket.send(json.dumps(price_request))
        price_response = await websocket.recv()
        price_data = json.loads(price_response)

        if not price_data.get('status'):
            error_descr = price_data.get('errorDescr', 'No error description provided')
            logger.error(f"Failed to fetch price for {symbol}: {error_descr}")
            print(f"Failed to fetch price for {symbol}: {error_descr}")
            return {"status": "error", "message": error_descr}

        ask_price = price_data['returnData']['ask']
        bid_price = price_data['returnData']['bid']
        price = ask_price if action.lower() == 'buy' else bid_price
        cmd = 0 if action.lower() == 'buy' else 1  # 0=buy, 1=sell

        margin_result = await (fetch_balance())
        if margin_result["status"] == "success":
            free_margin = margin_result["margin_free"]
            logger.info(f"Free Margin Retrieved: {free_margin}")
            print(f"Free Margin Retrieved: {free_margin}")
        else:
            logger.error("Failed to retrieve free margin.")
            print("Failed to retrieve free margin.")
            return jsonify(margin_result), 400
        
        
        # Get if Sympol is FX or STC in order to calculate size
        get_symbol_payload = {"command": "getSymbol","arguments":{"symbol": symbol }}
        await websocket.send(json.dumps(get_symbol_payload))
        get_symbol_payload_json_response = await websocket.recv()
        get_symbol_payload_data = json.loads(get_symbol_payload_json_response)


        sympol_type = get_symbol_payload_data["returnData"]["categoryName"]
        if sympol_type == "FX":
            newVolume =round(math.floor(free_margin / price)/10000,2) 
        else:
            newVolume =round(math.floor(free_margin / price)*0.95 ,2)

        # newVolume = 0.01
 
        # Execute trade if the market is open
        trade_request = {
            "command": "tradeTransaction",
            "arguments": {
                "tradeTransInfo": {
                    "cmd": cmd,
                    "symbol": str(symbol),
                    "type": 0,  # 0=open trade
                    "volume": newVolume,
                    "price": float(price),
                    "customComment": "TradingView Signal"
                }
            }
        }
        await websocket.send(json.dumps(trade_request))
        trade_request_response = await websocket.recv()
        trade_request_response_data = json.loads(trade_request_response)
   
        if trade_request_response_data.get('status'):
            fakeposition_id = trade_request_response_data['returnData'].get('order')
            logger.info ("Initial Trade is succesful")
            print("Initial Trade is succesful")
        else: 
            logger.error("Initial trade didni't work")
            print("Initial trade didni't work")
        
        trade_request_confirmation = {
            "command": "tradeTransactionStatus",
            "arguments": {
                "order": fakeposition_id
            }
        }

        await websocket.send(json.dumps(trade_request_confirmation))
        trade_request_confirmation_response = await websocket.recv()
        trade_request_confirmation_data = json.loads(trade_request_confirmation_response)

        if trade_request_confirmation_data.get('status'):
            if trade_request_response_data['returnData'].get('message') is None:
                # Perform some action
                logger.info ("secondary Trade is succesful")
                print("secondary Trade is succesful")
                return {"status": "success" , "price": price, "Volume" : newVolume, "pre_position" : fakeposition_id}
            else:
                print(f"secondary trade is not succesful because: {trade_request_response_data['returnData'].get('message')}")
                logger.error(f"secondary trade is not succesful because: {trade_request_response_data['returnData'].get('message')}")
      
        else:
            print("returnData is not a list or is missing.")
            error_descr = trade_request_response_data.get('errorDescr', 'No error description provided')
            logger.error(f"Trade execution failed: {error_descr}")
            print(f"Trade execution failed: {error_descr}")
            return {"status": "error", "message": error_descr}

    except Exception as e:
        logger.exception("An exception occurred during trade execution")
        print("An exception occurred during trade execution")
        return {"status": "error", "message": "Trade execution error"}

def add_position(user_id, tv_position_id, symbol, size, buy_timestamp_tv, buy_timestamp_xtb, buy_price_tv, buy_price_xtb, pre_position):
    """Add a new position to the database."""
    logger.info("Starting add_position function.")
    print("Starting add_position function.")
    try:
        logger.info("Formatting buy_timestamp_xtb.")
        print("Formatting buy_timestamp_xtb.")
        formatted_buy_timestamp_xtb = datetime.fromtimestamp(buy_timestamp_xtb).strftime('%Y-%m-%d %H:%M:%S')
        logger.info(f"Formatted buy_timestamp_xtb: {formatted_buy_timestamp_xtb}")
        print(f"Formatted buy_timestamp_xtb: {formatted_buy_timestamp_xtb}")

        logger.info("Getting database connection.")
        print("Getting database connection.")
        conn = get_db_connection()
        cursor = conn.cursor()

        logger.info("Preparing SQL query.")
        print("Preparing SQL query.")
        query = """
        INSERT INTO xtb_positions (user_id, tv_position_id, symbol, size, buy_timestamp_tv, buy_timestamp_xtb, buy_price_tv, buy_price_xtb, pre_position, status)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, 'open')
        """
        values = (user_id, tv_position_id, symbol, size, buy_timestamp_tv, formatted_buy_timestamp_xtb, buy_price_tv, buy_price_xtb, pre_position)
        logger.info(f"SQL Query: {query}")
        print(f"SQL Query: {query}")
        logger.info(f"Values: {values}")
        print(f"Values: {values}")

        logger.info("Executing SQL query.")
        print("Executing SQL query.")
        cursor.execute(query, values)
        conn.commit()
        logger.info("Position added successfully to the database.")
        print("Position added successfully to the database.")
    except mysql.connector.Error as err:
        logger.error(f"Error adding position to the database: {err}")
        print(f"Error adding position to the database: {err}")
    except Exception as e:
        logger.error(f"Unexpected error: {e}")
        print(f"Unexpected error: {e}")
    finally:
        if 'conn' in locals() and conn.is_connected():
            logger.info("Closing database connection.")
            print("Closing database connection.")
            conn.close()

def update_sell_position(tv_position_id, xtb_position_id, sell_price_xtb, sell_timestamp_xtb,tv_timestamp, sell_price_tv):
    """Update a sell position in the database."""
    try:
        formatted_sell_timestamp_xtb = datetime.fromtimestamp(sell_timestamp_xtb).strftime('%Y-%m-%d %H:%M:%S')
        conn = get_db_connection()
        cursor = conn.cursor()

        query = """
        UPDATE xtb_positions
        SET sell_price_xtb = %s, sell_timestamp_xtb = %s, sell_timestamp_tv = %s, sell_price_tv = %s, xtb_buy_position_id =%s, status = 'closed'
        WHERE tv_position_id = %s
        """
        values = (sell_price_xtb, formatted_sell_timestamp_xtb,tv_timestamp,sell_price_tv,xtb_position_id, tv_position_id)
        cursor.execute(query, values)
        conn.commit()
        print("Sell position updated successfully in the database.")
    except mysql.connector.Error as err:
        print(f"Error updating sell position in the database: {err}")
    finally:
        if conn.is_connected():
            conn.close()


#######################################
# CLOSE TRADE FUNCTION
#######################################

async def process_trade():
    try:
        logger.info("Starting process_trade function...")
        print("Starting process_trade function...")
        websocket = await xtb_login(USER_ID, PASSWORD)
        if not websocket:
            logger.error("Login failed during process_trade.")
            print("Login failed during process_trade.")
            return {"status": "error", "message": "Login failed"}

        logger.info(f"Action: {action}")
        print(f"Action: {action}")
        if action.lower() == "buy":
            logger.info("Executing buy trade...")
            print("Executing buy trade...")
            result = await execute_trade(websocket, symbol, action)
            logger.info(f"Buy trade result: {result}")
            print(f"Buy trade result: {result}")
            return result
        
        elif action.lower() in ["sl", "min_tp", "tp"]:
            logger.info("Executing sell trade...")
            print("Executing sell trade...")
            if not tv_position_id:
                logger.error("Position ID is required for sell action.")
                print("Position ID is required for sell action.")
                return {"status": "error", "message": "Position ID required for sell action"}
            result = await close_trade(websocket, symbol, tv_position_id, user_id)
            logger.info(f"Sell trade result: {result}")
            print(f"Sell trade result: {result}")
            return result
        else:
            logger.error("Invalid action provided.")
            print("Invalid action provided.")
            return {"status": "error", "message": "Invalid action"}
    except Exception as e:
        logger.exception("An error occurred in process_trade")
        print(f"An error occurred in process_trade: {str(e)}")
        return {"status": "error", "message": str(e)}

async def fetch_open_trades(websocket):
    """Fetch all open trades."""
    try:
        request = {
            "command": "getTrades",
            "arguments": {"openedOnly": True}
        }
        await websocket.send(json.dumps(request))
        response = await websocket.recv()
        return json.loads(response)
    except Exception as e:
        logger.exception("An exception occurred while fetching open trades")
        print(f"An exception occurred while fetching open trades: {str(e)}")
        return {"status": False, "errorDescr": "Error fetching open trades"}

async def close_trade(websocket, symbol, tv_position_id, user_id):
    """Close a specific trade and retrieve sell details."""
    try:

        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)

        # Fetch the open position details
        query = "SELECT * FROM xtb_positions WHERE user_id = %s AND tv_position_id = %s AND status = 'open'"
        cursor.execute(query, (user_id, tv_position_id))
        position = cursor.fetchone()
        # sellpositionid = int(position['xtb_buy_position_id'])
        volume = float(position['size'])
        pre_position = int (position['pre_position'])
        
        trade_returned_data = {
            "command": "getTrades",
            "arguments": {"openedOnly": True}
        }
                     
        await websocket.send(json.dumps(trade_returned_data))
        trade_returned_data_response = await websocket.recv()
        trade_returned_data_response_data = json.loads(trade_returned_data_response)

        if isinstance(trade_returned_data_response_data.get('returnData', []), list):
            for trade in trade_returned_data_response_data['returnData']:
                if trade.get('order2') == pre_position:  # Check if the order2 matches
                    logger.info(f"RETURNED DATA: {trade}")
                    print(f"RETURNED DATA: {trade}")
                    sellpositionid = trade.get('position')  # Assign the corresponding position to second_id
                    break
            else:
                logger.error(f"No position id is found buddy")
                print(f"No position id is found buddy")
                return {"status": "error"}


        # Fetch symbol price
        price_request = {"command": "getSymbol", "arguments": {"symbol": symbol}}
        await websocket.send(json.dumps(price_request))
        price_response = await websocket.recv()
        price_data = json.loads(price_response)

        if not price_data.get('status'):
            error_descr = price_data.get('errorDescr', 'No error description provided')
            logger.error(f"Failed to fetch price for {symbol}: {error_descr}")
            print(f"Failed to fetch price for {symbol}: {error_descr}")
            return {"status": "error", "message": error_descr}

        bid_price = price_data['returnData']['bid']
        logger.info(f"Using bid price: {bid_price}")
        print(f"Using bid price: {bid_price}")

        # Construct close trade request
        close_request = {
            "command": "tradeTransaction",
            "arguments": {
                "tradeTransInfo": {
                    "symbol": str(symbol),
                    "type": 2,
                    "volume": volume,
                    "price": float(bid_price),
                    "order": sellpositionid
                }
            }
        }
        logger.info(f"Close trade payload: {json.dumps(close_request, indent=2)}")
        print(f"Close trade payload: {json.dumps(close_request, indent=2)}")

        # Send close request
        await websocket.send(json.dumps(close_request))
        close_response = await websocket.recv()
        close_data = json.loads(close_response)
        logger.info(f"Close trade response: {json.dumps(close_data, indent=2)}")
        print(f"Close trade response: {json.dumps(close_data, indent=2)}")

        if close_data.get('status'):
            logger.info("Trade closed successfully.")
            print("Trade closed successfully.")
            return {"status": "success", "position_id": sellpositionid, "price" : bid_price}
        else:
            error_descr = close_data.get('errorDescr', 'No error description provided')
            logger.error(f"Close trade failed: {error_descr}")
            print(f"Close trade failed: {error_descr}")
            return {"status": "error", "message": error_descr}

    except Exception as e:
        logger.exception("An exception occurred during trade closing")
        print(f"An exception occurred during trade closing: {str(e)}")
        return {"status": "error", "message": "Trade closing error"}

# # Function to insert a signal into the database
def insert_signal(symbol, signal_type):
    connection = mysql.connector.connect(**db_config)
    cursor = connection.cursor()

    try:
        # Get the last signal for this symbol
        # Change DB name here
        cursor.execute("SELECT signal_type FROM stocks_signals_consecutives WHERE symbol = %s ORDER BY id DESC LIMIT 1", (symbol,))
        last_signal = cursor.fetchone()

        # Determine if this signal is consecutive
        is_consecutive = 'TRUE' if last_signal and last_signal[0] == signal_type else None
        
        # Change DB name here
        # Insert the new signal
        query = """
            INSERT INTO stocks_signals_consecutives (symbol, signal_type, is_consecutive)
            VALUES (%s, %s, %s)
        """
        cursor.execute(query, (symbol, signal_type, is_consecutive))
        connection.commit()

    except mysql.connector.Error as e:
        print(f"Error inserting signal: {e}")
    finally:
        cursor.close()
        connection.close()

#######################################
# WEBHOOK ENDPOINT
#######################################
@app.route('/webhook', methods=['POST'])
def webhook():
    """Handle webhook signals and execute trades."""
    try:
        data = request.get_json(force=True)
        if not data:
            logger.error("No data received in webhook")
            print("No data received in webhook")
            return jsonify({'status': 'error', 'message': 'No data received'}), 400

        # Extract required fields from webhook payload
        global symbol, action, tv_position_id, user_id
        api_key = data.get("api_key")
        tv_timestamp = data.get("timestamp")
        tradingview_symbol  = data.get('symbol')
        action = data.get('action')
        position_id = data.get('position_id')  # For closing trades
        # volume = float(data.get('volume', 0.01))  # Default volume if not specified
        tv_position_id = data.get('tv_position_id')
        buy_price_tv = data.get("buy_price_tv", 0)
        sell_price_tv = data.get("sell_price_tv", 0)  # Default to 0.0 if not provided

        # Map TradingView symbol to XTB API symbol using the database
        xtb_symbol = get_xtb_symbol(tradingview_symbol)  # Query the database
        if not xtb_symbol:
            logger.error(f"Symbol {tradingview_symbol} not found in database mapping.")
            print(f"Symbol {tradingview_symbol} not found in database mapping.")
            return jsonify({'status': 'error', 'message': f'Symbol {tradingview_symbol} not found in mapping'}), 400

        logger.info(f"Mapped TradingView symbol {tradingview_symbol} to XTB API symbol {xtb_symbol}")
        print(f"Mapped TradingView symbol {tradingview_symbol} to XTB API symbol {xtb_symbol}")
        symbol = xtb_symbol  # Update symbol for further processing

        insert_signal(symbol,action)

        user = authenticate_user(api_key)
        if not user:
            logger.error("Invalid user key")
            print("Invalid user key") 
            return jsonify({"error": "Invalid user key"}), 403

        user_id = user["id"]

        # Check if required fields are present
        if not action or not symbol:
            logger.error(f"Missing required fields: action={action}, symbol={symbol}")
            print(f"Missing required fields: action={action}, symbol={symbol}")
            return jsonify({'status': 'error', 'message': 'Missing required fields'}), 400

        # Debugging the extracted fields
        logger.info(f"Symbol: {symbol}, Action: {action}, Position ID: {position_id}")
        print(f"Symbol: {symbol}, Action: {action}, Position ID: {position_id}")

        # Run the trade processing logic
        trade_result = asyncio.run(process_trade())
        if trade_result and trade_result.get("status") == "success":
            if action.lower() == "buy":
                buy_timestamp_xtb = time.time()
                buy_price_xtb = trade_result.get("price")  # Retrieve the buy price
                # xtb_position_id = trade_result.get("position_id")
                volume = trade_result.get("Volume")
                pre_position = trade_result.get("pre_position")
                # order_details = fetch_order_details(symbol, okx_position_id)
                # buy_price_xtb = order_details.get('fillPx', 0)
                add_position(user_id, tv_position_id, symbol, volume, tv_timestamp, buy_timestamp_xtb, buy_price_tv, buy_price_xtb, pre_position)
            
            elif action.lower() in ["sl", "min_tp", "tp"]:
                sell_timestamp_xtb = time.time()
                sell_price_xtb = trade_result.get("price")  # Retrieve the sell price
                xtb_position_id = trade_result.get("position_id")  # Use the provided position_id for the sell
                pre_position = trade_result.get("pre_position")
                update_sell_position(tv_position_id, xtb_position_id, sell_price_xtb, sell_timestamp_xtb, tv_timestamp, sell_price_tv)

        # Debugging the response sent to Postman
        logger.info(f"Trade Result: {trade_result}")

        return jsonify(trade_result), 200 if trade_result and trade_result.get("status") == "success" else 400

    except Exception as e:
        logger.exception("An error occurred in the webhook handler")
        return jsonify({'status': 'error', 'message': 'Invalid request data'}), 400


#######################################
# RUN THE FLASK APP
#######################################

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=6000, debug=True)