<?php
namespace App\Models;

use App\Core\Model;
use PDO;

class Dashboard extends Model
{
    /**
     * Get client balance trend for the last N days
     * Note: For accurate trend, you would need daily balance snapshots.
     * This simplified version uses current balance only.
     * @param int $days Number of days
     * @return array
     */
    public function getClientBalanceTrend($days = 7)
    {
        // Since we don't have historical snapshots, we'll return placeholder data
        // In production, you'd join with a `client_balance_history` table.
        $sql = "SELECT CURDATE() as date, SUM(balance) as total FROM clients
                UNION
                SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY), SUM(balance) FROM clients
                UNION
                SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY), SUM(balance) FROM clients
                UNION
                SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY), SUM(balance) FROM clients
                UNION
                SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY), SUM(balance) FROM clients
                UNION
                SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY), SUM(balance) FROM clients
                UNION
                SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY), SUM(balance) FROM clients
                ORDER BY date";
        $stmt = $this->query($sql);
        return $stmt->fetchAll();
    }

    /**
     * Get current forex positions with KES equivalent
     * @return array
     */
    public function getForexPositions()
    {
        $sql = "SELECT c.code, ch.closing_balance, ch.average_cost 
                FROM currency_holdings ch
                JOIN currencies c ON ch.currency_id = c.id
                WHERE ch.holding_date = CURDATE() AND ch.closing_balance > 0";
        $stmt = $this->query($sql);
        return $stmt->fetchAll();
    }

    /**
     * Get monthly rent collection for last N months
     * @param int $months Number of months
     * @return array
     */
    public function getRentCollectionMonthly($months = 6)
    {
        $sql = "SELECT DATE_FORMAT(payment_date, '%Y-%m') as month, SUM(amount) as total
                FROM rent_payments
                WHERE payment_date >= DATE_SUB(CURDATE(), INTERVAL ? MONTH)
                GROUP BY DATE_FORMAT(payment_date, '%Y-%m')
                ORDER BY month";
        $stmt = $this->query($sql, [$months]);
        return $stmt->fetchAll();
    }

    /**
     * Get total client balance
     * @return float
     */
    public function getTotalClientBalance()
    {
        $stmt = $this->query("SELECT SUM(balance) FROM clients");
        return $stmt->fetchColumn() ?: 0;
    }

    /**
     * Get today's forex trading volume
     * @return float
     */
    public function getTodayForexVolume()
    {
        $stmt = $this->query("SELECT SUM(kes_amount) FROM forex_trades WHERE DATE(trade_date) = CURDATE()");
        return $stmt->fetchColumn() ?: 0;
    }

    /**
     * Get rent collected this month
     * @return float
     */
    public function getMonthlyRentCollected()
    {
        $stmt = $this->query("SELECT SUM(amount) FROM rent_payments WHERE DATE_FORMAT(payment_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')");
        return $stmt->fetchColumn() ?: 0;
    }

    /**
     * Get total number of clients
     * @return int
     */
    public function getTotalClients()
    {
        $stmt = $this->query("SELECT COUNT(*) FROM clients");
        return $stmt->fetchColumn();
    }
}