<?php
/**
 * RUHONDO BEACH RESORT HMS + POS System
 * Inventory Valuation Report
 * Developer: JOACHIM'S INNOTECH
 */

// Initialize system
require_once dirname(__DIR__, 2) . '/config/config.php';
require_once dirname(__DIR__, 2) . '/config/constants.php';
require_once dirname(__DIR__, 2) . '/config/database.php';
require_once dirname(__DIR__, 2) . '/config/helpers.php';
require_once dirname(__DIR__, 2) . '/config/auth.php';
require_once dirname(__DIR__, 2) . '/config/csrf.php';

// Require authentication and permission
require_auth();
require_permission('reports.view');

// Set page title
$page_title = 'Inventory Valuation';

// Get current user
$current_user = current_user();

// Get report parameters
$report_type = $_GET['type'] ?? 'summary';
$date_as_of = $_GET['date_as_of'] ?? date('Y-m-d');
$category_id = isset($_GET['category_id']) && $_GET['category_id'] !== '' ? (int)$_GET['category_id'] : null;
$export = $_GET['export'] ?? '';

// Get categories for filter
$categories = db_fetch_all("SELECT category_id, category_name FROM pos_categories WHERE is_active = 1 ORDER BY category_name");

// Get report data based on type
switch ($report_type) {
    case 'summary':
        $report_data = get_valuation_summary($date_as_of, $category_id);
        break;
    case 'movements':
        $report_data = get_movement_analysis($date_as_of, $category_id);
        break;
    case 'low-stock':
        $report_data = get_low_stock_report($date_as_of, $category_id);
        break;
    case 'turnover':
        $report_data = get_turnover_report($date_as_of, $category_id);
        break;
    default:
        $report_data = get_valuation_summary($date_as_of, $category_id);
}

// Handle export
if ($export === 'pdf') {
    export_pdf($report_data, $report_type, $date_as_of);
} elseif ($export === 'excel') {
    export_excel($report_data, $report_type, $date_as_of);
} elseif ($export === 'csv') {
    export_csv($report_data, $report_type, $date_as_of);
}

// Include header
require_once dirname(__DIR__, 2) . '/views/partials/header.php';
?>

<!-- Content Header -->
<div class="content-header">
    <div class="container-fluid">
        <div class="row mb-2">
            <div class="col-sm-6">
                <h1 class="m-0">Inventory Valuation</h1>
            </div>
            <div class="col-sm-6">
                <ol class="breadcrumb float-sm-right">
                    <li class="breadcrumb-item"><a href="<?php echo base_url('dashboard.php'); ?>">Home</a></li>
                    <li class="breadcrumb-item"><a href="dashboard.php">Reports</a></li>
                    <li class="breadcrumb-item active">Inventory</li>
                </ol>
            </div>
        </div>
    </div>
</div>

