<?php

declare(strict_types=1);

require_once __DIR__ . '/db.php';

class InvoiceSettings
{
    public static function get(): ?array
    {
        $pdo = getPDO();
        $stmt = $pdo->query('SELECT * FROM invoice_settings ORDER BY id DESC LIMIT 1');
        $result = $stmt->fetch();
        return $result ?: null;
    }

    public static function update(array $data): bool
    {
        $pdo = getPDO();
        
        // Check if settings exist
        $stmt = $pdo->query('SELECT id FROM invoice_settings ORDER BY id DESC LIMIT 1');
        $existing = $stmt->fetch();
        
        if ($existing) {
            // Update existing
            $sql = "UPDATE invoice_settings SET 
                company_name = ?, company_legal_name = ?, tax_id = ?, license_id = ?, 
                address = ?, address_line_1 = ?, address_line_2 = ?, city = ?, state = ?, zip_code = ?, country = ?,
                phone = ?, email = ?, website = ?, company_logo_path = ?, 
                currency = ?, tax_inclusive = ?, default_payment_terms = ?, 
                bank_name = ?, bank_account_name = ?, bank_account_number = ?, 
                bank_routing_number = ?, bank_swift = ?, payment_instructions = ?
                WHERE id = ?";
            
            $stmt = $pdo->prepare($sql);
            return $stmt->execute([
                $data['company_name'],
                $data['company_legal_name'] ?? null,
                $data['tax_id'] ?? null,
                $data['license_id'] ?? null,
                $data['address'] ?? null,
                $data['address_line_1'] ?? null,
                $data['address_line_2'] ?? null,
                $data['city'] ?? null,
                $data['state'] ?? null,
                $data['zip_code'] ?? null,
                $data['country'] ?? null,
                $data['phone'] ?? null,
                $data['email'] ?? null,
                $data['website'] ?? null,
                $data['company_logo_path'] ?? null,
                $data['currency'] ?? 'USD',
                ($data['tax_inclusive'] ?? false) ? 1 : 0,
                $data['default_payment_terms'] ?? 'Net 30',
                $data['bank_name'] ?? null,
                $data['bank_account_name'] ?? null,
                $data['bank_account_number'] ?? null,
                $data['bank_routing_number'] ?? null,
                $data['bank_swift'] ?? null,
                $data['payment_instructions'] ?? null,
                $existing['id']
            ]);
        } else {
            // Insert new
            $sql = "INSERT INTO invoice_settings (
                company_name, company_legal_name, tax_id, license_id, address, address_line_1, address_line_2, city, state, zip_code, country,
                phone, email, website, company_logo_path, currency, tax_inclusive, 
                default_payment_terms, bank_name, bank_account_name, bank_account_number, 
                bank_routing_number, bank_swift, payment_instructions
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            
            $stmt = $pdo->prepare($sql);
            return $stmt->execute([
                $data['company_name'],
                $data['company_legal_name'] ?? null,
                $data['tax_id'] ?? null,
                $data['license_id'] ?? null,
                $data['address'] ?? null,
                $data['address_line_1'] ?? null,
                $data['address_line_2'] ?? null,
                $data['city'] ?? null,
                $data['state'] ?? null,
                $data['zip_code'] ?? null,
                $data['country'] ?? null,
                $data['phone'] ?? null,
                $data['email'] ?? null,
                $data['website'] ?? null,
                $data['company_logo_path'] ?? null,
                $data['currency'] ?? 'USD',
                ($data['tax_inclusive'] ?? false) ? 1 : 0,
                $data['default_payment_terms'] ?? 'Net 30',
                $data['bank_name'] ?? null,
                $data['bank_account_name'] ?? null,
                $data['bank_account_number'] ?? null,
                $data['bank_routing_number'] ?? null,
                $data['bank_swift'] ?? null,
                $data['payment_instructions'] ?? null
            ]);
        }
    }

