<?php
session_start();
require_once '../config.php';

if (!isset($_SESSION['user_id'])) {
    header('Location: ../login.php');
    exit;
}
if (isset($_GET['ajax'])) {

    $q = trim($_GET['q'] ?? '');

    // USER COUNTRY
    $user_id = $_SESSION['user_id'];
    $stmt = $conn->prepare("SELECT country FROM users WHERE id=?");
    $stmt->bind_param("i", $user_id);
    $stmt->execute();
    $user = $stmt->get_result()->fetch_assoc();
    $user_country = $user['country'] ?? '';

    // COUNTRY ID
    $country_id = 0;
    if ($user_country != '') {
        $stmt = $conn->prepare("SELECT id FROM countries WHERE country_name=? LIMIT 1");
        $stmt->bind_param("s", $user_country);
        $stmt->execute();
        $res = $stmt->get_result();
        if ($row = $res->fetch_assoc()) {
            $country_id = $row['id'];
        }
    }

    $where = [];
    $params = [];
    $types = "";

    // SEARCH
    if ($q != '') {
        $where[] = "(p.product_name LIKE ? OR p.generic_name LIKE ? OR p.brand_name LIKE ?)";
        $like = "%$q%";
        $params = [$like,$like,$like];
        $types = "sss";
    }

    // COUNTRY FILTER
    if ($country_id > 0) {
        $where[] = "p.country_id=?";
        $params[] = $country_id;
        $types .= "i";
    }

    // ONLY APPROVED
    // USER COUNTRY
$user_id = $_SESSION['user_id'];

$stmt = $conn->prepare("SELECT country FROM users WHERE id=?");
$stmt->bind_param("i", $user_id);
$stmt->execute();
$user = $stmt->get_result()->fetch_assoc();

$country_id = 0;

if (!empty($user['country'])) {
    $stmt = $conn->prepare("SELECT id FROM countries WHERE country_name=? LIMIT 1");
    $stmt->bind_param("s", $user['country']);
    $stmt->execute();
    $res = $stmt->get_result();
    $row = $res->fetch_assoc();

    if ($row) {
        $country_id = $row['id'];
    }
}

/* COUNTRY FILTER */
if ($country_id > 0) {
    $where[] = "p.country_id=?";
    $params[] = $country_id;
    $types .= "i";
} else {
    $where[] = "1=0";
}

/* APPROVED ONLY */
$where[] = "p.status='approved'";

    $where_sql = $where ? "WHERE ".implode(" AND ",$where) : "";

    $sql = "SELECT p.*,c.country_name
            FROM products p
            LEFT JOIN countries c ON c.id=p.country_id
            $where_sql
            ORDER BY p.id DESC
            LIMIT 50";

    $stmt = $conn->prepare($sql);

    if (!empty($params)) {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $res = $stmt->get_result();

$i = 1;

while($p = $res->fetch_assoc()){

    echo "<tr>
        <td>".$i++."</td>
        <td>".htmlspecialchars($p['product_name'])."</td>
        <td>".htmlspecialchars($p['generic_name'])."</td>
        <td>".htmlspecialchars($p['brand_name'])."</td>
        <td>".htmlspecialchars($p['country_name'])."</td>
        <td>".htmlspecialchars($p['purchase_name'] ?? '')."</td>
        <td></td>
        <td></td>
    </tr>";
}

    exit;
}
function e($v){ return htmlspecialchars($v ?? '', ENT_QUOTES); }

if (empty($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(16));
}
$csrf = $_SESSION['csrf_token'];

$errors = [];
$messages = [];

/* ---------------------------
   Determine current user & (if agency) their country -> map to countries.id
   --------------------------- */
$session_user_id = (int)($_SESSION['user_id'] ?? 0);
$session_role = '';
$session_country_code = ''; // whatever is stored in users.country

if ($session_user_id > 0) {
    $uStmt = $conn->prepare("SELECT role, country FROM users WHERE id = ? LIMIT 1");
    if ($uStmt) {
        $uStmt->bind_param("i", $session_user_id);
        $uStmt->execute();
        $uRes = $uStmt->get_result();
        if ($uRes && $uRes->num_rows === 1) {
            $uRow = $uRes->fetch_assoc();
            $session_role = strtolower(trim($uRow['role'] ?? ''));
            $session_country_code = trim((string)($uRow['country'] ?? ''));
        }
        $uStmt->close();
    }
}

/* helper: try to find a country id in countries table that matches user's stored country value
   We try multiple heuristics to be tolerant of whether users.country is ISO code or country name.
*/
function find_country_id_for_user($conn, $user_country_value) {
    $user_country_value = trim((string)$user_country_value);
    if ($user_country_value === '') return 0;

    $lc = mb_strtolower($user_country_value);

    // fetch all columns for countries to allow matching by code or name
    $res = $conn->query("SELECT * FROM countries");
    if (!$res) return 0;

    while ($r = $res->fetch_assoc()) {
        // try direct matches across any column values
        foreach ($r as $col => $val) {
            if ($val === null) continue;
            $val_s = mb_strtolower((string)$val);
            // exact match
            if ($val_s === $lc) {
                return (int)$r['id'];
            }
        }
        // try fuzzy: match against country_name if present
        if (isset($r['country_name'])) {
            $cn = mb_strtolower((string)$r['country_name']);
            if ($cn === $lc) return (int)$r['id'];
            // if user stored ISO code like "IN" and country_name contains it? unlikely,
            // but try partial containment both ways
            if (mb_stripos($cn, $user_country_value) !== false) return (int)$r['id'];
            if (mb_stripos($user_country_value, $cn) !== false) return (int)$r['id'];
        }
    }
    return 0;
}

/* DELETE PRODUCT */
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['delete_id'])) {

    if (!isset($_POST['csrf_token']) || $_POST['csrf_token'] !== $_SESSION['csrf_token']) {
        $errors[] = 'Invalid request.';
    } else {

        $del_id = (int) $_POST['delete_id'];

        $stmt = $conn->prepare("SELECT file_path FROM products WHERE id=?");
        $stmt->bind_param("i",$del_id);
        $stmt->execute();
        $res=$stmt->get_result();
        $row=$res->fetch_assoc();
        $stmt->close();

        $stmt=$conn->prepare("DELETE FROM products WHERE id=?");
        $stmt->bind_param("i",$del_id);

        if($stmt->execute()){

            if(!empty($row['file_path'])){
                $file=__DIR__.'/../'.$row['file_path'];
                if(file_exists($file)) unlink($file);
            }

            $messages[]="Product deleted successfully.";
        }

        $stmt->close();
    }
}

