<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

class ReportModel extends Model
{
    public function getSalesSummary(int $tenantId, string $fromDate, string $toDate, string $groupBy = 'monthly'): array
    {
        $dateFormat = match ($groupBy) {
            'daily'   => '%Y-%m-%d',
            'weekly'  => '%x-%v',
            'monthly' => '%Y-%m',
            default   => '%Y-%m'
        };

        // Invoices
        $sql = "SELECT DATE_FORMAT(invoice_date, '$dateFormat') as period, 
                       SUM(grand_total) as inv_total, SUM(total_tax) as inv_tax
                FROM invoices 
                WHERE tenant_id = ? AND invoice_date BETWEEN ? AND ? AND status != 'cancelled'
                GROUP BY period";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $invoices = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE);

        // Credit Notes
        $sql = "SELECT DATE_FORMAT(cn_date, '$dateFormat') as period, 
                       SUM(grand_total) as cn_total, SUM(total_tax) as cn_tax
                FROM credit_notes 
                WHERE tenant_id = ? AND cn_date BETWEEN ? AND ? AND status = 'issued'
                GROUP BY period";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $cns = $stmt->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE);

        // Combine
        $allPeriods = array_unique(array_merge(array_keys($invoices), array_keys($cns)));
        sort($allPeriods);

        $results = [];
        foreach ($allPeriods as $p) {
            $inv = $invoices[$p] ?? ['inv_total' => 0, 'inv_tax' => 0];
            $cn = $cns[$p] ?? ['cn_total' => 0, 'cn_tax' => 0];
            $results[] = [
                'period' => $p,
                'total_amount' => (float)$inv['inv_total'] - (float)$cn['cn_total'],
                'tax_amount' => (float)$inv['inv_tax'] - (float)$cn['cn_tax'],
                'net_amount' => (float)$inv['inv_total'] - (float)$cn['cn_total']
            ];
        }

        return $results;
    }

    public function getPurchaseSummary(int $tenantId, string $fromDate, string $toDate, string $groupBy = 'monthly'): array
    {
        return [];
    }

