const { Sequelize, Op } = require('sequelize');
const { sequelize, Order, Trade, Wallet, TradingPair, User, Transaction, Asset, AssetHolding, AssetLot, UserBalance, TradeSymbol, TradeHistory, CopyRelation, SymbolStats, Coin, MarketPair, SpotOrder, DerivativeOrder, DerivativePosition, BinaryTrade, MarketStats } = require('../models');
const { generateOrderId, generateTransactionId, mapAsset, formatNumberShort, mapPrice, mapPair, baseUrl, getUploadedFile } = require('../utils/helpers');
const { BadRequestError, NotFoundError, ForbiddenError, ConflictError, ValidationError } = require('../utils/errors');
const config = require('../config');
const logger = require('../utils/logger');
const stockData = require("../services/stockMarketData");
const forexMarketData = require('./forexMarketData');
const cryptoMarketData = require('./cryptoMarketData');

class TradeService {

  async getOverview(userId) {
    // Fetch all counts in parallel
    const [
      totalTrades,
      totalAssetLots,
      totalSpot,
      totalDerivatives,
      myTraders,
      profitResult
    ] = await Promise.all([
      TradeHistory.count({ where: { userId } }),
      AssetLot.count({ where: { userId } }),
      SpotOrder.count({ where: { user_id: userId } }),
      DerivativeOrder.count({ where: { userId } }),
      CopyRelation.count({
        where: {
          copierId: userId,
          status: 'active'
        }
      }),
      TradeHistory.findOne({
        attributes: [
          [
            Sequelize.literal(`
            COALESCE(
              SUM(
                CASE
                  WHEN result = 'won' THEN profit_amount
                  WHEN result = 'loss' THEN -profit_amount
                  ELSE 0
                END
              ),
              0
            )
          `),
            'profitAmount'
          ]
        ],
        where: { userId },
        raw: true
      })
    ]);

    // Today's trades
    const startOfToday = new Date();
    startOfToday.setHours(0, 0, 0, 0);

    const tradesToday = await TradeHistory.count({
      where: {
        userId,
        createdAt: {
          [Op.gte]: startOfToday
        }
      }
    });

    // Fetch recent records from all models
    const [
      trades,
      assetLots,
      spotOrders,
      derivativeOrders
    ] = await Promise.all([
      TradeHistory.findAll({
        where: { userId },
        include: [{
          model: MarketPair,
          as: 'pair'
        }],
        order: [['createdAt', 'DESC']],
        limit: 10
      }),

      AssetLot.findAll({
        where: { userId },
        include: [{
          model: MarketPair,
          as: 'pair'
        }],
        order: [['createdAt', 'DESC']],
        limit: 10
      }),

      SpotOrder.findAll({
        where: { user_id: userId },
        include: [{
          model: MarketPair,
          as: 'pair'
        }],
        order: [['createdAt', 'DESC']],
        limit: 10
      }),

      DerivativeOrder.findAll({
        where: { userId },
        include: [{
          model: MarketPair,
          as: 'pair'
        }],
        order: [['createdAt', 'DESC']],
        limit: 10
      })
    ]);

    // Merge and normalize all activities
    const recentActivities = [
      ...trades.map(item => ({
        id: item.id,
        model: 'Terminal',
        pair: item.pair?.symbol || '—',
        type: item.side,
        price: item.entryPrice,
        amount: item.volume,
        status: item.status,
        createdAt: item.createdAt
      })),

      ...assetLots.map(item => ({
        id: item.id,
        model: 'Stock',
        pair: item.pair?.symbol || '—',
        type: item.side || '',
        price: item.buyPrice,
        amount: item.quantity,
        status: item.status,
        createdAt: item.createdAt
      })),

      ...spotOrders.map(item => ({
        id: item.id,
        model: 'Spot',
        pair: item.pair?.symbol || '—',
        type: item.side,
        price: item.price,
        amount: item.quantity,
        status: item.status,
        createdAt: item.createdAt
      })),

      ...derivativeOrders.map(item => ({
        id: item.id,
        model: 'Forex',
        pair: item.pair?.symbol || '—',
        type: item.side,
        price: item.entryPrice,
        amount: item.quantity,
        status: item.status,
        createdAt: item.createdAt
      }))
    ]
      .sort((a, b) => new Date(b.createdAt) - new Date(a.createdAt))
      .slice(0, 10);

    const totalOrders =
      totalTrades +
      totalAssetLots +
      totalSpot +
      totalDerivatives;

    const totalProfit = Number(profitResult?.profitAmount || 0);

    return {
      recentTrades: recentActivities,

      totals: {
        all: totalOrders,
        trades: totalTrades,
        assetLots: totalAssetLots,
        spot: totalSpot,
        derivatives: totalDerivatives
      },

      marketStats: [
        {
          label: 'Total Orders',
          value: formatNumberShort(totalOrders),
          positive: true
        },
        {
          label: 'Total Profit',
          value: formatNumberShort(totalProfit),
          positive: totalProfit >= 0
        },
        {
          label: 'Trades Today',
          value: formatNumberShort(tradesToday),
          positive: true
        },
        {
          label: 'My Traders',
          value: formatNumberShort(myTraders),
          positive: true
        }
      ]
    };
  }


  async getAllHistory(userId) {
    // Fetch all counts in parallel
    const [
      totalTrades,
      totalAssetLots,
      totalSpot,
      totalDerivatives,
      myTraders,
      profitResult
    ] = await Promise.all([
      TradeHistory.count({ where: { userId } }),
      AssetLot.count({ where: { userId } }),
      SpotOrder.count({ where: { user_id: userId } }),
      DerivativeOrder.count({ where: { userId } }),
      CopyRelation.count({
        where: {
          copierId: userId,
          status: 'active'
        }
      }),
      TradeHistory.findOne({
        attributes: [
          [
            Sequelize.literal(`
            COALESCE(
              SUM(
                CASE
                  WHEN result = 'won' THEN profit_amount
                  WHEN result = 'loss' THEN -profit_amount
                  ELSE 0
                END
              ),
              0
            )
          `),
            'profitAmount'
          ]
        ],
        where: { userId },
        raw: true
      })
    ]);

    // Today's trades
    const startOfToday = new Date();
    startOfToday.setHours(0, 0, 0, 0);

    const tradesToday = await TradeHistory.count({
      where: {
        userId,
        createdAt: {
          [Op.gte]: startOfToday
        }
      }
    });

    // Fetch recent records from all models
    const [
      trades,
      assetLots,
      spotOrders,
      derivativeOrders
    ] = await Promise.all([
      TradeHistory.findAll({
        where: { userId },
        include: [{
          model: MarketPair,
          as: 'pair'
        }],
        order: [['createdAt', 'DESC']],
      }),

      AssetLot.findAll({
        where: { userId },
        include: [{
          model: MarketPair,
          as: 'pair'
        }],
        order: [['createdAt', 'DESC']],
      }),

      SpotOrder.findAll({
        where: { user_id: userId },
        include: [{
          model: MarketPair,
          as: 'pair'
        }],
        order: [['createdAt', 'DESC']],
      }),

      DerivativePosition.findAll({
        where: { userId },
        include: [{
          model: MarketPair,
          as: 'pair'
        }],
        order: [['opened_at', 'DESC']],
      })
    ]);

    // Merge and normalize all activities
    const recentActivities = [
      ...trades.map(item => ({
        id: item.id,
        model: 'Terminal',
        pair: item.pair?.symbol || '—',
        side: item.side,
        leverage: item.leverage || '',
        price: item.entryPrice,
        sl: item.stopLoss || '—',
        tp: item.takeProfit || '—',
        realizedPnl: item.profitAmount || null,
        amount: item.volume,
        status: item.status,
        createdAt: item.createdAt
      })),

      ...assetLots.map(item => ({
        id: item.id,
        model: 'Stock',
        pair: item.pair?.symbol || '—',
        side: item.side || '',
        leverage: item.leverage || '',
        price: item.buyPrice,
        amount: item.quantity,
        status: item.status,
        createdAt: item.createdAt
      })),

      ...spotOrders.map(item => ({
        id: item.id,
        model: 'Spot',
        pair: item.pair?.symbol || '—',
        side: item.side,
        leverage: item?.leverage || '',
        sl: item.stopLoss || '—',
        tp: item.takeProfit || '—',
        price: item.price,
        amount: item.quantity,
        status: item.status,
        createdAt: item.createdAt
      })),

      ...derivativeOrders.map(item => ({
        id: item.id,
        model: 'Forex',
        pair: item.pair?.symbol || '—',
        side: item.side,
        leverage: item.leverage || '',
        price: item.entry_price,
        sl: item.stopLoss || '—',
        tp: item.takeProfit || '—',
        realizedPnl: item.realized_pnl || null,
        amount: item.qty,
        status: item.status,
        createdAt: item.opened_at
      }))
    ]
      .sort((a, b) => new Date(b.createdAt) - new Date(a.createdAt));
    //.slice(0, 10);

    const totalOrders =
      totalTrades +
      totalAssetLots +
      totalSpot +
      totalDerivatives;

    const totalProfit = Number(profitResult?.profitAmount || 0);

    return {
      orders: recentActivities,

      totals: {
        all: totalOrders,
        trades: totalTrades,
        assetLots: totalAssetLots,
        spot: totalSpot,
        derivatives: totalDerivatives
      },

      marketStats: [
        {
          label: 'Total Orders',
          value: formatNumberShort(totalOrders),
          positive: true
        },
        {
          label: 'Total Profit',
          value: formatNumberShort(totalProfit),
          positive: totalProfit >= 0
        },
        {
          label: 'Trades Today',
          value: formatNumberShort(tradesToday),
          positive: true
        },
        {
          label: 'My Traders',
          value: formatNumberShort(myTraders),
          positive: true
        }
      ]
    };
  }


  async getAllAsset() {
    const assets = await MarketPair.findAll({
      where: { status: 'active' }
    })

    return assets
  }


  async generateTradingPair() {
    const getSymbol = await Asset.findAll({
      where: {
        type: 'fiat'
      }
    })
  }

  async getTerminalHistory(userId) {
    const { count, rows } = await TradeHistory.findAndCountAll({
      where: { userId },
      include: [{ model: MarketPair, as: 'pair' }],
      order: [["created_at", "DESC"]],
    });

    const closed = rows.filter(t => t.status === "closed" && t.winStatus);

    let pnl = 0, wonCount = 0, lossCount = 0;

    for (const t of closed) {
      const p = Number(t.profitAmount ?? 0);
      if (t.winStatus === "won") { wonCount++; pnl += p; }
      else if (t.winStatus === "loss") { lossCount++; pnl -= p; }
    }

    const totalClosed = closed.length;

    return {
      trades: rows.map(r => ({
        orderId: r.orderId,
        tradedBy: r.tradedBy,
        symbol: r.pair?.symbol,
        side: r.side,
        orderType: r.orderType,
        volume: r.volume,
        entryPrice: r.entryPrice,
        requestedPrice: r.requestedPrice,
        leverage: r.leverage,
        duration: r.duration,
        stopLoss: r.stopLoss,
        takeProfit: r.takeProfit,
        status: r.status,
        result: r.result,
        profitAmount: r.profitAmount,
        closedAt: r.closedAt,
        createdAt: r.createdAt,
        updatedAt: r.updateAt
      })),
      stats: {
        total: count,
        totalClosed,
        totalWon: wonCount,
        totalLoss: lossCount,
        wonPercentage: totalClosed > 0 ? Number(((wonCount / totalClosed) * 100).toFixed(2)) : 0,
        pnl: Number(pnl.toFixed(2)),
      },
    };
  }