/* AVAILABLE COLUMNS */

 $allowed_columns = [
    
        /* Commercial Product Info */
        'product_name' => 'Product Name',
        'generic_name' => 'Generic Name',
        'brand_name' => 'Brand Name',
        'standards' => 'Standards',
        'strength' => 'Strength',
        'volume' => 'Volume',
        'dosage_form' => 'Dosage Form',
        'indication' => 'Indication',
        'primary_packing' => 'Primary Packing',
        'secondary_packing' => 'Secondary Packing',
        'shelf_life' => 'Shelf Life',
        'manufactured_by' => 'Manufactured By',
        'marketed_by' => 'Marketed By',
        'distributed_by' => 'Distributed By',
    
        /* Samples Details */
        'samples_batch_no' => 'Samples Batch No',
        'samples_manufacturing_date' => 'Samples Manufacturing Date',
        'samples_expiry_date' => 'Samples Expiry Date',
        'samples_submitted_on' => 'Samples Submitted On',
        'samples_tracking_no' => 'Samples Tracking No',
        'samples_qty' => 'Samples Qty',
        'samples_sent_to' => 'Samples Sent To',
        'samples_destination_address' => 'Samples Destination Address',
    
        /* WS Details */
        'ws_batch_no' => 'WS Batch No',
        'ws_manufacturing_date' => 'WS Manufacturing Date',
        'ws_expiry_date' => 'WS Expiry Date',
        'ws_submitted_on' => 'WS Submitted On',
        'ws_tracking_no' => 'WS Tracking No',
        'ws_qty' => 'WS Qty',
        'ws_sent_to' => 'WS Sent To',
        'ws_destination_address' => 'WS Destination Address',
    
        /* Dossier Details */
        'dossier_number' => 'Dossier Number',
        'submitted_to' => 'Submitted To',
        'dossier_sent_on' => 'Dossier Sent On',
        'query_received_on' => 'Query Received On',
        'query_submitted_on' => 'Query Submitted On',
        'dossier_submitted_in_moh_on' => 'Dossier Submitted In MOH On',
        'moh_comments_received_on' => 'MOH Comments Received On',
        'moh_comments' => 'MOH Comments',
        'moh_comments_submitted_on' => 'MOH Comments Submitted On',
    
        /* Registration */
        'registration_number' => 'Registration Number',
        'registration_details' => 'Registration Details',
        'purchase_name' => 'Purchase Name',
        'registration_exp_date' => 'Registration Expiry Date',
        'renewal_process_start' => 'Renewal Process Start',
        'variation_submitted' => 'Variation Submitted',
        'variation_approval_date' => 'Variation Approval Date',
    
        /* Pricing */
        'qty' => 'Balance Quantity',
        'units_per_packet' => 'Units Per Packet',
        'price_per_packet' => 'Price Per Packet',
        'hsn_no' => 'HSN No.',   // ✅ ADD THIS
    ];

