const { Op } = require('sequelize');
const { sequelize, Wallet, Transaction, User, Coin, CoinNetwork, Network, WalletAddress, UserBalance, Asset, SymbolStats, MarketPair } = require('../models');
const { generateTransactionId, generateRandomString, safeNumber, getUploadedFile } = require('../utils/helpers');
const { BadRequestError, NotFoundError, ForbiddenError, ValidationError, ConflictError } = require('../utils/errors');
const logger = require('../utils/logger');

function mapTx(t) {
  const meta = t.metadata || {};
  const type = t.type; // 'deposit' | 'withdraw' | 'transfer'
  const asset = (meta.asset || t.currency || t.asset || "").toString().toUpperCase();
  const network = meta.network || t.network || null;

  // optional fields depending on your schema
  const amount = safeNumber(t.amount);
  const usdValue =
    safeNumber(meta.usdValue ?? meta.usd_value ?? t.usdValue ?? t.usd_value); // ok if you store it
  const status = t.status;
  const date = t.createdAt ?? t.created_at; // keep as Date

  return {
    id: t.id,
    type,
    asset: asset || null,
    network: network || null,
    amount,
    usdValue: usdValue || null,
    status,
    coin: t?.wallet?.coin?.symbol,
    coinName: t?.wallet?.coin?.name,
    date,
    txHash: t.txHash ?? t.tx_hash ?? meta.txHash ?? null,
    address: t.address ?? meta.address ?? null,
    confirmations: meta.confirmations ?? null,
    failReason: meta.failReason ?? meta.fail_reason ?? null,
    from: meta.from ?? null,
    to: meta.to ?? null,
  };
}

function mapRecentTx(t) {
  return {
    id: t.transactionId,
    asset: t.currency ?? null,
    network: t.network ?? null,
    amount: t.amount,
    status: t.status,
    time: t.createdAt ?? t.created_at,
    txHash: t.txHash ?? t.tx_hash ?? null,
  };
}

function buildStats(mappedTxs) {
  const deposits = mappedTxs.filter((x) => x.type === "deposit");
  const withdrawals = mappedTxs.filter((x) => x.type === "withdraw");
  const transfers = mappedTxs.filter((x) => x.type === "transfer");

  const sum = (arr) => arr.reduce((s, x) => s + safeNumber(x.usdValue ?? 0), 0);

  return {
    totalDeposits: sum(deposits),
    totalWithdrawals: sum(withdrawals),
    totalTransfers: sum(transfers),
    depositCount: deposits.length,
    withdrawCount: withdrawals.length,
    transferCount: transfers.length,
  };
}

class WalletService {
  /**
   * Get all wallets for a user
   */
  async getWallets(userId) {
    return Wallet.findAll({
      where: { userId },
      include: [{ model: Asset, as: 'coin' }],
    });
  }

  async updateCoinNetwork() {
    const ex = {
      BTC: ['BTC'],
      ETH: ['ETH'],
      USDT: ['ETH', 'TRX', 'BEP20', 'SOL'],
      BCH: ['BCH'],
      LTC: ['LTC'],
      SOL: ['SOL'],
      TRX: ['TRX'],
      DOGE: ['DOGE'],
      BNB: ['BEP20', 'BNB'],
      XRP: ['XRP']
    };

    return sequelize.transaction(async (t) => {
      const coins = await Asset.findAll({
        attributes: ['id', 'symbol'],
        transaction: t,
      });

      const networks = await Network.findAll({
        attributes: ['id', 'network'],
        transaction: t,
      });

      // ✅ Build lookup map: networkCode -> networkRow
      const networkByCode = new Map(networks.map(n => [n.network, n]));

      const existing = await CoinNetwork.findAll({
        attributes: ['coin_id', 'network_id'],
        transaction: t,
      });

      const existingSet = new Set(existing.map(r => `${r.coin_id}:${r.network_id}`));

      const rows = [];
      const missingNetworks = new Set();

      for (const coin of coins) {
        const codes = ex[coin.symbol] || [];

        for (const code of codes) {
          const network = networkByCode.get(code);

          if (!network) {
            missingNetworks.add(code);
            continue;
          }

          const key = `${coin.id}:${network.id}`;
          if (existingSet.has(key)) continue;

          rows.push({
            coinId: coin.id,
            networkId: network.id,
            status: true,
          });

          existingSet.add(key);
        }
      }

      if (rows.length) {
        await CoinNetwork.bulkCreate(rows, {
          ignoreDuplicates: true,
          transaction: t,
        });
      }

      if (missingNetworks.size) {
        console.warn('[updateCoinNetwork] Missing networks in DB:', [...missingNetworks]);
      }

      return { inserted: rows.length, coins, missingNetworks: [...missingNetworks] };
    });
  }

