<?php

/**
 * Multi-Tenant Client Model
 */
class Client {
    
    private static function generateClientNumber(int $companyId): string
    {
        $pdo = getPDO();
        $stmt = $pdo->prepare('SELECT COUNT(*) as count FROM clients WHERE company_id = ?');
        $stmt->execute([$companyId]);
        $count = (int) $stmt->fetch()['count'];
        
        return 'CLI-' . ($count + 1);
    }
    
    /**
     * Get all clients for current company
     */
    public static function all(): array
    {
        // Get workspace from URL parameter or session
        $workspace = $_GET['workspace'] ?? $_SESSION['workspace'] ?? null;
        if (!$workspace || is_array($workspace)) {
            return [];
        }

        $pdo = getPDO();
        
        // Get company from workspace
        $stmt = $pdo->prepare('SELECT * FROM companies WHERE username = ?');
        $stmt->execute([$workspace]);
        $company = $stmt->fetch();
        
        if (!$company) {
            return [];
        }
        
        $stmt = $pdo->prepare('SELECT * FROM clients WHERE company_id = ? ORDER BY created_at DESC');
        $stmt->execute([$company['id']]);
        
        return $stmt->fetchAll();
    }
    
    /**
     * Find client by ID (with tenant scoping)
     */
    public static function find(int $id): ?array
    {
        // Get workspace from URL parameter or session
        $workspace = $_GET['workspace'] ?? $_SESSION['workspace'] ?? null;
        if (!$workspace || is_array($workspace)) {
            return null;
        }

        $pdo = getPDO();
        
        // Get company from workspace
        $stmt = $pdo->prepare('SELECT * FROM companies WHERE username = ?');
        $stmt->execute([$workspace]);
        $company = $stmt->fetch();
        
        if (!$company) {
            return null;
        }
        
        $stmt = $pdo->prepare('SELECT * FROM clients WHERE id = ? AND company_id = ?');
        $stmt->execute([$id, $company['id']]);
        
        return $stmt->fetch() ?: null;
    }
    
