<?php
/**
 * make_report.php  (Agent Console script)
 * Creates a report page (standalone PHP) under /adminconsole/reports/.
 *
 * Reads JSON args from $safe_args with schema shown below.
 * Does NOT require auth here; the generated page WILL require auth.
 *
 * JSON schema (minimal):
 * {
 *   "filename": "sample_report.php",
 *   "page_title": "Sample Report",
 *   "sql_select": "r.id, r.title, r.owner_name, r.status, r.amount, r.created_at",
 *   "sql_from": "FROM sample_reports r",
 *   "base_where": "",
 *   "sort_map": {
 *     "newest": "ORDER BY r.id DESC",
 *     "oldest": "ORDER BY r.id ASC",
 *     "title_asc": "ORDER BY r.title ASC, r.id DESC",
 *     "title_desc": "ORDER BY r.title DESC, r.id DESC",
 *     "amount_asc": "ORDER BY r.amount ASC, r.id DESC",
 *     "amount_desc": "ORDER BY r.amount DESC, r.id DESC"
 *   },
 *   "columns": [
 *     {"type":"sr","label":"SR No","width":"70px"},
 *     {"type":"text","key":"title","label":"Title",
 *       "sub":"Owner: {{owner_name}}"},
 *     {"type":"money","key":"amount","label":"Amount"},
 *     {"type":"status","key":"status","label":"Status"},
 *     {"type":"date","key":"created_at","label":"Created","format":"d M Y"},
 *     {"type":"actions","label":"Actions","width":"140px"}
 *   ]
 * }
 *
 * Notes:
 * - If "columns" omitted, basic columns are inferred from sql_select.
 * - filename is required and must end with .php
 */

if (!isset($con_ref)) { echo "[ERR] DB handle not passed.\n"; }
$args_raw = isset($safe_args) ? trim((string)$safe_args) : '';
if ($args_raw === '') {
  echo "[ERR] No args provided. Provide JSON in Args box.\n";
  return;
}

$cfg = json_decode($args_raw, true);
if (!$cfg) {
  echo "[ERR] Args are not valid JSON.\n";
  return;
}

function val($a,$k,$d=null){ return array_key_exists($k,$a)?$a[$k]:$d; }
$filename    = val($cfg,'filename','');
$page_title  = val($cfg,'page_title','Report');
$sql_select  = val($cfg,'sql_select','');
$sql_from    = val($cfg,'sql_from','');
$base_where  = val($cfg,'base_where','');
$sort_map    = val($cfg,'sort_map',[]);
$columns     = val($cfg,'columns',[]);
$default_ps  = (int)val($cfg,'default_page_size',50);

if (!$filename || !preg_match('/^[a-z0-9_\-]+\.php$/i',$filename)){
  echo "[ERR] filename required (letters/digits/_/-) and must end with .php\n";
  return;
}
if ($sql_select==='' || stripos($sql_from,'from ')===false){
  echo "[ERR] sql_select and sql_from are required. sql_from must include FROM.\n";
  return;
}

$reports_dir = dirname(__DIR__,1) . '/reports';
if (!is_dir($reports_dir)) { @mkdir($reports_dir, 0775, true); }
$target = $reports_dir . '/' . $filename;
if (file_exists($target)) {
  echo "[WARN] File already exists, will overwrite: $target\n";
}

/* ------- Helper: build $SORT_MAP PHP array ------- */
function build_sort_map_php($m){
  if (!is_array($m) || empty($m)) {
    $m = [
      'newest'    => 'ORDER BY id DESC',
      'oldest'    => 'ORDER BY id ASC',
      'name_asc'  => 'ORDER BY 1 ASC',
      'name_desc' => 'ORDER BY 1 DESC'
    ];
  }
  $lines = [];
  foreach($m as $k=>$v){
    $kq = var_export($k,true);
    $vq = var_export($v,true);
    $lines[] = "  $kq => $vq";
  }
  return "[\n".implode(",\n",$lines)."\n]";
}

/* ------- Helper: build $COLUMNS PHP array ------- */
function hq($s){ return var_export($s,true); }

