<?php
/**
 * Invoice Helper Functions
 * Shared between admin and client portals
 * Location: /clients/invoice-helpers.php
 * 
 * No GST/tax line items. All amounts are inclusive of taxes.
 * Payment terms: Net 30
 */

/**
 * Calculate invoice status based on dates
 */
function calculateInvoiceStatus($invoice) {
    if ($invoice['status'] === 'paid') return 'paid';
    
    $today = new DateTime();
    $dueDate = new DateTime($invoice['due_date']);
    $diff = $today->diff($dueDate);
    $daysPast = $dueDate < $today ? (int)$diff->days : -(int)$diff->days;
    
    if ($daysPast <= 0) return 'invoiced';        // Not yet due
    if ($daysPast <= 7) return 'due';              // 1-7 days past due
    if ($daysPast <= 30) return 'overdue';          // 8-30 days past due
    if ($daysPast <= 90) return 'critical';         // 31-90 days past due
    return 'banned';                                 // 90+ days past due
}

/**
 * Update all invoice statuses (run on page load)
 */
function updateInvoiceStatuses($pdo) {
    $stmt = $pdo->query("SELECT id, status, due_date, paid_date FROM invoices WHERE status != 'paid'");
    $invoices = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $updateStmt = $pdo->prepare("UPDATE invoices SET status = ?, updated_at = NOW() WHERE id = ? AND status != ?");
    foreach ($invoices as $inv) {
        $newStatus = calculateInvoiceStatus($inv);
        if ($newStatus !== $inv['status']) {
            $updateStmt->execute([$newStatus, $inv['id'], $newStatus]);
        }
    }
}

/**
 * Check if a client is banned (any invoice 90+ days overdue)
 */
function isClientBanned($pdo, $clientId) {
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM invoices WHERE client_id = ? AND status != 'paid' AND due_date < DATE_SUB(CURDATE(), INTERVAL 90 DAY)");
    $stmt->execute([$clientId]);
    return (int)$stmt->fetchColumn() > 0;
}

/**
 * Generate invoice for a closed project
 * No GST/tax - total = subtotal. "Inclusive of all taxes."
 * Payment terms: Net 30
 */
