import uuid         # For generating unique trade identifiers (UUIDs).
import json         # For handling JSON serialization of trade data.
import sqlite3      # For interacting with the SQLite database backend.
import datetime     # For managing timestamps and date-time operations.
import pandas as pd # For data manipulation and analysis, particularly with DataFrames.
import matplotlib.pyplot as plt # For creating visualizations of trade analytics.
from pathlib import Path  # For object-oriented filesystem paths.
from typing import Optional, List # For type hinting, enhancing code readability and maintainability.
import random # Import the random module for generating simulated trade data.

# Define the base directory for storing all trade log files.
LOG_DIR = Path("/home/trade_logs")
# Create the log directory if it does not exist, including any necessary parent directories.
LOG_DIR.mkdir(parents=True, exist_ok=True)

# Define the specific file paths for each logging backend within the LOG_DIR.
CSV_PATH  = LOG_DIR / "trades.csv"   # Path for the CSV log file.
JSON_PATH = LOG_DIR / "trades.json"  # Path for the JSON log file (newline-delimited).
DB_PATH   = LOG_DIR / "trades.db"    # Path for the SQLite database file.

def _generate_base_trade_data() -> dict:
    """
    Generates a base dictionary with a unique trade ID and current UTC timestamp.
    This ensures consistent ID and timestamp generation for all trade entries.
    """
    return {
        "trade_id": str(uuid.uuid4()),
        "timestamp": datetime.datetime.now(datetime.UTC).isoformat(),
    }

def create_open_trade_entry(exchange: str, symbol: str, direction: str, order_type: str,
                            entry_price: float, quantity: float, fee_usd: float, strategy: str) -> dict:
    """
    Generates a dictionary representing a position open event.

    Parameters
    ----------
    exchange    : Name of the trading exchange.
    symbol      : Trading pair.
    direction   : Position direction ('long' or 'short').
    order_type  : Order type ('market' or 'limit').
    entry_price : Fill price at entry.
    quantity    : Position size.
    fee_usd     : Fees incurred at entry.
    strategy    : Identifier of the strategy.

    Returns
    -------
    dict
        A dictionary containing trade details for an open position.
    """
    base_data = _generate_base_trade_data()
    return {
        **base_data,
        "exchange": exchange,
        "symbol": symbol,
        "direction": direction,
        "order_type": order_type,
        "entry_price": entry_price,
        "exit_price": 0.0,
        "quantity": quantity,
        "pnl_usd": 0.0,
        "pnl_pct": 0.0,
        "fee_usd": fee_usd,
        "reason": "open",
        "strategy": strategy,
    }

def create_close_trade_entry(exchange: str, symbol: str, direction: str, order_type: str,
                             entry_price: float, exit_price: float, quantity: float, pnl_usd: float,
                             pnl_pct: float, fee_usd: float, reason: str, strategy: str) -> dict:
    """
    Generates a dictionary representing a position close event.

    Parameters
    ----------
    exchange    : Name of the trading exchange.
    symbol      : Trading pair.
    direction   : Position direction ('long' or 'short').
    order_type  : Order type ('market' or 'limit').
    entry_price : Fill price at entry.
    exit_price  : Fill price at exit.
    quantity    : Position size.
    pnl_usd     : Realized PnL in USD.
    pnl_pct     : Realized PnL as a percentage.
    fee_usd     : Fees incurred at exit.
    reason      : Reason for trade closure.
    strategy    : Identifier of the strategy.

    Returns
    -------
    dict
        A dictionary containing trade details for a closed position.
    """
    base_data = _generate_base_trade_data()
    return {
        **base_data,
        "exchange": exchange,
        "symbol": symbol,
        "direction": direction,
        "order_type": order_type,
        "entry_price": entry_price,
        "exit_price": exit_price,
        "quantity": quantity,
        "pnl_usd": pnl_usd,
        "pnl_pct": pnl_pct,
        "fee_usd": fee_usd,
        "reason": reason,
        "strategy": strategy,
    }

def initialize_trade_db(db_path: Path) -> None:
    """
    Establishes a connection to the SQLite database and creates the 'trades' table
    if it does not already exist. The 'trade_id' field is designated as the primary key
    to ensure data integrity and prevent duplicate entries.
    """
    conn = sqlite3.connect(db_path) # Establish a connection to the SQLite database.
    conn.execute("""
        CREATE TABLE IF NOT EXISTS trades (
            trade_id    TEXT PRIMARY KEY,
            timestamp   TEXT,
            exchange    TEXT,
            symbol      TEXT,
            direction   TEXT,
            order_type  TEXT,
            entry_price REAL,
            exit_price  REAL,
            quantity    REAL,
            pnl_usd     REAL,
            pnl_pct     REAL,
            fee_usd     REAL,
            reason      TEXT,
            strategy    TEXT
        )
    """)
    conn.commit() # Commit the transaction to create the table.
    conn.close()  # Close the database connection.

