<?php
/* ============================================================
 * Larspeenghar Pedigree — scalable database API
 * Built for ~3,000,000 animals and ~3,000 users.
 *
 * Everything is paginated + indexed: the browser NEVER loads more than
 * one page of animals at a time. Search and per-owner scoping run in SQL
 * against indexes, not in the browser.
 *
 * Endpoints (JSON):
 *   POST ?action=login         {username,password}
 *   POST ?action=logout
 *   GET  ?action=session
 *   GET  ?action=animals       &page&per_page&q&owner_id&breed&sex&province&sort
 *   GET  ?action=animal&id=#
 *   POST ?action=animal_save   {animal:{...}}
 *   POST ?action=animal_delete {id}
 *   GET  ?action=offspring&id=#         (children of a sire/dam)
 *   GET  ?action=owners        &q&page&per_page
 *   POST ?action=owner_save    {owner:{...}}
 *   GET  ?action=users
 *   POST ?action=user_save / user_delete
 *   GET  ?action=setting&k=...      POST ?action=setting_save&k=...  {data}
 *   GET  ?action=stats
 * ============================================================ */

session_start();
header('Content-Type: application/json; charset=utf-8');
header('Cache-Control: no-store');
require __DIR__ . '/db.php';

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

$pdo = lp_db();

/* ---- one-time bootstrap: ensure a default admin exists ---- */
(function () use ($pdo) {
    $n = (int)$pdo->query("SELECT COUNT(*) FROM users WHERE role='admin'")->fetchColumn();
    if ($n === 0) {
        $stmt = $pdo->prepare(
            "INSERT INTO users (id, username, password_hash, role, name)
             VALUES ('admin','admin',?, 'admin','Administrator')
             ON DUPLICATE KEY UPDATE username=username"
        );
        $stmt->execute([password_hash('larspeenghar2024', PASSWORD_DEFAULT)]);
    }
})();

/* ---- auth helpers ---- */
function current_user() {
    global $pdo;
    if (empty($_SESSION['uid'])) return null;
    $st = $pdo->prepare("SELECT id, username, role, owner_id, name FROM users WHERE id=?");
    $st->execute([$_SESSION['uid']]);
    return $st->fetch() ?: null;
}
function require_login() { $u = current_user(); if (!$u) fail(401, 'Not authenticated'); return $u; }
function require_admin() { $u = require_login(); if ($u['role'] !== 'admin') fail(403, 'Admin only'); return $u; }

$action = $_GET['action'] ?? '';
$method = $_SERVER['REQUEST_METHOD'];
$body = [];
if ($method === 'POST') {
    $raw = file_get_contents('php://input');
    $body = $raw ? (json_decode($raw, true) ?: []) : [];
}

/* ---- map a DB animal row <-> the frontend animal object ---- */
function row_to_animal($r) {
    if (!$r) return null;
    $extra = $r['extra'] ? (json_decode($r['extra'], true) ?: []) : [];
    $a = [
        'id'          => (string)$r['id'],
        'regId'       => $r['reg_id'],
        'earTag'      => $r['ear_tag'],
        'name'        => $r['name'],
        'breed'       => $r['breed'],
        'sex'         => $r['sex'],
        'role'        => $r['role'],
        'dob'         => $r['dob'],
        'color'       => $r['color'],
        'lact'        => $r['lactation'],
        'ownerId'     => $r['owner_id'] !== null ? (string)$r['owner_id'] : '',
        'province'    => $r['province'],
        'district'    => $r['district'],
        'sireLinkedId'=> $r['sire_id'] !== null ? (string)$r['sire_id'] : '',
        'damLinkedId' => $r['dam_id'] !== null ? (string)$r['dam_id'] : '',
        'sireName'    => $r['sire_name'], 'sireId' => $r['sire_reg'], 'sireBreed' => $r['sire_breed'],
        'damName'     => $r['dam_name'],  'damId'  => $r['dam_reg'],  'damBreed'  => $r['dam_breed'],
        'ssName' => $r['ss_name'], 'ssId' => $r['ss_reg'],
        'sdName' => $r['sd_name'], 'sdId' => $r['sd_reg'],
        'dsName' => $r['ds_name'], 'dsId' => $r['ds_reg'],
        'ddName' => $r['dd_name'], 'ddId' => $r['dd_reg'],
        'milk' => $r['milk_305'], 'fat' => $r['fat_pct'], 'prot' => $r['prot_pct'],
        'peak' => $r['peak'], 'dim' => $r['dim'], 'scs' => $r['scs'],
        'photo' => $r['photo'],
        'breedComp' => $r['breed_comp'] ? json_decode($r['breed_comp'], true) : null,
        'pendingStatus' => $r['pending_status'],
        'notes' => $r['notes'],
    ];
    // overflow fields (sireDob, sdMilk, calving, etc.) live in `extra`
    return array_merge($a, $extra);
}

