<?php

declare(strict_types=1);

require_once __DIR__ . '/db.php';
require_once __DIR__ . '/InvoiceModels.php';

class Invoice
{
    public static function all(): array
    {
        $pdo = getPDO();
        $stmt = $pdo->query('SELECT i.*, c.name as client_name, c.company as client_company 
                            FROM invoices i 
                            LEFT JOIN clients c ON i.client_id = c.id 
                            ORDER BY i.created_at DESC');
        return $stmt->fetchAll();
    }

    public static function find(int $id): ?array
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('SELECT i.*, c.name as client_name, c.company as client_company, 
                              c.email as client_email, c.phone as client_phone, c.address as client_address,
                              c.display_name_option as client_display_name_option, 
                              c.custom_display_name as client_custom_display_name
                              FROM invoices i 
                              LEFT JOIN clients c ON i.client_id = c.id 
                              WHERE i.id = ?');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public static function create(array $data): int
    {
        $pdo = getPDO();
        
        // Get company ID from data or context
        $companyId = $data['company_id'] ?? null;
        if (!$companyId) {
            $companyId = $_SESSION['company_id'] ?? $_GET['company_id'] ?? null;
            if (!$companyId) {
                // Try to get from workspace
                $workspace = $_GET['workspace'] ?? $_SESSION['workspace'] ?? null;
                if ($workspace) {
                    $stmt = $pdo->prepare('SELECT id FROM companies WHERE username = ?');
                    $stmt->execute([$workspace]);
                    $company = $stmt->fetch();
                    $companyId = $company['id'] ?? null;
                }
            }
        }
        
        // Generate invoice number with company context
        $invoiceNumber = self::generateInvoiceNumber($companyId);
        
        $sql = "INSERT INTO invoices (
            company_id, invoice_number, client_id, proposal_id, contract_id, issue_date, due_date,
            status, subtotal, tax_total, total, currency, payment_terms, notes,
            company_name, company_address, tax_exempt, tax_exemption_reason, invoice_level_tax_bracket_id
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            $companyId,
            $invoiceNumber,
            $data['client_id'],
            $data['proposal_id'] ?? null,
            $data['contract_id'] ?? null,
            $data['issue_date'],
            $data['due_date'],
            $data['status'] ?? 'draft',
            $data['subtotal'] ?? 0,
            $data['tax_total'] ?? 0,
            $data['total'] ?? 0,
            $data['currency'] ?? 'USD',
            $data['payment_terms'] ?? null,
            $data['notes'] ?? null,
            $data['company_name'] ?? null,
            $data['company_address'] ?? null,
            ($data['tax_exempt'] ?? false) ? 1 : 0,
            $data['tax_exemption_reason'] ?? null,
            $data['invoice_level_tax_bracket_id'] ?? null
        ]);
        
        return (int) $pdo->lastInsertId();
    }

    public static function update(int $id, array $data): bool
    {
        $pdo = getPDO();
        
        $sql = "UPDATE invoices SET 
                client_id = ?, proposal_id = ?, contract_id = ?, invoice_date = ?, due_date = ?,
                status = ?, subtotal = ?, tax_total = ?, total = ?, currency = ?, 
                payment_terms = ?, notes = ?, company_name = ?, company_address = ?,
                tax_exempt = ?, tax_exemption_reason = ?
                WHERE id = ?";
        
        $stmt = $pdo->prepare($sql);
        return $stmt->execute([
            $data['client_id'],
            $data['proposal_id'] ?? null,
            $data['contract_id'] ?? null,
            $data['issue_date'],
            $data['due_date'],
            $data['status'] ?? 'draft',
            $data['subtotal'] ?? 0,
            $data['tax_total'] ?? 0,
            $data['total'] ?? 0,
            $data['currency'] ?? 'USD',
            $data['payment_terms'] ?? null,
            $data['notes'] ?? null,
            $data['company_name'] ?? null,
            $data['company_address'] ?? null,
            ($data['tax_exempt'] ?? false) ? 1 : 0,
            $data['tax_exemption_reason'] ?? null,
            $id
        ]);
    }

