<?php
// inventory_analyst.php

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Konfigurasi koneksi database
define('DB_HOST', 'localhost');
define('DB_USER', 'wiselie_nanda');
define('DB_PASS', 'Clemira123');
define('DB_NAME', 'wiselie_inventory');

// Inisialisasi variabel
$error = '';
$inventory_data = [];
$warehouse_data = [];
$warehouses = [
    'products' => 'Surabaya',
    'wh_bali' => 'Bali',
    'wh_jakarta' => 'Jakarta',
    'wh_bandung' => 'Bandung',
    'wh_makassar' => 'Makassar',
    'wh_semarang' => 'Semarang'
];

// Konfigurasi lead time (dalam hari)
$lead_time = 7; // Waktu tunggu pengiriman (7 hari)

// Handle Excel export
if (isset($_GET['export']) && $_GET['export'] == 'excel') {
    exportToExcel();
    exit;
}

// Periode penjualan (default bulan ini)
$start_date = date('Y-m-01');
$end_date = date('Y-m-t');
if (isset($_GET['start_date'])) {
    $start_date = $_GET['start_date'];
}
if (isset($_GET['end_date'])) {
    $end_date = $_GET['end_date'];
}

// Hitung hari berjalan dalam periode
$start_datetime = new DateTime($start_date);
$end_datetime = new DateTime($end_date);
$days_passed = $start_datetime->diff($end_datetime)->days + 1;
$current_day = (new DateTime())->format('Y-m-d');
if ($current_day < $end_date) {
    $days_passed = $start_datetime->diff(new DateTime())->days + 1;
}