function build_columns_php($cols, $sql_select){
  if (!is_array($cols) || empty($cols)){
    // infer very basic columns from SELECT aliases/exprs
    // we’ll split by comma and try to get alias after " as " or last token
    $raw = array_map('trim', explode(',', $sql_select));
    $base = [
      ['type'=>'sr','label'=>'SR No','width'=>'70px']
    ];
    foreach($raw as $expr){
      $label = $expr;
      if (preg_match('/\bas\b\s+([a-z0-9_]+)/i', $expr, $m)) {
        $key = $m[1];
        $label = ucfirst(str_replace('_',' ',$key));
        $base[] = ['type'=>'text','key'=>$key,'label'=>$label];
      } else {
        // try last token as key
        $parts = preg_split('/\s+/', $expr);
        $key = preg_replace('/[^a-z0-9_]/i','', end($parts));
        if ($key) {
          $label = ucfirst(str_replace('_',' ',$key));
          $base[] = ['type'=>'text','key'=>$key,'label'=>$label];
        }
      }
    }
    $base[] = ['type'=>'actions','label'=>'Actions','width'=>'140px'];
    $cols = $base;
  }

  $php = "[\n";
  foreach($cols as $c){
    $type = strtolower($c['type'] ?? 'text');
    $label= $c['label'] ?? 'Col';
    $width= $c['width'] ?? null;
    $key  = $c['key']   ?? null;
    $sub  = $c['sub']   ?? null;
    $fmt  = $c['format']?? null;

    if ($type==='sr'){
      $php .= "  ['label' => ".hq($label).", 'width' => ".hq($width ?? '70px').", 'render' => function(\$row,\$sr){ echo (int)\$sr; }],\n";
      continue;
    }
    if ($type==='actions'){
      $w = $width ? ", 'width'=>".hq($width) : "";
      $php .= "  ['label' => ".hq($label)."$w, 'render' => function(\$row){ echo '<a class=\"btn secondary\" href=\"#\" onclick=\"alert(\\'view #'.(int)\$row['id'].'\\');return false;\">View</a>'; }],\n";
      continue;
    }
    if ($type==='status'){
      $w = $width ? ", 'width'=>".hq($width) : "";
      $k = $key ?: 'status';
      $php .= "  ['label' => ".hq($label)."$w, 'render' => function(\$row){ \$ok=((int)(\$row['$k'] ?? 0)===1); echo '<span class=\"badge '.(\$ok?'success':'danger').'\">'.(\$ok?'Active':'Inactive').'</span>'; }],\n";
      continue;
    }
    if ($type==='money'){
      $w = $width ? ", 'width'=>".hq($width) : "";
      $k = $key ?: 'amount';
      $php .= "  ['label' => ".hq($label)."$w, 'render' => function(\$row){ echo number_format((float)(\$row['$k'] ?? 0), 2); }],\n";
      continue;
    }
    if ($type==='date'){
      $w = $width ? ", 'width'=>".hq($width) : "";
      $k = $key ?: 'created_at';
      $f = $fmt ?: 'd M Y';
      $php .= "  ['label' => ".hq($label)."$w, 'render' => function(\$row){ \$v=\$row['$k'] ?? ''; echo \$v?htmlspecialchars(date(".hq($f).", strtotime(\$v))):''; }],\n";
      continue;
    }
    // default text with optional sub line
    $w = $width ? ", 'width'=>".hq($width) : "";
    $k = $key ?: 'value';
    $php .= "  ['label' => ".hq($label)."$w, 'render' => function(\$row){ echo htmlspecialchars((string)(\$row['$k'] ?? ''));";
    if ($sub){
      // render mini subline with {{placeholders}}
      $subphp = preg_replace_callback('/\{\{([a-z0-9_]+)\}\}/i', function($m){
        $kk = $m[1];
        return "'.htmlspecialchars((string)(\$row['$kk'] ?? '')).'";
      }, $sub);
      $php .= " echo '<div style=\"font-size:12px;color:#9ca3af\">".str_replace("'", "\\'", $subphp)."</div>';";
    }
    $php .= " }],\n";
  }
  $php .= "]";
  return $php;
}

$SORT_MAP_PHP = build_sort_map_php($sort_map);
$COLUMNS_PHP  = build_columns_php($columns, $sql_select);

$template = <<<'PHP'
<?php
@ini_set('display_errors','1'); @error_reporting(E_ALL);

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

