<?php
// cv_project_category.php
// Master page for CV Project Category (list + form + save/delete)
// Layout matches the Skills Master screenshot (card header with back button, compact fields).

require_once __DIR__ . '/../includes/auth.php';
require_login();

/* ---------- CONFIG ---------- */
$page_title = 'CV Project Category';
$TABLE      = 'jos_app_can_projectcategory';

$FIELDS = [
    ['name'=>'name','label'=>'Name','type'=>'text','required'=>true],
    ['name'=>'orderby','label'=>'Order Level','type'=>'number','attrs'=>['min'=>0,'step'=>1]],
];
/* ---------------------------- */

/* ---------- CSRF helpers ---------- */
if (session_status() === PHP_SESSION_NONE) session_start();
if (!function_exists('csrf_token')) {
    function csrf_token(): string {
        if (empty($_SESSION['csrf_token'])) $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
        return $_SESSION['csrf_token'];
    }
}
if (!function_exists('csrf_field')) {
    function csrf_field(): string {
        return '<input type="hidden" name="csrf" value="' . htmlspecialchars(csrf_token(), ENT_QUOTES, 'UTF-8') . '">';
    }
}
if (!function_exists('verify_csrf')) {
    function verify_csrf(?string $token): bool {
        if (empty($token) || empty($_SESSION['csrf_token'])) return false;
        return hash_equals($_SESSION['csrf_token'], $token);
    }
}

/* ---------- helpers ---------- */
function h($v){ return htmlspecialchars((string)$v, ENT_QUOTES, 'UTF-8'); }
function keep_params(array $changes = []) {
  $qs = $_GET;
  foreach ($changes as $k=>$v) { if ($v===null) unset($qs[$k]); else $qs[$k]=$v; }
  return '?' . http_build_query($qs);
}
function clean($v){ return trim((string)$v); }
function col_exists($con,$table,$col){
  $col   = mysqli_real_escape_string($con,$col);
  $table = mysqli_real_escape_string($con,$table);
  $sql   = "SHOW COLUMNS FROM `$table` LIKE '$col'";
  $r = @mysqli_query($con,$sql);
  return ($r && mysqli_num_rows($r)>0);
}
function table_exists($con,$table){
  $table = mysqli_real_escape_string($con,$table);
  $sql = "SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '$table' LIMIT 1";
  $r = @mysqli_query($con,$sql);
  return ($r && mysqli_num_rows($r)>0);
}

/* ---------- graceful fail if table missing ---------- */
if (!table_exists($con, $TABLE)) {
    http_response_code(500);
    $dbNameRow = @mysqli_fetch_assoc(@mysqli_query($con, "SELECT DATABASE() AS db"));
    $dbName = $dbNameRow['db'] ?? 'database';
    echo '<div style="font-family:sans-serif;padding:24px;background:#fff5f5;border:1px solid #ffcccc;color:#7f1d1d">';
    echo '<h2>⚠ Configuration Error</h2>';
    echo '<p>The master page is set to use table <strong>' . h($TABLE) . '</strong>, but this table does not exist in the database <strong>' . h($dbName) . '</strong>.</p>';
    echo '<p><strong>To fix this:</strong></p>';
    echo '<ul><li>Edit this file and set <code>$TABLE</code> to the correct table name.</li><li>Create the table if required.</li></ul>';
    echo '<p><a href="/adminconsole/masters/index.php" style="color:#1d4ed8">← Back to Masters</a></p>';
    echo '</div>';
    exit;
}

/* ---------- ensure schema ---------- */
function ensure_schema($con,$table){
  if(!col_exists($con,$table,'status')){
    @mysqli_query($con,"ALTER TABLE `$table` ADD `status` TINYINT(1) NOT NULL DEFAULT 1");
  }
  $has_orderby  = col_exists($con,$table,'orderby');
  $has_order_by = col_exists($con,$table,'order_by');
  if(!$has_orderby){
    @mysqli_query($con,"ALTER TABLE `$table` ADD `orderby` INT NOT NULL DEFAULT 0");
    if($has_order_by){
      @mysqli_query($con,"UPDATE `$table` SET `orderby` = `order_by` WHERE 1");
    }
  }
}
ensure_schema($con,$TABLE);

