<?php
declare(strict_types=1);

namespace App\Controllers;

use App\Core\Controller;
use App\Core\Response;
use App\Config\Database;
use PDO;

class DashboardController extends Controller
{
    public function stats(): void
    {
        $tenantId = (int) $_REQUEST['tenant_id'];
        $period = $_REQUEST['period'] ?? '12m';
        $db = Database::getInstance();
        
        $monthsInPeriod = 12;
        switch($period) {
            case '30d':
                $start = date('Y-m-d', strtotime('-30 days'));
                $prevStart = date('Y-m-d', strtotime('-60 days'));
                $prevEnd = date('Y-m-d', strtotime('-31 days'));
                $monthsInPeriod = 1;
                break;
            case '3m':
                $start = date('Y-m-d', strtotime('-3 months'));
                $prevStart = date('Y-m-d', strtotime('-6 months'));
                $prevEnd = date('Y-m-d', strtotime('-3 months -1 day'));
                $monthsInPeriod = 3;
                break;
            case '6m':
                $start = date('Y-m-d', strtotime('-6 months'));
                $prevStart = date('Y-m-d', strtotime('-12 months'));
                $prevEnd = date('Y-m-d', strtotime('-6 months -1 day'));
                $monthsInPeriod = 6;
                break;
            case '12m':
            default:
                $start = date('Y-m-d', strtotime('-12 months'));
                $prevStart = date('Y-m-d', strtotime('-24 months'));
                $prevEnd = date('Y-m-d', strtotime('-12 months -1 day'));
                $monthsInPeriod = 12;
                break;
        }
        $end = date('Y-m-d');

        // 1. Total Revenue for period
        $stmt = $db->prepare("SELECT SUM(grand_total) FROM invoices WHERE tenant_id = ? AND invoice_date BETWEEN ? AND ? AND status != 'cancelled'");
        $stmt->execute([$tenantId, $start, $end]);
        $revenue = (float)$stmt->fetchColumn();

        // 2. Revenue Growth Percent
        $stmt->execute([$tenantId, $prevStart, $prevEnd]);
        $lastPeriodRevenue = (float)$stmt->fetchColumn();
        $growth = ($lastPeriodRevenue > 0) ? (($revenue - $lastPeriodRevenue) / $lastPeriodRevenue) * 100 : 0;

        // 3. Outstanding Receivables (For the Period)
        $stmt = $db->prepare("SELECT SUM(balance_due) FROM invoices WHERE tenant_id = ? AND status != 'cancelled' AND invoice_date >= ?");
        $stmt->execute([$tenantId, $start]);
        $receivables = (float)$stmt->fetchColumn();

        // 4. Invoices Overdue Count (For the Period)
        $stmt = $db->prepare("SELECT COUNT(*) FROM invoices WHERE tenant_id = ? AND status != 'cancelled' AND balance_due > 0 AND due_date < ? AND invoice_date >= ?");
        $stmt->execute([$tenantId, date('Y-m-d'), $start]);
        $overdueCount = (int)$stmt->fetchColumn();

        // 5. Total Expenses Period
        $stmt = $db->prepare("SELECT SUM(total_amount) FROM expenses WHERE tenant_id = ? AND expense_date BETWEEN ? AND ?");
        $stmt->execute([$tenantId, $start, $end]);
        $expenses = (float)$stmt->fetchColumn();

        // 6. Cash in Bank (Snapshot)
        $stmt = $db->prepare("SELECT 
            (SELECT COALESCE(SUM(amount),0) FROM payments WHERE tenant_id = ? AND payment_type='received') -
            (SELECT COALESCE(SUM(amount),0) FROM payments WHERE tenant_id = ? AND payment_type='made') -
            (SELECT COALESCE(SUM(total_amount),0) FROM expenses WHERE tenant_id = ?)
        as balance");
        $stmt->execute([$tenantId, $tenantId, $tenantId]);
        $bankBalance = (float)$stmt->fetchColumn();

        // 7. Invoices in period
        $stmt = $db->prepare("SELECT COUNT(*) FROM invoices WHERE tenant_id = ? AND invoice_date BETWEEN ? AND ?");
        $stmt->execute([$tenantId, $start, $end]);
        $invoicesCount = (int)$stmt->fetchColumn();

        // 8. Invoices paid in period
        $stmt = $db->prepare("SELECT COUNT(*) FROM invoices WHERE tenant_id = ? AND status = 'paid' AND invoice_date BETWEEN ? AND ?");
        $stmt->execute([$tenantId, $start, $end]);
        $paidCount = (int)$stmt->fetchColumn();

        Response::success([
            'total_revenue_period' => $revenue,
            'revenue_growth_percent' => round($growth, 2),
            'outstanding_receivables' => $receivables,
            'invoices_overdue_count' => $overdueCount,
            'total_expenses_period' => $expenses,
            'cash_in_bank' => $bankBalance,
            'invoices_period_count' => $invoicesCount,
            'invoices_paid_period_count' => $paidCount,
            'months_in_period' => $monthsInPeriod,
            'currency' => 'INR'
        ]);
    }

    public function revenueChart(): void
    {
        $tenantId = (int) $_REQUEST['tenant_id'];
        $period = $_REQUEST['period'] ?? '12m';
        $db = Database::getInstance();
        $data = [];

        if ($period === '30d') {
            $startDate = date('Y-m-d', strtotime('-29 days'));
            $endDate = date('Y-m-d');
            
            $stmt = $db->prepare("SELECT invoice_date as d, SUM(grand_total) as val FROM invoices WHERE tenant_id = ? AND invoice_date BETWEEN ? AND ? AND status != 'cancelled' GROUP BY invoice_date");
            $stmt->execute([$tenantId, $startDate, $endDate]);
            $revData = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

            $stmt = $db->prepare("SELECT expense_date as d, SUM(total_amount) as val FROM expenses WHERE tenant_id = ? AND expense_date BETWEEN ? AND ? GROUP BY expense_date");
            $stmt->execute([$tenantId, $startDate, $endDate]);
            $expData = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

            for ($i = 29; $i >= 0; $i--) {
                $day = date('Y-m-d', strtotime("-$i days"));
                $data[] = [
                    'month' => date('M d', strtotime($day)),
                    'revenue' => (float)($revData[$day] ?? 0),
                    'expenses' => (float)($expData[$day] ?? 0)
                ];
            }
        } else {
            $monthsToLoop = 11;
            if ($period === '6m') $monthsToLoop = 5;
            if ($period === '3m') $monthsToLoop = 2;

            $startDate = date('Y-m-01', strtotime("-$monthsToLoop months"));
            $endDate = date('Y-m-t');

            $stmt = $db->prepare("SELECT DATE_FORMAT(invoice_date, '%Y-%m') as m, SUM(grand_total) as val FROM invoices WHERE tenant_id = ? AND invoice_date BETWEEN ? AND ? AND status != 'cancelled' GROUP BY m");
            $stmt->execute([$tenantId, $startDate, $endDate]);
            $revData = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

            $stmt = $db->prepare("SELECT DATE_FORMAT(expense_date, '%Y-%m') as m, SUM(total_amount) as val FROM expenses WHERE tenant_id = ? AND expense_date BETWEEN ? AND ? GROUP BY m");
            $stmt->execute([$tenantId, $startDate, $endDate]);
            $expData = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);

            for ($i = $monthsToLoop; $i >= 0; $i--) {
                $monthStr = date('Y-m', strtotime("-$i months"));
                $data[] = [
                    'month' => date('M Y', strtotime("$monthStr-01")),
                    'revenue' => (float)($revData[$monthStr] ?? 0),
                    'expenses' => (float)($expData[$monthStr] ?? 0)
                ];
            }
        }

        Response::success($data);
    }