  async getTerminalData(userId) {
    const [tradingPair, symbolprices] = await Promise.all([
      MarketPair.findAll({
        where: {
          status: 'active', type: {
            [Op.in]: ['spot', 'fx']
          }
        },
        include: [
          { model: Asset, as: 'base' },
          { model: Asset, as: 'quote' },
          { model: SymbolStats, as: 'rate' }
        ]
      }),
      SymbolStats.findAll({
        include: [{
          model: Asset,
          as: 'asset',
        }]
      })
    ]);


    const orderBook = (currentAsk, currentBid) => {
      const asks = [];
      const bids = [];
      let askPrice = currentAsk;
      let bidPrice = currentBid;

      for (let i = 0; i < 12; i++) {
        asks.push({
          price: askPrice.toFixed(5),
          volume: (Math.random() * 50 + 10).toFixed(2),
          total: (Math.random() * 500000).toFixed(0),
        });
        bids.push({
          price: bidPrice.toFixed(5),
          volume: (Math.random() * 50 + 10).toFixed(2),
          total: (Math.random() * 500000).toFixed(0),
        });
        askPrice += Math.random() * 0.0003 + 0.0001;
        bidPrice -= Math.random() * 0.0003 + 0.0001;
      }
      return { asks: asks.reverse(), bids };
    };


    const recentTrades = (currentBid) => {
      const trades = [];
      for (let i = 0; i < 15; i++) {
        trades.push({
          price: (currentBid + (Math.random() - 0.5) * 0.001).toFixed(5),
          volume: (Math.random() * 10 + 0.5).toFixed(2),
          time: `${12 + Math.floor(i / 5)}:${(45 - i * 3).toString().padStart(2, '0')}:${Math.floor(Math.random() * 60).toString().padStart(2, '0')}`,
          type: Math.random() > 0.5 ? 'buy' : 'sell',
        });
      }
      return trades;
    };

    const openPositions = [
      { id: 1, pair: 'EUR/USD', type: 'buy', lots: 0.10, entryPrice: 1.0850, currentPrice: 1.0874, pnl: 24.00, pnlPercent: 0.22, swap: -1.25, time: '2024-01-15 12:30' },
      { id: 2, pair: 'GBP/USD', type: 'sell', lots: 0.05, entryPrice: 1.2720, currentPrice: 1.2695, pnl: 12.50, pnlPercent: 0.20, swap: -0.85, time: '2024-01-15 11:20' },
    ];

    return {
      tradingPair: tradingPair.map(t => ({
        symbol: t.symbol,
        name: t.name,
        market: t.market,
        type: t.type,
        baseSymbol: t.base?.symbol ?? '',
        baseName: t.base?.name ?? '',
        baseLogo: getUploadedFile(t.base?.logo ?? null, 'assets'),
        quoteSymbol: t.quote?.symbol ?? '',
        quoteName: t.quote?.name ?? '',
        quoteLogo: getUploadedFile(t.quote?.logo ?? null, 'assets'),
        price: t?.rate?.current_price ?? 0,
        change24h: t?.rate?.change24h ?? 0,
        volume24h: t?.rate?.volume24h ?? 0,
        high24h: t?.rate?.high24h ?? 0,
        low24h: t?.rate?.low24h ?? 0,
        changePercent24h: t?.rate?.change_percent24h ?? 0,
        decimals: t?.quote?.decimals ?? 2,

      })), recentTrades: recentTrades(1.0840), openPositions, orderBook: orderBook(1.0874, 1.2695), prices: symbolprices.map(t => mapPrice(t))
    };
  }

  async getAssetData(userId) {

    const [assets, trades, holdings, balance] = await Promise.all([
      MarketPair.findAll({
        distinct: true,
        where: {

          status: 'active',
          type: { [Op.in]: ['stock', 'commodity'] },
        },
        limit: 5,
        include: [
          { model: Asset, as: 'base', include: [{ model: MarketStats, as: 'stats' }] },
          { model: Asset, as: 'quote' },
          { model: SymbolStats, as: 'rate' }
        ]
      }),
      AssetLot.findAll({
        where: { userId },
        include: [{ model: MarketPair, as: 'pair', include: [{ model: Asset, as: 'base' }] }],
        order: [['createdAt', 'DESC']],
        limit: 5
      }),
      AssetHolding.findAll({
        where: { userId },
        include: [{
          model: MarketPair, as: 'asset', include: [
            { model: Asset, as: 'base' },
            { model: SymbolStats, as: 'rate' }
          ]
        }],
        order: [['createdAt', 'DESC']],
        limit: 5
      }),
      UserBalance.findOne({
        where: { userId },
      })
    ]);



    const mappedAssets = assets.map(a => mapPair(a))
    return {
      balance: balance ? Number(balance.balance) : 0,
      assets: mappedAssets,
      trades,
      assetTrades: trades.map(t => {
        return {
          id: t.id,
          symbol: t.pair?.symbol || "—",
          logo: getUploadedFile(t.pair?.base?.logo, 'assets') || null,
          type: t.side === "buy" ? "Buy" : "Sell",
          orderType: t.type.charAt(0).toUpperCase() + t.type.slice(1),
          price: t.entryPrice || t.buyPrice,
          shares: t.quantity || 0,
          filled: t.status === "closed" ? t.quantity : 0,
          status: t.status.charAt(0).toUpperCase() + t.status.slice(1),
          time: new Date(t.createdAt).toISOString().replace('T', ' ').substring(0, 19)
        }
      }),
      assetHoldings: holdings.map(h => {
        return {
          symbol: h.asset?.symbol || "—",
          shares: h.quantity || 0,
          avgCost: h.avgBuyPrice || 0,
          logo: getUploadedFile(h.asset?.base?.logo, 'assets') || null,
          currentPrice: h.asset?.rate?.current_price || 0,
          price: h.asset?.rate?.current_price,
          high24h: h.asset?.rate?.high24h,
          low24h: h.asset?.rate?.low24h,
          volume24h: h.asset?.rate?.volume24h,
          decimals: h.asset?.decimals,
          precision: h.asset?.pricePrecision,
          value: ((h.quantity || 0) * (h.asset?.currentPrice || 0)).toFixed(2),
          pnl: (((h.asset?.currentPrice || 0) - (h.avgBuyPrice || 0)) * (h.quantity || 0)).toFixed(2),
          pnlPercent: h.avgBuyPrice > 0 ? (((h.asset?.currentPrice || 0) - (h.avgBuyPrice || 0)) / (h.avgBuyPrice || 1) * 100).toFixed(2) : "0"
        }
      })
    }
  }

  async tradeAsset(userId, currency, data) {
    const { asset: symbol, side, price, marketType, limit, quantity } = data;

    const qty = Number(quantity ?? 0);
    const pxr = Number(price ?? 0);

    if (!qty || qty <= 0) throw new ValidationError("Invalid amount");
    if (!pxr || pxr <= 0) throw new ValidationError("Invalid price");






    const asset = await MarketPair.findOne({
      where: { symbol, status: "active" },
    });

    if (!asset) throw new ConflictError(`${symbol} not available for trading`);

    const stPrice = stockData.getTicker(symbol);
    const px = stPrice ? Number(stPrice?.price) : pxr;
    const lotCost = px * qty;

    const t = await sequelize.transaction();
    try {
      const balance = await UserBalance.findOne({
        where: { userId },
        transaction: t,
        lock: t.LOCK.UPDATE,
      });

      if (!balance) throw new ConflictError("User must have a user balance");

      const currentBalance = Number(balance.balance || 0);

      // Lock holdings row too (if exists)
      let holding = await AssetHolding.findOne({
        where: { userId, assetId: asset.id },
        transaction: t,
        lock: t.LOCK.UPDATE,
      });



      // ---- BUY ----
      if (side === "buy") {
        if (currentBalance < lotCost) {
          throw new ValidationError("Insufficient balance for this trade");
        }

        if (holding) {
          const oldQty = Number(holding.quantity || 0);
          const oldAvg = Number(holding.avgBuyPrice || 0);
          const oldCost = Number(holding.totalCost || 0);

          // Weighted average
          const newQty = oldQty + qty;
          const newTotalCost = oldCost + lotCost;
          const newAvg = newTotalCost / newQty;

          await holding.update(
            { quantity: newQty, totalCost: newTotalCost, avgBuyPrice: newAvg },
            { transaction: t }
          );
        } else {
          holding = await AssetHolding.create(
            {
              userId,
              assetId: asset.id,
              quantity: qty,
              avgBuyPrice: px,
              totalCost: lotCost,
            },
            { transaction: t }
          );
        }

        await balance.update(
          { balance: currentBalance - lotCost },
          { transaction: t }
        );
      }

      // ---- SELL ----
      if (side === "sell") {
        if (!holding) throw new ValidationError("You don't own this asset");

        const oldQty = Number(holding.quantity || 0);
        if (oldQty < qty) throw new ValidationError("Insufficient asset quantity");

        // for simple accounting: reduce quantity; keep avgBuyPrice
        const newQty = oldQty - qty;

        // If you track totalCost as cost basis, reduce proportionally by avg
        const avg = Number(holding.avgBuyPrice || 0);
        const newTotalCost = Math.max(0, Number(holding.totalCost || 0) - avg * qty);

        await holding.update(
          { quantity: newQty, totalCost: newTotalCost },
          { transaction: t }
        );

        // credit proceeds (qty * px). If you have fees, subtract fees here.
        await balance.update(
          { balance: currentBalance + lotCost },
          { transaction: t }
        );
      }

      // Create lot record
      const assetLot = await AssetLot.create(
        {
          userId,
          assetId: asset.id,
          quantity: qty,
          buyPrice: px,
          side,
          type: marketType,
          marketLimit: limit ?? null,
          status: "closed",
        },
        { transaction: t }
      );

      // Create transaction record
      const tx = await Transaction.create(
        {
          userId,
          source: "funding",
          toType: "internal",
          kind: side === "buy" ? "buy_asset" : "sell_asset",
          direction: side === "buy" ? "debit" : "credit",
          currency,
          description: `${side === "buy" ? "Bought" : "Sold"} ${symbol}`,
          assetLotId: assetLot.id,
          amount: lotCost,
          netAmount: lotCost,
          status: "completed",
        },
        { transaction: t }
      );

      await t.commit();

      const holdings = await AssetHolding.findAll({
        where: { userId },
        order: [["createdAt", "DESC"]],
        include: [{
          model: MarketPair, as: 'asset', include: [
            { model: Asset, as: 'base' },
            { model: SymbolStats, as: 'rate' }
          ]
        }],
        limit: 5,
      });

      const lots = await AssetLot.findAll({
        where: { userId },
        order: [["createdAt", "DESC"]],
        include: [{ model: MarketPair, as: 'pair', include: [{ model: Asset, as: 'base' }] }],
        limit: 5,
      });

      return {
        transaction: tx,
        assetTrades: lots.map(t => {
          return {
            id: t.id,
            symbol: t.pair?.symbol || "—",
            logo: getUploadedFile(t.pair?.base?.logo, 'assets') || null,
            type: t.side === "buy" ? "Buy" : "Sell",
            orderType: t.type.charAt(0).toUpperCase() + t.type.slice(1),
            price: t.entryPrice || t.buyPrice,
            shares: t.quantity || 0,
            filled: t.status === "closed" ? t.quantity : 0,
            status: t.status.charAt(0).toUpperCase() + t.status.slice(1),
            time: new Date(t.createdAt).toISOString().replace('T', ' ').substring(0, 19)
          }
        }),
        assetHoldings: holdings.map(h => (
          {
            symbol: h.asset?.symbol || "—",
            shares: h.quantity || 0,
            avgCost: h.avgBuyPrice || 0,
            logo: getUploadedFile(h.asset?.base?.logo, 'assets') || null,
            currentPrice: h.asset?.currentPrice || 0,
            value: ((h.quantity || 0) * (h.asset?.currentPrice || 0)).toFixed(2),
            pnl: (((h.asset?.currentPrice || 0) - (h.avgBuyPrice || 0)) * (h.quantity || 0)).toFixed(2),
            pnlPercent: h.avgBuyPrice > 0 ? (((h.asset?.currentPrice || 0) - (h.avgBuyPrice || 0)) / (h.avgBuyPrice || 1) * 100).toFixed(2) : "0"
          }))
      };
    } catch (e) {
      logger.info(e)
      await t.rollback();
      throw e;
    }
  }