/* ---------- ordering helpers ---------- */
$has_order_by = col_exists($con, $TABLE, 'order_by');
$order_select_expr = $has_order_by ? 'COALESCE(orderby, order_by, 0) AS orderby' : 'COALESCE(orderby, 0) AS orderby';
$order_coalesce    = $has_order_by ? 'COALESCE(t.orderby, t.order_by, 0)' : 'COALESCE(t.orderby, 0)';

/* ---------- detect single-child table for counts (optional) ---------- */
$CHILD_TBL = null; $CHILD_FK = null;
foreach ([
  ['jos_app_can_project','category_id'],
  ['jos_app_can_projects','category_id'],
  ['jos_app_projects','category_id'],
] as $cand){
  if (table_exists($con,$cand[0])) { $CHILD_TBL=$cand[0]; $CHILD_FK=$cand[1]; break; }
}

/* ---------- flash ---------- */
$err = '';
$ok  = clean($_GET['ok'] ?? '');

/* ---------- POST handling ---------- */
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
  if (!verify_csrf($_POST['csrf'] ?? null)) {
    $err = 'Invalid request. Please refresh the page and try again.';
  } else {
    if (isset($_POST['save'])) {
      $id = (int)($_POST['id'] ?? 0);
      $values = [];
      foreach ($GLOBALS['FIELDS'] as $f) {
        $n = $f['name'];
        $values[$n] = (isset($_POST[$n]) ? clean($_POST[$n]) : '');
      }

      $firstField = $FIELDS[0]['name'] ?? 'name';
      if ($values[$firstField] === '') {
        $err = ($FIELDS[0]['label'] ?? $firstField) . ' is required.';
      } else {
        if (col_exists($con,$TABLE,$firstField)){
          $sql = "SELECT `id` FROM `$TABLE` WHERE LOWER(`$firstField`) = LOWER(?) AND `id` <> ?";
          $st  = mysqli_prepare($con,$sql);
          if ($st) {
            mysqli_stmt_bind_param($st,'si',$values[$firstField],$id);
            mysqli_stmt_execute($st);
            $dup = mysqli_stmt_get_result($st);
            if ($dup && mysqli_num_rows($dup)>0) {
              $err = 'A record with this ' . ($FIELDS[0]['label'] ?? $firstField) . ' already exists.';
            }
            mysqli_stmt_close($st);
          }
        }

        if (!$err) {
          if ($id > 0) {
            // UPDATE
            $setParts=[]; $types=''; $params=[];
            foreach ($FIELDS as $f) {
              $col = $f['name']; $val = $values[$col] ?? '';
              $setParts[] = "`$col` = ?";
              $isNumberField = (isset($f['type']) && $f['type']==='number');
              $types .= $isNumberField ? 'i' : 's';
              $params[] = $isNumberField ? (int)$val : $val;
            }
            if (!array_key_exists('status',$values) && col_exists($con,$TABLE,'status')) {
              $setParts[] = "`status` = ?"; $types.='i'; $params[]=(int)($_POST['status'] ?? 1);
            }
            if (!array_key_exists('orderby',$values) && col_exists($con,$TABLE,'orderby') && isset($_POST['orderby'])) {
              $setParts[] = "`orderby` = ?"; $types.='i'; $params[]=(int)$_POST['orderby'];
            }
            $sql = "UPDATE `$TABLE` SET ".implode(',',$setParts)." WHERE `id`=?";
            $types .= 'i'; $params[] = $id;
            $st = mysqli_prepare($con,$sql);
            if ($st) {
              mysqli_stmt_bind_param($st,$types,...$params);
              if (!mysqli_stmt_execute($st)) $err = 'Database error while saving (update).';
              else { $ok='Updated successfully.'; header('Location: '.keep_params(['mode'=>null,'id'=>null,'ok'=>$ok])); exit; }
              mysqli_stmt_close($st);
            } else { $err = 'Database prepare error while updating.'; }
          } else {
            // INSERT
            $cols=[]; $place=[]; $types=''; $params=[];
            foreach ($FIELDS as $f) {
              $col = $f['name']; $val = $values[$col] ?? '';
              $cols[] = "`$col`"; $place[] = '?';
              $isNumberField = (isset($f['type']) && $f['type']==='number');
              $types .= $isNumberField ? 'i' : 's';
              $params[] = $isNumberField ? (int)$val : $val;
            }
            if (!in_array('status',array_keys($values)) && col_exists($con,$TABLE,'status')){
              $cols[]='`status`'; $place[]='?'; $types.='i'; $params[]=(int)($_POST['status'] ?? 1);
            }
            if (!in_array('orderby', array_keys($values)) && col_exists($con,$TABLE,'orderby') && isset($_POST['orderby'])) {
              $cols[]='`orderby`'; $place[]='?'; $types.='i'; $params[]=(int)$_POST['orderby'];
            }
            $sql = "INSERT INTO `$TABLE` (".implode(',',$cols).") VALUES (".implode(',',$place).")";
            $st = mysqli_prepare($con,$sql);
            if ($st) {
              if ($types !== '') mysqli_stmt_bind_param($st,$types,...$params);
              if (!mysqli_stmt_execute($st)) $err = 'Database error while saving (insert).';
              else { $ok='Saved successfully.'; header('Location: '.keep_params(['mode'=>null,'id'=>null,'ok'=>$ok])); exit; }
              mysqli_stmt_close($st);
            } else { $err = 'Database prepare error while inserting.'; }
          }
        }
      }
    }

    if (!$err && isset($_POST['delete'])) {
      $id = (int)($_POST['id'] ?? 0);
      if ($id > 0) {
        $sql = "DELETE FROM `$TABLE` WHERE `id`=?";
        $st  = mysqli_prepare($con,$sql);
        if ($st) {
          mysqli_stmt_bind_param($st,'i',$id);
          if (!mysqli_stmt_execute($st)) $err = 'Unable to delete this record.';
          else { header('Location: '.keep_params(['mode'=>null,'id'=>null,'ok'=>'Deleted successfully.'])); exit; }
          mysqli_stmt_close($st);
        } else { $err = 'Database prepare error while deleting.'; }
      } else $err = 'Invalid record.';
    }
  }
}

