<?php
/** Subscriber-backed confirmation for physical device disconnect alerts. */
require_once __DIR__ . '/subscriber_reality_lib.php';

if (!function_exists('ua_device_confirm_col')) {
  function ua_device_confirm_col(mysqli $conn, string $table, string $column): bool {
    static $cache=[];$key=$table.'|'.$column;
    if(isset($cache[$key]))return $cache[$key];
    $t=$conn->real_escape_string($table);$c=$conn->real_escape_string($column);
    $rs=@$conn->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'");
    return $cache[$key]=(bool)($rs&&$rs->num_rows);
  }
}

if (!function_exists('ua_device_confirm_subscriber_rows')) {
  function ua_device_confirm_subscriber_rows(mysqli $conn,string $group,int $deviceId): array {
    if($deviceId<=0||!in_array($group,['routers','sectors','receivers'],true))return [];
    $where=[];
    if(ua_device_confirm_col($conn,'subscribers','is_deleted'))$where[]='COALESCE(s.is_deleted,0)=0';
    if($group==='receivers'){
      if(!ua_device_confirm_col($conn,'subscribers','airmax_device_id'))return [];
      $where[]='COALESCE(s.airmax_device_id,0)='.$deviceId;
    }elseif($group==='sectors'){
      $sectorId=0;
      if(ua_device_confirm_col($conn,'airmax_devices','sector_id')){
        $rs=@$conn->query('SELECT sector_id FROM airmax_devices WHERE id='.$deviceId.' LIMIT 1');
        if($rs&&($row=$rs->fetch_assoc()))$sectorId=(int)($row['sector_id']??0);
      }
      $scope=[];
      if($sectorId>0&&ua_device_confirm_col($conn,'subscribers','sector_id'))$scope[]='COALESCE(s.sector_id,0)='.$sectorId;
      if($sectorId>0&&ua_device_confirm_col($conn,'subscribers','airmax_device_id')&&ua_device_confirm_col($conn,'airmax_devices','sector_id')){
        $scope[]='COALESCE(s.airmax_device_id,0) IN (SELECT id FROM airmax_devices WHERE COALESCE(sector_id,0)='.$sectorId.')';
      }
      // Include receivers below this access device. This is the strongest
      // device-level relationship when legacy installations have no sector_id.
      if(ua_device_confirm_col($conn,'subscribers','airmax_device_id')&&ua_device_confirm_col($conn,'airmax_devices','parent_device_id')){
        $parents=[];$rs=@$conn->query('SELECT id,parent_device_id FROM airmax_devices');
        while($rs&&($row=$rs->fetch_assoc()))$parents[(int)$row['id']]=(int)($row['parent_device_id']??0);
        $desc=[];$changed=true;
        while($changed){$changed=false;foreach($parents as $id=>$parent){if($id<=0||isset($desc[$id]))continue;if($parent===$deviceId||isset($desc[$parent])){$desc[$id]=true;$changed=true;}}}
        if($desc)$scope[]='COALESCE(s.airmax_device_id,0) IN ('.implode(',',array_map('intval',array_keys($desc))).')';
      }
      if(!$scope)return [];
      $where[]='('.implode(' OR ',$scope).')';
    }
    $columns=['s.id','s.username'];
    foreach(['ppp_username','is_online','ppp_is_disabled','mt_disabled','is_cut','speed_state','status'] as $column){
      $columns[]=ua_device_confirm_col($conn,'subscribers',$column)?('s.`'.$column.'`'):('NULL AS `'.$column.'`');
    }
    $sql='SELECT '.implode(',',$columns).' FROM subscribers s'.($where?' WHERE '.implode(' AND ',$where):'').' ORDER BY s.id';
    $rows=[];$rs=@$conn->query($sql);while($rs&&($row=$rs->fetch_assoc()))$rows[]=$row;
    return $rows;
  }
}

if (!function_exists('ua_device_subscriber_confirmation')) {
  function ua_device_subscriber_confirmation(mysqli $conn,string $group,int $deviceId,?array $reality=null): array {
    $reality=$reality??ua_subscriber_reality_snapshot($conn,3600);
    $rows=ua_device_confirm_subscriber_rows($conn,$group,$deviceId);
    $online=0;$disabled=0;
    foreach($rows as $row){
      $state=ua_subscriber_reality_state($row,$reality);
      if(!empty($state['is_online']))$online++;
      if(!empty($state['is_disabled']))$disabled++;
    }
    $total=count($rows);$age=(int)($reality['data_age']??PHP_INT_MAX);
    $hasObservation=!empty($reality['has_observation']);
    $reliable=!empty($reality['live_data_reliable'])&&$age<=120;
    if($online>0){$allow=false;$reason='linked_subscriber_online';}
    elseif($total===0){$allow=false;$reason='no_linked_subscriber_evidence';}
    elseif(!$hasObservation||!$reliable){$allow=false;$reason='subscriber_snapshot_not_reliable';}
    else{$allow=true;$reason='all_linked_subscribers_offline';}
    return [
      'key'=>$group.':id:'.$deviceId,'group'=>$group,'device_id'=>$deviceId,
      'linked_total'=>$total,'online'=>$online,'offline'=>max(0,$total-$online),'disabled'=>$disabled,
      'allow_alert'=>$allow,'reason'=>$reason,'source'=>(string)($reality['source']??'unknown'),
      'data_age'=>$age,'live_data_reliable'=>$reliable,'has_observation'=>$hasObservation,
    ];
  }
}

if (!function_exists('ua_device_subscriber_confirm_batch')) {
  function ua_device_subscriber_confirm_batch(mysqli $conn,array $requests): array {
    $reality=ua_subscriber_reality_snapshot($conn,3600);$out=[];$seen=[];
    foreach(array_slice($requests,0,30) as $request){
      if(!is_array($request))continue;
      $group=strtolower(trim((string)($request['group']??'')));$id=(int)($request['id']??0);$key=$group.':'.$id;
      if($id<=0||isset($seen[$key])||!in_array($group,['routers','sectors','receivers'],true))continue;
      $seen[$key]=true;$result=ua_device_subscriber_confirmation($conn,$group,$id,$reality);$out[$result['key']]=$result;
    }
    return ['ok'=>true,'items'=>$out,'source'=>(string)($reality['source']??'unknown'),'data_age'=>(int)($reality['data_age']??0),'live_data_reliable'=>!empty($reality['live_data_reliable'])];
  }
}