  /**
   * Get wallet overview with totals
   */
  async getOverview(userId, limit = 5) {

    const [wallets, recentTransactions] = await Promise.all([
      this.getWallets(userId),
      Transaction.findAll({
        where: { userId, source: 'wallet' },
        order: [['createdAt', 'DESC']],
        limit,
      }),
    ]);

    const walletSymbols = wallets.map(
      w => `${w.coin?.symbol}_USDT`
    );

    // Get market prices first
    const marketPairs = await MarketPair.findAll({
      where: {
        symbol: {
          [Op.in]: walletSymbols
        },
        type: 'spot'
      },
      include: [{ model: SymbolStats, as: 'rate' }]
    });

    // Convert to lookup map (FAST access)
    const priceMap = new Map(
      marketPairs.map(p => [
        p.symbol,
        {
          price: safeNumber(p?.rate?.current_price),
          change: safeNumber(p?.rate?.change_percent24h)
        }
      ])
    );

    // Map wallets using REAL prices
    const mappedWallets = wallets.map((w) => {

      const balance = safeNumber(w.balance);
      const locked = safeNumber(w.lockedBalance ?? w.locked_balance);
      const available = balance - locked;

      const symbolKey = `${w.coin?.symbol}_USDT`;

      const price = priceMap.get(symbolKey)?.price ?? 0;
      const change = priceMap.get(symbolKey)?.change ?? 0;

      const previousPrice = price / (1 + (change / 100 || 0));

      const amount = balance * price;
      const previousAmount = balance * previousPrice;

      return {
        id: w.id,
        symbol: w.coin?.symbol,
        name: w.coin?.name,
        balance,
        price,
        previousPrice,
        amount,
        previousAmount,
        change,
        locked,
        available,
        logo: getUploadedFile(w.coin?.logo , 'assets'),
      };
    });

    const totalBalance = mappedWallets.reduce((sum, x) => sum + x.amount, 0);
    const previousBalance = mappedWallets.reduce((sum, x) => sum + x.previousAmount, 0);

    const stats = {
      totalBalance,
      previousBalance,
      pctChange: previousBalance > 0
        ? (totalBalance - previousBalance) / previousBalance
        : 0,
    };

    return {
      stats,
      assets: mappedWallets,
      recentTransactions,
      walletPrice: marketPairs.map(p => ({
        symbol: p.symbol,
        price: p?.rate?.current_price,
        changePercent: p?.rate?.change_percent24h
      }))
    };
  }

  async getDeposit(userId, limit = 5) {

    const [wallets, networks, transactions] = await Promise.all([
      Wallet.findAll({
        where: { userId },
        include: [
          {
            model: Asset,
            as: "coin",
            include: [{
              model: CoinNetwork, as: "networks", include: [
                { model: Network, as: "network" }
              ]
            }],
          },
        ],
      }),
      Network.findAll(),
      Transaction.findAll({
        where: { userId, source: "wallet", kind: "deposit" },
        order: [["createdAt", "DESC"]],
        limit,
      }),
    ]);

    // Assets list (unique coins)
    const assetsMap = new Map();
    for (const w of wallets) {
      const coinId = w.coin_id ?? w.coinId ?? w.coin?.id;
      if (!coinId) continue;

      if (!assetsMap.has(coinId)) {
        assetsMap.set(coinId, {
          id: coinId,
          name: w.coin?.name ?? null,
          symbol: w.coin?.symbol ?? null,
          logo: getUploadedFile(w.coin?.logo ?? null, 'assets'),
          networks: (w.coin?.networks ?? []).map((cn) => ({
            id: cn.id,
            networkId: cn.network_id ?? cn.networkId ?? null,
            network: cn.network?.network ?? cn.name ?? null,
            address: w.address ?? w.wallet_address ?? null, // adjust to your column
            memo: w.memo ?? null,
            minDeposit: cn.min_deposit ?? cn.minDeposit ?? null,
            confirmations: cn.confirmations ?? null,
            fee: cn.fee ?? cn.network_fee ?? null,
          })),
        });
      }
    }
    const assets = [...assetsMap.values()];

    // Network details (global)
    const networkDetails = networks.map((n) => ({
      id: n.id,
      name: n.name,
      network: n.network,
      confirmations: n.confirmations,
      minDeposit: n.minDeposits ?? n.min_deposit ?? null,
      estimatedTime: n.time ?? "30-60 min",
    }));

    const recentDeposits = transactions.map((t) => mapRecentTx(t));


    return { assets, networkDetails, recentDeposits };
  }