/* ---------- routing and fetch ---------- */
$mode = $_GET['mode'] ?? 'list';

$rec = ['id'=>0,'status'=>1,'orderby'=>0,'name'=>''];
if ($mode==='form' && isset($_GET['id'])) {
  $id = (int)$_GET['id'];
  if ($id>0){
    $fieldNames = array_map(function($f){ return $f['name']; }, $FIELDS);
    $fieldList  = implode(',', array_map(function($c){ return "`$c`"; }, $fieldNames));
    $selectParts = [];
    if ($fieldList !== '') $selectParts[] = $fieldList;
    $selectParts[] = $order_select_expr;
    if (col_exists($con,$TABLE,'status')) $selectParts[] = 'status';
    $sql = "SELECT `id`, ".implode(',',$selectParts)." FROM `$TABLE` WHERE `id`=?";
    $st = mysqli_prepare($con,$sql);
    if ($st) {
      mysqli_stmt_bind_param($st,'i',$id);
      mysqli_stmt_execute($st);
      $r = mysqli_stmt_get_result($st);
      if ($r && $row = mysqli_fetch_assoc($r)) {
        foreach ($row as $k=>$v) $rec[$k] = $v;
      }
      mysqli_stmt_close($st);
    }
  }
}

/* ---------- render ---------- */
$is_edit      = ($mode === 'form' && !empty($rec['id']));
$form_title   = $is_edit ? "Edit $page_title" : "Add $page_title";
$form_subtitle= $is_edit ? "Update details" : "Create a new record";