/* SELECTED COLUMNS (from GET or defaults) */

$selected_columns = $_GET['columns'] ?? [
'product_name','brand_name','dossier_number','registration_number','purchase_name'
];

$valid_columns=[];

foreach($selected_columns as $col){
    if(isset($allowed_columns[$col])){
        $valid_columns[]=$col;
    }
}

if(!$valid_columns){
    $valid_columns=['product_name'];
}

/* SEARCH + FILTER (Note: if current user is agency we will override country filter below) */

$q = trim($_GET['q'] ?? '');
$country_filter = (int)($_GET['country_id'] ?? 0);

$page=max(1,(int)($_GET['page'] ?? 1));
$per_page=20;
$offset=($page-1)*$per_page;

$where=[];
$params=[];
$types="";

/* If current user is agency -> force country filter to the agency's registered country */
if ($session_role === 'agency') {
    $agency_country_id = 0;
    if ($session_country_code !== '') {
        $agency_country_id = find_country_id_for_user($conn, $session_country_code);
    }
    // if we found a mapping, force $country_filter to it (override any GET param)
    if ($agency_country_id > 0) {
        $country_filter = $agency_country_id;
    } else {
        // If we couldn't map, force a condition that yields no rows (safe fallback)
        // This prevents an agency seeing other countries' products if mapping is missing.
        // But rather than showing none silently, we'll leave it showing none by setting an impossible id.
        $country_filter = -1;
    }
}

/* SEARCH */

if($q!=''){
    $where[]="(
    p.product_name LIKE ? OR
    p.generic_name LIKE ? OR
    p.brand_name LIKE ? OR
    p.dossier_number LIKE ? OR
    p.registration_number LIKE ?
    )";
    $like="%$q%";
    $params=array_merge($params,[$like,$like,$like,$like,$like]);
    $types.="sssss";
}

/* AGENCY COUNTRY FILTER */
if ($session_role === 'agency') {

    $stmt = $conn->prepare("SELECT id FROM countries WHERE country_name=? LIMIT 1");
    $stmt->bind_param("s", $session_country_code);
    $stmt->execute();
    $res = $stmt->get_result();
    $row = $res->fetch_assoc();

    if ($row) {
        $where[] = "p.country_id=?";
        $params[] = $row['id'];
        $types .= "i";
    } else {
        $where[] = "1=0";
    }
}

/* ONLY APPROVED PRODUCTS */
$where[] = "p.status='approved'";
/* Optional: If the current user is not agency, keep existing behavior (admin, others) */

/* Build WHERE SQL */

$where_sql="";
if($where){
    $where_sql="WHERE ".implode(" AND ",$where);
}

/* TOTAL COUNT */

$sql_count="SELECT COUNT(*) as total FROM products p $where_sql";
$stmt=$conn->prepare($sql_count);

if($types){
    // bind params dynamically
    $stmt->bind_param($types,...$params);
}

$stmt->execute();
$total=$stmt->get_result()->fetch_assoc()['total'];
$stmt->close();

/* PRODUCT LIST */

