<?php
require_once __DIR__ . '/includes/base.php';

$breadcrumbs = [['title' => 'Market Prices', 'url' => '']];

// Fetch market prices with analytics
try {
    $sql = "SELECT * FROM market_prices ORDER BY price_date DESC, product_type";
    $stmt = $db->query($sql);
    $prices = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get unique products and markets
    $products = array_unique(array_column($prices, 'product_type'));
    $markets = array_unique(array_column($prices, 'market_location'));
    
    // Get recent price trends
    $trendSql = "SELECT product_type, AVG(price) as avg_price, MIN(price) as min_price, 
                 MAX(price) as max_price, COUNT(*) as records
                 FROM market_prices 
                 WHERE price_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
                 GROUP BY product_type
                 ORDER BY avg_price DESC";
    $trendStmt = $db->query($trendSql);
    $trends = $trendStmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Get today's prices
    $todaySql = "SELECT COUNT(*) as today_count FROM market_prices WHERE price_date = CURDATE()";
    $todayCount = $db->query($todaySql)->fetchColumn();
    
    // Get price changes (compare today vs yesterday)
    $changeSql = "SELECT 
                    mp1.product_type,
                    mp1.price as today_price,
                    mp2.price as yesterday_price,
                    ((mp1.price - mp2.price) / mp2.price * 100) as change_percent
                  FROM market_prices mp1
                  LEFT JOIN market_prices mp2 ON mp1.product_type = mp2.product_type 
                    AND mp2.price_date = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
                  WHERE mp1.price_date = CURDATE()
                  AND mp1.market_location = mp2.market_location";
    $changeStmt = $db->query($changeSql);
    $priceChanges = $changeStmt->fetchAll(PDO::FETCH_ASSOC);
    
} catch (PDOException $e) {
    $prices = [];
    $products = [];
    $markets = [];
    $trends = [];
    $todayCount = 0;
    $priceChanges = [];
}

renderAdminHeader('Market Prices', $breadcrumbs);
?>

<div class="d-flex justify-content-between align-items-center mb-4">
    <div>
        <h2 class="mb-1 fw-bold">Market Prices</h2>
        <p class="text-muted mb-0">Track and manage agricultural product prices</p>
    </div>
    <div>
        <button class="btn btn-success me-2" onclick="importPrices()">
            <i class="bi bi-cloud-upload me-2"></i>Import CSV
        </button>
        <button class="btn btn-primary" data-bs-toggle="modal" data-bs-target="#addModal">
            <i class="bi bi-plus-circle me-2"></i>Add Price
        </button>
    </div>
</div>

<!-- Statistics Cards -->
<div class="row mb-4">
    <div class="col-md-3 mb-3">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <h6 class="text-muted mb-1">Total Products</h6>
                        <h3 class="mb-0 fw-bold"><?php echo count($products); ?></h3>
                    </div>
                    <div class="bg-primary bg-opacity-10 rounded p-3">
                        <i class="bi bi-box-seam fs-4 text-primary"></i>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <div class="col-md-3 mb-3">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <h6 class="text-muted mb-1">Markets</h6>
                        <h3 class="mb-0 fw-bold"><?php echo count($markets); ?></h3>
                    </div>
                    <div class="bg-success bg-opacity-10 rounded p-3">
                        <i class="bi bi-shop fs-4 text-success"></i>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <div class="col-md-3 mb-3">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <h6 class="text-muted mb-1">Today's Entries</h6>
                        <h3 class="mb-0 fw-bold"><?php echo $todayCount; ?></h3>
                    </div>
                    <div class="bg-info bg-opacity-10 rounded p-3">
                        <i class="bi bi-calendar-check fs-4 text-info"></i>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <div class="col-md-3 mb-3">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <div class="d-flex justify-content-between align-items-center">
                    <div>
                        <h6 class="text-muted mb-1">Total Records</h6>
                        <h3 class="mb-0 fw-bold"><?php echo count($prices); ?></h3>
                    </div>
                    <div class="bg-warning bg-opacity-10 rounded p-3">
                        <i class="bi bi-database fs-4 text-warning"></i>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Price Changes Alert -->