def write_trade_to_csv(trade_data: dict, csv_path: Path) -> None:
    """
    Appends a trade record to the CSV log file. If the file is newly created or empty,
    it ensures that the header row is written.

    Parameters
    ----------
    trade_data : dict
        The dictionary containing the trade data to be appended to the CSV.
    csv_path : Path
        The file path to the CSV log file.
    """
    row = pd.DataFrame([trade_data]) # Convert trade data dictionary to a DataFrame row.
    row.to_csv(
        csv_path,
        mode="a", # Append mode.
        header=not csv_path.exists() or csv_path.stat().st_size == 0, # Write header only if file is new/empty.
        index=False, # Do not write DataFrame index.
    )

def write_trade_to_json(trade_data: dict, json_path: Path) -> None:
    """
    Appends a trade record to the JSON log file in a newline-delimited JSON (NDJSON) format.

    Parameters
    ----------
    trade_data : dict
        The dictionary containing the trade data to be appended to the JSON file.
    json_path : Path
        The file path to the JSON log file.
    """
    with open(json_path, "a") as f: # Open JSON file in append mode.
        f.write(json.dumps(trade_data) + "\n") # Write JSON representation of the trade followed by a newline.

def write_trade_to_db(trade_data: dict, db_path: Path) -> None:
    """
    Inserts a trade record into the SQLite database. The `INSERT OR IGNORE` clause
    prevents duplicate entries based on the `trade_id` primary key.

    Parameters
    ----------
    trade_data : dict
        The dictionary containing the trade data to be inserted into the database.
    db_path : Path
        The file path to the SQLite database file.
    """
    conn = sqlite3.connect(db_path) # Establish database connection.
    conn.execute("""
        INSERT OR IGNORE INTO trades VALUES (
            :trade_id, :timestamp, :exchange, :symbol, :direction, :order_type,
            :entry_price, :exit_price, :quantity, :pnl_usd, :pnl_pct,
            :fee_usd, :reason, :strategy
        )
    """, trade_data) # Execute SQL insert statement with dictionary parameters.
    conn.commit() # Commit the transaction.
    conn.close()  # Close the database connection.

def log_trade_entry(trade_data: dict, csv_path: Path, json_path: Path, db_path: Path) -> None:
    """
    Persists a trade record to all configured storage backends (CSV, JSON, SQLite).

    Parameters
    ----------
    trade_data : dict
        The dictionary containing the trade data to be recorded.
    csv_path : Path
        The file path to the CSV log file.
    json_path : Path
        The file path to the JSON log file.
    db_path : Path
        The file path to the SQLite database file.
    """
    write_trade_to_csv(trade_data, csv_path) # Write trade data to the CSV file.
    write_trade_to_json(trade_data, json_path) # Write trade data to the JSON file.
    write_trade_to_db(trade_data, db_path) # Write trade data to the SQLite database.
    # Output log entry for user feedback.
    print(f"[LOG] {trade_data['reason'].upper():<15} | {trade_data['exchange']:<7} {trade_data['symbol']:<7} | "
          f"PnL: ${trade_data['pnl_usd']:9.2f} ({trade_data['pnl_pct']:7.2f}%) | ID: {trade_data['trade_id'][:8]}...")

def load_all_trades(db_path: Path) -> pd.DataFrame:
    """
    Retrieves all trade records from the SQLite database.

    Parameters
    ----------
    db_path : Path
        The file path to the SQLite database file.

    Returns
    -------
    pd.DataFrame
        A Pandas DataFrame containing all logged trades, ordered by timestamp.
    """
    conn = sqlite3.connect(db_path) # Establish database connection.
    df   = pd.read_sql("SELECT * FROM trades ORDER BY timestamp ASC", conn) # Read all trades into a DataFrame.
    conn.close() # Close the database connection.
    return df

def load_trades_by_symbol(symbol: str, db_path: Path) -> pd.DataFrame:
    """
    Retrieves trade records filtered by a specific trading symbol from the SQLite database.

    Parameters
    ----------
    symbol : str
        The trading symbol to filter by (e.g., 'BTCUSDT').
    db_path : Path
        The file path to the SQLite database file.

    Returns
    -------
    pd.DataFrame
        A Pandas DataFrame containing trades for the specified symbol, ordered by timestamp.
    """
    conn = sqlite3.connect(db_path) # Establish database connection.
    # Read trades for the specific symbol into a DataFrame.
    df   = pd.read_sql("SELECT * FROM trades WHERE symbol=? ORDER BY timestamp", conn, params=(symbol,))
    conn.close() # Close the database connection.
    return df