    /**
     * Create new client
     */
    public static function create(array $data): int
    {
        $companyId = TenantDB::getTenant();
        if (!$companyId) {
            throw new Exception('No company context available');
        }
        
        // Check if user can add more clients based on plan limits
        if (!TenantManager::canPerformAction('clients', 1)) {
            throw new Exception('You have reached the client limit for your plan. Upgrade to add more clients.');
        }
        
        $pdo = getPDO();
        $clientNumber = self::generateClientNumber($companyId);
        $stmt = $pdo->prepare('
            INSERT INTO clients (company_id, client_number, name, company, email, phone, address, notes, 
                               display_name_option, custom_display_name, created_at) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
        ');
        
        $result = $stmt->execute([
            $companyId,
            $clientNumber,
            $data['name'],
            $data['company'] ?? null,
            $data['email'] ?? null,
            $data['phone'] ?? null,
            $data['address'] ?? null,
            $data['notes'] ?? null,
            $data['display_name_option'] ?? 'client_name',
            $data['custom_display_name'] ?? null
        ]);
        
        if ($result) {
            // Track usage
            TenantManager::trackUsage('clients', 1);
            return (int) $pdo->lastInsertId();
        }
        
        throw new Exception('Failed to create client');
    }
    
    /**
     * Update client
     */
    public static function update(int $id, array $data): bool
    {
        $companyId = TenantManager::getCurrentCompanyId();
        if (!$companyId) {
            throw new Exception('No company context available');
        }
        
        $pdo = getPDO();
        $stmt = $pdo->prepare('
            UPDATE clients SET 
                name = ?, company = ?, email = ?, phone = ?, address = ?, notes = ?, 
                display_name_option = ?, custom_display_name = ?, updated_at = NOW()
            WHERE id = ? AND company_id = ?
        ');
        
        return $stmt->execute([
            $data['name'],
            $data['company'] ?? null,
            $data['email'] ?? null,
            $data['phone'] ?? null,
            $data['address'] ?? null,
            $data['notes'] ?? null,
            $data['display_name_option'] ?? 'client_name',
            $data['custom_display_name'] ?? null,
            $id,
            $companyId
        ]);
    }
    
    /**
     * Delete client
     */
    public static function delete(int $id): bool
    {
        $companyId = TenantManager::getCurrentCompanyId();
        if (!$companyId) {
            throw new Exception('No company context available');
        }
        
        // Check if client has associated proposals or invoices
        if (self::hasAssociatedDocuments($id)) {
            throw new Exception('Cannot delete client with associated proposals or invoices');
        }
        
        $pdo = getPDO();
        $stmt = $pdo->prepare('DELETE FROM clients WHERE id = ? AND company_id = ?');
        $result = $stmt->execute([$id, $companyId]);
        
        if ($result) {
            // Update usage tracking
            TenantManager::trackUsage('clients', -1);
        }
        
        return $result;
    }
    
    /**
     * Search clients
     */
    public static function search(string $term): array
    {
        $companyId = TenantManager::getCurrentCompanyId();
        if (!$companyId) {
            throw new Exception('No company context available');
        }
        
        $pdo = getPDO();
        $stmt = $pdo->prepare('
            SELECT * FROM clients 
            WHERE company_id = ? AND (
                name LIKE ? OR 
                company LIKE ? OR 
                email LIKE ? OR 
                phone LIKE ?
            )
            ORDER BY name ASC 
            LIMIT 20
        ');
        
        $searchTerm = "%{$term}%";
        $stmt->execute([
            $companyId, 
            $searchTerm, $searchTerm, $searchTerm, $searchTerm
        ]);
        
        return $stmt->fetchAll();
    }
    
    /**
     * Get client statistics
     */
    public static function getStatistics(): array
    {
        $companyId = TenantManager::getCurrentCompanyId();
        if (!$companyId) {
            throw new Exception('No company context available');
        }
        
        $pdo = getPDO();
        
        // Total clients
        $stmt = $pdo->prepare('SELECT COUNT(*) as total FROM clients WHERE company_id = ?');
        $stmt->execute([$companyId]);
        $total = $stmt->fetch()['total'];
        
        // Clients added this month
        $stmt = $pdo->prepare('
            SELECT COUNT(*) as this_month 
            FROM clients 
            WHERE company_id = ? AND MONTH(created_at) = MONTH(CURRENT_DATE) 
            AND YEAR(created_at) = YEAR(CURRENT_DATE)
        ');
        $stmt->execute([$companyId]);
        $thisMonth = $stmt->fetch()['this_month'];
        
        // Clients with companies
        $stmt = $pdo->prepare('
            SELECT COUNT(*) as with_company 
            FROM clients 
            WHERE company_id = ? AND company IS NOT NULL AND company != ""
        ');
        $stmt->execute([$companyId]);
        $withCompany = $stmt->fetch()['with_company'];
        
        return [
            'total' => $total,
            'this_month' => $thisMonth,
            'with_company' => $withCompany,
            'individuals' => $total - $withCompany
        ];
    }
    
    /**
     * Check if client has associated documents
     */
    private static function hasAssociatedDocuments(int $clientId): bool
    {
        $companyId = TenantManager::getCurrentCompanyId();
        $pdo = getPDO();
        
        // Check proposals
        $stmt = $pdo->prepare('SELECT COUNT(*) as count FROM proposals WHERE client_id = ? AND company_id = ?');
        $stmt->execute([$clientId, $companyId]);
        if ($stmt->fetch()['count'] > 0) return true;
        
        // Check invoices
        $stmt = $pdo->prepare('SELECT COUNT(*) as count FROM invoices WHERE client_id = ? AND company_id = ?');
        $stmt->execute([$clientId, $companyId]);
        if ($stmt->fetch()['count'] > 0) return true;
        
        // Check contracts
        $stmt = $pdo->prepare('SELECT COUNT(*) as count FROM contracts WHERE client_id = ? AND company_id = ?');
        $stmt->execute([$clientId, $companyId]);
        if ($stmt->fetch()['count'] > 0) return true;
        
        return false;
    }
    
    /**
     * Get recent clients for dashboard
     */
    public static function getRecent(int $limit = 5): array
    {
        $companyId = TenantManager::getCurrentCompanyId();
        if (!$companyId) {
            throw new Exception('No company context available');
        }
        
        $pdo = getPDO();
        $stmt = $pdo->prepare('
            SELECT * FROM clients 
            WHERE company_id = ? 
            ORDER BY created_at DESC 
            LIMIT ?
        ');
        $stmt->execute([$companyId, $limit]);
        
        return $stmt->fetchAll();
    }
    
    /**
     * Get clients with outstanding invoices
     */
    public static function getWithOutstandingInvoices(): array
    {
        $companyId = TenantManager::getCurrentCompanyId();
        if (!$companyId) {
            throw new Exception('No company context available');
        }
        
        $pdo = getPDO();
        $stmt = $pdo->prepare('
            SELECT DISTINCT c.*, 
                   (SELECT SUM(total - paid_amount) 
                    FROM invoices i 
                    WHERE i.client_id = c.id AND i.company_id = ? AND i.status != \'paid\'
                   ) as outstanding_amount
            FROM clients c
            WHERE c.company_id = ? AND EXISTS (
                SELECT 1 FROM invoices i 
                WHERE i.client_id = c.id AND i.company_id = ? AND i.status != \'paid\'
            )
            ORDER BY outstanding_amount DESC
        ');
        
        $stmt->execute([$companyId, $companyId, $companyId]);
        return $stmt->fetchAll();
    }
}