/* Columns that map 1:1; everything else in the incoming object goes to `extra`. */
function animal_to_columns($a) {
    $idOrNull = function ($v) { return ($v === '' || $v === null) ? null : (int)$v; };
    $cols = [
        'reg_id' => $a['regId'] ?? null,
        'ear_tag' => $a['earTag'] ?? null,
        'name' => $a['name'] ?? null,
        'breed' => $a['breed'] ?? null,
        'sex' => in_array($a['sex'] ?? '', ['Male','Female'], true) ? $a['sex'] : null,
        'role' => $a['role'] ?? null,
        'dob' => ($a['dob'] ?? '') !== '' ? $a['dob'] : null,
        'color' => $a['color'] ?? null,
        'lactation' => ($a['lact'] ?? '') !== '' ? (int)$a['lact'] : null,
        'owner_id' => $idOrNull($a['ownerId'] ?? null),
        'province' => $a['province'] ?? null,
        'district' => $a['district'] ?? null,
        'sire_id' => $idOrNull($a['sireLinkedId'] ?? null),
        'dam_id' => $idOrNull($a['damLinkedId'] ?? null),
        'sire_name' => $a['sireName'] ?? null, 'sire_reg' => $a['sireId'] ?? null, 'sire_breed' => $a['sireBreed'] ?? null,
        'dam_name' => $a['damName'] ?? null, 'dam_reg' => $a['damId'] ?? null, 'dam_breed' => $a['damBreed'] ?? null,
        'ss_name' => $a['ssName'] ?? null, 'ss_reg' => $a['ssId'] ?? null,
        'sd_name' => $a['sdName'] ?? null, 'sd_reg' => $a['sdId'] ?? null,
        'ds_name' => $a['dsName'] ?? null, 'ds_reg' => $a['dsId'] ?? null,
        'dd_name' => $a['ddName'] ?? null, 'dd_reg' => $a['ddId'] ?? null,
        'milk_305' => ($a['milk'] ?? '') !== '' ? $a['milk'] : null,
        'fat_pct' => ($a['fat'] ?? '') !== '' ? $a['fat'] : null,
        'prot_pct' => ($a['prot'] ?? '') !== '' ? $a['prot'] : null,
        'peak' => ($a['peak'] ?? '') !== '' ? $a['peak'] : null,
        'dim' => ($a['dim'] ?? '') !== '' ? (int)$a['dim'] : null,
        'scs' => ($a['scs'] ?? '') !== '' ? $a['scs'] : null,
        'photo' => $a['photo'] ?? null,
        'breed_comp' => isset($a['breedComp']) && $a['breedComp'] ? json_encode($a['breedComp']) : null,
        'pending_status' => $a['pendingStatus'] ?? null,
        'notes' => $a['notes'] ?? null,
    ];
    // Stash any remaining keys (not core columns, not id) into extra.
    $mapped = ['id','regId','earTag','name','breed','sex','role','dob','color','lact','ownerId',
        'province','district','sireLinkedId','damLinkedId','sireName','sireId','sireBreed','damName',
        'damId','damBreed','ssName','ssId','sdName','sdId','dsName','dsId','ddName','ddId','milk','fat',
        'prot','peak','dim','scs','photo','breedComp','pendingStatus','notes'];
    $extra = [];
    foreach ($a as $k => $v) { if (!in_array($k, $mapped, true)) $extra[$k] = $v; }
    $cols['extra'] = $extra ? json_encode($extra) : null;
    return $cols;
}

