<?php
		include "db_connection/db_connection.php";
		include "config/config.php";
		$page   = isset($_GET['page'])     ? intval($_GET['page']) : 1;
		$limit  = isset($_GET['limit'])    ? intval($_GET['limit']) : 100;
		$search =   isset($_GET['search'])   ? mysqli_real_escape_string($connect, $_GET['search']) : "";
		$sortBy = isset($_GET['sortBy'])   ? $_GET['sortBy'] : "mas_stone_type_id";
		$sortDir= isset($_GET['sortDir'])  ? $_GET['sortDir'] : "DESC";

		$offset = ($page - 1) * $limit;

		$allowedCols = ['mas_stone_cat_id','cat_name','stone_name','stone_weight'];
		if(!in_array($sortBy, $allowedCols)) $sortBy = 'mas_stone_type_id';
		$sortDir = ($sortDir == "ASC") ? "ASC" : "DESC";

		$where = "";

		if ($search != "") {

			$search = mysqli_real_escape_string($connect, $search);

			$where = "AND 
				stone_name LIKE '%$search%' 
				OR cat_name LIKE '%$search%'
				OR stone_weight LIKE '%$search%'";
		}


		$totalQuery = mysqli_query($connect, "SELECT COUNT(*) AS cnt FROM mas_stone_type,mas_stone_cat WHERE mas_stone_cat.mas_stone_cat_id=mas_stone_type.mas_stone_cat_id $where");
		$total = mysqli_fetch_assoc($totalQuery)['cnt'];
		$pages = ceil($total / $limit);

		$sql = "SELECT * FROM mas_stone_type,mas_stone_cat WHERE mas_stone_cat.mas_stone_cat_id=mas_stone_type.mas_stone_cat_id $where 
				ORDER BY $sortBy $sortDir 
				LIMIT $limit OFFSET $offset";

		$result = mysqli_query($connect, $sql);

		$data = "";
		$loop=1;
		if(mysqli_num_rows($result)>0){
		while($row = mysqli_fetch_assoc($result)){
						if($loop%2 != "0")
							$bg = "#ffffff";
						else
							$bg = "#f2f2f2";
			$data .= "<tr style='background-color:".$bg."'>
						<td style='text-align:center;'>".$loop."</td>
						<td>".$row['cat_name']."</td>
						<td>".$row['stone_name']."</td>
						<td>".$row['stone_weight']." g</td>
					  </tr>";
					  $loop ++;
		}
		} else {
			$data .= "<tr><td colspan='7' style='text-align:center;'>There are no records in the category</td></tr>";
			
		}
		$response = [
			"rows"   => $data,
			"pages"  => $pages,
			"total"  => $total
		];

		echo json_encode($response);