    public static function updateLogo(string $logoPath): bool
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('UPDATE invoice_settings SET company_logo_path = ? WHERE id = (SELECT id FROM (SELECT id FROM invoice_settings ORDER BY id DESC LIMIT 1) AS temp)');
        return $stmt->execute([$logoPath]);
    }
}

class TaxBracket
{
    public static function all(): array
    {
        $pdo = getPDO();
        $stmt = $pdo->query('SELECT * FROM tax_brackets ORDER BY rate ASC');
        return $stmt->fetchAll();
    }

    public static function create(array $data): bool
    {
        $pdo = getPDO();
        $sql = "INSERT INTO tax_brackets (name, rate, description, is_compound, is_active, jurisdiction, tax_id) 
                VALUES (?, ?, ?, ?, ?, ?, ?)";
        $stmt = $pdo->prepare($sql);
        return $stmt->execute([
            $data['name'],
            $data['rate'],
            $data['description'] ?? null,
            ($data['is_compound'] ?? false) ? 1 : 0,
            ($data['is_active'] ?? true) ? 1 : 0,
            $data['jurisdiction'] ?? null,
            $data['tax_id'] ?? null
        ]);
    }

    public static function update(int $id, array $data): bool
    {
        $pdo = getPDO();
        $sql = "UPDATE tax_brackets SET 
                name = ?, rate = ?, description = ?, is_compound = ?, 
                is_active = ?, jurisdiction = ?, tax_id = ? 
                WHERE id = ?";
        $stmt = $pdo->prepare($sql);
        return $stmt->execute([
            $data['name'],
            $data['rate'],
            $data['description'] ?? null,
            ($data['is_compound'] ?? false) ? 1 : 0,
            ($data['is_active'] ?? true) ? 1 : 0,
            $data['jurisdiction'] ?? null,
            $data['tax_id'] ?? null,
            $id
        ]);
    }

    public static function isInUse(int $id): bool
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('SELECT COUNT(*) as count FROM invoice_items WHERE default_tax_bracket_id = ?');
        $stmt->execute([$id]);
        $result = $stmt->fetch();
        return $result['count'] > 0;
    }

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

    public static function find(int $id): ?array
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('SELECT * FROM tax_brackets WHERE id = ?');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public static function getActive(): array
    {
        $pdo = getPDO();
        $stmt = $pdo->query('SELECT * FROM tax_brackets WHERE is_active = 1 ORDER BY rate ASC');
        return $stmt->fetchAll();
    }
}

class InvoiceItem
{
    public static function all(): array
    {
        $pdo = getPDO();
        $stmt = $pdo->query('SELECT ii.*, tb.name as tax_bracket_name FROM invoice_items ii 
                            LEFT JOIN tax_brackets tb ON ii.default_tax_bracket_id = tb.id 
                            ORDER BY ii.name ASC');
        return $stmt->fetchAll();
    }

    public static function create(array $data): bool
    {
        $pdo = getPDO();
        $sql = "INSERT INTO invoice_items (name, description, unit_price, sku, category, is_taxable, default_tax_bracket_id) 
                VALUES (?, ?, ?, ?, ?, ?, ?)";
        $stmt = $pdo->prepare($sql);
        return $stmt->execute([
            $data['name'],
            $data['description'] ?? null,
            $data['unit_price'] ?? 0,
            $data['sku'] ?? null,
            $data['category'] ?? null,
            ($data['is_taxable'] ?? true) ? 1 : 0,
            $data['default_tax_bracket_id'] ?? null
        ]);
    }

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

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

    public static function find(int $id): ?array
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('SELECT * FROM invoice_items WHERE id = ?');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public static function search(string $term): array
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('SELECT * FROM invoice_items 
                             WHERE name LIKE ? OR description LIKE ? OR sku LIKE ? 
                             ORDER BY name ASC LIMIT 20');
        $searchTerm = "%{$term}%";
        $stmt->execute([$searchTerm, $searchTerm, $searchTerm]);
        return $stmt->fetchAll();
    }
}