    public function invoiceStatus(): void
    {
        $tenantId = (int) $_REQUEST['tenant_id'];
        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT status, COUNT(*) as count FROM invoices WHERE tenant_id = ? GROUP BY status");
        $stmt->execute([$tenantId]);
        $rows = $stmt->fetchAll();
        
        $stats = ['draft' => 0, 'sent' => 0, 'paid' => 0, 'overdue' => 0, 'partial' => 0];
        foreach($rows as $row) {
            $stats[$row['status']] = (int)$row['count'];
        }
        
        Response::success($stats);
    }

    public function topCustomers(): void
    {
        $tenantId = (int) $_REQUEST['tenant_id'];
        $yearStart = date('Y-01-01');
        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT c.name, SUM(i.grand_total) as total_revenue 
                             FROM invoices i JOIN customers c ON i.customer_id = c.id 
                             WHERE i.tenant_id = ? AND i.invoice_date >= ? AND i.status != 'cancelled'
                             GROUP BY c.id ORDER BY total_revenue DESC LIMIT 5");
        $stmt->execute([$tenantId, $yearStart]);
        Response::success($stmt->fetchAll());
    }

    public function recentInvoices(): void
    {
        $tenantId = (int) $_REQUEST['tenant_id'];
        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT i.id, i.invoice_number, c.name as customer, i.grand_total, i.status, i.invoice_date 
                             FROM invoices i JOIN customers c ON i.customer_id = c.id 
                             WHERE i.tenant_id = ? ORDER BY i.created_at DESC LIMIT 10");
        $stmt->execute([$tenantId]);
        Response::success($stmt->fetchAll());
    }

