<?php
// public/admin/api_attendance.php — schema-aware for (action rows: checkin/checkout)
// Supports: ulb_today, district_today, state_today (district-wise), ulb_list_for_district,
//           ulb_list_all (for super-admin picker), state_ulbs_today (ULB-wise summary),
//           district_list_all (for district picker)

declare(strict_types=1);
require __DIR__ . '/../../includes/db.php';
require __DIR__ . '/../../includes/auth.php';
header('Content-Type: application/json; charset=utf-8');

function fail($msg, $code = 400) {
  http_response_code($code);
  echo json_encode(['error' => $msg], JSON_UNESCAPED_UNICODE);
  exit;
}

/** table exists helper */
function table_exists(PDO $pdo, string $name): bool {
  try {
    $st = $pdo->query("SHOW TABLES LIKE " . $pdo->quote($name));
    return (bool)$st->fetchColumn();
  } catch (Throwable $e) { return false; }
}

/** simple column exists helper */
function column_exists(PDO $pdo, string $table, string $column): bool {
  try {
    $st = $pdo->prepare("SHOW COLUMNS FROM {$table} LIKE ?");
    $st->execute([$column]);
    return (bool)$st->fetchColumn();
  } catch (Throwable $e) { return false; }
}

try {
  if (!isset($pdo)) fail('DB not ready', 500);
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
  try { $pdo->exec("SET time_zone = '+05:30'"); } catch(Throwable $e) {}

  // ====== CONFIG: set your real table names here if different ======
  $T_EXEC = 'executives';        // executives table
  $T_ATT  = 'attendance_logs';   // row-per-action table (action: checkin/checkout)
  $T_DIST = 'districts';         // master: id, name, code
  $T_ULB  = 'ulbs';              // master: id, district_id, name, code
  // ================================================================

  $mode  = $_GET['mode'] ?? 'ulb_today';
  $today = (new DateTime('now', new DateTimeZone('Asia/Kolkata')))->format('Y-m-d');

  // auth + scope
  $me = require_login_scoped(['super_admin','district_admin','ulb_admin','president','dm']);

  // basic presence of tables — if missing, return safe zeros for attendance modes
  $hasExec = table_exists($pdo, $T_EXEC);
  $hasAtt  = table_exists($pdo, $T_ATT);
  $needsAttendance = in_array($mode, ['ulb_today','district_today','state_today','state_ulbs_today'], true);

  // ULB scope (IDs). super_admin => all ulbs (if master present)
  $ulbScopeIds = table_exists($pdo, $T_ULB) ? auth_ulb_scope($pdo) : [];

  if ($needsAttendance && (!$hasExec || !$hasAtt)) {
    echo json_encode([
      'date' => $today,
      'summary' => ['present'=>0,'working'=>0,'visits_today'=>0,'visits_total'=>0,'total_execs'=>0],
      'executives' => [],
      'ulbs' => [],
      'districts' => []
    ]);
    exit;
  }

  // Helpers to translate ids->names
  $ulbNameFromId = function(int $ulbId) use ($pdo, $T_ULB): ?string {
    if (!table_exists($pdo, $T_ULB)) return null;
    $st = $pdo->prepare("SELECT name FROM {$T_ULB} WHERE id=:id");
    $st->execute(['id'=>$ulbId]);
    $n = $st->fetchColumn();
    return $n ? (string)$n : null;
  };
  $districtNameFromId = function(int $did) use ($pdo, $T_DIST): ?string {
    if (!table_exists($pdo, $T_DIST)) return null;
    $st = $pdo->prepare("SELECT name FROM {$T_DIST} WHERE id=:id");
    $st->execute(['id'=>$did]);
    $n = $st->fetchColumn();
    return $n ? (string)$n : null;
  };

  // ---------- Robust executive fetchers (fixes “rows not fetching”) ----------
  $has_exec_ulb_id     = column_exists($pdo, $T_EXEC, 'ulb_id');
  $has_exec_duty_ulbid = column_exists($pdo, $T_EXEC, 'duty_ulb_id');
  $has_exec_duty_ulb   = column_exists($pdo, $T_EXEC, 'duty_ulb');
  $has_exec_full_name  = column_exists($pdo, $T_EXEC, 'full_name');

  /**
   * Return executive rows (id, full_name/name, phone) mapped to given ULB
   * Tries ulb_id -> duty_ulb_id -> duty_ulb(name).
   */
  $getExecRowsForUlb = function(int $ulbId, string $ulbName) use ($pdo, $T_EXEC, $has_exec_ulb_id, $has_exec_duty_ulbid, $has_exec_duty_ulb, $has_exec_full_name): array {
    // 1) executives.ulb_id = :id
    if ($has_exec_ulb_id) {
      $st = $pdo->prepare("SELECT id, ".($has_exec_full_name?'full_name AS full_name':'name AS full_name').", phone FROM {$T_EXEC} WHERE ulb_id = :id");
      $st->execute(['id'=>$ulbId]);
      $rows = $st->fetchAll();
      if ($rows) return $rows;
    }
    // 2) executives.duty_ulb_id = :id
    if ($has_exec_duty_ulbid) {
      $st = $pdo->prepare("SELECT id, ".($has_exec_full_name?'full_name AS full_name':'name AS full_name').", phone FROM {$T_EXEC} WHERE duty_ulb_id = :id");
      $st->execute(['id'=>$ulbId]);
      $rows = $st->fetchAll();
      if ($rows) return $rows;
    }
    // 3) executives.duty_ulb = :name
    if ($has_exec_duty_ulb) {
      $st = $pdo->prepare("SELECT id, ".($has_exec_full_name?'full_name AS full_name':'name AS full_name').", phone FROM {$T_EXEC} WHERE duty_ulb = :n");
      $st->execute(['n'=>$ulbName]);
      $rows = $st->fetchAll();
      if ($rows) return $rows;
    }
    // nothing
    return [];
  };

  /**
   * Return only executive IDs for a ULB (for faster summaries)
   */
  $getExecIdsForUlb = function(int $ulbId, string $ulbName) use ($pdo, $T_EXEC, $has_exec_ulb_id, $has_exec_duty_ulbid, $has_exec_duty_ulb): array {
    if ($has_exec_ulb_id) {
      $st = $pdo->prepare("SELECT id FROM {$T_EXEC} WHERE ulb_id = :id");
      $st->execute(['id'=>$ulbId]);
      $ids = array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN));
      if ($ids) return $ids;
    }
    if ($has_exec_duty_ulbid) {
      $st = $pdo->prepare("SELECT id FROM {$T_EXEC} WHERE duty_ulb_id = :id");
      $st->execute(['id'=>$ulbId]);
      $ids = array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN));
      if ($ids) return $ids;
    }
    if ($has_exec_duty_ulb) {
      $st = $pdo->prepare("SELECT id FROM {$T_EXEC} WHERE duty_ulb = :n");
      $st->execute(['n'=>$ulbName]);
      $ids = array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN));
      if ($ids) return $ids;
    }
    return [];
  };

  // Prepared statements used across modes
  $latestActionStmt = $pdo->prepare("
    SELECT action FROM {$T_ATT}
    WHERE executive_id = :eid AND DATE(created_at) = :d
    ORDER BY created_at DESC, id DESC
    LIMIT 1
  ");
  $hasCheckinStmt = $pdo->prepare("
    SELECT 1 FROM {$T_ATT}
    WHERE executive_id = :eid AND DATE(created_at) = :d AND action = 'checkin'
    LIMIT 1
  ");

  // visits (using trainings); optional
  $stVisitsToday = null;
  $stVisitsTotal = null;
  try {
    $stVisitsToday = $pdo->prepare("
      SELECT COUNT(*) FROM trainings
      WHERE created_by_executive_id = :eid
        AND DATE(created_at) = :d
    ");
    $stVisitsTotal = $pdo->prepare("
      SELECT COUNT(*) FROM trainings
      WHERE created_by_executive_id = :eid
    ");
  } catch (Throwable $e) {
    // trainings table/columns may not exist — we'll fallback to zeros
  }

  // compute present/working/visits for list of executives
  $computeExecStats = function(array $execRows) use ($today, $latestActionStmt, $hasCheckinStmt, $stVisitsToday, $stVisitsTotal): array {
    $present = 0; $working = 0;
    $perExec = [];
    foreach ($execRows as $e) {
      $eid = (int)$e['id'];

      // present today? any checkin row today
      $hasCheckinStmt->execute(['eid'=>$eid, 'd'=>$today]);
      $isPresent = (bool)$hasCheckinStmt->fetchColumn();

      // working now? latest action today == checkin
      $latestActionStmt->execute(['eid'=>$eid, 'd'=>$today]);
      $last = $latestActionStmt->fetchColumn();
      $isWorking = ($last === 'checkin');

      if ($isPresent) $present++;
      if ($isWorking) $working++;

      // visits stats (optional trainings mapping)
      $vToday = 0; $vTotal = 0;
      if ($stVisitsToday && $stVisitsTotal) {
        try {
          $stVisitsToday->execute(['eid'=>$eid, 'd'=>$today]);
          $vToday = (int)$stVisitsToday->fetchColumn();
          $stVisitsTotal->execute(['eid'=>$eid]);
          $vTotal = (int)$stVisitsTotal->fetchColumn();
        } catch (Throwable $ex) { /* keep zeros */ }
      }

      $perExec[] = [
        'id' => $eid,
        'name' => $e['full_name'] ?? ($e['name'] ?? '—'),
        'phone' => $e['phone'] ?? '',
        'present_today' => $isPresent,
        'working_now' => $isWorking,
        'visits_today' => $vToday,
        'visits_total' => $vTotal,
      ];
    }
    return [$present, $working, $perExec];
  };

  // ========== NEW: District list (within scope) ==========
  if ($mode === 'district_list_all') {
    if (!table_exists($pdo, $T_DIST)) fail('District master missing', 500);
    $rows = $pdo->query("SELECT id, name FROM {$T_DIST} ORDER BY name")->fetchAll();
    if ($ulbScopeIds && table_exists($pdo, $T_ULB)) {
      $in = implode(',', array_fill(0, count($ulbScopeIds), '?'));
      $st = $pdo->prepare("SELECT DISTINCT district_id FROM {$T_ULB} WHERE id IN ($in)");
      $st->execute($ulbScopeIds);
      $allowed = array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN));
      $rows = array_values(array_filter($rows, fn($d)=> in_array((int)$d['id'], $allowed, true)));
    }
    echo json_encode(['districts'=>$rows]); exit;
  }

  // ========== ULB list for district (picker) ==========
  if ($mode === 'ulb_list_for_district') {
    if (!table_exists($pdo, $T_ULB)) fail('ULB master missing', 500);
    $districtId = isset($_GET['district_id']) ? (int)$_GET['district_id'] : 0;
    if ($districtId <= 0) fail('district_id required', 400);

    $st = $pdo->prepare("SELECT id, name FROM {$T_ULB} WHERE district_id = :d ORDER BY name");
    $st->execute(['d'=>$districtId]);
    $ulbs = $st->fetchAll();
    if ($ulbScopeIds) {
      $ulbs = array_values(array_filter($ulbs, fn($u) => in_array((int)$u['id'], $ulbScopeIds, true)));
    }
    echo json_encode(['ulbs'=>$ulbs]); exit;
  }

  // ========== ULB list (all within scope) — for super admin ULB picker ==========
  if ($mode === 'ulb_list_all') {
    if (!table_exists($pdo, $T_ULB)) fail('ULB master missing', 500);
    $districtId = isset($_GET['district_id']) ? (int)$_GET['district_id'] : 0;

    $where = '';
    $args = [];
    if ($districtId > 0) {
      $where = "WHERE district_id = :d";
      $args['d'] = $districtId;
    }
    $st = $pdo->prepare("SELECT id, name, district_id FROM {$T_ULB} {$where} ORDER BY name");
    $st->execute($args);
    $rows = $st->fetchAll();

    if ($ulbScopeIds) {
      $rows = array_values(array_filter($rows, fn($u) => in_array((int)$u['id'], $ulbScopeIds, true)));
    }
    // attach district name
    if (table_exists($pdo, $T_DIST)) {
      $stD = $pdo->prepare("SELECT name FROM {$T_DIST} WHERE id = ? LIMIT 1");
      foreach ($rows as &$r) {
        $stD->execute([(int)$r['district_id']]);
        $r['district_name'] = (string)($stD->fetchColumn() ?: '');
      }
      unset($r);
    }
    echo json_encode(['ulbs'=>$rows]); exit;
  }

  // ========== ULB TODAY ==========
  if ($mode === 'ulb_today') {
    if (!table_exists($pdo, $T_ULB)) fail('ULB master missing', 500);

    $ulbId = isset($_GET['ulb_id']) ? (int)$_GET['ulb_id'] : 0;
    if ($ulbId <= 0) {
      if (strtolower($me['role']) === 'ulb_admin' && !empty($me['ulb_id'])) {
        $ulbId = (int)$me['ulb_id'];
      } else {
        fail('ulb_id required', 400);
      }
    }
    if ($ulbScopeIds && !in_array($ulbId, $ulbScopeIds, true)) fail('ULB not in scope', 403);

    $ulbName = $ulbNameFromId($ulbId);
    if (!$ulbName) fail('ULB not found', 404);

    // executives — robust mapping
    $execRows = $getExecRowsForUlb($ulbId, $ulbName);

    [$present, $working, $perExec] = $computeExecStats($execRows);

    echo json_encode([
      'ulb_id' => $ulbId,
      'ulb_name' => $ulbName,
      'date' => $today,
      'summary' => [
        // KPIs used by UI
        'total_execs' => count($perExec),
        'present'     => $present,   // checked-in today
        'working'     => $working,   // currently checked-in
        // (kept for completeness)
        'visits_today' => array_sum(array_column($perExec, 'visits_today')),
        'visits_total' => array_sum(array_column($perExec, 'visits_total'))
      ],
      'executives' => $perExec
    ]);
    exit;
  }

  // ========== DISTRICT TODAY ==========
  if ($mode === 'district_today') {
    if (!table_exists($pdo, $T_DIST) || !table_exists($pdo, $T_ULB)) fail('Masters missing', 500);

    $districtId = isset($_GET['district_id']) ? (int)$_GET['district_id'] : 0;
    if ($districtId <= 0 && strtolower($me['role']) === 'district_admin') {
      $districtId = (int)($me['district_id'] ?? 0);
    }
    if ($districtId <= 0) fail('district_id required', 400);

    $districtName = $districtNameFromId($districtId);
    if (!$districtName) fail('District not found', 404);

    // ULBs of district within scope
    $stU = $pdo->prepare("SELECT id, name FROM {$T_ULB} WHERE district_id = :d ORDER BY name");
    $stU->execute(['d'=>$districtId]);
    $ulbs = $stU->fetchAll();
    if ($ulbScopeIds) {
      $ulbs = array_values(array_filter($ulbs, fn($u) => in_array((int)$u['id'], $ulbScopeIds, true)));
    }

    $payload = ['district_id'=>$districtId, 'district_name'=>$districtName, 'date'=>$today, 'ulbs'=>[]];
    $sumP = 0; $sumW = 0; $sumToday = 0; $sumTotal = 0; $sumExecs = 0;

    foreach ($ulbs as $u) {
      $ulbId = (int)$u['id'];
      $ulbName = $u['name'];

      $execRows = $getExecRowsForUlb($ulbId, $ulbName);

      [$p, $w, $perExec] = $computeExecStats($execRows);
      $visToday = array_sum(array_column($perExec, 'visits_today'));
      $visTotal = array_sum(array_column($perExec, 'visits_total'));
      $totExecs = count($perExec);

      $payload['ulbs'][] = [
        'ulb_id' => $ulbId,
        'ulb_name' => $ulbName,
        'present' => $p,
        'working' => $w,
        'visits_today' => $visToday,
        'visits_total' => $visTotal,
        'total_execs' => $totExecs
      ];

      $sumP += $p; $sumW += $w;
      $sumToday += $visToday; $sumTotal += $visTotal;
      $sumExecs += $totExecs;
    }

    $payload['summary'] = [
      'present' => $sumP,
      'working' => $sumW,
      'visits_today' => $sumToday,
      'visits_total' => $sumTotal,
      'total_execs' => $sumExecs,
      'ulbs_count' => count($payload['ulbs'])
    ];

    echo json_encode($payload); exit;
  }

  // ========== STATE TODAY (district-wise summary) ==========
  if ($mode === 'state_today') {
    if (!table_exists($pdo, $T_DIST) || !table_exists($pdo, $T_ULB)) {
      echo json_encode([
        'date'=>$today,
        'summary'=>['present'=>0,'working'=>0,'visits_today'=>0,'visits_total'=>0,'districts_count'=>0,'total_execs'=>0],
        'districts'=>[]
      ]);
      exit;
    }

    // All districts (optionally filtered by ULB scope)
    $dRows = $pdo->query("SELECT id, name FROM {$T_DIST} ORDER BY name")->fetchAll();
    if ($ulbScopeIds) {
      $in = implode(',', array_fill(0, count($ulbScopeIds), '?'));
      $st = $pdo->prepare("SELECT DISTINCT district_id FROM {$T_ULB} WHERE id IN ($in)");
      $st->execute($ulbScopeIds);
      $allowedD = array_map('intval', $st->fetchAll(PDO::FETCH_COLUMN));
      $dRows = array_values(array_filter($dRows, fn($d)=> in_array((int)$d['id'], $allowedD, true)));
    }

    $result = ['date'=>$today, 'districts'=>[]];
    $sumP=0; $sumW=0; $sumToday=0; $sumTotal=0; $sumExecs=0;

    $stU = $pdo->prepare("SELECT id, name FROM {$T_ULB} WHERE district_id = ?");
    foreach ($dRows as $d) {
      $did = (int)$d['id'];
      $dname = $d['name'];

      // ULBs in district
      $stU->execute([$did]);
      $ulbs = $stU->fetchAll();

      $execRowsAll = [];
      foreach ($ulbs as $u) {
        $rows = $getExecRowsForUlb((int)$u['id'], (string)$u['name']);
        if ($rows) $execRowsAll = array_merge($execRowsAll, $rows);
      }

      [$p, $w, $perExec] = $computeExecStats($execRowsAll);
      $visToday = array_sum(array_column($perExec, 'visits_today'));
      $visTotal = array_sum(array_column($perExec, 'visits_total'));
      $totExecs = count($perExec);

      $result['districts'][] = [
        'district_id'   => $did,
        'district_name' => $dname,
        'present'       => $p,
        'working'       => $w,
        'visits_today'  => $visToday,
        'visits_total'  => $visTotal,
        'total_execs'   => $totExecs
      ];

      $sumP += $p; $sumW += $w;
      $sumToday += $visToday; $sumTotal += $visTotal;
      $sumExecs += $totExecs;
    }

    $result['summary'] = [
      'present' => $sumP,
      'working' => $sumW,
      'visits_today' => $sumToday,
      'visits_total' => $sumTotal,
      'total_execs' => $sumExecs,
      'districts_count' => count($result['districts'])
    ];

    echo json_encode($result); exit;
  }

  // ========== STATE ULBs TODAY (ULB-wise summary across scope; optional district filter) ==========
  if ($mode === 'state_ulbs_today') {
    if (!table_exists($pdo, $T_ULB) || !$hasExec || !$hasAtt) {
      echo json_encode(['date'=>$today,'ulbs'=>[],'summary'=>['present'=>0,'working'=>0,'visits_today'=>0,'visits_total'=>0,'ulbs_count'=>0,'total_execs'=>0]]);
      exit;
    }

    $districtFilter = isset($_GET['district_id']) ? (int)$_GET['district_id'] : 0;

    $where = '';
    $args = [];
    if ($districtFilter > 0) {
      $where = 'WHERE district_id = :d';
      $args['d'] = $districtFilter;
    }
    $stULBs = $pdo->prepare("SELECT id, name, district_id FROM {$T_ULB} {$where} ORDER BY name");
    $stULBs->execute($args);
    $rows = $stULBs->fetchAll();

    if ($ulbScopeIds) {
      $rows = array_values(array_filter($rows, fn($u) => in_array((int)$u['id'], $ulbScopeIds, true)));
    }

    $stDistName = table_exists($pdo, $T_DIST) ? $pdo->prepare("SELECT name FROM {$T_DIST} WHERE id = ? LIMIT 1") : null;

    $ulbsOut = [];
    $sumP = 0; $sumW = 0; $sumToday = 0; $sumTotal = 0; $sumExecs = 0;

    foreach ($rows as $u) {
      $ulbId = (int)$u['id'];
      $ulbName = $u['name'];

      $execIds = array_map('intval', array_column($getExecRowsForUlb($ulbId, $ulbName), 'id'));

      $present = 0; $working = 0; $visToday = 0; $visTotal = 0;
      foreach ($execIds as $eid) {
        // present?
        $hasCheckinStmt->execute(['eid'=>$eid, 'd'=>$today]);
        if ($hasCheckinStmt->fetchColumn()) $present++;

        // working?
        $latestActionStmt->execute(['eid'=>$eid, 'd'=>$today]);
        if ($latestActionStmt->fetchColumn() === 'checkin') $working++;

        // visits
        if ($stVisitsToday && $stVisitsTotal) {
          try {
            $stVisitsToday->execute(['eid'=>$eid, 'd'=>$today]);
            $visToday += (int)$stVisitsToday->fetchColumn();
            $stVisitsTotal->execute(['eid'=>$eid]);
            $visTotal += (int)$stVisitsTotal->fetchColumn();
          } catch (Throwable $e) {}
        }
      }

      $districtName = '';
      if ($stDistName) {
        $stDistName->execute([(int)$u['district_id']]);
        $districtName = (string)($stDistName->fetchColumn() ?: '');
      }

      $ulbsOut[] = [
        'ulb_id'        => $ulbId,
        'ulb_name'      => $ulbName,
        'district_name' => $districtName,
        'present'       => $present,
        'working'       => $working,
        'visits_today'  => $visToday,
        'visits_total'  => $visTotal,
        'total_execs'   => count($execIds)
      ];

      $sumP += $present; $sumW += $working;
      $sumToday += $visToday; $sumTotal += $visTotal;
      $sumExecs += count($execIds);
    }

    echo json_encode([
      'date' => $today,
      'ulbs' => $ulbsOut,
      'summary' => [
        'present' => $sumP,
        'working' => $sumW,
        'visits_today' => $sumToday,
        'visits_total' => $sumTotal,
        'total_execs' => $sumExecs,
        'ulbs_count' => count($ulbsOut)
      ]
    ]);
    exit;
  }

  fail('Unknown mode', 400);

} catch (Throwable $e) {
  fail($e->getMessage(), 500);
}