try {
    // Membuat koneksi database
    $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
    
    // Cek koneksi
    if ($conn->connect_error) {
        throw new Exception("Koneksi database gagal: " . $conn->connect_error);
    }

    // Ambil semua part_number dan buy_price dari products dengan categorie_id = 7
    $products_query = "SELECT part_number, buy_price FROM products WHERE categorie_id = 7";
    $products_result = $conn->query($products_query);
    
    if ($products_result === false) {
        throw new Exception("Error mengambil data products: " . $conn->error);
    }

    $all_products = [];
    if ($products_result->num_rows > 0) {
        while ($row = $products_result->fetch_assoc()) {
            $all_products[] = [
                'part_number' => $row['part_number'],
                'buy_price' => $row['buy_price']
            ];
        }
    } else {
        $error = "Tidak ditemukan part_number dengan categorie_id = 7";
    }

    // Ambil data penjualan per lokasi
    $sales_data = [];
    $sales_query = "SELECT part_number, 
                    SUM(CASE WHEN loc = 'Jakarta' THEN quantity ELSE 0 END) as jakarta,
                    SUM(CASE WHEN loc = 'Makassar' THEN quantity ELSE 0 END) as makassar,
                    SUM(CASE WHEN loc = 'Bali' THEN quantity ELSE 0 END) as bali,
                    SUM(CASE WHEN loc = 'Semarang' THEN quantity ELSE 0 END) as semarang,
                    SUM(CASE WHEN loc = 'Bandung' THEN quantity ELSE 0 END) as bandung,
                    SUM(CASE WHEN loc = 'Sidoarjo' THEN quantity ELSE 0 END) as surabaya
                    FROM awb 
                    WHERE DATE(scan_in) BETWEEN ? AND ?
                    GROUP BY part_number";
    
    $stmt = $conn->prepare($sales_query);
    $stmt->bind_param("ss", $start_date, $end_date);
    $stmt->execute();
    $sales_result = $stmt->get_result();
    
    while ($row = $sales_result->fetch_assoc()) {
        $sales_data[$row['part_number']] = [
            'Jakarta' => $row['jakarta'],
            'Makassar' => $row['makassar'],
            'Bali' => $row['bali'],
            'Semarang' => $row['semarang'],
            'Bandung' => $row['bandung'],
            'Surabaya' => $row['surabaya']
        ];
    }

    // Siapkan data untuk setiap part_number di semua warehouse
    foreach ($all_products as $product) {
        $part_number = $product['part_number'];
        $buy_price = $product['buy_price'];
        
        $part_data = [
            'part_number' => $part_number,
            'buy_price' => $buy_price
        ];
        
        $total = 0;
        $total_sales = 0;
        
        foreach ($warehouses as $table => $location) {
            // Query quantity untuk part_number di setiap warehouse
            $query = "SELECT quantity FROM $table WHERE part_number = ?";
            $stmt = $conn->prepare($query);
            
            if ($stmt === false) {
                throw new Exception("Error prepare statement untuk $table: " . $conn->error);
            }
            
            $stmt->bind_param("s", $part_number);
            if (!$stmt->execute()) {
                throw new Exception("Error execute query untuk $table: " . $stmt->error);
            }
            
            $result = $stmt->get_result();
            if ($result === false) {
                throw new Exception("Error mendapatkan result untuk $table: " . $stmt->error);
            }
            
            $quantity = 0;
            if ($result->num_rows > 0) {
                $row = $result->fetch_assoc();
                $quantity = (int)$row['quantity'];
            }
            
            $part_data[$location] = $quantity;
            $total += $quantity;
            
            // Tambahkan data penjualan jika ada
            $sales_qty = isset($sales_data[$part_number][$location]) ? $sales_data[$part_number][$location] : 0;
            $part_data[$location.'_sales'] = $sales_qty;
            $total_sales += $sales_qty;
            
            // Hitung metrics per lokasi untuk breakdown
            $daily_sales_rate_loc = ($days_passed > 0) ? $sales_qty / $days_passed : 0;
            $inventory_days_loc = ($daily_sales_rate_loc > 0) ? round($quantity / $daily_sales_rate_loc, 2) : 0;
            
            // Hitung safety stock dan reorder point per lokasi (MODIFIED)
            $safety_stock_loc = ceil($daily_sales_rate_loc * 14); // Safety stock untuk 14 hari
            $reorder_point_loc = ceil(($daily_sales_rate_loc * $lead_time) + $safety_stock_loc); // Lead time 7 hari
            
            // Tentukan status stok per lokasi
            if ($quantity <= 0) {
                $stock_status_loc = 'Stockout';
                $stock_status_class_loc = 'stockout';
            } elseif ($quantity <= $safety_stock_loc) {
                $stock_status_loc = 'Low';
                $stock_status_class_loc = 'low-stock';
            } elseif ($quantity <= $reorder_point_loc) {
                $stock_status_loc = 'Reorder';
                $stock_status_class_loc = 'reorder';
            } elseif ($inventory_days_loc > 90) {
                $stock_status_loc = 'Excess';
                $stock_status_class_loc = 'excess-stock';
            } else {
                $stock_status_loc = 'Adequate';
                $stock_status_class_loc = 'adequate';
            }
            
            // Tentukan movement status per lokasi
            if ($inventory_days_loc > 60) {
                $status_loc = 'Slow Moving';
                $status_class_loc = 'slow-moving';
            } elseif ($inventory_days_loc < 30) {
                $status_loc = 'Fast Moving';
                $status_class_loc = 'fast-moving';
            } else {
                $status_loc = 'Normal';
                $status_class_loc = 'normal';
            }

            // Determine if production is needed for this location
            $need_produce_loc = ($inventory_days_loc <= 30 && $inventory_days_loc > 0) ? 'YES' : 'NO';
            if ($sales_qty == 0 && $quantity > 0) { // If no sales but stock, no production needed (it's slow/dead stock)
                $need_produce_loc = 'NO';
            } elseif ($sales_qty == 0 && $quantity == 0) { // If no sales and no stock, no production needed (no demand)
                $need_produce_loc = 'NO';
            } elseif ($daily_sales_rate_loc == 0 && $quantity > 0 && $inventory_days_loc <= 30){ // To handle cases where daily sales rate might be 0 but stock exists. If stock is low and no sales, still no production.
                 $need_produce_loc = 'NO';
            }
            if ($quantity <= $safety_stock_loc && $daily_sales_rate_loc > 0) { // If current stock is below safety stock and there's demand
                 $need_produce_loc = 'YES';
            }
            if ($quantity == 0 && $sales_qty > 0) { // If stock is zero and there's sales, definitely need produce
                 $need_produce_loc = 'YES';
            }


            // Simpan data per lokasi
            $warehouse_data[$location][$part_number] = [
                'quantity' => $quantity,
                'buy_price' => $buy_price,
                'amount' => $quantity * $buy_price,
                'sales' => $sales_qty,
                'daily_sales_rate' => round($daily_sales_rate_loc, 2),
                'inventory_days' => $inventory_days_loc,
                'safety_stock' => $safety_stock_loc,
                'reorder_point' => $reorder_point_loc,
                'stock_status' => $stock_status_loc,
                'stock_status_class' => $stock_status_class_loc,
                'movement_status' => $status_loc,
                'movement_status_class' => $status_class_loc,
                'need_produce' => $need_produce_loc // Add this line
            ];
        }
        
        $part_data['Total'] = $total;
        $part_data['Total_sales'] = $total_sales;
        $part_data['Amount'] = $total * $buy_price;
        $part_data['Sales_amount'] = $total_sales * $buy_price;
        
        // Hitung daily sales rate dan inventory days
        $daily_sales_rate = ($days_passed > 0) ? $total_sales / $days_passed : 0;
        $part_data['Daily_sales_rate'] = round($daily_sales_rate, 2);
        $part_data['Inventory_days'] = ($daily_sales_rate > 0) ? round($total / $daily_sales_rate, 2) : 0;
        
        // Hitung safety stock dan reorder point (MODIFIED)
        $safety_stock = ceil($daily_sales_rate * 14); // Safety stock untuk 14 hari
        $reorder_point = ceil(($daily_sales_rate * $lead_time) + $safety_stock); // Lead time 7 hari
        
        $part_data['Safety_stock'] = $safety_stock;
        $part_data['Reorder_point'] = $reorder_point;
        
        // Tentukan status berdasarkan inventory days
        if ($part_data['Inventory_days'] > 60) {
            $part_data['Status'] = 'Slow Moving';
            $part_data['Status_Class'] = 'slow-moving';
        } elseif ($part_data['Inventory_days'] < 30) {
            $part_data['Status'] = 'Fast Moving';
            $part_data['Status_Class'] = 'fast-moving';
        } else {
            $part_data['Status'] = 'Normal';
            $part_data['Status_Class'] = 'normal';
        }
        
        // Tentukan status stok (Stockout, Low, Adequate, Excess)
        if ($total <= 0) {
            $part_data['Stock_status'] = 'Stockout';
            $part_data['Stock_status_class'] = 'stockout';
        } elseif ($total <= $safety_stock) {
            $part_data['Stock_status'] = 'Low';
            $part_data['Stock_status_class'] = 'low-stock';
        } elseif ($total <= $reorder_point) {
            $part_data['Stock_status'] = 'Reorder';
            $part_data['Stock_status_class'] = 'reorder';
        } elseif ($part_data['Inventory_days'] > 90) {
            $part_data['Stock_status'] = 'Excess';
            $part_data['Stock_status_class'] = 'excess-stock';
        } else {
            $part_data['Stock_status'] = 'Adequate';
            $part_data['Stock_status_class'] = 'adequate';
        }

        // Determine if production is needed for summary
        $need_produce_summary = ($part_data['Inventory_days'] <= 30 && $part_data['Inventory_days'] > 0) ? 'YES' : 'NO';
        if ($total_sales == 0 && $total > 0) {
             $need_produce_summary = 'NO';
        } elseif ($total_sales == 0 && $total == 0) {
            $need_produce_summary = 'NO';
        } elseif ($daily_sales_rate == 0 && $total > 0 && $part_data['Inventory_days'] <= 30) {
            $need_produce_summary = 'NO';
        }
        if ($total <= $safety_stock && $daily_sales_rate > 0) {
            $need_produce_summary = 'YES';
        }
        if ($total == 0 && $total_sales > 0) {
            $need_produce_summary = 'YES';
        }


        $part_data['Need_Produce'] = $need_produce_summary; // Add this line
        
        $inventory_data[] = $part_data;
    }

    // Urutkan berdasarkan Inventory Days
    usort($inventory_data, function($a, $b) {
        return $b['Inventory_days'] <=> $a['Inventory_days'];
    });

} catch (Exception $e) {
    $error = $e->getMessage();
} finally {
    // Tutup koneksi database jika ada
    if (isset($conn)) {
        $conn->close();
    }
}