    public function lowStock(): void
    {
        $tenantId = (int) $_REQUEST['tenant_id'];
        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT p.name, p.sku, 
                             (SELECT SUM(quantity) FROM stock_entries WHERE product_id = p.id) as current_stock,
                             p.reorder_level
                             FROM products p 
                             WHERE p.tenant_id = ? AND p.track_inventory = 1 
                             HAVING current_stock <= p.reorder_level");
        $stmt->execute([$tenantId]);
        Response::success($stmt->fetchAll());
    }

    public function upcomingDues(): void
    {
        $tenantId = (int) $_REQUEST['tenant_id'];
        $today = date('Y-m-d');
        $next7Days = date('Y-m-d', strtotime('+7 days'));
        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT i.invoice_number, c.name as customer, i.balance_due, i.due_date 
                             FROM invoices i JOIN customers c ON i.customer_id = c.id 
                             WHERE i.tenant_id = ? AND i.balance_due > 0 AND i.due_date BETWEEN ? AND ?
                             ORDER BY i.due_date ASC");
        $stmt->execute([$tenantId, $today, $next7Days]);
        Response::success($stmt->fetchAll());
    }

    public function receivableAging(): void
    {
        $tenantId = (int) $_REQUEST['tenant_id'];
        $period = $_REQUEST['period'] ?? '12m';
        $db = Database::getInstance();
        $today = date('Y-m-d');

        switch($period) {
            case '30d': $start = date('Y-m-d', strtotime('-30 days')); break;
            case '3m':  $start = date('Y-m-d', strtotime('-3 months')); break;
            case '6m':  $start = date('Y-m-d', strtotime('-6 months')); break;
            case '12m':
            default:    $start = date('Y-m-d', strtotime('-12 months')); break;
        }

        $query = "SELECT 
            SUM(CASE WHEN due_date >= ? THEN balance_due ELSE 0 END) as current,
            SUM(CASE WHEN due_date < ? AND due_date >= DATE_SUB(?, INTERVAL 15 DAY) THEN balance_due ELSE 0 END) as age_1_15,
            SUM(CASE WHEN due_date < DATE_SUB(?, INTERVAL 15 DAY) AND due_date >= DATE_SUB(?, INTERVAL 30 DAY) THEN balance_due ELSE 0 END) as age_16_30,
            SUM(CASE WHEN due_date < DATE_SUB(?, INTERVAL 30 DAY) AND due_date >= DATE_SUB(?, INTERVAL 45 DAY) THEN balance_due ELSE 0 END) as age_31_45,
            SUM(CASE WHEN due_date < DATE_SUB(?, INTERVAL 45 DAY) THEN balance_due ELSE 0 END) as age_45_plus
            FROM invoices WHERE tenant_id = ? AND balance_due > 0 AND status != 'cancelled' AND invoice_date >= ?";
        
        $stmt = $db->prepare($query);
        $stmt->execute([$today, $today, $today, $today, $today, $today, $today, $today, $tenantId, $start]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        // Sanitize output for frontend
        $result = [
            'current'   => (float)($result['current'] ?? 0),
            'age_1_15'  => (float)($result['age_1_15'] ?? 0),
            'age_16_30' => (float)($result['age_16_30'] ?? 0),
            'age_31_45' => (float)($result['age_31_45'] ?? 0),
            'age_45_plus' =>(float)($result['age_45_plus'] ?? 0)
        ];

        Response::success($result);
    }
}
