<?php
$page_title = 'Monthly Production Analysis (Incoming Goods)';
require_once('includes/load.php');
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use TCPDF as PDF;

// Check if PDF export is requested
if (isset($_GET['export_pdf'])) {
    // Get company information
    $company = find_by_id('company', (int)1);
    
    // Get monthly production data from incoming_goods for category_id 7
    $monthly_query = "
        SELECT 
            DATE_FORMAT(arrival_date, '%Y-%m') AS month,
            SUM(quantity) AS total_quantity
        FROM incoming_goods
        WHERE categorie_id = 7
        GROUP BY DATE_FORMAT(arrival_date, '%Y-%m')
        ORDER BY month ASC";

    $monthly_result = $db->query($monthly_query);
    $monthly_data = array();
    while($row = $monthly_result->fetch_assoc()) {
        $monthly_data[] = $row;
    }

    // Calculate month-to-month changes
    $changes = array();
    $previous_total = null;
    $total_increase = 0;
    $total_decrease = 0;

    foreach ($monthly_data as $index => $month) {
        $change = array(
            'month' => $month['month'],
            'total' => $month['total_quantity'],
            'change' => 0,
            'percentage' => 0,
            'trend' => 'equal'
        );
        
        if ($index > 0) {
            $change_amount = $month['total_quantity'] - $monthly_data[$index-1]['total_quantity'];
            $change['change'] = $change_amount;
            
            if ($monthly_data[$index-1]['total_quantity'] > 0) {
                $change['percentage'] = round(($change_amount / $monthly_data[$index-1]['total_quantity']) * 100, 2);
            }
            
            if ($change_amount > 0) {
                $change['trend'] = 'up';
                $total_increase += $change_amount;
            } elseif ($change_amount < 0) {
                $change['trend'] = 'down';
                $total_decrease += abs($change_amount);
            }
        }
        
        $changes[] = $change;
    }

    // Calculate overall change
    $overall_change = 0;
    $overall_percentage = 0;
    if (count($monthly_data) > 1) {
        $first_month = $monthly_data[0]['total_quantity'];
        $last_month = end($monthly_data)['total_quantity'];
        $overall_change = $last_month - $first_month;
        if ($first_month > 0) {
            $overall_percentage = round(($overall_change / $first_month) * 100, 2);
        }
    }

    // Create new PDF document
    $pdf = new PDF('P', 'mm', 'A4', true, 'UTF-8', false);
    
    // Set document information
    $pdf->SetCreator(PDF_CREATOR);
    $pdf->SetAuthor($company['name']);
    $pdf->SetTitle('Monthly Production Analysis Report');
    $pdf->SetSubject('Category 7 Production Analysis');
    $pdf->SetKeywords('Production, Analysis, Monthly, Report');
    
    // Set default header data with company logo
    $logo = 'uploads/company/'.$company['logo'];
    $logo_exists = is_file($logo) ? $logo : '';
    $pdf->SetHeaderData($logo_exists, 15, $company['name'], "Monthly Production Analysis Report\n".date('F j, Y'));
    
    // Set header and footer fonts
    $pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
    $pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));
    
    // Set margins
    $pdf->SetMargins(15, 35, 15);
    $pdf->SetHeaderMargin(10);
    $pdf->SetFooterMargin(10);
    
    // Set auto page breaks
    $pdf->SetAutoPageBreak(TRUE, 25);
    
    // Add a page
    $pdf->AddPage();
    
    // Add title with styling
    $pdf->SetFont('helvetica', 'B', 16);
    $pdf->SetTextColor(0, 51, 102); // Dark blue color
    $pdf->Cell(0, 10, 'MONTHLY PRODUCTION ANALYSIS REPORT', 0, 1, 'C');
    $pdf->SetFont('helvetica', '', 12);
    $pdf->Cell(0, 5, 'Category 7 - Incoming Goods', 0, 1, 'C');
    $pdf->Ln(10);
    
    // Report period
    $pdf->SetFont('helvetica', 'B', 12);
    $pdf->SetTextColor(0); // Black color
    $pdf->Cell(0, 6, 'Report Period:', 0, 1);
    $pdf->SetFont('helvetica', '', 12);
    $pdf->Cell(0, 6, date('F Y', strtotime($monthly_data[0]['month'].'-01')) . ' to ' . date('F Y', strtotime(end($monthly_data)['month'].'-01')), 0, 1);
    $pdf->Ln(8);
    
    // Key Highlights section
    $pdf->SetFont('helvetica', 'B', 14);
    $pdf->SetFillColor(240, 240, 240);
    $pdf->SetTextColor(0, 51, 102);
    $pdf->Cell(0, 8, 'KEY HIGHLIGHTS', 0, 1, 'L', true);
    $pdf->Ln(4);
    
    $pdf->SetFont('helvetica', '', 12);
    $pdf->SetTextColor(0);
    
    // Highlight boxes
    $pdf->SetFillColor(230, 247, 255); // Light blue background
    $pdf->SetDrawColor(0, 102, 204); // Blue border
    
    // First row of highlights
    $pdf->Cell(60, 25, '', 1, 0, 'C', true, '', 1);
    $pdf->Cell(60, 25, '', 1, 0, 'C', true, '', 1);
    $pdf->Cell(60, 25, '', 1, 1, 'C', true, '', 1);
    
    // Position text inside boxes
    $pdf->SetXY(15, $pdf->GetY() - 25);
    $pdf->SetFont('helvetica', 'B', 12);
    $pdf->Cell(60, 6, 'Total Months Recorded', 0, 2, 'C');
    $pdf->SetFont('helvetica', 'B', 16);
    $pdf->Cell(60, 6, count($monthly_data), 0, 2, 'C');
    
    $pdf->SetXY(75, $pdf->GetY() - 12);
    $pdf->SetFont('helvetica', 'B', 12);
    $pdf->Cell(60, 6, 'Total Increase', 0, 2, 'C');
    $pdf->SetFont('helvetica', 'B', 16);
    $pdf->SetTextColor(0, 128, 0); // Green color
    $pdf->Cell(60, 6, '+'.number_format($total_increase), 0, 2, 'C');
    
    $pdf->SetXY(135, $pdf->GetY() - 12);
    $pdf->SetFont('helvetica', 'B', 12);
    $pdf->SetTextColor(0); // Black color
    $pdf->Cell(60, 6, 'Total Decrease', 0, 2, 'C');
    $pdf->SetFont('helvetica', 'B', 16);
    $pdf->SetTextColor(255, 0, 0); // Red color
    $pdf->Cell(60, 6, '-'.number_format($total_decrease), 0, 2, 'C');
    
    $pdf->SetTextColor(0); // Reset to black
    $pdf->Ln(15);
    
    // Overall performance
    $pdf->SetFont('helvetica', 'B', 12);
    $pdf->Cell(0, 6, 'Overall Performance:', 0, 1);
    $pdf->SetFont('helvetica', '', 12);
    
    $overall_text = sprintf(
        "From %s to %s, production %s by %s (%s%%)",
        date('F Y', strtotime($monthly_data[0]['month'].'-01')),
        date('F Y', strtotime(end($monthly_data)['month'].'-01')),
        $overall_change >= 0 ? 'increased' : 'decreased',
        number_format(abs($overall_change)),
        ($overall_change >= 0 ? '+' : '-') . abs($overall_percentage)
    );
    
    $pdf->MultiCell(0, 6, $overall_text, 0, 'L');
    $pdf->Ln(10);
    
    // Monthly Trends section
    $pdf->SetFont('helvetica', 'B', 14);
    $pdf->SetFillColor(240, 240, 240);
    $pdf->SetTextColor(0, 51, 102);
    $pdf->Cell(0, 8, 'MONTHLY TRENDS', 0, 1, 'L', true);
    $pdf->Ln(4);
    
    // Monthly data table
    $pdf->SetFont('helvetica', '', 10);
    
    // Table header
    $pdf->SetFillColor(0, 51, 102);
    $pdf->SetTextColor(255);
    $pdf->SetDrawColor(0, 0, 0);
    $pdf->SetLineWidth(0.1);
    $pdf->SetFont('', 'B');
    
    $header = array('Month', 'Total Input', 'Change', 'Percentage', 'Trend');
    $w = array(40, 40, 40, 40, 30);
    
    for ($i = 0; $i < count($header); $i++) {
        $pdf->Cell($w[$i], 7, $header[$i], 1, 0, 'C', true);
    }
    $pdf->Ln();
    
    // Table content
    $pdf->SetFont('');
    $pdf->SetFillColor(255, 255, 255);
    $pdf->SetTextColor(0);
    
    foreach ($changes as $change) {
        // Month
        $pdf->Cell($w[0], 6, date('F Y', strtotime($change['month'].'-01')), 'LR', 0, 'L');
        
        // Total Input
        $pdf->Cell($w[1], 6, number_format($change['total']), 'LR', 0, 'R');
        
        // Change
        $change_text = ($change['trend'] != 'equal') ? 
                      (($change['trend'] == 'up' ? '+' : '-') . number_format(abs($change['change']))) : '-';
        $pdf->SetTextColor($change['trend'] == 'up' ? 0 : ($change['trend'] == 'down' ? 255 : 0), 
                          $change['trend'] == 'up' ? 128 : 0, 
                          $change['trend'] == 'up' ? 0 : 0);
        $pdf->Cell($w[2], 6, $change_text, 'LR', 0, 'R');
        $pdf->SetTextColor(0);
        
        // Percentage
        $percentage_text = ($change['trend'] != 'equal') ? 
                          (($change['trend'] == 'up' ? '+' : '-') . abs($change['percentage']) . '%' : '-';
        $pdf->SetTextColor($change['trend'] == 'up' ? 0 : ($change['trend'] == 'down' ? 255 : 0), 
                          $change['trend'] == 'up' ? 128 : 0, 
                          $change['trend'] == 'up' ? 0 : 0);
        $pdf->Cell($w[3], 6, $percentage_text, 'LR', 0, 'R');
        $pdf->SetTextColor(0);
        
        // Trend
        $trend_symbol = '';
        if ($change['trend'] == 'up') {
            $trend_symbol = '▲';
            $pdf->SetTextColor(0, 128, 0); // Green
        } elseif ($change['trend'] == 'down') {
            $trend_symbol = '▼';
            $pdf->SetTextColor(255, 0, 0); // Red
        } else {
            $trend_symbol = '-';
            $pdf->SetTextColor(0); // Black
        }
        $pdf->Cell($w[4], 6, $trend_symbol, 'LR', 0, 'C');
        $pdf->SetTextColor(0);
        
        $pdf->Ln();
    }
    
    // Closing line
    $pdf->Cell(array_sum($w), 0, '', 'T');
    $pdf->Ln(15);
    
    // Analysis Notes section
    $pdf->SetFont('helvetica', 'B', 14);
    $pdf->SetFillColor(240, 240, 240);
    $pdf->SetTextColor(0, 51, 102);
    $pdf->Cell(0, 8, 'ANALYSIS NOTES', 0, 1, 'L', true);
    $pdf->Ln(4);
    
    $pdf->SetFont('helvetica', '', 10);
    $pdf->SetTextColor(0);
    
    $notes = array(
        "This report analyzes the monthly production input for Category 7 goods.",
        "The data covers the period from " . date('F Y', strtotime($monthly_data[0]['month'].'-01')) . " to " . date('F Y', strtotime(end($monthly_data)['month'].'-01')) . ".",
        "Key trends and changes are highlighted in the monthly trends table.",
        "Green arrows indicate positive growth, while red arrows indicate declines.",
        "For detailed analysis, please refer to the complete dataset."
    );
    
    foreach ($notes as $note) {
        $pdf->Cell(0, 6, '• ' . $note, 0, 1);
    }
    
    // Footer with disclaimer
    $pdf->Ln(10);
    $pdf->SetFont('helvetica', 'I', 8);
    $pdf->SetTextColor(100, 100, 100);
    $pdf->MultiCell(0, 4, "Disclaimer: This report is generated automatically based on available data. For any discrepancies or questions, please contact the production department.", 0, 'C');
    
    // Output the PDF
    $pdf->Output('Production_Analysis_Report_'.date('Ymd').'.pdf', 'D');
    exit;
}