  async getSpotData(userId) {
    const [pairs, balance] = await Promise.all([
      MarketPair.findAll({
        where: { type: 'spot', status: 'active' },
        include: [{ model: Asset, as: 'base' }, { model: Asset, as: 'quote' }]
      }),
      UserBalance.findOne({
        where: { userId },
      })
    ]);
    return {
      pairs: pairs.map(p => {
        const baseAsset = p?.base ?? '';
        const quoteAsset = p?.quote ?? '';
        if (!baseAsset && !quoteAsset) return
        return {
          pair: p.name,
          symbol: p.symbol,
          base: baseAsset.symbol,
          quote: quoteAsset.symbol,
          price: p.price,
          change24h: p.change24h,
          volume24h: p.volume24h,
          baseLogo: baseAsset.logo,
          quoteLogo: quoteAsset.logo,
        }
      }).filter(Boolean), balance
    };
  }

  async getFxData(userId) {
    const [pairs, balance] = await Promise.all([
      MarketPair.findAll({
        where: { type: 'fx', status: 'active' },
        include: [{ model: Asset, as: 'base' }, { model: Asset, as: 'quote' }]
      }),
      UserBalance.findOne({
        where: { userId },
      })
    ])
    return {
      pairs: pairs.map(p => {
        const baseAsset = p?.base ?? '';
        const quoteAsset = p?.quote ?? '';
        if (!baseAsset && !quoteAsset) return
        const name = `${baseAsset.symbol}_${quoteAsset.symbol}`;
        const symbol = `${baseAsset.symbol}/${quoteAsset.symbol}`;
        return {
          pair: p.name,
          symbol: p.symbol,
          base: baseAsset.symbol,
          quote: quoteAsset.symbol,
          price: p.price,
          change24h: p.change24h,
          volume24h: p.volume24h,
          baseLogo: baseAsset.logo,
          quoteLogo: quoteAsset.logo,
        }
      }).filter(Boolean),
      balance: balance ? Number(balance.balance) : 0
    };
  };

  findTicker(symbol) {
    const sym = String(symbol ?? "").toUpperCase();
    return (
      forexMarketData.getTicker(sym) ??
      stockData.getTicker(sym) ??
      cryptoMarketData.getTicker(sym) ??
      null
    );
  }

  async tradeTerminal(userId, currency, data) {
    const {
      asset,
      side,
      marketType = "market",
      limit,
      quantity,
      price,
      leverage,
      duration,
      stopLoss,
      takeProfit
    } = data;

    // 1) Validate
    if (!asset) throw new BadRequestError("asset is required");
    if (!["buy", "sell"].includes(side)) throw new BadRequestError("side must be buy or sell");

    const qty = Number(quantity);
    if (!Number.isFinite(qty) || qty <= 0) throw new BadRequestError("quantity must be > 0");


    const pricePayload = Number(price);
    if (!Number.isFinite(pricePayload) || pricePayload <= 0) throw new BadRequestError("price must be > 0");


    const lev = Number(leverage);
    if (!Number.isFinite(lev) || lev <= 0) throw new BadRequestError("leverage must be > 0");

    const dur = Number(duration);
    if (!Number.isFinite(dur) || dur <= 0) throw new BadRequestError("duration must be > 0");

    if (marketType === "limit") {
      const lim = Number(limit);
      if (!Number.isFinite(lim) || lim <= 0) {
        throw new BadRequestError("limit is required for limit orders");
      }
    }

    // 2) Asset exists + tradable
    const assetRow = await MarketPair.findOne({
      where: { symbol: asset, status: 'active' },
    });
    if (!assetRow) throw new NotFoundError("Trading Pair not found or not tradable");



    // 3) Anti-spam duplicate pending
    const existing = await TradeHistory.findOne({
      where: {
        userId,
        pairId: asset,
        side: side,
        status: { [Op.in]: ["pending", "open", "initiated", "processing"] },
        createdAt: { [Op.gte]: new Date(Date.now() - 30 * 1000) }, // last 30s
      },
      order: [["created_at", "DESC"]],
    });
    if (existing) {
      throw new BadRequestError("You already have a pending trade for this asset. Please wait.");
    }

    // 4) Financials (example)
    // Example margin model: cost = (qty * px) / leverage
    //const marginRequired = (qty * px) / lev;
    const latest = this.findTicker(assetRow?.symbol);
    const px = latest?.price ?? pricePayload

    const t = await sequelize.transaction();
    try {
      // Lock user balance row
      const balance = await UserBalance.findOne({
        where: { userId },
        transaction: t,
        lock: t.LOCK.UPDATE,
      });
      if (!balance) throw new ConflictError("User must have a wallet/balance");

      const currentBalance = Number(balance.balance ?? 0);
      if (currentBalance < qty) {
        throw new BadRequestError("Insufficient balance for this trade");
      }

      // Deduct margin (simple example)
      balance.balance = currentBalance - qty;
      await balance.save({ transaction: t });

      // Create transaction record (audit trail)
      const tx = await Transaction.create(
        {
          transactionId: generateTransactionId("OD"),
          userId,
          source: "funding",
          kind: "trade_margin",
          direction: "debit",
          toType: 'internal',
          currency,
          amount: qty,
          status: "completed",
          description: `(Terminal)${side.toUpperCase()} ${qty} ${asset?.replace("_", "/")} @ ${px} (x${lev})`,
          metadata: {
            asset,
            side,
            marketType,
            limit: marketType === "limit" ? Number(limit) : null,
            quantity: qty,
            price: px,
            leverage: lev,
            duration: dur,
          },
        },
        { transaction: t }
      );

      // Create trade history (your table)
      const trade = await TradeHistory.create(
        {
          orderId: tx.transactionId,
          userId,
          pairId: assetRow.id,
          side,
          orderType: marketType,
          volume: qty,
          entryPrice: px,
          requestedPrice: limit,
          leverage: lev,
          duration: dur,
          takeProfit: takeProfit ?? null,
          stopLoss: stopLoss ?? null,
          status: "open",
        },
        { transaction: t }
      );

      await t.commit();
      return { tx, trade };
    } catch (e) {
      await t.rollback();
      throw e;
    }
  }


  async closeTerminalTrade(userId, tradeId) {
    if (!tradeId) throw new ValidationError('TradeId can not be empty.');
    const t = await sequelize.transaction();
    try {
      // 1. Fetch the open trade with lock
      const trade = await TradeHistory.findOne({
        where: { id: tradeId, userId, status: 'open' },
        lock: t.LOCK.UPDATE,
        transaction: t,
      });
      if (!trade) throw new NotFoundError('Open trade not found.');

      // 2. Get current market price
      const market = await MarketPair.findByPk(trade.pairId, {
        include: [
          {
            model: Asset,
            as: "base",
            require: true
          },
          {
            model: Asset,
            as: "quote",
            require: true
          },
        ], transaction: t
      });
      if (!market) throw new NotFoundError('Market pair not found.');
      const marketSymbol = market?.type === 'spot' ? `${market?.base?.symbol}${market?.quote?.symbol}` : `${market?.base?.symbol}_${market?.quote?.symbol}`;
      const ticker = this.findTicker(marketSymbol);
      if (!ticker) throw new Error(`No live price for ${market.symbol?.replace("_", "/")} - ${marketSymbol}`);

      // Buy closes at BID, Sell closes at ASK
      const exitPrice = trade.side === 'buy'
        ? (ticker.bid ?? 0)
        : (ticker.ask ?? 0);

      if (!exitPrice || exitPrice <= 0) throw new Error('Invalid exit price.');

      // 3. Calculate PnL
      //    Buy:  (exitPrice - entryPrice) * volume
      //    Sell: (entryPrice - exitPrice) * volume
      const entryPrice = Number(trade.entryPrice);
      const volume = Number(trade.volume);
      const leverage = Number(trade.leverage) || 1;

      const rawPnl = trade.side === 'buy'
        ? (exitPrice - entryPrice) * volume
        : (entryPrice - exitPrice) * volume;

      const profitAmount = parseFloat(rawPnl.toFixed(10));
      const result = profitAmount > 0 ? 'won' : profitAmount < 0 ? 'loss' : 'none';

      // 4. Lock and update user balance
      const balance = await UserBalance.findOne({
        where: { userId },
        lock: t.LOCK.UPDATE,
        transaction: t,
      });
      if (!balance) throw new ConflictError('User wallet not found.');

      const currentBalance = Number(balance.balance ?? 0);

      // Return the original margin (volume) + any profit (can be negative for a loss)
      const payout = volume + profitAmount;
      balance.balance = parseFloat((currentBalance + Math.max(0, payout)).toFixed(10));
      await balance.save({ transaction: t });

      // 5. Close the trade record
      await trade.update(
        {
          status: 'closed',
          result,
          profitAmount: Math.abs(profitAmount),
          closedAt: new Date(),
        },
        { transaction: t }
      );

      // 6. Audit transaction
      await Transaction.create(
        {
          transactionId: generateTransactionId('CL'),
          userId,
          source: 'trading',
          kind: 'trade_close',
          direction: profitAmount >= 0 ? 'credit' : 'debit',
          toType: 'internal',
          currency: 'USD',
          amount: Math.abs(profitAmount),
          status: 'completed',
          description: `Closed ${trade.side.toUpperCase()} ${volume} ${market.symbol?.replace("_", "/")} @ ${exitPrice}`,
          metadata: {
            tradeId: trade.id,
            orderId: trade.orderId,
            symbol: market.symbol,
            side: trade.side,
            entryPrice,
            exitPrice,
            volume,
            leverage,
            profitAmount,
            result,
          },
        },
        { transaction: t }
      );

      await t.commit();

      return {
        message: `${market?.base?.symbol}/${market?.quote?.symbol} ${trade.side} trade closed`,
        data: {
          trade: {
            id: trade.id,
            orderId: trade.orderId,
            symbol: market.symbol,
            side: trade.side,
            volume,
            entryPrice,
            exitPrice,
            profitAmount,
            result,
            status: 'closed',
            closedAt: new Date().toISOString(),
          },
          account: {
            balance: balance.balance,
          },
        },
      };
    } catch (e) {
      await t.rollback();
      throw e;
    }
  }