function exportToExcel() {
    global $warehouses, $lead_time;
    
    // Tangkap parameter periode dari URL
    $start_date = $_GET['start_date'] ?? date('Y-m-01');
    $end_date = $_GET['end_date'] ?? date('Y-m-t');
    
    try {
        // Buat koneksi database baru
        $conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        
        if ($conn->connect_error) {
            throw new Exception("Koneksi database gagal: " . $conn->connect_error);
        }

        // AMBIL DATA PRODUK
        $products_query = "SELECT part_number, buy_price FROM products WHERE categorie_id = 7";
        $products_result = $conn->query($products_query);
        
        if ($products_result === false || $products_result->num_rows === 0) {
            throw new Exception("Tidak ditemukan produk dengan categorie_id = 7");
        }

        $all_products = [];
        while ($row = $products_result->fetch_assoc()) {
            $all_products[] = $row;
        }

        // AMBIL DATA PENJUALAN
        $sales_data = [];
        $sales_query = "SELECT part_number, 
                        SUM(CASE WHEN loc = 'Jakarta' THEN quantity ELSE 0 END) as jakarta,
                        SUM(CASE WHEN loc = 'Makassar' THEN quantity ELSE 0 END) as makassar,
                        SUM(CASE WHEN loc = 'Bali' THEN quantity ELSE 0 END) as bali,
                        SUM(CASE WHEN loc = 'Semarang' THEN quantity ELSE 0 END) as semarang,
                        SUM(CASE WHEN loc = 'Bandung' THEN quantity ELSE 0 END) as bandung,
                        SUM(CASE WHEN loc = 'Sidoarjo' THEN quantity ELSE 0 END) as surabaya
                        FROM awb 
                        WHERE DATE(scan_in) BETWEEN ? AND ?
                        GROUP BY part_number";
        
        $stmt = $conn->prepare($sales_query);
        $stmt->bind_param("ss", $start_date, $end_date);
        $stmt->execute();
        $sales_result = $stmt->get_result();
        
        while ($row = $sales_result->fetch_assoc()) {
            $sales_data[$row['part_number']] = [
                'Jakarta' => $row['jakarta'],
                'Makassar' => $row['makassar'],
                'Bali' => $row['bali'],
                'Semarang' => $row['semarang'],
                'Bandung' => $row['bandung'],
                'Surabaya' => $row['surabaya']
            ];
        }

        // PROSES DATA UNTUK EXPORT
        $export_data = [];
        $export_data_by_location = [];
        
        $start_datetime = new DateTime($start_date);
        $end_datetime = new DateTime($end_date);
        $days_passed = $start_datetime->diff($end_datetime)->days + 1;

        foreach ($all_products as $product) {
            $part_number = $product['part_number'];
            $buy_price = $product['buy_price'];
            
            $part_data = ['part_number' => $part_number];
            $total = 0;
            $total_sales = 0;
            
            foreach ($warehouses as $table => $location) {
                $query = "SELECT quantity FROM $table WHERE part_number = ?";
                $stmt = $conn->prepare($query);
                $stmt->bind_param("s", $part_number);
                $stmt->execute();
                $result = $stmt->get_result();
                
                $quantity = ($result->num_rows > 0) ? (int)$result->fetch_assoc()['quantity'] : 0;
                $part_data[$location] = $quantity;
                $total += $quantity;
                
                $sales_qty = $sales_data[$part_number][$location] ?? 0;
                $total_sales += $sales_qty;
                
                // Hitung metrics per lokasi untuk breakdown
                $daily_sales_rate_loc = ($days_passed > 0) ? $sales_qty / $days_passed : 0;
                $inventory_days_loc = ($daily_sales_rate_loc > 0) ? round($quantity / $daily_sales_rate_loc, 2) : 0;
                
                // Hitung safety stock dan reorder point per lokasi (MODIFIED)
                $safety_stock_loc = ceil($daily_sales_rate_loc * 14); // Safety stock untuk 14 hari
                $reorder_point_loc = ceil(($daily_sales_rate_loc * $lead_time) + $safety_stock_loc); // Lead time 7 hari
                
                // Tentukan status stok per lokasi
                if ($quantity <= 0) {
                    $stock_status_loc = 'Stockout';
                } elseif ($quantity <= $safety_stock_loc) {
                    $stock_status_loc = 'Low';
                } elseif ($quantity <= $reorder_point_loc) {
                    $stock_status_loc = 'Reorder';
                } elseif ($inventory_days_loc > 90) {
                    $stock_status_loc = 'Excess';
                } else {
                    $stock_status_loc = 'Adequate';
                }
                
                // Tentukan movement status per lokasi
                $movement_status_loc = ($inventory_days_loc > 60) ? 'Slow Moving' : 
                                         (($inventory_days_loc < 30) ? 'Fast Moving' : 'Normal');
                
                // Determine if production is needed for this location for Excel
                $need_produce_loc_excel = ($inventory_days_loc <= 30 && $inventory_days_loc > 0) ? 'YES' : 'NO';
                if ($sales_qty == 0 && $quantity > 0) {
                    $need_produce_loc_excel = 'NO';
                } elseif ($sales_qty == 0 && $quantity == 0) {
                    $need_produce_loc_excel = 'NO';
                } elseif ($daily_sales_rate_loc == 0 && $quantity > 0 && $inventory_days_loc <= 30) {
                     $need_produce_loc_excel = 'NO';
                }
                if ($quantity <= $safety_stock_loc && $daily_sales_rate_loc > 0) {
                     $need_produce_loc_excel = 'YES';
                }
                if ($quantity == 0 && $sales_qty > 0) {
                     $need_produce_loc_excel = 'YES';
                }

                // Simpan data per lokasi untuk export
                $export_data_by_location[$location][] = [
                    'Part Number' => $part_number,
                    'Buy Price' => $buy_price,
                    'Stock' => $quantity,
                    'Stock Value' => $quantity * $buy_price,
                    'Sales' => $sales_qty,
                    'Daily Sales Rate' => round($daily_sales_rate_loc, 2),
                    'Inventory Days' => $inventory_days_loc,
                    'Safety Stock' => $safety_stock_loc,
                    'Reorder Point' => $reorder_point_loc,
                    'Stock Status' => $stock_status_loc,
                    'Movement Status' => $movement_status_loc,
                    'Need Produce' => $need_produce_loc_excel // Add this line
                ];
            }
            
            // Hitung metrics untuk total
            $daily_sales_rate = ($days_passed > 0) ? $total_sales / $days_passed : 0;
            $inventory_days = ($daily_sales_rate > 0) ? round($total / $daily_sales_rate, 2) : 0;
            
            // Hitung safety stock dan reorder point (MODIFIED)
            $safety_stock = ceil($daily_sales_rate * 14); // Safety stock untuk 14 hari
            $reorder_point = ceil(($daily_sales_rate * $lead_time) + $safety_stock); // Lead time 7 hari
            
            // Tentukan status stok
            if ($total <= 0) {
                $stock_status = 'Stockout';
            } elseif ($total <= $safety_stock) {
                $stock_status = 'Low';
            } elseif ($total <= $reorder_point) {
                $stock_status = 'Reorder';
            } elseif ($inventory_days > 90) {
                $stock_status = 'Excess';
            } else {
                $stock_status = 'Adequate';
            }
            
            // Determine if production is needed for summary for Excel
            $need_produce_summary_excel = ($inventory_days <= 30 && $inventory_days > 0) ? 'YES' : 'NO';
            if ($total_sales == 0 && $total > 0) {
                $need_produce_summary_excel = 'NO';
            } elseif ($total_sales == 0 && $total == 0) {
                $need_produce_summary_excel = 'NO';
            } elseif ($daily_sales_rate == 0 && $total > 0 && $inventory_days <= 30) {
                 $need_produce_summary_excel = 'NO';
            }
            if ($total <= $safety_stock && $daily_sales_rate > 0) {
                $need_produce_summary_excel = 'YES';
            }
            if ($total == 0 && $total_sales > 0) {
                $need_produce_summary_excel = 'YES';
            }

            $export_data[] = [
                'Part Number' => $part_number,
                'Buy Price' => $buy_price,
                'Total Stock' => $total,
                'Stock Value' => $total * $buy_price,
                'Total Sales' => $total_sales,
                'Daily Sales Rate' => round($daily_sales_rate, 2),
                'Inventory Days' => $inventory_days,
                'Safety Stock' => $safety_stock,
                'Reorder Point' => $reorder_point,
                'Stock Status' => $stock_status,
                'Movement Status' => ($inventory_days > 60) ? 'Slow Moving' : 
                                        (($inventory_days < 30) ? 'Fast Moving' : 'Normal'),
                'Need Produce' => $need_produce_summary_excel // Add this line
            ];
        }

        // Urutkan data
        usort($export_data, function($a, $b) {
            return $b['Inventory Days'] <=> $a['Inventory Days'];
        });
        
        foreach ($warehouses as $location) {
            usort($export_data_by_location[$location], function($a, $b) {
                return $b['Inventory Days'] <=> $a['Inventory Days'];
            });
        }

        // BUAT FILE EXCEL
        $spreadsheet = new Spreadsheet();
        
        // Sheet 1: Summary
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Summary');
        
        // Header
        $sheet->setCellValue('A1', 'Inventory Analysis Report');
        $sheet->setCellValue('A2', 'Period: ' . $start_date . ' to ' . $end_date);
        $sheet->setCellValue('A3', 'Lead Time: ' . $lead_time . ' days | Safety Stock: 14 days coverage');
        
        // Tulis header kolom
        $headers = array_keys($export_data[0]);
        $sheet->fromArray($headers, null, 'A5');
        
        // Tulis data
        $row = 6;
        foreach ($export_data as $item) {
            $sheet->fromArray(array_values($item), null, 'A' . $row);
            $row++;
        }
        
        // Formatting
        $headerStyle = [
            'font' => ['bold' => true],
            'alignment' => ['horizontal' => 'center'],
            'borders' => ['bottom' => ['borderStyle' => 'thin']]
        ];
        $sheet->getStyle('A5:M5')->applyFromArray($headerStyle); // Adjusted to M5 for the new column
        
        // Format currency for buy price and stock value
        $sheet->getStyle('B6:B' . ($row-1))->getNumberFormat()->setFormatCode('#,##0.00');
        $sheet->getStyle('D6:D' . ($row-1))->getNumberFormat()->setFormatCode('#,##0.00');
        
        // Auto size columns
        foreach (range('A', 'M') as $column) { // Adjusted to M for the new column
            $sheet->getColumnDimension($column)->setAutoSize(true);
        }
        
        // Conditional formatting for stock status
        $conditionalStyles = [
            'Stockout' => 'FF0000', // Red
            'Low' => 'FFA500',     // Orange
            'Reorder' => 'FFFF00',  // Yellow
            'Excess' => 'ADD8E6',   // Light Blue
            'Adequate' => '90EE90'  // Light Green
        ];
        
        foreach ($conditionalStyles as $status => $color) {
            $conditional = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
            $conditional->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
            $conditional->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_EQUAL);
            $conditional->addCondition('"' . $status . '"');
            $conditional->getStyle()->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getEndColor()->setARGB($color);
            
            $sheet->getStyle('K6:K' . ($row-1))->setConditionalStyles([$conditional]);
        }
        
        // Sheets per location
        foreach ($warehouses as $location) {
            $sheet = $spreadsheet->createSheet();
            $sheet->setTitle($location);
            
            // Header
            $sheet->setCellValue('A1', 'Inventory Analysis - ' . $location);
            $sheet->setCellValue('A2', 'Period: ' . $start_date . ' to ' . $end_date);
            
            // Tulis header kolom
            $headers = array_keys($export_data_by_location[$location][0]);
            $sheet->fromArray($headers, null, 'A4');
            
            // Tulis data
            $row_loc = 5;
            foreach ($export_data_by_location[$location] as $item) {
                $sheet->fromArray(array_values($item), null, 'A' . $row_loc);
                $row_loc++;
            }
            
            // Formatting
            $sheet->getStyle('A4:M4')->applyFromArray($headerStyle); // Adjusted to M4
            
            // Format currency for buy price and stock value
            $sheet->getStyle('B5:B' . ($row_loc-1))->getNumberFormat()->setFormatCode('#,##0.00');
            $sheet->getStyle('D5:D' . ($row_loc-1))->getNumberFormat()->setFormatCode('#,##0.00');
            
            // Auto size columns
            foreach (range('A', 'M') as $column) { // Adjusted to M
                $sheet->getColumnDimension($column)->setAutoSize(true);
            }
            
            // Conditional formatting
            foreach ($conditionalStyles as $status => $color) {
                $conditional = new \PhpOffice\PhpSpreadsheet\Style\Conditional();
                $conditional->setConditionType(\PhpOffice\PhpSpreadsheet\Style\Conditional::CONDITION_CELLIS);
                $conditional->setOperatorType(\PhpOffice\PhpSpreadsheet\Style\Conditional::OPERATOR_EQUAL);
                $conditional->addCondition('"' . $status . '"');
                $conditional->getStyle()->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getEndColor()->setARGB($color);
                
                $sheet->getStyle('K5:K' . ($row_loc-1))->setConditionalStyles([$conditional]);
            }
        }
        
        // Set active sheet back to summary
        $spreadsheet->setActiveSheetIndex(0);
        
        // Output file
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="inventory_analysis_' . date('YmdHis') . '.xlsx"');
        header('Cache-Control: max-age=0');
        
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;

    } catch (Exception $e) {
        die("Error during export: " . $e->getMessage());
    } finally {
        if (isset($conn)) {
            $conn->close();
        }
    }
}
?>