$sql="SELECT p.*,c.country_name
FROM products p
LEFT JOIN countries c ON c.id=p.country_id
$where_sql
ORDER BY p.created_at DESC
LIMIT ? OFFSET ?";

$stmt=$conn->prepare($sql);

$params2=$params;
$params2[]=$per_page;
$params2[]=$offset;

$types2=$types."ii";

if ($types2 === "ii") {
    // no search/country filters, only limit/offset
    $stmt->bind_param("ii", $per_page, $offset);
} else {
    $stmt->bind_param($types2,...$params2);
}

$stmt->execute();
$result=$stmt->get_result();

$products=[];
while($row=$result->fetch_assoc()){
    $products[]=$row;
}

$stmt->close();

$total_pages=($per_page>0) ? ceil($total/$per_page) : 1;

/* COUNTRIES (for the filter dropdown - visible to non-agency users; for agencies it will be overridden) */

$countries=[];
$res=$conn->query("SELECT id,country_name FROM countries ORDER BY country_name");

while($r=$res->fetch_assoc()){
    $countries[]=$r;
}
?>

<!doctype html>
<html>
<head>

<meta charset="utf-8">
<title>All Commercial Products</title>

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.5.0/css/all.min.css">

<style>

body{
font-family:Inter,Arial;
background:#eef2f7;
margin:0;
padding:80px 25px;
}

/* keep left margin consistent with your sidebar width */
.container{
margin-left:260px;
}

/* CARD */

.card{
background:#fff;
border-radius:14px;
padding:25px;
box-shadow:0 10px 25px rgba(0,0,0,0.05);
}

/* TOP BAR */

.top-bar{
display:flex;
justify-content:space-between;
align-items:center;
margin-bottom:20px;
}

h2{
margin:0;
font-weight:700;
font-size:24px;
color:#1e293b;
}

/* FILTER */

.filter{
background:#f1f5f9;
padding:15px;
border-radius:12px;
display:flex;
gap:10px;
align-items:center;
margin-bottom:25px;
flex-wrap:wrap;
}

.filter input{
flex:1;
min-width:200px;
padding:12px;
border-radius:8px;
border:1px solid #e2e8f0;
font-size:14px;
}

.filter select{
padding:12px;
border-radius:8px;
border:1px solid #e2e8f0;
}

/* BUTTONS */

.btn{
background:#334155;
color:#fff;
padding:10px 18px;
border-radius:20px;
border:none;
cursor:pointer;
font-size:14px;
text-decoration:none; /* remove underline */
display:inline-flex;
align-items:center;
gap:6px;
}

.btn:hover{
background:#1e293b;
text-decoration:none; /* keep underline removed on hover */
}
.btn-small{
width:78px;
height:28px;
display:inline-flex;
align-items:center;
justify-content:center;
border-radius:8px;
background:#e2e8f0;
color:#111827;
border:none;
cursor:pointer;
}

.btn-small:hover{
background:#cbd5e1;
}

/* TABLE */

.table{
width:100%;
border-collapse:separate;
border-spacing:0 12px;
}

/* TABLE HEADER */

.table th{
background:#334155;
color:#fff;
padding:14px;
font-size:13px;
text-align:left;
}

.table th:first-child{
border-radius:10px 0 0 10px;
}

.table th:last-child{
border-radius:0 10px 10px 0;
}

/* TABLE ROWS */

.table td{
background:#f8fafc;
padding:14px;
font-size:13px;
border-top:1px solid #e2e8f0;
border-bottom:1px solid #e2e8f0;
}

.table tr td:first-child{
border-left:1px solid #e2e8f0;
border-radius:10px 0 0 10px;
}

.table tr td:last-child{
border-right:1px solid #e2e8f0;
border-radius:0 10px 10px 0;
}

.table tr:hover td{
background:#f1f5f9;
}

/* PAGINATION */

.pagination{
display:flex;
gap:8px;
margin-top:20px;
flex-wrap:wrap;
}

.pagination a{
padding:8px 12px;
border-radius:8px;
border:1px solid #ddd;
text-decoration:none;
background:#fff;
font-size:13px;
}

.pagination a:hover{
background:#e2e8f0;
}

/* COLUMN DROPDOWN */