// Rest of your original code for the HTML page remains the same
page_require_level(3);
ob_start();
include_once('layouts/header.php');

// Get monthly production data from incoming_goods for category_id 7
$monthly_query = "
    SELECT 
        DATE_FORMAT(arrival_date, '%Y-%m') AS month,
        SUM(quantity) AS total_quantity
    FROM incoming_goods
    WHERE categorie_id = 7
    GROUP BY DATE_FORMAT(arrival_date, '%Y-%m')
    ORDER BY month ASC";

$monthly_result = $db->query($monthly_query);
$monthly_data = array();
while($row = $monthly_result->fetch_assoc()) {
    $monthly_data[] = $row;
}

// Calculate month-to-month changes
$changes = array();
$previous_total = null;
$total_increase = 0;
$total_decrease = 0;

foreach ($monthly_data as $index => $month) {
    $change = array(
        'month' => $month['month'],
        'total' => $month['total_quantity'],
        'change' => 0,
        'percentage' => 0,
        'trend' => 'equal'
    );
    
    if ($index > 0) {
        $change_amount = $month['total_quantity'] - $monthly_data[$index-1]['total_quantity'];
        $change['change'] = $change_amount;
        
        if ($monthly_data[$index-1]['total_quantity'] > 0) {
            $change['percentage'] = round(($change_amount / $monthly_data[$index-1]['total_quantity']) * 100, 2);
        }
        
        if ($change_amount > 0) {
            $change['trend'] = 'up';
            $total_increase += $change_amount;
        } elseif ($change_amount < 0) {
            $change['trend'] = 'down';
            $total_decrease += abs($change_amount);
        }
    }
    
    $changes[] = $change;
}

// Calculate overall change
$overall_change = 0;
$overall_percentage = 0;
if (count($monthly_data) > 1) {
    $first_month = $monthly_data[0]['total_quantity'];
    $last_month = end($monthly_data)['total_quantity'];
    $overall_change = $last_month - $first_month;
    if ($first_month > 0) {
        $overall_percentage = round(($overall_change / $first_month) * 100, 2);
    }
}

$chart_json = json_encode($monthly_data);
?>

<!-- Rest of your HTML remains exactly the same -->