<!DOCTYPE html>
<html lang="id">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Inventory Analyst - All Items</title>
    <style>
        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background-color: #f5f5f5;
            margin: 0;
            padding: 20px;
            color: #333;
        }
        .container {
            max-width: 1600px;
            margin: 0 auto;
        }
        .header {
            text-align: center;
            margin-bottom: 30px;
            padding: 20px;
            background: white;
            border-radius: 10px;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
        }
        h1 {
            color: #2c3e50;
            margin: 0;
        }
        h2 {
            color: #2c3e50;
            margin-top: 30px;
            border-bottom: 2px solid #eee;
            padding-bottom: 10px;
        }
        .card {
            background: white;
            border-radius: 10px;
            padding: 20px;
            box-shadow: 0 0 10px rgba(0,0,0,0.1);
            margin-bottom: 20px;
        }
        .card-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 15px;
            padding-bottom: 10px;
            border-bottom: 1px solid #eee;
        }
        .card-title {
            font-size: 18px;
            font-weight: bold;
            color: #2c3e50;
            margin: 0;
        }
        .inventory-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        .inventory-table th, 
        .inventory-table td {
            padding: 10px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }
        .inventory-table th {
            background-color: #f2f2f2;
            font-weight: bold;
            position: sticky;
            top: 0;
        }
        .inventory-table tr:hover {
            background-color: #f5f5f5;
        }
        .slow-moving {
            color: #e74c3c;
            font-weight: bold;
        }
        .fast-moving {
            color: #27ae60;
            font-weight: bold;
        }
        .normal {
            color: #3498db;
        }
        .stockout {
            color: #e74c3c;
            font-weight: bold;
            background-color: #ffebee;
        }
        .low-stock {
            color: #f39c12;
            font-weight: bold;
            background-color: #fff3e0;
        }
        .reorder {
            color: #f1c40f;
            font-weight: bold;
            background-color: #fffde7;
        }
        .excess-stock {
            color: #9b59b6;
            font-weight: bold;
            background-color: #f3e5f5;
        }
        .adequate {
            color: #27ae60;
            font-weight: bold;
            background-color: #e8f5e9;
        }
        .need-produce-yes {
            background-color: #d4edda; /* Light green for YES */
            color: #155724; /* Darker green text */
            font-weight: bold;
        }
        .need-produce-no {
            background-color: #f8d7da; /* Light red for NO */
            color: #721c24; /* Darker red text */
        }
        .date-filter {
            background-color: #f8f9fa;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
        }
        .date-filter label {
            margin-right: 10px;
            font-weight: bold;
        }
        .date-filter input {
            padding: 8px;
            border: 1px solid #ddd;
            border-radius: 4px;
            margin-right: 15px;
        }
        .date-filter button {
            padding: 8px 15px;
            background-color: #3498db;
            color: white;
            border: none;
            border-radius: 4px;
            cursor: pointer;
        }
        .error-message {
            background-color: #f8d7da;
            color: #721c24;
            padding: 15px;
            border-radius: 5px;
            margin-bottom: 20px;
            border: 1px solid #f5c6cb;
        }
        .export-btn {
            padding: 8px 15px;
            background-color: #2ecc71;
            color: white;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            text-decoration: none;
            display: inline-block;
        }
        .export-btn:hover {
            background-color: #27ae60;
        }
        .legend {
            display: flex;
            flex-wrap: wrap;
            gap: 15px;
            margin-top: 15px;
        }
        .legend-item {
            display: flex;
            align-items: center;
            margin-right: 10px;
        }
        .legend-color {
            width: 20px;
            height: 20px;
            margin-right: 5px;
            border: 1px solid #ddd;
        }
        .tab {
            overflow: hidden;
            border: 1px solid #ccc;
            background-color: #f1f1f1;
            border-radius: 5px 5px 0 0;
        }
        .tab button {
            background-color: inherit;
            float: left;
            border: none;
            outline: none;
            cursor: pointer;
            padding: 10px 16px;
            transition: 0.3s;
        }
        .tab button:hover {
            background-color: #ddd;
        }
        .tab button.active {
            background-color: #3498db;
            color: white;
        }
        .tabcontent {
            display: none;
            padding: 20px;
            border: 1px solid #ccc;
            border-top: none;
            border-radius: 0 0 5px 5px;
            background-color: white;
        }
        @media (max-width: 768px) {
            .inventory-table {
                font-size: 14px;
            }
            .tab button {
                padding: 8px 12px;
                font-size: 14px;
            }
        }
    </style>