  async getMarketsByType(type) {
    const validTypes = ['spot', 'futures', 'margin', 'forex', 'stocks'];
    if (!validTypes.includes(type)) {
      throw new BadRequestError('Invalid market type');
    }


    const recentTrades = (currentPrice) => {
      const trades = [];
      for (let i = 0; i < 15; i++) {
        trades.push({
          symbol: ['BTC/USDT', 'ETH/USDT', 'BNB/USDT', 'SOL/USDT', 'XRP/USDT'][i % 5],
          price: (currentPrice + (Math.random() - 0.5) * 50).toFixed(2),
          amount: (Math.random() * 0.5).toFixed(4),
          time: `${12 + Math.floor(i / 5)}:${(45 - i * 3).toString().padStart(2, '0')}:${Math.floor(Math.random() * 60).toString().padStart(2, '0')}`,
          type: Math.random() > 0.5 ? 'buy' : 'sell',
        });
      }
      return trades;
    };

    const orderBook = (currentPrice) => {
      const asks = [];
      const bids = [];
      let askPrice = currentPrice + 5;
      let bidPrice = currentPrice - 5;

      for (let i = 0; i < 12; i++) {
        asks.push({
          price: askPrice.toFixed(2),
          amount: (Math.random() * 2).toFixed(4),
          total: (Math.random() * 50000).toFixed(2),
        });
        bids.push({
          price: bidPrice.toFixed(2),
          amount: (Math.random() * 2).toFixed(4),
          total: (Math.random() * 50000).toFixed(2),
        });
        askPrice += Math.random() * 10 + 2;
        bidPrice -= Math.random() * 10 + 2;
      }
      return { asks: asks.reverse(), bids };
    };
    // Open orders
    const openOrders = [
      { id: 1, pair: 'BTC/USDT', type: 'buy', orderType: 'Limit', price: 42500, amount: 0.05, filled: 0, total: 2125, time: '2024-01-15 12:30:45' },
      { id: 2, pair: 'BTC/USDT', type: 'sell', orderType: 'Limit', price: 45000, amount: 0.1, filled: 0.02, total: 4500, time: '2024-01-15 11:20:15' },
    ];

    // Trading pairs
    const tradingPairs = [
      {
        pair: 'BTC_USDT', price: 43567.82, stats: {
          price: 43567.82,
          change: 2.45,
          high24h: 44123.45,
          low24h: 42890.12,
          volume24h: '28.5B',
        }, change: 2.45
      },
      { pair: 'ETH_USDT', price: 2284.56, change: 4.32 },
      { pair: 'BNB_USDT', price: 312.45, change: -1.23 },
      { pair: 'SOL_USDT', price: 98.76, change: 5.67 },
      { pair: 'XRP_USDT', price: 0.5234, change: 1.89 },
      { pair: 'ADA_USDT', price: 0.4567, change: -2.11 },
    ];
    return { recentTrades: recentTrades(43567.82), orderBook: orderBook(43567.82), openOrders, tradingPairs };

    const markets = await TradingPair.findAll({
      where: { market: type, status: 'active' },
      attributes: ['symbol', 'baseAsset', 'quoteAsset', 'price', 'change24h', 'volume24h'],
    });

    return markets;
  }

  /**
   * Create a new order
   */
  async createOrder(userId, orderData) {
    const {
      market, symbol, side, type, quantity, price,
      stopPrice, leverage = 1, takeProfit, stopLoss,
      timeInForce = 'GTC', reduceOnly = false, postOnly = false,
      clientOrderId,
    } = orderData;

    // Get trading pair
    const tradingPair = await TradingPair.findOne({
      where: { symbol, market, status: 'active' },
    });

    if (!tradingPair) {
      throw new BadRequestError('Trading pair not found or inactive');
    }

    if (!tradingPair.tradingEnabled) {
      throw new BadRequestError('Trading is disabled for this pair');
    }

    // Validate quantity
    if (quantity < parseFloat(tradingPair.minQuantity)) {
      throw new BadRequestError(`Minimum quantity is ${tradingPair.minQuantity}`);
    }

    if (tradingPair.maxQuantity && quantity > parseFloat(tradingPair.maxQuantity)) {
      throw new BadRequestError(`Maximum quantity is ${tradingPair.maxQuantity}`);
    }

    // Validate leverage for futures
    if (market === 'futures' && leverage > tradingPair.maxLeverage) {
      throw new BadRequestError(`Maximum leverage is ${tradingPair.maxLeverage}x`);
    }

    // Calculate order value
    const orderPrice = type === 'market' ? parseFloat(tradingPair.price) : price;
    const orderValue = quantity * orderPrice;

    // Check minimum notional
    if (orderValue < parseFloat(tradingPair.minNotional)) {
      throw new BadRequestError(`Minimum order value is ${tradingPair.minNotional} ${tradingPair.quoteAsset}`);
    }

    // Get user's wallet
    const walletType = market === 'futures' ? 'futures' : 'spot';
    const walletCurrency = side === 'buy' ? tradingPair.quoteAsset : tradingPair.baseAsset;

    const wallet = await Wallet.findOne({
      where: { userId, type: walletType, currency: walletCurrency },
    });

    if (!wallet) {
      throw new BadRequestError(`No ${walletCurrency} wallet found`);
    }

    // Calculate required balance
    const requiredBalance = side === 'buy'
      ? orderValue / leverage
      : quantity;

    // Check balance
    if (parseFloat(wallet.available) < requiredBalance) {
      throw new BadRequestError('Insufficient balance');
    }

    // Start transaction
    const t = await sequelize.transaction();

    try {
      // Lock balance
      await wallet.lockBalance(requiredBalance);

      // Calculate fee
      const feeRate = postOnly ? parseFloat(tradingPair.makerFee) : parseFloat(tradingPair.takerFee);
      const fee = orderValue * feeRate;

      // Create order
      const order = await Order.create({
        orderId: generateOrderId(market.toUpperCase().slice(0, 3)),
        userId,
        market,
        symbol,
        baseCurrency: tradingPair.baseAsset,
        quoteCurrency: tradingPair.quoteAsset,
        side,
        type,
        price: type === 'market' ? null : price,
        stopPrice,
        quantity,
        filledQuantity: 0,
        leverage,
        takeProfit,
        stopLoss,
        timeInForce,
        reduceOnly,
        postOnly,
        clientOrderId,
        feeRate,
        status: type === 'market' ? 'pending' : 'open',
        openedAt: new Date(),
      }, { transaction: t });

      // For market orders, execute immediately (simplified)
      if (type === 'market') {
        await this.executeMarketOrder(order, tradingPair, wallet, t);
      }

      await t.commit();

      logger.info(`Order created: ${order.orderId} by user ${userId}`);

      return order;
    } catch (error) {
      await t.rollback();
      // Unlock balance on failure
      await wallet.unlockBalance(requiredBalance);
      throw error;
    }
  }

  /**
   * Execute market order (simplified simulation)
   */
  async executeMarketOrder(order, tradingPair, wallet, transaction) {
    const executionPrice = parseFloat(tradingPair.price);
    const quantity = parseFloat(order.quantity);
    const fee = quantity * executionPrice * parseFloat(order.feeRate);

    // Create trade record
    const trade = await Trade.create({
      tradeId: `T${Date.now()}${Math.random().toString(36).substr(2, 6).toUpperCase()}`,
      orderId: order.id,
      userId: order.userId,
      market: order.market,
      symbol: order.symbol,
      side: order.side,
      price: executionPrice,
      quantity,
      fee,
      feeCurrency: order.quoteCurrency,
      feeRate: order.feeRate,
      isMaker: false,
      executedAt: new Date(),
    }, { transaction });

    // Update order
    await order.update({
      filledQuantity: quantity,
      avgPrice: executionPrice,
      totalValue: quantity * executionPrice,
      fee,
      feeCurrency: order.quoteCurrency,
      status: 'filled',
      filledAt: new Date(),
    }, { transaction });

    // Update wallet balances
    const walletType = order.market === 'futures' ? 'futures' : 'spot';

    if (order.side === 'buy') {
      // Deduct quote currency, add base currency
      const quoteWallet = await Wallet.findOne({
        where: { userId: order.userId, type: walletType, currency: order.quoteCurrency },
      });

      let baseWallet = await Wallet.findOne({
        where: { userId: order.userId, type: walletType, currency: order.baseCurrency },
      });

      if (!baseWallet) {
        baseWallet = await Wallet.create({
          userId: order.userId,
          type: walletType,
          currency: order.baseCurrency,
        }, { transaction });
      }

      const totalCost = (quantity * executionPrice) + fee;
      await quoteWallet.update({
        locked: parseFloat(quoteWallet.locked) - totalCost,
      }, { transaction });

      await baseWallet.update({
        available: parseFloat(baseWallet.available) + quantity,
      }, { transaction });

    } else {
      // Deduct base currency, add quote currency
      const baseWallet = await Wallet.findOne({
        where: { userId: order.userId, type: walletType, currency: order.baseCurrency },
      });

      let quoteWallet = await Wallet.findOne({
        where: { userId: order.userId, type: walletType, currency: order.quoteCurrency },
      });

      if (!quoteWallet) {
        quoteWallet = await Wallet.create({
          userId: order.userId,
          type: walletType,
          currency: order.quoteCurrency,
        }, { transaction });
      }

      await baseWallet.update({
        locked: parseFloat(baseWallet.locked) - quantity,
      }, { transaction });

      const proceeds = (quantity * executionPrice) - fee;
      await quoteWallet.update({
        available: parseFloat(quoteWallet.available) + proceeds,
      }, { transaction });
    }

    // Create transaction records
    await Transaction.create({
      transactionId: generateTransactionId('TRD'),
      userId: order.userId,
      walletId: wallet.id,
      type: order.side === 'buy' ? 'trade_buy' : 'trade_sell',
      currency: order.side === 'buy' ? order.baseCurrency : order.quoteCurrency,
      amount: order.side === 'buy' ? quantity : quantity * executionPrice,
      fee,
      feeCurrency: order.quoteCurrency,
      status: 'completed',
      orderId: order.id,
      tradePair: order.symbol,
      price: executionPrice,
      quantity,
      completedAt: new Date(),
    }, { transaction });

    return trade;
  }