<?php if (!empty($priceChanges)): ?>
<div class="alert alert-info alert-dismissible fade show mb-4">
    <h6 class="alert-heading"><i class="bi bi-graph-up-arrow me-2"></i>Today's Price Changes</h6>
    <div class="row">
        <?php foreach (array_slice($priceChanges, 0, 4) as $change): ?>
        <div class="col-md-3">
            <strong><?php echo htmlspecialchars($change['product_type']); ?>:</strong>
            <span class="badge bg-<?php echo $change['change_percent'] > 0 ? 'success' : 'danger'; ?>">
                <?php echo $change['change_percent'] > 0 ? '+' : ''; ?><?php echo number_format($change['change_percent'], 1); ?>%
            </span>
        </div>
        <?php endforeach; ?>
    </div>
    <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
</div>
<?php endif; ?>

<!-- Tabs -->
<ul class="nav nav-tabs mb-4">
    <li class="nav-item">
        <a class="nav-link active" data-bs-toggle="tab" href="#current">Current Prices</a>
    </li>
    <li class="nav-item">
        <a class="nav-link" data-bs-toggle="tab" href="#trends">Price Trends</a>
    </li>
    <li class="nav-item">
        <a class="nav-link" data-bs-toggle="tab" href="#analytics">Analytics</a>
    </li>
</ul>