  async getWithdrawal(userId, limit = 5) {
    const [wallets, networks, transactions] = await Promise.all([
      Wallet.findAll({
        where: { userId },
        include: [
          {
            model: Asset,
            as: "coin",
            include: [{
              model: CoinNetwork, as: "networks", include: [
                { model: Network, as: "network" }
              ]
            }],
          },
        ],
      }),
      Network.findAll(),
      Transaction.findAll({
        where: { userId, source: "wallet", kind: "withdrawal" },
        order: [["createdAt", "DESC"]],
        limit,
      }),
    ]);

    const walletSymbols = wallets.map(
      w => `${w.coin?.symbol}_USDT`
    );

    // Get market prices first
    const marketPairs = await MarketPair.findAll({
      where: {
        symbol: {
          [Op.in]: walletSymbols
        },
        type: 'spot'
      },
      include: [{ model: SymbolStats, as: 'rate' }]
    });

    // Convert to lookup map (FAST access)
    const priceMap = new Map(
      marketPairs.map(p => [
        p.symbol,
        {
          price: safeNumber(p?.rate?.current_price),
          change: safeNumber(p?.rate?.change_percent24h)
        }
      ])
    );

    // Map wallets using REAL prices
    const assets = wallets.map((w) => {

      const balance = safeNumber(w.balance);
      const locked = safeNumber(w.lockedBalance ?? w.locked_balance);
      const available = balance - locked;

      const symbolKey = `${w.coin?.symbol}_USDT`;

      const price = priceMap.get(symbolKey)?.price ?? 0;
      const change = priceMap.get(symbolKey)?.change ?? 0;

      const previousPrice = price / (1 + (change / 100 || 0));

      const amount = balance * price;
      const previousAmount = balance * previousPrice;

      return {
        id: w.id,
        symbol: w.coin?.symbol,
        name: w.coin?.name,
        balance,
        price,
        previousPrice,
        amount,
        previousAmount,
        change,
        locked,
        available,
        logo: getUploadedFile(w.coin?.logo, 'assets'),
        fiatValue: balance * price,
        networks: (w.coin?.networks ?? []).map((cn) => ({
          id: cn.id,
          networkId: cn.network_id ?? cn.networkId ?? null,
          network: cn.network ?? cn.name ?? null,
          fee: cn.fee ?? cn.network_fee ?? null,
          minWithdrawal: cn.min_withdrawal ?? cn.minWithdrawal ?? null,
          address: w.address ?? w.wallet_address ?? null, // withdraw-from address if you store one
        })),
      };
    });

    /* const assets = wallets.map((w) => {
      const balance = safeNumber(w.balance);
      const locked = safeNumber(w.lockedBalance ?? w.locked_balance);
      const available = balance - locked;

      const price = safeNumber(w.rate?.price ?? w.coin?.rate?.price ?? 1);

      return {
        id: w.coin_id ?? w.coinId ?? w.coin?.id,
        name: w.coin?.name ?? null,
        symbol: w.coin?.symbol ?? null,
        logo: w.coin?.logo ?? null,
        balance,
        available,
        locked,
        price,
        fiatValue: balance * price,
        networks: (w.coin?.networks ?? []).map((cn) => ({
          id: cn.id,
          networkId: cn.network_id ?? cn.networkId ?? null,
          network: cn.network ?? cn.name ?? null,
          fee: cn.fee ?? cn.network_fee ?? null,
          minWithdrawal: cn.min_withdrawal ?? cn.minWithdrawal ?? null,
          address: w.address ?? w.wallet_address ?? null, // withdraw-from address if you store one
        })),
      };
    }); */

    const networkDetails = networks.map((n) => ({
      id: n.id,
      name: n.name,
      network: n.network,
      fee: n.networkFees ?? n.network_fees ?? null,
      minWithdrawal: n.minWithdrawal ?? n.min_withdrawal ?? null,
    }));

    const withdrawalLimits = {
      daily: 100000,
      remaining: 85000,
      minWithdraw: 0.001,
    };

    const recentWithdrawals = transactions.map((t) => mapRecentTx(t));

    return { assets, networkDetails, withdrawalLimits, transactions: recentWithdrawals };
  }