  /**
   * Get orders for a user
   */
  /* async getOrders(userId, filters = {}) {
    const { market, symbol, side, status, startDate, endDate, page = 1, limit = 20 } = filters;

    const where = { userId };

    if (market) where.market = market;
    if (symbol) where.symbol = symbol;
    if (side) where.side = side;
    if (status) where.status = status;

    if (startDate || endDate) {
      where.createdAt = {};
      if (startDate) where.createdAt[Op.gte] = new Date(startDate);
      if (endDate) where.createdAt[Op.lte] = new Date(endDate);
    }

    const { count, rows } = await Order.findAndCountAll({
      where,
      order: [['createdAt', 'DESC']],
      limit,
      offset: (page - 1) * limit,
    });

    return {
      orders: rows,
      pagination: {
        total: count,
        page,
        limit,
        totalPages: Math.ceil(count / limit),
      },
    };
  } */

  /* async getOrders(userId, filters = {}) {
    const { market, symbol, side, startDate, endDate, page = 1, limit = 20 } = filters;

    const where = { user_id: userId };

    if (market) where.market = market;
    if (symbol) where.symbol = symbol;
    if (side) where.side = side;

    if (startDate || endDate) {
      where.createdAt = {};
      if (startDate) where.createdAt[Op.gte] = new Date(startDate);
      if (endDate) where.createdAt[Op.lte] = new Date(endDate);
    }
    const [
      spots,
      //forex
    ] = await Promise.all([
      SpotOrder.findAndCountAll({
        where,
        order: [['createdAt', 'DESC']],
        limit,
        offset: (page - 1) * limit,
        include: [{ model: MarketPair, as: 'pair' }],
      }),
      DerivativeOrderOrder.findAndCountAll({
        where,
        order: [['executedAt', 'DESC']],
        limit,
        offset: (page - 1) * limit,
        include: [{ model: Order, as: 'order', attributes: ['orderId', 'type', 'timeInForce'] }],
      }),

    ])

    const spotOrders = spots.rows.map(t => {
      const qty = Number(t.qty ?? 0)
      const price = Number(t.price ?? 0)
      return {
        market: "spot",
        pair: t.market?.symbol,
        amount: qty,
        type: t.type,
        side: t.side,
        filled: t.qty_filled,
        price: t.price,
        limitPrice: t.stop_price,
        total: qty * price,
        status: t.status,
        createdAt: t.createdAt
      }
    })

    const positions = positions.map(t => {
      const baseAsset = t.pair?.base ?? '';
      const quoteAsset = t.pair?.quote ?? '';
      const price = t.pair?.rate ?? {};
      if (!baseAsset && !quoteAsset) return
      return {
        id: t.id,
        symbol: t.pair?.symbol,
        baseSymbol: baseAsset?.symbol || "—",
        quoteSymbol: quoteAsset?.symbol || "—",
        baselogo: getUploadedFile(baseAsset?.logo, 'assets') || null,
        quotelogo: getUploadedFile(quoteAsset?.logo, 'assets') || null,
        type: t.side,
        entryPrice: t.entry_price,
        price: price?.price,
        low24h: price?.low24h,
        high24: price?.high24h,
        leverage: t.leverage,
        lots: t.qty || 0,
        sl: t.stop_loss ?? '-',
        tp: t.take_profit ?? '-',
        status: t.status.charAt(0).toUpperCase() + t.status.slice(1),
        time: new Date(t.opened_at).toISOString().replace('T', ' ').substring(0, 19)
      }
    }).filter(Boolean)

    return {
      //trades: rows,
      orders: spotOrders,
      pagination: {
        total: spots.count,
        page,
        limit,
        totalPages: Math.ceil(spots.count / limit),
      },
    };
  } */

  async getOrders(userId, filters = {}) {
    // Fetch all counts in parallel
    const { market, symbol, side, startDate, endDate, page = 1, limit = 20 } = filters;

    const where = { user_id: userId };


    // Fetch recent records from all models
    const [
      trades,
      derivativeOrders
    ] = await Promise.all([
      TradeHistory.findAll({
        where: { userId },
        include: [{
          model: MarketPair,
          as: 'pair'
        }],
        order: [['createdAt', 'DESC']],
        limit: 10
      }),

      DerivativeOrder.findAll({
        where: { userId },
        include: [{
          model: MarketPair,
          as: 'pair'
        }],
        order: [['createdAt', 'DESC']],
        limit: 10
      })
    ]);

    // Merge and normalize all activities
    const recentActivities = [
      ...trades.map(item => ({
        id: item.id,
        model: 'Terminal',
        pair: item.pair?.symbol || '—',
        type: item.side,
        price: item.entryPrice,
        amount: item.volume,
        status: item.status,
        createdAt: item.createdAt
      })),

      ...derivativeOrders.map(item => ({
        id: item.id,
        model: 'Forex',
        pair: item.pair?.symbol || '—',
        type: item.side,
        price: item.entryPrice,
        amount: item.quantity,
        status: item.status,
        createdAt: item.createdAt
      }))
    ]
      .sort((a, b) => new Date(b.createdAt) - new Date(a.createdAt))
      .slice(0, 10);

    const totalOrders =
      totalTrades +
      totalAssetLots +
      totalSpot +
      totalDerivatives;

    const totalProfit = Number(profitResult?.profitAmount || 0);

    return {
      recentTrades: recentActivities,
    };
  }


  // ─────────────────────────────────────────────────────────────────────────────
  // openOrders.service.js  –  getOpenOrders with proper DB-level pagination
  // ─────────────────────────────────────────────────────────────────────────────

  // ─────────────────────────────────────────────────────────────────────────────
  // tradeHistory.service.js  –  getAllOrders with pagination + filters
  // Same architecture as openOrders.service.js but:
  //   • No hardcoded status:'open' – status is an optional filter param
  //   • Adds realizedPnl / fee / closedAt fields from the normalized rows
  //   • Adds a `role` field (maker/taker) where available
  // ─────────────────────────────────────────────────────────────────────────────

  async getAllTerminalOrders(userId, filters = {}) {
    const {
      market,    // 'spot' | 'forex' | undefined  (maps to Terminal vs Forex model)
      symbol,    // 'BTC/USDT' | undefined
      side,      // 'buy' | 'sell' | undefined
      status,    // 'open' | 'closed' | 'cancelled' | undefined  (all statuses if omitted)
      startDate,
      endDate,
      page = 1,
      limit = 20,
    } = filters;

    const offset = (Number(page) - 1) * Number(limit);

    // ── shared where clause ───────────────────────────────────────────────────
    const baseWhere = { userId };

    if (side) baseWhere.side = side;
    if (status) baseWhere.status = status;   // ← optional; omit to get ALL statuses

    if (startDate || endDate) {
      baseWhere.createdAt = {};
      if (startDate) baseWhere.createdAt[Op.gte] = new Date(startDate);
      if (endDate) baseWhere.createdAt[Op.lte] = new Date(endDate);
    }

    // ── pair include ──────────────────────────────────────────────────────────
    const pairInclude = {
      model: MarketPair,
      as: 'pair',
      required: !!symbol,
      where: symbol ? { symbol } : undefined,
      include: [
        { model: Asset, as: 'base' },
        { model: Asset, as: 'quote' },
        { model: SymbolStats, as: 'rate' },
      ],
    };

    // ── decide which sources to query ─────────────────────────────────────────
    const fetchTerminal = !market || market === 'spot';
    const fetchForex = !market || market === 'forex';

    // ── parallel queries ──────────────────────────────────────────────────────
    const [
      { count: terminalCount, rows: terminalRows },
      { count: forexCount, rows: forexRows },
    ] = await Promise.all([
      fetchTerminal
        ? TradeHistory.findAndCountAll({
          where: baseWhere,
          include: [pairInclude],
          order: [['createdAt', 'DESC']],
          limit: fetchForex ? Number(limit) : Number(limit),
          offset: fetchForex ? 0 : offset,
          distinct: true,
        })
        : Promise.resolve({ count: 0, rows: [] }),

      fetchForex
        ? DerivativePosition.findAndCountAll({
          where: {
            ...baseWhere,
            // DerivativePosition uses opened_at not createdAt
            ...(startDate || endDate
              ? {
                opened_at: {
                  ...(startDate ? { [Op.gte]: new Date(startDate) } : {}),
                  ...(endDate ? { [Op.lte]: new Date(endDate) } : {}),
                },
                createdAt: undefined,
              }
              : {}),
          },
          include: [
            pairInclude,
            { model: DerivativeOrder, as: 'order' },
          ],
          order: [['opened_at', 'DESC']],
          limit: fetchTerminal ? Number(limit) : Number(limit),
          offset: fetchTerminal ? 0 : offset,
          distinct: true,
        })
        : Promise.resolve({ count: 0, rows: [] }),
    ]);

    // ── normalize ─────────────────────────────────────────────────────────────
    const normalize = (item, source) => {
      const baseAsset = item.pair?.base ?? null;
      const quoteAsset = item.pair?.quote ?? null;
      const rate = item.pair?.rate ?? {};

      if (!baseAsset || !quoteAsset) return null;

      const isForex = source === 'Forex';

      return {
        id: item.id,
        model: source,                                   // 'Terminal' | 'Forex'
        symbol: item.pair?.symbol ?? '—',
        baseSymbol: baseAsset.symbol ?? '—',
        quoteSymbol: quoteAsset.symbol ?? '—',
        baselogo: getUploadedFile(baseAsset.logo, 'assets') || null,
        quotelogo: getUploadedFile(quoteAsset.logo, 'assets') || null,
        side: item.side,
        entryPrice: isForex ? item.entry_price : item.entryPrice,
        closePrice: isForex ? (item.close_price ?? null) : (item.closePrice ?? null),
        price: rate.price ?? null,                      // live market price
        low24h: rate.low24h ?? null,
        high24h: rate.high24h ?? null,
        leverage: item.leverage ?? null,
        amount: isForex ? (item.qty ?? 0) : (item.volume ?? 0),
        total: isForex
          ? (item.qty ?? 0) * (item.entry_price ?? 0)
          : (item.total ?? (item.volume ?? 0) * (item.entryPrice ?? 0)),
        fee: isForex ? (item.commission ?? null) : (item.fee ?? null),
        feeCurrency: isForex ? (item.order?.feeCurrency ?? quoteAsset.symbol) : (item.feeCurrency ?? quoteAsset.symbol),
        role: isForex ? null : (item.role ?? null),     // maker | taker | null
        realizedPnl: isForex ? (item.profit ?? null) : (item.realizedPnl ?? null),
        sl: isForex ? (item.stop_loss ?? null) : (item.stopLoss ?? null),
        tp: isForex ? (item.take_profit ?? null) : (item.takeProfit ?? null),
        status: item.status ?? null,
        time: isForex ? item.opened_at : item.createdAt,
        closedAt: isForex ? (item.closed_at ?? null) : (item.closedAt ?? null),
      };
    };

    const normalizedTerminal = terminalRows.map(i => normalize(i, 'Terminal')).filter(Boolean);
    const normalizedForex = forexRows.map(i => normalize(i, 'Forex')).filter(Boolean);

    // ── merge + paginate ──────────────────────────────────────────────────────
    let orders;
    let totalCount;

    if (fetchTerminal && fetchForex) {
      const all = [...normalizedTerminal, ...normalizedForex]
        .sort((a, b) => new Date(b.time) - new Date(a.time));
      totalCount = terminalCount + forexCount;
      orders = all.slice(offset, offset + Number(limit));
    } else {
      totalCount = fetchTerminal ? terminalCount : forexCount;
      orders = fetchTerminal ? normalizedTerminal : normalizedForex;
    }

    const totalPages = Math.ceil(totalCount / Number(limit));

    return {
      orders,
      pagination: {
        page: Number(page),
        limit: Number(limit),
        totalCount,
        totalPages,
        hasNext: Number(page) < totalPages,
        hasPrev: Number(page) > 1,
      },
    };
  }