<div class="tab-content">
    <!-- Current Prices Tab -->
    <div class="tab-pane fade show active" id="current">
        <div class="card shadow-sm border-0">
            <div class="card-header bg-white py-3">
                <div class="row align-items-center">
                    <div class="col-md-6">
                        <h5 class="mb-0 fw-bold"><i class="bi bi-currency-dollar me-2"></i>All Market Prices</h5>
                    </div>
                    <div class="col-md-6 text-end">
                        <select class="form-select form-select-sm d-inline-block w-auto" id="filterProduct">
                            <option value="">All Products</option>
                            <?php foreach ($products as $p): ?>
                            <option value="<?php echo htmlspecialchars($p); ?>"><?php echo htmlspecialchars($p); ?></option>
                            <?php endforeach; ?>
                        </select>
                        <select class="form-select form-select-sm d-inline-block w-auto ms-2" id="filterMarket">
                            <option value="">All Markets</option>
                            <?php foreach ($markets as $m): ?>
                            <option value="<?php echo htmlspecialchars($m); ?>"><?php echo htmlspecialchars($m); ?></option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                </div>
            </div>
            <div class="card-body p-0">
                <table class="table table-hover mb-0" id="pricesTable">
                    <thead class="table-light">
                        <tr>
                            <th>Product</th>
                            <th>Market</th>
                            <th>Price</th>
                            <th>Unit</th>
                            <th>Source</th>
                            <th>Date</th>
                            <th class="text-end">Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($prices as $price): ?>
                        <tr>
                            <td class="fw-semibold">
                                <i class="bi bi-box text-primary me-2"></i>
                                <?php echo htmlspecialchars($price['product_type']); ?>
                            </td>
                            <td>
                                <i class="bi bi-geo-alt text-success me-1"></i>
                                <?php echo htmlspecialchars($price['market_location']); ?>
                            </td>
                            <td>
                                <strong class="text-success">UGX <?php echo number_format($price['price']); ?></strong>
                            </td>
                            <td><span class="badge bg-secondary"><?php echo htmlspecialchars($price['unit']); ?></span></td>
                            <td><small class="text-muted"><?php echo htmlspecialchars($price['source']); ?></small></td>
                            <td>
                                <small class="text-muted">
                                    <i class="bi bi-calendar3"></i>
                                    <?php echo date('M d, Y', strtotime($price['price_date'])); ?>
                                </small>
                            </td>
                            <td class="text-end">
                                <div class="btn-group btn-group-sm">
                                    <button class="btn btn-outline-primary" onclick='editPrice(<?php echo json_encode($price); ?>)' title="Edit">
                                        <i class="bi bi-pencil"></i>
                                    </button>
                                    <button class="btn btn-outline-danger" onclick="deletePrice(<?php echo $price['id']; ?>)" title="Delete">
                                        <i class="bi bi-trash"></i>
                                    </button>
                                </div>
                            </td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <!-- Price Trends Tab -->
    <div class="tab-pane fade" id="trends">
        <div class="row">
            <div class="col-md-8 mb-4">
                <div class="card shadow-sm border-0">
                    <div class="card-header bg-white">
                        <h6 class="mb-0 fw-bold"><i class="bi bi-graph-up me-2"></i>Price Trends (Last 30 Days)</h6>
                    </div>
                    <div class="card-body">
                        <canvas id="trendChart" height="80"></canvas>
                    </div>
                </div>
            </div>
            <div class="col-md-4 mb-4">
                <div class="card shadow-sm border-0">
                    <div class="card-header bg-white">
                        <h6 class="mb-0 fw-bold"><i class="bi bi-bar-chart me-2"></i>Average Prices</h6>
                    </div>
                    <div class="card-body">
                        <canvas id="avgPriceChart" height="150"></canvas>
                    </div>
                </div>
            </div>
        </div>
        
        <!-- Price Statistics -->
        <div class="card shadow-sm border-0">
            <div class="card-header bg-white">
                <h6 class="mb-0 fw-bold"><i class="bi bi-table me-2"></i>30-Day Statistics by Product</h6>
            </div>
            <div class="card-body p-0">
                <table class="table table-hover mb-0">
                    <thead class="table-light">
                        <tr>
                            <th>Product</th>
                            <th>Average Price</th>
                            <th>Min Price</th>
                            <th>Max Price</th>
                            <th>Price Range</th>
                            <th>Records</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($trends as $trend): ?>
                        <tr>
                            <td class="fw-semibold"><?php echo htmlspecialchars($trend['product_type']); ?></td>
                            <td><strong class="text-primary">UGX <?php echo number_format($trend['avg_price']); ?></strong></td>
                            <td><small class="text-success">UGX <?php echo number_format($trend['min_price']); ?></small></td>
                            <td><small class="text-danger">UGX <?php echo number_format($trend['max_price']); ?></small></td>
                            <td>
                                <div class="progress" style="height: 20px;">
                                    <div class="progress-bar bg-gradient" style="width: 100%">
                                        UGX <?php echo number_format($trend['max_price'] - $trend['min_price']); ?>
                                    </div>
                                </div>
                            </td>
                            <td><span class="badge bg-info"><?php echo $trend['records']; ?></span></td>
                        </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>

    <!-- Analytics Tab -->
    <div class="tab-pane fade" id="analytics">
        <div class="row">
            <div class="col-md-6 mb-4">
                <div class="card shadow-sm border-0">
                    <div class="card-header bg-white">
                        <h6 class="mb-0 fw-bold"><i class="bi bi-pie-chart me-2"></i>Market Distribution</h6>
                    </div>
                    <div class="card-body">
                        <canvas id="marketChart"></canvas>
                    </div>
                </div>
            </div>
            <div class="col-md-6 mb-4">
                <div class="card shadow-sm border-0">
                    <div class="card-header bg-white">
                        <h6 class="mb-0 fw-bold"><i class="bi bi-clock-history me-2"></i>Price Updates Timeline</h6>
                    </div>
                    <div class="card-body">
                        <canvas id="timelineChart"></canvas>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Add/Edit Modal -->
