<?php
namespace App\Models;

use App\Core\Model;
use PDO;

class ForexTrade extends Model
{
    /**
     * Get all trades with optional limit
     * @param int $limit
     * @return array
     */
    public function getAll($limit = 100)
    {
        $sql = "SELECT ft.*, c.code as currency_code 
                FROM forex_trades ft
                JOIN currencies c ON ft.currency_id = c.id
                ORDER BY ft.trade_date DESC 
                LIMIT :limit";
        
        $stmt = self::getPDO()->prepare($sql);
        $stmt->bindValue(':limit', (int)$limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function create($data)
    {
        $pdo = self::getPDO();
        $pdo->beginTransaction();
        try {
            $tradeId = $this->insert('forex_trades', $data);

            // Update currency holdings
            $holding = new CurrencyHolding();
            $holding->updateAfterTrade($data['currency_id'], $data['trade_type'], $data['amount'], $data['rate']);

            $pdo->commit();
            return $tradeId;
        } catch (\Exception $e) {
            $pdo->rollBack();
            throw $e;
        }
    }

    public function getVolumeForDate($date)
    {
        $stmt = $this->query(
            "SELECT SUM(kes_amount) FROM forex_trades WHERE DATE(trade_date) = ?",
            [$date]
        );
        return $stmt->fetchColumn() ?: 0;
    }

    public function getRealisedProfit($currencyId = null, $fromDate = null, $toDate = null)
    {
        $sql = "SELECT ft.*, c.code 
                FROM forex_trades ft
                JOIN currencies c ON ft.currency_id = c.id
                WHERE ft.trade_type = 'sell'";
        $params = [];
        if ($currencyId) {
            $sql .= " AND ft.currency_id = ?";
            $params[] = $currencyId;
        }
        if ($fromDate) {
            $sql .= " AND ft.trade_date >= ?";
            $params[] = $fromDate;
        }
        if ($toDate) {
            $sql .= " AND ft.trade_date <= ?";
            $params[] = $toDate;
        }
        $stmt = $this->query($sql, $params);
        $trades = $stmt->fetchAll();

        $profit = 0;
        foreach ($trades as $trade) {
            $holding = (new CurrencyHolding())->getForDate($trade['currency_id'], date('Y-m-d', strtotime($trade['trade_date'])));
            $avgCost = $holding['average_cost'] ?? 0;
            $profit += $trade['amount'] * ($trade['rate'] - $avgCost);
        }
        return $profit;
    }
}