<?php include 'db_connect.php';

// Get filter parameter
$filter = isset($_GET['filter']) ? $_GET['filter'] : 'all';

// Build date conditions based on filter
$today = date('Y-m-d');
$three_months = date('Y-m-d', strtotime('+3 months'));
$six_months = date('Y-m-d', strtotime('+6 months'));
$twelve_months = date('Y-m-d', strtotime('+12 months'));

switch($filter) {
    case 'expired':
        $date_condition = "b.expiry_date < '$today'";
        $filter_title = "Already Expired";
        break;
    case '3months':
        $date_condition = "b.expiry_date >= '$today' AND b.expiry_date <= '$three_months'";
        $filter_title = "Expiring Within 3 Months";
        break;
    case '6months':
        $date_condition = "b.expiry_date >= '$today' AND b.expiry_date <= '$six_months'";
        $filter_title = "Expiring Within 6 Months";
        break;
    case '12months':
        $date_condition = "b.expiry_date >= '$today' AND b.expiry_date <= '$twelve_months'";
        $filter_title = "Expiring Within 12 Months";
        break;
    default:
        $date_condition = "b.expiry_date <= '$twelve_months'";
        $filter_title = "All Expiring Products";
        break;
}

// Get statistics
$stats_expired = $conn->query("SELECT COUNT(*) as count, COALESCE(SUM(quantity_received - quantity_sold - quantity_expired), 0) as qty FROM batch_list WHERE expiry_date < '$today' AND status = 'active'")->fetch_assoc();
$stats_3m = $conn->query("SELECT COUNT(*) as count, COALESCE(SUM(quantity_received - quantity_sold - quantity_expired), 0) as qty FROM batch_list WHERE expiry_date >= '$today' AND expiry_date <= '$three_months' AND status = 'active'")->fetch_assoc();
$stats_6m = $conn->query("SELECT COUNT(*) as count, COALESCE(SUM(quantity_received - quantity_sold - quantity_expired), 0) as qty FROM batch_list WHERE expiry_date >= '$today' AND expiry_date <= '$six_months' AND status = 'active'")->fetch_assoc();
$stats_12m = $conn->query("SELECT COUNT(*) as count, COALESCE(SUM(quantity_received - quantity_sold - quantity_expired), 0) as qty FROM batch_list WHERE expiry_date >= '$today' AND expiry_date <= '$twelve_months' AND status = 'active'")->fetch_assoc();
?>