function generateProjectInvoice($pdo, $projectId, $projectCode, $clientId) {
    // Check if invoice already exists
    $stmt = $pdo->prepare("SELECT id FROM invoices WHERE project_id = ?");
    $stmt->execute([$projectId]);
    if ($stmt->fetch()) return false;
    
    // Load company settings
    $settings = [];
    try {
        $result = $pdo->query("SELECT setting_key, setting_value FROM company_settings");
        while ($row = $result->fetch()) $settings[$row['setting_key']] = $row['setting_value'];
    } catch (Exception $e) {}
    
    // Load pricing
    $pricing = ['base_sample'=>0,'base_incentive'=>0,'incr_sample'=>0,'incr_incentive'=>0,'usd_factor'=>0,'eur_factor'=>0];
    $pricingMap = [
        'pricing_base_sample_cost'=>'base_sample','pricing_base_incentive'=>'base_incentive',
        'pricing_loi_increment_sample'=>'incr_sample','pricing_loi_increment_incentive'=>'incr_incentive',
        'pricing_usd_factor'=>'usd_factor','pricing_eur_factor'=>'eur_factor',
    ];
    foreach ($pricingMap as $dbKey => $arrKey) {
        if (isset($settings[$dbKey])) $pricing[$arrKey] = (float)$settings[$dbKey];
    }
    
    // Get project
    $stmt = $pdo->prepare("SELECT * FROM projects WHERE id = ?");
    $stmt->execute([$projectId]);
    $project = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$project) return false;
    
    // Get client
    $stmt = $pdo->prepare("SELECT * FROM clients WHERE id = ?");
    $stmt->execute([$clientId]);
    $client = $stmt->fetch(PDO::FETCH_ASSOC);
    if (!$client) return false;
    
    $clientCurrency = $client['currency'] ?? 'INR';
    $currencySymbol = '₹';
    if ($clientCurrency === 'USD') $currencySymbol = '$';
    elseif ($clientCurrency === 'EUR') $currencySymbol = '€';
    
    // Get valid completes and LOI
    $stmt = $pdo->prepare("
        SELECT COUNT(*) as valid_completes,
               AVG(actual_loi_seconds) as avg_loi_seconds,
               SUM(CASE WHEN actual_loi_seconds IS NOT NULL THEN 1 ELSE 0 END) as with_loi
        FROM survey_urls WHERE project_id = ? AND status = 'complete' AND quality_flag = 'valid'
    ");
    $stmt->execute([$projectCode]);
    $stats = $stmt->fetch(PDO::FETCH_ASSOC);
    
    $validCompletes = (int)($stats['valid_completes'] ?? 0);
    if ($validCompletes === 0) return false;
    
    // Determine LOI (measured or fallback to ELOI)
    $avgLoi = 0;
    if ($stats['with_loi'] > 0 && $stats['avg_loi_seconds'] > 0) {
        $avgLoi = round($stats['avg_loi_seconds'] / 60, 1);
    } else {
        $avgLoi = (float)$project['eloi'];
    }
    
    // Calculate costs (same formula as estimator)
    $extraMin = max(0, $avgLoi - 5);
    $sampleCostPerINR = $pricing['base_sample'] + ($extraMin * $pricing['incr_sample']);
    $incentivePerINR = $pricing['base_incentive'] + ($extraMin * $pricing['incr_incentive']);
    
    $sampleCostPer = $sampleCostPerINR;
    $incentivePer = $incentivePerINR;
    
    if ($clientCurrency === 'USD') {
        $factor = 1 + $pricing['usd_factor'] / 100;
        $sampleCostPer = $sampleCostPerINR * $factor;
        $incentivePer = $incentivePerINR * $factor;
    } elseif ($clientCurrency === 'EUR') {
        $factor = 1 + $pricing['eur_factor'] / 100;
        $sampleCostPer = $sampleCostPerINR * $factor;
        $incentivePer = $incentivePerINR * $factor;
    }
    
    $ratePer = $sampleCostPer + $incentivePer;
    $sampleTotal = $sampleCostPer * $validCompletes;
    $incentiveTotal = $incentivePer * $validCompletes;
    $subtotal = $ratePer * $validCompletes;
    
    // No tax - total = subtotal (inclusive of all taxes)
    $totalAmount = $subtotal;
    
    // Generate invoice number
    $prefix = $settings['invoice_prefix'] ?? 'RRC';
    $stmt = $pdo->query("SELECT COALESCE(MAX(id), 0) + 1 as next_id FROM invoices");
    $nextId = $stmt->fetch()['next_id'];
    $invoiceNumber = $prefix . '-' . str_pad($nextId, 4, '0', STR_PAD_LEFT);
    
    // Net 30
    $invoiceDate = date('Y-m-d');
    $dueDate = date('Y-m-d', strtotime('+30 days'));
    
    // Build addresses
    $clientAddr = trim(implode(', ', array_filter([
        $client['address'] ?? '', $client['city'] ?? '', $client['state'] ?? '',
        $client['country'] ?? '', $client['postal_code'] ?? ''
    ])));
    $companyAddr = trim(implode(', ', array_filter([
        $settings['company_address'] ?? '', $settings['company_city'] ?? '',
        $settings['company_state'] ?? '', $settings['company_country'] ?? '',
        $settings['company_pincode'] ?? ''
    ])));
    
    $stmt = $pdo->prepare("
        INSERT INTO invoices (
            invoice_number, project_id, project_code, client_id,
            currency, currency_symbol,
            subtotal, total_amount,
            valid_completes, avg_loi_minutes, rate_per_sample, sample_cost, incentive_cost,
            invoice_date, due_date, status,
            company_name, company_address, company_gstin, company_pan, company_sac_code,
            client_name, client_address, client_email, client_country
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'invoiced', ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ");
    $stmt->execute([
        $invoiceNumber, $projectId, $projectCode, $clientId,
        $clientCurrency, $currencySymbol,
        round($subtotal, 2), round($totalAmount, 2),
        $validCompletes, $avgLoi, round($ratePer, 2), round($sampleTotal, 2), round($incentiveTotal, 2),
        $invoiceDate, $dueDate,
        $settings['company_name'] ?? '', $companyAddr,
        $settings['tax_gst'] ?? ($settings['company_gstin'] ?? ''),
        $settings['tax_pan'] ?? ($settings['company_pan'] ?? ''),
        $settings['tax_sac_code'] ?? '',
        $client['company_name'], $clientAddr, $client['email'], $client['country'] ?? ''
    ]);
    
    return $pdo->lastInsertId();
}

/**
 * Get status badge HTML
 */
function getStatusBadge($status) {
    $map = [
        'invoiced' => ['bg'=>'#dbeafe','color'=>'#1e40af','label'=>'Invoiced'],
        'paid'     => ['bg'=>'#d1fae5','color'=>'#065f46','label'=>'Paid'],
        'due'      => ['bg'=>'#fef3c7','color'=>'#92400e','label'=>'Due'],
        'overdue'  => ['bg'=>'#fed7aa','color'=>'#9a3412','label'=>'Overdue'],
        'critical' => ['bg'=>'#fecaca','color'=>'#991b1b','label'=>'Critical'],
        'banned'   => ['bg'=>'#1f2937','color'=>'#f9fafb','label'=>'Banned'],
    ];
    $s = $map[$status] ?? $map['invoiced'];
    return '<span style="display:inline-block;padding:3px 10px;border-radius:6px;font-size:.76rem;font-weight:700;background:'.$s['bg'].';color:'.$s['color'].'">'.$s['label'].'</span>';
}

/**
 * Ensure invoices table exists (inline DDL for portability)
 */
function ensureInvoicesTable($pdo) {
    try {
        $pdo->exec("CREATE TABLE IF NOT EXISTS `invoices` (
            `id` int(11) NOT NULL AUTO_INCREMENT,
            `invoice_number` varchar(50) NOT NULL,
            `project_id` int(11) NOT NULL,
            `project_code` varchar(50) NOT NULL,
            `client_id` int(11) NOT NULL,
            `currency` enum('INR','USD','EUR') NOT NULL DEFAULT 'INR',
            `currency_symbol` varchar(5) NOT NULL DEFAULT '₹',
            `subtotal` decimal(12,2) NOT NULL DEFAULT 0.00,
            `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
            `valid_completes` int(11) NOT NULL DEFAULT 0,
            `avg_loi_minutes` decimal(6,1) NOT NULL DEFAULT 0.0,
            `rate_per_sample` decimal(10,2) NOT NULL DEFAULT 0.00,
            `sample_cost` decimal(12,2) NOT NULL DEFAULT 0.00,
            `incentive_cost` decimal(12,2) NOT NULL DEFAULT 0.00,
            `invoice_date` date NOT NULL,
            `due_date` date NOT NULL,
            `paid_date` date DEFAULT NULL,
            `status` enum('invoiced','paid','due','overdue','critical','banned') NOT NULL DEFAULT 'invoiced',
            `company_name` varchar(255) DEFAULT NULL,
            `company_address` text DEFAULT NULL,
            `company_gstin` varchar(50) DEFAULT NULL,
            `company_pan` varchar(20) DEFAULT NULL,
            `company_sac_code` varchar(20) DEFAULT NULL,
            `client_name` varchar(255) DEFAULT NULL,
            `client_address` text DEFAULT NULL,
            `client_email` varchar(255) DEFAULT NULL,
            `client_country` varchar(100) DEFAULT NULL,
            `remarks` text DEFAULT NULL,
            `payment_reference` varchar(255) DEFAULT NULL,
            `created_at` timestamp NULL DEFAULT current_timestamp(),
            `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
            PRIMARY KEY (`id`),
            UNIQUE KEY `invoice_number` (`invoice_number`),
            KEY `idx_project` (`project_id`),
            KEY `idx_client` (`client_id`),
            KEY `idx_status` (`status`),
            KEY `idx_due_date` (`due_date`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    } catch (Exception $e) {}
}