  async getOpenOrders(userId, filters = {}) {
    const {
      market,   // 'spot' | 'forex' | undefined
      symbol,   // e.g. 'BTC/USDT'
      side,     // 'buy' | 'sell' | undefined
      startDate,
      endDate,
      page = 1,
      limit = 20,
    } = filters;

    const offset = (Number(page) - 1) * Number(limit);

    // ── shared where clauses ──────────────────────────────────────────────────
    const baseWhere = { userId, status: 'open' };

    if (side) baseWhere.side = side;

    if (startDate || endDate) {
      baseWhere.createdAt = {};
      if (startDate) baseWhere.createdAt[Op.gte] = new Date(startDate);
      if (endDate) baseWhere.createdAt[Op.lte] = new Date(endDate);
    }

    // ── pair include (reusable) ───────────────────────────────────────────────
    const pairInclude = {
      model: MarketPair,
      as: 'pair',
      required: !!symbol,           // INNER JOIN only when filtering by symbol
      where: symbol ? { symbol } : undefined,
      include: [
        { model: Asset, as: 'base' },
        { model: Asset, as: 'quote' },
        { model: SymbolStats, as: 'rate' },
      ],
    };

    // ── decide which models to query ─────────────────────────────────────────
    const fetchTrades = !market || market === 'spot';
    const fetchDerivatives = !market || market === 'forex';

    // ── parallel count + data queries ────────────────────────────────────────
    const [
      { count: tradeCount, rows: trades },
      { count: derivativeCount, rows: derivatives },
    ] = await Promise.all([
      fetchTrades
        ? TradeHistory.findAndCountAll({
          where: baseWhere,
          include: [pairInclude],
          order: [['createdAt', 'DESC']],
          // pagination applied per-source then merged; see note below
          limit: fetchDerivatives ? Number(limit) : Number(limit),
          offset: fetchDerivatives ? 0 : offset,
          distinct: true,
        })
        : Promise.resolve({ count: 0, rows: [] }),

      fetchDerivatives
        ? DerivativePosition.findAndCountAll({
          where: {
            ...baseWhere,
            // DerivativePosition uses opened_at instead of createdAt for date range
            ...(startDate || endDate
              ? {
                opened_at: {
                  ...(startDate ? { [Op.gte]: new Date(startDate) } : {}),
                  ...(endDate ? { [Op.lte]: new Date(endDate) } : {}),
                },
                createdAt: undefined,   // remove the base date filter
              }
              : {}),
          },
          include: [
            pairInclude,
            { model: DerivativeOrder, as: 'order' },
          ],
          order: [['opened_at', 'DESC']],
          limit: fetchTrades ? Number(limit) : Number(limit),
          offset: fetchTrades ? 0 : offset,
          distinct: true,
        })
        : Promise.resolve({ count: 0, rows: [] }),
    ]);

    // ── normalize ─────────────────────────────────────────────────────────────
    const normalize = (item, source) => {
      const baseAsset = item.pair?.base ?? null;
      const quoteAsset = item.pair?.quote ?? null;
      const rate = item.pair?.rate ?? {};

      if (!baseAsset || !quoteAsset) return null;

      const isForex = source === 'Forex';

      return {
        id: item.id,
        model: source,
        symbol: item.pair?.symbol ?? '—',
        baseSymbol: baseAsset.symbol ?? '—',
        quoteSymbol: quoteAsset.symbol ?? '—',
        baselogo: getUploadedFile(baseAsset.logo, 'assets') || null,
        quotelogo: getUploadedFile(quoteAsset.logo, 'assets') || null,
        side: item.side,
        entryPrice: isForex ? item.entry_price : item.entryPrice,
        price: rate.price ?? null,
        low24h: rate.low24h ?? null,
        high24h: rate.high24h ?? null,
        leverage: item.leverage ?? null,
        amount: isForex ? (item.qty ?? 0) : (item.volume ?? 0),
        sl: isForex ? (item.stop_loss ?? null) : (item.stopLoss ?? null),
        tp: isForex ? (item.take_profit ?? null) : (item.takeProfit ?? null),
        status: item.status ?? 'open',
        time: isForex ? item.opened_at : item.createdAt,
      };
    };

    const normalizedTrades = trades.map(i => normalize(i, 'Terminal')).filter(Boolean);
    const normalizedDerivatives = derivatives.map(i => normalize(i, 'Forex')).filter(Boolean);

    // ── merge, sort, paginate the combined list ───────────────────────────────
    // When querying both sources we fetched extra rows then slice after merge.
    // When querying a single source the DB-level offset/limit already applies.
    let merged;
    let totalCount;

    if (fetchTrades && fetchDerivatives) {
      // Merge both, sort descending, then apply page/limit
      const all = [...normalizedTrades, ...normalizedDerivatives]
        .sort((a, b) => new Date(b.time) - new Date(a.time));

      totalCount = tradeCount + derivativeCount; // approximate (nulls filtered out)
      merged = all.slice(offset, offset + Number(limit));
    } else {
      // Single source — DB already handled offset/limit
      totalCount = fetchTrades ? tradeCount : derivativeCount;
      merged = fetchTrades ? normalizedTrades : normalizedDerivatives;
    }

    const totalPages = Math.ceil(totalCount / Number(limit));

    return {
      openOrders: merged,
      pagination: {
        page: Number(page),
        limit: Number(limit),
        totalCount,
        totalPages,
        hasNext: Number(page) < totalPages,
        hasPrev: Number(page) > 1,
      },
    };
  }


  /**
   * Get open orders
   */
  /* async getOpenOrders(userId, market = null, symbol = null) {
    const where = {
      userId,
      status: { [Op.in]: ['open', 'partially_filled'] },
    };

    if (market) where.market = market;
    if (symbol) where.symbol = symbol;

    const [
      spots,
      //forex
    ] = await Promise.all([
      SpotOrder.findAndCountAll({
        where,
        order: [['createdAt', 'DESC']],
        limit,
        offset: (page - 1) * limit,
        include: [{ model: MarketPair, as: 'market' }],
      }),
       DerivativeOrderOrder.findAndCountAll({
         where,
         order: [['executedAt', 'DESC']],
         limit,
         offset: (page - 1) * limit,
         include: [{ model: Order, as: 'order', attributes: ['orderId', 'type', 'timeInForce'] }],
       }),

    ])

    const spotOrders = spots.rows.map(t => {
      const qty = Number(t.qty ?? 0)
      const price = Number(t.price ?? 0)
      return {
        market: "spot",
        pair: t.market?.symbol,
        amount: qty,
        type: t.type,
        side: t.side,
        filled: t.qty_filled,
        price: t.price,
        limitPrice: t.stop_price,
        total: qty * price,
        status: t.status,
        createdAt: t.createdAt
      }
    })

    return { orders: spotOrders };
  } */

  // ─────────────────────────────────────────────────────────────────────────────
  // spotOrders.service.js  –  getSpotOrders with pagination + filters
  //
  // Model: SpotOrder  (spot_orders table, underscored: true)
  //   Columns: id, user_id, market_id, side, type, price, stop_price,
  //            qty, qty_filled, status, client_order_id, created_at, updated_at
  //
  // Statuses: 'new' | 'partially_filled' | 'filled' | 'canceled' | 'rejected'
  // MarketPair association via market_id  (as: 'market')
  // ─────────────────────────────────────────────────────────────────────────────


  async getSpotOrders(userId, filters = {}) {
    const {
      symbol,    // e.g. 'BTC/USDT'  — filters on MarketPair.symbol
      side,      // 'buy' | 'sell'
      type,      // 'limit' | 'market' | 'stop_limit'
      status,    // 'new' | 'partially_filled' | 'filled' | 'canceled' | 'rejected'
      //  omit to return ALL statuses
      startDate,
      endDate,
      page = 1,
      limit = 20,
    } = filters;

    const offset = (Number(page) - 1) * Number(limit);

    // ── where clause ──────────────────────────────────────────────────────────
    const where = { user_id: userId };

    if (side) where.side = side;
    if (type) where.type = type;
    if (status) where.status = status;

    if (startDate || endDate) {
      where.created_at = {};
      if (startDate) where.created_at[Op.gte] = new Date(startDate);
      if (endDate) where.created_at[Op.lte] = new Date(endDate);
    }

    // ── MarketPair include ─────────────────────────────────────────────────────
    // SpotOrder belongs to MarketPair via market_id; association alias: 'market'
    const marketInclude = {
      model: MarketPair,
      as: 'pair',
      required: !!symbol,
      where: symbol ? { symbol } : undefined,
      include: [
        { model: Asset, as: 'base' },
        { model: Asset, as: 'quote' },
        { model: SymbolStats, as: 'rate' },
      ],
    };

    // ── query ──────────────────────────────────────────────────────────────────
    const { count, rows } = await SpotOrder.findAndCountAll({
      where,
      include: [marketInclude],
      order: [['created_at', 'DESC']],
      limit: Number(limit),
      offset,
      distinct: true,   // required when include has hasMany; keeps count accurate
    });

    // ── normalize ──────────────────────────────────────────────────────────────
    const normalize = (item) => {
      const baseAsset = item.market?.base ?? null;
      const quoteAsset = item.market?.quote ?? null;
      const rate = item.market?.rate ?? {};

      // skip rows where the pair relation is broken
      if (!baseAsset || !quoteAsset) return null;

      const qty = Number(item.qty ?? 0);
      const qtyFilled = Number(item.qty_filled ?? 0);
      const price = Number(item.price ?? 0);

      return {
        id: item.id,
        clientOrderId: item.client_order_id ?? null,

        // pair info
        symbol: item.market?.symbol ?? '—',
        baseSymbol: baseAsset.symbol ?? '—',
        quoteSymbol: quoteAsset.symbol ?? '—',
        baselogo: getUploadedFile(baseAsset.logo, 'assets') || null,
        quotelogo: getUploadedFile(quoteAsset.logo, 'assets') || null,

        // order core
        side: item.side,
        type: item.type,                       // limit | market | stop_limit
        price: price || null,                  // null for market orders
        stopPrice: Number(item.stop_price ?? 0) || null,
        marketPrice: rate.price ?? null,             // live price from SymbolStats
        low24h: rate.low24h ?? null,
        high24h: rate.high24h ?? null,

        // quantities
        qty,
        qtyFilled,
        qtyRemaining: Math.max(0, qty - qtyFilled),
        filledPct: qty > 0 ? Math.min(100, (qtyFilled / qty) * 100) : 0,

        // totals
        total: price * qty,                     // order value at limit price
        filledTotal: price * qtyFilled,               // value of filled portion

        // status
        status: item.status,                     // new | partially_filled | filled | canceled | rejected

        // timestamps
        time: item.created_at,
        updatedAt: item.updated_at,
      };
    };

    const orders = rows.map(normalize).filter(Boolean);

    const totalPages = Math.ceil(count / Number(limit));

    // ── aggregate stats across the full result set (not just this page) ────────
    // Run a lightweight aggregation query in parallel if you want cross-page stats;
    // for now we compute from the current page as a fast approximation.
    const pageStats = {
      totalVolume: orders.reduce((s, o) => s + o.total, 0),
      totalFilledVolume: orders.reduce((s, o) => s + o.filledTotal, 0),
      buyOrders: orders.filter(o => o.side === 'buy').length,
      sellOrders: orders.filter(o => o.side === 'sell').length,
    };

    return {
      orders,
      pagination: {
        page: Number(page),
        limit: Number(limit),
        totalCount: count,
        totalPages,
        hasNext: Number(page) < totalPages,
        hasPrev: Number(page) > 1,
      },
      pageStats,
    };
  }