<!-- Main content -->
<section class="content">
    <div class="container-fluid">
        <!-- Report Controls -->
        <div class="card">
            <div class="card-body">
                <form method="GET" class="form-inline">
                    <div class="form-group mb-2 mr-2">
                        <label for="report_type" class="mr-2">Report Type:</label>
                        <select class="form-control" id="report_type" name="type">
                            <option value="summary" <?php echo $report_type == 'summary' ? 'selected' : ''; ?>>Valuation Summary</option>
                            <option value="movements" <?php echo $report_type == 'movements' ? 'selected' : ''; ?>>Movement Analysis</option>
                            <option value="low-stock" <?php echo $report_type == 'low-stock' ? 'selected' : ''; ?>>Low Stock Alert</option>
                            <option value="turnover" <?php echo $report_type == 'turnover' ? 'selected' : ''; ?>>Turnover Rate</option>
                        </select>
                    </div>
                    
                    <div class="form-group mb-2 mr-2">
                        <label for="date_as_of" class="mr-2">As of Date:</label>
                        <input type="date" class="form-control" id="date_as_of" name="date_as_of" 
                               value="<?php echo $date_as_of; ?>">
                    </div>
                    
                    <div class="form-group mb-2 mr-2">
                        <label for="category_id" class="mr-2">Category:</label>
                        <select class="form-control" id="category_id" name="category_id">
                            <option value="">All Categories</option>
                            <?php foreach ($categories as $cat): ?>
                                <option value="<?php echo $cat['category_id']; ?>" 
                                        <?php echo $category_id == $cat['category_id'] ? 'selected' : ''; ?>>
                                    <?php echo $cat['category_name']; ?>
                                </option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    
                    <button type="submit" class="btn btn-primary mb-2 mr-2">
                        <i class="fas fa-sync-alt"></i> Generate
                    </button>
                    
                    <button type="button" class="btn btn-success mb-2 mr-2" onclick="exportReport('pdf')">
                        <i class="fas fa-file-pdf"></i> PDF
                    </button>
                    
                    <button type="button" class="btn btn-info mb-2 mr-2" onclick="exportReport('excel')">
                        <i class="fas fa-file-excel"></i> Excel
                    </button>
                    
                    <button type="button" class="btn btn-secondary mb-2" onclick="exportReport('csv')">
                        <i class="fas fa-file-csv"></i> CSV
                    </button>
                </form>
            </div>
        </div>

        <!-- Summary Cards -->
        <div class="row">
            <div class="col-lg-3 col-6">
                <div class="small-box bg-info">
                    <div class="inner">
                        <h3><?php echo $report_data['summary']['total_products']; ?></h3>
                        <p>Total Products</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-boxes"></i>
                    </div>
                </div>
            </div>
            
            <div class="col-lg-3 col-6">
                <div class="small-box bg-success">
                    <div class="inner">
                        <h3><?php echo $report_data['summary']['total_units']; ?></h3>
                        <p>Total Units</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-cubes"></i>
                    </div>
                </div>
            </div>
            
            <div class="col-lg-3 col-6">
                <div class="small-box bg-warning">
                    <div class="inner">
                        <h3><?php echo format_currency($report_data['summary']['total_value']); ?></h3>
                        <p>Total Value</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-dollar-sign"></i>
                    </div>
                </div>
            </div>
            
            <div class="col-lg-3 col-6">
                <div class="small-box bg-danger">
                    <div class="inner">
                        <h3><?php echo $report_data['summary']['low_stock_items']; ?></h3>
                        <p>Low Stock Items</p>
                    </div>
                    <div class="icon">
                        <i class="fas fa-exclamation-triangle"></i>
                    </div>
                </div>
            </div>
        </div>

        <!-- Charts -->
        <div class="row">
            <div class="col-md-6">
                <div class="card">
                    <div class="card-header">
                        <h3 class="card-title">
                            <i class="fas fa-chart-pie mr-1"></i>
                            Value by Category
                        </h3>
                    </div>
                    <div class="card-body">
                        <canvas id="categoryChart" style="height: 300px;"></canvas>
                    </div>
                </div>
            </div>
            
            <div class="col-md-6">
                <div class="card">
                    <div class="card-header">
                        <h3 class="card-title">
                            <i class="fas fa-chart-bar mr-1"></i>
                            Top 10 Products by Value
                        </h3>
                    </div>
                    <div class="card-body">
                        <canvas id="topProductsChart" style="height: 300px;"></canvas>
                    </div>
                </div>
            </div>
        </div>

        <!-- Data Table -->
        <div class="row">
            <div class="col-12">
                <div class="card">
                    <div class="card-header">
                        <h3 class="card-title">
                            <i class="fas fa-table mr-1"></i>
                            <?php echo ucfirst(str_replace('_', ' ', $report_type)); ?> Data
                        </h3>
                    </div>
                    
                    <div class="card-body p-0">
                        <div class="table-responsive">
                            <table class="table table-striped">
                                <thead>
                                    <tr>
                                        <?php foreach ($report_data['headers'] as $header): ?>
                                            <th><?php echo $header; ?></th>
                                        <?php endforeach; ?>
                                    </tr>
                                </thead>
                                <tbody>
                                    <?php foreach ($report_data['rows'] as $row): ?>
                                    <tr class="<?php echo isset($row['status']) && $row['status'] == 'low' ? 'table-warning' : ''; ?>">
                                        <?php foreach ($row as $cell): ?>
                                            <td><?php echo $cell; ?></td>
                                        <?php endforeach; ?>
                                    </tr>
                                    <?php endforeach; ?>
                                </tbody>
                                <?php if (!empty($report_data['totals'])): ?>
                                <tfoot>
                                    <tr class="table-active">
                                        <?php foreach ($report_data['totals'] as $total): ?>
                                            <td><strong><?php echo $total; ?></strong></td>
                                        <?php endforeach; ?>
                                    </tr>
                                </tfoot>
                                <?php endif; ?>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</section>

