<?php
$page_title = 'Monthly Production Output Analysis';
require_once('includes/load.php');
require '/home/wiselie/public_html/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\Exception;
use PHPMailer\PHPMailer\SMTP;

page_require_level(3);
ob_start();
include_once('layouts/header.php');

// Get monthly production output data (category 7 - Finished Products)
$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 = [];

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

// Calculate monthly changes
$changes = [];
$total_increase = 0;
$total_decrease = 0;

foreach ($monthly_data as $index => $month) {
    $current_total = $month['total_quantity'];
    $previous_total = $index > 0 ? $monthly_data[$index - 1]['total_quantity'] : null;

    $change = [
        'month' => $month['month'],
        'total' => $current_total,
        'change' => 0,
        'percentage' => 0,
        'trend' => 'equal'
    ];

    if ($previous_total !== null) {
        $change_amount = $current_total - $previous_total;
        $change['change'] = $change_amount;

        if ($previous_total > 0) {
            $change['percentage'] = round(($change_amount / $previous_total) * 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 changes
$overall_change = 0;
$overall_percentage = 0;
$lowest_month = null;
$highest_month = null;

if (count($monthly_data) > 1) {
    $lowest = $monthly_data[0];
    $highest = $monthly_data[0];
    
    foreach ($monthly_data as $month) {
        if ($month['total_quantity'] < $lowest['total_quantity']) {
            $lowest = $month;
        }
        if ($month['total_quantity'] > $highest['total_quantity']) {
            $highest = $month;
        }
    }
    
    $lowest_month = $lowest;
    $highest_month = $highest;
    
    $overall_change = $highest_month['total_quantity'] - $lowest_month['total_quantity'];
    if ($lowest_month['total_quantity'] > 0) {
        $overall_percentage = round(($overall_change / $lowest_month['total_quantity']) * 100, 2);
    }
}

$chart_json = json_encode($monthly_data);

// Process email sending
if (isset($_POST['send_email'])) {
    $recipient = 'Joswiselie@gmail.com';
    
    try {
        $mail = new PHPMailer(true);
        
        // Enable verbose debug output (level 2)
        $mail->SMTPDebug = SMTP::DEBUG_SERVER;
        
        // SMTP Configuration
        $mail->isSMTP();
        $mail->Host = 'mail.wiselie.co.id';
        $mail->SMTPAuth = true;
        $mail->Username = 'nanda@wiselie.co.id';
        $mail->Password = 'Clemiraputra123';
        $mail->SMTPSecure = PHPMailer::ENCRYPTION_SMTPS;
        $mail->Port = 465;
        
        // Timeout settings
        $mail->Timeout = 30;
        $mail->SMTPKeepAlive = true;
        
        // Recipients
        $mail->setFrom('nanda@wiselie.co.id', 'Production Reporting');
        $mail->addAddress($recipient);
        $mail->addReplyTo('no-reply@wiselie.co.id', 'No Reply');
        
        // Content
        $mail->isHTML(true);
        $mail->Subject = 'Monthly Production Output Report - ' . date('F Y');
        
        // Build HTML email content
        $email_content = '
        <!DOCTYPE html>
        <html>
        <head>
            <style>
                body { font-family: Arial, sans-serif; line-height: 1.6; }
                .header { background-color: #f8f9fa; padding: 20px; text-align: center; }
                .summary { margin: 20px 0; padding: 15px; background-color: #e9ecef; border-radius: 5px; }
                table { width: 100%; border-collapse: collapse; margin: 20px 0; }
                th { background-color: #343a40; color: white; padding: 10px; text-align: left; }
                td { padding: 8px; border-bottom: 1px solid #ddd; }
                .up { color: green; }
                .down { color: red; }
                .equal { color: blue; }
            </style>
        </head>
        <body>
            <div class="header">
                <h2>Monthly Production Output Report</h2>
                <p>Generated on: ' . date('Y-m-d H:i:s') . '</p>
            </div>
            
            <div class="summary">
                <h3>Production Summary</h3>
                <p><strong>Period:</strong> ' . date('F Y', strtotime($monthly_data[0]['month'] . '-01')) . ' to ' . date('F Y', strtotime(end($monthly_data)['month'] . '-01')) . '</p>
                <p><strong>Total Months Recorded:</strong> ' . count($monthly_data) . '</p>
                <p><strong>Total Increase:</strong> <span class="up">+' . number_format($total_increase) . '</span></p>
                <p><strong>Total Decrease:</strong> <span class="down">-' . number_format($total_decrease) . '</span></p>
                <p><strong>Overall Change:</strong> <span class="' . ($overall_change >= 0 ? 'up">+' : 'down">-') . number_format(abs($overall_change)) . ' (' . ($overall_percentage >= 0 ? '+' : '-') . abs($overall_percentage) . '%)</span></p>
            </div>
            
            <h3>Monthly Production Details</h3>
            <table>
                <tr>
                    <th>Month</th>
                    <th>Output Quantity</th>
                    <th>Change</th>
                    <th>Percentage</th>
                    <th>Trend</th>
                </tr>';
        
        foreach ($changes as $change) {
            $email_content .= '
                <tr>
                    <td>' . date('F Y', strtotime($change['month'] . '-01')) . '</td>
                    <td>' . number_format($change['total']) . '</td>
                    <td class="' . $change['trend'] . '">' . 
                        ($change['trend'] !== 'equal' ? ($change['trend'] === 'up' ? '+' : '-') . number_format(abs($change['change'])) : '-') . 
                    '</td>
                    <td class="' . $change['trend'] . '">' . 
                        ($change['trend'] !== 'equal' ? ($change['trend'] === 'up' ? '+' : '-') . abs($change['percentage']) . '%' : '-') . 
                    '</td>
                    <td class="' . $change['trend'] . '">' . 
                        ($change['trend'] === 'up' ? '↑' : ($change['trend'] === 'down' ? '↓' : '→')) . 
                    '</td>
                </tr>';
        }
        
        $email_content .= '
            </table>
            <p>This report was automatically generated by the Wiselie Production System.</p>
            </body>
            </html>';
        
        $mail->Body = $email_content;
        $mail->AltBody = strip_tags(str_replace(['<br>', '<br/>', '<br />'], "\n", $email_content));
        
        // Try to send email
        if ($mail->send()) {
            $session->msg('s', 'Report has been sent successfully to ' . $recipient);
        } else {
            $session->msg('d', 'Failed to send email. Error: ' . $mail->ErrorInfo);
        }
        
    } catch (Exception $e) {
        error_log('Email sending failed: ' . $e->getMessage());
        $session->msg('d', 'Message could not be sent. Error: ' . $e->getMessage());
    }
    
    redirect('production_output_chart.php', false);
}
?>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading clearfix">
                <h3 class="panel-title">Monthly Production Output (Finished Products)</h3>
                <div class="pull-right">
                    <form method="post" style="display:inline;">
                        <button type="submit" name="send_email" class="btn btn-primary">
                            <i class="glyphicon glyphicon-envelope"></i> Email This Report
                        </button>
                    </form>
                </div>
            </div>
            <div class="panel-body">
                <canvas id="productionChart" height="100"></canvas>
            </div>
        </div>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">Monthly Production Changes</h3>
            </div>
            <div class="panel-body">
                <table class="table table-bordered table-striped">
                    <thead>
                        <tr>
                            <th>Month</th>
                            <th>Output Quantity</th>
                            <th>Change from Previous</th>
                            <th>Percentage Change</th>
                            <th>Trend</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($changes as $change): ?>
                            <tr>
                                <td><?php echo date('F Y', strtotime($change['month'] . '-01')); ?></td>
                                <td><?php echo number_format($change['total']); ?></td>
                                <td class="<?php echo $change['trend'] === 'up' ? 'text-success' : ($change['trend'] === 'down' ? 'text-danger' : ''); ?>">
                                    <?php echo $change['trend'] !== 'equal' ? ($change['trend'] === 'up' ? '+' : '-') . number_format(abs($change['change'])) : '-'; ?>
                                </td>
                                <td class="<?php echo $change['trend'] === 'up' ? 'text-success' : ($change['trend'] === 'down' ? 'text-danger' : ''); ?>">
                                    <?php echo $change['trend'] !== 'equal' ? ($change['trend'] === 'up' ? '+' : '-') . abs($change['percentage']) . '%' : '-'; ?>
                                </td>
                                <td class="text-center">
                                    <?php if ($change['trend'] === 'up'): ?>
                                        <i class="glyphicon glyphicon-arrow-up text-success"></i>
                                    <?php elseif ($change['trend'] === 'down'): ?>
                                        <i class="glyphicon glyphicon-arrow-down text-danger"></i>
                                    <?php else: ?>
                                        <i class="glyphicon glyphicon-minus"></i>
                                    <?php endif; ?>
                                </td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<div class="row">
    <div class="col-md-12">
        <div class="panel panel-default">
            <div class="panel-heading">
                <h3 class="panel-title">Production Performance Summary</h3>
            </div>
            <div class="panel-body">
                <div class="row">
                    <div class="col-md-4">
                        <div class="info-box">
                            <span class="info-box-icon bg-blue"><i class="glyphicon glyphicon-calendar"></i></span>
                            <div class="info-box-content">
                                <span class="info-box-text">Reporting Period</span>
                                <span class="info-box-number">
                                    <?php echo date('M Y', strtotime($monthly_data[0]['month'] . '-01')); ?> to 
                                    <?php echo date('M Y', strtotime(end($monthly_data)['month'] . '-01')); ?>
                                </span>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-4">
                        <div class="info-box">
                            <span class="info-box-icon bg-green"><i class="glyphicon glyphicon-arrow-up"></i></span>
                            <div class="info-box-content">
                                <span class="info-box-text">Highest Output Month</span>
                                <span class="info-box-number">
                                    <?php echo $highest_month ? date('M Y', strtotime($highest_month['month'] . '-01')) . ': ' . number_format($highest_month['total_quantity']) : 'N/A'; ?>
                                </span>
                            </div>
                        </div>
                    </div>
                    <div class="col-md-4">
                        <div class="info-box">
                            <span class="info-box-icon bg-red"><i class="glyphicon glyphicon-arrow-down"></i></span>
                            <div class="info-box-content">
                                <span class="info-box-text">Lowest Output Month</span>
                                <span class="info-box-number">
                                    <?php echo $lowest_month ? date('M Y', strtotime($lowest_month['month'] . '-01')) . ': ' . number_format($lowest_month['total_quantity']) : 'N/A'; ?>
                                </span>
                            </div>
                        </div>
                    </div>
                </div>

                <div class="row">
                    <div class="col-md-12">
                        <div class="callout <?php echo $overall_change >= 0 ? 'callout-success' : 'callout-danger'; ?>">
                            <h4>Overall Production Trend</h4>
                            <p>
                                From <?php echo date('F Y', strtotime($monthly_data[0]['month'] . '-01')); ?> to
                                <?php echo date('F Y', strtotime(end($monthly_data)['month'] . '-01')); ?>:
                                <strong class="<?php echo $overall_change >= 0 ? 'text-success' : 'text-danger'; ?>">
                                    <?php echo ($overall_change >= 0 ? '+' : '-') . number_format(abs($overall_change)); ?>
                                    (<?php echo ($overall_change >= 0 ? '+' : '-') . abs($overall_percentage); ?>%)
                                </strong>
                            </p>
                            <p>
                                <?php if ($overall_change > 0): ?>
                                    <i class="glyphicon glyphicon-arrow-up text-success"></i> Overall production has increased during this period.
                                <?php elseif ($overall_change < 0): ?>
                                    <i class="glyphicon glyphicon-arrow-down text-danger"></i> Overall production has decreased during this period.
                                <?php else: ?>
                                    <i class="glyphicon glyphicon-minus text-info"></i> Production has remained stable during this period.
                                <?php endif; ?>
                            </p>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
    const productionData = <?php echo $chart_json; ?>;

    const monthLabels = productionData.map(item => {
        const [year, month] = item.month.split('-');
        return new Date(year, month - 1).toLocaleDateString('en-US', { month: 'short', year: 'numeric' });
    });

    const productionValues = productionData.map(item => item.total_quantity);

    const ctx = document.getElementById('productionChart').getContext('2d');
    const productionChart = new Chart(ctx, {
        type: 'bar',
        data: {
            labels: monthLabels,
            datasets: [{
                label: 'Monthly Production Output (Finished Products)',
                data: productionValues,
                backgroundColor: 'rgba(54, 162, 235, 0.7)',
                borderColor: 'rgba(54, 162, 235, 1)',
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            scales: {
                y: {
                    beginAtZero: true,
                    title: { 
                        display: true, 
                        text: 'Output Quantity',
                        font: {
                            weight: 'bold'
                        }
                    },
                    ticks: {
                        callback: function(value) {
                            return value.toLocaleString();
                        }
                    }
                },
                x: {
                    title: { 
                        display: true, 
                        text: 'Month',
                        font: {
                            weight: 'bold'
                        }
                    }
                }
            },
            plugins: {
                tooltip: {
                    callbacks: {
                        label: function(context) {
                            return `${context.dataset.label}: ${context.raw.toLocaleString()}`;
                        }
                    }
                },
                legend: {
                    position: 'top',
                    labels: {
                        font: {
                            size: 14
                        }
                    }
                }
            }
        }
    });
</script>

<?php include_once('layouts/footer.php'); ?>