  /**
   * Get wallets by type
   */
  async getWalletsByType(userId, type) {
    const wallets = await Wallet.findAll({
      where: { userId, type },
      order: [['currency', 'ASC']],
    });
    return wallets;
  }

  /**
   * Get balance for specific currency
   */
  async getBalance(userId, type, currency) {
    const wallet = await Wallet.findOne({
      where: { userId, type, currency: currency.toUpperCase() },
    });

    if (!wallet) {
      return { available: 0, locked: 0, pending: 0, staked: 0, total: 0 };
    }

    return {
      available: parseFloat(wallet.available),
      locked: parseFloat(wallet.locked),
      pending: parseFloat(wallet.pending),
      staked: parseFloat(wallet.staked),
      total: wallet.getTotal(),
    };
  }

  /**
   * Get or create deposit address
   */
  async getDepositAddress(userId, data = {}) {
    const { symbol, network } = data;
    const coin = await Asset.findOne({ where: { symbol } })
    if (!coin) {
      throw new ValidationError('Invalid Coin')
    }
    const getNetwork = await Network.findOne({ where: { network } })
    if (!getNetwork) {
      throw new ValidationError('Invalid Network')
    }
    const walletAddress = await WalletAddress.findOne({
      where: {
        userId,
        coinId: coin.id,
        networkId: getNetwork.id
      }
    })
    return {
      address: walletAddress?.address ? walletAddress?.address : 'none',
    };
  }

  /**
   * Get deposit history
   */
  async getDepositHistory(userId, filters = {}) {
    const { currency, status, page = 1, limit = 20 } = filters;

    const where = { userId, type: 'deposit' };
    if (currency) where.currency = currency.toUpperCase();
    if (status) where.status = status;

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

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

  /**
   * Request withdrawal
   */
  async requestWithdrawal(userId, withdrawalData) {
    const { symbol, type } = withdrawalData;
    const amount = Number(withdrawalData.amount);
    const symbolUpper = symbol.toUpperCase();
    const coin = await Asset.findOne({ where: { symbol: symbolUpper } })
    if (!coin) {
      throw new BadRequestError(`${symbolUpper} not available for withdrawal`);
    }

    // Get wallet
    const wallet = await Wallet.findOne({
      where: { userId, coinId: coin.id },
    });
    if (!wallet) {
      throw new BadRequestError(`No ${symbolUpper} wallet found`);
    }

    if (type === 'wallet') {
      const networkUpper = withdrawalData?.network?.toUpperCase();
      const getNetwork = await Network.findOne({ where: { network: networkUpper } })
      if (!getNetwork) {
        throw new BadRequestError(`${networkUpper} not available for withdrawal`);
      }
    }
    const currentWalletBalance = Number(wallet.balance ?? 0)
    const lockedBalance = Number(wallet.locked_balance ?? 0)
    const available = currentWalletBalance - lockedBalance
    if (parseFloat(available) < amount) {
      throw new BadRequestError('Insufficient balance');
    }

    // Get user for verification check
    const user = await User.findByPk(userId);
    if (!user || user.kycStatus === 'none') {
      throw new ForbiddenError('KYC verification required for withdrawals');
    }

    const t = await sequelize.transaction();

    try {

      // await wallet.lockBalance(amount);

      // Create transaction
      const transaction = await Transaction.create({
        userId,
        source: 'wallet',
        toType: type === 'funding' ? 'internal' : 'external',
        kind: 'withdrawal',
        direction: 'debit',
        currency: symbolUpper,
        description: `Withdrawal to ${type === 'funding' ? 'funding account' : 'external wallet'} via ${symbolUpper} wallet`,
        walletId: wallet.id,
        amount: amount,
        netAmount: amount,
        network: withdrawalData?.network ?? '',
        address: withdrawalData?.address ?? '',
        status: type === 'funding' ? 'completed' : 'pending'
      }, { transaction: t })

      if (type === 'funding') {
        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)
        const creditAmount = amount * 123
        await Transaction.create({
          userId,
          source: 'funding',
          toType: 'internal',
          kind: 'deposit',
          direction: 'credit',
          currency: user.currency,
          amount: creditAmount,
          netAmount: creditAmount,
          status: 'completed'
        }, { transaction: t })

        await balance.update(
          { balance: currentBalance + creditAmount }, // debit original amount (or finalAmount if you only want to debit discounted)
          { transaction: t }
        );
      }

      await wallet.update(
        { balance: currentWalletBalance - amount }, // debit original amount (or finalAmount if you only want to debit discounted)
        { transaction: t }
      );


      await t.commit();

      logger.info(`Withdrawal requested: ${transaction.transactionId} by user ${userId}`);
      const recents = await Transaction.findAll({
        where: { userId, source: "wallet", kind: "withdrawal" },
        order: [["createdAt", "DESC"]],
        limit: 5,
      })
      return { transaction, recent: recents?.map(t => mapRecentTx(t)) };
    } catch (error) {
      await t.rollback();
      throw error;
    }
  }

