<?php
session_start();
$where 					= " WHERE 1=1 ";
$next_page_condition 	= "";
$paging ="";
if($_REQUEST['start']){
	$paging .="&start=".$_REQUEST['start'];
}
###########################################  STRAT FILTER CONITIONS    #####################################
$customer 	= isset($_REQUEST['customer']) ? $_REQUEST['customer'] : "";
if($customer) {
    $where	.= " AND id = '$customer' ";
    $next_page_condition.="&customer=$customer";
}
###########################################  	END  FILTER CONITIONS 	#####################################
###########################################  PAGING WITH PER PAGE  #####################################
$file_name		= "index"; // this is file name which is used during paging  , included at the bottom of the page
$table_name		= "production_planning";
$paging_table_name	= $table_name;
$paging_query		= $where;
$join_query_for_paging = "SELECT * FROM $table_name  $where ";
$page_header = "Production Planning Management";
include("paging/paging_query.inc.php");
############################################ END PAGING WITH PER PAGE  #######################################
//$query = "SELECT * FROM $table_name $where ORDER BY id ASC LIMIT $eu, $limit";
// Fetch minimum and maximum req_date
$date_range_query = "SELECT MIN(req_date) AS min_date, MAX(req_date) AS max_date FROM production_planning";
$date_range_result = mysqli_query($conn, $date_range_query);
$date_range = mysqli_fetch_assoc($date_range_result);
$min_date = $date_range['min_date'];
$max_date = $date_range['max_date'];

// Calculate week-wise range
$start_week = date('oW', strtotime($min_date)); // Get Year + Week Number (YYYYWW)
$end_week = date('oW', strtotime($max_date));   // Get Year + Week Number (YYYYWW)


// Fetch Data Grouped by Part Number and Week
$query = "
    SELECT 
        part_number, 
        YEARWEEK(req_date, 3) AS week_number,  
        SUM(req_qty) as total_req_qty
    FROM production_planning 
    $where
    GROUP BY part_number, YEARWEEK(req_date, 3) 
    ORDER BY part_number, week_number ASC LIMIT $eu, $limit";
$sql = mysqli_query($conn, $query);
$result = mysqli_num_rows($sql);
$total_no_of_records = mysqli_num_rows(mysqli_query($conn, $query));

#################################   DELETE    #########################################
// when we click on delete this area of php code will execute
if(isset($_REQUEST['action']) && $_REQUEST['action']=='delete')
{
	$sql=mysql_query("DELETE FROM $table_name WHERE id = '".$_REQUEST['id']."' ");
	$_SESSION['info_message'] = "IteM deleted sucessfully";
	header("location: $file_name?page=view_country".$next_page_condition.$paging);
	exit;
}
#################################  END DELETE    #########################################
?>
<section class="content-header">
  <h1>
	<?=$page_header;?>
  </h1>
</section>
<style> 
 table {
        width: 100%;
        border-collapse: collapse;
        table-layout: fixed;
    }
    th ,td{
        border: 1px solid #ddd;
        padding: 8px;
        text-align: center;
       white-space: nowrap;
        overflow: hidden;
        text-overflow: ellipsis;
    }
 th:first-child, td:first-child { width: 100px; }
    th:nth-child(2), td:nth-child(2) { width: 100px; }
    th:nth-child(3), td:nth-child(3) { width: 100px; }
    th,td { width: 73px; }
</style>
<section class="content">
	<div class="row">
		<div class="col-lg-12">
			<!----------------------- Message ------------------------------->
			<? if(isset($_SESSION['info_message'])){ ?>
			  <div class="alert alert-success alert-dismissable">
				<button type="button" class="close" data-dismiss="alert" aria-hidden="true">&times;</button>
				<? echo $_SESSION['info_message']; unset($_SESSION['info_message']);?>
			  </div>
			  <? } ?>
			<!----------------------- End Message ------------------------------->
			<div class="box">
				<div class="box-body">
				<!-- search form -->
				<?php include_once('planning_search_options.inc.php');?>
				<!-- /.search form -->
					<form action="index?page=add_country" method="post" name="form1" id="form1">
					<div class="box-footer">
					   <button type="submit" name="add" class="hide btn btn-success">Add </button>
					</div>
					
				<div class="table-responsive">
				<div style="margin-bottom: 10px;">
						<span style="background-color: #adf79d; color: black; padding: 5px; margin-right: 10px;">  In Stock</span>
						<span style="background-color: #fbf9ab; color: black; padding: 5px;">Works Order raised</span>
					</div>
					<?php if ($result > 0) { ?>
					<table id="example1" class="table table-bordered table-striped" style="font-size:13px;">
					<thead>
						<tr>
							<th>Part Number</th>
							<th>Stock</th>
							<th>Works Order Outstanding</th>
							<?php 
								// Generate dynamic weeks from min_date to max_date
								$weeks = [];
								for ($week = $start_week; $week <= $end_week; $week++) {
									$week_start_date = date('d-M', strtotime($min_date . " +".($week - $start_week)." week"));
									echo "<th>$week_start_date</th>";
									$weeks[] = $week;
								}
							?>
						</tr>
					</thead>
					<tbody>
					<?php
						$part_data = [];

						while ($row = mysqli_fetch_assoc($sql)) {
							$part_id = $row['part_number'];
							$week = $row['week_number'];
							$part_data[$part_id][$week] = $row['total_req_qty'];
						}

						$work_order_query = "
							SELECT 
								w.product_id, 
								i.part_number, 
								SUM(w.order_quantity) AS total_production
							FROM work_order w
							JOIN item_management i ON w.product_id = i.id
							GROUP BY w.product_id, i.part_number";

						$work_order_result = mysqli_query($conn, $work_order_query);
						$work_order_data = [];

						while ($wo_row = mysqli_fetch_assoc($work_order_result)) {
							$work_order_data[$wo_row['part_number']] = $wo_row['total_production'];
						}

						foreach ($part_data as $part_id => $weeks_data) {
							
							$inventory_query = "
								SELECT i.quantity_in_stock 
								FROM inventory i 
								JOIN item_management im ON im.id = i.item_id 
								WHERE im.part_number = '$part_id'
							";
							$inventory_result = mysqli_query($conn, $inventory_query);
							$inventory_row = mysqli_fetch_assoc($inventory_result);
							$stock = $inventory_row['quantity_in_stock'] ?? 0; 

							$remaining_stock = $stock;
							$produced_qty = $work_order_data[$part_id] ?? 0; 

							?>
							<tr>
								<td><?php echo ucfirst($part_id); ?></td>
								<td><?php echo $stock; ?></td>
								<td><?php echo array_sum($weeks_data); ?></td>

								<?php
								foreach ($weeks as $week) {
									$qty = $weeks_data[$week] ?? 0;
									$color = "white"; 

									if ($qty > 0) {
										if ($remaining_stock >= $qty) {  
											$color = "#adf79d"; 
											$remaining_stock -= $qty;  
										} elseif (isset($work_order_data[$part_id])) { 
											$color = "#fbf9ab";
										}
									}

									echo "<td style='width:200px; background-color: $color;'>$qty</td>";
								}
								?>
							</tr>
						<?php } ?>

					<!-- Paging starts here  -->
							<? 
								$next_page_condition .="&page=view_production";
								include("paging/paging_row.inc.php") ?>
							<!-- Paging ends starts here  -->
				</tbody>

				</table>
			<?php } else { ?>
				No Order found...
			<?php } ?>


				</div>
				<div class="box-footer">
                   <button type="submit" name="add" class="hide btn btn-success">Add Country</button>
                </div>
				</form>
			</div>
		</div>
	</div>
</section>