  /**
   * getBinaryTrades
   *
   * Fetches paginated binary trade history for a user with optional filters.
   * Mirrors the shape of getSpotOrders so the frontend AuthContext / fetchResult
   * pattern works identically.
   *
   * @param {number} userId
   * @param {object} filters
   * @param {string} [filters.direction]   'buy' | 'sell'
   * @param {string} [filters.status]      'open' | 'won' | 'lost' | 'draw' | 'cancelled'
   * @param {string} [filters.walletType]  'real' | 'demo'
   * @param {string} [filters.assetSymbol] e.g. 'BTCUSDT'
   * @param {string} [filters.startDate]   ISO string
   * @param {string} [filters.endDate]     ISO string
   * @param {number} [filters.page=1]
   * @param {number} [filters.limit=20]
   * @returns {{ trades, pagination, pageStats }}
   */
  async getOptionOrders(userId, filters = {}) {
    const {
      direction,
      status,
      walletType,
      assetSymbol,
      startDate,
      endDate,
      page = 1,
      limit = 20,
    } = filters;

    const offset = (Number(page) - 1) * Number(limit);

    // ── where clause ──────────────────────────────────────────────────────────
    const where = { user_id: userId };

    if (direction) where.direction = direction;
    if (status) where.status = status;
    if (walletType) where.wallet_type = walletType;
    if (assetSymbol) where.asset_symbol = assetSymbol;

    if (startDate || endDate) {
      where.created_at = {};
      if (startDate) where.created_at[Op.gte] = new Date(startDate);
      if (endDate) where.created_at[Op.lte] = new Date(endDate);
    }

    // ── query ──────────────────────────────────────────────────────────────────
    const { count, rows } = await BinaryTrade.findAndCountAll({
      where,
      order: [['created_at', 'DESC']],
      limit: Number(limit),
      offset,
      distinct: true,
    });

    // ── normalize ──────────────────────────────────────────────────────────────
    const normalize = (item) => ({
      id: item.id,

      // asset
      assetId: item.asset_id,
      assetSymbol: item.asset_symbol,

      // trade core
      direction: item.direction,           // 'buy' | 'sell'
      walletType: item.wallet_type,         // 'real' | 'demo'

      // financials
      investment: Number(item.investment ?? 0),
      payoutPercent: Number(item.payout_percent ?? 0),
      potentialProfit: Number(item.potential_profit ?? 0),
      potentialReturn: Number(item.potential_return ?? 0),
      profit: Number(item.profit ?? 0),
      payoutAmount: Number(item.payout_amount ?? 0),

      // prices
      entryPrice: Number(item.entry_price ?? 0),
      resultPrice: item.result_price != null ? Number(item.result_price) : null,

      // timing
      durationSeconds: item.duration_seconds,
      expiresAt: item.expires_at,
      settledAt: item.settled_at ?? null,

      // status
      status: item.status,             // 'open' | 'won' | 'lost' | 'draw' | 'cancelled'

      // timestamps
      createdAt: item.created_at,
      updatedAt: item.updated_at,

      // extra meta
      meta: item.meta ?? null,
    });

    const trades = rows.map(normalize);

    const totalPages = Math.ceil(count / Number(limit));

    // ── aggregate stats across this page ──────────────────────────────────────
    // For accurate cross-page totals, run a separate aggregate query (see below).
    const settled = trades.filter(tr => tr.status === 'won' || tr.status === 'lost');
    const won = trades.filter(tr => tr.status === 'won');

    const pageStats = {
      wonTrades: won.length,
      lostTrades: trades.filter(tr => tr.status === 'lost').length,
      openTrades: trades.filter(tr => tr.status === 'open').length,
      drawTrades: trades.filter(tr => tr.status === 'draw').length,
      netProfit: trades.reduce((s, tr) => s + tr.profit, 0),
      winRate: settled.length > 0
        ? parseFloat(((won.length / settled.length) * 100).toFixed(2))
        : 0,
      totalInvestment: trades.reduce((s, tr) => s + tr.investment, 0),
    };

    return {
      trades,
      pagination: {
        page: Number(page),
        limit: Number(limit),
        totalCount: count,
        totalPages,
        hasNext: Number(page) < totalPages,
        hasPrev: Number(page) > 1,
      },
      pageStats,
    };
  }


  // ─── getOpenBinaryTrades ──────────────────────────────────────────────────────
  /**
   * Returns all currently open (unexpired, unsettled) binary trades for a user.
   * Used by BinaryOpenTrades.jsx — powers the live countdown / auto-settle view.
   *
   * @param {number} userId
   * @param {object} filters
   * @param {string} [filters.direction]   'buy' | 'sell'
   * @param {string} [filters.walletType]  'real' | 'demo'
   * @param {string} [filters.assetSymbol]
   * @param {number} [filters.page=1]
   * @param {number} [filters.limit=20]
   */
  async getOpenOptionTrades(userId, filters = {}) {
    const {
      direction,
      walletType,
      assetSymbol,
      page = 1,
      limit = 20,
    } = filters;

    const offset = (Number(page) - 1) * Number(limit);

    const where = {
      user_id: userId,
      status: 'open',          // hard-locked — this method is open trades only
    };

    if (direction) where.direction = direction;
    if (walletType) where.wallet_type = walletType;
    if (assetSymbol) where.asset_symbol = assetSymbol;

    const { count, rows } = await BinaryTrade.findAndCountAll({
      where,
      order: [['expires_at', 'ASC']],   // soonest to expire first
      limit: Number(limit),
      offset,
      distinct: true,
    });

    const normalize = (item) => ({
      id: item.id,
      assetId: item.asset_id,
      assetSymbol: item.asset_symbol,
      direction: item.direction,
      walletType: item.wallet_type,
      investment: Number(item.investment ?? 0),
      payoutPercent: Number(item.payout_percent ?? 0),
      potentialProfit: Number(item.potential_profit ?? 0),
      potentialReturn: Number(item.potential_return ?? 0),
      entryPrice: Number(item.entry_price ?? 0),
      resultPrice: null,
      durationSeconds: item.duration_seconds,
      expiresAt: item.expires_at,       // used by frontend countdown timer
      settledAt: null,
      status: 'open',
      profit: 0,
      payoutAmount: 0,
      createdAt: item.created_at,
      updatedAt: item.updated_at,
      meta: item.meta ?? null,
    });

    const trades = rows.map(normalize);
    const totalPages = Math.ceil(count / Number(limit));

    const pageStats = {
      callTrades: trades.filter(tr => tr.direction === 'buy').length,
      putTrades: trades.filter(tr => tr.direction === 'sell').length,
      totalInvestment: trades.reduce((s, tr) => s + tr.investment, 0),
      totalPotentialProfit: trades.reduce((s, tr) => s + tr.potentialProfit, 0),
    };

    return {
      trades,
      pagination: {
        page: Number(page),
        limit: Number(limit),
        totalCount: count,
        totalPages,
        hasNext: Number(page) < totalPages,
        hasPrev: Number(page) > 1,
      },
      pageStats,
    };
  }


  // ─── getBinaryTradeHistory ────────────────────────────────────────────────────
  /**
   * Returns settled binary trade history (won / lost / draw / cancelled).
   * Open trades are intentionally excluded — use getOpenBinaryTrades for those.
   * Used by BinaryHistory.jsx.
   *
   * @param {number} userId
   * @param {object} filters
   * @param {string} [filters.direction]    'buy' | 'sell'
   * @param {string} [filters.status]       'won' | 'lost' | 'draw' | 'cancelled'
   *                                         omit = all settled statuses
   * @param {string} [filters.walletType]   'real' | 'demo'
   * @param {string} [filters.assetSymbol]
   * @param {string} [filters.startDate]    ISO string
   * @param {string} [filters.endDate]      ISO string
   * @param {number} [filters.page=1]
   * @param {number} [filters.limit=20]
   */
  async getOptionTradeHistory(userId, filters = {}) {
    const {
      direction,
      status,
      walletType,
      assetSymbol,
      startDate,
      endDate,
      page = 1,
      limit = 20,
    } = filters;

    const offset = (Number(page) - 1) * Number(limit);

    const SETTLED_STATUSES = ['won', 'lost', 'draw', 'cancelled'];

    const where = {
      user_id: userId,
      // specific settled status requested, or all settled (excludes 'open')
      status: status && SETTLED_STATUSES.includes(status)
        ? status
        : { [Op.in]: SETTLED_STATUSES },
    };

    if (direction) where.direction = direction;
    if (walletType) where.wallet_type = walletType;
    if (assetSymbol) where.asset_symbol = assetSymbol;

    if (startDate || endDate) {
      where.created_at = {};
      if (startDate) where.created_at[Op.gte] = new Date(startDate);
      if (endDate) where.created_at[Op.lte] = new Date(endDate);
    }

    const { count, rows } = await BinaryTrade.findAndCountAll({
      where,
      order: [['created_at', 'DESC']],
      limit: Number(limit),
      offset,
      distinct: true,
    });

    const normalize = (item) => ({
      id: item.id,
      assetId: item.assetId,
      assetSymbol: item.assetSymbol,
      direction: item.direction,
      walletType: item.wallet_type,
      investment: Number(item.investment ?? 0),
      payoutPercent: Number(item.payoutPercent ?? 0),
      potentialProfit: Number(item.potentialProfit ?? 0),
      potentialReturn: Number(item.potentialReturn ?? 0),
      profit: Number(item.profit ?? 0),
      payoutAmount: Number(item.payoutAmount ?? 0),
      entryPrice: Number(item.entryPrice ?? 0),
      resultPrice: item.resultPrice != null ? Number(item.resultPrice) : null,
      durationSeconds: item.durationSeconds,
      expiresAt: item.expiresAt,
      settledAt: item.settledAt ?? null,
      status: item.status,
      createdAt: item.createdAt,
      updatedAt: item.updatedAt,
      meta: item.meta ?? null,
    });

    const trades = rows.map(normalize);
    const totalPages = Math.ceil(count / Number(limit));

    const won = trades.filter(tr => tr.status === 'won');
    const lost = trades.filter(tr => tr.status === 'lost');
    const settled = won.length + lost.length;

    const pageStats = {
      wonTrades: won.length,
      lostTrades: lost.length,
      drawTrades: trades.filter(tr => tr.status === 'draw').length,
      cancelledTrades: trades.filter(tr => tr.status === 'cancelled').length,
      netProfit: trades.reduce((s, tr) => s + tr.profit, 0),
      totalInvestment: trades.reduce((s, tr) => s + tr.investment, 0),
      winRate: settled > 0
        ? parseFloat(((won.length / settled) * 100).toFixed(2))
        : 0,
    };

    return {
      trades,
      pagination: {
        page: Number(page),
        limit: Number(limit),
        totalCount: count,
        totalPages,
        hasNext: Number(page) < totalPages,
        hasPrev: Number(page) > 1,
      },
      pageStats,
    };
  }