ob_start();
?>
<div class="master-wrap">
  <div class="headbar">
    <div class="left"><h2 style="margin:0"><?= h($page_title) ?></h2></div>
    <!-- Right side intentionally empty (no "Masters" button) -->
  </div>

  <?php if ($ok): ?><div class="alert success" style="max-width:900px;margin:12px auto"><?= h($ok) ?></div><?php endif; ?>
  <?php if ($err): ?><div class="alert error" style="max-width:900px;margin:12px auto"><?= h($err) ?></div><?php endif; ?>

  <?php if ($mode==='form'): ?>
    <div class="card form-card">
      <div class="card-header">
        <div class="title">
          <h3 style="margin:0;font-weight:600"><?= h($form_title) ?></h3>
          <div class="subtitle" style="color:#9ca3af;font-size:13px"><?= h($form_subtitle) ?></div>
        </div>
        <div class="actions">
          <a class="btn secondary" href="<?= keep_params(['mode'=>null,'id'=>null]) ?>">Back to List</a>
        </div>
      </div>

      <form method="post" autocomplete="off" class="master-form" style="padding:18px 20px 26px;">
        <?= csrf_field() ?>
        <input type="hidden" name="id" value="<?= (int)$rec['id'] ?>">

        <div class="form-grid">
          <div class="form-row full">
            <label class="field-label">Title<span class="req">*</span></label>
            <input type="text" name="name" value="<?= h($rec['name'] ?? '') ?>" placeholder="Enter title">
          </div>

          <div class="form-row">
            <label class="field-label">Status</label>
            <select name="status">
              <option value="1" <?= ($rec['status']==1)?'selected':'' ?>>Active</option>
              <option value="0" <?= ($rec['status']==0)?'selected':'' ?>>Inactive</option>
            </select>
          </div>

          <div class="form-row">
            <label class="field-label"><?= h($FIELDS[1]['label'] ?? 'Order Level') ?></label>
            <input type="number" name="orderby" value="<?= (int)($rec['orderby'] ?? 0) ?>" min="<?= ($FIELDS[1]['attrs']['min'] ?? 0) ?>" step="<?= ($FIELDS[1]['attrs']['step'] ?? 1) ?>">
          </div>
        </div>

        <div class="form-actions">
          <button class="btn primary" name="save" value="1">Save</button>
        </div>
      </form>
    </div>

  <?php else: // LIST view ?>

    <?php
      $q        = clean($_GET['q'] ?? '');
      $orderEq  = clean($_GET['order_eq'] ?? '');
      $sort     = clean($_GET['sort'] ?? 'order_asc');
      $view     = clean($_GET['view'] ?? 'last50');
      $noRec    = (int)($_GET['no_records'] ?? 0);

      $where = "WHERE 1";
      $args  = [];
      $types = '';

      $searchCol = $FIELDS[0]['name'] ?? 'name';
      if ($q !== '') { $where .= " AND t.`$searchCol` LIKE ?"; $args[]="%$q%"; $types.='s'; }
      if ($orderEq !== '' && is_numeric($orderEq)) { $where .= " AND {$order_coalesce}=?"; $args[]=(int)$orderEq; $types.='i'; }

      $sortMap = [
        'order_asc'   => $order_coalesce . ' ASC, t.id DESC',
        'order_desc'  => $order_coalesce . ' DESC, t.id DESC',
        'name_asc'    => "t.`$searchCol` ASC",
        'name_desc'   => "t.`$searchCol` DESC",
        'id_desc'     => 't.id DESC',
        'id_asc'      => 't.id ASC',
        'count_desc'  => 'COALESCE(u.ucnt,0) DESC, t.`' . $searchCol . '` ASC',
        'count_asc'   => 'COALESCE(u.ucnt,0) ASC, t.`' . $searchCol . '` ASC',
      ];
      $orderBy  = $sortMap[$sort] ?? $sortMap['order_asc'];
      $limitSql = ($view==='all') ? '' : ' LIMIT 50';

      if ($CHILD_TBL) {
        $sql = "SELECT t.id, t.`$searchCol` AS name, t.status, {$order_coalesce} AS orderby,
                       COALESCE(u.ucnt,0) AS usage_count
                FROM `$TABLE` t
                LEFT JOIN (
                  SELECT `$CHILD_FK` AS cid, COUNT(*) AS ucnt
                  FROM `$CHILD_TBL`
                  GROUP BY `$CHILD_FK`
                ) u ON u.cid = t.id
                $where";
        if ($noRec===1) { $sql .= " AND COALESCE(u.ucnt,0)=0"; }
        $sql .= " ORDER BY $orderBy $limitSql";
      } else {
        $sql = "SELECT t.id, t.`$searchCol` AS name, t.status, {$order_coalesce} AS orderby,
                       0 AS usage_count
                FROM `$TABLE` t
                $where
                ORDER BY $orderBy $limitSql";
      }

      $st = mysqli_prepare($con,$sql);
      if ($st) {
        if ($types!=='') mysqli_stmt_bind_param($st,$types,...$args);
        mysqli_stmt_execute($st);
        $rows = mysqli_stmt_get_result($st);
        mysqli_stmt_close($st);
      } else {
        $rows = false;
        $err = 'Database error while fetching list.';
      }
    ?>

    <div class="card">
      <form class="toolbar" method="get" style="position:sticky;top:0;z-index:2;padding-top:10px">
        <input type="hidden" name="mode" value="list">
        <input class="inp" type="text" name="q" placeholder="Search <?= h($FIELDS[0]['label'] ?? 'name') ?>…" value="<?= h($q) ?>" style="min-width:240px">
        <input class="inp" type="number" name="order_eq" placeholder="Order Level =" value="<?= h($orderEq) ?>" style="width:150px">
        <select class="inp" name="sort" style="width:230px">
          <option value="order_asc"  <?= $sort==='order_asc'?'selected':'' ?>>Order Level ↑</option>
          <option value="order_desc" <?= $sort==='order_desc'?'selected':'' ?>>Order Level ↓</option>
          <option value="name_asc"   <?= $sort==='name_asc'?'selected':'' ?>>Name A–Z</option>
          <option value="name_desc"  <?= $sort==='name_desc'?'selected':'' ?>>Name Z–A</option>
          <option value="id_desc"    <?= $sort==='id_desc'?'selected':'' ?>>Newest first</option>
          <option value="id_asc"     <?= $sort==='id_asc'?'selected':'' ?>>Oldest first</option>
          <?php if ($CHILD_TBL): ?>
          <option value="count_desc" <?= $sort==='count_desc'?'selected':'' ?>>Usage Count ↓</option>
          <option value="count_asc"  <?= $sort==='count_asc'?'selected':''  ?>>Usage Count ↑</option>
          <?php endif; ?>
        </select>
        <?php if ($CHILD_TBL): ?>
          <label style="display:flex;align-items:center;gap:6px;color:#9ca3af">
            <input type="checkbox" name="no_records" value="1" <?= $noRec? 'checked':'' ?> onchange="this.form.submit()">
            No Records
          </label>
        <?php endif; ?>
        <div class="spacer"></div>
        <a class="btn <?= $view==='last50'?'primary':'' ?>" href="<?= keep_params(['view'=>'last50']) ?>">Last 50</a>
        <a class="btn <?= $view==='all'?'primary':'' ?>" href="<?= keep_params(['view'=>'all']) ?>">View All</a>
        <a class="btn" href="<?= keep_params(['mode'=>'form','id'=>null]) ?>">+ Add New</a>
      </form>

      <div class="table">
        <table>
          <thead>
            <tr>
              <th style="width:70px">Sr</th>
              <th><?= h($FIELDS[0]['label'] ?? 'Name') ?></th>
              <th style="width:140px">Order Level</th>
              <?php if ($CHILD_TBL): ?><th style="width:120px">Count</th><?php endif; ?>
              <th style="width:120px">Status</th>
              <th style="width:120px">Action</th>
            </tr>
          </thead>
          <tbody>
            <?php
              $sr=0;
              if ($rows && mysqli_num_rows($rows)>0):
                while($r = mysqli_fetch_assoc($rows)): $sr++;
            ?>
              <tr>
                <td><?= $sr ?></td>
                <td><?= h($r['name']) ?></td>
                <td><?= (int)$r['orderby'] ?></td>
                <?php if ($CHILD_TBL): ?><td><?= (int)$r['usage_count'] ?></td><?php endif; ?>
                <td>
                  <?php if ((int)$r['status']===1): ?>
                    <span class="badge success">Active</span>
                  <?php else: ?>
                    <span class="badge">Inactive</span>
                  <?php endif; ?>
                </td>
                <td><a class="btn" href="<?= keep_params(['mode'=>'form','id'=>$r['id']]) ?>">Edit</a></td>
              </tr>
            <?php endwhile; else: ?>
              <tr><td colspan="<?= $CHILD_TBL?6:5 ?>" style="text-align:center;color:#9ca3af">No records found.</td></tr>
            <?php endif; ?>
          </tbody>
        </table>
      </div>
    </div>

  <?php endif; ?>