  /**
   * Get withdrawal history
   */
  async getWithdrawalHistory(userId, filters = {}) {
    const { currency, status, page = 1, limit = 20 } = filters;

    const where = { userId, type: 'withdrawal' };
    if (currency) where.currency = currency.toUpperCase();
    if (status) where.status = status;

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

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

  /**
   * Cancel pending withdrawal
   */
  async cancelWithdrawal(userId, transactionId) {
    const transaction = await Transaction.findOne({
      where: { id: transactionId, userId, type: 'withdrawal' },
    });

    if (!transaction) {
      throw new NotFoundError('Withdrawal not found');
    }

    if (transaction.status !== 'pending') {
      throw new BadRequestError('Only pending withdrawals can be cancelled');
    }

    const t = await sequelize.transaction();

    try {
      // Unlock the amount
      const wallet = await Wallet.findByPk(transaction.walletId);
      if (wallet) {
        await wallet.unlockBalance(parseFloat(transaction.amount));
      }

      // Update transaction
      await transaction.cancel('Cancelled by user');

      await t.commit();

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

  /**
   * Transfer between wallets
   */
  async getTransfer(userId, limit = 5) {
    const [wallets, networks, transactions] = await Promise.all([
      Wallet.findAll({
        where: { userId },
        include: [
          {
            model: Asset,
            as: "coin",
            include: [{
              model: CoinNetwork, as: "networks", include: [
                { model: Network, as: "network" }
              ]
            }],
          },
        ],
      }),
      Network.findAll(),
      Transaction.findAll({
        where: { userId, source: "wallet", kind: "swap" },
        order: [["createdAt", "DESC"]],
        limit,
      }),
    ]);

    const assets = wallets.map((w) => {
      const balance = safeNumber(w.balance);
      const locked = safeNumber(w.lockedBalance ?? w.locked_balance);
      const available = balance - locked;

      const price = safeNumber(w.rate?.price ?? w.coin?.rate?.price ?? 1);

      return {
        id: w.coin_id ?? w.coinId ?? w.coin?.id,
        name: w.coin?.name ?? null,
        symbol: w.coin?.symbol ?? null,
        logo: getUploadedFile(w.coin?.logo ?? null,'assets'),
        balance,
        available,
        locked,
        price,
        fiatValue: balance * price,
      };
    });

    const networkDetails = networks.map((n) => ({
      id: n.id,
      name: n.name,
      network: n.network,
      fee: n.networkFees ?? n.network_fees ?? null,
      minWithdrawal: n.minWithdrawal ?? n.min_withdrawal ?? null,
    }));

    const recentTransfers = transactions.map((t) => mapRecentTx(t));

    return { assets, networkDetails, transactions: recentTransfers };
  }

  async swap(userId, from, to, amount) {
    from = from.toUpperCase();
    to = to.toUpperCase();

    if (from === to) {
      throw new BadRequestError('Source and destination must differ');
    }

    const fromAsset = await Asset.findOne({ where: { symbol: from } });
    const toAsset = await Asset.findOne({ where: { symbol: to } });

    if (!fromAsset) throw new BadRequestError('Invalid source coin');
    if (!toAsset) throw new BadRequestError('Invalid destination coin');

    const t = await sequelize.transaction();


    try {
      const fromWallet = await Wallet.findOne({
        where: { userId, coinId: fromAsset.id },
        transaction: t,
        lock: true
      });

      if (!fromWallet) throw new BadRequestError('Source wallet not found');

      // ✅ Balance check
      if (parseFloat(fromWallet.balance) < amount) {
        throw new BadRequestError('Insufficient balance');
      }

      // ✅ Get or create destination wallet
      let toWallet = await Wallet.findOne({
        where: { userId, coinId: toAsset.id },
        transaction: t,
        lock: true
      });

      if (!fromWallet) throw new BadRequestError('Destination wallet not found');


      const fromBefore = parseFloat(fromWallet.balance);
      const toBefore = parseFloat(toWallet.balance);

      // ❗ For now assume 1:1 (replace with real rate later)
      const rates = 1.5;
      const convertedAmount = amount * rates;

      // ✅ Update balances
      await fromWallet.update({
        balance: fromBefore - amount
      }, { transaction: t });

      await toWallet.update({
        balance: toBefore + convertedAmount
      }, { transaction: t });

      // ✅ OUT transaction
      await Transaction.create({
        transactionId: generateTransactionId('SWP'),
        userId: userId,
        source: 'wallet',
        toType: 'internal',
        kind: 'swap',
        direction: 'debit',
        currency: from,
        amount: amount,
        walletId: fromWallet.id,
        fromWalletId: fromWallet.id,
        toWalletId: toWallet.id,
        status: 'completed',
        completed_at: new Date(),
        description: `Swap ${from} → ${to}`,
        price: rates


      }, { transaction: t });

      // ✅ IN transaction
      /*  await Transaction.create({
         transactionId: generateTransactionId('SWP'),
         user_id: userId,
         wallet_id: toWallet.id,
         from_wallet_id: fromWallet.id,
         to_wallet_id: toWallet.id,
   
         source: 'wallet',
         to_type: 'internal',
   
         kind: 'swap',
         direction: 'credit',
   
         currency: to,
         amount: convertedAmount,
   
         balance_before: toBefore,
         balance_after: toBefore + convertedAmount,
   
         status: 'completed',
         completed_at: new Date(),
         description: `Swap from ${from}`
       }, { transaction: t }); */

      await t.commit();

      return { fromWallet, toWallet };

    } catch (err) {
      await t.rollback();
      throw err;
    }
  }


  /**
   * Get transfer history
   */
  async getTransferHistory(userId, page = 1, limit = 20) {
    const { count, rows } = await Transaction.findAndCountAll({
      where: {
        userId,
        type: { [Op.in]: ['transfer_in', 'transfer_out'] },
      },
      order: [['createdAt', 'DESC']],
      limit,
      offset: (page - 1) * limit,
    });

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

  /**
   * Get all transactions
   */


  async getTransactions(userId, filters = {}) {
    const {
      type = "all",
      currency,
      status = "all",
      startDate,
      endDate,
      page = 1,
      limit = 20,
    } = filters;

    const where = { userId, source: "wallet" };

    if (type && type !== "all") where.kind = type; // deposit/withdraw/transfer
    if (status && status !== "all") where.status = status;

    // If you store currency column use it; otherwise rely on metadata.asset
    if (currency) {
      where.currency = currency.toUpperCase();
      // If you DON'T have currency column, remove line above and filter on metadata in JS,
      // or use a JSONB query with Sequelize.literal(...) (tell me your exact column).
    }

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

    // main paged query
    const { count, rows } = await Transaction.findAndCountAll({
      where,
      order: [["createdAt", "DESC"]],
      limit,
      offset: (page - 1) * limit,
      include: [{
        model: Wallet, as: 'wallet', include: [{
          model: Asset, as: coin
        }]
      }]
    });

    const transactions = rows.map(mapTx);

    // stats should normally be based on ALL matching rows (not just current page)
    // easiest: run a light second query without pagination for stats only
    const allForStats = await Transaction.findAll({
      where,
      attributes: ["id", "kind", "amount", "status", "createdAt", "currency", "metadata", "txHash", "address", "walletId"],
      order: [["createdAt", "DESC"]],

    });
    const stats = buildStats(allForStats.map(mapTx));

    const transactionTypes = [
      { id: "all", label: "All Types" },
      { id: "deposit", label: "Deposits" },
      { id: "withdraw", label: "Withdrawals" },
      { id: "transfer", label: "Transfers" },
    ];

    const statusTypes = [
      { id: "all", label: "All Status" },
      { id: "completed", label: "Completed" },
      { id: "pending", label: "Pending" },
      { id: "failed", label: "Failed" },
    ];

    const dateRanges = [
      { id: "all", label: "All Time" },
      { id: "today", label: "Today" },
      { id: "7days", label: "Last 7 Days" },
      { id: "30days", label: "Last 30 Days" },
      { id: "90days", label: "Last 90 Days" },
    ];

    return {
      stats,
      transactions, // mapped to your UI format
      pagination: {
        total: count,
        page,
        limit,
        totalPages: Math.ceil(count / limit),
      },
      transactionTypes,
      statusTypes,
      dateRanges,
    };
  }

  async getHistory(userId) {

    const transactions = await Transaction.findAll({
      where: { userId, source: 'wallet' },
      order: [['createdAt', 'DESC']],
      include: [{
        model: Wallet, as: 'wallet', include: [{
          model: Asset, as: 'coin'
        }]
      }]

    });

    const stats = await Transaction.findOne({
      where: { userId },
      attributes: [
        [sequelize.fn("COUNT", sequelize.col("id")), "totalTransactions"],

        [
          sequelize.literal(`
        COALESCE(SUM(CASE
          WHEN kind = 'deposit' AND  source="wallet" AND status = 'completed' THEN amount
          ELSE 0
        END), 0)
      `),
          "totalDeposits",
        ],

        [
          sequelize.literal(`
        COALESCE(SUM(CASE
          WHEN kind = 'withdrawal' AND status = 'completed' THEN amount
          ELSE 0
        END), 0)
      `),
          "totalWithdrawals",
        ],

        [
          sequelize.literal(`
        COALESCE(SUM(CASE
          WHEN kind = 'deposit' AND status IN ('initiated', 'pending') THEN amount
          ELSE 0
        END), 0)
      `),
          "pendingDeposits",
        ],

        [
          sequelize.literal(`
        COALESCE(SUM(CASE
          WHEN kind = 'withdrawal' AND status IN ('initiated', 'pending') THEN amount
          ELSE 0
        END), 0)
      `),
          "pendingWithdrawals",
        ],
      ],
      raw: true,
    });

    return {
      transactions: transactions.map(t => ({
        transactionId: t.transactionId,
        type: t.kind,
        amount: t.netAmount,
        status: t.status,
        description: t.description,
        coin: t?.wallet?.coin?.symbol,
        coinName: t?.wallet?.coin?.name,
        side: t.direction,
        date: t.createdAt
      })),
      stats,
      transactionTypes: ['deposit', 'withdrawal', 'transfer_in', 'transfer_out', 'bonus', 'swap', 'staking',
        'signal_buy', 'bot_buy', 'trade_profit',
        'trade_buy', 'trade_sell', 'spot', 'buy_stock', 'sell_stock', 'fee', 'referral_bonus',
        'staking_reward', 'savings_interest', 'launchpad_purchase', 'investment', 'roi',
        'airdrop', 'adjustment'],
      statusTypes: ['initiated', 'pending', 'processing', 'completed', 'failed', 'cancelled']
    };
  }



  /**
   * Get transaction by ID
   */
  async getTransactionById(userId, transactionId) {
    const transaction = await Transaction.findOne({
      where: { id: transactionId, userId },
      include: [{ model: Wallet, as: 'wallet' }],
    });

    if (!transaction) {
      throw new NotFoundError('Transaction not found');
    }

    return transaction;
  }
}

module.exports = new WalletService();