<div class="container-fluid">
	<!-- Page Header -->
	<div class="d-flex justify-content-between align-items-center mb-4">
		<div>
			<h2 class="mb-1" style="font-weight: 700; color: var(--gray-800);">Expiring Products</h2>
			<p class="text-muted mb-0">Monitor and manage products nearing expiration</p>
		</div>
	</div>

	<!-- Stats Cards -->
	<div class="row mb-4">
		<div class="col-md-3">
			<a href="index.php?page=expired_product&filter=expired" class="text-decoration-none">
				<div class="modern-card <?php echo $filter == 'expired' ? 'border-danger' : ''; ?>" style="border-left: 4px solid var(--danger); cursor: pointer;">
					<div class="card-body">
						<div class="d-flex align-items-center">
							<div class="stat-icon mr-3" style="width: 50px; height: 50px; font-size: 1.2rem; background: linear-gradient(135deg, rgba(239, 68, 68, 0.15), rgba(239, 68, 68, 0.05)); color: var(--danger); border-radius: 12px; display: flex; align-items: center; justify-content: center;">
								<i class="fas fa-times-circle"></i>
							</div>
							<div>
								<h3 class="mb-0" style="font-weight: 700; color: var(--danger);"><?php echo $stats_expired['count']; ?></h3>
								<small class="text-muted">Already Expired</small>
								<br><small class="text-danger"><?php echo $stats_expired['qty']; ?> units</small>
							</div>
						</div>
					</div>
				</div>
			</a>
		</div>
		<div class="col-md-3">
			<a href="index.php?page=expired_product&filter=3months" class="text-decoration-none">
				<div class="modern-card <?php echo $filter == '3months' ? 'border-warning' : ''; ?>" style="border-left: 4px solid var(--warning); cursor: pointer;">
					<div class="card-body">
						<div class="d-flex align-items-center">
							<div class="stat-icon mr-3" style="width: 50px; height: 50px; font-size: 1.2rem; background: linear-gradient(135deg, rgba(245, 158, 11, 0.15), rgba(245, 158, 11, 0.05)); color: var(--warning); border-radius: 12px; display: flex; align-items: center; justify-content: center;">
								<i class="fas fa-exclamation-triangle"></i>
							</div>
							<div>
								<h3 class="mb-0" style="font-weight: 700; color: var(--warning);"><?php echo $stats_3m['count']; ?></h3>
								<small class="text-muted">Within 3 Months</small>
								<br><small class="text-warning"><?php echo $stats_3m['qty']; ?> units</small>
							</div>
						</div>
					</div>
				</div>
			</a>
		</div>
		<div class="col-md-3">
			<a href="index.php?page=expired_product&filter=6months" class="text-decoration-none">
				<div class="modern-card <?php echo $filter == '6months' ? 'border-info' : ''; ?>" style="border-left: 4px solid #06b6d4; cursor: pointer;">
					<div class="card-body">
						<div class="d-flex align-items-center">
							<div class="stat-icon mr-3" style="width: 50px; height: 50px; font-size: 1.2rem; background: linear-gradient(135deg, rgba(6, 182, 212, 0.15), rgba(6, 182, 212, 0.05)); color: #06b6d4; border-radius: 12px; display: flex; align-items: center; justify-content: center;">
								<i class="fas fa-clock"></i>
							</div>
							<div>
								<h3 class="mb-0" style="font-weight: 700; color: #06b6d4;"><?php echo $stats_6m['count']; ?></h3>
								<small class="text-muted">Within 6 Months</small>
								<br><small style="color: #06b6d4;"><?php echo $stats_6m['qty']; ?> units</small>
							</div>
						</div>
					</div>
				</div>
			</a>
		</div>
		<div class="col-md-3">
			<a href="index.php?page=expired_product&filter=12months" class="text-decoration-none">
				<div class="modern-card <?php echo $filter == '12months' ? 'border-primary' : ''; ?>" style="border-left: 4px solid var(--primary); cursor: pointer;">
					<div class="card-body">
						<div class="d-flex align-items-center">
							<div class="stat-icon mr-3" style="width: 50px; height: 50px; font-size: 1.2rem; background: linear-gradient(135deg, rgba(99, 102, 241, 0.15), rgba(99, 102, 241, 0.05)); color: var(--primary); border-radius: 12px; display: flex; align-items: center; justify-content: center;">
								<i class="fas fa-calendar-alt"></i>
							</div>
							<div>
								<h3 class="mb-0" style="font-weight: 700; color: var(--primary);"><?php echo $stats_12m['count']; ?></h3>
								<small class="text-muted">Within 12 Months</small>
								<br><small class="text-primary"><?php echo $stats_12m['qty']; ?> units</small>
							</div>
						</div>
					</div>
				</div>
			</a>
		</div>
	</div>

	<!-- Filter Dropdown and Table -->
	<div class="modern-card">
		<div class="card-header d-flex justify-content-between align-items-center">
			<h3 class="card-title mb-0">
				<i class="fas fa-pills mr-2"></i>
				<?php echo $filter_title; ?>
			</h3>
			<div class="d-flex align-items-center">
				<select id="expiry_filter" class="form-control" style="width: 220px; border-radius: 10px;">
					<option value="all" <?php echo $filter == 'all' ? 'selected' : ''; ?>>All Expiring (12 months)</option>
					<option value="expired" <?php echo $filter == 'expired' ? 'selected' : ''; ?>>Already Expired</option>
					<option value="3months" <?php echo $filter == '3months' ? 'selected' : ''; ?>>Within 3 Months</option>
					<option value="6months" <?php echo $filter == '6months' ? 'selected' : ''; ?>>Within 6 Months</option>
					<option value="12months" <?php echo $filter == '12months' ? 'selected' : ''; ?>>Within 12 Months</option>
				</select>
			</div>
		</div>
		<div class="card-body p-0">
			<div class="table-responsive">
				<table class="table table-hover mb-0" id="expiry-table">
					<thead>
						<tr>
							<th style="padding-left: 24px;">#</th>
							<th>Batch No.</th>
							<th>Product</th>
							<th>Expiry Date</th>
							<th class="text-center">Days Left</th>
							<th class="text-center">Stock</th>
							<th class="text-center">Status</th>
							<th class="text-center" style="padding-right: 24px;">Actions</th>
						</tr>
					</thead>
					<tbody>
					<?php
					$i = 1;
					$query = "SELECT b.*, p.name as product_name, p.sku, p.measurement
							  FROM batch_list b
							  INNER JOIN product_list p ON p.id = b.product_id
							  WHERE $date_condition AND b.status = 'active'
							  AND (b.quantity_received - b.quantity_sold - b.quantity_expired) > 0
							  ORDER BY b.expiry_date ASC";
					$batches = $conn->query($query);

					while($row = $batches->fetch_assoc()):
						$expiry_date = strtotime($row['expiry_date']);
						$today_ts = strtotime($today);
						$days_left = floor(($expiry_date - $today_ts) / (60 * 60 * 24));
						$remaining_stock = $row['quantity_received'] - $row['quantity_sold'] - $row['quantity_expired'];

						// Determine status and colors
						if($days_left < 0) {
							$status_class = 'danger';
							$status_text = 'Expired';
							$days_text = abs($days_left) . ' days ago';
						} elseif($days_left == 0) {
							$status_class = 'danger';
							$status_text = 'Expires Today';
							$days_text = 'Today';
						} elseif($days_left <= 30) {
							$status_class = 'danger';
							$status_text = 'Critical';
							$days_text = $days_left . ' days';
						} elseif($days_left <= 90) {
							$status_class = 'warning';
							$status_text = 'Warning';
							$days_text = $days_left . ' days';
						} elseif($days_left <= 180) {
							$status_class = 'info';
							$status_text = 'Monitor';
							$days_text = $days_left . ' days';
						} else {
							$status_class = 'primary';
							$status_text = 'OK';
							$days_text = $days_left . ' days';
						}
					?>
						<tr>
							<td style="padding-left: 24px;"><?php echo $i++; ?></td>
							<td>
								<code class="text-primary"><?php echo htmlspecialchars($row['batch_number']); ?></code>
							</td>
							<td>
								<div>
									<strong><?php echo htmlspecialchars($row['product_name']); ?></strong>
									<sup><?php echo htmlspecialchars($row['measurement']); ?></sup>
								</div>
								<small class="text-muted">SKU: <?php echo htmlspecialchars($row['sku']); ?></small>
							</td>
							<td>
								<div><?php echo date("M d, Y", strtotime($row['expiry_date'])); ?></div>
							</td>
							<td class="text-center">
								<span class="badge badge-<?php echo $status_class; ?>" style="font-size: 0.9rem; padding: 6px 12px;">
									<?php echo $days_text; ?>
								</span>
							</td>
							<td class="text-center">
								<strong><?php echo $remaining_stock; ?></strong> units
							</td>
							<td class="text-center">
								<span class="badge badge-<?php echo $status_class; ?>" style="padding: 6px 12px;">
									<i class="fas fa-<?php echo $days_left < 0 ? 'times-circle' : ($days_left <= 30 ? 'exclamation-circle' : 'clock'); ?> mr-1"></i>
									<?php echo $status_text; ?>
								</span>
							</td>
							<td class="text-center" style="padding-right: 24px;">
								<?php if($days_left < 0): ?>
								<button class="btn btn-sm btn-danger mark_expired" data-id="<?php echo $row['id']; ?>" data-qty="<?php echo $remaining_stock; ?>" title="Mark as Disposed">
									<i class="fas fa-trash-alt"></i> Dispose
								</button>
								<?php else: ?>
								<button class="btn btn-sm btn-light view_batch" data-id="<?php echo $row['id']; ?>" title="View Details">
									<i class="fas fa-eye"></i>
								</button>
								<?php endif; ?>
							</td>
						</tr>
					<?php endwhile; ?>
					</tbody>
				</table>
			</div>
		</div>
	</div>