</div>

<link rel="stylesheet" href="/adminconsole/assets/ui.css?v=1.0.3">

<!-- Scoped CSS matching the screenshot (dark card, header with back button, aligned fields) -->
<style>
/* card header */
.form-card{ max-width:900px; margin:18px auto; border-radius:12px; background:linear-gradient(180deg, rgba(11,18,32,0.85), rgba(8,13,22,0.85)); box-shadow:0 6px 30px rgba(2,6,23,0.55); border:1px solid rgba(255,255,255,0.03); overflow:hidden; }
.form-card .card-header{ display:flex; align-items:center; justify-content:space-between; padding:18px 20px; border-bottom:1px solid rgba(255,255,255,0.02); }
.form-card .card-header .title h3{ color:#e6eef8; font-size:18px; margin:0; }
.form-card .card-header .subtitle{ margin-top:4px; color:#9ca3af; font-size:13px; }

/* header action (Back to List) */
.form-card .card-header .actions .btn.secondary{
  background:#1f2937; color:#e6eef8; border-radius:8px; padding:8px 12px; display:inline-block; text-decoration:none; border:1px solid rgba(255,255,255,0.03);
}
.form-card .card-header .actions .btn.secondary:hover{ opacity:.95; transform:none; }

/* form grid */
.master-form .form-grid{ display:grid; grid-template-columns: 1fr 1fr; gap:14px; padding-top:12px; }
.master-form .form-grid .full{ grid-column:1 / -1; }
@media (max-width:900px){ .master-form .form-grid{ grid-template-columns:1fr; } }

/* label and inputs */
.field-label{ display:block; color:#cbd5e1; font-size:13px; margin-bottom:6px; }
.master-form input[type="text"],
.master-form input[type="number"],
.master-form select,
.master-form textarea{
  background: rgba(255,255,255,0.03); border:1px solid rgba(255,255,255,0.04); color:#e6eef8; padding:10px 12px; border-radius:8px; width:100%; box-sizing:border-box;
  outline:none; transition:box-shadow .12s, border-color .12s;
}
.master-form input::placeholder{ color:#6b7280; }
.master-form input:focus,
.master-form select:focus,
.master-form textarea:focus{ box-shadow:0 4px 18px rgba(2,6,23,0.5); border-color: rgba(59,130,246,0.12); }

/* form actions */
.form-actions{ padding-top:16px; display:flex; align-items:center; gap:10px; max-width:900px; }
.form-actions .btn.primary{ background:#10b981; color:#04201a; border-radius:8px; padding:8px 14px; border:none; font-weight:600; box-shadow:0 6px 18px rgba(16,185,129,0.08); }
.form-actions .btn.primary:hover{ opacity:0.95; }

/* small niceties */
.req{ color:#34d399; margin-left:6px; font-weight:600; }
.badge.success{ background:#064e3b; color:#6ee7b7; padding:6px 8px; border-radius:6px; font-size:13px; display:inline-block; }
.table{ margin-top:12px; overflow:auto; }
.card{ max-width:1100px; margin:18px auto; padding:0; border-radius:10px; background:transparent; }
.headbar{ max-width:1100px; margin:18px auto; display:flex; justify-content:space-between; align-items:center; }
</style>
 
<?php echo ob_get_clean(); ?>
