<?php
/**
 * Enterprise HRM ERP System - Fortnight Stock Export to Excel
 */

require_once __DIR__ . '/../../includes/init.php';
requireLogin();

$siteId = getCurrentSiteId();
$periodId = isset($_GET['period_id']) ? (int)$_GET['period_id'] : 0;
$month = isset($_GET['month']) ? (int)$_GET['month'] : (int)date('m');
$year = isset($_GET['year']) ? (int)$_GET['year'] : (int)date('Y');

try {
    $db = getDB();
    
    // Get site name
    $siteStmt = $db->prepare("SELECT site_name FROM sites WHERE site_id = ?");
    $siteStmt->execute([$siteId]);
    $siteData = $siteStmt->fetch();
    $siteName = $siteData['site_name'] ?? '';
    
    // Get "To" name for this site
    $toName = '';
    try {
        $stmt = $db->prepare("SELECT to_name FROM fortnight_to_names WHERE site_id = ?");
        $stmt->execute([$siteId]);
        $toNameRow = $stmt->fetch();
        if ($toNameRow) {
            $toName = $toNameRow['to_name'];
        }
    } catch (Exception $e) {
        // Table might not exist
    }
    
    if ($periodId > 0) {
        // Export specific period
        $stmt = $db->prepare("SELECT *, date as start_date, period_date as end_date FROM fortnight_periods WHERE period_id = ? AND site_id = ?");
        $stmt->execute([$periodId, $siteId]);
        $period = $stmt->fetch();
        
        if (!$period) {
            die('Period not found.');
        }
        
        $stmt = $db->prepare("
            SELECT * FROM fortnight_inventory 
            WHERE period_id = ? 
            ORDER BY display_order ASC, inventory_id ASC
        ");
        $stmt->execute([$periodId]);
        $items = $stmt->fetchAll();
        
        $filename = 'Fortnight_Report_' . date('Y-m-d', strtotime($period['start_date'])) . '_to_' . date('Y-m-d', strtotime($period['end_date'])) . '.xls';
        
        // Output Excel (HTML format)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        
        echo '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">';
        echo '<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">';
        echo '<style>
            table { border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; font-size: 10pt; }
            th { background-color: #1e3c72; color: white; font-weight: bold; padding: 8px; text-align: center; border: 1px solid #000; }
            td { padding: 5px; border: 1px solid #ccc; text-align: left; }
            .text-right { text-align: right !important; }
            .site-info { font-weight: bold; font-size: 12pt; padding: 10px; border: none; }
            .header-row { background-color: #808080 !important; color: white !important; font-weight: bold; }
            .calc-row { background-color: #ffa500 !important; color: #000 !important; font-weight: bold; }
            .calculated { background-color: #dcfce7 !important; }
        </style></head><body>';
        
        // Add site info
        echo '<table style="margin-bottom: 10px; border: none;">';
        echo '<tr><td class="site-info" style="border: none;">Site: ' . htmlspecialchars($siteName) . '</td></tr>';
        if (!empty($toName)) {
            echo '<tr><td style="border: none; font-weight: bold;">To: ' . htmlspecialchars($toName) . '</td></tr>';
        }
        echo '<tr><td style="border: none; font-weight: bold;">Period: ' . htmlspecialchars($period['period_name']) . '</td></tr>';
        echo '<tr><td style="border: none;">Date: ' . date('d M Y', strtotime($period['start_date'])) . ' to ' . date('d M Y', strtotime($period['end_date'])) . '</td></tr>';
        echo '<tr><td style="border: none;">Export Date: ' . date('d-m-Y H:i') . '</td></tr>';
        echo '</table>';
        
        // Main table
        echo '<table>';
        echo '<tr>';
        echo '<th>SR NO.</th>';
        echo '<th>Material HSN Code No</th>';
        echo '<th>Material Description</th>';
        echo '<th>Net Rate</th>';
        echo '<th>Opening Stock Qty</th>';
        echo '<th>Opening Stock Value</th>';
        echo '<th>Purchase</th>';
        echo '<th>Purchase Value</th>';
        echo '<th>Consumption</th>';
        echo '<th>Consumption Value</th>';
        echo '<th>Closing Stock Qty</th>';
        echo '<th>Closing Stock Value</th>';
        echo '<th>Supplier Name</th>';
        echo '<th>Bill/ Challan No</th>';
        echo '</tr>';
        
        $srNo = 1;
        foreach ($items as $item) {
            $rowType = $item['row_type'] ?? 'data';
            
            if ($rowType === 'header') {
                // Header row
                echo '<tr class="header-row">';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">SR NO.</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Material HSN Code No</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">' . htmlspecialchars($item['header_text'] ?? 'Material Description') . '</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Net Rate</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Opening Stock Qty</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Opening Stock Value</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Purchase</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Purchase Value</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Consumption</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Consumption Value</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Closing Stock Qty</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Closing Stock Value</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Supplier Name</td>';
                echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Bill/ Challan No</td>';
                echo '</tr>';
            } elseif ($rowType === 'calculation') {
                // Calculation row
                echo '<tr class="calc-row">';
                echo '<td colspan="3" style="background-color: #ffa500; color: #000; font-weight: bold; text-align: center;">' . htmlspecialchars($item['header_text'] ?? 'Total Amount') . '</td>';
                echo '<td style="background-color: #ffa500; color: #000; font-weight: bold;"></td>';
                echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['opening_stock_qty'] ?? 0, 3) . '</td>';
                echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['opening_stock_value'] ?? 0, 2) . '</td>';
                echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['purchase_qty'] ?? 0, 3) . '</td>';
                echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['purchase_value'] ?? 0, 2) . '</td>';
                echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['consumption_qty'] ?? 0, 3) . '</td>';
                echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['consumption_value'] ?? 0, 2) . '</td>';
                echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['closing_stock_qty'] ?? 0, 3) . '</td>';
                echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['closing_stock_value'] ?? 0, 2) . '</td>';
                echo '<td colspan="2" style="background-color: #ffa500; color: #000; font-weight: bold;"></td>';
                echo '</tr>';
            } else {
                // Data row
                echo '<tr>';
                echo '<td style="text-align: center;">' . $srNo++ . '</td>';
                echo '<td>' . htmlspecialchars($item['material_hsn_code'] ?? '') . '</td>';
                echo '<td>' . htmlspecialchars($item['material_description'] ?? '') . '</td>';
                echo '<td class="text-right">' . number_format($item['net_rate'] ?? 0, 2) . '</td>';
                echo '<td class="text-right">' . number_format($item['opening_stock_qty'] ?? 0, 3) . '</td>';
                echo '<td class="text-right calculated">' . number_format($item['opening_stock_value'] ?? 0, 2) . '</td>';
                echo '<td class="text-right">' . number_format($item['purchase_qty'] ?? 0, 3) . '</td>';
                echo '<td class="text-right calculated">' . number_format($item['purchase_value'] ?? 0, 2) . '</td>';
                echo '<td class="text-right">' . number_format($item['consumption_qty'] ?? 0, 3) . '</td>';
                echo '<td class="text-right calculated">' . number_format($item['consumption_value'] ?? 0, 2) . '</td>';
                echo '<td class="text-right calculated">' . number_format($item['closing_stock_qty'] ?? 0, 3) . '</td>';
                echo '<td class="text-right calculated">' . number_format($item['closing_stock_value'] ?? 0, 2) . '</td>';
                echo '<td>' . htmlspecialchars($item['supplier_name'] ?? '') . '</td>';
                echo '<td>' . htmlspecialchars($item['bill_challan_no'] ?? '') . '</td>';
                echo '</tr>';
            }
        }
        
        echo '</table>';
        echo '</body></html>';
        exit;
        
    } else {
        // Export all periods for month/year
        $stmt = $db->prepare("
            SELECT * FROM fortnight_periods 
            WHERE site_id = ? AND month = ? AND year = ?
            ORDER BY date DESC, period_id DESC
        ");
        $stmt->execute([$siteId, $month, $year]);
        $periods = $stmt->fetchAll();
        
        $filename = 'Fortnight_Report_' . date('F_Y', mktime(0, 0, 0, $month, 1, $year)) . '.xls';
        
        // Output Excel (HTML format)
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        
        echo '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40">';
        echo '<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8">';
        echo '<style>
            table { border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; font-size: 10pt; }
            th { background-color: #1e3c72; color: white; font-weight: bold; padding: 8px; text-align: center; border: 1px solid #000; }
            td { padding: 5px; border: 1px solid #ccc; text-align: left; }
            .text-right { text-align: right !important; }
            .site-info { font-weight: bold; font-size: 12pt; padding: 10px; border: none; }
            .period-header { background-color: #2a5298 !important; color: white !important; font-weight: bold; }
            .header-row { background-color: #808080 !important; color: white !important; font-weight: bold; }
            .calc-row { background-color: #ffa500 !important; color: #000 !important; font-weight: bold; }
            .calculated { background-color: #dcfce7 !important; }
        </style></head><body>';
        
        // Add site info
        echo '<table style="margin-bottom: 10px; border: none;">';
        echo '<tr><td class="site-info" style="border: none;">Site: ' . htmlspecialchars($siteName) . '</td></tr>';
        if (!empty($toName)) {
            echo '<tr><td style="border: none; font-weight: bold;">To: ' . htmlspecialchars($toName) . '</td></tr>';
        }
        echo '<tr><td style="border: none; font-weight: bold;">Period: ' . date('F Y', mktime(0, 0, 0, $month, 1, $year)) . '</td></tr>';
        echo '<tr><td style="border: none;">Export Date: ' . date('d-m-Y H:i') . '</td></tr>';
        echo '</table>';
        
        // Export each period
        foreach ($periods as $period) {
            $stmt = $db->prepare("
                SELECT * FROM fortnight_inventory 
                WHERE period_id = ? 
                ORDER BY display_order ASC, inventory_id ASC
            ");
            $stmt->execute([$period['period_id']]);
            $items = $stmt->fetchAll();
            
            echo '<table style="margin-top: 20px;">';
            
            // Period header
            echo '<tr class="period-header">';
            echo '<td colspan="14" style="background-color: #2a5298; color: white; font-weight: bold; padding: 10px;">';
            echo htmlspecialchars($period['period_name']) . ' | ';
            echo date('d M Y', strtotime($period['date'])) . ' to ' . date('d M Y', strtotime($period['period_date']));
            echo '</td></tr>';
            
            // Column headers
            echo '<tr>';
            echo '<th>SR NO.</th>';
            echo '<th>Material HSN Code No</th>';
            echo '<th>Material Description</th>';
            echo '<th>Net Rate</th>';
            echo '<th>Opening Stock Qty</th>';
            echo '<th>Opening Stock Value</th>';
            echo '<th>Purchase</th>';
            echo '<th>Purchase Value</th>';
            echo '<th>Consumption</th>';
            echo '<th>Consumption Value</th>';
            echo '<th>Closing Stock Qty</th>';
            echo '<th>Closing Stock Value</th>';
            echo '<th>Supplier Name</th>';
            echo '<th>Bill/ Challan No</th>';
            echo '</tr>';
            
            $srNo = 1;
            foreach ($items as $item) {
                $rowType = $item['row_type'] ?? 'data';
                
                if ($rowType === 'header') {
                    echo '<tr class="header-row">';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">SR NO.</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Material HSN Code No</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">' . htmlspecialchars($item['header_text'] ?? 'Material Description') . '</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Net Rate</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Opening Stock Qty</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Opening Stock Value</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Purchase</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Purchase Value</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Consumption</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Consumption Value</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Closing Stock Qty</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Closing Stock Value</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Supplier Name</td>';
                    echo '<td style="background-color: #808080; color: white; font-weight: bold; text-align: center;">Bill/ Challan No</td>';
                    echo '</tr>';
                } elseif ($rowType === 'calculation') {
                    echo '<tr class="calc-row">';
                    echo '<td colspan="3" style="background-color: #ffa500; color: #000; font-weight: bold; text-align: center;">' . htmlspecialchars($item['header_text'] ?? 'Total Amount') . '</td>';
                    echo '<td style="background-color: #ffa500; color: #000; font-weight: bold;"></td>';
                    echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['opening_stock_qty'] ?? 0, 3) . '</td>';
                    echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['opening_stock_value'] ?? 0, 2) . '</td>';
                    echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['purchase_qty'] ?? 0, 3) . '</td>';
                    echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['purchase_value'] ?? 0, 2) . '</td>';
                    echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['consumption_qty'] ?? 0, 3) . '</td>';
                    echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['consumption_value'] ?? 0, 2) . '</td>';
                    echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['closing_stock_qty'] ?? 0, 3) . '</td>';
                    echo '<td class="text-right" style="background-color: #ffa500; color: #000; font-weight: bold;">' . number_format($item['closing_stock_value'] ?? 0, 2) . '</td>';
                    echo '<td colspan="2" style="background-color: #ffa500; color: #000; font-weight: bold;"></td>';
                    echo '</tr>';
                } else {
                    echo '<tr>';
                    echo '<td style="text-align: center;">' . $srNo++ . '</td>';
                    echo '<td>' . htmlspecialchars($item['material_hsn_code'] ?? '') . '</td>';
                    echo '<td>' . htmlspecialchars($item['material_description'] ?? '') . '</td>';
                    echo '<td class="text-right">' . number_format($item['net_rate'] ?? 0, 2) . '</td>';
                    echo '<td class="text-right">' . number_format($item['opening_stock_qty'] ?? 0, 3) . '</td>';
                    echo '<td class="text-right calculated">' . number_format($item['opening_stock_value'] ?? 0, 2) . '</td>';
                    echo '<td class="text-right">' . number_format($item['purchase_qty'] ?? 0, 3) . '</td>';
                    echo '<td class="text-right calculated">' . number_format($item['purchase_value'] ?? 0, 2) . '</td>';
                    echo '<td class="text-right">' . number_format($item['consumption_qty'] ?? 0, 3) . '</td>';
                    echo '<td class="text-right calculated">' . number_format($item['consumption_value'] ?? 0, 2) . '</td>';
                    echo '<td class="text-right calculated">' . number_format($item['closing_stock_qty'] ?? 0, 3) . '</td>';
                    echo '<td class="text-right calculated">' . number_format($item['closing_stock_value'] ?? 0, 2) . '</td>';
                    echo '<td>' . htmlspecialchars($item['supplier_name'] ?? '') . '</td>';
                    echo '<td>' . htmlspecialchars($item['bill_challan_no'] ?? '') . '</td>';
                    echo '</tr>';
                }
            }
            
            echo '</table>';
        }
        
        if (empty($periods)) {
            echo '<table><tr><td style="text-align: center; padding: 20px;">No data found for the selected period.</td></tr></table>';
        }
        
        echo '</body></html>';
        exit;
    }
    
} catch (Exception $e) {
    error_log("Export error: " . $e->getMessage());
    die('Export failed: ' . $e->getMessage());
}