#columnDropdown{
display:none;
position:absolute;
top:35px;
left:0;
background:#fff;
border:1px solid #ddd;
border-radius:10px;
padding:15px;
box-shadow:0 4px 20px rgba(0,0,0,0.1);
width:320px;
z-index:100;
}

.column-grid{
display:grid;
grid-template-columns:repeat(2,1fr);
gap:6px;
max-height:200px;
overflow:auto;
}

/* responsive adjustments to keep exact same design but work well on small screens */
@media (max-width:900px) {
  .container{ margin-left:0; padding:20px; }
  body{ padding:20px 12px; }
  .filter{ flex-direction:column; align-items:stretch; }
  .top-bar{ flex-direction:column; gap:12px; align-items:flex-start; }
  .btn{ width:100%; justify-content:center; }
  .table th, .table td { padding:10px; font-size:13px; }
}

/* ADD THIS CSS INSIDE YOUR <style> BELOW EXISTING CSS */

/* ===== Responsive Table Wrapper ===== */

.table-wrapper{
width:100%;
overflow-x:auto;
}

/* Sticky header */

.table thead th{
position:sticky;
top:0;
z-index:2;
}

/* Improve mobile layout */

@media (max-width:900px){

.container{
margin-left:0;
padding:10px;
}

body{
padding:80px 10px;
}

.card{
padding:18px;
}

.top-bar{
flex-direction:column;
align-items:flex-start;
gap:10px;
}

.filter{
flex-direction:column;
align-items:stretch;
}

.filter input{
width:100%;
}

.btn-small{
width:100%;
justify-content:center;
}

.table{
min-width:900px;
}

}
.export-btn{
background:#16a34a;
color:#fff;
padding:10px 16px;
border-radius:10px;
font-size:14px;
text-decoration:none;
display:flex;
align-items:center;
gap:8px;
font-weight:600;
}

.export-btn:hover{
background:#15803d;
}
</style>

</head>

<body>

<?php include 'agencysidebar.php'; ?>
<?php include '../header.php'; ?>

<div class="container">

<div class="card">

<!-- TOP BAR -->
<div class="top-bar">

<h2>All Commercial Products</h2>

<a href="export_products_excel.php" class="export-btn">
<i class="fa fa-file-excel"></i> Export Excel
</a>

</div>

<!-- show any messages -->
<?php if(!empty($messages)): ?>
  <?php foreach($messages as $m): ?>
    <div style="margin-bottom:12px;padding:10px;border-radius:8px;background:#e6ffef;color:#064e2a;"><?php echo e($m); ?></div>
  <?php endforeach; ?>
<?php endif; ?>

<?php if(!empty($errors)): ?>
  <?php foreach($errors as $er): ?>
    <div style="margin-bottom:12px;padding:10px;border-radius:8px;background:#ffecec;color:#5b1313;"><?php echo e($er); ?></div>
  <?php endforeach; ?>
<?php endif; ?>

<!-- COLUMN SELECT -->

<div style="margin-bottom:15px; position:relative; display:inline-block;">

<button type="button" class="btn-small" onclick="toggleColumns()">
<i class="fa fa-table"></i> Columns
</button>

<div id="columnDropdown">

<form method="get">

<div class="column-grid">

<?php foreach($allowed_columns as $key=>$label): ?>

<label>
<input type="checkbox" name="columns[]" value="<?php echo $key ?>"
<?php if(in_array($key,$valid_columns)) echo "checked"; ?>>
<?php echo $label ?>
</label>

<?php endforeach; ?>

</div>

<br>

<button class="btn" style="width:100%">Apply Columns</button>

</form>

</div>

</div>
<form class="filter" method="get">

<input type="search" id="searchInput" name="q"
placeholder="Search product..."
value="<?php echo e($q); ?>">

<button class="btn-small">
<i class="fa fa-search"></i> Search
</button>

</form>
<div class="table-wrapper">
<table class="table">

<thead>

<tr>

<th>No.</th>

<?php foreach($valid_columns as $col): ?>
<th><?php echo $allowed_columns[$col]; ?></th>
<?php endforeach; ?>

<th>Country</th>
<th>Purchase Name</th>
<th>File</th>
<th>Action</th>

</tr>

</thead>

