<?php
// assets/includes/loadLevyAccountRefs.php
if (session_status() !== PHP_SESSION_ACTIVE) session_start();
header('Content-Type: application/json; charset=UTF-8');

require_once('../config/dbConnection.php');

$db = DatabaseConnection::getInstance();
$conn = $db->getConnection();

$typeId = isset($_GET['type_id']) ? (int)$_GET['type_id'] : 0;
$catId  = isset($_GET['cat_id'])  ? (int)$_GET['cat_id']  : 0;
$locId  = isset($_GET['loc_id'])  ? (int)$_GET['loc_id']  : 0;

$codes = ['blevy'=>'','fire'=>'','health'=>''];

/* 1) Prefer business type columns */
if ($typeId > 0) {
  $sql = "SELECT account_ref_blevy, account_ref_fire, account_ref_health
          FROM smartsync_businesstypes WHERE id=? LIMIT 1";
  if ($st = $conn->prepare($sql)) {
    $st->bind_param('i', $typeId);
    $st->execute();
    $st->bind_result($b,$f,$h);
    if ($st->fetch()) {
      $codes['blevy']  = (string)($b ?? '');
      $codes['fire']   = (string)($f ?? '');
      $codes['health'] = (string)($h ?? '');
    }
    $st->close();
  }
}

/* 2) (Optional) If you also want per-category overrides, add similar read
       from smartsync_businesscategories where id=$catId and override non-empty
   Example (uncomment after you add the columns):
// if ($catId > 0) {
//   $sql = "SELECT account_ref_blevy, account_ref_fire, account_ref_health
//           FROM smartsync_businesscategories WHERE id=? LIMIT 1";
//   if ($st = $conn->prepare($sql)) {
//     $st->bind_param('i', $catId);
//     $st->execute();
//     $st->bind_result($b,$f,$h);
//     if ($st->fetch()) {
//       if ($b) $codes['blevy'] = $b;
//       if ($f) $codes['fire']  = $f;
//       if ($h) $codes['health']= $h;
//     }
//     $st->close();
//   }
// }
*/

/* 3) Fallback (optional): try leaf Revenue names in chartofaccounts */
if ($codes['blevy']==='' || $codes['fire']==='' || $codes['health']==='') {
  $need = [];
  foreach ($codes as $k=>$v) if ($v==='') $need[]=$k;
  if ($need) {
    $patterns = [
      'blevy'  => '%Business%Levy%',
      'fire'   => '%Fire%Cert%',
      'health' => '%Health%Permit%',
    ];
    $types = '';
    $vals  = [];
    foreach ($need as $k){ $types.='s'; $vals[]=$patterns[$k]; }
    $sql = "SELECT AccountReference, AccountName
            FROM chartofaccounts c
            LEFT JOIN (SELECT ParentID, COUNT(*) cnt FROM chartofaccounts WHERE ParentID IS NOT NULL GROUP BY ParentID) k
            ON k.ParentID=c.id
            WHERE COALESCE(k.cnt,0)=0 AND c.AccountType='Revenue' AND ("
         . implode(' OR ', array_fill(0, count($need), "c.AccountName LIKE ?")) . ") LIMIT 50";
    if ($st = $conn->prepare($sql)) {
      $bind = [$types];
      foreach ($vals as $i => $v) $bind[] = &$vals[$i];
      call_user_func_array([$st,'bind_param'],$bind);
      $st->execute();
      $rs = $st->get_result();
      while ($r = $rs->fetch_assoc()) {
        $nm = strtolower($r['AccountName']);
        if ($codes['blevy']===''  && str_contains($nm,'business') && str_contains($nm,'levy')) $codes['blevy']=$r['AccountReference'];
        if ($codes['fire']===''   && str_contains($nm,'fire') && (str_contains($nm,'cert') || str_contains($nm,'certificate'))) $codes['fire']=$r['AccountReference'];
        if ($codes['health']==='' && str_contains($nm,'health') && str_contains($nm,'permit')) $codes['health']=$r['AccountReference'];
      }
      $st->close();
    }
  }
}

echo json_encode(['codes'=>$codes], JSON_UNESCAPED_UNICODE);