</div>

<style>
	#expiry-table thead th {
		background: var(--gray-50);
		border-bottom: 2px solid var(--gray-200);
		font-weight: 600;
		font-size: 0.75rem;
		text-transform: uppercase;
		letter-spacing: 0.05em;
		color: var(--gray-600);
		padding: 16px 12px;
	}

	#expiry-table tbody td {
		padding: 16px 12px;
		vertical-align: middle;
		border-bottom: 1px solid var(--gray-100);
	}

	#expiry-table tbody tr:hover {
		background: var(--gray-50);
	}

	.modern-card.border-danger {
		border: 2px solid var(--danger) !important;
	}
	.modern-card.border-warning {
		border: 2px solid var(--warning) !important;
	}
	.modern-card.border-info {
		border: 2px solid #06b6d4 !important;
	}
	.modern-card.border-primary {
		border: 2px solid var(--primary) !important;
	}

	.stat-icon {
		transition: transform 0.2s ease;
	}

	.modern-card:hover .stat-icon {
		transform: scale(1.1);
	}

	@media (max-width: 768px) {
		.row.mb-4 .col-md-3 {
			margin-bottom: 1rem;
		}
	}
</style>

<script>
	$(document).ready(function(){
		$('#expiry-table').DataTable({
			"order": [[3, "asc"]],
			"pageLength": 15,
			"language": {
				"search": "",
				"searchPlaceholder": "Search products...",
				"lengthMenu": "Show _MENU_",
				"info": "Showing _START_ to _END_ of _TOTAL_ items",
				"paginate": {
					"previous": "<i class='fas fa-chevron-left'></i>",
					"next": "<i class='fas fa-chevron-right'></i>"
				}
			}
		});

		$('.dataTables_filter input').addClass('form-control').css({
			'width': '250px',
			'display': 'inline-block',
			'border-radius': '10px'
		});

		// Filter dropdown change
		$('#expiry_filter').change(function(){
			var filter = $(this).val();
			location.href = 'index.php?page=expired_product&filter=' + filter;
		});

		// Mark as disposed/expired
		$('.mark_expired').click(function(){
			var id = $(this).attr('data-id');
			var qty = $(this).attr('data-qty');
			_conf("Mark this batch as disposed? This will remove " + qty + " units from inventory.", "dispose_batch", [id]);
		});
	});

	function dispose_batch(id){
		start_load();
		$.ajax({
			url: 'ajax.php?action=mark_batch_expired',
			method: 'POST',
			data: {id: id},
			success: function(resp){
				if(resp == 1){
					alert_toast("Batch marked as disposed successfully", 'success');
					setTimeout(function(){
						location.reload();
					}, 1500);
				} else {
					alert_toast("Error disposing batch", 'danger');
					end_load();
				}
			},
			error: function(){
				alert_toast("Connection error", 'danger');
				end_load();
			}
		});
	}
</script>