</head>
<body>
    <div class="container">
        <div class="header">
            <h1>Inventory Analysis - All Items</h1>
            <div class="date-filter">
                <form method="GET" action="">
                    <label for="start_date">Start Date:</label>
                    <input type="date" name="start_date" value="<?= htmlspecialchars($start_date) ?>">
                    
                    <label for="end_date">End Date:</label>
                    <input type="date" name="end_date" value="<?= htmlspecialchars($end_date) ?>">
                    
                    <button type="submit">Filter</button>
                    <a href="?export=excel&start_date=<?= $start_date ?>&end_date=<?= $end_date ?>" class="export-btn">Export to Excel</a>
                </form>
                <p style="margin-top: 10px; font-size: 14px; color: #7f8c8d;">
                    Period: <?= htmlspecialchars($start_date) ?> to <?= htmlspecialchars($end_date) ?> 
                    (<?= $days_passed ?> days passed)
                </p>
            </div>
        </div>
        
        <?php if (!empty($error)): ?>
            <div class="error-message">
                <strong>Error:</strong> <?= htmlspecialchars($error) ?>
            </div>
        <?php endif; ?>
        
        <?php if (empty($error) && empty($inventory_data)): ?>
            <div class="card">
                <p>No inventory data found for this category.</p>
            </div>
        <?php else: ?>
            <div class="tab">
                <button class="tablinks active" onclick="openTab(event, 'summary')">Summary</button>
                <?php foreach ($warehouses as $location): ?>
                    <button class="tablinks" onclick="openTab(event, '<?= strtolower($location) ?>')"><?= $location ?></button>
                <?php endforeach; ?>
            </div>
            
            <div id="summary" class="tabcontent" style="display: block;">
                <div class="card-header">
                    <h2 class="card-title">Inventory Summary</h2>
                    <div>
                        <span class="slow-moving">Slow Moving (>60 days)</span> | 
                        <span class="normal">Normal (30-60 days)</span> | 
                        <span class="fast-moving">Fast Moving (<30 days)</span>
                    </div>
                </div>
                
                <div class="legend">
                    <div class="legend-item">
                        <div class="legend-color" style="background-color: #ffebee;"></div>
                        <span>Stockout</span>
                    </div>
                    <div class="legend-item">
                        <div class="legend-color" style="background-color: #fff3e0;"></div>
                        <span>Low Stock</span>
                    </div>
                    <div class="legend-item">
                        <div class="legend-color" style="background-color: #fffde7;"></div>
                        <span>Reorder</span>
                    </div>
                    <div class="legend-item">
                        <div class="legend-color" style="background-color: #e8f5e9;"></div>
                        <span>Adequate</span>
                    </div>
                    <div class="legend-item">
                        <div class="legend-color" style="background-color: #f3e5f5;"></div>
                        <span>Excess</span>
                    </div>
                     <div class="legend-item">
                        <div class="legend-color" style="background-color: #d4edda;"></div>
                        <span>Need Produce: Yes</span>
                    </div>
                     <div class="legend-item">
                        <div class="legend-color" style="background-color: #f8d7da;"></div>
                        <span>Need Produce: No</span>
                    </div>
                </div>
                
                <div style="overflow-x: auto;">
                    <table class="inventory-table">
                        <thead>
                            <tr>
                                <th>Part Number</th>
                                <th>Buy Price</th>
                                <th>Total Stock</th>
                                <th>Stock Value</th>
                                <th>Total Sales</th>
                                <th>Daily Sales Rate</th>
                                <th>Safety Stock</th>
                                <th>Reorder Point</th>
                                <th>Inventory Days</th>
                                <th>Stock Status</th>
                                <th>Movement Status</th>
                                <th>Need Produce</th> </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($inventory_data as $item): ?>
                                <tr>
                                    <td><?= htmlspecialchars($item['part_number']) ?></td>
                                    <td><?= number_format($item['buy_price'], 2) ?></td>
                                    <td><?= number_format($item['Total']) ?></td>
                                    <td><?= number_format($item['Amount'], 2) ?></td>
                                    <td><?= number_format($item['Total_sales']) ?></td>
                                    <td><?= number_format($item['Daily_sales_rate'], 2) ?></td>
                                    <td><?= number_format($item['Safety_stock']) ?></td>
                                    <td><?= number_format($item['Reorder_point']) ?></td>
                                    <td><?= number_format($item['Inventory_days'], 2) ?></td>
                                    <td class="<?= $item['Stock_status_class'] ?>"><?= $item['Stock_status'] ?></td>
                                    <td class="<?= $item['Status_Class'] ?>"><?= $item['Status'] ?></td>
                                    <td class="<?= ($item['Need_Produce'] == 'YES' ? 'need-produce-yes' : 'need-produce-no') ?>"><?= $item['Need_Produce'] ?></td> </tr>
                            <?php endforeach; ?>
                        </tbody>
                    </table>
                </div>
            </div>
            
            <?php foreach ($warehouses as $location): ?>
                <div id="<?= strtolower($location) ?>" class="tabcontent">
                    <div class="card-header">
                        <h2 class="card-title">Inventory Analysis - <?= $location ?></h2>
                        <div>
                            <span class="slow-moving">Slow Moving (>60 days)</span> | 
                            <span class="normal">Normal (30-60 days)</span> | 
                            <span class="fast-moving">Fast Moving (<30 days)</span>
                        </div>
                    </div>
                    
                    <div style="overflow-x: auto;">
                        <table class="inventory-table">
                            <thead>
                                <tr>
                                    <th>Part Number</th>
                                    <th>Buy Price</th>
                                    <th>Stock</th>
                                    <th>Stock Value</th>
                                    <th>Sales</th>
                                    <th>Daily Sales Rate</th>
                                    <th>Safety Stock</th>
                                    <th>Reorder Point</th>
                                    <th>Inventory Days</th>
                                    <th>Stock Status</th>
                                    <th>Movement Status</th>
                                    <th>Need Produce</th> </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($inventory_data as $item): 
                                    $loc_data = $warehouse_data[ucfirst($location)][$item['part_number']] ?? null; // Use ucfirst to match array key
                                    if ($loc_data):
                                ?>
                                    <tr>
                                        <td><?= htmlspecialchars($item['part_number']) ?></td>
                                        <td><?= number_format($loc_data['buy_price'], 2) ?></td>
                                        <td><?= number_format($loc_data['quantity']) ?></td>
                                        <td><?= number_format($loc_data['amount'], 2) ?></td>
                                        <td><?= number_format($loc_data['sales']) ?></td>
                                        <td><?= number_format($loc_data['daily_sales_rate'], 2) ?></td>
                                        <td><?= number_format($loc_data['safety_stock']) ?></td>
                                        <td><?= number_format($loc_data['reorder_point']) ?></td>
                                        <td><?= number_format($loc_data['inventory_days'], 2) ?></td>
                                        <td class="<?= $loc_data['stock_status_class'] ?>"><?= $loc_data['stock_status'] ?></td>
                                        <td class="<?= $loc_data['movement_status_class'] ?>"><?= $loc_data['movement_status'] ?></td>
                                        <td class="<?= ($loc_data['need_produce'] == 'YES' ? 'need-produce-yes' : 'need-produce-no') ?>"><?= $loc_data['need_produce'] ?></td> </tr>
                                <?php endif; endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            <?php endforeach; ?>
        <?php endif; ?>
    </div>

    <script>
        function openTab(evt, tabName) {
            var i, tabcontent, tablinks;
            
            // Hide all tab content
            tabcontent = document.getElementsByClassName("tabcontent");
            for (i = 0; i < tabcontent.length; i++) {
                tabcontent[i].style.display = "none";
            }
            
            // Remove active class from all tab buttons
            tablinks = document.getElementsByClassName("tablinks");
            for (i = 0; i < tablinks.length; i++) {
                tablinks[i].className = tablinks[i].className.replace(" active", "");
            }
            
            // Show the current tab and add active class to the button
            document.getElementById(tabName).style.display = "block";
            evt.currentTarget.className += " active";
        }
    </script>
</body>
</html>