/* ---------------- CONFIG (auto-generated) ---------------- */
$page_title = %PAGE_TITLE%;
$DEFAULT_PAGE_SIZE = %DEFAULT_PAGE_SIZE%;

global $con;
if (!$con) { die('DB connection not initialized'); }

$SQL_SELECT = %SQL_SELECT%;
$SQL_FROM   = %SQL_FROM%;
$BASE_WHERE = %BASE_WHERE%;
$SORT_MAP   = %SORT_MAP%;
$COLUMNS    = %COLUMNS%;
/* ---------------- END CONFIG ---------------- */

/* ---------------- Helpers ---------------- */
function h($s){ return htmlspecialchars((string)$s, 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;} }
  $q = http_build_query($qs);
  return $q?('?'.$q):'';
}
function get_int($key,$default=0){ return isset($_GET[$key]) ? (int)$_GET[$key] : $default; }
function get_str($key,$default=''){ return isset($_GET[$key]) ? trim((string)$_GET[$key]) : $default; }

/* ---------------- Filters ---------------- */
$q            = get_str('q','');
$status_in    = get_str('status','');
$created_from = get_str('created_from','');
$created_to   = get_str('created_to','');

$sort = get_str('sort','newest');
$view = get_str('view','last50');
$page = max(1, get_int('page',1));
$per_page = ($view==='all') ? 1000 : $DEFAULT_PAGE_SIZE;
$offset = ($page-1)*$per_page;

/* ---------------- WHERE + Params ---------------- */
$where = [];
$types = '';
$params = [];

if ($BASE_WHERE!==''){ $where[] = '(' . $BASE_WHERE . ')'; }

if ($q!==''){
  $where[] = "(r.title LIKE CONCAT('%',?,'%') OR r.owner_name LIKE CONCAT('%',?,'%'))";
  $types  .= 'ss';
  $params = array_merge($params, [$q,$q]);
}

if ($status_in==='active'){  $where[] = "r.status=1"; }
elseif ($status_in==='inactive'){ $where[] = "r.status=0"; }

if ($created_from!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$created_from)){
  $where[] = "DATE(r.created_at)>=?";
  $types  .= 's';
  $params[]= $created_from;
}
if ($created_to!=='' && preg_match('/^\d{4}-\d{2}-\d{2}$/',$created_to)){
  $where[] = "DATE(r.created_at)<=?";
  $types  .= 's';
  $params[]= $created_to;
}

$sql_where = $where ? (' WHERE '.implode(' AND ',$where)) : '';

/* ---------------- ORDER BY ---------------- */
$order = $SORT_MAP[$sort] ?? reset($SORT_MAP);

/* ---------------- Count ---------------- */
$sql_count = "SELECT COUNT(*) ".$SQL_FROM.$sql_where;
$stmt = $con->prepare($sql_count);
if($types!==''){ $stmt->bind_param($types, ...$params); }
$stmt->execute();
$total = 0; $stmt->bind_result($total); $stmt->fetch(); $stmt->close();

if ($view!=='all'){
  $pages = max(1, (int)ceil($total / $per_page));
  if ($page>$pages){ $page=$pages; $offset=($page-1)*$per_page; }
} else { $pages = 1; $page = 1; $offset = 0; }

/* ---------------- Main Query ---------------- */
$sql = "SELECT ".$SQL_SELECT.$SQL_FROM.$sql_where." ".$order." ".($view==='all' ? "" : " LIMIT $per_page OFFSET $offset");
$stmt = $con->prepare($sql);
if($types!==''){ $stmt->bind_param($types, ...$params); }
$stmt->execute();
$res = $stmt->get_result();