<div class="modal fade" id="addModal" tabindex="-1">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title" id="modalTitle">Add Market Price</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form id="priceForm">
                <div class="modal-body">
                    <input type="hidden" name="id" id="priceId">
                    
                    <div class="mb-3">
                        <label>Product Type *</label>
                        <input type="text" class="form-control" name="product_type" list="productsList" required>
                        <datalist id="productsList">
                            <option value="Sugar Cane"><option value="Maize"><option value="Beans">
                            <option value="Coffee"><option value="Bananas"><option value="Cassava">
                            <option value="Tomatoes"><option value="Cabbages"><option value="Eggs">
                            <option value="Chicken"><option value="Milk">
                        </datalist>
                    </div>
                    
                    <div class="mb-3">
                        <label>Market Location *</label>
                        <input type="text" class="form-control" name="market_location" list="marketsList" required>
                        <datalist id="marketsList">
                            <?php foreach ($markets as $m): ?>
                            <option value="<?php echo htmlspecialchars($m); ?>">
                            <?php endforeach; ?>
                        </datalist>
                    </div>
                    
                    <div class="row">
                        <div class="col-md-6 mb-3">
                            <label>Price (UGX) *</label>
                            <input type="number" class="form-control" name="price" step="0.01" required>
                        </div>
                        <div class="col-md-6 mb-3">
                            <label>Unit *</label>
                            <select class="form-select" name="unit" required>
                                <option value="">Select...</option>
                                <option value="kg">Kilogram (kg)</option>
                                <option value="ton">Ton</option>
                                <option value="piece">Piece</option>
                                <option value="bunch">Bunch</option>
                                <option value="tray">Tray</option>
                                <option value="liter">Liter</option>
                                <option value="bag">Bag</option>
                            </select>
                        </div>
                    </div>
                    
                    <div class="row">
                        <div class="col-md-6 mb-3">
                            <label>Source</label>
                            <input type="text" class="form-control" name="source" value="Manual Entry">
                        </div>
                        <div class="col-md-6 mb-3">
                            <label>Date *</label>
                            <input type="date" class="form-control" name="price_date" value="<?php echo date('Y-m-d'); ?>" required>
                        </div>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                    <button type="submit" class="btn btn-primary">Save Price</button>
                </div>
            </form>
        </div>
    </div>
</div>

<script>
let pricesTable;

$(document).ready(function() {
    // Initialize DataTable
    pricesTable = $('#pricesTable').DataTable({
        pageLength: 25,
        order: [[5, 'desc']],
        language: {
            search: "_INPUT_",
            searchPlaceholder: "Search prices..."
        }
    });
    
    // Filter handlers
    $('#filterProduct, #filterMarket').change(function() {
        const product = $('#filterProduct').val();
        const market = $('#filterMarket').val();
        
        pricesTable
            .column(0).search(product)
            .column(1).search(market)
            .draw();
    });
    
    // Initialize charts
    initCharts();
});

// Form submission
$('#priceForm').submit(function(e) {
    e.preventDefault();
    
    const btn = $(this).find('button[type="submit"]');
    btn.prop('disabled', true).html('<span class="spinner-border spinner-border-sm me-2"></span>Saving...');
    
    const action = $('#priceId').val() ? 'edit_market_price' : 'add_market_price';
    
    $.post(APP_URL + '/api/admin.php', $(this).serialize() + '&action=' + action)
    .done(function(r) {
        if (r.success) {
            showToast(r.message, 'success');
            $('#addModal').modal('hide');
            setTimeout(() => location.reload(), 1000);
        } else {
            showToast(r.message, 'error');
            btn.prop('disabled', false).text('Save Price');
        }
    })
    .fail(function() {
        showToast('Connection error', 'error');
        btn.prop('disabled', false).text('Save Price');
    });
});

function editPrice(price) {
    $('#modalTitle').text('Edit Market Price');
    $('#priceId').val(price.id);
    $('input[name="product_type"]').val(price.product_type);
    $('input[name="market_location"]').val(price.market_location);
    $('input[name="price"]').val(price.price);
    $('select[name="unit"]').val(price.unit);
    $('input[name="source"]').val(price.source);
    $('input[name="price_date"]').val(price.price_date);
    $('#addModal').modal('show');
}