def load_trades_by_strategy(strategy: str, db_path: Path) -> pd.DataFrame:
    """
    Retrieves trade records filtered by a specific strategy from the SQLite database.

    Parameters
    ----------
    strategy : str
        The strategy identifier to filter by.
    db_path : Path
        The file path to the SQLite database file.

    Returns
    -------
    pd.DataFrame
        A Pandas DataFrame containing trades for the specified strategy, ordered by timestamp.
    """
    conn = sqlite3.connect(db_path) # Establish database connection.
    # Read trades for the specific strategy into a DataFrame.
    df   = pd.read_sql("SELECT * FROM trades WHERE strategy=? ORDER BY timestamp", conn, params=(strategy,))
    conn.close() # Close the database connection.
    return df

def generate_performance_report(db_path: Path) -> pd.DataFrame:
    """
    Generates a performance report summarizing key statistics per trading strategy from logged trades.
    Only closed trades (i.e., `reason != "open"`) are included in the analysis.

    Parameters
    ----------
    db_path : Path
        The file path to the SQLite database file.

    Returns
    -------
    pd.DataFrame
        A Pandas DataFrame containing strategy-level performance statistics such as total trades,
        total PnL, average PnL percentage, win rate, maximum win/loss percentages, and total fees.
        Returns a message DataFrame if no closed trades are found.
    """
    df = load_all_trades(db_path) # Load all trade data.
    df = df[df["reason"] != "open"]   # Exclude open positions as they do not have realized PnL.

    if df.empty:
        # Return a DataFrame indicating no closed trades if the filtered DataFrame is empty.
        return pd.DataFrame([{"Message": "No closed trades found for report generation."}])

    # Convert PnL columns to numeric types for aggregation.
    df["pnl_usd"] = pd.to_numeric(df["pnl_usd"])
    df["pnl_pct"] = pd.to_numeric(df["pnl_pct"])

    # Group by strategy and aggregate relevant metrics.
    report = df.groupby("strategy").agg(
        total_trades  = ("trade_id",  "count"), # Count of closed trades per strategy.
        total_pnl_usd = ("pnl_usd",  "sum"),   # Sum of realized PnL in USD.
        avg_pnl_pct    = ("pnl_pct",  "mean"),  # Mean realized PnL percentage.
        win_rate      = ("pnl_pct",  lambda x: (x > 0).mean() * 100), # Percentage of winning trades.
        max_win_pct   = ("pnl_pct",  "max"),   # Maximum PnL percentage achieved.
        max_loss_pct  = ("pnl_pct",  "min"),   # Minimum PnL percentage (largest loss).
        total_fees    = ("fee_usd",  "sum"),   # Total fees incurred.
    ).round(2).reset_index() # Round numerical results to two decimal places and reset index.
    return report

# Initialize the database before logging any trades.
initialize_trade_db(DB_PATH)

# ── Simulate a sequence of trades ─────────────────────────────────────────

# Define predefined lists for simulating trade attributes.
strategies = ["btc_1h", "eth_4h", "sol_1d"]
exchanges  = ["Binance", "Bybit", "Kraken"]

# Loop 20 times to simulate 20 individual trade events.
for i in range(20):
    strategy  = random.choice(strategies) # Randomly select a strategy.
    exchange  = random.choice(exchanges)  # Randomly select an exchange.
    symbol    = random.choice(["BTCUSDT", "ETHUSDT", "SOLUSDT"]) # Randomly select a trading symbol.
    direction = random.choice(["long", "short"]) # Randomly select trade direction.

    # Determine entry price based on the symbol for realistic simulation.
    entry     = random.uniform(45_000, 70_000) if "BTC" in symbol else random.uniform(150, 300)
    qty       = round(random.uniform(0.01, 0.5), 4) # Randomly generate quantity and round to 4 decimal places.

    # Simulate PnL percentage using a Gaussian distribution with a slight positive bias.
    pnl_pct   = random.gauss(0.3, 1.5)
    # Calculate exit price based on entry price and PnL percentage.
    exit_p    = entry * (1 + pnl_pct / 100)
    # Calculate PnL in USD based on direction.
    pnl_usd   = (exit_p - entry) * qty if direction == "long" else (entry - exit_p) * qty
    fee       = qty * entry * 0.0004 # Calculate a fixed percentage fee.

    # Randomly select a reason for trade closure.
    reason    = random.choice(["take_profit", "stop_loss", "direction_change"])

    # Create a trade entry using the create_close_trade_entry function.
    trade_entry = create_close_trade_entry(
        exchange=exchange, symbol=symbol, direction=direction,
        order_type="market", entry_price=round(entry, 2), exit_price=round(exit_p, 2),
        quantity=qty, pnl_usd=round(pnl_usd, 4), pnl_pct=round(pnl_pct, 4),
        fee_usd=round(fee, 4), reason=reason, strategy=strategy,
    )
    # Log the simulated trade using the log_trade_entry function.
    log_trade_entry(trade_entry, CSV_PATH, JSON_PATH, DB_PATH)

print("\n--- Performance Report ---") # Print a header for the performance report.
# Generate and display the performance report as a string.
print(generate_performance_report(DB_PATH).to_string(index=False))