/* ---------------- Render ---------------- */
ob_start();
?>
<link rel="stylesheet" href="/adminconsole/assets/ui.css">
<div class="master-wrap">
  <div class="headbar">
    <h2 style="margin:0"><?=h($page_title)?></h2>
  </div>

  <div class="card">
    <form method="get" class="toolbar" style="gap:10px;flex-wrap:wrap">
      <input class="inp" type="text" name="q" value="<?=h($q)?>" placeholder="Search title/owner..." style="min-width:240px">

      <select class="inp" name="status">
        <option value="" <?= $status_in===''?'selected':''?>>Status: Any</option>
        <option value="active" <?= $status_in==='active'?'selected':''?>>Active</option>
        <option value="inactive" <?= $status_in==='inactive'?'selected':''?>>Inactive</option>
      </select>

      <input class="inp" type="date" name="created_from" value="<?=h($created_from)?>">
      <input class="inp" type="date" name="created_to" value="<?=h($created_to)?>">

      <select class="inp" name="sort">
        <?php foreach($SORT_MAP as $k=>$sqlOB): ?>
          <option value="<?=$k?>" <?=$sort===$k?'selected':''?>><?=h($k)?></option>
        <?php endforeach; ?>
      </select>

      <button class="btn primary" type="submit">Apply</button>

      <div style="flex:1"></div>
      <a class="btn secondary" href="<?=h(keep_params(['view'=>'last50','page'=>1]))?>">Last <?=$DEFAULT_PAGE_SIZE?></a>
      <a class="btn secondary" href="<?=h(keep_params(['view'=>'all','page'=>1]))?>">View All</a>
    </form>

    <div style="display:flex;align-items:center;gap:12px;margin:8px 0 12px">
      <span class="badge">Total: <?= (int)$total ?></span>
      <span class="badge">Showing: <?= ($view==='all') ? 'All' : ($res->num_rows) ?></span>
      <?php if($view!=='all'){ ?>
        <div style="margin-left:auto;display:flex;gap:6px;align-items:center">
          <?php if($page>1){ ?><a class="btn secondary" href="<?=h(keep_params(['page'=>$page-1]))?>">‹ Prev</a><?php } ?>
          <span>Page <?= (int)$page ?> / <?= (int)$pages ?></span>
          <?php if($page<$pages){ ?><a class="btn secondary" href="<?=h(keep_params(['page'=>$page+1]))?>">Next ›</a><?php } ?>
        </div>
      <?php } ?>
    </div>

    <div class="table-wrap">
      <table class="table">
        <thead>
          <tr>
            <?php foreach($COLUMNS as $col): ?>
              <th<?=isset($col['width'])?' style="width:'.$col['width'].';"':''?>><?=h($col['label'])?></th>
            <?php endforeach; ?>
          </tr>
        </thead>
        <tbody>
        <?php
          $srStart = ($view==='all') ? 1 : ($offset+1);
          $sr = $srStart;
          while($row = $res->fetch_assoc()):
            echo '<tr>';
            foreach($COLUMNS as $col){
              echo '<td>';
              $col['render']($row, $sr);
              echo '</td>';
            }
            echo '</tr>';
            $sr++;
          endwhile;
          $stmt->close();

          if ($sr === $srStart){
            echo '<tr><td colspan="'.count($COLUMNS).'" style="text-align:center;color:#9ca3af">No records found.</td></tr>';
          }
        ?>
        </tbody>
      </table>
    </div>

    <?php if($view!=='all'){ ?>
      <div style="display:flex;gap:8px;justify-content:flex-end;margin-top:12px">
        <?php if($page>1){ ?><a class="btn secondary" href="<?=h(keep_params(['page'=>$page-1]))?>">‹ Prev</a><?php } ?>
        <span class="badge">Page <?= (int)$page ?> / <?= (int)$pages ?></span>
        <?php if($page<$pages){ ?><a class="btn secondary" href="<?=h(keep_params(['page'=>$page+1]))?>">Next ›</a><?php } ?>
      </div>
    <?php } ?>

  </div>
</div>
<?php
echo ob_get_clean();
PHP;

$repl = [
  '%PAGE_TITLE%'        => var_export($page_title,true),
  '%DEFAULT_PAGE_SIZE%' => (string)( $default_ps ?: 50 ),
  '%SQL_SELECT%'        => var_export($sql_select,true),
  '%SQL_FROM%'          => var_export($sql_from,true),
  '%BASE_WHERE%'        => var_export($base_where,true),
  '%SORT_MAP%'          => $SORT_MAP_PHP,
  '%COLUMNS%'           => $COLUMNS_PHP,
];

$out = strtr($template, $repl);

if (false === file_put_contents($target, $out)) {
  echo "[ERR] Could not write: $target\n";
  return;
}
@chmod($target, 0664);

$rel = '/adminconsole/reports/' . $filename;
echo "[OK] Report created: $target\n";
echo "URL: $rel\n";