switch ($action) {

    /* ─────────────── AUTH ─────────────── */
    case 'login': {
        if ($method !== 'POST') fail(405, 'POST required');
        $uname = strtolower(trim($body['username'] ?? ''));
        $pass = (string)($body['password'] ?? '');
        if ($uname === '' || $pass === '') fail(400, 'Username and password required');
        $st = $pdo->prepare("SELECT * FROM users WHERE username=? LIMIT 1");
        $st->execute([$uname]);
        $u = $st->fetch();
        if (!$u || !password_verify($pass, $u['password_hash'])) fail(401, 'Invalid username or password');
        session_regenerate_id(true);
        $_SESSION['uid'] = $u['id'];
        $pdo->prepare("UPDATE users SET last_login=NOW() WHERE id=?")->execute([$u['id']]);
        unset($u['password_hash']);
        out(['user' => $u]);
    }

    case 'logout': {
        $_SESSION = [];
        session_destroy();
        out(['ok' => true]);
    }

    case 'session': {
        out(['user' => current_user()]);
    }

    /* ─────────────── ANIMALS (paginated, indexed) ─────────────── */
    case 'animals': {
        $me = require_login();
        $page = max(1, (int)($_GET['page'] ?? 1));
        $per = min(200, max(1, (int)($_GET['per_page'] ?? 50)));
        $offset = ($page - 1) * $per;

        $where = [];
        $args = [];

        // Per-owner scoping: non-admins only ever see their own herd.
        if ($me['role'] !== 'admin') {
            $where[] = 'owner_id = ?';
            $args[] = $me['owner_id'];
        } elseif (!empty($_GET['owner_id'])) {
            $where[] = 'owner_id = ?';
            $args[] = (int)$_GET['owner_id'];
        }
        if (!empty($_GET['breed']))    { $where[] = 'breed = ?';    $args[] = $_GET['breed']; }
        if (!empty($_GET['sex']))      { $where[] = 'sex = ?';      $args[] = $_GET['sex']; }
        if (!empty($_GET['province'])) { $where[] = 'province = ?'; $args[] = $_GET['province']; }

        // Search: FULLTEXT for 3+ char queries (uses ft index → fast at 3M rows),
        // anchored LIKE prefix for very short queries.
        $q = trim($_GET['q'] ?? '');
        if ($q !== '') {
            if (mb_strlen($q) >= 3) {
                $where[] = 'MATCH(name, reg_id, ear_tag) AGAINST (? IN BOOLEAN MODE)';
                $args[] = $q . '*';
            } else {
                $where[] = '(name LIKE ? OR reg_id LIKE ? OR ear_tag LIKE ?)';
                $args[] = $q . '%'; $args[] = $q . '%'; $args[] = $q . '%';
            }
        }

        $sortMap = ['name' => 'name', 'updated' => 'updated_at DESC', 'reg' => 'reg_id', 'created' => 'id DESC'];
        $order = $sortMap[$_GET['sort'] ?? 'updated'] ?? 'updated_at DESC';

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

        // Count (same filters) for pagination UI.
        $cst = $pdo->prepare("SELECT COUNT(*) FROM animals $wsql");
        $cst->execute($args);
        $total = (int)$cst->fetchColumn();

        $st = $pdo->prepare(
            "SELECT * FROM animals $wsql ORDER BY $order LIMIT $per OFFSET $offset"
        );
        $st->execute($args);
        $rows = array_map('row_to_animal', $st->fetchAll());

        out(['rows' => $rows, 'total' => $total, 'page' => $page, 'per_page' => $per,
             'pages' => (int)ceil($total / $per)]);
    }

    case 'animal': {
        $me = require_login();
        $id = (int)($_GET['id'] ?? 0);
        $st = $pdo->prepare("SELECT * FROM animals WHERE id=?");
        $st->execute([$id]);
        $row = $st->fetch();
        if (!$row) fail(404, 'Not found');
        if ($me['role'] !== 'admin' && (string)$row['owner_id'] !== (string)$me['owner_id']) fail(403, 'Forbidden');
        out(['animal' => row_to_animal($row)]);
    }

    case 'animal_save': {
        $me = require_login();
        if ($method !== 'POST') fail(405, 'POST required');
        $a = $body['animal'] ?? null;
        if (!is_array($a)) fail(400, 'animal object required');

        // Non-admins can only write to their own herd.
        if ($me['role'] !== 'admin') $a['ownerId'] = $me['owner_id'];

        $cols = animal_to_columns($a);
        $id = isset($a['id']) && $a['id'] !== '' && ctype_digit((string)$a['id']) ? (int)$a['id'] : 0;

        if ($id > 0) {
            // ownership check before update
            $chk = $pdo->prepare("SELECT owner_id FROM animals WHERE id=?");
            $chk->execute([$id]);
            $owner = $chk->fetchColumn();
            if ($owner === false) fail(404, 'Not found');
            if ($me['role'] !== 'admin' && (string)$owner !== (string)$me['owner_id']) fail(403, 'Forbidden');

            $set = implode(', ', array_map(fn($c) => "$c = :$c", array_keys($cols)));
            $st = $pdo->prepare("UPDATE animals SET $set WHERE id = :id");
            $cols['id'] = $id;
            $st->execute($cols);
            out(['ok' => true, 'id' => (string)$id]);
        } else {
            $names = implode(', ', array_keys($cols));
            $ph = implode(', ', array_map(fn($c) => ":$c", array_keys($cols)));
            $st = $pdo->prepare("INSERT INTO animals ($names) VALUES ($ph)");
            $st->execute($cols);
            out(['ok' => true, 'id' => (string)$pdo->lastInsertId()]);
        }
    }

    case 'animal_delete': {
        $me = require_login();
        if ($method !== 'POST') fail(405, 'POST required');
        $id = (int)($body['id'] ?? 0);
        $chk = $pdo->prepare("SELECT owner_id FROM animals WHERE id=?");
        $chk->execute([$id]);
        $owner = $chk->fetchColumn();
        if ($owner === false) fail(404, 'Not found');
        if ($me['role'] !== 'admin' && (string)$owner !== (string)$me['owner_id']) fail(403, 'Forbidden');
        $pdo->prepare("DELETE FROM animals WHERE id=?")->execute([$id]);
        out(['ok' => true]);
    }

    case 'offspring': {
        require_login();
        $id = (int)($_GET['id'] ?? 0);
        $st = $pdo->prepare(
            "SELECT * FROM animals WHERE sire_id=? OR dam_id=? ORDER BY dob DESC LIMIT 500"
        );
        $st->execute([$id, $id]);
        out(['rows' => array_map('row_to_animal', $st->fetchAll())]);
    }

    /* ─────────────── PHASE 3: whole-herd analytics (server-side) ─────────────── */

    /* Ancestor subgraph for one animal, up to `depth` generations, via a
       recursive CTE walking sire_id/dam_id. Returns the ancestor animal rows
       (mapped) so the browser can build the tree / compute inbreeding from a
       small, bounded set instead of the whole 3M-row herd.
       Requires MySQL 8+ / MariaDB 10.2.2+ (recursive CTE). */
    case 'pedigree': {
        $me = require_login();
        $id = (int)($_GET['id'] ?? 0);
        $depth = max(1, min(22, (int)($_GET['depth'] ?? 11)));
        if ($id <= 0) fail(400, 'id required');

        // scope check on the root animal
        $chk = $pdo->prepare("SELECT owner_id FROM animals WHERE id=?");
        $chk->execute([$id]);
        $owner = $chk->fetchColumn();
        if ($owner === false) fail(404, 'Not found');
        if ($me['role'] !== 'admin' && (string)$owner !== (string)$me['owner_id']) fail(403, 'Forbidden');

        $sql =
            "WITH RECURSIVE anc (id, gen) AS (
                SELECT id, 0 FROM animals WHERE id = :root
                UNION ALL
                SELECT a.id, anc.gen + 1
                FROM animals a
                JOIN anc ON (a.id = (SELECT sire_id FROM animals WHERE id = anc.id)
                          OR a.id = (SELECT dam_id  FROM animals WHERE id = anc.id))
                WHERE anc.gen < :depth
             )
             SELECT DISTINCT animals.* FROM animals
             JOIN anc ON anc.id = animals.id";
        $st = $pdo->prepare($sql);
        $st->bindValue(':root', $id, PDO::PARAM_INT);
        $st->bindValue(':depth', $depth, PDO::PARAM_INT);
        $st->execute();
        out(['rows' => array_map('row_to_animal', $st->fetchAll())]);
    }

    /* Ancestor subgraphs for several animals at once (for the kinship matrix).
       ?ids=1,2,3  &depth=N  → one merged, de-duplicated set of animal rows. */
    case 'ancestors_batch': {
        $me = require_login();
        $ids = array_filter(array_map('intval', explode(',', $_GET['ids'] ?? '')));
        $depth = max(1, min(22, (int)($_GET['depth'] ?? 6)));
        if (!$ids) out(['rows' => []]);
        $ids = array_slice($ids, 0, 60); // bound the selection size
        $in = implode(',', array_fill(0, count($ids), '?'));

        // Seed the recursion with all requested ids, then walk up.
        $sql =
            "WITH RECURSIVE anc (id, gen) AS (
                SELECT id, 0 FROM animals WHERE id IN ($in)
                UNION ALL
                SELECT a.id, anc.gen + 1
                FROM animals a
                JOIN anc ON (a.id = (SELECT sire_id FROM animals WHERE id = anc.id)
                          OR a.id = (SELECT dam_id  FROM animals WHERE id = anc.id))
                WHERE anc.gen < $depth
             )
             SELECT DISTINCT animals.* FROM animals JOIN anc ON anc.id = animals.id";
        $st = $pdo->prepare($sql);
        $st->execute($ids);
        $rows = array_map('row_to_animal', $st->fetchAll());

        // Non-admins: only return rows from their own herd.
        if ($me['role'] !== 'admin') {
            $own = (string)$me['owner_id'];
            $rows = array_values(array_filter($rows, fn($r) => (string)$r['ownerId'] === $own));
        }
        out(['rows' => $rows]);
    }

    /* Herd founders: animals with no recorded parents (paginated). */
    case 'founders': {
        $me = require_login();
        $page = max(1, (int)($_GET['page'] ?? 1));
        $per = min(200, max(1, (int)($_GET['per_page'] ?? 50)));
        $offset = ($page - 1) * $per;
        $where = "sire_id IS NULL AND dam_id IS NULL AND (sire_name IS NULL OR sire_name='') AND (dam_name IS NULL OR dam_name='')";
        $args = [];
        if ($me['role'] !== 'admin') { $where .= " AND owner_id = ?"; $args[] = $me['owner_id']; }
        elseif (!empty($_GET['owner_id'])) { $where .= " AND owner_id = ?"; $args[] = (int)$_GET['owner_id']; }
        $cst = $pdo->prepare("SELECT COUNT(*) FROM animals WHERE $where");
        $cst->execute($args);
        $total = (int)$cst->fetchColumn();
        $st = $pdo->prepare("SELECT * FROM animals WHERE $where ORDER BY name LIMIT $per OFFSET $offset");
        $st->execute($args);
        out(['rows' => array_map('row_to_animal', $st->fetchAll()), 'total' => $total, 'page' => $page]);
    }

    /* Most influential ancestors: animals with the most direct offspring.
       Cheap GROUP BY on the indexed sire_id / dam_id columns. */
    case 'top_ancestors': {
        $me = require_login();
        $limit = min(50, max(1, (int)($_GET['limit'] ?? 15)));
        $args = [];
        // Build the sire/dam offspring sub-select with an optional owner scope.
        if ($me['role'] !== 'admin') {
            $sireSel = "SELECT sire_id AS pid FROM animals WHERE owner_id = ? AND sire_id IS NOT NULL";
            $damSel  = "SELECT dam_id  AS pid FROM animals WHERE owner_id = ? AND dam_id  IS NOT NULL";
            $args = [$me['owner_id'], $me['owner_id']];
        } else {
            $sireSel = "SELECT sire_id AS pid FROM animals WHERE sire_id IS NOT NULL";
            $damSel  = "SELECT dam_id  AS pid FROM animals WHERE dam_id  IS NOT NULL";
        }
        $sql =
            "SELECT p.id, p.name, p.reg_id, COUNT(*) AS descendants
             FROM ($sireSel UNION ALL $damSel) k
             JOIN animals p ON p.id = k.pid
             GROUP BY p.id, p.name, p.reg_id
             ORDER BY descendants DESC LIMIT $limit";
        $st = $pdo->prepare($sql);
        $st->execute($args);
        out(['rows' => $st->fetchAll()]);
    }

    /* Herd inbreeding report: animals sorted by their CACHED inbreeding_f,
       plus the herd average. (F is precomputed via ?action=recompute_f so the
       report stays instant at 3M rows.) */
    case 'herd_report': {
        $me = require_login();
        $page = max(1, (int)($_GET['page'] ?? 1));
        $per = min(200, max(1, (int)($_GET['per_page'] ?? 50)));
        $offset = ($page - 1) * $per;
        $where = "inbreeding_f IS NOT NULL";
        $args = [];
        if ($me['role'] !== 'admin') { $where .= " AND owner_id = ?"; $args[] = $me['owner_id']; }
        elseif (!empty($_GET['owner_id'])) { $where .= " AND owner_id = ?"; $args[] = (int)$_GET['owner_id']; }
        $agg = $pdo->prepare("SELECT COUNT(*) c, AVG(inbreeding_f) a, MAX(inbreeding_f) mx FROM animals WHERE $where");
        $agg->execute($args);
        $ag = $agg->fetch();
        $st = $pdo->prepare("SELECT * FROM animals WHERE $where ORDER BY inbreeding_f DESC LIMIT $per OFFSET $offset");
        $st->execute($args);
        out([
            'rows' => array_map('row_to_animal', $st->fetchAll()),
            'count' => (int)$ag['c'], 'avg_f' => $ag['a'] !== null ? round($ag['a'], 2) : null,
            'max_f' => $ag['mx'], 'page' => $page,
        ]);
    }

    /* Store a precomputed inbreeding F (and optional AVK) for one animal.
       The browser computes F from the pedigree subgraph, then posts it here so
       the herd report can sort millions of animals instantly. */
    case 'set_f': {
        $me = require_login();
        if ($method !== 'POST') fail(405, 'POST required');
        $id = (int)($body['id'] ?? 0);
        if ($id <= 0) fail(400, 'id required');
        $chk = $pdo->prepare("SELECT owner_id FROM animals WHERE id=?");
        $chk->execute([$id]);
        $owner = $chk->fetchColumn();
        if ($owner === false) fail(404, 'Not found');
        if ($me['role'] !== 'admin' && (string)$owner !== (string)$me['owner_id']) fail(403, 'Forbidden');
        $f = isset($body['f']) ? (float)$body['f'] : null;
        $avk = isset($body['avk']) ? (float)$body['avk'] : null;
        $pdo->prepare("UPDATE animals SET inbreeding_f=?, avk=? WHERE id=?")->execute([$f, $avk, $id]);
        out(['ok' => true]);
    }

    /* ─────────────── OWNERS ─────────────── */
    case 'owners': {
        require_login();
        $page = max(1, (int)($_GET['page'] ?? 1));
        $per = min(500, max(1, (int)($_GET['per_page'] ?? 200)));
        $offset = ($page - 1) * $per;
        $args = [];
        $wsql = '';
        $q = trim($_GET['q'] ?? '');
        if ($q !== '') {
            $wsql = "WHERE herd_name LIKE ? OR name LIKE ? OR code LIKE ?";
            $args = [$q . '%', $q . '%', $q . '%'];
        }
        $cst = $pdo->prepare("SELECT COUNT(*) FROM owners $wsql");
        $cst->execute($args);
        $total = (int)$cst->fetchColumn();
        $st = $pdo->prepare("SELECT * FROM owners $wsql ORDER BY herd_name LIMIT $per OFFSET $offset");
        $st->execute($args);
        out(['rows' => $st->fetchAll(), 'total' => $total, 'page' => $page]);
    }

    case 'owner_save': {
        require_admin();
        if ($method !== 'POST') fail(405, 'POST required');
        $o = $body['owner'] ?? [];
        $fields = ['type','code','herd_name','name','breed_focus','phone','email','province','district','address','notes'];
        $cols = [];
        foreach ($fields as $f) $cols[$f] = $o[$f] ?? null;
        if (!in_array($cols['type'], ['herd','individual'], true)) $cols['type'] = 'herd';
        $id = isset($o['id']) && ctype_digit((string)$o['id']) ? (int)$o['id'] : 0;
        if ($id > 0) {
            $set = implode(', ', array_map(fn($c) => "$c=:$c", array_keys($cols)));
            $cols['id'] = $id;
            $pdo->prepare("UPDATE owners SET $set WHERE id=:id")->execute($cols);
            out(['ok' => true, 'id' => (string)$id]);
        } else {
            $names = implode(',', array_keys($cols));
            $ph = implode(',', array_map(fn($c) => ":$c", array_keys($cols)));
            $pdo->prepare("INSERT INTO owners ($names) VALUES ($ph)")->execute($cols);
            out(['ok' => true, 'id' => (string)$pdo->lastInsertId()]);
        }
    }

    /* ─────────────── USERS ─────────────── */
    case 'users': {
        $me = require_login();
        if ($me['role'] === 'admin') {
            $rows = $pdo->query("SELECT id, username, role, owner_id, name, last_login FROM users ORDER BY username")->fetchAll();
        } else {
            $st = $pdo->prepare("SELECT id, username, role, owner_id, name FROM users WHERE id=?");
            $st->execute([$me['id']]);
            $rows = $st->fetchAll();
        }
        out(['rows' => $rows]);
    }

    case 'user_save': {
        $me = require_login();
        if ($method !== 'POST') fail(405, 'POST required');
        $u = $body;
        $editId = $u['id'] ?? '';
        if ($me['role'] !== 'admin' && $editId !== $me['id']) fail(403, 'Forbidden');

        $st = $pdo->prepare("SELECT id FROM users WHERE id=?");
        $st->execute([$editId]);
        $exists = $st->fetchColumn() !== false;

        if (!$exists) {
            require_admin();
            $pw = (string)($u['password'] ?? '');
            if (strlen($pw) < 6) fail(400, 'Password must be at least 6 characters');
            $newId = $u['id'] ?: ('u' . time() . substr(bin2hex(random_bytes(2)), 0, 4));
            $ins = $pdo->prepare(
                "INSERT INTO users (id, username, password_hash, role, owner_id, name)
                 VALUES (?,?,?,?,?,?)"
            );
            $ins->execute([
                $newId, trim($u['username'] ?? ''), password_hash($pw, PASSWORD_DEFAULT),
                in_array($u['role'] ?? '', ['admin','owner'], true) ? $u['role'] : 'owner',
                ($u['ownerId'] ?? '') !== '' ? (int)$u['ownerId'] : null,
                trim($u['name'] ?? ''),
            ]);
            out(['ok' => true, 'id' => $newId]);
        } else {
            $sets = []; $args = [];
            if ($me['role'] === 'admin') {
                if (isset($u['username'])) { $sets[] = 'username=?'; $args[] = trim($u['username']); }
                if (isset($u['role'])) { $sets[] = 'role=?'; $args[] = in_array($u['role'], ['admin','owner'], true) ? $u['role'] : 'owner'; }
                if (array_key_exists('ownerId', $u)) { $sets[] = 'owner_id=?'; $args[] = ($u['ownerId'] !== '') ? (int)$u['ownerId'] : null; }
            }
            if (isset($u['name'])) { $sets[] = 'name=?'; $args[] = trim($u['name']); }
            if (!empty($u['password'])) {
                if (strlen($u['password']) < 6) fail(400, 'Password must be at least 6 characters');
                $sets[] = 'password_hash=?'; $args[] = password_hash($u['password'], PASSWORD_DEFAULT);
            }
            if ($sets) {
                $args[] = $editId;
                $pdo->prepare("UPDATE users SET " . implode(',', $sets) . " WHERE id=?")->execute($args);
            }
            out(['ok' => true]);
        }
    }

    case 'user_delete': {
        require_admin();
        if ($method !== 'POST') fail(405, 'POST required');
        $id = $body['id'] ?? '';
        if ($id === 'admin') fail(400, 'Cannot delete the default admin');
        $pdo->prepare("DELETE FROM users WHERE id=?")->execute([$id]);
        out(['ok' => true]);
    }

    /* ─────────────── SETTINGS (key→JSON) ─────────────── */
    case 'setting': {
        require_login();
        $k = $_GET['k'] ?? '';
        $st = $pdo->prepare("SELECT v FROM settings WHERE k=?");
        $st->execute([$k]);
        $v = $st->fetchColumn();
        out(['data' => $v ? json_decode($v, true) : null]);
    }

    case 'setting_save': {
        require_admin();
        if ($method !== 'POST') fail(405, 'POST required');
        $k = $_GET['k'] ?? '';
        if ($k === '') fail(400, 'key required');
        $st = $pdo->prepare(
            "INSERT INTO settings (k, v) VALUES (?, ?)
             ON DUPLICATE KEY UPDATE v=VALUES(v)"
        );
        $st->execute([$k, json_encode($body['data'] ?? null)]);
        out(['ok' => true]);
    }

    /* ─────────────── STATS (cheap COUNTs) ─────────────── */
    case 'stats': {
        $me = require_login();
        if ($me['role'] === 'admin') {
            $animals = (int)$pdo->query("SELECT COUNT(*) FROM animals")->fetchColumn();
            $owners = (int)$pdo->query("SELECT COUNT(*) FROM owners")->fetchColumn();
            $users = (int)$pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
        } else {
            $st = $pdo->prepare("SELECT COUNT(*) FROM animals WHERE owner_id=?");
            $st->execute([$me['owner_id']]);
            $animals = (int)$st->fetchColumn();
            $owners = 1; $users = 1;
        }
        out(['animals' => $animals, 'owners' => $owners, 'users' => $users]);
    }

    /* Herd-genetics metrics computed in SQL (work at 3M rows):
       average inbreeding, generation interval, ΔF/generation, Ne. */
    case 'herd_stats': {
        $me = require_login();
        $scope = '';
        $args = [];
        if ($me['role'] !== 'admin') { $scope = ' AND owner_id = ?'; $args[] = $me['owner_id']; }

        // average / max inbreeding from the cached column
        $agg = $pdo->prepare("SELECT AVG(inbreeding_f) a, MAX(inbreeding_f) mx, COUNT(inbreeding_f) c
                              FROM animals WHERE inbreeding_f IS NOT NULL$scope");
        $agg->execute($args);
        $ag = $agg->fetch();

        // generation interval = avg (offspring year − parent year) over sire & dam links
        $giSql = "SELECT AVG(d) FROM (
            SELECT (YEAR(a.dob)-YEAR(s.dob)) d FROM animals a JOIN animals s ON a.sire_id=s.id
              WHERE a.dob IS NOT NULL AND s.dob IS NOT NULL AND (YEAR(a.dob)-YEAR(s.dob)) BETWEEN 1 AND 24" . str_replace('owner_id', 'a.owner_id', $scope) . "
            UNION ALL
            SELECT (YEAR(a.dob)-YEAR(m.dob)) d FROM animals a JOIN animals m ON a.dam_id=m.id
              WHERE a.dob IS NOT NULL AND m.dob IS NOT NULL AND (YEAR(a.dob)-YEAR(m.dob)) BETWEEN 1 AND 24" . str_replace('owner_id', 'a.owner_id', $scope) . "
        ) t";
        $giSt = $pdo->prepare($giSql);
        $giSt->execute(array_merge($args, $args));
        $gi = $giSt->fetchColumn();
        $gi = $gi !== null ? (float)$gi : null;

        // trend: avg F by birth year → least-squares slope (%F/yr)
        $trSt = $pdo->prepare("SELECT YEAR(dob) y, AVG(inbreeding_f) f, COUNT(*) n
                               FROM animals WHERE inbreeding_f > 0 AND dob IS NOT NULL$scope
                               GROUP BY y ORDER BY y");
        $trSt->execute($args);
        $trend = $trSt->fetchAll();
        $slope = null; $deltaFperGen = null; $ne = null;
        if (count($trend) >= 2) {
            $n = count($trend); $sx = $sy = $sxy = $sxx = 0;
            foreach ($trend as $t) { $x = (float)$t['y']; $y = (float)$t['f']; $sx += $x; $sy += $y; $sxy += $x*$y; $sxx += $x*$x; }
            $den = $n*$sxx - $sx*$sx;
            $slope = $den ? ($n*$sxy - $sx*$sy)/$den : 0;            // %F per year
            if ($gi) { $deltaFperGen = ($slope/100)*$gi; if ($deltaFperGen > 0.00001) $ne = 1/(2*$deltaFperGen); }
        }

        out([
            'avgF' => $ag['a'] !== null ? round($ag['a'], 2) : null,
            'maxF' => $ag['mx'],
            'withF' => (int)$ag['c'],
            'gi' => $gi !== null ? round($gi, 2) : null,
            'slopePerYear' => $slope !== null ? round($slope, 4) : null,
            'deltaFperGen' => $deltaFperGen,
            'ne' => $ne !== null ? round($ne) : null,
        ]);
    }

    /* Atomic per-region+breed tag serial. Each call reserves the next number
       safely even under concurrent registrations (single UPDATE-or-INSERT). */
    case 'next_tag': {
        require_login();
        $key = trim($_GET['key'] ?? '');
        $start = max(1, (int)($_GET['start'] ?? 1));
        if ($key === '') fail(400, 'key required');
        $pdo->prepare(
            "INSERT INTO tag_counters (k, n) VALUES (?, ?)
             ON DUPLICATE KEY UPDATE n = n + 1"
        )->execute([$key, $start]);
        $st = $pdo->prepare("SELECT n FROM tag_counters WHERE k = ?");
        $st->execute([$key]);
        out(['n' => (int)$st->fetchColumn()]);
    }

    default:
        fail(404, 'Unknown action');
}