<!-- Chart.js -->
<script src="https://cdn.jsdelivr.net/npm/chart.js@4.3.0/dist/chart.umd.min.js"></script>

<script>
$(document).ready(function() {
    // Category Chart
    const categoryCtx = document.getElementById('categoryChart').getContext('2d');
    
    new Chart(categoryCtx, {
        type: 'doughnut',
        data: {
            labels: <?php echo json_encode($report_data['category_chart']['labels']); ?>,
            datasets: [{
                data: <?php echo json_encode($report_data['category_chart']['data']); ?>,
                backgroundColor: ['#007bff', '#28a745', '#ffc107', '#dc3545', '#17a2b8', '#6c757d'],
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: {
                    position: 'bottom'
                }
            }
        }
    });
    
    // Top Products Chart
    const topCtx = document.getElementById('topProductsChart').getContext('2d');
    
    new Chart(topCtx, {
        type: 'bar',
        data: {
            labels: <?php echo json_encode($report_data['top_products']['labels']); ?>,
            datasets: [{
                label: 'Value',
                data: <?php echo json_encode($report_data['top_products']['data']); ?>,
                backgroundColor: '#28a745',
                borderColor: '#218838',
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            indexAxis: 'y',
            scales: {
                x: {
                    beginAtZero: true,
                    ticks: {
                        callback: function(value) {
                            return '<?php echo CURRENCY_SYMBOL; ?>' + value;
                        }
                    }
                }
            }
        }
    });
});

function exportReport(format) {
    const url = new URL(window.location.href);
    url.searchParams.set('export', format);
    window.location.href = url.toString();
}
</script>

<?php
/**
 * Get valuation summary
 */
function get_valuation_summary($date_as_of, $category_id = null) {
    $data = [
        'summary' => [
            'total_products' => 0,
            'total_units' => 0,
            'total_value' => 0,
            'low_stock_items' => 0
        ],
        'headers' => ['Category', 'Product Code', 'Product Name', 'Stock', 'Unit', 'Cost Price', 'Total Value', 'Status'],
        'rows' => [],
        'category_chart' => ['labels' => [], 'data' => []],
        'top_products' => ['labels' => [], 'data' => []],
        'totals' => []
    ];
    
    $sql = "SELECT 
                p.product_id, p.product_code, p.product_name,
                p.current_stock, p.unit_of_measure, p.cost_price,
                c.category_name,
                (p.current_stock * p.cost_price) as total_value,
                CASE 
                    WHEN p.current_stock <= p.min_stock THEN 'low'
                    WHEN p.current_stock <= 0 THEN 'out'
                    ELSE 'normal'
                END as stock_status
            FROM pos_products p
            JOIN pos_categories c ON p.category_id = c.category_id
            WHERE p.stock_item = 1 AND p.is_active = 1";
    
    $params = [];
    
    if ($category_id) {
        $sql .= " AND p.category_id = ?";
        $params[] = $category_id;
    }
    
    $sql .= " ORDER BY c.category_name, p.product_name";
    
    $products = db_fetch_all($sql, $params);
    
    $category_totals = [];
    $total_value = 0;
    $total_units = 0;
    $low_stock = 0;
    
    foreach ($products as $product) {
        $status_badge = '';
        if ($product['stock_status'] == 'low') {
            $status_badge = '<span class="badge badge-warning">Low Stock</span>';
            $low_stock++;
        } elseif ($product['stock_status'] == 'out') {
            $status_badge = '<span class="badge badge-danger">Out of Stock</span>';
        } else {
            $status_badge = '<span class="badge badge-success">Normal</span>';
        }
        
        $data['rows'][] = [
            $product['category_name'],
            $product['product_code'],
            $product['product_name'],
            $product['current_stock'],
            $product['unit_of_measure'] ?: 'unit',
            format_currency($product['cost_price'] ?: 0),
            format_currency($product['total_value'] ?: 0),
            $status_badge
        ];
        
        // Category totals
        if (!isset($category_totals[$product['category_name']])) {
            $category_totals[$product['category_name']] = 0;
        }
        $category_totals[$product['category_name']] += $product['total_value'] ?: 0;
        
        $total_value += $product['total_value'] ?: 0;
        $total_units += $product['current_stock'];
    }
    
    // Category chart data
    foreach ($category_totals as $name => $value) {
        if ($value > 0) {
            $data['category_chart']['labels'][] = $name;
            $data['category_chart']['data'][] = $value;
        }
    }
    
    // Top 10 products by value
    usort($products, function($a, $b) {
        return ($b['total_value'] ?? 0) <=> ($a['total_value'] ?? 0);
    });
    
    $top_products = array_slice($products, 0, 10);
    foreach ($top_products as $product) {
        $data['top_products']['labels'][] = $product['product_name'];
        $data['top_products']['data'][] = $product['total_value'] ?: 0;
    }
    
    $data['summary']['total_products'] = count($products);
    $data['summary']['total_units'] = $total_units;
    $data['summary']['total_value'] = $total_value;
    $data['summary']['low_stock_items'] = $low_stock;
    
    $data['totals'] = ['', '', 'TOTAL', $total_units, '', '', format_currency($total_value), ''];
    
    return $data;
}

/**
 * Get movement analysis
 */
function get_movement_analysis($date_as_of, $category_id = null) {
    $data = [
        'summary' => [
            'total_products' => 0,
            'total_units' => 0,
            'total_value' => 0,
            'low_stock_items' => 0
        ],
        'headers' => ['Product', 'Category', 'Opening Stock', 'Purchases', 'Sales', 'Adjustments', 'Closing Stock', 'Value'],
        'rows' => [],
        'category_chart' => ['labels' => [], 'data' => []],
        'top_products' => ['labels' => [], 'data' => []]
    ];
    
    // This would need complex calculations based on stock movements
    // For now, we'll show a simplified version
    
    $sql = "SELECT 
                p.product_id, p.product_code, p.product_name,
                p.current_stock, p.cost_price,
                c.category_name,
                (SELECT COALESCE(SUM(quantity), 0) FROM stock_movements 
                 WHERE product_id = p.product_id AND movement_type = 'purchase') as purchases,
                (SELECT COALESCE(SUM(quantity), 0) FROM stock_movements 
                 WHERE product_id = p.product_id AND movement_type = 'sale') as sales,
                (SELECT COALESCE(SUM(quantity), 0) FROM stock_movements 
                 WHERE product_id = p.product_id AND movement_type = 'adjustment') as adjustments
            FROM pos_products p
            JOIN pos_categories c ON p.category_id = c.category_id
            WHERE p.stock_item = 1 AND p.is_active = 1";
    
    $params = [];
    
    if ($category_id) {
        $sql .= " AND p.category_id = ?";
        $params[] = $category_id;
    }
    
    $sql .= " ORDER BY c.category_name, p.product_name";
    
    $products = db_fetch_all($sql, $params);
    
    $total_value = 0;
    
    foreach ($products as $product) {
        $opening = $product['current_stock'] - $product['purchases'] + $product['sales'] - $product['adjustments'];
        $value = $product['current_stock'] * ($product['cost_price'] ?: 0);
        
        $data['rows'][] = [
            $product['product_name'],
            $product['category_name'],
            $opening,
            $product['purchases'],
            abs($product['sales']),
            $product['adjustments'],
            $product['current_stock'],
            format_currency($value)
        ];
        
        $total_value += $value;
    }
    
    $data['summary']['total_value'] = $total_value;
    
    return $data;
}

/**
 * Get low stock report
 */
function get_low_stock_report($date_as_of, $category_id = null) {
    $data = [
        'summary' => [
            'total_products' => 0,
            'total_units' => 0,
            'total_value' => 0,
            'low_stock_items' => 0
        ],
        'headers' => ['Product', 'Category', 'Current Stock', 'Min Stock', 'Max Stock', 'Status', 'Suggested Order', 'Value'],
        'rows' => [],
        'category_chart' => ['labels' => [], 'data' => []],
        'top_products' => ['labels' => [], 'data' => []]
    ];
    
    $sql = "SELECT 
                p.product_id, p.product_code, p.product_name,
                p.current_stock, p.min_stock, p.max_stock,
                p.cost_price,
                c.category_name,
                CASE 
                    WHEN p.current_stock <= 0 THEN 'out'
                    WHEN p.current_stock <= p.min_stock THEN 'low'
                    ELSE 'normal'
                END as stock_status,
                (p.min_stock * 2 - p.current_stock) as suggested_order
            FROM pos_products p
            JOIN pos_categories c ON p.category_id = c.category_id
            WHERE p.stock_item = 1 AND p.is_active = 1
            AND (p.current_stock <= p.min_stock OR p.current_stock <= 0)";
    
    $params = [];
    
    if ($category_id) {
        $sql .= " AND p.category_id = ?";
        $params[] = $category_id;
    }
    
    $sql .= " ORDER BY 
                CASE 
                    WHEN p.current_stock <= 0 THEN 1
                    ELSE 2
                END,
                (p.current_stock / p.min_stock)";
    
    $products = db_fetch_all($sql, $params);
    
    $total_value = 0;
    
    foreach ($products as $product) {
        $status_badge = $product['stock_status'] == 'out' 
            ? '<span class="badge badge-danger">Out of Stock</span>'
            : '<span class="badge badge-warning">Low Stock</span>';
        
        $value = $product['current_stock'] * ($product['cost_price'] ?: 0);
        
        $data['rows'][] = [
            $product['product_name'],
            $product['category_name'],
            $product['current_stock'],
            $product['min_stock'],
            $product['max_stock'] ?: '-',
            $status_badge,
            max(0, $product['suggested_order']),
            format_currency($value)
        ];
        
        $total_value += $value;
    }
    
    $data['summary']['total_products'] = count($products);
    $data['summary']['total_value'] = $total_value;
    $data['summary']['low_stock_items'] = count($products);
    
    return $data;
}

/**
 * Get turnover report
 */
function get_turnover_report($date_as_of, $category_id = null) {
    $data = [
        'summary' => [
            'total_products' => 0,
            'total_units' => 0,
            'total_value' => 0,
            'low_stock_items' => 0
        ],
        'headers' => ['Product', 'Category', 'Avg Stock', 'Units Sold', 'Turnover Rate', 'Days of Supply', 'Value'],
        'rows' => [],
        'category_chart' => ['labels' => [], 'data' => []],
        'top_products' => ['labels' => [], 'data' => []]
    ];
    
    $sql = "SELECT 
                p.product_id, p.product_code, p.product_name,
                p.current_stock, p.cost_price,
                c.category_name,
                (SELECT COALESCE(SUM(quantity), 0) FROM stock_movements 
                 WHERE product_id = p.product_id AND movement_type = 'sale'
                 AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) as monthly_sales
            FROM pos_products p
            JOIN pos_categories c ON p.category_id = c.category_id
            WHERE p.stock_item = 1 AND p.is_active = 1";
    
    $params = [];
    
    if ($category_id) {
        $sql .= " AND p.category_id = ?";
        $params[] = $category_id;
    }
    
    $sql .= " ORDER BY c.category_name, p.product_name";
    
    $products = db_fetch_all($sql, $params);
    
    $total_value = 0;
    
    foreach ($products as $product) {
        $avg_stock = $product['current_stock']; // Simplified
        $turnover_rate = $avg_stock > 0 ? round($product['monthly_sales'] / $avg_stock, 2) : 0;
        $days_supply = $product['monthly_sales'] > 0 ? round(30 * $avg_stock / $product['monthly_sales'], 1) : 0;
        $value = $product['current_stock'] * ($product['cost_price'] ?: 0);
        
        $data['rows'][] = [
            $product['product_name'],
            $product['category_name'],
            $avg_stock,
            $product['monthly_sales'],
            $turnover_rate,
            $days_supply,
            format_currency($value)
        ];
        
        $total_value += $value;
    }
    
    $data['summary']['total_value'] = $total_value;
    
    return $data;
}

/**
 * Export as PDF
 */
function export_pdf($data, $type, $date_as_of) {
    header('Location: export-pdf.php?report=inventory_valuation&type=' . $type . '&as_of=' . $date_as_of);
    exit;
}

/**
 * Export as Excel
 */
function export_excel($data, $type, $date_as_of) {
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="inventory_valuation_' . $type . '_' . $date_as_of . '.xls"');
    
    echo "Inventory Valuation Report\n";
    echo "Type: " . ucfirst(str_replace('_', ' ', $type)) . "\n";
    echo "As of: " . format_date($date_as_of) . "\n\n";
    
    echo "Summary\n";
    echo "Total Products\t" . $data['summary']['total_products'] . "\n";
    echo "Total Units\t" . $data['summary']['total_units'] . "\n";
    echo "Total Value\t" . format_currency($data['summary']['total_value']) . "\n";
    echo "Low Stock Items\t" . $data['summary']['low_stock_items'] . "\n\n";
    
    echo implode("\t", $data['headers']) . "\n";
    foreach ($data['rows'] as $row) {
        // Remove HTML tags from status for Excel
        $clean_row = array_map(function($cell) {
            return strip_tags($cell);
        }, $row);
        echo implode("\t", $clean_row) . "\n";
    }
    
    if (!empty($data['totals'])) {
        echo implode("\t", $data['totals']) . "\n";
    }
    exit;
}

/**
 * Export as CSV
 */
function export_csv($data, $type, $date_as_of) {
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="inventory_valuation_' . $type . '_' . $date_as_of . '.csv"');
    
    $output = fopen('php://output', 'w');
    
    fputcsv($output, ['Inventory Valuation Report']);
    fputcsv($output, ['Type:', ucfirst(str_replace('_', ' ', $type))]);
    fputcsv($output, ['As of:', format_date($date_as_of)]);
    fputcsv($output, []);
    
    fputcsv($output, ['Summary']);
    fputcsv($output, ['Total Products', $data['summary']['total_products']]);
    fputcsv($output, ['Total Units', $data['summary']['total_units']]);
    fputcsv($output, ['Total Value', format_currency($data['summary']['total_value'])]);
    fputcsv($output, ['Low Stock Items', $data['summary']['low_stock_items']]);
    fputcsv($output, []);
    
    // Clean headers and rows of HTML
    $clean_headers = array_map(function($h) {
        return strip_tags($h);
    }, $data['headers']);
    
    fputcsv($output, $clean_headers);
    
    foreach ($data['rows'] as $row) {
        $clean_row = array_map(function($cell) {
            return strip_tags($cell);
        }, $row);
        fputcsv($output, $clean_row);
    }
    
    if (!empty($data['totals'])) {
        $clean_totals = array_map(function($t) {
            return strip_tags($t);
        }, $data['totals']);
        fputcsv($output, $clean_totals);
    }
    
    fclose($output);
    exit;
}

// Include footer
require_once dirname(__DIR__, 2) . '/views/partials/footer.php';
?>