<tbody id="productTable">

<?php if(empty($products)): ?>
<tr>
  <td colspan="<?php echo (5 + count($valid_columns)); ?>" style="text-align:center;padding:20px;background:transparent;">No products found.</td>
</tr>
<?php endif; ?>

<?php $i = $offset + 1; foreach($products as $p): ?>

<tr>

<td><?php echo $i++; ?></td>

<?php foreach($valid_columns as $col): ?>
<td><?php echo e($p[$col]); ?></td>
<?php endforeach; ?>

<td><?php echo e($p['country_name']); ?></td>

<td><?php echo e($p['purchase_name'] ?? ''); ?></td>

<td>

<?php if(!empty($p['file_path'])): ?>

<a class="btn-small" target="_blank" href="../<?php echo e($p['file_path']); ?>">
<i class="fa fa-file"></i>
</a>

<?php endif; ?>

</td>

<td>

<div class="action-group" style="display:flex;gap:6px;">

<div class="action-group" style="display:flex;gap:6px;align-items:center;">
<div class="action-group" style="display:flex;gap:8px;align-items:center;">

<!-- Preview -->
<button type="button" class="btn-small"
onclick="previewProduct(`
<b>Product:</b> <?php echo e($p['product_name']); ?><br>
<b>Brand:</b> <?php echo e($p['brand_name']); ?><br>
<b>Dossier:</b> <?php echo e($p['dossier_number']); ?><br>
<b>Country:</b> <?php echo e($p['country_name']); ?>
`)">

<i class="fa fa-eye"></i>

</button>
</div>

</form>

</div>
</form>

</div>

</td>

</tr>

<?php endforeach; ?>

</tbody>

</table>
</div>
<div class="pagination">

<?php for($i=1;$i<=$total_pages;$i++): ?>

<?php
  // build query preserving q and columns (and country_id if not agency)
  $qs = [];
  if ($q !== '') $qs[] = 'q='.urlencode($q);
  // preserve selected columns
  foreach ($valid_columns as $vc) {
      $qs[] = 'columns[]='.urlencode($vc);
  }
  if ($session_role !== 'agency' && $country_filter) {
      $qs[] = 'page='.$i;
  } elseif ($session_role === 'agency' && $country_filter) {
      // for agency also include country_id so page links keep the same filter
      $qs[] = 'page='.$i;
  }
  $qs[] = 'page='.$i;
  $link = '?'.implode('&', $qs);
?>

<a href="<?php echo $link; ?>" <?php if($i===$page) echo 'style="background:#334155;color:#fff;border-color:#334155;"'; ?>>
<?php echo $i; ?>
</a>

<?php endfor; ?>

</div>

</div>

</div>
<div id="previewModal" style="display:none;
position:fixed;
top:0;
left:0;
width:100%;
height:100%;
background:rgba(0,0,0,0.6);
align-items:center;
justify-content:center;
z-index:9999;">

<div style="background:#fff;
padding:25px;
border-radius:12px;
max-width:600px;
width:90%;">

<h3>Product Details</h3>

<div id="previewContent"></div>

<button onclick="closePreview()" class="btn-small"
style="margin-top:15px">Close</button>

</div>

</div>
<script>

function previewProduct(data){

const modal=document.getElementById("previewModal");
const content=document.getElementById("previewContent");

content.innerHTML=data;

modal.style.display="flex";

}

function closePreview(){

document.getElementById("previewModal").style.display="none";

}

</script>
<script>

function toggleColumns(){

var box=document.getElementById("columnDropdown");

box.style.display = box.style.display === "block" ? "none" : "block";

}

document.addEventListener("click",function(e){

var box=document.getElementById("columnDropdown");

if(!e.target.closest("#columnDropdown") && !e.target.closest("button")){
box.style.display="none";
}

});

</script>
<script>

let timer;

document.getElementById("searchInput").addEventListener("keyup", function(){

    clearTimeout(timer);

    let query = this.value;

    timer = setTimeout(() => {

        fetch("?ajax=1&q=" + encodeURIComponent(query))
        .then(res => res.text())
        .then(data => {
            document.getElementById("productTable").innerHTML = data;
        });

    }, 300);

});

</script>
</body>
</html>