    public function getProfitLoss(int $tenantId, string $fromDate, string $toDate): array
    {
        $stmt = $this->db->prepare("SELECT SUM(grand_total) FROM invoices 
                                   WHERE tenant_id = ? AND invoice_date BETWEEN ? AND ? 
                                   AND status IN ('paid', 'partial', 'sent')");
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $sales = (float)$stmt->fetchColumn();

        // Subtract Issued Credit Notes
        $stmt = $this->db->prepare("SELECT SUM(grand_total) FROM credit_notes 
                                   WHERE tenant_id = ? AND cn_date BETWEEN ? AND ? 
                                   AND status = 'issued'");
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $creditNotes = (float)$stmt->fetchColumn();
        $sales -= $creditNotes;

        // Purchases removed
        $purchases = 0.0;

        // Expenses
        $stmt = $this->db->prepare("SELECT expense_category, SUM(total_amount) as total 
                                   FROM expenses WHERE tenant_id = ? AND expense_date BETWEEN ? AND ? 
                                   GROUP BY expense_category");
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $expenseDetails = $stmt->fetchAll();
        $totalExpenses = array_sum(array_column($expenseDetails, 'total'));

        $grossProfit = $sales - $purchases;
        $netProfit = $grossProfit - $totalExpenses;

        return [
            'income' => [
                'sales' => $sales,
                'other_income' => 0,
                'total' => $sales
            ],
            'expenses' => [
                'purchases' => $purchases,
                'operational_expenses' => $totalExpenses,
                'categories' => $expenseDetails,
                'total' => $purchases + $totalExpenses
            ],
            'gross_profit' => $grossProfit,
            'net_profit' => $netProfit,
            'gross_margin_percent' => $sales > 0 ? ($grossProfit / $sales) * 100 : 0,
            'net_margin_percent' => $sales > 0 ? ($netProfit / $sales) * 100 : 0
        ];
    }

    public function getReceivablesAging(int $tenantId, string $asOfDate): array
    {
        $buckets = [
            'current' => "due_date >= ?",
            '1_30'    => "DATEDIFF(?, due_date) BETWEEN 1 AND 30",
            '31_60'   => "DATEDIFF(?, due_date) BETWEEN 31 AND 60",
            '61_90'   => "DATEDIFF(?, due_date) BETWEEN 61 AND 90",
            '90_plus' => "DATEDIFF(?, due_date) > 90"
        ];

        $results = [];
        foreach ($buckets as $key => $condition) {
            $sql = "SELECT c.name as customer, SUM(i.balance_due) as amount, 
                           COUNT(i.id) as invoice_count
                    FROM invoices i 
                    JOIN customers c ON i.customer_id = c.id
                    WHERE i.tenant_id = ? AND i.balance_due > 0 AND $condition
                    GROUP BY c.id";
            $stmt = $this->db->prepare($sql);
            // Most conditions take $asOfDate twice except 'current'
            if ($key === 'current') {
                $stmt->execute([$tenantId, $asOfDate]);
            } else {
                $stmt->execute([$tenantId, $asOfDate, $asOfDate]);
            }
            $results[$key] = $stmt->fetchAll();
        }
        return $results;
    }

    public function getPayablesAging(int $tenantId, string $asOfDate): array
    {
        return [
            'current' => [], '1_30' => [], '31_60' => [], '61_90' => [], '90_plus' => []
        ];
    }

    public function getCustomerOutstanding(int $tenantId): array
    {
        $sql = "SELECT c.name, SUM(i.balance_due) as total_due, 
                       COUNT(i.id) as invoice_count
                FROM customers c 
                JOIN invoices i ON c.id = i.customer_id 
                WHERE c.tenant_id = ? AND i.balance_due > 0 
                GROUP BY c.id ORDER BY total_due DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$tenantId]);
        return $stmt->fetchAll();
    }

    public function getVendorOutstanding(int $tenantId): array
    {
        return [];
    }

    public function getCashFlow(int $tenantId, string $fromDate, string $toDate): array
    {
        // Opening Balance (Estimated from all previous transactions)
        $stmt = $this->db->prepare("SELECT 
            (SELECT COALESCE(SUM(amount),0) FROM payments WHERE tenant_id = ? AND payment_type='received' AND payment_date < ?) -
            (SELECT COALESCE(SUM(amount),0) FROM payments WHERE tenant_id = ? AND payment_type='made' AND payment_date < ?) -
            (SELECT COALESCE(SUM(total_amount),0) FROM expenses WHERE tenant_id = ? AND expense_date < ?)
        as opening");
        $stmt->execute([$tenantId, $fromDate, $tenantId, $fromDate, $tenantId, $fromDate]);
        $opening = (float)$stmt->fetchColumn();

        // Inflows
        $stmt = $this->db->prepare("SELECT SUM(amount) FROM payments WHERE tenant_id = ? AND payment_type = 'received' AND payment_date BETWEEN ? AND ?");
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $cashIn = (float)$stmt->fetchColumn();

        // Outflows
        $stmt = $this->db->prepare("SELECT SUM(amount) FROM payments WHERE tenant_id = ? AND payment_type = 'made' AND payment_date BETWEEN ? AND ?");
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $cashOutPurchases = (float)$stmt->fetchColumn();

        $stmt = $this->db->prepare("SELECT SUM(total_amount) FROM expenses WHERE tenant_id = ? AND expense_date BETWEEN ? AND ?");
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $cashOutExpenses = (float)$stmt->fetchColumn();

        $netCashFlow = $cashIn - ($cashOutPurchases + $cashOutExpenses);

        return [
            'opening_balance' => $opening,
            'cash_inflows' => [
                'from_customers' => $cashIn,
                'other' => 0,
                'total' => $cashIn
            ],
            'cash_outflows' => [
                'to_vendors' => $cashOutPurchases,
                'expenses' => $cashOutExpenses,
                'other' => 0,
                'total' => $cashOutPurchases + $cashOutExpenses
            ],
            'net_cash_flow' => $netCashFlow,
            'closing_balance' => $opening + $netCashFlow
        ];
    }

    public function getStockSummaryReport(int $tenantId, string $asOfDate, ?int $warehouseId = null): array
    {
        $params = [$tenantId];
        $whFilter = "";
        if ($warehouseId) {
            $whFilter = " AND warehouse_id = ?";
            $params[] = $warehouseId;
        }

        $sql = "SELECT p.name, p.sku, u.abbreviation as unit,
                       (SELECT COALESCE(SUM(quantity),0) FROM stock_entries WHERE product_id = p.id AND created_at <= '$asOfDate' $whFilter) as closing_stock,
                       (SELECT COALESCE(AVG(cost_price),0) FROM stock_entries WHERE product_id = p.id AND cost_price > 0 $whFilter) as avg_cost
                FROM products p
                LEFT JOIN units u ON p.unit_id = u.id
                WHERE p.tenant_id = ? AND p.is_active = 1";
        
        $stmt = $this->db->prepare($sql);
        $queryParams = [$tenantId];
        if ($warehouseId) {
            // The subqueries both use ? so we need it twice
            array_unshift($queryParams, $warehouseId, $warehouseId);
        }
        $stmt->execute($queryParams);
        $rows = $stmt->fetchAll();

        foreach ($rows as &$row) {
            $row['stock_value'] = (float)$row['closing_stock'] * (float)$row['avg_cost'];
        }
        return $rows;
    }

    public function getExpenseSummary(int $tenantId, string $fromDate, string $toDate): array
    {
        $sql = "SELECT expense_category, SUM(total_amount) as total 
                FROM expenses WHERE tenant_id = ? AND expense_date BETWEEN ? AND ? 
                GROUP BY expense_category ORDER BY total DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        return $stmt->fetchAll();
    }

    public function getTaxSummary(int $tenantId, string $fromDate, string $toDate): array
    {
        // Output Tax (Sales) - Subtract Credit Notes
        $stmt = $this->db->prepare("SELECT SUM(cgst_amount) as cgst, SUM(sgst_amount) as sgst, SUM(igst_amount) as igst 
                                   FROM invoices WHERE tenant_id = ? AND invoice_date BETWEEN ? AND ? AND status != 'cancelled'");
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $outInv = $stmt->fetch();

        $stmt = $this->db->prepare("SELECT SUM(cgst_amount) as cgst, SUM(sgst_amount) as sgst, SUM(igst_amount) as igst 
                                   FROM credit_notes WHERE tenant_id = ? AND cn_date BETWEEN ? AND ? AND status = 'issued'");
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $outCN = $stmt->fetch();

        $output = [
            'cgst' => (float)($outInv['cgst'] ?? 0) - (float)($outCN['cgst'] ?? 0),
            'sgst' => (float)($outInv['sgst'] ?? 0) - (float)($outCN['sgst'] ?? 0),
            'igst' => (float)($outInv['igst'] ?? 0) - (float)($outCN['igst'] ?? 0)
        ];

        // Input Tax (Purchases) - Subtract Debit Notes (if any)
        $inPO = ['cgst' => 0, 'sgst' => 0, 'igst' => 0];

        $stmt = $this->db->prepare("SELECT SUM(cgst_amount) as cgst, SUM(sgst_amount) as sgst, SUM(igst_amount) as igst 
                                   FROM debit_notes WHERE tenant_id = ? AND dn_date BETWEEN ? AND ? AND status = 'issued'");
        $stmt->execute([$tenantId, $fromDate, $toDate]);
        $inDN = $stmt->fetch();

        $input = [
            'cgst' => (float)($inPO['cgst'] ?? 0) - (float)($inDN['cgst'] ?? 0),
            'sgst' => (float)($inPO['sgst'] ?? 0) - (float)($inDN['sgst'] ?? 0),
            'igst' => (float)($inPO['igst'] ?? 0) - (float)($inDN['igst'] ?? 0)
        ];

        $totalOutput = (float)$output['cgst'] + (float)$output['sgst'] + (float)$output['igst'];
        $totalInput = (float)$input['cgst'] + (float)$input['sgst'] + (float)$input['igst'];

        return [
            'output_tax' => $output,
            'input_tax' => $input,
            'total_output' => $totalOutput,
            'total_input' => $totalInput,
            'net_payable' => max(0, $totalOutput - $totalInput)
        ];
    }

    public function getDayBook(int $tenantId, string $date): array
    {
        $sql = "SELECT 'Sale' as type, invoice_number as ref, grand_total as amount, 'In' as flow FROM invoices WHERE tenant_id = ? AND invoice_date = ? AND status != 'cancelled'
                UNION ALL
                SELECT 'Expense' as type, COALESCE(reference_number, 'EXP') as ref, total_amount as amount, 'Out' as flow FROM expenses WHERE tenant_id = ? AND expense_date = ?
                UNION ALL
                SELECT 'Payment' as type, payment_number as ref, amount, IF(payment_type='received', 'In', 'Out') as flow FROM payments WHERE tenant_id = ? AND payment_date = ?
                UNION ALL
                SELECT 'Credit Note' as type, cn_number as ref, grand_total as amount, 'Out' as flow FROM credit_notes WHERE tenant_id = ? AND cn_date = ? AND status = 'issued'
                UNION ALL
                SELECT 'Debit Note' as type, dn_number as ref, grand_total as amount, 'In' as flow FROM debit_notes WHERE tenant_id = ? AND dn_date = ? AND status = 'issued'";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute([$tenantId, $date, $tenantId, $date, $tenantId, $date, $tenantId, $date, $tenantId, $date]);
        return $stmt->fetchAll();
    }

    public function getStockMovement(int $tenantId, array $filters = [], int $page = 1, int $perPage = 10): array
    {
        $offset = ($page - 1) * $perPage;
        $sql = "SELECT se.created_at as date, p.name as product, p.sku, 
                       se.quantity, se.movement_type as type, se.notes as reason,
                       w.name as warehouse, se.reference_id
                FROM stock_movements se
                JOIN products p ON se.product_id = p.id
                LEFT JOIN warehouses w ON se.warehouse_id = w.id
                WHERE p.tenant_id = :tenant_id";

        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['product_id'])) {
            $sql .= " AND se.product_id = :product_id";
            $params[':product_id'] = $filters['product_id'];
        }

        if (!empty($filters['warehouse_id'])) {
            $sql .= " AND se.warehouse_id = :warehouse_id";
            $params[':warehouse_id'] = $filters['warehouse_id'];
        }

        if (!empty($filters['type'])) {
            $sql .= " AND se.movement_type = :type";
            $params[':type'] = $filters['type'];
        }

        if (!empty($filters['from_date'])) {
            $sql .= " AND se.created_at >= :from_date";
            $params[':from_date'] = $filters['from_date'] . ' 00:00:00';
        }

        if (!empty($filters['to_date'])) {
            $sql .= " AND se.created_at <= :to_date";
            $params[':to_date'] = $filters['to_date'] . ' 23:59:59';
        }

        $sql .= " ORDER BY se.created_at DESC LIMIT :limit OFFSET :offset";
        
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':tenant_id', $tenantId, PDO::PARAM_INT);
        if (isset($params[':product_id'])) $stmt->bindValue(':product_id', $params[':product_id'], PDO::PARAM_INT);
        if (isset($params[':warehouse_id'])) $stmt->bindValue(':warehouse_id', $params[':warehouse_id'], PDO::PARAM_INT);
        if (isset($params[':type'])) $stmt->bindValue(':type', $params[':type'], PDO::PARAM_STR);
        if (isset($params[':from_date'])) $stmt->bindValue(':from_date', $params[':from_date'], PDO::PARAM_STR);
        if (isset($params[':to_date'])) $stmt->bindValue(':to_date', $params[':to_date'], PDO::PARAM_STR);
        
        $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function countStockMovements(int $tenantId, array $filters = []): int
    {
        $sql = "SELECT COUNT(*) 
                FROM stock_movements se
                JOIN products p ON se.product_id = p.id
                WHERE p.tenant_id = :tenant_id";

        $params = [':tenant_id' => $tenantId];

        if (!empty($filters['product_id'])) {
            $sql .= " AND se.product_id = :product_id";
            $params[':product_id'] = $filters['product_id'];
        }

        if (!empty($filters['warehouse_id'])) {
            $sql .= " AND se.warehouse_id = :warehouse_id";
            $params[':warehouse_id'] = $filters['warehouse_id'];
        }

        if (!empty($filters['type'])) {
            $sql .= " AND se.movement_type = :type";
            $params[':type'] = $filters['type'];
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }
}