    public static function delete(int $id): bool
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('DELETE FROM invoices WHERE id = ?');
        return $stmt->execute([$id]);
    }

    public static function getInvoiceItems(int $invoiceId): array
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('SELECT iil.*, tb.name as tax_bracket_name, tb.rate as tax_rate
                              FROM invoice_items iil 
                              LEFT JOIN tax_brackets tb ON iil.default_tax_bracket_id = tb.id 
                              WHERE iil.invoice_id = ? 
                              ORDER BY iil.sort_order ASC, iil.id ASC');
        $stmt->execute([$invoiceId]);
        return $stmt->fetchAll();
    }

    public static function addInvoiceItem(int $invoiceId, array $data): bool
    {
        $pdo = getPDO();
        
        $sql = "INSERT INTO invoice_items (
            invoice_id, company_id, description, sku, is_taxable, quantity, unit_price, 
            tax_rate, default_tax_bracket_id, total, sort_order
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        $stmt = $pdo->prepare($sql);
        return $stmt->execute([
            $invoiceId,
            $data['company_id'] ?? null,
            $data['description'],
            $data['sku'] ?? null,
            $data['is_taxable'] ?? 1,
            $data['quantity'],
            $data['unit_price'],
            $data['tax_rate'] ?? 0,
            $data['default_tax_bracket_id'] ?? null,
            $data['total'],
            $data['sort_order'] ?? 0
        ]);
    }

    public static function updateInvoiceItem(int $id, array $data): bool
    {
        $pdo = getPDO();
        
        $sql = "UPDATE invoice_items SET 
                description = ?, sku = ?, unit_price = ?, tax_rate = ?, 
                default_tax_bracket_id = ?, total = ?, sort_order = ?
                WHERE id = ?";
        
        $stmt = $pdo->prepare($sql);
        return $stmt->execute([
            $data['description'],
            $data['sku'] ?? null,
            $data['unit_price'],
            $data['tax_rate'] ?? 0,
            $data['default_tax_bracket_id'] ?? null,
            $data['total'],
            $data['sort_order'] ?? 0,
            $id
        ]);
    }

    public static function deleteInvoiceItem(int $id): bool
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('DELETE FROM invoice_items WHERE id = ?');
        return $stmt->execute([$id]);
    }

    public static function calculateInvoiceTotals(int $invoiceId): array
    {
        $pdo = getPDO();
        
        // Get invoice details
        $stmt = $pdo->prepare('SELECT * FROM invoices WHERE id = ?');
        $stmt->execute([$invoiceId]);
        $invoice = $stmt->fetch();
        
        if (!$invoice) {
            return ['subtotal' => 0, 'tax_total' => 0, 'total' => 0];
        }
        
        // Get invoice items
        $items = self::getInvoiceItems($invoiceId);
        
        $subtotal = 0;
        $taxTotal = 0;
        $taxCalculations = [];
        
        // Calculate line totals and taxes
        foreach ($items as $item) {
            $lineSubtotal = $item['quantity'] * $item['unit_price'];
            $discount = $lineSubtotal * ($item['discount_percent'] / 100);
            $taxableAmount = $lineSubtotal - $discount;
            
            $itemTax = 0;
            
            // Calculate tax if taxable and not tax exempt
            if (!$invoice['tax_exempt'] && $item['tax_bracket_id'] && $item['tax_rate']) {
                $taxBracket = TaxBracket::find($item['tax_bracket_id']);
                if ($taxBracket && $taxBracket['is_active']) {
                    if ($taxBracket['is_compound']) {
                        // Compound tax - tax on top of other taxes
                        $itemTax = $taxableAmount * $item['tax_rate'];
                    } else {
                        // Standard tax
                        $itemTax = $taxableAmount * $item['tax_rate'];
                    }
                    
                    // Track tax by bracket
                    $bracketId = $item['tax_bracket_id'];
                    if (!isset($taxCalculations[$bracketId])) {
                        $taxCalculations[$bracketId] = [
                            'tax_bracket_id' => $bracketId,
                            'taxable_amount' => 0,
                            'tax_amount' => 0,
                            'is_compound' => $taxBracket['is_compound']
                        ];
                    }
                    $taxCalculations[$bracketId]['taxable_amount'] += $taxableAmount;
                    $taxCalculations[$bracketId]['tax_amount'] += $itemTax;
                }
            }
            
            $lineTotal = $taxableAmount + $itemTax;
            
            // Update line item totals
            self::updateInvoiceItem($item['id'], [
                'description' => $item['description'],
                'quantity' => $item['quantity'],
                'unit_price' => $item['unit_price'],
                'discount_percent' => $item['discount_percent'],
                'tax_bracket_id' => $item['tax_bracket_id'],
                'line_total' => $lineTotal,
                'tax_amount' => $itemTax
            ]);
            
            $subtotal += $taxableAmount;
            $taxTotal += $itemTax;
        }
        
        $total = $subtotal + $taxTotal;
        
        // Update invoice totals
        self::update($invoiceId, [
            'client_id' => $invoice['client_id'],
            'proposal_id' => $invoice['proposal_id'],
            'contract_id' => $invoice['contract_id'],
            'issue_date' => $invoice['issue_date'],
            'due_date' => $invoice['due_date'],
            'status' => $invoice['status'],
            'subtotal' => $subtotal,
            'tax_total' => $taxTotal,
            'total' => $total,
            'currency' => $invoice['currency'],
            'payment_terms' => $invoice['payment_terms'],
            'notes' => $invoice['notes'],
            'company_name' => $invoice['company_name'],
            'company_address' => $invoice['company_address'],
            'tax_exempt' => $invoice['tax_exempt'],
            'tax_exemption_reason' => $invoice['tax_exemption_reason']
        ]);
        
        // Update tax calculations
        self::updateTaxCalculations($invoiceId, $taxCalculations);
        
        return [
            'subtotal' => $subtotal,
            'tax_total' => $taxTotal,
            'total' => $total
        ];
    }

    public static function updateTaxCalculations(int $invoiceId, array $taxCalculations): void
    {
        $pdo = getPDO();
        
        // Delete existing tax calculations
        $stmt = $pdo->prepare('DELETE FROM invoice_taxes WHERE invoice_id = ?');
        $stmt->execute([$invoiceId]);
        
        // Insert new tax calculations
        foreach ($taxCalculations as $calc) {
            $sql = "INSERT INTO invoice_taxes (invoice_id, tax_bracket_id, taxable_amount, tax_amount, is_compound) 
                    VALUES (?, ?, ?, ?, ?)";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([
                $invoiceId,
                $calc['tax_bracket_id'],
                $calc['taxable_amount'],
                $calc['tax_amount'],
                $calc['is_compound'] ? 1 : 0
            ]);
        }
    }

    public static function generateInvoiceNumber($companyId = null): string
    {
        $pdo = getPDO();
        
        // Get current year and month
        $year = date('Y');
        $month = date('m');
        
        // Use provided company ID or get from context
        if (!$companyId) {
            $companyId = $_SESSION['company_id'] ?? $_GET['company_id'] ?? null;
            if (!$companyId) {
                // Try to get from workspace
                $workspace = $_GET['workspace'] ?? $_SESSION['workspace'] ?? null;
                if ($workspace) {
                    $stmt = $pdo->prepare('SELECT id FROM companies WHERE username = ?');
                    $stmt->execute([$workspace]);
                    $company = $stmt->fetch();
                    $companyId = $company['id'] ?? null;
                }
            }
        }
        
        if (!$companyId) {
            // Fallback to global counting if no company context
            $stmt = $pdo->prepare('SELECT COUNT(*) as count FROM invoices WHERE YEAR(created_at) = ? AND MONTH(created_at) = ?');
            $stmt->execute([$year, $month]);
            $result = $stmt->fetch();
            $nextNumber = ($result['count'] ?? 0) + 1;
        } else {
            // Per-company counting
            $stmt = $pdo->prepare('SELECT COUNT(*) as count FROM invoices WHERE company_id = ? AND YEAR(created_at) = ? AND MONTH(created_at) = ?');
            $stmt->execute([$companyId, $year, $month]);
            $result = $stmt->fetch();
            $nextNumber = ($result['count'] ?? 0) + 1;
        }
        
        // Format: INV-2025-12-001 (resets to 001 each month for each company)
        return sprintf('INV-%d-%02d-%03d', $year, $month, $nextNumber);
    }

    public static function updateStatus(int $id, string $status): bool
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('UPDATE invoices SET status = ? WHERE id = ?');
        return $stmt->execute([$status, $id]);
    }

    public static function addPayment(int $invoiceId, array $paymentData): bool
    {
        $pdo = getPDO();
        
        $sql = "INSERT INTO invoice_payments (invoice_id, amount, payment_date, payment_method, reference_number, notes) 
                VALUES (?, ?, ?, ?, ?, ?)";
        
        $stmt = $pdo->prepare($sql);
        $result = $stmt->execute([
            $invoiceId,
            $paymentData['amount'],
            $paymentData['payment_date'],
            $paymentData['payment_method'] ?? null,
            $paymentData['reference_number'] ?? null,
            $paymentData['notes'] ?? null
        ]);
        
        if ($result) {
            // Update paid amount
            $stmt = $pdo->prepare('SELECT SUM(amount) as total_paid FROM invoice_payments WHERE invoice_id = ?');
            $stmt->execute([$invoiceId]);
            $paidResult = $stmt->fetch();
            
            $paidAmount = $paidResult['total_paid'] ?? 0;
            
            // Update invoice status based on payment
            $stmt = $pdo->prepare('SELECT total FROM invoices WHERE id = ?');
            $stmt->execute([$invoiceId]);
            $invoice = $stmt->fetch();
            
            if ($invoice) {
                $status = 'sent';
                if ($paidAmount >= $invoice['total']) {
                    $status = 'paid';
                } elseif ($paidAmount > 0) {
                    $status = 'sent'; // Partial payment
                }
                
                $stmt = $pdo->prepare('UPDATE invoices SET paid_amount = ?, status = ? WHERE id = ?');
                $stmt->execute([$paidAmount, $status, $invoiceId]);
            }
        }
        
        return $result;
    }

    public static function getPayments(int $invoiceId): array
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('SELECT * FROM invoice_payments WHERE invoice_id = ? ORDER BY payment_date DESC');
        $stmt->execute([$invoiceId]);
        return $stmt->fetchAll();
    }

    public static function search(string $term): array
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('SELECT i.*, c.name as client_name, c.company as client_company 
                              FROM invoices i 
                              LEFT JOIN clients c ON i.client_id = c.id 
                              WHERE i.invoice_number LIKE ? OR c.name LIKE ? OR c.company LIKE ? 
                              ORDER BY i.created_at DESC LIMIT 50');
        $searchTerm = "%{$term}%";
        $stmt->execute([$searchTerm, $searchTerm, $searchTerm]);
        return $stmt->fetchAll();
    }
}