  // ─── getBinaryTradeAggregates ─────────────────────────────────────────────────
  /**
   * Optional companion — runs a single GROUP BY query for accurate cross-page stats.
   * Call in parallel with getBinaryTradeHistory when the stats bar needs totals
   * across ALL matching rows, not just the current page.
   *
   * @param {number} userId
   * @param {object} filters  Same shape as getBinaryTradeHistory (page/limit ignored)
   */
  async getOptionTradeAggregates(userId, filters = {}) {
    const { direction, walletType, assetSymbol, startDate, endDate } = filters;

    const SETTLED_STATUSES = ['won', 'lost', 'draw', 'cancelled'];

    const where = {
      user_id: userId,
      status: { [Op.in]: SETTLED_STATUSES },
    };

    if (direction) where.direction = direction;
    if (walletType) where.wallet_type = walletType;
    if (assetSymbol) where.asset_symbol = assetSymbol;

    if (startDate || endDate) {
      where.created_at = {};
      if (startDate) where.created_at[Op.gte] = new Date(startDate);
      if (endDate) where.created_at[Op.lte] = new Date(endDate);
    }

    const rows = await BinaryTrade.findAll({
      where,
      attributes: [
        'status',
        [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
        [sequelize.fn('SUM', sequelize.col('profit')), 'totalProfit'],
        [sequelize.fn('SUM', sequelize.col('investment')), 'totalInvestment'],
      ],
      group: ['status'],
      raw: true,
    });

    const byStatus = {};
    rows.forEach(r => {
      byStatus[r.status] = {
        count: Number(r.count ?? 0),
        totalProfit: Number(r.totalProfit ?? 0),
        totalInvestment: Number(r.totalInvestment ?? 0),
      };
    });

    const wonCount = byStatus.won?.count ?? 0;
    const lostCount = byStatus.lost?.count ?? 0;
    const settled = wonCount + lostCount;

    return {
      wonTrades: wonCount,
      lostTrades: lostCount,
      drawTrades: byStatus.draw?.count ?? 0,
      cancelledTrades: byStatus.cancelled?.count ?? 0,
      netProfit: rows.reduce((s, r) => s + Number(r.totalProfit ?? 0), 0),
      totalInvestment: rows.reduce((s, r) => s + Number(r.totalInvestment ?? 0), 0),
      winRate: settled > 0
        ? parseFloat(((wonCount / settled) * 100).toFixed(2))
        : 0,
    };
  }

  /**
   * Get order by ID
   */
  async getOrderById(userId, orderId) {
    const order = await Order.findOne({
      where: { id: orderId, userId },
      include: [{ model: Trade, as: 'trades' }],
    });

    if (!order) {
      throw new NotFoundError('Order not found');
    }

    return order;
  }

  /**
   * Cancel order
   */
  async cancelOrder(userId, orderId) {
    const order = await Order.findOne({
      where: { id: orderId, userId },
    });

    if (!order) {
      throw new NotFoundError('Order not found');
    }

    if (!['open', 'partially_filled', 'pending'].includes(order.status)) {
      throw new BadRequestError('Order cannot be cancelled');
    }

    const t = await sequelize.transaction();

    try {
      // Unlock remaining balance
      const walletType = order.market === 'futures' ? 'futures' : 'spot';
      const walletCurrency = order.side === 'buy' ? order.quoteCurrency : order.baseCurrency;

      const wallet = await Wallet.findOne({
        where: { userId, type: walletType, currency: walletCurrency },
      });

      if (wallet) {
        const remainingQuantity = parseFloat(order.quantity) - parseFloat(order.filledQuantity);
        const remainingValue = order.side === 'buy'
          ? remainingQuantity * parseFloat(order.price || 0)
          : remainingQuantity;

        await wallet.update({
          available: parseFloat(wallet.available) + remainingValue,
          locked: parseFloat(wallet.locked) - remainingValue,
        }, { transaction: t });
      }

      // Update order status
      await order.update({
        status: 'cancelled',
        cancelledAt: new Date(),
        cancelReason: 'User requested cancellation',
      }, { transaction: t });

      await t.commit();

      logger.info(`Order cancelled: ${order.orderId} by user ${userId}`);

      return order;
    } catch (error) {
      await t.rollback();
      throw error;
    }
  }

  /**
   * Cancel all open orders
   */
  async cancelAllOrders(userId, market = null, symbol = null) {
    const where = {
      userId,
      status: { [Op.in]: ['open', 'partially_filled'] },
    };

    if (market) where.market = market;
    if (symbol) where.symbol = symbol;

    const orders = await Order.findAll({ where });

    const results = await Promise.allSettled(
      orders.map(order => this.cancelOrder(userId, order.id))
    );

    const cancelled = results.filter(r => r.status === 'fulfilled').length;
    const failed = results.filter(r => r.status === 'rejected').length;

    return { cancelled, failed, total: orders.length };
  }

  /**
   * Get trades for a user
   */
  async getTrades(userId, filters = {}) {
    const { market, symbol, side, startDate, endDate, page = 1, limit = 20 } = filters;

    const where = { userId };

    if (market) where.market = market;
    if (symbol) where.symbol = symbol;
    if (side) where.side = side;

    if (startDate || endDate) {
      where.executedAt = {};
      if (startDate) where.executedAt[Op.gte] = new Date(startDate);
      if (endDate) where.executedAt[Op.lte] = new Date(endDate);
    }

    const { count, rows } = await Trade.findAndCountAll({
      where,
      order: [['executedAt', 'DESC']],
      limit,
      offset: (page - 1) * limit,
      include: [{ model: Order, as: 'order', attributes: ['orderId', 'type', 'timeInForce'] }],
    });

    return {
      trades: rows,
      pagination: {
        total: count,
        page,
        limit,
        totalPages: Math.ceil(count / limit),
      },
    };
  }


  async getAllOrders(userId, filters = {}) {
    const { market, symbol, side, startDate, endDate, page = 1, limit = 20 } = filters;

    const where = { userId };

    if (market) where.market = market;
    if (symbol) where.symbol = symbol;
    if (side) where.side = side;

    if (startDate || endDate) {
      where.executedAt = {};
      if (startDate) where.executedAt[Op.gte] = new Date(startDate);
      if (endDate) where.executedAt[Op.lte] = new Date(endDate);
    }
    const [
      spots,
      //forex
    ] = await Promise.all([
      SpotOrder.findAndCountAll({
        where,
        order: [['executedAt', 'DESC']],
        limit,
        offset: (page - 1) * limit,
        include: [{ model: MarketPair, as: 'pair' }],
      }),
      /*  DerivativeOrderOrder.findAndCountAll({
         where,
         order: [['executedAt', 'DESC']],
         limit,
         offset: (page - 1) * limit,
         include: [{ model: Order, as: 'order', attributes: ['orderId', 'type', 'timeInForce'] }],
       }), */

    ])

    const spotOrders = spots.rows.map(t => ({
      symbol: t.pair?.sym,
      qty: t.qty,
      filled: t.qty_filled,
      entryPrice: price,
      limitPrice: t.stop_price,
      status: t.status
    }))

    return {
      //trades: rows,
      orders: spotOrders,
      pagination: {
        total: spots.count,
        page,
        limit,
        totalPages: Math.ceil(spots.count / limit),
      },
    };
  }

  /**
   * Get trade by ID
   */
  async getTradeById(userId, tradeId) {
    const trade = await Trade.findOne({
      where: { id: tradeId, userId },
      include: [{ model: Order, as: 'order' }],
    });

    if (!trade) {
      throw new NotFoundError('Trade not found');
    }

    return trade;
  }

  /**
   * Get trading statistics
   */
  async getStatistics(userId, startDate = null, endDate = null) {
    const where = { userId };

    if (startDate || endDate) {
      where.executedAt = {};
      if (startDate) where.executedAt[Op.gte] = new Date(startDate);
      if (endDate) where.executedAt[Op.lte] = new Date(endDate);
    }

    const trades = await Trade.findAll({ where });

    const stats = {
      totalTrades: trades.length,
      buyTrades: trades.filter(t => t.side === 'buy').length,
      sellTrades: trades.filter(t => t.side === 'sell').length,
      totalVolume: trades.reduce((sum, t) => sum + parseFloat(t.quoteQuantity || 0), 0),
      totalFees: trades.reduce((sum, t) => sum + parseFloat(t.fee || 0), 0),
      byMarket: {},
      bySymbol: {},
    };

    // Group by market
    trades.forEach(trade => {
      if (!stats.byMarket[trade.market]) {
        stats.byMarket[trade.market] = { count: 0, volume: 0 };
      }
      stats.byMarket[trade.market].count++;
      stats.byMarket[trade.market].volume += parseFloat(trade.quoteQuantity || 0);

      if (!stats.bySymbol[trade.symbol]) {
        stats.bySymbol[trade.symbol] = { count: 0, volume: 0 };
      }
      stats.bySymbol[trade.symbol].count++;
      stats.bySymbol[trade.symbol].volume += parseFloat(trade.quoteQuantity || 0);
    });

    return stats;
  }

  /**
   * Get profit/loss data
   */
  async getProfitLoss(userId, period = '30d') {
    const periodDays = {
      '7d': 7,
      '30d': 30,
      '90d': 90,
      '365d': 365,
    };

    const days = periodDays[period] || 30;
    const startDate = new Date();
    startDate.setDate(startDate.getDate() - days);

    const trades = await Trade.findAll({
      where: {
        userId,
        executedAt: { [Op.gte]: startDate },
      },
      order: [['executedAt', 'ASC']],
    });

    // Calculate daily P&L (simplified)
    const dailyPnL = {};
    trades.forEach(trade => {
      const date = trade.executedAt.toISOString().split('T')[0];
      if (!dailyPnL[date]) {
        dailyPnL[date] = { volume: 0, fees: 0, trades: 0 };
      }
      dailyPnL[date].volume += parseFloat(trade.quoteQuantity || 0);
      dailyPnL[date].fees += parseFloat(trade.fee || 0);
      dailyPnL[date].trades++;
    });

    return {
      period,
      startDate,
      endDate: new Date(),
      totalTrades: trades.length,
      totalVolume: trades.reduce((sum, t) => sum + parseFloat(t.quoteQuantity || 0), 0),
      totalFees: trades.reduce((sum, t) => sum + parseFloat(t.fee || 0), 0),
      dailyPnL: Object.entries(dailyPnL).map(([date, data]) => ({
        date,
        ...data,
      })),
    };
  }


}

module.exports = new TradeService();