function deletePrice(id) {
    Swal.fire({
        title: 'Delete Price?',
        text: 'This action cannot be undone',
        icon: 'warning',
        showCancelButton: true,
        confirmButtonColor: '#ef4444',
        confirmButtonText: 'Yes, delete'
    }).then((result) => {
        if (result.isConfirmed) {
            $.post(APP_URL + '/api/admin.php', {action: 'delete_market_price', id: id})
            .done(function(r) {
                if (r.success) {
                    showToast(r.message, 'success');
                    setTimeout(() => location.reload(), 1000);
                } else {
                    showToast(r.message, 'error');
                }
            });
        }
    });
}

function importPrices() {
    Swal.fire({
        title: 'Import Prices',
        html: '<input type="file" id="csvFile" class="form-control" accept=".csv">',
        showCancelButton: true,
        confirmButtonText: 'Upload',
        preConfirm: () => {
            const file = document.getElementById('csvFile').files[0];
            if (!file) {
                Swal.showValidationMessage('Please select a file');
                return false;
            }
            return file;
        }
    }).then((result) => {
        if (result.isConfirmed) {
            const formData = new FormData();
            formData.append('file', result.value);
            formData.append('action', 'import_prices');
            
            showLoading();
            $.ajax({
                url: APP_URL + '/api/admin.php',
                method: 'POST',
                data: formData,
                processData: false,
                contentType: false,
                success: function(r) {
                    hideLoading();
                    if (r.success) {
                        showToast(r.message, 'success');
                        setTimeout(() => location.reload(), 1000);
                    } else {
                        showToast(r.message, 'error');
                    }
                },
                error: function() {
                    hideLoading();
                    showToast('Upload failed', 'error');
                }
            });
        }
    });
}

// Initialize charts
function initCharts() {
    // Price Trends Chart
    <?php
    $chartData = [];
    foreach ($trends as $t) {
        $chartData[] = [
            'label' => $t['product_type'],
            'avg' => $t['avg_price'],
            'min' => $t['min_price'],
            'max' => $t['max_price']
        ];
    }
    ?>
    
    const trendData = <?php echo json_encode($chartData); ?>;
    
    new Chart(document.getElementById('trendChart'), {
        type: 'line',
        data: {
            labels: trendData.map(d => d.label),
            datasets: [{
                label: 'Average Price',
                data: trendData.map(d => d.avg),
                borderColor: '#059669',
                tension: 0.4
            }]
        },
        options: {
            responsive: true,
            plugins: {
                legend: {display: true}
            }
        }
    });
    
    // Average Price Chart
    new Chart(document.getElementById('avgPriceChart'), {
        type: 'bar',
        data: {
            labels: trendData.slice(0, 10).map(d => d.label),
            datasets: [{
                label: 'Avg Price (UGX)',
                data: trendData.slice(0, 10).map(d => d.avg),
                backgroundColor: '#3b82f6'
            }]
        },
        options: {
            indexAxis: 'y',
            responsive: true,
            plugins: {
                legend: {display: false}
            }
        }
    });
    
    // Market Distribution Chart
    new Chart(document.getElementById('marketChart'), {
        type: 'doughnut',
        data: {
            labels: <?php echo json_encode(array_slice($markets, 0, 10)); ?>,
            datasets: [{
                data: [<?php echo implode(',', array_fill(0, min(10, count($markets)), 1)); ?>],
                backgroundColor: ['#ef4444', '#f59e0b', '#10b981', '#3b82f6', '#8b5cf6', '#ec4899', '#14b8a6', '#f97316', '#6366f1', '#84cc16']
            }]
        }
    });
    
    // Timeline Chart
    new Chart(document.getElementById('timelineChart'), {
        type: 'line',
        data: {
            labels: ['Week 1', 'Week 2', 'Week 3', 'Week 4'],
            datasets: [{
                label: 'Updates',
                data: [12, 19, 15, 25],
                borderColor: '#8b5cf6',
                fill: true,
                backgroundColor: 'rgba(139, 92, 246, 0.1)'
            }]
        }
    });
}

// Reset form when modal closes
$('#addModal').on('hidden.bs.modal', function() {
    $('#modalTitle').text('Add Market Price');
    $('#priceForm')[0].reset();
    $('#priceId').val('');
});
</script>

<?php renderAdminFooter(); ?>