#!/usr/bin/env perl
#
# host_manager.pl - Minimal host registry web app with no CPAN dependencies.
#

use strict;
use warnings;

use Cwd qw(abs_path);
use DBI;
use Digest::SHA qw(hmac_sha1 hmac_sha256_hex sha256_hex);
use File::Basename qw(dirname);
use File::Path qw(make_path);
use IO::Socket::INET;
use POSIX qw(strftime);
use Time::HiRes qw(time);

my $script_dir = dirname(abs_path($0));
my $project_dir = dirname($script_dir);

my %opt = (
    bind => $ENV{HOST_MANAGER_BIND} || '127.0.0.1',
    port => $ENV{HOST_MANAGER_PORT} || 8088,
    db => $ENV{HOST_MANAGER_DB} || "$project_dir/var/host-manager.sqlite",
    data => $ENV{HOST_MANAGER_DATA} || "$project_dir/config/hosts.yaml",
    local_hosts_tsv => $ENV{HOST_MANAGER_LOCAL_HOSTS_TSV} || "$project_dir/config/local-hosts.tsv",
    dns_publish_trigger => $ENV{HOST_MANAGER_DNS_PUBLISH_TRIGGER} || "$project_dir/var/dns-publish.trigger",
    work_orders => $ENV{HOST_MANAGER_WORK_ORDERS} || "$project_dir/config/work-orders.yaml",
);
my $print_local_hosts_tsv = 0;

while (@ARGV) {
    my $arg = shift @ARGV;
    if ($arg eq '--bind') {
        $opt{bind} = shift @ARGV;
    } elsif ($arg eq '--port') {
        $opt{port} = shift @ARGV;
    } elsif ($arg eq '--db') {
        $opt{db} = shift @ARGV;
    } elsif ($arg eq '--data') {
        $opt{data} = shift @ARGV;
    } elsif ($arg eq '--local-hosts-tsv') {
        $opt{local_hosts_tsv} = shift @ARGV;
    } elsif ($arg eq '--work-orders') {
        $opt{work_orders} = shift @ARGV;
    } elsif ($arg eq '--print-local-hosts-tsv') {
        $print_local_hosts_tsv = 1;
    } elsif ($arg eq '--help' || $arg eq '-h') {
        usage();
        exit 0;
    } else {
        die "Unknown option: $arg\n";
    }
}

if ($print_local_hosts_tsv) {
    print render_local_hosts_tsv(load_registry());
    exit 0;
}

my $session_secret = $ENV{HOST_MANAGER_SESSION_SECRET} || random_hex(32);
my %sessions;

my $server = IO::Socket::INET->new(
    LocalHost => $opt{bind},
    LocalPort => $opt{port},
    Proto => 'tcp',
    Listen => 10,
    ReuseAddr => 1,
) or die "Cannot listen on $opt{bind}:$opt{port}: $!\n";

print "host-manager listening on http://$opt{bind}:$opt{port}\n";
print "database: $opt{db}\n";
print "seed/export hosts file: $opt{data}\n";
print "OTP login: " . ($ENV{HOST_MANAGER_TOTP_SECRET} ? "enabled\n" : "disabled; set HOST_MANAGER_TOTP_SECRET\n");

while (my $client = $server->accept) {
    eval {
        $client->autoflush(1);
        handle_client($client);
    };
    if ($@) {
        eval { send_json($client, 500, { error => 'internal_error', detail => "$@" }); };
    }
    close $client;
}

sub usage {
    print <<"EOF";
Usage: perl scripts/host_manager.pl [--bind 127.0.0.1] [--port 8088]

Environment:
  HOST_MANAGER_TOTP_SECRET      Base32 TOTP secret required for write access.
  HOST_MANAGER_SESSION_SECRET   Optional session signing secret.
  HOST_MANAGER_DHCP_PUSH_TOKEN  Token for DHCP lease push collector.
  HOST_MANAGER_DB               Defaults to var/host-manager.sqlite.
  HOST_MANAGER_DATA             Defaults to config/hosts.yaml.
  HOST_MANAGER_LOCAL_HOSTS_TSV  Defaults to config/local-hosts.tsv.
  HOST_MANAGER_DNS_PUBLISH_TRIGGER
                                  Defaults to var/dns-publish.trigger.
  HOST_MANAGER_WORK_ORDERS      Defaults to config/work-orders.yaml.
  --print-local-hosts-tsv       Print the runtime DNS manifest and exit.

SQLite is the runtime source of truth. YAML files seed a new database and remain
download/export compatibility artifacts. The nginx vhost keeps registry, CA,
work order and download endpoints behind OTP.
EOF
}

sub handle_client {
    my ($client) = @_;
    my $request_line = <$client>;
    return unless defined $request_line;
    $request_line =~ s/\r?\n$//;
    my ($method, $target) = $request_line =~ m{^([A-Z]+)\s+(\S+)\s+HTTP/};
    return send_text($client, 400, 'bad request') unless $method && $target;

    my %headers;
    while (my $line = <$client>) {
        $line =~ s/\r?\n$//;
        last if $line eq '';
        my ($k, $v) = split /:\s*/, $line, 2;
        $headers{lc $k} = $v if defined $k && defined $v;
    }

    my $body = '';
    if (($headers{'content-length'} || 0) > 0) {
        read($client, $body, int($headers{'content-length'}));
    }

    my ($path, $query) = split /\?/, $target, 2;
    my %query = parse_params($query || '');

    if ($method eq 'GET' && app_page_path($path)) {
        return send_html($client, 200, app_html());
    }
    if ($method eq 'GET' && $path eq '/healthz') {
        return send_json($client, 200, { ok => json_bool(1) });
    }
    if ($method eq 'GET' && $path eq '/api/session') {
        return send_json($client, 200, { authenticated => is_authenticated(\%headers) ? json_bool(1) : json_bool(0) });
    }
    if ($method eq 'POST' && $path eq '/api/login') {
        return send_json($client, 503, { error => 'otp_not_configured' }) unless $ENV{HOST_MANAGER_TOTP_SECRET};
        my $payload = request_payload(\%headers, $body);
        my $otp = $payload->{otp} || '';
        if (!verify_totp($ENV{HOST_MANAGER_TOTP_SECRET} || '', $otp)) {
            return send_json($client, 401, { error => 'invalid_otp' });
        }
        my $token = create_session();
        return send_json($client, 200, { ok => json_bool(1) }, [ "Set-Cookie: hm_session=$token; HttpOnly; SameSite=Strict; Path=/" ]);
    }
    if ($method eq 'POST' && $path eq '/api/logout') {
        expire_session(\%headers);
        return send_json($client, 200, { ok => json_bool(1) }, [ "Set-Cookie: hm_session=deleted; Max-Age=0; Path=/" ]);
    }
    if ($method eq 'POST' && $path eq '/api/collect/dhcp-leases') {
        return collect_dhcp_leases($client, \%headers, $body);
    }

    return send_json($client, 401, { error => 'authentication_required' }) unless is_authenticated(\%headers);

    if ($method eq 'GET' && $path eq '/api/hosts') {
        my $registry = load_registry();
        return send_json($client, 200, registry_payload($registry));
    }
    if ($method eq 'GET' && $path eq '/api/work-orders') {
        return send_json($client, 200, work_orders_payload(load_work_orders()));
    }
    if ($method eq 'GET' && $path eq '/api/debug/database/tables') {
        return send_json($client, 200, debug_database_tables_payload());
    }
    if ($method eq 'GET' && $path eq '/api/debug/database/table') {
        return send_json($client, 200, debug_database_table_payload($query{name} || $query{table} || '', $query{limit} || 100));
    }
    if ($method eq 'GET' && $path eq '/download/debug/database/table.json') {
        my $export = debug_database_table_export_payload($query{name} || $query{table} || '');
        return send_json($client, 400, { error => $export->{error} }) if $export->{error};
        return send_download($client, 200, json_encode($export), 'application/json; charset=utf-8', debug_table_export_filename($export->{table}, 'json'));
    }
    if ($method eq 'GET' && $path eq '/download/debug/database/table.csv') {
        my $export = debug_database_table_export_payload($query{name} || $query{table} || '');
        return send_json($client, 400, { error => $export->{error} }) if $export->{error};
        return send_download($client, 200, render_debug_table_csv($export), 'text/csv; charset=utf-8', debug_table_export_filename($export->{table}, 'csv'));
    }
    if ($method eq 'GET' && $path eq '/download/hosts.yaml') {
        my $registry = load_registry();
        return send_download($client, 200, render_hosts_yaml($registry), 'application/x-yaml; charset=utf-8', 'hosts.yaml');
    }
    if ($method eq 'GET' && $path eq '/download/local-hosts.tsv') {
        my $registry = load_registry();
        return send_download($client, 200, render_local_hosts_tsv($registry), 'text/tab-separated-values; charset=utf-8', 'local-hosts.tsv');
    }
    if ($method eq 'GET' && $path eq '/download/monitoring.json') {
        my $registry = load_registry();
        return send_download($client, 200, json_encode(render_monitoring($registry)), 'application/json; charset=utf-8', 'monitoring-hosts.json');
    }
    if ($method eq 'GET' && $path eq '/api/ca/status') {
        return send_json_raw($client, 200, ca_manager_json('status-json'));
    }
    if ($method eq 'GET' && $path eq '/api/ca/certificates') {
        return send_json_raw($client, 200, ca_manager_json('list-json'));
    }
    if ($method eq 'GET' && $path eq '/download/ca.crt') {
        return send_file($client, ca_cert_path(), 'application/x-pem-file; charset=utf-8', 'xdev-madagascar-host-ca.crt');
    }
    if ($method eq 'GET' && $path =~ m{\A/download/ca/cert/([A-Za-z0-9_.-]+)\.crt\z}) {
        my $name = $1;
        return send_file($client, ca_issued_cert_path($name), 'application/x-pem-file; charset=utf-8', "$name.crt");
    }
    if ($method eq 'GET' && $path =~ m{\A/download/ca/key/([A-Za-z0-9_.-]+)\.key\z}) {
        my $name = $1;
        return send_file($client, ca_issued_key_path($name), 'application/x-pem-file; charset=utf-8', "$name.key");
    }

    if ($method eq 'POST' && $path =~ m{^/api/}) {
        if ($path eq '/api/hosts/upsert') {
            my $payload = request_payload(\%headers, $body);
            return upsert_host($client, $payload);
        }
        if ($path eq '/api/hosts/delete') {
            my $payload = request_payload(\%headers, $body);
            return delete_host($client, $payload->{id} || '');
        }
        if ($path eq '/api/hosts/certificate') {
            my $payload = request_payload(\%headers, $body);
            return set_host_certificate($client, $payload);
        }
        if ($path eq '/api/hosts/issue-certificate') {
            my $payload = request_payload(\%headers, $body);
            return issue_host_certificate($client, $payload);
        }
        if ($path eq '/api/vhosts/reassign') {
            my $payload = request_payload(\%headers, $body);
            return reassign_vhost($client, $payload);
        }
        if ($path eq '/api/vhosts/upsert') {
            my $payload = request_payload(\%headers, $body);
            return upsert_vhost($client, $payload);
        }
        if ($path eq '/api/vhosts/delete') {
            my $payload = request_payload(\%headers, $body);
            return delete_vhost($client, $payload);
        }
        if ($path eq '/api/vhosts/certificate') {
            my $payload = request_payload(\%headers, $body);
            return set_vhost_certificate($client, $payload);
        }
        if ($path eq '/api/vhosts/issue-certificate') {
            my $payload = request_payload(\%headers, $body);
            return issue_vhost_certificate($client, $payload);
        }
        if ($path eq '/api/work-orders/confirm') {
            my $payload = request_payload(\%headers, $body);
            return confirm_work_order($client, $payload);
        }
        if ($path eq '/api/work-orders/checklist') {
            my $payload = request_payload(\%headers, $body);
            return update_work_order_checklist($client, $payload);
        }
        if ($path eq '/api/render/local-hosts-tsv') {
            my $registry = load_registry();
            my $publish = publish_dns_change($registry, 'manual-render');
            return send_json($client, 200, { ok => json_bool(1), file => $opt{local_hosts_tsv}, dns_publish => $publish });
        }
    }

    return send_json($client, 404, { error => 'not_found' });
}

sub app_page_path {
    my ($path) = @_;
    return $path =~ m{\A/(?:|overview|hosts|vhosts|dns|work-orders|ca|debug)\z};
}

sub load_registry {
    my $registry = load_registry_from_db();
    normalize_registry_policy($registry);
    return $registry;
}

sub save_registry {
    my ($registry) = @_;
    $registry->{updated_at} = iso_now();
    normalize_registry_policy($registry);
    save_registry_to_db($registry);
    return publish_dns_change($registry, 'registry-save');
}

sub load_work_orders {
    return load_work_orders_from_db();
}

sub save_work_orders {
    my ($orders) = @_;
    save_work_orders_to_db($orders);
}

sub work_orders_payload {
    my ($orders) = @_;
    my $pending = 0;
    for my $wo (@{ $orders->{work_orders} || [] }) {
        $pending++ if ($wo->{status} || 'pending') eq 'pending';
    }
    return {
        version => $orders->{version},
        work_orders => $orders->{work_orders} || [],
        counts => {
            work_orders => scalar @{ $orders->{work_orders} || [] },
            pending => $pending,
        },
    };
}

sub collect_dhcp_leases {
    my ($client, $headers, $body) = @_;
    my $expected = $ENV{HOST_MANAGER_DHCP_PUSH_TOKEN} || '';
    return send_json($client, 503, { error => 'dhcp_push_not_configured' }) unless length $expected;

    my $provided = dhcp_push_token_from_headers($headers);
    return send_json($client, 401, { error => 'invalid_dhcp_push_token' }) unless token_matches($expected, $provided);

    my $payload = request_payload($headers, $body);
    my @leases = dhcp_payload_leases($payload);
    return send_json($client, 400, { error => 'missing_dhcp_leases' }) unless @leases;

    my $dbh = dbh();
    my $now = iso_now();
    my $worker_id = clean_id($payload->{worker_id} || $payload->{source_id} || 'dhcp-router');
    $worker_id ||= 'dhcp-router';
    my @stored;
    with_transaction($dbh, sub {
        upsert_dhcp_worker($dbh, $worker_id, $now);
        for my $lease (@leases) {
            my $stored = upsert_dhcp_lease($dbh, $worker_id, $lease, $now);
            push @stored, $stored if $stored;
        }
        $dbh->do(
            'UPDATE data_workers SET last_run_at = ?, updated_at = ? WHERE worker_id = ?',
            undef,
            $now,
            $now,
            $worker_id,
        );
    });

    return send_json($client, 200, {
        ok => json_bool(1),
        worker_id => $worker_id,
        stored => scalar(@stored),
        leases => \@stored,
    });
}

sub dhcp_push_token_from_headers {
    my ($headers) = @_;
    my $token = clean_scalar($headers->{'x-dhcp-push-token'} || '');
    return $token if length $token;
    my $authorization = clean_scalar($headers->{authorization} || '');
    return $1 if $authorization =~ /\ABearer\s+(.+)\z/i;
    return '';
}

sub token_matches {
    my ($expected, $provided) = @_;
    return 0 unless length($expected || '') && length($provided || '');
    return 0 unless length($expected) == length($provided);
    my $diff = 0;
    for my $i (0 .. length($expected) - 1) {
        $diff |= ord(substr($expected, $i, 1)) ^ ord(substr($provided, $i, 1));
    }
    return $diff == 0 ? 1 : 0;
}

sub dhcp_payload_leases {
    my ($payload) = @_;
    return () unless ref($payload) eq 'HASH';
    if (ref($payload->{leases}) eq 'ARRAY') {
        return grep { ref($_) eq 'HASH' } @{ $payload->{leases} };
    }
    return ($payload);
}

sub upsert_dhcp_worker {
    my ($dbh, $worker_id, $now) = @_;
    $dbh->do(
        'INSERT INTO data_workers (worker_id, worker_type, name, status, source, last_run_at, notes, created_at, updated_at) '
        . "VALUES (?, 'dhcp', 'Router DHCP leases', 'active', 'push:192.168.2.1', ?, 'DHCP lease push collector source.', ?, ?) "
        . 'ON CONFLICT(worker_id) DO UPDATE SET worker_type = excluded.worker_type, name = excluded.name, status = excluded.status, '
        . 'source = excluded.source, last_run_at = excluded.last_run_at, notes = excluded.notes, updated_at = excluded.updated_at',
        undef,
        $worker_id,
        $now,
        $now,
        $now,
    );
}

sub upsert_dhcp_lease {
    my ($dbh, $worker_id, $lease, $now) = @_;
    my $ip = clean_ip($lease->{ip_address} || $lease->{ip} || $lease->{address} || '');
    my $mac = clean_mac($lease->{mac_address} || $lease->{mac} || $lease->{active_mac} || '');
    return unless length $ip || length $mac;

    my $name = normalize_dhcp_name($lease->{observed_name} || $lease->{host_name} || $lease->{hostname} || $lease->{name} || '');
    my $state = clean_scalar($lease->{lease_state} || $lease->{state} || $lease->{status} || '');
    if (!length $state && exists $lease->{bound}) {
        $state = ($lease->{bound} || '') eq '1' ? 'bound' : 'unbound';
    }
    $state ||= 'observed';

    my $lease_key = length $mac ? "$worker_id|mac|$mac" : "$worker_id|ip|$ip";
    my $host_fqdn = match_dhcp_host_fqdn($dbh, $name, $ip);
    my $raw = json_encode($lease);
    $dbh->do(
        'INSERT INTO dhcp_leases (lease_key, worker_id, host_fqdn, observed_name, ip_address, mac_address, lease_state, first_seen, last_seen, raw) '
        . 'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) '
        . 'ON CONFLICT(lease_key) DO UPDATE SET host_fqdn = excluded.host_fqdn, observed_name = excluded.observed_name, '
        . 'ip_address = excluded.ip_address, mac_address = excluded.mac_address, lease_state = excluded.lease_state, '
        . 'last_seen = excluded.last_seen, raw = excluded.raw',
        undef,
        $lease_key,
        $worker_id,
        length($host_fqdn) ? $host_fqdn : undef,
        $name,
        $ip,
        $mac,
        $state,
        $now,
        $now,
        $raw,
    );

    return {
        lease_key => $lease_key,
        host_fqdn => $host_fqdn,
        observed_name => $name,
        ip_address => $ip,
        mac_address => $mac,
        lease_state => $state,
    };
}

sub match_dhcp_host_fqdn {
    my ($dbh, $name, $ip) = @_;
    my @names;
    $name = normalize_dns_name($name || '');
    if (length $name) {
        push @names, $name;
        push @names, "$name.madagascar.xdev.ro" unless $name =~ /\./;
    }
    for my $candidate (unique_preserve(@names)) {
        my ($fqdn) = $dbh->selectrow_array('SELECT fqdn FROM hosts WHERE fqdn = ? AND status <> ?', undef, $candidate, 'retired');
        return $fqdn if $fqdn;
        ($fqdn) = $dbh->selectrow_array('SELECT host_fqdn FROM host_aliases WHERE alias_name = ? AND status = ?', undef, $candidate, 'active');
        return $fqdn if $fqdn;
    }
    if (length($ip || '')) {
        my ($fqdn) = $dbh->selectrow_array('SELECT fqdn FROM hosts WHERE (dns_ip = ? OR hosts_ip = ?) AND status <> ? ORDER BY fqdn LIMIT 1', undef, $ip, $ip, 'retired');
        return $fqdn if $fqdn;
    }
    return '';
}

sub confirm_work_order {
    my ($client, $payload) = @_;
    my $id = clean_scalar($payload->{id} || '');
    return send_json($client, 400, { error => 'invalid_work_order_id' }) unless $id =~ /\AWO-[A-Za-z0-9_.-]+\z/;
    return send_json($client, 400, { error => 'confirmation_required' }) unless clean_scalar($payload->{confirm} || '') eq $id;

    my $orders = load_work_orders();
    my $work_order;
    for my $wo (@{ $orders->{work_orders} || [] }) {
        if (($wo->{id} || '') eq $id) {
            $work_order = $wo;
            last;
        }
    }
    return send_json($client, 404, { error => 'work_order_not_found' }) unless $work_order;
    return send_json($client, 409, { error => 'work_order_not_pending' }) unless ($work_order->{status} || 'pending') eq 'pending';
    my $incomplete = incomplete_work_order_items($work_order);
    return send_json($client, 409, {
        error => 'work_order_incomplete',
        incomplete => $incomplete,
    }) if @$incomplete;

    my $registry = load_registry();
    my $results = apply_work_order($registry, $work_order);
    $work_order->{status} = 'confirmed';
    $work_order->{confirmed_at} = iso_now();
    $work_order->{result} = scalar(@$results) . ' action(s) applied';

    my $publish = save_registry($registry);
    save_work_orders($orders);

    return send_json($client, 200, {
        ok => json_bool(1),
        work_order => $work_order,
        results => $results,
        local_hosts_tsv => $opt{local_hosts_tsv},
        dns_publish => $publish,
    });
}

sub update_work_order_checklist {
    my ($client, $payload) = @_;
    my $id = clean_scalar($payload->{id} || '');
    my $item_id = clean_scalar($payload->{item_id} || '');
    my $status = clean_scalar($payload->{status} || '');
    my $notes = clean_scalar($payload->{notes} || '');
    return send_json($client, 400, { error => 'invalid_work_order_id' }) unless $id =~ /\AWO-[A-Za-z0-9_.-]+\z/;
    return send_json($client, 400, { error => 'invalid_checklist_item' }) unless $item_id =~ /\A[A-Za-z0-9_.-]+\z/;
    return send_json($client, 400, { error => 'invalid_checklist_status' }) unless $status =~ /\A(?:pending|done|blocked)\z/;

    my $orders = load_work_orders();
    my $work_order;
    for my $wo (@{ $orders->{work_orders} || [] }) {
        if (($wo->{id} || '') eq $id) {
            $work_order = $wo;
            last;
        }
    }
    return send_json($client, 404, { error => 'work_order_not_found' }) unless $work_order;
    return send_json($client, 409, { error => 'work_order_not_pending' }) unless ($work_order->{status} || 'pending') eq 'pending';

    my $item;
    for my $candidate (@{ $work_order->{checklist} || [] }) {
        if (($candidate->{id} || '') eq $item_id) {
            $item = $candidate;
            last;
        }
    }
    return send_json($client, 404, { error => 'checklist_item_not_found' }) unless $item;

    $item->{status} = $status;
    $item->{updated_at} = iso_now();
    $item->{notes} = $notes if length $notes;
    save_work_orders($orders);
    return send_json($client, 200, { ok => json_bool(1), work_order => $work_order });
}

sub incomplete_work_order_items {
    my ($work_order) = @_;
    my @incomplete;
    for my $item (@{ $work_order->{checklist} || [] }) {
        push @incomplete, $item unless ($item->{status} || 'pending') eq 'done';
    }
    return \@incomplete;
}

sub apply_work_order {
    my ($registry, $work_order) = @_;
    my @results;
    for my $action (@{ $work_order->{actions} || [] }) {
        my $type = $action->{type} || '';
        if ($type eq 'remove_name') {
            my $host_id = $action->{host_id} || '';
            my $name = $action->{name} || '';
            my $removed = 0;
            for my $host (@{ $registry->{hosts} || [] }) {
                next unless ($host->{id} || '') eq $host_id;
                my @kept_aliases = grep { $_ ne $name } declared_alias_names($host);
                my @kept_vhosts = grep { $_ ne $name } declared_vhost_names($host);
                $removed = (@kept_aliases != @{ $host->{aliases} || [] }) || (@kept_vhosts != @{ $host->{vhosts} || [] });
                $host->{aliases} = \@kept_aliases;
                $host->{vhosts} = \@kept_vhosts;
                last;
            }
            push @results, {
                type => $type,
                host_id => $host_id,
                name => $name,
                removed => json_bool($removed),
            };
        } else {
            die "Unsupported work order action: $type\n";
        }
    }
    return \@results;
}

sub registry_payload {
    my ($registry) = @_;
    my $problems = analyze_hosts($registry->{hosts});
    my $dbh = dbh();
    my %host_tls = host_tls_payloads($dbh);
    my @hosts = map { host_payload($_, $host_tls{ canonical_host_fqdn($_) }) } @{ $registry->{hosts} };
    my @vhosts = vhost_payloads($dbh);
    my @certificates = certificate_payloads($dbh);
    my $vhost_count = sum(map { scalar declared_vhost_names($_) } @{ $registry->{hosts} });
    return {
        version => $registry->{version},
        updated_at => $registry->{updated_at},
        policy => $registry->{policy},
        hosts => \@hosts,
        vhosts => \@vhosts,
        certificates => \@certificates,
        problems => $problems,
        counts => {
            hosts => scalar @{ $registry->{hosts} },
            vhosts => scalar(@vhosts) || $vhost_count,
            problems => scalar @$problems,
        },
    };
}

sub host_tls_payloads {
    my ($dbh) = @_;
    my %rows;
    my $sth = $dbh->prepare(<<'SQL');
SELECT
    ht.host_fqdn,
    ht.certificate_id,
    c.common_name,
    c.not_after,
    c.fingerprint_sha256,
    c.status AS certificate_status
FROM host_tls ht
LEFT JOIN certificates c ON c.certificate_id = ht.certificate_id
ORDER BY ht.host_fqdn
SQL
    $sth->execute;
    while (my $row = $sth->fetchrow_hashref) {
        my $host_fqdn = clean_scalar($row->{host_fqdn} || '');
        next unless length $host_fqdn;
        my $cert_id = clean_scalar($row->{certificate_id} || '');
        my %payload = (
            certificate_id => $cert_id,
        );
        if (length $cert_id) {
            $payload{certificate} = {
                id => $cert_id,
                name => $cert_id,
                common_name => clean_scalar($row->{common_name} || ''),
                status => clean_scalar($row->{certificate_status} || ''),
                not_after => clean_scalar($row->{not_after} || ''),
                fingerprint_sha256 => clean_scalar($row->{fingerprint_sha256} || ''),
                has_private_key => json_bool(ca_private_key_exists($cert_id)),
            };
        }
        $rows{$host_fqdn} = \%payload;
    }
    return %rows;
}

sub vhost_payloads {
    my ($dbh) = @_;
    my @rows;
    my $sth = $dbh->prepare(<<'SQL');
SELECT
    v.vhost_fqdn,
    v.host_fqdn,
    v.status AS vhost_status,
    v.certificate_id,
    h.legacy_id,
    h.monitoring,
    h.status AS host_status,
    c.common_name,
    c.not_after,
    c.fingerprint_sha256,
    c.status AS certificate_status
FROM vhosts v
JOIN hosts h ON h.fqdn = v.host_fqdn
LEFT JOIN certificates c ON c.certificate_id = v.certificate_id
WHERE v.status = 'active'
ORDER BY v.vhost_fqdn
SQL
    $sth->execute;
    while (my $row = $sth->fetchrow_hashref) {
        my $cert_id = clean_scalar($row->{certificate_id} || '');
        my %certificate = $cert_id ? (
            id => $cert_id,
            name => $cert_id,
            common_name => clean_scalar($row->{common_name} || ''),
            status => clean_scalar($row->{certificate_status} || ''),
            not_after => clean_scalar($row->{not_after} || ''),
            fingerprint_sha256 => clean_scalar($row->{fingerprint_sha256} || ''),
            has_private_key => json_bool(ca_private_key_exists($cert_id)),
        ) : ();
        push @rows, {
            vhost => $row->{vhost_fqdn},
            vhost_fqdn => $row->{vhost_fqdn},
            host_id => $row->{legacy_id} || '',
            host_fqdn => $row->{host_fqdn},
            derived_aliases => short_alias_for_fqdn($row->{vhost_fqdn}) ? [ short_alias_for_fqdn($row->{vhost_fqdn}) ] : [],
            monitoring => $row->{monitoring} || '',
            status => $row->{host_status} || $row->{vhost_status} || '',
            vhost_status => $row->{vhost_status} || '',
            certificate_id => $cert_id,
            certificate => $cert_id ? \%certificate : undef,
        };
    }
    return @rows;
}

sub certificate_payloads {
    my ($dbh) = @_;
    my @certificates;
    my $sth = $dbh->prepare('SELECT * FROM certificates WHERE status <> ? ORDER BY certificate_id');
    $sth->execute('retired');
    while (my $row = $sth->fetchrow_hashref) {
        my $id = clean_scalar($row->{certificate_id} || '');
        next unless $id;
        push @certificates, {
            id => $id,
            name => $id,
            host_fqdn => $row->{host_fqdn} || '',
            common_name => $row->{common_name} || '',
            subject => $row->{subject} || '',
            issuer => $row->{issuer} || '',
            serial => $row->{serial} || '',
            status => $row->{status} || '',
            not_before => $row->{not_before} || '',
            not_after => $row->{not_after} || '',
            fingerprint_sha256 => $row->{fingerprint_sha256} || '',
            dns_names => [ certificate_dns_names($dbh, $id) ],
            has_private_key => json_bool(ca_private_key_exists($id)),
        };
    }
    return @certificates;
}

sub certificate_dns_names {
    my ($dbh, $certificate_id) = @_;
    my @names;
    my $sth = $dbh->prepare('SELECT dns_name FROM certificate_dns_names WHERE certificate_id = ? ORDER BY dns_name');
    $sth->execute($certificate_id);
    while (my ($name) = $sth->fetchrow_array) {
        push @names, $name;
    }
    return @names;
}

sub upsert_host {
    my ($client, $payload) = @_;
    my $id = clean_id($payload->{id} || '');
    return send_json($client, 400, { error => 'invalid_id' }) unless $id;

    my $ip = canonical_ip($payload);
    return send_json($client, 400, { error => 'missing_ip' }) unless $ip;

    my $fqdn = canonical_host_fqdn($payload);
    return send_json($client, 400, { error => 'missing_fqdn' }) unless $fqdn;
    my @aliases = clean_alias_names($payload);

    my $registry = load_registry();
    my ($existing_host) = grep { ($_->{id} || '') eq $id } @{ $registry->{hosts} || [] };
    my @vhosts = defined $payload->{vhosts}
        ? clean_vhost_names($payload)
        : ($existing_host ? declared_vhost_names($existing_host) : ());
    my %host = (
        id => $id,
        fqdn => $fqdn,
        status => clean_scalar($payload->{status} || 'active'),
        ip => $ip,
        aliases => \@aliases,
        vhosts => \@vhosts,
        roles => [ clean_list($payload->{roles}) ],
        sources => [ clean_list($payload->{sources}) ],
        monitoring => clean_scalar($payload->{monitoring} || 'pending'),
        notes => clean_scalar($payload->{notes} || ''),
    );

    my $response = eval {
        my $replaced = 0;
        for my $i (0 .. $#{ $registry->{hosts} }) {
            if ($registry->{hosts}->[$i]{id} eq $id) {
                $registry->{hosts}->[$i] = \%host;
                $replaced = 1;
                last;
            }
        }
        push @{ $registry->{hosts} }, \%host unless $replaced;
        save_registry($registry);
        1;
    };
    if (!$response) {
        my $err = $@ || 'upsert_failed';
        return send_json($client, 409, { error => 'alias_conflict', detail => clean_scalar($err) })
            if $err =~ /alias_conflict:/;
        die $err;
    }
    return send_json($client, 200, { ok => json_bool(1), host => \%host });
}

sub delete_host {
    my ($client, $id) = @_;
    $id = clean_id($id);
    return send_json($client, 400, { error => 'invalid_id' }) unless $id;

    my $registry = load_registry();
    my @kept = grep { $_->{id} ne $id } @{ $registry->{hosts} };
    return send_json($client, 404, { error => 'not_found' }) if @kept == @{ $registry->{hosts} };
    $registry->{hosts} = \@kept;
    save_registry($registry);
    return send_json($client, 200, { ok => json_bool(1) });
}

sub reassign_vhost {
    my ($client, $payload) = @_;
    my $vhost = normalize_dns_name($payload->{vhost_fqdn} || '');
    my $target_fqdn = normalize_dns_name($payload->{host_fqdn} || $payload->{fqdn} || '');
    return send_json($client, 400, { error => 'invalid_vhost' }) unless vhost_name_is_valid($vhost);
    return send_json($client, 400, { error => 'missing_target_host' }) unless $target_fqdn;

    my $dbh = dbh();
    my ($current_fqdn) = $dbh->selectrow_array(
        "SELECT host_fqdn FROM vhosts WHERE vhost_fqdn = ? AND status = 'active'",
        undef,
        $vhost,
    );
    return send_json($client, 404, { error => 'vhost_not_found' }) unless $current_fqdn;
    return send_json($client, 400, { error => 'invalid_target_host' }) unless db_scalar($dbh, 'SELECT COUNT(*) FROM hosts WHERE fqdn = ? AND status <> ?', $target_fqdn, 'retired');
    return send_json($client, 200, { ok => json_bool(1), vhost_fqdn => $vhost, host_fqdn => $current_fqdn }) if $current_fqdn eq $target_fqdn;

    my $result = eval {
        with_transaction($dbh, sub {
            my $now = iso_now();
            $dbh->do(
                "UPDATE vhosts SET host_fqdn = ?, updated_at = ?, status = 'active' WHERE vhost_fqdn = ?",
                undef,
                $target_fqdn, $now, $vhost,
            );

            my $registry = load_registry_from_db();
            my ($target_host) = grep { ($_->{fqdn} || '') eq $target_fqdn } @{ $registry->{hosts} || [] };
            my ($current_host) = grep { ($_->{fqdn} || '') eq $current_fqdn } @{ $registry->{hosts} || [] };

            upsert_host_to_db($dbh, $target_host) if $target_host;
            upsert_host_to_db($dbh, $current_host) if $current_host;
            set_schema_meta($dbh, 'registry_updated_at', iso_now());
        });
        1;
    };
    if (!$result) {
        my $err = $@ || 'vhost_reassign_failed';
        return send_json($client, 409, { error => 'vhost_reassign_failed', detail => clean_scalar($err) });
    }
    my $publish = publish_dns_change(load_registry(), 'vhost-reassign');
    return send_json($client, 200, { ok => json_bool(1), vhost_fqdn => $vhost, host_fqdn => $target_fqdn, previous_host_fqdn => $current_fqdn, dns_publish => $publish });
}

sub upsert_vhost {
    my ($client, $payload) = @_;
    my $vhost = normalize_dns_name($payload->{vhost_fqdn} || '');
    my $target_fqdn = normalize_dns_name($payload->{host_fqdn} || $payload->{fqdn} || '');
    return send_json($client, 400, { error => 'invalid_vhost' }) unless vhost_name_is_valid($vhost);
    return send_json($client, 400, { error => 'missing_target_host' }) unless $target_fqdn;

    my $dbh = dbh();
    return send_json($client, 400, { error => 'invalid_target_host' }) unless db_scalar($dbh, 'SELECT COUNT(*) FROM hosts WHERE fqdn = ? AND status <> ?', $target_fqdn, 'retired');
    return send_json($client, 400, { error => 'vhost_matches_host' }) if db_scalar($dbh, 'SELECT COUNT(*) FROM hosts WHERE fqdn = ? AND status <> ?', $vhost, 'retired');
    my ($current_fqdn) = $dbh->selectrow_array(
        "SELECT host_fqdn FROM vhosts WHERE vhost_fqdn = ? AND status = 'active'",
        undef,
        $vhost,
    );

    my $result = eval {
        with_transaction($dbh, sub {
            my $now = iso_now();
            upsert_vhost_to_db($dbh, $target_fqdn, $vhost, $now);

            my $registry = load_registry_from_db();
            my ($target_host) = grep { ($_->{fqdn} || '') eq $target_fqdn } @{ $registry->{hosts} || [] };
            my ($current_host) = grep { ($_->{fqdn} || '') eq ($current_fqdn || '') } @{ $registry->{hosts} || [] };

            upsert_host_to_db($dbh, $target_host) if $target_host;
            upsert_host_to_db($dbh, $current_host) if $current_host && ($current_fqdn || '') ne $target_fqdn;
            set_schema_meta($dbh, 'registry_updated_at', iso_now());
        });
        1;
    };
    if (!$result) {
        my $err = $@ || 'vhost_upsert_failed';
        return send_json($client, 409, { error => 'vhost_upsert_failed', detail => clean_scalar($err) });
    }
    my $publish = publish_dns_change(load_registry(), 'vhost-upsert');
    return send_json($client, 200, { ok => json_bool(1), vhost_fqdn => $vhost, host_fqdn => $target_fqdn, previous_host_fqdn => $current_fqdn || '', dns_publish => $publish });
}

sub delete_vhost {
    my ($client, $payload) = @_;
    my $vhost = normalize_dns_name($payload->{vhost_fqdn} || '');
    my $confirm = normalize_dns_name($payload->{confirm} || '');
    return send_json($client, 400, { error => 'invalid_vhost' }) unless vhost_name_is_valid($vhost);
    return send_json($client, 400, { error => 'confirmation_required' }) unless $confirm eq $vhost;

    my $dbh = dbh();
    my ($current_fqdn) = $dbh->selectrow_array(
        "SELECT host_fqdn FROM vhosts WHERE vhost_fqdn = ? AND status = 'active'",
        undef,
        $vhost,
    );
    return send_json($client, 404, { error => 'vhost_not_found' }) unless $current_fqdn;

    my $result = eval {
        with_transaction($dbh, sub {
            my $now = iso_now();
            $dbh->do(
                "UPDATE vhosts SET status = 'retired', updated_at = ? WHERE vhost_fqdn = ? AND status = 'active'",
                undef,
                $now, $vhost,
            );

            my $registry = load_registry_from_db();
            my ($current_host) = grep { ($_->{fqdn} || '') eq $current_fqdn } @{ $registry->{hosts} || [] };
            upsert_host_to_db($dbh, $current_host) if $current_host;
            set_schema_meta($dbh, 'registry_updated_at', iso_now());
        });
        1;
    };
    if (!$result) {
        my $err = $@ || 'vhost_delete_failed';
        return send_json($client, 409, { error => 'vhost_delete_failed', detail => clean_scalar($err) });
    }
    my $publish = publish_dns_change(load_registry(), 'vhost-delete');
    return send_json($client, 200, { ok => json_bool(1), vhost_fqdn => $vhost, previous_host_fqdn => $current_fqdn, dns_publish => $publish });
}

sub set_host_certificate {
    my ($client, $payload) = @_;
    my $host_fqdn = normalize_dns_name($payload->{host_fqdn} || $payload->{fqdn} || '');
    my $raw_certificate_id = clean_scalar($payload->{certificate_id} || $payload->{cert_id} || '');
    my $certificate_id = clean_certificate_id($raw_certificate_id);
    return send_json($client, 400, { error => 'invalid_host' }) unless $host_fqdn;
    return send_json($client, 400, { error => 'invalid_certificate' })
        if length($raw_certificate_id) && !length($certificate_id);

    my $dbh = dbh();
    return send_json($client, 404, { error => 'host_not_found' })
        unless db_scalar($dbh, "SELECT COUNT(*) FROM hosts WHERE fqdn = ? AND status = 'active'", $host_fqdn);
    if (length $certificate_id) {
        return send_json($client, 400, { error => 'invalid_certificate' })
            unless db_scalar($dbh, "SELECT COUNT(*) FROM certificates WHERE certificate_id = ? AND status <> 'retired'", $certificate_id);
    }

    my $now = iso_now();
    with_transaction($dbh, sub {
        upsert_host_tls_row($dbh, $host_fqdn, $certificate_id, $now);
        set_schema_meta($dbh, 'registry_updated_at', $now);
    });
    return send_json($client, 200, { ok => json_bool(1), host_fqdn => $host_fqdn, certificate_id => $certificate_id });
}

sub issue_host_certificate {
    my ($client, $payload) = @_;
    my $host_fqdn = normalize_dns_name($payload->{host_fqdn} || $payload->{fqdn} || '');
    return send_json($client, 400, { error => 'invalid_host' }) unless $host_fqdn;

    my $registry = load_registry();
    my ($host) = grep { canonical_host_fqdn($_) eq $host_fqdn } @{ $registry->{hosts} || [] };
    return send_json($client, 404, { error => 'host_not_found' }) unless $host;

    my @dns_names = unique_preserve(grep { length $_ } (
        $host_fqdn,
        declared_alias_names($host),
        derived_alias_names($host),
    ));
    my $certificate_id = clean_certificate_id($host_fqdn . '-' . strftime('%Y%m%d%H%M%S', localtime));
    my $dbh = dbh();
    my $issued = eval {
        ca_manager_output('issue', $certificate_id, @dns_names);
        ca_manager_json('list-json');
        with_transaction($dbh, sub {
            my $now = iso_now();
            upsert_host_tls_row($dbh, $host_fqdn, $certificate_id, $now);
            set_schema_meta($dbh, 'registry_updated_at', $now);
        });
        1;
    };
    if (!$issued) {
        return send_json($client, 409, { error => 'certificate_issue_failed', detail => clean_scalar($@ || '') });
    }

    my ($cert) = grep { ($_->{id} || '') eq $certificate_id } certificate_payloads($dbh);
    return send_json($client, 200, {
        ok => json_bool(1),
        host_fqdn => $host_fqdn,
        certificate_id => $certificate_id,
        certificate => $cert || { id => $certificate_id, name => $certificate_id, dns_names => \@dns_names },
    });
}

sub set_vhost_certificate {
    my ($client, $payload) = @_;
    my $vhost = normalize_dns_name($payload->{vhost_fqdn} || '');
    my $raw_certificate_id = clean_scalar($payload->{certificate_id} || $payload->{cert_id} || '');
    my $certificate_id = clean_certificate_id($raw_certificate_id);
    return send_json($client, 400, { error => 'invalid_vhost' }) unless vhost_name_is_valid($vhost);
    return send_json($client, 400, { error => 'invalid_certificate' })
        if length($raw_certificate_id) && !length($certificate_id);

    my $dbh = dbh();
    return send_json($client, 404, { error => 'vhost_not_found' })
        unless db_scalar($dbh, "SELECT COUNT(*) FROM vhosts WHERE vhost_fqdn = ? AND status = 'active'", $vhost);
    if (length $certificate_id) {
        return send_json($client, 400, { error => 'invalid_certificate' })
            unless db_scalar($dbh, "SELECT COUNT(*) FROM certificates WHERE certificate_id = ? AND status <> 'retired'", $certificate_id);
    }

    my $now = iso_now();
    $dbh->do(
        'UPDATE vhosts SET certificate_id = ?, tls_mode = ?, updated_at = ? WHERE vhost_fqdn = ? AND status = ?',
        undef,
        length($certificate_id) ? $certificate_id : undef,
        length($certificate_id) ? 'local-ca' : 'none',
        $now,
        $vhost,
        'active',
    );
    set_schema_meta($dbh, 'registry_updated_at', $now);
    return send_json($client, 200, { ok => json_bool(1), vhost_fqdn => $vhost, certificate_id => $certificate_id });
}

sub issue_vhost_certificate {
    my ($client, $payload) = @_;
    my $vhost = normalize_dns_name($payload->{vhost_fqdn} || '');
    return send_json($client, 400, { error => 'invalid_vhost' }) unless vhost_name_is_valid($vhost);

    my $dbh = dbh();
    my ($host_fqdn) = $dbh->selectrow_array(
        "SELECT host_fqdn FROM vhosts WHERE vhost_fqdn = ? AND status = 'active'",
        undef,
        $vhost,
    );
    return send_json($client, 404, { error => 'vhost_not_found' }) unless $host_fqdn;

    my @dns_names = unique_preserve(grep { length $_ } ($vhost, short_alias_for_fqdn($vhost)));
    my $certificate_id = clean_certificate_id($vhost . '-' . strftime('%Y%m%d%H%M%S', localtime));
    my $issued = eval {
        ca_manager_output('issue', $certificate_id, @dns_names);
        ca_manager_json('list-json');
        with_transaction($dbh, sub {
            my $now = iso_now();
            $dbh->do(
                "UPDATE vhosts SET certificate_id = ?, tls_mode = 'local-ca', updated_at = ? WHERE vhost_fqdn = ? AND status = 'active'",
                undef,
                $certificate_id,
                $now,
                $vhost,
            );
            set_schema_meta($dbh, 'registry_updated_at', $now);
        });
        1;
    };
    if (!$issued) {
        return send_json($client, 409, { error => 'certificate_issue_failed', detail => clean_scalar($@ || '') });
    }

    my ($cert) = grep { ($_->{id} || '') eq $certificate_id } certificate_payloads($dbh);
    return send_json($client, 200, {
        ok => json_bool(1),
        vhost_fqdn => $vhost,
        host_fqdn => $host_fqdn,
        certificate_id => $certificate_id,
        certificate => $cert || { id => $certificate_id, name => $certificate_id, dns_names => \@dns_names },
    });
}

sub analyze_hosts {
    my ($hosts) = @_;
    my @problems;
    my (%names, %ids);
    for my $host (@$hosts) {
        push @problems, problem($host, 'duplicate-id', "Duplicate id $host->{id}") if $ids{ $host->{id} }++;
        my $fqdn = canonical_host_fqdn($host);
        push @problems, problem($host, 'missing-fqdn', 'No madagascar.xdev.ro FQDN') unless ($fqdn =~ /\.madagascar\.xdev\.ro$/) || ($host->{status} || '') ne 'active';
        my @declared = declared_dns_names($host);
        push @problems, problem($host, 'deprecated-vad-is', 'Deprecated vad.is.xdev.ro name present')
            if grep { /\.vad\.is\.xdev\.ro$/ } @declared;
        push @problems, problem($host, 'legacy-prefix', 'Legacy prefix should be normalized out')
            if grep { /^(is|vad|b)-/ } @declared;
        for my $name (@declared) {
            push @problems, problem($host, 'duplicate-name', "Duplicate name $name") if $names{$name}++;
        }
        my %declared = map { $_ => 1 } @declared;
        for my $derived (derived_alias_names($host), derived_vhost_alias_names($host)) {
            push @problems, problem($host, 'redundant-derived-name', "Name $derived is derived from madagascar.xdev.ro")
                if $declared{$derived};
        }
        push @problems, problem($host, 'missing-ip', 'Host is missing a canonical routable IP')
            unless canonical_ip($host) || ($host->{status} || '') ne 'active';
    }
    return \@problems;
}

sub host_payload {
    my ($host, $tls) = @_;
    my %copy = %$host;
    $copy{fqdn} = canonical_host_fqdn($host);
    $copy{ip} = canonical_ip($host);
    $copy{names} = [ effective_names($host) ];
    $copy{declared_names} = [ declared_dns_names($host) ];
    $copy{aliases} = [ declared_alias_names($host) ];
    $copy{derived_aliases} = [ derived_alias_names($host) ];
    $copy{vhosts} = [ declared_vhost_names($host) ];
    $copy{derived_vhost_aliases} = [ derived_vhost_alias_names($host) ];
    $copy{certificate_id} = clean_scalar($tls->{certificate_id} || '');
    $copy{certificate} = $tls->{certificate} if $tls && ref($tls->{certificate}) eq 'HASH';
    return \%copy;
}

sub effective_names {
    my ($host) = @_;
    my @names = declared_dns_names($host);
    push @names, derived_alias_names($host), derived_vhost_alias_names($host);
    return unique_preserve(@names);
}

sub host_dns_names {
    my ($host) = @_;
    my @names;
    my $fqdn = canonical_host_fqdn($host);
    push @names, $fqdn if length $fqdn;
    push @names, declared_alias_names($host), derived_alias_names($host);
    return unique_preserve(@names);
}

sub vhost_cname_records {
    my ($host) = @_;
    my $target = canonical_host_fqdn($host);
    return () unless length $target;
    my @records;
    for my $vhost (declared_vhost_names($host)) {
        push @records, [ $vhost, $target ];
        if (my $short = short_alias_for_fqdn($vhost)) {
            push @records, [ $short, $target ];
        }
    }
    my %seen;
    return grep { !$seen{$_->[0]}++ } @records;
}

sub declared_dns_names {
    my ($host) = @_;
    my @names;
    my $fqdn = canonical_host_fqdn($host);
    push @names, $fqdn if length $fqdn;
    push @names, declared_alias_names($host);
    push @names, declared_vhost_names($host);
    return unique_preserve(@names);
}

sub declared_alias_names {
    my ($host) = @_;
    return unique_preserve(map { normalize_dns_name($_) } @{ $host->{aliases} || [] });
}

sub declared_vhost_names {
    my ($host) = @_;
    return unique_preserve(map { normalize_dns_name($_) } @{ $host->{vhosts} || [] });
}

sub declared_dns_names_legacy {
    my ($host) = @_;
    return map { normalize_dns_name($_) } @{ $host->{names} || [] };
}

sub split_legacy_names {
    my ($id, $names) = @_;
    my $fallback = clean_id($id || '');
    my (%result) = (
        fqdn => '',
        aliases => [],
        vhosts => [],
    );
    for my $name (map { normalize_dns_name($_) } @$names) {
        next unless length $name;
        if (!$result{fqdn} && $name =~ /\.madagascar\.xdev\.ro\z/ && !name_is_vhost($name)) {
            $result{fqdn} = $name;
            next;
        }
        if (!$result{fqdn} && $name =~ /\./ && !name_is_vhost($name)) {
            $result{fqdn} = $name;
            next;
        }
        if (name_is_vhost($name)) {
            push @{ $result{vhosts} }, $name;
        } else {
            push @{ $result{aliases} }, $name;
        }
    }
    $result{fqdn} ||= $fallback ? "$fallback.madagascar.xdev.ro" : '';
    $result{aliases} = [ unique_preserve(grep { $_ ne $result{fqdn} } @{ $result{aliases} }) ];
    $result{vhosts} = [ unique_preserve(@{ $result{vhosts} }) ];
    return \%result;
}

sub derived_alias_names {
    my ($host) = @_;
    my @derived;
    my $fqdn = canonical_host_fqdn($host);
    push @derived, short_alias_for_fqdn($fqdn) if length $fqdn;
    for my $name (declared_alias_names($host)) {
        push @derived, short_alias_for_fqdn($name);
    }
    return unique_preserve(grep { length $_ } @derived);
}

sub derived_vhost_alias_names {
    my ($host) = @_;
    my @derived;
    for my $name (declared_vhost_names($host)) {
        push @derived, short_alias_for_fqdn($name);
    }
    return unique_preserve(grep { length $_ } @derived);
}

sub clean_alias_names {
    my ($payload) = @_;
    return clean_name_bucket($payload->{aliases})
        if defined $payload->{aliases};
    my @legacy = remove_derived_names(clean_list($payload->{names}));
    return grep { !name_is_vhost($_) && $_ ne canonical_host_fqdn({ %$payload, names => \@legacy }) } @legacy;
}

sub clean_vhost_names {
    my ($payload) = @_;
    return clean_name_bucket($payload->{vhosts})
        if defined $payload->{vhosts};
    my @legacy = remove_derived_names(clean_list($payload->{names}));
    return grep { name_is_vhost($_) } @legacy;
}

sub clean_name_bucket {
    my ($value) = @_;
    my @names = clean_list($value);
    return unique_preserve(map { normalize_dns_name($_) } remove_derived_names(@names));
}

sub remove_derived_names {
    my @names = @_;
    my %derived;
    for my $name (@names) {
        next unless $name =~ /^(.+)\.madagascar\.xdev\.ro$/;
        $derived{$1} = 1;
    }
    return grep { !$derived{$_} } @names;
}

sub unique_preserve {
    my @values = @_;
    my %seen;
    return grep { !$seen{$_}++ } @values;
}

sub canonical_ip {
    my ($host) = @_;
    return '' unless $host && ref($host) eq 'HASH';
    for my $key (qw(ip dns_ip hosts_ip)) {
        my $value = clean_scalar($host->{$key} || '');
        return $value if length $value;
    }
    return '';
}

sub problem {
    my ($host, $code, $message) = @_;
    return { host_id => $host->{id}, code => $code, message => $message };
}

sub render_local_hosts_tsv {
    my ($registry) = @_;
    my $out = "# Local DNS manifest for the madagascar network.\n";
    $out .= "# Generated by scripts/host_manager.pl from the runtime SQLite registry.\n";
    $out .= "#\n";
    $out .= "# Format:\n";
    $out .= "# ip<TAB>name [aliases...]\n";
    $out .= "# CNAME<TAB>alias<TAB>target\n";
    $out .= "#\n";
    $out .= "# Priority rule:\n";
    $out .= "# - DHCP lease/reservation on 192.168.2.1 is canonical for LAN IP allocation.\n";
    $out .= "# - madagascar.json is canonical for cluster roles and service interfaces.\n";
    $out .= "# - This file publishes approved local DNS records derived from those sources.\n";
    for my $host (sort { $a->{id} cmp $b->{id} } @{ $registry->{hosts} }) {
        next unless ($host->{status} || 'active') eq 'active';
        my $ip = canonical_ip($host);
        next unless $ip;
        my @names = host_dns_names($host);
        next unless @names;
        $out .= join("\t", $ip, join(' ', @names)) . "\n";
        for my $record (vhost_cname_records($host)) {
            $out .= join("\t", 'CNAME', @$record) . "\n";
        }
    }
    return $out;
}

sub render_monitoring {
    my ($registry) = @_;
    my @hosts;
    for my $host (sort { $a->{id} cmp $b->{id} } @{ $registry->{hosts} }) {
        next unless ($host->{status} || 'active') eq 'active';
        next if ($host->{monitoring} || 'pending') eq 'disabled';
        my @names = effective_names($host);
        push @hosts, {
            id => $host->{id},
            primary_name => $names[0],
            address => canonical_ip($host),
            aliases => \@names,
            fqdn => canonical_host_fqdn($host),
            declared_names => [ declared_dns_names($host) ],
            aliases_declared => [ declared_alias_names($host) ],
            aliases_derived => [ derived_alias_names($host) ],
            vhosts_declared => [ declared_vhost_names($host) ],
            vhost_aliases_derived => [ derived_vhost_alias_names($host) ],
            roles => [ @{ $host->{roles} || [] } ],
            monitoring => $host->{monitoring} || 'pending',
            notes => $host->{notes} || '',
        };
    }
    return {
        version => $registry->{version},
        generated_at => iso_now(),
        source => $opt{db},
        hosts => \@hosts,
    };
}

sub debug_database_tables_payload {
    my $dbh = dbh();
    my @tables;
    my $sth = $dbh->prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name");
    $sth->execute;
    while (my ($name) = $sth->fetchrow_array) {
        my $quoted = $dbh->quote_identifier($name);
        my ($count) = $dbh->selectrow_array("SELECT COUNT(*) FROM $quoted");
        push @tables, {
            name => $name,
            rows => int($count || 0),
        };
    }
    return {
        database => $opt{db},
        generated_at => iso_now(),
        tables => \@tables,
        counts => {
            tables => scalar @tables,
            rows => sum(map { $_->{rows} } @tables),
        },
    };
}

sub debug_database_table_payload {
    my ($table, $limit) = @_;
    my $dbh = dbh();
    $table = clean_scalar($table);
    return { error => 'missing_table' } unless length $table;
    return { error => 'invalid_table' } unless debug_table_exists($dbh, $table);
    $limit = int($limit || 100);
    $limit = 1 if $limit < 1;
    $limit = 500 if $limit > 500;

    my $quoted = $dbh->quote_identifier($table);
    my $columns = $dbh->selectall_arrayref("PRAGMA table_info($quoted)", { Slice => {} }) || [];
    my $indexes = $dbh->selectall_arrayref("PRAGMA index_list($quoted)", { Slice => {} }) || [];
    my @index_details;
    for my $index (@$indexes) {
        my $index_name = $index->{name} || '';
        next unless length $index_name;
        my $quoted_index = $dbh->quote_identifier($index_name);
        my $index_columns = $dbh->selectall_arrayref("PRAGMA index_info($quoted_index)", { Slice => {} }) || [];
        push @index_details, {
            name => $index_name,
            unique => int($index->{unique} || 0),
            origin => $index->{origin} || '',
            partial => int($index->{partial} || 0),
            columns => [ map { $_->{name} || '' } @$index_columns ],
        };
    }
    my $foreign_keys = $dbh->selectall_arrayref("PRAGMA foreign_key_list($quoted)", { Slice => {} }) || [];
    my ($row_count) = $dbh->selectrow_array("SELECT COUNT(*) FROM $quoted");
    my $rows = $dbh->selectall_arrayref("SELECT * FROM $quoted LIMIT ?", { Slice => {} }, $limit) || [];

    return {
        database => $opt{db},
        table => $table,
        generated_at => iso_now(),
        limit => $limit,
        row_count => int($row_count || 0),
        columns => $columns,
        indexes => \@index_details,
        foreign_keys => $foreign_keys,
        rows => $rows,
    };
}

sub debug_database_table_export_payload {
    my ($table) = @_;
    my $dbh = dbh();
    $table = clean_scalar($table);
    return { error => 'missing_table' } unless length $table;
    return { error => 'invalid_table' } unless debug_table_exists($dbh, $table);

    my $quoted = $dbh->quote_identifier($table);
    my $columns = $dbh->selectall_arrayref("PRAGMA table_info($quoted)", { Slice => {} }) || [];
    my @column_names = map { $_->{name} || '' } @$columns;
    my ($row_count) = $dbh->selectrow_array("SELECT COUNT(*) FROM $quoted");
    my $rows = $dbh->selectall_arrayref("SELECT * FROM $quoted", { Slice => {} }) || [];

    return {
        database => $opt{db},
        table => $table,
        generated_at => iso_now(),
        row_count => int($row_count || 0),
        columns => \@column_names,
        rows => $rows,
    };
}

sub render_debug_table_csv {
    my ($export) = @_;
    my @columns = @{ $export->{columns} || [] };
    my @lines = (join(',', map { csv_cell($_) } @columns));
    for my $row (@{ $export->{rows} || [] }) {
        push @lines, join(',', map { csv_cell($row->{$_}) } @columns);
    }
    return join("\n", @lines) . "\n";
}

sub csv_cell {
    my ($value) = @_;
    $value = '' unless defined $value;
    $value = "$value";
    $value =~ s/"/""/g;
    return qq("$value") if $value =~ /[",\r\n]/;
    return $value;
}

sub debug_table_export_filename {
    my ($table, $extension) = @_;
    $table = clean_scalar($table || 'table');
    $table =~ s/[^A-Za-z0-9_.-]+/-/g;
    $table = 'table' unless length $table;
    return "debug-$table.$extension";
}

sub debug_table_exists {
    my ($dbh, $table) = @_;
    return 0 unless $table =~ /\A[A-Za-z_][A-Za-z0-9_]*\z/;
    my ($exists) = $dbh->selectrow_array(
        "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = ? AND name NOT LIKE 'sqlite_%'",
        undef,
        $table,
    );
    return $exists ? 1 : 0;
}

sub sum {
    my $total = 0;
    $total += $_ || 0 for @_;
    return $total;
}

sub ca_script_path {
    return "$project_dir/scripts/ca_manager.sh";
}

sub ca_dir {
    return $ENV{HOST_MANAGER_CA_DIR} || "$project_dir/var/ca";
}

sub ca_cert_path {
    return ca_dir() . "/certs/ca.cert.pem";
}

sub ca_issued_cert_path {
    my ($name) = @_;
    die "unsafe certificate name\n" unless $name =~ /\A[A-Za-z0-9_.-]+\z/;
    return ca_dir() . "/issued/$name.cert.pem";
}

sub ca_issued_key_path {
    my ($name) = @_;
    die "unsafe certificate name\n" unless $name =~ /\A[A-Za-z0-9_.-]+\z/;
    return ca_dir() . "/issued/$name.key.pem";
}

sub ca_private_key_exists {
    my ($name) = @_;
    return 0 unless clean_certificate_id($name || '');
    return -f ca_issued_key_path($name) ? 1 : 0;
}

sub ca_manager_output {
    my (@args) = @_;
    my $script = ca_script_path();
    die "CA manager script is missing\n" unless -x $script;
    local $ENV{HOST_MANAGER_CA_DIR} = ca_dir();
    open my $fh, '-|', $script, @args or die "Cannot run CA manager\n";
    local $/;
    my $out = <$fh>;
    close $fh or die "CA manager failed\n";
    return $out || '';
}

sub ca_manager_json {
    my ($command) = @_;
    my $out = ca_manager_output($command);
    $out ||= $command eq 'list-json' ? '[]' : '{}';
    sync_certificates_from_json($out) if $command eq 'list-json';
    return $out;
}

sub sync_certificates_from_json {
    my ($json) = @_;
    my $certs = eval { json_decode($json || '[]') };
    return if $@ || ref($certs) ne 'ARRAY';
    my $dbh = dbh();
    my $now = iso_now();
    with_transaction($dbh, sub {
        for my $cert (@$certs) {
            next unless ref($cert) eq 'HASH';
            my $name = clean_id($cert->{name} || $cert->{serial} || $cert->{fingerprint_sha256} || '');
            next unless $name;
            my @dns_names = map { normalize_dns_name($_) } @{ $cert->{dns_names} || [] };
            my $host_fqdn = infer_certificate_host_fqdn($dbh, \@dns_names);
            my $cert_path = ca_issued_cert_path($name);
            my $csr_path = ca_dir() . "/csr/$name.csr.pem";
            my $serial = clean_scalar($cert->{serial} || '');
            my $fingerprint = clean_scalar($cert->{fingerprint_sha256} || '');
            $dbh->do(
                'INSERT INTO certificates (certificate_id, host_fqdn, common_name, subject, issuer, serial, status, not_before, not_after, fingerprint_sha256, cert_path, csr_path, created_at, updated_at, notes) '
                . "VALUES (?, ?, ?, ?, ?, ?, 'issued', ?, ?, ?, ?, ?, ?, ?, '') "
                . 'ON CONFLICT(certificate_id) DO UPDATE SET host_fqdn = excluded.host_fqdn, common_name = excluded.common_name, '
                . 'subject = excluded.subject, issuer = excluded.issuer, serial = excluded.serial, status = excluded.status, '
                . 'not_before = excluded.not_before, not_after = excluded.not_after, fingerprint_sha256 = excluded.fingerprint_sha256, '
                . 'cert_path = excluded.cert_path, csr_path = excluded.csr_path, updated_at = excluded.updated_at',
                undef,
                $name,
                $host_fqdn || undef,
                $dns_names[0] || '',
                clean_scalar($cert->{subject} || ''),
                clean_scalar($cert->{issuer} || ''),
                length($serial) ? $serial : undef,
                clean_scalar($cert->{not_before} || ''),
                clean_scalar($cert->{not_after} || ''),
                length($fingerprint) ? $fingerprint : undef,
                $cert_path,
                $csr_path,
                $now,
                $now,
            );
            $dbh->do('DELETE FROM certificate_dns_names WHERE certificate_id = ?', undef, $name);
            for my $dns_name (@dns_names) {
                next unless length $dns_name;
                $dbh->do(
                    'INSERT OR IGNORE INTO certificate_dns_names (certificate_id, dns_name) VALUES (?, ?)',
                    undef,
                    $name,
                    $dns_name,
                );
            }
        }
    });
}

sub infer_certificate_host_fqdn {
    my ($dbh, $dns_names) = @_;
    for my $name (@$dns_names) {
        my ($fqdn) = $dbh->selectrow_array('SELECT fqdn FROM hosts WHERE fqdn = ?', undef, $name);
        return $fqdn if $fqdn;
    }
    for my $name (@$dns_names) {
        my ($fqdn) = $dbh->selectrow_array('SELECT host_fqdn FROM host_aliases WHERE alias_name = ? AND status = ?', undef, $name, 'active');
        return $fqdn if $fqdn;
        ($fqdn) = $dbh->selectrow_array('SELECT host_fqdn FROM vhosts WHERE vhost_fqdn = ? AND status = ?', undef, $name, 'active');
        return $fqdn if $fqdn;
    }
    return '';
}

sub parse_hosts_yaml {
    my ($text) = @_;
    my %registry = (
        version => 1,
        updated_at => '',
        policy => {},
        hosts => [],
    );
    my ($section, $current, $list_key);
    for my $line (split /\n/, $text) {
        next if $line =~ /^\s*$/ || $line =~ /^\s*#/;
        if ($line =~ /^version:\s*(\d+)/) {
            $registry{version} = int($1);
        } elsif ($line =~ /^updated_at:\s*(.+)$/) {
            $registry{updated_at} = yaml_unquote($1);
        } elsif ($line =~ /^policy:\s*$/) {
            $section = 'policy';
        } elsif ($line =~ /^hosts:\s*$/) {
            $section = 'hosts';
        } elsif (($section || '') eq 'policy' && $line =~ /^  ([A-Za-z0-9_]+):\s*(.+)$/) {
            $registry{policy}{$1} = yaml_unquote($2);
        } elsif (($section || '') eq 'hosts' && $line =~ /^  - id:\s*(.+)$/) {
            $current = {
                id => yaml_unquote($1),
                fqdn => '',
                status => 'active',
                ip => '',
                aliases => [],
                vhosts => [],
                roles => [],
                sources => [],
                monitoring => 'pending',
                notes => '',
            };
            push @{ $registry{hosts} }, $current;
            $list_key = undef;
        } elsif ($current && $line =~ /^    ([A-Za-z0-9_]+):\s*$/) {
            $list_key = $1;
            $current->{$list_key} ||= [];
        } elsif ($current && defined $list_key && $line =~ /^      -\s*(.+)$/) {
            push @{ $current->{$list_key} }, yaml_unquote($1);
        } elsif ($current && $line =~ /^    ([A-Za-z0-9_]+):\s*(.*)$/) {
            my $key = $1;
            my $value = yaml_unquote($2);
            if ($key eq 'ip') {
                $current->{ip} = $value;
            } elsif ($key eq 'dns_ip' || $key eq 'hosts_ip') {
                $current->{ip} ||= $value;
            } elsif ($key eq 'fqdn') {
                $current->{fqdn} = normalize_dns_name($value);
            } elsif ($key eq 'names') {
                # ignored here; legacy list is handled after parsing
            } else {
                $current->{$key} = $value;
            }
            $list_key = undef;
        }
    }
    for my $host (@{ $registry{hosts} }) {
        my @legacy_names = @{ $host->{names} || [] };
        if (@legacy_names) {
            my $legacy = split_legacy_names($host->{id}, \@legacy_names);
            $host->{fqdn} ||= $legacy->{fqdn};
            $host->{aliases} = $legacy->{aliases} unless @{ $host->{aliases} || [] };
            $host->{vhosts} = $legacy->{vhosts} unless @{ $host->{vhosts} || [] };
        }
        delete $host->{names};
        $host->{fqdn} ||= canonical_host_fqdn($host);
    }
    return \%registry;
}

sub render_hosts_yaml {
    my ($registry) = @_;
    my $out = "version: " . int($registry->{version} || 1) . "\n";
    $out .= "updated_at: " . yq($registry->{updated_at} || iso_now()) . "\n";
    $out .= "policy:\n";
    for my $key (sort keys %{ $registry->{policy} || {} }) {
        $out .= "  $key: " . yq($registry->{policy}{$key}) . "\n";
    }
    $out .= "hosts:\n";
    for my $host (sort { $a->{id} cmp $b->{id} } @{ $registry->{hosts} || [] }) {
        $out .= "  - id: " . yq($host->{id}) . "\n";
        $out .= "    fqdn: " . yq(canonical_host_fqdn($host)) . "\n";
        $out .= "    status: " . yq($host->{status} || '') . "\n";
        $out .= "    ip: " . yq(canonical_ip($host)) . "\n";
        for my $key (qw(aliases vhosts roles sources)) {
            $out .= "    $key:\n";
            for my $value (@{ $host->{$key} || [] }) {
                $out .= "      - " . yq($value) . "\n";
            }
        }
        $out .= "    monitoring: " . yq($host->{monitoring} || 'pending') . "\n";
        $out .= "    notes: " . yq($host->{notes} || '') . "\n";
    }
    return $out;
}

sub parse_work_orders_yaml {
    my ($text) = @_;
    my %orders = (
        version => 1,
        work_orders => [],
    );
    my ($section, $current, $list_section, $current_action, $current_item);
    for my $line (split /\n/, $text) {
        next if $line =~ /^\s*$/ || $line =~ /^\s*#/;
        if ($line =~ /^version:\s*(\d+)/) {
            $orders{version} = int($1);
        } elsif ($line =~ /^work_orders:\s*$/) {
            $section = 'work_orders';
        } elsif (($section || '') eq 'work_orders' && $line =~ /^  - id:\s*(.+)$/) {
            $current = {
                id => yaml_unquote($1),
                status => 'pending',
                checklist => [],
                actions => [],
            };
            push @{ $orders{work_orders} }, $current;
            $list_section = '';
            $current_action = undef;
            $current_item = undef;
        } elsif ($current && $line =~ /^    checklist:\s*$/) {
            $list_section = 'checklist';
            $current->{checklist} ||= [];
        } elsif ($current && $list_section eq 'checklist' && $line =~ /^      - id:\s*(.+)$/) {
            $current_item = { id => yaml_unquote($1), status => 'pending' };
            push @{ $current->{checklist} }, $current_item;
            $current_action = undef;
        } elsif ($current_item && $list_section eq 'checklist' && $line =~ /^        ([A-Za-z0-9_]+):\s*(.*)$/) {
            $current_item->{$1} = yaml_unquote($2);
        } elsif ($current && $line =~ /^    actions:\s*$/) {
            $list_section = 'actions';
            $current->{actions} ||= [];
        } elsif ($current && $list_section eq 'actions' && $line =~ /^      - type:\s*(.+)$/) {
            $current_action = { type => yaml_unquote($1) };
            push @{ $current->{actions} }, $current_action;
            $current_item = undef;
        } elsif ($current_action && $list_section eq 'actions' && $line =~ /^        ([A-Za-z0-9_]+):\s*(.*)$/) {
            $current_action->{$1} = yaml_unquote($2);
        } elsif ($current && $line =~ /^    ([A-Za-z0-9_]+):\s*(.*)$/) {
            $current->{$1} = yaml_unquote($2);
            $list_section = '';
            $current_action = undef;
            $current_item = undef;
        }
    }
    return \%orders;
}

sub render_work_orders_yaml {
    my ($orders) = @_;
    my $out = "version: " . int($orders->{version} || 1) . "\n";
    $out .= "work_orders:\n";
    for my $wo (@{ $orders->{work_orders} || [] }) {
        $out .= "  - id: " . yq($wo->{id}) . "\n";
        for my $key (qw(status title reason created_at confirmed_at result)) {
            next unless exists $wo->{$key} && length($wo->{$key} || '');
            $out .= "    $key: " . yq($wo->{$key}) . "\n";
        }
        $out .= "    checklist:\n";
        for my $item (@{ $wo->{checklist} || [] }) {
            $out .= "      - id: " . yq($item->{id}) . "\n";
            for my $key (qw(text status owner notes updated_at)) {
                next unless exists $item->{$key} && length($item->{$key} || '');
                $out .= "        $key: " . yq($item->{$key}) . "\n";
            }
        }
        $out .= "    actions:\n";
        for my $action (@{ $wo->{actions} || [] }) {
            $out .= "      - type: " . yq($action->{type}) . "\n";
            for my $key (qw(host_id name)) {
                next unless exists $action->{$key} && length($action->{$key} || '');
                $out .= "        $key: " . yq($action->{$key}) . "\n";
            }
        }
    }
    return $out;
}

sub request_payload {
    my ($headers, $body) = @_;
    my $type = $headers->{'content-type'} || '';
    if ($type =~ m{application/json}) {
        return json_decode($body || '{}');
    }
    return { parse_params($body || '') };
}

sub json_bool {
    my ($value) = @_;
    return bless \(my $bool = $value ? 1 : 0), 'HostManager::JSONBool';
}

sub json_encode {
    my ($value) = @_;
    if (!defined $value) {
        return 'null';
    }
    my $ref = ref($value);
    if (!$ref) {
        return $value if $value =~ /\A-?(?:0|[1-9][0-9]*)(?:\.[0-9]+)?\z/;
        return json_string($value);
    }
    if ($ref eq 'HostManager::JSONBool') {
        return $$value ? 'true' : 'false';
    }
    if ($ref eq 'ARRAY') {
        return '[' . join(',', map { json_encode($_) } @$value) . ']';
    }
    if ($ref eq 'HASH') {
        return '{' . join(',', map { json_string($_) . ':' . json_encode($value->{$_}) } sort keys %$value) . '}';
    }
    return json_string("$value");
}

sub json_string {
    my ($value) = @_;
    $value = '' unless defined $value;
    $value =~ s/\\/\\\\/g;
    $value =~ s/"/\\"/g;
    $value =~ s/\n/\\n/g;
    $value =~ s/\r/\\r/g;
    $value =~ s/\t/\\t/g;
    $value =~ s/([\x00-\x1f])/sprintf("\\u%04x", ord($1))/eg;
    return qq("$value");
}

sub json_decode {
    my ($text) = @_;
    my $i = 0;
    my $len = length($text);
    my ($parse_value, $parse_string, $parse_array, $parse_object, $parse_number, $skip_ws);

    $skip_ws = sub {
        $i++ while $i < $len && substr($text, $i, 1) =~ /\s/;
    };

    $parse_string = sub {
        die "Expected JSON string\n" unless substr($text, $i, 1) eq '"';
        $i++;
        my $out = '';
        while ($i < $len) {
            my $ch = substr($text, $i++, 1);
            return $out if $ch eq '"';
            if ($ch eq "\\") {
                die "Bad JSON escape\n" if $i >= $len;
                my $esc = substr($text, $i++, 1);
                if ($esc eq '"' || $esc eq "\\" || $esc eq '/') {
                    $out .= $esc;
                } elsif ($esc eq 'b') {
                    $out .= "\b";
                } elsif ($esc eq 'f') {
                    $out .= "\f";
                } elsif ($esc eq 'n') {
                    $out .= "\n";
                } elsif ($esc eq 'r') {
                    $out .= "\r";
                } elsif ($esc eq 't') {
                    $out .= "\t";
                } elsif ($esc eq 'u') {
                    my $hex = substr($text, $i, 4);
                    die "Bad JSON unicode escape\n" unless $hex =~ /\A[0-9A-Fa-f]{4}\z/;
                    $out .= chr(hex($hex));
                    $i += 4;
                } else {
                    die "Bad JSON escape\n";
                }
            } else {
                $out .= $ch;
            }
        }
        die "Unterminated JSON string\n";
    };

    $parse_number = sub {
        my $start = $i;
        $i++ if substr($text, $i, 1) eq '-';
        $i++ while $i < $len && substr($text, $i, 1) =~ /[0-9]/;
        if ($i < $len && substr($text, $i, 1) eq '.') {
            $i++;
            $i++ while $i < $len && substr($text, $i, 1) =~ /[0-9]/;
        }
        if ($i < $len && substr($text, $i, 1) =~ /[eE]/) {
            $i++;
            $i++ if $i < $len && substr($text, $i, 1) =~ /[+-]/;
            $i++ while $i < $len && substr($text, $i, 1) =~ /[0-9]/;
        }
        return 0 + substr($text, $start, $i - $start);
    };

    $parse_array = sub {
        die "Expected JSON array\n" unless substr($text, $i, 1) eq '[';
        $i++;
        my @out;
        $skip_ws->();
        if ($i < $len && substr($text, $i, 1) eq ']') {
            $i++;
            return \@out;
        }
        while (1) {
            push @out, $parse_value->();
            $skip_ws->();
            my $ch = substr($text, $i++, 1);
            last if $ch eq ']';
            die "Expected JSON array comma\n" unless $ch eq ',';
        }
        return \@out;
    };

    $parse_object = sub {
        die "Expected JSON object\n" unless substr($text, $i, 1) eq '{';
        $i++;
        my %out;
        $skip_ws->();
        if ($i < $len && substr($text, $i, 1) eq '}') {
            $i++;
            return \%out;
        }
        while (1) {
            $skip_ws->();
            my $key = $parse_string->();
            $skip_ws->();
            die "Expected JSON object colon\n" unless substr($text, $i++, 1) eq ':';
            $out{$key} = $parse_value->();
            $skip_ws->();
            my $ch = substr($text, $i++, 1);
            last if $ch eq '}';
            die "Expected JSON object comma\n" unless $ch eq ',';
        }
        return \%out;
    };

    $parse_value = sub {
        $skip_ws->();
        die "Unexpected end of JSON\n" if $i >= $len;
        my $ch = substr($text, $i, 1);
        return $parse_string->() if $ch eq '"';
        return $parse_object->() if $ch eq '{';
        return $parse_array->() if $ch eq '[';
        if (substr($text, $i, 4) eq 'true') {
            $i += 4;
            return json_bool(1);
        }
        if (substr($text, $i, 5) eq 'false') {
            $i += 5;
            return json_bool(0);
        }
        if (substr($text, $i, 4) eq 'null') {
            $i += 4;
            return undef;
        }
        return $parse_number->() if $ch =~ /[-0-9]/;
        die "Unexpected JSON token\n";
    };

    my $value = $parse_value->();
    $skip_ws->();
    die "Trailing JSON content\n" if $i != $len;
    return $value;
}

sub parse_params {
    my ($text) = @_;
    my %out;
    for my $pair (split /&/, $text) {
        next unless length $pair;
        my ($k, $v) = split /=/, $pair, 2;
        $out{url_decode($k)} = url_decode($v || '');
    }
    return %out;
}

sub clean_id {
    my ($value) = @_;
    $value = lc clean_scalar($value);
    $value =~ s/[^a-z0-9_.-]+/-/g;
    $value =~ s/^-+|-+$//g;
    return $value;
}

sub clean_certificate_id {
    my ($value) = @_;
    $value = clean_scalar($value);
    return '' unless length $value;
    return $value =~ /\A[A-Za-z0-9_.-]+\z/ ? $value : '';
}

sub clean_ip {
    my ($value) = @_;
    $value = clean_scalar($value);
    return $value if $value =~ /\A(?:25[0-5]|2[0-4]\d|1?\d?\d)(?:\.(?:25[0-5]|2[0-4]\d|1?\d?\d)){3}\z/;
    return '';
}

sub clean_mac {
    my ($value) = @_;
    $value = lc clean_scalar($value);
    $value =~ s/-/:/g;
    return $value if $value =~ /\A[0-9a-f]{2}(?::[0-9a-f]{2}){5}\z/;
    return '';
}

sub normalize_dhcp_name {
    my ($value) = @_;
    $value = normalize_dns_name($value || '');
    $value =~ s/[^a-z0-9_.-]+/-/g;
    $value =~ s/^-+|-+$//g;
    return $value;
}

sub clean_scalar {
    my ($value) = @_;
    $value = '' unless defined $value;
    $value =~ s/[\r\n\t]+/ /g;
    $value =~ s/^\s+|\s+$//g;
    return $value;
}

sub clean_list {
    my ($value) = @_;
    return () unless defined $value;
    my @items = ref($value) eq 'ARRAY' ? @$value : split /[\s,]+/, $value;
    my @clean;
    for my $item (@items) {
        $item = clean_scalar($item);
        push @clean, $item if length $item;
    }
    return @clean;
}

sub yq {
    my ($value) = @_;
    $value = '' unless defined $value;
    $value =~ s/\\/\\\\/g;
    $value =~ s/"/\\"/g;
    return qq("$value");
}

sub yaml_unquote {
    my ($value) = @_;
    $value = '' unless defined $value;
    $value =~ s/^\s+|\s+$//g;
    if ($value =~ /^"(.*)"$/) {
        $value = $1;
        $value =~ s/\\"/"/g;
        $value =~ s/\\\\/\\/g;
    }
    return $value;
}

sub verify_totp {
    my ($secret, $otp) = @_;
    return 0 unless $secret && $otp =~ /^\d{6}$/;
    my $key = eval { base32_decode($secret) };
    return 0 if $@ || !length $key;
    my $counter = int(time() / 30);
    for my $offset (-1, 0, 1) {
        return 1 if totp_code($key, $counter + $offset) eq $otp;
    }
    return 0;
}

sub totp_code {
    my ($key, $counter) = @_;
    my $msg = pack('NN', int($counter / 4294967296), $counter & 0xffffffff);
    my $hash = hmac_sha1($msg, $key);
    my $offset = ord(substr($hash, -1)) & 0x0f;
    my $bin = unpack('N', substr($hash, $offset, 4)) & 0x7fffffff;
    return sprintf('%06d', $bin % 1_000_000);
}

sub base32_decode {
    my ($text) = @_;
    $text = uc($text || '');
    $text =~ s/[^A-Z2-7]//g;
    my %map;
    my @chars = ('A'..'Z', '2'..'7');
    @map{@chars} = (0..31);
    my ($bits, $value, $out) = (0, 0, '');
    for my $char (split //, $text) {
        die "Invalid base32\n" unless exists $map{$char};
        $value = ($value << 5) | $map{$char};
        $bits += 5;
        while ($bits >= 8) {
            $bits -= 8;
            $out .= chr(($value >> $bits) & 0xff);
        }
    }
    return $out;
}

sub create_session {
    my $nonce = random_hex(24);
    my $expires = int(time() + 8 * 3600);
    my $sig = hmac_sha256_hex("$nonce:$expires", $session_secret);
    my $token = "$nonce:$expires:$sig";
    $sessions{$token} = $expires;
    return $token;
}

sub is_authenticated {
    my ($headers) = @_;
    my $token = cookie_value($headers->{'cookie'} || '', 'hm_session');
    return 0 unless $token;
    my ($nonce, $expires, $sig) = split /:/, $token;
    return 0 unless $nonce && $expires && $sig;
    return 0 if $expires < time();
    return 0 unless hmac_sha256_hex("$nonce:$expires", $session_secret) eq $sig;
    return exists $sessions{$token};
}

sub expire_session {
    my ($headers) = @_;
    my $token = cookie_value($headers->{'cookie'} || '', 'hm_session');
    delete $sessions{$token} if $token;
}

sub cookie_value {
    my ($cookie, $name) = @_;
    for my $part (split /;\s*/, $cookie) {
        my ($k, $v) = split /=/, $part, 2;
        return $v if defined $k && $k eq $name;
    }
    return '';
}

sub send_json {
    my ($client, $status, $payload, $extra_headers) = @_;
    return send_response($client, $status, json_encode($payload), 'application/json; charset=utf-8', $extra_headers);
}

sub send_json_raw {
    my ($client, $status, $json_body, $extra_headers) = @_;
    return send_response($client, $status, $json_body, 'application/json; charset=utf-8', $extra_headers);
}

sub send_html {
    my ($client, $status, $html) = @_;
    return send_response($client, $status, $html, 'text/html; charset=utf-8');
}

sub send_text {
    my ($client, $status, $text) = @_;
    return send_response($client, $status, $text, 'text/plain; charset=utf-8');
}

sub send_download {
    my ($client, $status, $content, $type, $filename) = @_;
    return send_response($client, $status, $content, $type, [ qq(Content-Disposition: attachment; filename="$filename") ]);
}

sub send_file {
    my ($client, $path, $type, $filename) = @_;
    return send_json($client, 404, { error => 'missing_file' }) unless -f $path;
    return send_download($client, 200, read_file($path), $type, $filename);
}

sub send_response {
    my ($client, $status, $body, $type, $extra_headers) = @_;
    my %reason = (200 => 'OK', 400 => 'Bad Request', 401 => 'Unauthorized', 404 => 'Not Found', 409 => 'Conflict', 500 => 'Internal Server Error', 503 => 'Service Unavailable');
    $body = '' unless defined $body;
    print $client "HTTP/1.1 $status " . ($reason{$status} || 'OK') . "\r\n";
    print $client "Content-Type: $type\r\n";
    print $client "Content-Length: " . length($body) . "\r\n";
    print $client "Cache-Control: no-store\r\n";
    print $client "$_\r\n" for @{ $extra_headers || [] };
    print $client "Connection: close\r\n\r\n";
    print $client $body;
}

sub read_file {
    my ($path) = @_;
    open my $fh, '<', $path or die "Cannot read $path: $!";
    local $/;
    return <$fh>;
}

sub write_file {
    my ($path, $content) = @_;
    open my $fh, '>', $path or die "Cannot write $path: $!";
    print {$fh} $content;
    close $fh or die "Cannot close $path: $!";
}

sub backup_file {
    my ($path) = @_;
    return unless -f $path;
    my $backup_dir = "$project_dir/backups/host-manager";
    make_path($backup_dir) unless -d $backup_dir;
    my $name = $path;
    $name =~ s{.*/}{};
    my $stamp = strftime('%Y%m%d_%H%M%S', localtime);
    write_file("$backup_dir/$name.$stamp.bak", read_file($path));
}

sub publish_dns_change {
    my ($registry, $reason) = @_;
    $reason = clean_scalar($reason || 'registry-change');

    backup_file($opt{local_hosts_tsv});
    write_file($opt{local_hosts_tsv}, render_local_hosts_tsv($registry));

    my $trigger = $opt{dns_publish_trigger} || '';
    return {
        queued => json_bool(0),
        file => $opt{local_hosts_tsv},
        reason => $reason,
    } unless length $trigger;

    ensure_parent_dir($trigger);
    open my $fh, '>>', $trigger or die "Cannot write DNS publish trigger $trigger: $!";
    print {$fh} iso_now() . "\t$reason\n";
    close $fh or die "Cannot close DNS publish trigger $trigger: $!";

    return {
        queued => json_bool(1),
        file => $opt{local_hosts_tsv},
        trigger => $trigger,
        reason => $reason,
    };
}

my $db_handle;
my $db_seeded = 0;

sub dbh {
    return $db_handle if $db_handle;
    ensure_parent_dir($opt{db});
    $db_handle = DBI->connect(
        "dbi:SQLite:dbname=$opt{db}",
        '',
        '',
        {
            RaiseError => 1,
            PrintError => 0,
            AutoCommit => 1,
            sqlite_unicode => 1,
        },
    ) or die "Cannot open SQLite database $opt{db}\n";
    $db_handle->do('PRAGMA journal_mode = WAL');
    $db_handle->do('PRAGMA foreign_keys = ON');
    create_database_schema($db_handle);
    seed_database($db_handle) unless $db_seeded++;
    return $db_handle;
}

sub create_database_schema {
    my ($dbh) = @_;
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS schema_meta (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at TEXT NOT NULL
)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS documents (
    name TEXT PRIMARY KEY,
    content TEXT NOT NULL,
    updated_at TEXT NOT NULL
)
SQL
    $dbh->do(
        'INSERT INTO schema_meta (key, value, updated_at) VALUES (?, ?, ?) '
        . 'ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at',
        undef, 'schema_version', '2', iso_now()
    );
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS hosts (
    fqdn TEXT PRIMARY KEY,
    legacy_id TEXT NOT NULL UNIQUE,
    status TEXT NOT NULL DEFAULT 'active',
    hosts_ip TEXT NOT NULL DEFAULT '',
    dns_ip TEXT NOT NULL DEFAULT '',
    monitoring TEXT NOT NULL DEFAULT 'pending',
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS host_aliases (
    alias_name TEXT NOT NULL,
    host_fqdn TEXT NOT NULL,
    alias_kind TEXT NOT NULL DEFAULT 'declared',
    status TEXT NOT NULL DEFAULT 'active',
    is_dns_published INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    retired_at TEXT,
    notes TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (alias_name, host_fqdn),
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE RESTRICT
)
SQL
    $dbh->do(<<'SQL');
CREATE UNIQUE INDEX IF NOT EXISTS idx_host_aliases_active_name
ON host_aliases(alias_name)
WHERE status = 'active'
SQL
    $dbh->do(<<'SQL');
CREATE INDEX IF NOT EXISTS idx_host_aliases_host_status
ON host_aliases(host_fqdn, status)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS host_roles (
    host_fqdn TEXT NOT NULL,
    role TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    created_at TEXT NOT NULL,
    retired_at TEXT,
    PRIMARY KEY (host_fqdn, role),
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE RESTRICT
)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS host_sources (
    host_fqdn TEXT NOT NULL,
    source TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    created_at TEXT NOT NULL,
    retired_at TEXT,
    PRIMARY KEY (host_fqdn, source),
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE RESTRICT
)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS host_flags (
    host_fqdn TEXT NOT NULL,
    flag TEXT NOT NULL,
    value TEXT NOT NULL DEFAULT '1',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    PRIMARY KEY (host_fqdn, flag),
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE RESTRICT
)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS host_ssh (
    host_fqdn TEXT NOT NULL,
    profile_name TEXT NOT NULL DEFAULT 'default',
    username TEXT NOT NULL DEFAULT '',
    port INTEGER NOT NULL DEFAULT 22,
    identity_file TEXT NOT NULL DEFAULT '',
    address TEXT NOT NULL DEFAULT '',
    local_forward_host TEXT NOT NULL DEFAULT '',
    local_forward_port INTEGER,
    remote_forward_host TEXT NOT NULL DEFAULT '',
    remote_forward_port INTEGER,
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    PRIMARY KEY (host_fqdn, profile_name),
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE RESTRICT
)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS host_tls (
    host_fqdn TEXT PRIMARY KEY,
    tls_mode TEXT NOT NULL DEFAULT 'local-ca',
    certificate_id TEXT,
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (certificate_id) REFERENCES certificates(certificate_id) ON UPDATE CASCADE ON DELETE SET NULL
)
SQL
    $dbh->do(<<'SQL');
CREATE INDEX IF NOT EXISTS idx_host_tls_certificate
ON host_tls(certificate_id)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS certificates (
    certificate_id TEXT PRIMARY KEY,
    host_fqdn TEXT,
    common_name TEXT NOT NULL DEFAULT '',
    subject TEXT NOT NULL DEFAULT '',
    issuer TEXT NOT NULL DEFAULT '',
    serial TEXT UNIQUE,
    status TEXT NOT NULL DEFAULT 'issued',
    not_before TEXT NOT NULL DEFAULT '',
    not_after TEXT NOT NULL DEFAULT '',
    fingerprint_sha256 TEXT UNIQUE,
    cert_path TEXT NOT NULL DEFAULT '',
    csr_path TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    notes TEXT NOT NULL DEFAULT '',
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE SET NULL
)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS certificate_dns_names (
    certificate_id TEXT NOT NULL,
    dns_name TEXT NOT NULL,
    PRIMARY KEY (certificate_id, dns_name),
    FOREIGN KEY (certificate_id) REFERENCES certificates(certificate_id) ON UPDATE CASCADE ON DELETE CASCADE
)
SQL
    $dbh->do(<<'SQL');
CREATE INDEX IF NOT EXISTS idx_certificate_dns_names_dns_name
ON certificate_dns_names(dns_name)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS vhosts (
    vhost_fqdn TEXT PRIMARY KEY,
    host_fqdn TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    service_name TEXT NOT NULL DEFAULT '',
    upstream_url TEXT NOT NULL DEFAULT '',
    tls_mode TEXT NOT NULL DEFAULT 'local-ca',
    certificate_id TEXT,
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (certificate_id) REFERENCES certificates(certificate_id) ON UPDATE CASCADE ON DELETE SET NULL
)
SQL
    $dbh->do(<<'SQL');
CREATE INDEX IF NOT EXISTS idx_vhosts_host_status
ON vhosts(host_fqdn, status)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS data_workers (
    worker_id TEXT PRIMARY KEY,
    worker_type TEXT NOT NULL,
    name TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'active',
    source TEXT NOT NULL DEFAULT '',
    last_run_at TEXT,
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL
)
SQL
    $dbh->do(<<'SQL');
CREATE INDEX IF NOT EXISTS idx_data_workers_type_status
ON data_workers(worker_type, status)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS dhcp_leases (
    lease_key TEXT PRIMARY KEY,
    worker_id TEXT NOT NULL,
    host_fqdn TEXT,
    observed_name TEXT NOT NULL DEFAULT '',
    ip_address TEXT NOT NULL,
    mac_address TEXT NOT NULL DEFAULT '',
    lease_state TEXT NOT NULL DEFAULT '',
    first_seen TEXT NOT NULL,
    last_seen TEXT NOT NULL,
    raw TEXT NOT NULL DEFAULT '',
    FOREIGN KEY (worker_id) REFERENCES data_workers(worker_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE SET NULL
)
SQL
    $dbh->do('CREATE INDEX IF NOT EXISTS idx_dhcp_leases_ip ON dhcp_leases(ip_address)');
    $dbh->do('CREATE INDEX IF NOT EXISTS idx_dhcp_leases_mac ON dhcp_leases(mac_address)');
    $dbh->do('CREATE INDEX IF NOT EXISTS idx_dhcp_leases_worker_last_seen ON dhcp_leases(worker_id, last_seen)');
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS mdns_observations (
    observation_key TEXT PRIMARY KEY,
    worker_id TEXT NOT NULL,
    host_fqdn TEXT,
    observed_name TEXT NOT NULL,
    ip_address TEXT NOT NULL,
    rr_type TEXT NOT NULL DEFAULT 'A',
    ttl INTEGER NOT NULL DEFAULT 0,
    first_seen TEXT NOT NULL,
    last_seen TEXT NOT NULL,
    seen_count INTEGER NOT NULL DEFAULT 1,
    last_peer TEXT NOT NULL DEFAULT '',
    raw TEXT NOT NULL DEFAULT '',
    FOREIGN KEY (worker_id) REFERENCES data_workers(worker_id) ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE SET NULL
)
SQL
    $dbh->do('CREATE INDEX IF NOT EXISTS idx_mdns_observations_name ON mdns_observations(observed_name)');
    $dbh->do('CREATE INDEX IF NOT EXISTS idx_mdns_observations_ip ON mdns_observations(ip_address)');
    $dbh->do('CREATE INDEX IF NOT EXISTS idx_mdns_observations_worker_last_seen ON mdns_observations(worker_id, last_seen)');
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS work_orders (
    id TEXT PRIMARY KEY,
    status TEXT NOT NULL DEFAULT 'pending',
    title TEXT NOT NULL DEFAULT '',
    reason TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL,
    confirmed_at TEXT NOT NULL DEFAULT '',
    result TEXT NOT NULL DEFAULT '',
    updated_at TEXT NOT NULL
)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS work_order_checklist (
    work_order_id TEXT NOT NULL,
    item_id TEXT NOT NULL,
    text TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'pending',
    owner TEXT NOT NULL DEFAULT '',
    notes TEXT NOT NULL DEFAULT '',
    updated_at TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (work_order_id, item_id),
    FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON UPDATE CASCADE ON DELETE CASCADE
)
SQL
    $dbh->do(<<'SQL');
CREATE TABLE IF NOT EXISTS work_order_actions (
    work_order_id TEXT NOT NULL,
    position INTEGER NOT NULL,
    type TEXT NOT NULL,
    host_fqdn TEXT,
    host_legacy_id TEXT NOT NULL DEFAULT '',
    name TEXT NOT NULL DEFAULT '',
    payload TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (work_order_id, position),
    FOREIGN KEY (work_order_id) REFERENCES work_orders(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (host_fqdn) REFERENCES hosts(fqdn) ON UPDATE CASCADE ON DELETE SET NULL
)
SQL
}

sub seed_database {
    my ($dbh) = @_;
    seed_default_workers($dbh);

    if (!db_scalar($dbh, 'SELECT COUNT(*) FROM hosts')) {
        my $registry = parse_hosts_yaml(legacy_document_text($dbh, 'hosts_yaml', $opt{data}, default_hosts_yaml()));
        normalize_registry_policy($registry);
        with_transaction($dbh, sub {
            import_registry_to_db($dbh, $registry, 0);
        });
    }

    if (!db_scalar($dbh, 'SELECT COUNT(*) FROM work_orders')) {
        my $orders = parse_work_orders_yaml(legacy_document_text($dbh, 'work_orders_yaml', $opt{work_orders}, default_work_orders_yaml()));
        with_transaction($dbh, sub {
            import_work_orders_to_db($dbh, $orders);
        });
    }

    seed_mdns_observations_from_yaml($dbh);
}

sub with_transaction {
    my ($dbh, $code) = @_;
    return $code->() unless $dbh->{AutoCommit};
    $dbh->begin_work;
    my $ok = eval {
        $code->();
        1;
    };
    if (!$ok) {
        my $err = $@ || 'transaction failed';
        eval { $dbh->rollback };
        die $err;
    }
    $dbh->commit;
}

sub db_scalar {
    my ($dbh, $sql, @bind) = @_;
    my ($value) = $dbh->selectrow_array($sql, undef, @bind);
    return $value || 0;
}

sub legacy_document_text {
    my ($dbh, $name, $seed_path, $default_text) = @_;
    my $row = $dbh->selectrow_hashref('SELECT content FROM documents WHERE name = ?', undef, $name);
    return $row->{content} if $row && defined $row->{content};
    return read_file($seed_path) if -f $seed_path;
    return $default_text;
}

sub load_registry_from_db {
    my $dbh = dbh();
    my $registry = {
        version => 1,
        updated_at => db_scalar($dbh, 'SELECT value FROM schema_meta WHERE key = ?', 'registry_updated_at') || '',
        policy => {},
        hosts => [],
    };

    my $sth = $dbh->prepare('SELECT * FROM hosts ORDER BY legacy_id');
    $sth->execute;
    while (my $row = $sth->fetchrow_hashref) {
        my $fqdn = $row->{fqdn};
        push @{ $registry->{hosts} }, {
            id => $row->{legacy_id},
            fqdn => $fqdn,
            status => $row->{status},
            ip => canonical_ip($row),
            aliases => [ active_aliases_for_host($dbh, $fqdn) ],
            vhosts => [ active_vhosts_for_host($dbh, $fqdn) ],
            roles => [ active_values_for_host($dbh, 'host_roles', 'role', $fqdn) ],
            sources => [ active_values_for_host($dbh, 'host_sources', 'source', $fqdn) ],
            monitoring => $row->{monitoring},
            notes => $row->{notes},
        };
    }

    return $registry;
}

sub save_registry_to_db {
    my ($registry) = @_;
    my $dbh = dbh();
    with_transaction($dbh, sub {
        import_registry_to_db($dbh, $registry, 1);
        set_schema_meta($dbh, 'registry_updated_at', $registry->{updated_at} || iso_now());
    });
}

sub import_registry_to_db {
    my ($dbh, $registry, $retire_missing) = @_;
    my %seen;
    for my $host (@{ $registry->{hosts} || [] }) {
        my $fqdn = upsert_host_to_db($dbh, $host);
        $seen{$fqdn} = 1 if $fqdn;
    }

    return unless $retire_missing;
    my $sth = $dbh->prepare('SELECT fqdn FROM hosts WHERE status <> ?');
    $sth->execute('retired');
    while (my ($fqdn) = $sth->fetchrow_array) {
        next if $seen{$fqdn};
        retire_host_in_db($dbh, $fqdn);
    }
}

sub upsert_host_to_db {
    my ($dbh, $host) = @_;
    my $now = iso_now();
    my $fqdn = canonical_host_fqdn($host);
    return '' unless $fqdn;
    my $legacy_id = clean_id($host->{id} || legacy_id_from_fqdn($fqdn));
    my $status = clean_scalar($host->{status} || 'active');
    my $ip = canonical_ip($host);
    my $monitoring = clean_scalar($host->{monitoring} || 'pending');
    my $notes = clean_scalar($host->{notes} || '');

    $dbh->do(
        'INSERT INTO hosts (fqdn, legacy_id, status, hosts_ip, dns_ip, monitoring, notes, created_at, updated_at) '
        . 'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) '
        . 'ON CONFLICT(fqdn) DO UPDATE SET legacy_id = excluded.legacy_id, status = excluded.status, '
        . 'hosts_ip = excluded.hosts_ip, dns_ip = excluded.dns_ip, monitoring = excluded.monitoring, '
        . 'notes = excluded.notes, updated_at = excluded.updated_at',
        undef,
        $fqdn, $legacy_id, $status, $ip, $ip, $monitoring, $notes, $now, $now,
    );

    sync_host_values($dbh, 'host_roles', 'role', $fqdn, [ clean_list($host->{roles}) ]);
    sync_host_values($dbh, 'host_sources', 'source', $fqdn, [ clean_list($host->{sources}) ]);
    sync_host_aliases_and_vhosts($dbh, $fqdn, [ declared_alias_names($host) ], [ declared_vhost_names($host) ]);
    return $fqdn;
}

sub upsert_host_tls_row {
    my ($dbh, $host_fqdn, $certificate_id, $now) = @_;
    $certificate_id = clean_certificate_id($certificate_id || '');
    $dbh->do(
        'INSERT INTO host_tls (host_fqdn, tls_mode, certificate_id, notes, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?) '
        . 'ON CONFLICT(host_fqdn) DO UPDATE SET tls_mode = excluded.tls_mode, certificate_id = excluded.certificate_id, updated_at = excluded.updated_at',
        undef,
        $host_fqdn,
        length($certificate_id) ? 'local-ca' : 'none',
        length($certificate_id) ? $certificate_id : undef,
        '',
        $now,
        $now,
    );
}

sub sync_host_values {
    my ($dbh, $table, $column, $fqdn, $values) = @_;
    my $now = iso_now();
    my %active = map { $_ => 1 } @$values;
    for my $value (@$values) {
        $dbh->do(
            "INSERT INTO $table (host_fqdn, $column, status, created_at, retired_at) VALUES (?, ?, 'active', ?, '') "
            . "ON CONFLICT(host_fqdn, $column) DO UPDATE SET status = 'active', retired_at = ''",
            undef,
            $fqdn, $value, $now,
        );
    }

    my $sth = $dbh->prepare("SELECT $column FROM $table WHERE host_fqdn = ? AND status = 'active'");
    $sth->execute($fqdn);
    while (my ($value) = $sth->fetchrow_array) {
        next if $active{$value};
        $dbh->do("UPDATE $table SET status = 'retired', retired_at = ? WHERE host_fqdn = ? AND $column = ?", undef, $now, $fqdn, $value);
    }
}

sub sync_host_aliases_and_vhosts {
    my ($dbh, $fqdn, $aliases_in, $vhosts_in) = @_;
    my $now = iso_now();
    my (%aliases, %vhosts);
    if (my $short = short_alias_for_fqdn($fqdn)) {
        $aliases{$short} = 1;
        upsert_alias_to_db($dbh, $fqdn, $short, 'derived', $now);
    }
    for my $name (@$aliases_in) {
        $name = normalize_dns_name($name);
        next unless length $name;
        next if $name eq $fqdn;
        $aliases{$name} = 1;
        upsert_alias_to_db($dbh, $fqdn, $name, 'declared', $now);
        if (my $short = short_alias_for_fqdn($name)) {
            $aliases{$short} = 1;
            upsert_alias_to_db($dbh, $fqdn, $short, 'derived', $now);
        }
    }
    for my $name (@$vhosts_in) {
        $name = normalize_dns_name($name);
        next unless length $name;
        $vhosts{$name} = 1;
        upsert_vhost_to_db($dbh, $fqdn, $name, $now);
        if (my $short = short_alias_for_fqdn($name)) {
            $aliases{$short} = 1;
            upsert_alias_to_db($dbh, $fqdn, $short, 'derived-vhost', $now);
        }
    }

    retire_missing_names($dbh, 'host_aliases', 'alias_name', $fqdn, \%aliases, $now);
    retire_missing_names($dbh, 'vhosts', 'vhost_fqdn', $fqdn, \%vhosts, $now);
}

sub upsert_alias_to_db {
    my ($dbh, $fqdn, $alias, $kind, $now) = @_;
    my ($existing_fqdn) = $dbh->selectrow_array(
        "SELECT host_fqdn FROM host_aliases WHERE alias_name = ? AND status = 'active'",
        undef,
        $alias,
    );
    if ($existing_fqdn && $existing_fqdn ne $fqdn) {
        if ($kind eq 'derived-vhost') {
            $dbh->do(
                "UPDATE host_aliases SET status = 'retired', is_dns_published = 0, retired_at = ? WHERE alias_name = ? AND host_fqdn = ? AND status = 'active'",
                undef,
                $now, $alias, $existing_fqdn,
            );
        } else {
            die "alias_conflict: $alias is already active on $existing_fqdn\n";
        }
    }
    $dbh->do(
        'INSERT INTO host_aliases (alias_name, host_fqdn, alias_kind, status, is_dns_published, created_at, retired_at, notes) '
        . "VALUES (?, ?, ?, 'active', 1, ?, '', '') "
        . "ON CONFLICT(alias_name, host_fqdn) DO UPDATE SET alias_kind = excluded.alias_kind, status = 'active', is_dns_published = 1, retired_at = ''",
        undef,
        $alias, $fqdn, $kind, $now,
    );
}

sub upsert_vhost_to_db {
    my ($dbh, $fqdn, $vhost, $now) = @_;
    my $service = vhost_service_name($vhost);
    $dbh->do(
        'INSERT INTO vhosts (vhost_fqdn, host_fqdn, status, service_name, upstream_url, tls_mode, certificate_id, notes, created_at, updated_at) '
        . "VALUES (?, ?, 'active', ?, '', 'local-ca', NULL, '', ?, ?) "
        . "ON CONFLICT(vhost_fqdn) DO UPDATE SET host_fqdn = excluded.host_fqdn, status = 'active', "
        . 'service_name = excluded.service_name, updated_at = excluded.updated_at',
        undef,
        $vhost, $fqdn, $service, $now, $now,
    );
}

sub retire_missing_names {
    my ($dbh, $table, $name_column, $fqdn, $active, $now) = @_;
    my $sth = $dbh->prepare("SELECT $name_column FROM $table WHERE host_fqdn = ? AND status = 'active'");
    $sth->execute($fqdn);
    while (my ($name) = $sth->fetchrow_array) {
        next if $active->{$name};
        if ($table eq 'host_aliases') {
            $dbh->do(
                "UPDATE host_aliases SET status = 'retired', is_dns_published = 0, retired_at = ? WHERE host_fqdn = ? AND alias_name = ?",
                undef, $now, $fqdn, $name,
            );
        } else {
            $dbh->do(
                "UPDATE vhosts SET status = 'retired', updated_at = ? WHERE host_fqdn = ? AND vhost_fqdn = ?",
                undef, $now, $fqdn, $name,
            );
        }
    }
}

sub retire_host_in_db {
    my ($dbh, $fqdn) = @_;
    my $now = iso_now();
    $dbh->do("UPDATE hosts SET status = 'retired', updated_at = ? WHERE fqdn = ?", undef, $now, $fqdn);
    $dbh->do("UPDATE host_aliases SET status = 'retired', is_dns_published = 0, retired_at = ? WHERE host_fqdn = ? AND status = 'active'", undef, $now, $fqdn);
    $dbh->do("UPDATE vhosts SET status = 'retired', updated_at = ? WHERE host_fqdn = ? AND status = 'active'", undef, $now, $fqdn);
    $dbh->do("UPDATE host_roles SET status = 'retired', retired_at = ? WHERE host_fqdn = ? AND status = 'active'", undef, $now, $fqdn);
    $dbh->do("UPDATE host_sources SET status = 'retired', retired_at = ? WHERE host_fqdn = ? AND status = 'active'", undef, $now, $fqdn);
}

sub active_aliases_for_host {
    my ($dbh, $fqdn) = @_;
    my @names;
    my $aliases = $dbh->prepare("SELECT alias_name FROM host_aliases WHERE host_fqdn = ? AND status = 'active' AND is_dns_published = 1 AND alias_kind NOT LIKE 'derived%' ORDER BY alias_name");
    $aliases->execute($fqdn);
    while (my ($name) = $aliases->fetchrow_array) {
        push @names, $name;
    }
    return unique_preserve(@names);
}

sub active_vhosts_for_host {
    my ($dbh, $fqdn) = @_;
    my @names;
    my $vhosts = $dbh->prepare("SELECT vhost_fqdn FROM vhosts WHERE host_fqdn = ? AND status = 'active' ORDER BY vhost_fqdn");
    $vhosts->execute($fqdn);
    while (my ($name) = $vhosts->fetchrow_array) {
        push @names, $name;
    }
    return unique_preserve(@names);
}

sub active_values_for_host {
    my ($dbh, $table, $column, $fqdn) = @_;
    my @values;
    my $sth = $dbh->prepare("SELECT $column FROM $table WHERE host_fqdn = ? AND status = 'active' ORDER BY $column");
    $sth->execute($fqdn);
    while (my ($value) = $sth->fetchrow_array) {
        push @values, $value;
    }
    return @values;
}

sub load_work_orders_from_db {
    my $dbh = dbh();
    my $orders = { version => 1, work_orders => [] };
    my $sth = $dbh->prepare('SELECT * FROM work_orders ORDER BY id');
    $sth->execute;
    while (my $row = $sth->fetchrow_hashref) {
        my $wo = {
            id => $row->{id},
            status => $row->{status},
            title => $row->{title},
            reason => $row->{reason},
            created_at => $row->{created_at},
            checklist => [],
            actions => [],
        };
        $wo->{confirmed_at} = $row->{confirmed_at} if length($row->{confirmed_at} || '');
        $wo->{result} = $row->{result} if length($row->{result} || '');

        my $items = $dbh->prepare('SELECT * FROM work_order_checklist WHERE work_order_id = ? ORDER BY item_id');
        $items->execute($row->{id});
        while (my $item = $items->fetchrow_hashref) {
            my %copy = (
                id => $item->{item_id},
                text => $item->{text},
                status => $item->{status},
            );
            for my $key (qw(owner notes updated_at)) {
                $copy{$key} = $item->{$key} if length($item->{$key} || '');
            }
            push @{ $wo->{checklist} }, \%copy;
        }

        my $actions = $dbh->prepare('SELECT * FROM work_order_actions WHERE work_order_id = ? ORDER BY position');
        $actions->execute($row->{id});
        while (my $action = $actions->fetchrow_hashref) {
            my %copy = ( type => $action->{type} );
            $copy{host_id} = $action->{host_legacy_id} if length($action->{host_legacy_id} || '');
            $copy{name} = $action->{name} if length($action->{name} || '');
            push @{ $wo->{actions} }, \%copy;
        }

        push @{ $orders->{work_orders} }, $wo;
    }
    return $orders;
}

sub save_work_orders_to_db {
    my ($orders) = @_;
    my $dbh = dbh();
    with_transaction($dbh, sub {
        import_work_orders_to_db($dbh, $orders);
    });
}

sub import_work_orders_to_db {
    my ($dbh, $orders) = @_;
    my $now = iso_now();
    my %seen;
    for my $wo (@{ $orders->{work_orders} || [] }) {
        my $id = clean_scalar($wo->{id} || '');
        next unless $id;
        $seen{$id} = 1;
        $dbh->do(
            'INSERT INTO work_orders (id, status, title, reason, created_at, confirmed_at, result, updated_at) '
            . 'VALUES (?, ?, ?, ?, ?, ?, ?, ?) '
            . 'ON CONFLICT(id) DO UPDATE SET status = excluded.status, title = excluded.title, reason = excluded.reason, '
            . 'created_at = excluded.created_at, confirmed_at = excluded.confirmed_at, result = excluded.result, updated_at = excluded.updated_at',
            undef,
            $id,
            clean_scalar($wo->{status} || 'pending'),
            clean_scalar($wo->{title} || ''),
            clean_scalar($wo->{reason} || ''),
            clean_scalar($wo->{created_at} || $now),
            clean_scalar($wo->{confirmed_at} || ''),
            clean_scalar($wo->{result} || ''),
            $now,
        );
        $dbh->do('DELETE FROM work_order_checklist WHERE work_order_id = ?', undef, $id);
        for my $item (@{ $wo->{checklist} || [] }) {
            $dbh->do(
                'INSERT INTO work_order_checklist (work_order_id, item_id, text, status, owner, notes, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)',
                undef,
                $id,
                clean_scalar($item->{id} || ''),
                clean_scalar($item->{text} || ''),
                clean_scalar($item->{status} || 'pending'),
                clean_scalar($item->{owner} || ''),
                clean_scalar($item->{notes} || ''),
                clean_scalar($item->{updated_at} || ''),
            );
        }
        $dbh->do('DELETE FROM work_order_actions WHERE work_order_id = ?', undef, $id);
        my $position = 0;
        for my $action (@{ $wo->{actions} || [] }) {
            my $legacy_id = clean_id($action->{host_id} || '');
            my $host_fqdn = fqdn_for_legacy_id($dbh, $legacy_id);
            $dbh->do(
                'INSERT INTO work_order_actions (work_order_id, position, type, host_fqdn, host_legacy_id, name, payload) VALUES (?, ?, ?, ?, ?, ?, ?)',
                undef,
                $id,
                $position++,
                clean_scalar($action->{type} || ''),
                $host_fqdn || undef,
                $legacy_id,
                normalize_dns_name($action->{name} || ''),
                '',
            );
        }
    }
}

sub seed_default_workers {
    my ($dbh) = @_;
    my $now = iso_now();
    my @workers = (
        [ 'dhcp-router', 'dhcp', 'Router DHCP leases', 'admin@192.168.2.1', 'DHCP lease/reservation collector source.' ],
        [ 'mdns-listener', 'mdns', 'mDNS listener', 'var/mdns-observations.yaml', 'mDNS observation collector source.' ],
    );
    for my $worker (@workers) {
        $dbh->do(
            'INSERT INTO data_workers (worker_id, worker_type, name, status, source, last_run_at, notes, created_at, updated_at) '
            . "VALUES (?, ?, ?, 'active', ?, NULL, ?, ?, ?) "
            . 'ON CONFLICT(worker_id) DO UPDATE SET worker_type = excluded.worker_type, name = excluded.name, '
            . 'status = excluded.status, source = excluded.source, notes = excluded.notes, updated_at = excluded.updated_at',
            undef,
            @$worker,
            $now,
            $now,
        );
    }
}

sub seed_mdns_observations_from_yaml {
    my ($dbh) = @_;
    return if db_scalar($dbh, 'SELECT COUNT(*) FROM mdns_observations');
    my $path = "$project_dir/var/mdns-observations.yaml";
    return unless -f $path;
    my $db = parse_mdns_observations_yaml(read_file($path));
    with_transaction($dbh, sub {
        for my $observation (@{ $db->{observations} || [] }) {
            $dbh->do(
                'INSERT INTO mdns_observations (observation_key, worker_id, host_fqdn, observed_name, ip_address, rr_type, ttl, first_seen, last_seen, seen_count, last_peer, raw) '
                . "VALUES (?, 'mdns-listener', NULL, ?, ?, 'A', ?, ?, ?, ?, ?, '') "
                . 'ON CONFLICT(observation_key) DO UPDATE SET observed_name = excluded.observed_name, ip_address = excluded.ip_address, '
                . 'ttl = excluded.ttl, last_seen = excluded.last_seen, seen_count = excluded.seen_count, last_peer = excluded.last_peer',
                undef,
                clean_scalar($observation->{key} || "$observation->{name}|$observation->{ip}"),
                clean_scalar($observation->{name} || ''),
                clean_scalar($observation->{ip} || ''),
                int($observation->{ttl} || 0),
                clean_scalar($observation->{first_seen} || iso_now()),
                clean_scalar($observation->{last_seen} || iso_now()),
                int($observation->{seen_count} || 1),
                clean_scalar($observation->{last_peer} || ''),
            );
        }
    });
}

sub parse_mdns_observations_yaml {
    my ($text) = @_;
    my %db = ( observations => [] );
    my ($section, $current);
    for my $line (split /\n/, $text || '') {
        next if $line =~ /^\s*$/ || $line =~ /^\s*#/;
        if ($line =~ /^observations:\s*$/) {
            $section = 'observations';
        } elsif (($section || '') eq 'observations' && $line =~ /^  - key:\s*(.+)$/) {
            $current = { key => yaml_unquote($1) };
            push @{ $db{observations} }, $current;
        } elsif ($current && $line =~ /^    ([A-Za-z0-9_]+):\s*(.*)$/) {
            $current->{$1} = yaml_unquote($2);
        }
    }
    return \%db;
}

sub set_schema_meta {
    my ($dbh, $key, $value) = @_;
    $dbh->do(
        'INSERT INTO schema_meta (key, value, updated_at) VALUES (?, ?, ?) '
        . 'ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at',
        undef,
        $key,
        defined $value ? $value : '',
        iso_now(),
    );
}

sub fqdn_for_legacy_id {
    my ($dbh, $legacy_id) = @_;
    return '' unless length($legacy_id || '');
    my ($fqdn) = $dbh->selectrow_array('SELECT fqdn FROM hosts WHERE legacy_id = ?', undef, $legacy_id);
    return $fqdn || '';
}

sub canonical_host_fqdn {
    my ($host) = @_;
    my $fqdn = normalize_dns_name($host->{fqdn} || '');
    return $fqdn if length $fqdn;
    my @names = declared_dns_names_legacy($host);
    for my $name (@names) {
        return $name if $name =~ /\.madagascar\.xdev\.ro\z/ && !name_is_vhost($name);
    }
    for my $name (@names) {
        return $name if $name =~ /\./ && !name_is_vhost($name);
    }
    my $id = clean_id($host->{id} || '');
    return $id ? "$id.madagascar.xdev.ro" : '';
}

sub legacy_id_from_fqdn {
    my ($fqdn) = @_;
    $fqdn = normalize_dns_name($fqdn);
    $fqdn =~ s/\.madagascar\.xdev\.ro\z//;
    $fqdn =~ s/\..*\z//;
    return clean_id($fqdn);
}

sub normalize_dns_name {
    my ($name) = @_;
    $name = lc clean_scalar($name || '');
    $name =~ s/\.\z//;
    return $name;
}

sub name_is_vhost {
    my ($name) = @_;
    $name = normalize_dns_name($name);
    return $name =~ /\A(?:pmx|pbs|hosts)\./ ? 1 : 0;
}

sub vhost_name_is_valid {
    my ($name) = @_;
    $name = normalize_dns_name($name);
    return 0 unless length $name;
    return 0 unless $name eq 'madagascar.xdev.ro' || $name =~ /\.madagascar\.xdev\.ro\z/;
    return 0 unless length($name) <= 253;
    for my $label (split /\./, $name) {
        return 0 unless length($label) >= 1 && length($label) <= 63;
        return 0 unless $label =~ /\A[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\z/;
    }
    return 1;
}

sub vhost_service_name {
    my ($name) = @_;
    $name = normalize_dns_name($name);
    return $1 if $name =~ /\A([a-z0-9-]+)\./;
    return '';
}

sub short_alias_for_fqdn {
    my ($name) = @_;
    $name = normalize_dns_name($name);
    return $1 if $name =~ /\A(.+)\.madagascar\.xdev\.ro\z/;
    return '';
}

sub normalize_registry_policy {
    my ($registry) = @_;
    $registry->{policy} ||= {};
    $registry->{policy}{storage_authority} = 'sqlite-relational';
    $registry->{policy}{runtime_database} = $opt{db};
}

sub default_hosts_yaml {
    return <<'YAML';
version: 1
updated_at: ""
policy:
  storage_authority: "sqlite-relational"
hosts:
YAML
}

sub default_work_orders_yaml {
    return <<'YAML';
version: 1
work_orders:
YAML
}

sub ensure_parent_dir {
    my ($path) = @_;
    my $dir = dirname($path);
    make_path($dir) unless -d $dir;
}

sub url_decode {
    my ($value) = @_;
    $value = '' unless defined $value;
    $value =~ tr/+/ /;
    $value =~ s/%([0-9A-Fa-f]{2})/chr(hex($1))/eg;
    return $value;
}

sub random_hex {
    my ($bytes) = @_;
    if (open my $fh, '<:raw', '/dev/urandom') {
        read($fh, my $raw, $bytes);
        close $fh;
        return unpack('H*', $raw);
    }
    return sha256_hex(rand() . time() . $$);
}

sub iso_now {
    return strftime('%Y-%m-%dT%H:%M:%SZ', gmtime);
}

sub build_info {
    my %info = (
        revision => '',
        branch => '',
        built_at => '',
        deployed_at => '',
        dirty => '',
    );

    if ($ENV{HOST_MANAGER_BUILD}) {
        $info{revision} = clean_scalar($ENV{HOST_MANAGER_BUILD});
        return \%info;
    }

    my $build_file = "$project_dir/BUILD";
    if (-f $build_file) {
        for my $line (split /\n/, read_file($build_file)) {
            next unless $line =~ /\A([A-Za-z0-9_.-]+)=(.*)\z/;
            $info{$1} = clean_scalar($2);
        }
        return \%info if $info{revision} || $info{built_at};
    }

    my $revision = git_value('rev-parse --short=12 HEAD');
    my $branch = git_value('rev-parse --abbrev-ref HEAD');
    $info{revision} = $revision if $revision;
    $info{branch} = $branch if $branch && $branch ne 'HEAD';
    return \%info;
}

sub git_value {
    my ($args) = @_;
    return '' unless -d "$project_dir/.git";
    open my $fh, '-|', "git -C '$project_dir' $args 2>/dev/null" or return '';
    my $value = <$fh> || '';
    close $fh;
    chomp $value;
    return clean_scalar($value);
}

sub build_label {
    my $info = build_info();
    my $revision = $info->{revision} || 'unknown';
    my $branch = $info->{branch} || '';
    $branch = '' if $branch eq 'HEAD';
    my $label = $branch ? "$branch $revision" : $revision;
    $label .= '+dirty' if ($info->{dirty} || '') eq '1';
    return $label;
}

sub build_title {
    my $info = build_info();
    my $label = build_label();
    my $stamp = $info->{deployed_at} || $info->{built_at} || '';
    return $stamp ? "$label deployed $stamp" : $label;
}

sub build_revision {
    my $info = build_info();
    return $info->{revision} || 'unknown';
}

sub build_details {
    my $info = build_info();
    my %details = (
        app => 'Madagascar Local Authority',
        revision => $info->{revision} || 'unknown',
        branch => $info->{branch} || '',
        dirty => ($info->{dirty} || '') eq '1' ? json_bool(1) : json_bool(0),
        built_at => $info->{built_at} || '',
        deployed_at => $info->{deployed_at} || '',
        label => build_label(),
        title => build_title(),
    );
    return json_encode(\%details);
}

sub html_escape {
    my ($value) = @_;
    $value = '' unless defined $value;
    $value =~ s/&/&amp;/g;
    $value =~ s/</&lt;/g;
    $value =~ s/>/&gt;/g;
    $value =~ s/"/&quot;/g;
    $value =~ s/'/&#039;/g;
    return $value;
}

sub app_html {
    my $build = html_escape(build_revision());
    my $build_title = html_escape(build_title());
    my $build_details = html_escape(build_details());
    my $html = <<'HTML';
<!doctype html>
<html lang="ro">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <meta name="xdev-build" content="__HOST_MANAGER_BUILD_TITLE__">
  <title>Madagascar Local Authority</title>
  <style>
    :root {
      color-scheme: light;
      --ink: #152033;
      --muted: #647084;
      --line: #d8dee8;
      --soft: #f4f6f9;
      --panel: #ffffff;
      --accent: #1267d8;
      --bad: #b42318;
      --warn: #946200;
      --ok: #137333;
    }
    * { box-sizing: border-box; }
    body { margin: 0; font-family: system-ui, -apple-system, BlinkMacSystemFont, "Segoe UI", sans-serif; color: var(--ink); background: #eef2f6; font-size: 14px; }

    /* ── Login screen ── */
    #login-screen {
      display: flex;
      align-items: flex-start;
      justify-content: center;
      min-height: 100dvh;
      padding: clamp(48px, 10vh, 96px) 24px clamp(140px, 20vh, 220px);
      background: #13182a;
      overflow: auto;
    }
    .login-card {
      --otp-size: 48px;
      --otp-gap: 18px;
      --login-form-width: calc((var(--otp-size) * 6) + (var(--otp-gap) * 5));
      background: #fff;
      border-radius: 16px;
      /* Extra bottom room so Safari's OTP autofill banner, which overlays just
         below the first box, sits inside the card instead of spilling past it. */
      padding: 54px 64px 110px;
      width: 100%;
      max-width: 680px;
      min-height: 360px;
      display: grid;
      align-content: start;
      justify-items: center;
      gap: 28px;
      box-shadow: 0 8px 40px rgba(0,0,0,.28);
    }
    .login-card .brand { text-align: center; display: grid; gap: 8px; justify-items: center; }
    .login-card .brand .icon {
      margin: 0 0 8px;
      width: 64px; height: 64px; border-radius: 18px;
      background: #e8f0fe; display: flex; align-items: center; justify-content: center;
    }
    .login-card .brand .icon svg { width: 38px; height: 38px; fill: none; stroke: var(--accent); stroke-width: 2.4; stroke-linecap: round; stroke-linejoin: round; }
    .login-card .brand h1 { margin: 0; font-size: 32px; line-height: 1.05; font-weight: 750; color: var(--ink); }
    .login-card .brand p { margin: 0; color: var(--muted); font-size: 16px; }
    .login-card form {
      display: grid;
      width: min(100%, var(--login-form-width));
      justify-self: center;
      padding-bottom: 0;
    }
    .login-card form.busy { opacity: .72; pointer-events: none; }
    /* Off-screen helper fields keep the visible UI to the 6 OTP boxes while still
       giving the password manager a username anchor and an aggregated OTP target
       (see development-log: "Password-Manager-Friendly Form Shape"). */
    .pm-helper-fields {
      position: absolute;
      left: -10000px;
      top: auto;
      width: 1px;
      height: 1px;
      overflow: hidden;
      opacity: 0.01;
    }
    .pm-helper-fields input {
      width: 1px;
      height: 1px;
      padding: 0;
      border: 0;
    }
    /* 6 separate OTP digit boxes. No autocomplete="one-time-code" on them: that
       hint was what made Safari mark the whole group and re-present its OTP
       autofill on every focused box. Without it, the banner stays on the first. */
    .otp-row {
      display: flex;
      gap: var(--otp-gap);
      justify-content: center;
    }
    .otp-row input {
      width: var(--otp-size); height: 56px; border: 1.5px solid #dde2ec; border-radius: 10px;
      font-size: 22px; font-weight: 600; text-align: center; color: var(--ink);
      background: #f8fafc; caret-color: transparent; outline: none;
      transition: border-color .15s, background .15s;
    }
    .otp-row input:focus { border-color: var(--accent); background: #fff; }
    .otp-row input.filled { border-color: #b3c6f0; background: #fff; }
    #login-error {
      color: var(--bad); font-size: 13px; text-align: center;
      min-height: 18px; margin: -14px 0;
    }
    @media (max-width: 760px) {
      .login-card {
        max-width: 520px;
        min-height: 0;
        padding: 48px 36px 100px;
        gap: 26px;
      }
      .login-card .brand h1 { font-size: 24px; }
      .login-card .brand p { font-size: 14px; }
      .login-card form { padding-bottom: 0; }
    }
    @media (max-width: 430px) {
      #login-screen { padding: 24px 16px 120px; }
      .login-card {
        --otp-size: 42px;
        --otp-gap: 12px;
        padding: 36px 22px 92px;
      }
      .otp-row input { height: 52px; }
      .login-card form { padding-bottom: 0; }
    }
    @media (max-height: 720px) {
      #login-screen { padding-top: 28px; padding-bottom: 96px; }
      .login-card { padding-top: 34px; padding-bottom: 84px; gap: 20px; }
      .login-card form { padding-bottom: 0; }
    }

    /* ── App shell (hidden until authenticated) ── */
    #app { display: none; }
    header { display: grid; grid-template-columns: minmax(180px, auto) 1fr auto; align-items: center; gap: 16px; padding: 12px 18px; background: var(--panel); border-bottom: 1px solid var(--line); position: sticky; top: 0; z-index: 2; }
    h1 { margin: 0; font-size: 17px; font-weight: 700; }
    nav { display: flex; align-items: center; gap: 4px; min-width: 0; overflow-x: auto; }
    nav a { color: var(--muted); text-decoration: none; padding: 7px 10px; border-radius: 6px; white-space: nowrap; font-weight: 650; }
    nav a:hover { color: var(--ink); background: var(--soft); }
    nav a.active { color: var(--accent); background: #e8f0fe; }
    .header-right { display: flex; align-items: center; justify-content: flex-end; gap: 10px; min-width: 0; }
    #message { max-width: 260px; white-space: nowrap; overflow: hidden; text-overflow: ellipsis; }
    main { padding: 16px; display: grid; gap: 16px; max-width: 1280px; margin: 0 auto; }
    .page { display: grid; gap: 16px; }
    .page[hidden] { display: none; }
    .toolbar, .panel { background: var(--panel); border: 1px solid var(--line); border-radius: 8px; }
    .toolbar { display: flex; flex-wrap: wrap; align-items: center; gap: 8px; padding: 10px; }
    .panel { overflow: hidden; }
    .panel-head { display: flex; justify-content: space-between; align-items: center; gap: 12px; padding: 12px 14px; border-bottom: 1px solid var(--line); background: #fafbfc; }
    .panel-head h2 { margin: 0; font-size: 14px; }
    .stats { display: flex; gap: 8px; flex-wrap: wrap; }
    .stat { padding: 6px 8px; border: 1px solid var(--line); border-radius: 6px; background: var(--soft); font-size: 12px; color: var(--muted); }
    button, input, select, textarea { font: inherit; }
    button, .linkbtn { border: 1px solid var(--line); background: #fff; color: var(--ink); border-radius: 6px; padding: 7px 10px; min-height: 34px; cursor: pointer; text-decoration: none; display: inline-flex; align-items: center; gap: 6px; }
    button.primary { background: var(--accent); border-color: var(--accent); color: #fff; }
    button:disabled { opacity: .45; cursor: not-allowed; }
    button.danger { color: var(--bad); }
    button:disabled, .linkbtn[aria-disabled="true"] { opacity: .55; cursor: not-allowed; pointer-events: none; }
    input, select, textarea { width: 100%; border: 1px solid var(--line); border-radius: 6px; padding: 8px; background: #fff; color: var(--ink); }
    textarea { min-height: 74px; resize: vertical; }
    table { width: 100%; border-collapse: collapse; table-layout: fixed; }
    th, td { padding: 9px 10px; border-bottom: 1px solid var(--line); text-align: left; vertical-align: top; overflow-wrap: anywhere; }
    th { color: var(--muted); font-size: 12px; font-weight: 700; background: #fafbfc; }
    tr:hover td { background: #f8fafc; }
    .pill { display: inline-block; padding: 2px 6px; border-radius: 999px; background: var(--soft); border: 1px solid var(--line); color: var(--muted); font-size: 12px; margin: 0 4px 4px 0; }
    .pill.ok { color: var(--ok); border-color: #b7dfc1; background: #edf8ef; }
    .pill.warn { color: var(--warn); border-color: #f1d184; background: #fff7df; }
    .pill.bad { color: var(--bad); border-color: #f0b8b3; background: #fff0ee; }
    .pill.derived { border-style: dashed; }
    .pill.canonical { font-weight: 700; }
    .pill.vhost { background: #eef7ff; border-color: #b6d6f7; color: #0e4f96; }
    .grid { display: grid; grid-template-columns: repeat(2, minmax(0, 1fr)); gap: 10px; padding: 14px; }
    .span2 { grid-column: 1 / -1; }
    label { display: grid; gap: 5px; color: var(--muted); font-size: 12px; font-weight: 650; }
    .muted { color: var(--muted); }
    .mono { font-family: ui-monospace, SFMono-Regular, Menlo, Consolas, "Liberation Mono", monospace; font-size: 12px; }
    .ca-detail { display: grid; gap: 6px; min-width: 0; }
    .ca-fingerprint { overflow-wrap: anywhere; }
    .ca-empty { padding: 12px 14px; }
    .build-control {
      position: fixed;
      right: 10px;
      bottom: 8px;
      z-index: 5;
      display: inline-flex;
      align-items: center;
      gap: 4px;
    }
    .build-badge, .build-copy {
      color: rgba(255,255,255,.46);
      background: rgba(19,24,42,.28);
      border: 1px solid rgba(255,255,255,.08);
      border-radius: 4px;
      font-size: 10px;
      line-height: 1.2;
    }
    .build-badge {
      padding: 2px 5px;
      cursor: text;
      user-select: text;
    }
    .build-copy {
      min-height: 0;
      padding: 2px 5px;
      cursor: pointer;
    }
    .build-copy:hover {
      color: rgba(255,255,255,.72);
      border-color: rgba(255,255,255,.24);
    }
    body.is-app .build-badge, body.is-app .build-copy {
      color: rgba(100,112,132,.58);
      background: rgba(255,255,255,.72);
      border-color: rgba(216,222,232,.72);
    }
    body.is-app .build-copy:hover {
      color: rgba(21,32,51,.78);
      border-color: rgba(100,112,132,.42);
    }
    .problems { padding: 10px 14px; display: grid; gap: 8px; }
    .problem { border-left: 3px solid var(--warn); padding: 7px 9px; background: #fffaf0; }
    .work-order-card { display: grid; gap: 8px; min-width: 0; }
    .work-order-head { display: flex; align-items: center; justify-content: space-between; gap: 12px; flex-wrap: wrap; }
    .work-order-title { color: var(--ink); font-size: 14px; font-weight: 650; }
    .work-order-checklist, .work-order-actions { display: grid; gap: 6px; min-width: 0; }
    .work-order-actions { gap: 4px; }
    .work-order-checkitem { display: flex; align-items: flex-start; gap: 8px; min-width: 0; color: var(--ink); font-size: 13px; font-weight: 400; }
    .work-order-checkitem input[type="checkbox"] { width: auto; flex: 0 0 auto; margin: 2px 0 0; }
    .work-order-checkitem span { min-width: 0; overflow-wrap: anywhere; }
    .debug-controls { display: flex; flex-wrap: wrap; gap: 8px; align-items: center; width: 100%; }
    .debug-meta { display: flex; flex-wrap: wrap; gap: 8px; align-items: center; }
    .debug-table-cards { display: grid; grid-template-columns: repeat(auto-fill, minmax(180px, 1fr)); gap: 8px; padding: 10px; border-top: 1px solid var(--line); }
    .debug-table-card { display: grid; grid-template-columns: minmax(0, 1fr) auto; align-items: center; gap: 6px; min-height: 58px; padding: 8px; border: 1px solid var(--line); border-radius: 6px; background: #fff; }
    .debug-table-card:hover { border-color: #9fb7e9; background: #f8fbff; }
    .debug-table-card.active { border-color: var(--accent); background: #e8f0fe; box-shadow: inset 0 0 0 1px var(--accent); }
    .debug-table-card-main { display: grid; align-content: center; justify-items: start; gap: 5px; min-width: 0; min-height: 42px; width: 100%; padding: 4px 6px; border: 0; background: transparent; text-align: left; }
    .debug-table-card-main:hover { background: transparent; }
    .debug-table-card-name { max-width: 100%; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; color: var(--ink); font-weight: 700; }
    .debug-table-card-rows { color: var(--muted); font-size: 12px; }
    .debug-table-copy { position: relative; min-width: 34px; width: 34px; justify-content: center; padding: 7px; color: var(--muted); font-size: 0; }
    .debug-table-copy::before, .debug-table-copy::after { content: ""; position: absolute; width: 12px; height: 14px; border: 1.6px solid currentColor; border-radius: 2px; box-sizing: border-box; }
    .debug-table-copy::before { transform: translate(2px, -2px); opacity: .62; }
    .debug-table-copy::after { transform: translate(-2px, 2px); background: #fff; }
    .debug-table-head-actions { display: flex; align-items: center; justify-content: flex-end; gap: 8px; flex-wrap: wrap; }
    .debug-table-exports { display: flex; align-items: center; gap: 6px; flex-wrap: wrap; }
    .debug-section { display: grid; gap: 16px; }
    .host-tools { display: flex; align-items: center; justify-content: flex-end; gap: 8px; min-width: 0; }
    .host-tools input { max-width: 240px; }
    .host-alias-cell { display: grid; gap: 5px; min-width: 0; }
    .host-alias-list { display: flex; flex-wrap: wrap; gap: 4px; align-items: flex-start; }
    .host-alias-pill { display: inline-flex; align-items: center; gap: 4px; min-width: 0; margin: 0; }
    .host-alias-label { min-width: 0; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; }
    .host-alias-remove, .host-alias-add { min-height: 28px; padding: 3px 7px; font-size: 12px; }
    .host-alias-remove { min-height: 0; padding: 0; border: 0; background: transparent; color: var(--bad); }
    .host-alias-remove:hover { background: transparent; }
    .iconbtn { min-width: 34px; width: 34px; justify-content: center; padding: 7px; }
    .iconbtn svg { width: 14px; height: 14px; stroke: currentColor; fill: none; stroke-width: 1.8; stroke-linecap: round; stroke-linejoin: round; }
    .host-actions { display: flex; align-items: center; gap: 6px; }
    .field-head { display: flex; align-items: center; justify-content: space-between; gap: 8px; }
    .host-cert-cell { min-width: 0; }
    #page-vhosts .panel-head { align-items: center; padding-block: 10px; }
    #page-vhosts .host-tools { flex-wrap: wrap; }
    #page-vhosts .host-tools input { max-width: 280px; }
    #page-vhosts .stats { justify-content: flex-end; }
    #page-vhosts .table-wrap { overflow-x: visible; }
    #page-vhosts table { min-width: 0; }
    #page-vhosts th, #page-vhosts td { overflow-wrap: normal; }
    #page-vhosts .pill.vhost { max-width: 100%; overflow: hidden; text-overflow: ellipsis; white-space: nowrap; vertical-align: top; }
    .vhost-name-cell { display: grid; gap: 5px; min-width: 0; }
    .vhost-name-main { display: grid; grid-template-columns: minmax(0, 1fr) auto; align-items: center; gap: 6px; min-width: 0; }
    .vhost-delete { min-height: 28px; padding: 3px 7px; color: var(--bad); font-size: 12px; }
    .vhost-host { display: grid; gap: 2px; }
    .vhost-pill-row { display: flex; flex-wrap: wrap; gap: 4px; }
    .vhost-pill-row .pill { margin: 0; }
    .vhost-host-select { width: 100%; max-width: 100%; min-height: 34px; }
    .vhost-cert { display: grid; gap: 5px; min-width: 0; }
    .vhost-cert-main { display: grid; grid-template-columns: minmax(0, 1fr) auto; gap: 6px; align-items: center; }
    .vhost-cert-select { width: 100%; max-width: 100%; min-height: 34px; }
    .vhost-cert-meta { display: flex; align-items: center; gap: 6px; flex-wrap: wrap; min-height: 24px; }
    .vhost-cert-links { display: flex; align-items: center; gap: 4px; flex-wrap: wrap; }
    .vhost-cert-links .linkbtn { padding: 3px 7px; font-size: 12px; }
    .vhost-cert-validity { font-size: 12px; }
    .vhost-inline-editor { display: grid; grid-template-columns: minmax(260px, 1fr) minmax(260px, 1fr) auto; gap: 8px; padding: 10px; border-bottom: 1px solid var(--line); background: #fff; }
    .host-inline-row td { padding: 0; background: #fff; }
    .host-inline-editor-shell { background: #fff; }
    .host-inline-editor-head { display: flex; align-items: center; justify-content: space-between; gap: 12px; padding: 12px 14px; border-top: 1px solid var(--line); border-bottom: 1px solid var(--line); background: #fafbfc; }
    .host-inline-editor-head h2 { margin: 0; font-size: 14px; }
    .host-inline-editor-tools { display: flex; align-items: center; gap: 8px; flex-wrap: wrap; }
    .form-message { min-height: 18px; color: var(--muted); font-size: 13px; }
    .form-message.error { color: var(--bad); }
    .form-actions { display: flex; flex-wrap: wrap; gap: 8px; }
    @media (max-width: 760px) {
      header { grid-template-columns: 1fr; align-items: stretch; gap: 10px; }
      .header-right { justify-content: flex-start; flex-wrap: wrap; }
      #message { max-width: 100%; }
      .panel-head { align-items: stretch; flex-direction: column; }
      .host-tools { justify-content: flex-start; flex-wrap: wrap; }
      .host-tools input { max-width: none; }
      .vhost-inline-editor { grid-template-columns: 1fr; }
      .host-inline-editor-head { align-items: stretch; flex-direction: column; }
      .host-inline-editor-tools { justify-content: flex-start; }
      .debug-controls { align-items: stretch; }
      .grid { grid-template-columns: 1fr; }
      table { min-width: 760px; }
      .table-wrap { overflow-x: auto; }
    }
  </style>
</head>
<body class="is-login">

  <!-- ── Login screen ── -->
  <div id="login-screen">
    <div class="login-card">
      <div class="brand">
        <div class="icon">
          <svg viewBox="0 0 64 64" xmlns="http://www.w3.org/2000/svg" aria-hidden="true">
            <rect x="16" y="10" width="32" height="44" rx="4"/>
            <rect x="21" y="16" width="22" height="8" rx="2"/>
            <rect x="21" y="28" width="22" height="8" rx="2"/>
            <rect x="21" y="40" width="22" height="8" rx="2"/>
            <path d="M26 20h8M26 32h8M26 44h8"/>
            <path d="M40 20h.01M40 32h.01M40 44h.01"/>
          </svg>
        </div>
        <h1>Madagascar Local Authority</h1>
        <p>Hosts, DNS &amp; Local CA</p>
      </div>
      <div id="login-error"></div>
      <form id="login-form" method="post" action="/api/login" autocomplete="on" novalidate>
        <div class="pm-helper-fields" aria-hidden="true">
          <input type="text" id="login-account" name="username" autocomplete="username" autocapitalize="off" spellcheck="false">
          <input type="hidden" id="otp-hidden" name="otp">
        </div>
        <div class="otp-row">
          <input type="text" class="otp-digit" maxlength="1" inputmode="numeric" autocomplete="off" required aria-label="Digit 1">
          <input type="text" class="otp-digit" maxlength="1" inputmode="numeric" autocomplete="off" required aria-label="Digit 2">
          <input type="text" class="otp-digit" maxlength="1" inputmode="numeric" autocomplete="off" required aria-label="Digit 3">
          <input type="text" class="otp-digit" maxlength="1" inputmode="numeric" autocomplete="off" required aria-label="Digit 4">
          <input type="text" class="otp-digit" maxlength="1" inputmode="numeric" autocomplete="off" required aria-label="Digit 5">
          <input type="text" class="otp-digit" maxlength="1" inputmode="numeric" autocomplete="off" required aria-label="Digit 6">
        </div>
      </form>
    </div>
  </div>

  <!-- ── App (shown after login) ── -->
  <div id="app">
    <header>
      <h1>Madagascar Local Authority</h1>
      <nav aria-label="Sections">
        <a href="/overview" data-page-link="overview">Overview</a>
        <a href="/hosts" data-page-link="hosts">Hosts</a>
        <a href="/vhosts" data-page-link="vhosts">Vhosts</a>
        <a href="/dns" data-page-link="dns">DNS</a>
        <a href="/work-orders" data-page-link="work-orders">Work Orders</a>
        <a href="/ca" data-page-link="ca">Local CA</a>
        <a href="/debug" data-page-link="debug">Debug</a>
      </nav>
      <div class="header-right">
        <span class="muted" id="app-updated"></span>
        <span id="message" class="muted"></span>
        <button id="refresh">Refresh</button>
        <button type="button" id="logout">Logout</button>
      </div>
    </header>
    <main>
      <section class="page" id="page-overview" data-page="overview">
        <section class="panel">
          <div class="panel-head">
            <h2>Overview</h2>
            <div class="stats" id="stats"></div>
          </div>
          <div class="problems" id="problems"></div>
        </section>
      </section>

      <section class="page" id="page-hosts" data-page="hosts" hidden>
        <section class="panel">
          <div class="panel-head">
            <h2>Hosts</h2>
            <div class="host-tools">
              <input id="filter" placeholder="filter">
              <button type="button" id="new-host">New host</button>
            </div>
          </div>
          <div class="table-wrap">
            <table>
              <thead>
                <tr>
                  <th style="width: 280px">Host</th>
                  <th style="width: 140px">IP</th>
                  <th>Aliases</th>
                  <th style="width: 150px">Roles</th>
                  <th style="width: 260px">Certificate</th>
                  <th style="width: 110px">Monitoring</th>
                  <th style="width: 90px">Status</th>
                  <th style="width: 90px">Actions</th>
                </tr>
              </thead>
              <tbody id="hosts"></tbody>
            </table>
          </div>
        </section>
      </section>

      <section class="page" id="page-vhosts" data-page="vhosts" hidden>
        <section class="panel">
          <div class="panel-head">
            <h2>Vhosts</h2>
            <div class="host-tools">
              <input id="vhost-filter" placeholder="filter">
              <div class="stats" id="vhost-stats"></div>
            </div>
          </div>
          <div class="vhost-inline-editor">
            <input id="vhost-new-name" placeholder="vhost fqdn">
            <select id="vhost-new-host"></select>
            <button type="button" id="vhost-add">Add</button>
          </div>
          <div class="table-wrap">
            <table>
              <thead>
                <tr>
                  <th style="width: 22%">Vhost</th>
                  <th style="width: 28%">Host</th>
                  <th style="width: 34%">Certificate</th>
                  <th style="width: 8%">Monitoring</th>
                  <th style="width: 6%">Status</th>
                </tr>
              </thead>
              <tbody id="vhosts"></tbody>
            </table>
          </div>
        </section>
      </section>

      <section class="page" id="page-dns" data-page="dns" hidden>
        <section class="toolbar">
          <a class="linkbtn" href="/download/hosts.yaml">hosts.yaml</a>
          <a class="linkbtn" href="/download/local-hosts.tsv">local-hosts.tsv</a>
          <a class="linkbtn" href="/download/monitoring.json">monitoring.json</a>
          <button id="write-tsv">Write local-hosts.tsv</button>
        </section>
      </section>

      <section class="page" id="page-work-orders" data-page="work-orders" hidden>
        <section class="panel">
          <div class="panel-head">
            <h2>Work Orders</h2>
            <div class="stats" id="wo-stats"></div>
          </div>
          <div class="problems" id="work-orders"></div>
        </section>
      </section>

      <section class="page" id="page-ca" data-page="ca" hidden>
        <section class="panel">
          <div class="panel-head">
            <h2>Local Certificate Authority</h2>
            <a class="linkbtn" href="/download/ca.crt">ca.crt</a>
          </div>
          <div class="problems" id="ca-status"></div>
        </section>
        <section class="panel">
          <div class="panel-head">
            <h2>Issued Certificates</h2>
            <div class="stats" id="ca-certs-summary"></div>
          </div>
          <div class="table-wrap">
            <table>
              <thead>
                <tr>
                  <th style="width: 150px">Name</th>
                  <th>DNS names</th>
                  <th style="width: 210px">Validity</th>
                  <th style="width: 180px">Serial</th>
                  <th>Fingerprint</th>
                  <th style="width: 110px">Download</th>
                </tr>
              </thead>
              <tbody id="ca-certs"></tbody>
            </table>
          </div>
        </section>
      </section>

      <section class="page" id="page-debug" data-page="debug" hidden>
        <section class="panel">
          <div class="panel-head">
            <h2>Database</h2>
            <div class="stats" id="debug-db-stats"></div>
          </div>
          <div class="toolbar">
            <div class="debug-controls">
              <button type="button" id="debug-db-refresh">Refresh</button>
              <div class="debug-meta muted mono" id="debug-db-meta"></div>
            </div>
          </div>
          <div class="debug-table-cards" id="debug-db-tables"></div>
        </section>
        <section class="debug-section">
          <section class="panel">
            <div class="panel-head">
              <h2>Rows</h2>
              <div class="debug-table-head-actions">
                <div class="stats" id="debug-table-stats"></div>
                <div class="debug-table-exports">
                  <a class="linkbtn" id="debug-export-json" href="#" aria-disabled="true">JSON</a>
                  <a class="linkbtn" id="debug-export-csv" href="#" aria-disabled="true">CSV</a>
                </div>
              </div>
            </div>
            <div class="table-wrap" id="debug-table-rows"></div>
          </section>
          <section class="panel">
            <div class="panel-head">
              <h2>Columns</h2>
            </div>
            <div class="table-wrap" id="debug-table-columns"></div>
          </section>
          <section class="panel">
            <div class="panel-head">
              <h2>Indexes</h2>
            </div>
            <div class="table-wrap" id="debug-table-indexes"></div>
          </section>
          <section class="panel">
            <div class="panel-head">
              <h2>Foreign Keys</h2>
            </div>
            <div class="table-wrap" id="debug-table-foreign-keys"></div>
          </section>
        </section>
      </section>
    </main>

  </div>

  <div class="build-control" title="Running build __HOST_MANAGER_BUILD_TITLE__">
    <span class="build-badge">__HOST_MANAGER_BUILD__</span>
    <button type="button" class="build-copy" id="copy-build" data-build-details="__HOST_MANAGER_BUILD_DETAILS__" aria-label="Copy build details">Copy</button>
  </div>

  <script>
    let state = { hosts: [], vhosts: [], certificates: [], problems: [], workOrders: [], authenticated: false, debugTable: '' };
    let hostFormSnapshot = '';
    let hostFormBusy = false;
    let hostFormMode = 'new';
    let hostEditorTarget = '';

    const $ = (id) => document.getElementById(id);
    const msg = (text) => { $('message').textContent = text || ''; };
    const hostFormShell = document.createElement('div');
    hostFormShell.id = 'host-form-shell';
    hostFormShell.className = 'host-inline-editor-shell';
    hostFormShell.hidden = true;
    hostFormShell.innerHTML = `
      <div class="host-inline-editor-head">
        <h2 id="host-form-title">New host</h2>
        <div class="host-inline-editor-tools">
          <button class="primary" type="submit" id="save-host" form="host-form">Save host</button>
          <button class="danger" type="button" id="delete-host">Delete host</button>
          <button type="button" id="cancel-host-form">Close</button>
        </div>
      </div>
      <form id="host-form" class="grid">
        <label>Legacy ID<input name="id" required></label>
        <label>FQDN<input name="fqdn" required></label>
        <label>Status<select name="status"><option>active</option><option>planned</option><option>retired</option></select></label>
        <label>IP<input name="ip" required></label>
        <label class="span2"><span class="field-head"><span>Aliases</span><button type="button" id="host-add-alias-editor" class="host-alias-add" title="Add alias">+</button></span><textarea name="aliases"></textarea></label>
        <label>Roles<input name="roles"></label>
        <label>Monitoring<select name="monitoring"><option>pending</option><option>enabled</option><option>disabled</option></select></label>
        <label>Notes<input name="notes"></label>
        <div id="host-form-message" class="span2 form-message" aria-live="polite"></div>
      </form>`;
    const hostForm = hostFormShell.querySelector('#host-form');
    const hostFormTitle = hostFormShell.querySelector('#host-form-title');
    const hostFormMessage = hostFormShell.querySelector('#host-form-message');
    const saveHostButton = hostFormShell.querySelector('#save-host');
    const deleteHostButton = hostFormShell.querySelector('#delete-host');
    const cancelHostButton = hostFormShell.querySelector('#cancel-host-form');
    const hostAddAliasEditorButton = hostFormShell.querySelector('#host-add-alias-editor');
    const hostEditorRow = document.createElement('tr');
    hostEditorRow.className = 'host-inline-row';
    const hostEditorCell = document.createElement('td');
    hostEditorCell.colSpan = 8;
    hostEditorRow.appendChild(hostEditorCell);
    hostEditorCell.appendChild(hostFormShell);
    const PAGE_PATHS = {
      '/': 'overview',
      '/overview': 'overview',
      '/hosts': 'hosts',
      '/vhosts': 'vhosts',
      '/dns': 'dns',
      '/work-orders': 'work-orders',
      '/ca': 'ca',
      '/debug': 'debug',
    };

    function isAuthLost(error) {
      return !!(error && error.authLost);
    }

    function authLostError(message) {
      const error = new Error(message || 'Sesiunea a expirat. Autentifica-te din nou.');
      error.authLost = true;
      return error;
    }

    function handleAuthLost(message) {
      state.authenticated = false;
      msg('');
      showLogin(message || 'Sesiunea a expirat. Autentifica-te din nou.');
    }

    async function ensureAuthenticated(message) {
      if (!state.authenticated) {
        handleAuthLost(message || 'Autentifica-te pentru a continua.');
        return false;
      }
      const session = await api('/api/session');
      state.authenticated = session.authenticated;
      if (!state.authenticated) {
        handleAuthLost(message || 'Sesiunea a expirat. Autentifica-te din nou.');
        return false;
      }
      return true;
    }

    async function api(path, options = {}) {
      const res = await fetch(path, options);
      let body = {};
      try {
        body = await res.json();
      } catch (_) {
        body = {};
      }
      const errorCode = body.error || '';
      if (!res.ok) {
        if (res.status === 401 && !(path === '/api/login' && errorCode === 'invalid_otp')) {
          const error = authLostError();
          handleAuthLost(error.message);
          throw error;
        }
        const error = new Error(body.detail || errorCode || res.statusText);
        error.code = errorCode;
        throw error;
      }
      return body;
    }

    function currentPage() {
      return PAGE_PATHS[window.location.pathname] || 'overview';
    }

    function showPage(page, push = false) {
      const target = page || 'overview';
      document.querySelectorAll('[data-page]').forEach(section => {
        section.hidden = section.dataset.page !== target;
      });
      document.querySelectorAll('[data-page-link]').forEach(link => {
        link.classList.toggle('active', link.dataset.pageLink === target);
        link.setAttribute('aria-current', link.dataset.pageLink === target ? 'page' : 'false');
      });
      if (push) {
        const href = target === 'overview' ? '/overview' : '/' + target;
        history.pushState({ page: target }, '', href);
      }
      if (state.authenticated && target === 'debug') {
        renderDebugDatabase().catch(e => {
          if (!isAuthLost(e)) msg(e.message);
        });
      }
    }

    function showLogin(errorText) {
      state.authenticated = false;
      document.body.classList.remove('is-app');
      document.body.classList.add('is-login');
      $('app').style.display = 'none';
      $('login-screen').style.display = 'flex';
      $('login-error').textContent = errorText || '';
      clearOtp();
    }

    function showApp() {
      document.body.classList.remove('is-login');
      document.body.classList.add('is-app');
      $('login-screen').style.display = 'none';
      $('app').style.display = 'block';
      showPage(currentPage());
    }

    async function refresh() {
      const session = await api('/api/session');
      state.authenticated = session.authenticated;
      if (!state.authenticated) { showLogin('Autentifica-te pentru a continua.'); return; }
      showApp();
      const data = await api('/api/hosts');
      state.hosts = data.hosts || [];
      state.vhosts = data.vhosts || [];
      state.certificates = data.certificates || [];
      state.problems = data.problems || [];
      render(data);
      await renderCa();
      await renderWorkOrders();
      if (currentPage() === 'debug') await renderDebugDatabase();
    }

    function render(data) {
      $('app-updated').textContent = data.updated_at ? 'updated ' + data.updated_at : '';

      $('stats').innerHTML = [
        ['hosts', data.counts.hosts],
        ['vhosts', data.counts.vhosts || vhostRows().length],
        ['problems', data.counts.problems],
      ].map(([k, v]) => `<span class="stat">${k}: ${escapeHtml(String(v))}</span>`).join('');

      $('problems').innerHTML = state.problems.length
        ? state.problems.map(p => `<div class="problem"><strong>${escapeHtml(p.host_id)}</strong> ${escapeHtml(p.code)}: ${escapeHtml(p.message)}</div>`).join('')
        : '<div class="muted" style="padding: 8px 0">No registry problems detected.</div>';

      renderHosts();
      renderVhostEditor();
      renderVhosts();
    }

    async function renderCa() {
      try {
        const status = await api('/api/ca/status');
        if (!status.initialized) {
          $('ca-status').innerHTML = '<div class="problem"><strong>not initialized</strong> Run <code>sudo scripts/ca_manager.sh init</code> on jumper.</div>';
          $('ca-certs-summary').innerHTML = '';
          $('ca-certs').innerHTML = '<tr><td colspan="6" class="muted">CA is not initialized.</td></tr>';
          return;
        }
        const certs = await api('/api/ca/certificates');
        state.certificates = certs.map(cert => ({
          ...cert,
          id: cert.id || cert.name || '',
          name: cert.name || cert.id || '',
          has_private_key: !!cert.has_private_key
        }));
        const caDays = daysUntil(status.not_after);
        $('ca-status').innerHTML = `
          <div class="muted ca-detail">
            <div><strong>${escapeHtml(status.subject || '')}</strong></div>
            <div>SHA256 <span class="mono ca-fingerprint">${escapeHtml(status.fingerprint_sha256 || '')}</span></div>
            <div>valid ${escapeHtml(status.not_before || '')} - ${escapeHtml(status.not_after || '')}</div>
            <div>
              <span class="pill ${certStatusClass(caDays)}">${escapeHtml(certStatusLabel(caDays))}</span>
              <span>${certs.length} issued certificate(s)</span>
            </div>
          </div>`;
        $('ca-certs-summary').innerHTML = [
          ['issued', certs.length],
          ['expiring', certs.filter(cert => {
            const days = daysUntil(cert.not_after);
            return days !== null && days >= 0 && days <= 30;
          }).length],
          ['expired', certs.filter(cert => {
            const days = daysUntil(cert.not_after);
            return days !== null && days < 0;
          }).length],
        ].map(([k, v]) => `<span class="stat">${k}: ${escapeHtml(String(v))}</span>`).join('');
        $('ca-certs').innerHTML = certs.length ? certs.map(cert => {
          const days = daysUntil(cert.not_after);
          const dnsNames = cert.dns_names || [];
          const dnsHtml = dnsNames.length
            ? dnsNames.map(name => `<span class="pill">${escapeHtml(name)}</span>`).join('')
            : '<span class="muted">No DNS SANs reported.</span>';
          return `<tr>
            <td><strong>${escapeHtml(cert.name || '')}</strong></td>
            <td>${dnsHtml}</td>
            <td>
              <div class="ca-detail">
                <span class="pill ${certStatusClass(days)}">${escapeHtml(certStatusLabel(days))}</span>
                <span class="muted">until ${escapeHtml(cert.not_after || '')}</span>
              </div>
            </td>
            <td class="mono">${escapeHtml(cert.serial || '')}</td>
            <td class="mono ca-fingerprint">${escapeHtml(cert.fingerprint_sha256 || '')}</td>
            <td>
              <div class="vhost-cert-links">
                <a class="linkbtn" href="/download/ca/cert/${encodeURIComponent(cert.name || '')}.crt">crt</a>
                ${cert.has_private_key ? `<a class="linkbtn" href="/download/ca/key/${encodeURIComponent(cert.name || '')}.key">key</a>` : ''}
              </div>
            </td>
          </tr>`;
        }).join('') : '<tr><td colspan="6" class="muted">No issued certificates.</td></tr>';
      } catch (e) {
        if (isAuthLost(e)) return;
        $('ca-status').innerHTML = `<div class="problem"><strong>CA status unavailable</strong> ${escapeHtml(e.message)}</div>`;
        $('ca-certs-summary').innerHTML = '';
        $('ca-certs').innerHTML = '<tr><td colspan="6" class="muted">Certificate list unavailable.</td></tr>';
      }
    }

    function daysUntil(dateText) {
      const time = Date.parse(dateText || '');
      if (!Number.isFinite(time)) return null;
      return Math.ceil((time - Date.now()) / 86400000);
    }

    function certStatusClass(days) {
      if (days === null) return '';
      if (days < 0) return 'bad';
      if (days <= 30) return 'warn';
      return 'ok';
    }

    function certStatusLabel(days) {
      if (days === null) return 'validity unknown';
      if (days < 0) return 'expired';
      if (days === 0) return 'expires today';
      return `${days}d remaining`;
    }

    async function renderWorkOrders() {
      try {
        const data = await api('/api/work-orders');
        state.workOrders = data.work_orders || [];
        $('wo-stats').innerHTML = [
          ['pending', data.counts.pending],
          ['total', data.counts.work_orders],
        ].map(([k, v]) => `<span class="stat">${k}: ${escapeHtml(String(v))}</span>`).join('');

        if (!state.workOrders.length) {
          $('work-orders').innerHTML = '<div class="muted" style="padding: 8px 0">No work orders.</div>';
          return;
        }

        $('work-orders').innerHTML = state.workOrders.map(wo => {
          const checklist = wo.checklist || [];
          const doneItems = checklist.filter(item => (item.status || 'pending') === 'done').length;
          const checklistComplete = checklist.length === 0 || doneItems === checklist.length;
          const checklistHtml = checklist.map(item => {
            const checked = (item.status || 'pending') === 'done' ? 'checked' : '';
            return `<label class="work-order-checkitem">
              <input type="checkbox" data-wo-checklist="${escapeHtml(wo.id)}" data-item-id="${escapeHtml(item.id || '')}" ${checked}>
              <span><strong>${escapeHtml(item.id || '')}</strong> ${escapeHtml(item.text || '')}</span>
            </label>`;
          }).join('');
          const actions = (wo.actions || []).map(a => {
            const target = [a.host_id, a.name].filter(Boolean).join(' ');
            return `<div><span class="pill">${escapeHtml(a.type || '')}</span> ${escapeHtml(target)}</div>`;
          }).join('');
          const statusClass = (wo.status || 'pending') === 'pending' ? 'warn' : 'ok';
          const button = (wo.status || 'pending') === 'pending'
            ? `<button type="button" class="primary" data-confirm-wo="${escapeHtml(wo.id)}" ${checklistComplete ? '' : 'disabled'}>Confirm</button>`
            : '';
          return `<div class="problem work-order-card">
            <div class="work-order-head">
              <div><strong>${escapeHtml(wo.id || '')}</strong> <span class="pill ${statusClass}">${escapeHtml(wo.status || 'pending')}</span> <span class="pill">${doneItems}/${checklist.length} done</span></div>
              ${button}
            </div>
            <div class="work-order-title">${escapeHtml(wo.title || '')}</div>
            <div class="muted">${escapeHtml(wo.reason || '')}</div>
            <div class="work-order-checklist">${checklistHtml}</div>
            <div class="work-order-actions">${actions}</div>
            ${wo.confirmed_at ? `<div class="muted">confirmed ${escapeHtml(wo.confirmed_at)}</div>` : ''}
          </div>`;
        }).join('');
        document.querySelectorAll('[data-wo-checklist]').forEach(input => input.addEventListener('change', () => updateWorkOrderChecklist(input.dataset.woChecklist, input.dataset.itemId, input.checked)));
        document.querySelectorAll('[data-confirm-wo]').forEach(button => button.addEventListener('click', () => confirmWorkOrder(button.dataset.confirmWo)));
      } catch (e) {
        if (isAuthLost(e)) return;
        $('work-orders').innerHTML = `<div class="problem"><strong>Work orders unavailable</strong> ${escapeHtml(e.message)}</div>`;
      }
    }

    async function renderDebugDatabase() {
      if (!state.authenticated) return;
      const data = await api('/api/debug/database/tables');
      const tables = data.tables || [];
      const selected = tables.some(table => table.name === state.debugTable) ? state.debugTable : (tables[0] ? tables[0].name : '');
      state.debugTable = selected;
      $('debug-db-stats').innerHTML = [
        ['tables', data.counts ? data.counts.tables : tables.length],
        ['rows', data.counts ? data.counts.rows : tables.reduce((total, table) => total + Number(table.rows || 0), 0)],
      ].map(([k, v]) => `<span class="stat">${escapeHtml(k)}: ${escapeHtml(String(v))}</span>`).join('');
      $('debug-db-meta').textContent = data.database || '';
      renderDebugTableCards(tables, selected, data.database || '');
      if (selected) {
        await renderDebugTable(selected);
      } else {
        clearDebugTable();
      }
    }

    function renderDebugTableCards(tables, selected, database) {
      $('debug-db-tables').innerHTML = tables.length
        ? tables.map(table => {
            const active = table.name === selected;
            const ref = debugTableReference(database, table.name);
            return `<div class="debug-table-card ${active ? 'active' : ''}">
              <button type="button" class="debug-table-card-main" data-debug-table="${escapeHtml(table.name)}" aria-pressed="${active ? 'true' : 'false'}">
                <span class="debug-table-card-name mono">${escapeHtml(table.name)}</span>
                <span class="debug-table-card-rows">${escapeHtml(String(table.rows || 0))} rows</span>
              </button>
              <button type="button" class="debug-table-copy" data-debug-table-ref="${escapeHtml(ref)}" title="${escapeHtml(ref)}" aria-label="Copy full table reference for ${escapeHtml(table.name)}"></button>
            </div>`;
          }).join('')
        : '<div class="ca-empty muted">No database tables found.</div>';
      document.querySelectorAll('[data-debug-table]').forEach(button => {
        button.addEventListener('click', () => selectDebugTable(button.dataset.debugTable).catch(e => {
          if (!isAuthLost(e)) msg(e.message);
        }));
      });
      document.querySelectorAll('[data-debug-table-ref]').forEach(button => {
        button.addEventListener('click', async () => {
          try {
            await copyText(button.dataset.debugTableRef || '');
            msg('table reference copied');
          } catch (e) {
            msg('copy failed');
          }
        });
      });
    }

    function debugTableReference(database, tableName) {
      return `sqlite:${database || ''}#${tableName || ''}`;
    }

    async function selectDebugTable(tableName) {
      state.debugTable = tableName || '';
      document.querySelectorAll('[data-debug-table]').forEach(button => {
        const active = button.dataset.debugTable === state.debugTable;
        const card = button.closest('.debug-table-card');
        if (card) card.classList.toggle('active', active);
        button.setAttribute('aria-pressed', active ? 'true' : 'false');
      });
      if (state.debugTable) await renderDebugTable(state.debugTable);
    }

    function clearDebugTable() {
      $('debug-table-stats').innerHTML = '';
      updateDebugExportLinks('');
      $('debug-table-rows').innerHTML = '<div class="ca-empty muted">No table selected.</div>';
      $('debug-table-columns').innerHTML = '<div class="ca-empty muted">No table selected.</div>';
      $('debug-table-indexes').innerHTML = '<div class="ca-empty muted">No table selected.</div>';
      $('debug-table-foreign-keys').innerHTML = '<div class="ca-empty muted">No table selected.</div>';
    }

    async function renderDebugTable(tableName) {
      const data = await api(`/api/debug/database/table?name=${encodeURIComponent(tableName)}&limit=200`);
      if (data.error) throw new Error(data.error);
      $('debug-table-stats').innerHTML = [
        ['table', data.table || tableName],
        ['rows', data.row_count || 0],
        ['shown', (data.rows || []).length],
      ].map(([k, v]) => `<span class="stat">${escapeHtml(k)}: ${escapeHtml(String(v))}</span>`).join('');
      updateDebugExportLinks(data.table || tableName);
      renderDebugRows(data);
      $('debug-table-columns').innerHTML = renderDebugObjectTable(data.columns || [], ['cid', 'name', 'type', 'notnull', 'dflt_value', 'pk']);
      $('debug-table-indexes').innerHTML = renderDebugObjectTable(data.indexes || [], ['name', 'unique', 'origin', 'partial', 'columns']);
      $('debug-table-foreign-keys').innerHTML = renderDebugObjectTable(data.foreign_keys || [], ['id', 'seq', 'table', 'from', 'to', 'on_update', 'on_delete', 'match']);
    }

    function updateDebugExportLinks(tableName) {
      const encoded = encodeURIComponent(tableName || '');
      [
        ['debug-export-json', `/download/debug/database/table.json?name=${encoded}`],
        ['debug-export-csv', `/download/debug/database/table.csv?name=${encoded}`],
      ].forEach(([id, href]) => {
        const link = $(id);
        const enabled = !!tableName;
        link.href = enabled ? href : '#';
        link.setAttribute('aria-disabled', enabled ? 'false' : 'true');
      });
    }

    function renderDebugRows(data) {
      const rows = data.rows || [];
      const columnNames = (data.columns || []).map(column => column.name).filter(Boolean);
      $('debug-table-rows').innerHTML = renderDebugObjectTable(rows, columnNames);
    }

    function renderDebugObjectTable(rows, preferredKeys) {
      const keys = preferredKeys && preferredKeys.length
        ? preferredKeys
        : Array.from(rows.reduce((set, row) => {
            Object.keys(row || {}).forEach(key => set.add(key));
            return set;
          }, new Set()));
      if (!keys.length) return '<div class="ca-empty muted">No columns.</div>';
      const header = keys.map(key => `<th>${escapeHtml(key)}</th>`).join('');
      const body = rows.length
        ? rows.map(row => `<tr>${keys.map(key => `<td class="mono">${escapeHtml(debugCell(row ? row[key] : ''))}</td>`).join('')}</tr>`).join('')
        : `<tr><td colspan="${keys.length}" class="muted">No rows.</td></tr>`;
      return `<table><thead><tr>${header}</tr></thead><tbody>${body}</tbody></table>`;
    }

    function debugCell(value) {
      if (value === null || value === undefined) return 'NULL';
      if (Array.isArray(value)) return value.join(', ');
      if (typeof value === 'object') return JSON.stringify(value);
      return String(value);
    }

    async function updateWorkOrderChecklist(id, itemId, checked) {
      try {
        await api('/api/work-orders/checklist', {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ id, item_id: itemId, status: checked ? 'done' : 'pending' })
        });
        msg('work order updated');
        await refresh();
      } catch (e) {
        if (isAuthLost(e)) return;
        msg(e.message);
        await refresh().catch(refreshError => {
          if (!isAuthLost(refreshError)) msg(refreshError.message);
        });
      }
    }

    async function confirmWorkOrder(id) {
      const typed = prompt(`Type ${id} to confirm this work order`);
      if (typed !== id) return;
      try {
        await api('/api/work-orders/confirm', {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ id, confirm: typed })
        });
        msg('work order confirmed; local-hosts.tsv written');
        await refresh();
      } catch (e) {
        if (isAuthLost(e)) return;
        msg(e.message);
      }
    }

    function renderHosts() {
      const filter = $('filter').value.toLowerCase();
      $('hosts').innerHTML = state.hosts
        .slice()
        .sort((a, b) => String(a.fqdn || a.id || '').localeCompare(String(b.fqdn || b.id || '')))
        .filter(h => JSON.stringify(h).toLowerCase().includes(filter))
        .map(h => {
          const problems = state.problems.filter(p => p.host_id === h.id);
          const cls = problems.length ? 'warn' : 'ok';
          return `<tr data-id="${escapeHtml(h.id)}" data-host-fqdn="${escapeHtml(h.fqdn || '')}">
            <td><span class="pill canonical" title="${escapeHtml(h.fqdn || '')}">${escapeHtml(h.fqdn || '')}</span></td>
            <td>${escapeHtml(h.ip || '')}</td>
            <td>${renderHostAliasCell(h)}</td>
            <td>${(h.roles || []).map(n => `<span class="pill">${escapeHtml(n)}</span>`).join('')}</td>
            <td class="host-cert-cell">${renderHostCertificateCell(h)}</td>
            <td><span class="pill ${cls}">${escapeHtml(h.monitoring || '')}</span></td>
            <td>${escapeHtml(h.status || '')}</td>
            <td><div class="host-actions">
              <button type="button" class="iconbtn" data-edit="${escapeHtml(h.id)}" title="Edit ${escapeHtml(h.fqdn || h.id || '')}" aria-label="Edit ${escapeHtml(h.fqdn || h.id || '')}">
                <svg viewBox="0 0 24 24" aria-hidden="true"><path d="M12 20h9"/><path d="M16.5 3.5a2.1 2.1 0 1 1 3 3L7 19l-4 1 1-4z"/></svg>
              </button>
              <button type="button" class="iconbtn danger" data-host-delete="${escapeHtml(h.id)}" title="Delete ${escapeHtml(h.fqdn || h.id || '')}" aria-label="Delete ${escapeHtml(h.fqdn || h.id || '')}">
                <svg viewBox="0 0 24 24" aria-hidden="true"><path d="M3 6h18"/><path d="M8 6V4h8v2"/><path d="M19 6l-1 14H6L5 6"/><path d="M10 11v6"/><path d="M14 11v6"/></svg>
              </button>
            </div></td>
          </tr>`;
        }).join('');
      document.querySelectorAll('[data-edit]').forEach(button => button.addEventListener('click', () => {
        editHost(button.dataset.edit).catch(e => {
          if (!isAuthLost(e)) msg(e.message);
        });
      }));
      document.querySelectorAll('[data-host-delete]').forEach(button => button.addEventListener('click', () => {
        deleteHostInline(button.dataset.hostDelete || '').catch(e => {
          if (!isAuthLost(e)) msg(e.message);
        });
      }));
      document.querySelectorAll('[data-host-alias-remove]').forEach(button => button.addEventListener('click', () => {
        removeHostAlias(button.dataset.hostAliasRemove || '', button.dataset.hostAliasName || '').catch(e => {
          if (!isAuthLost(e)) msg(e.message);
        });
      }));
      document.querySelectorAll('[data-host-cert-select]').forEach(select => {
        select.addEventListener('change', () => {
          setHostCertificateFromSelect(select).catch(e => {
            if (!isAuthLost(e)) msg(e.message);
            select.value = select.dataset.currentCertificate || '';
          });
        });
      });
      document.querySelectorAll('[data-host-cert-issue]').forEach(button => {
        button.addEventListener('click', () => {
          issueHostCertificate(button.dataset.hostCertIssue || '', button.dataset.currentCertificate || '', button).catch(e => {
            if (!isAuthLost(e)) msg(e.message);
          });
        });
      });
      mountHostEditor();
    }

    function renderHostAliasCell(host) {
      const aliases = (host.aliases || []).map(name => `<span class="pill host-alias-pill">
        <span class="host-alias-label">${escapeHtml(name)}</span>
        <button type="button" class="host-alias-remove" data-host-alias-remove="${escapeHtml(host.fqdn || '')}" data-host-alias-name="${escapeHtml(name)}" title="Delete ${escapeHtml(name)}">x</button>
      </span>`).join('');
      return `<div class="host-alias-cell">
        <div class="host-alias-list">${aliases}</div>
      </div>`;
    }

    function renderHostCertificateCell(host) {
      const cert = host.certificate || {};
      const certId = host.certificate_id || certificateIdOf(cert) || '';
      const row = hostCertificateRow(host);
      const links = certId ? `<div class="vhost-cert-links">
        <a class="linkbtn" href="/download/ca/cert/${encodeURIComponent(certId)}.crt">crt</a>
        ${cert.has_private_key ? `<a class="linkbtn" href="/download/ca/key/${encodeURIComponent(certId)}.key">key</a>` : ''}
      </div>` : '';
      const validity = cert.not_after ? `<span class="muted vhost-cert-validity">${escapeHtml(certStatusLabel(daysUntil(cert.not_after)))}</span>` : '';
      return `<div class="vhost-cert">
        <div class="vhost-cert-main">
          <select class="vhost-cert-select" data-host-cert-select="${escapeHtml(host.fqdn || '')}" data-current-certificate="${escapeHtml(certId)}">
            ${renderCertificateOptions(certId, row)}
          </select>
          <button type="button" data-host-cert-issue="${escapeHtml(host.fqdn || '')}" data-current-certificate="${escapeHtml(certId)}">Issue</button>
        </div>
        <div class="vhost-cert-meta">${links}${validity}</div>
      </div>`;
    }

    function hostCertificateRow(host) {
      return {
        host_fqdn: host.fqdn || '',
        aliases: Array.isArray(host.aliases) ? host.aliases : [],
        derived_aliases: Array.isArray(host.derived_aliases) ? host.derived_aliases : [],
        certificate_id: host.certificate_id || '',
        certificate: host.certificate || null,
      };
    }

    function vhostRows() {
      if (state.vhosts && state.vhosts.length) return state.vhosts;
      return state.hosts.flatMap(host => (host.vhosts || []).map(vhost => ({
        vhost,
        host_id: host.id || '',
        host_fqdn: host.fqdn || '',
        derived_aliases: shortAliasForFqdn(vhost) ? [shortAliasForFqdn(vhost)] : [],
        monitoring: host.monitoring || '',
        status: host.status || '',
        certificate_id: '',
        certificate: null,
      })));
    }

    function renderVhosts() {
      const input = $('vhost-filter');
      const filter = input ? input.value.toLowerCase() : '';
      const rows = vhostRows()
        .sort((a, b) => String(a.vhost || '').localeCompare(String(b.vhost || '')))
        .filter(row => JSON.stringify(row).toLowerCase().includes(filter));
      $('vhost-stats').innerHTML = [
        ['shown', rows.length],
        ['total', vhostRows().length],
      ].map(([k, v]) => `<span class="stat">${escapeHtml(k)}: ${escapeHtml(String(v))}</span>`).join('');
      $('vhosts').innerHTML = rows.length ? rows.map(row => `<tr>
        <td>${renderVhostNameCell(row)}</td>
        <td>
          <div class="vhost-host">
            <select class="vhost-host-select" data-vhost-select="${escapeHtml(row.vhost)}" data-current-host="${escapeHtml(row.host_fqdn)}">
              ${renderVhostHostOptions(row.host_fqdn)}
            </select>
          </div>
        </td>
        <td>${renderVhostCertificateCell(row)}</td>
        <td><span class="pill">${escapeHtml(row.monitoring)}</span></td>
        <td>${escapeHtml(row.status)}</td>
      </tr>`).join('') : '<tr><td colspan="5" class="muted">No vhosts.</td></tr>';
      document.querySelectorAll('[data-vhost-select]').forEach(select => {
        select.addEventListener('change', () => {
          reassignVhostFromSelect(select).catch(e => {
            if (!isAuthLost(e)) msg(e.message);
            select.value = select.dataset.currentHost || '';
          });
        });
      });
      document.querySelectorAll('[data-vhost-delete]').forEach(button => {
        button.addEventListener('click', () => {
          deleteVhostInline(button.dataset.vhostDelete || '').catch(e => {
            if (!isAuthLost(e)) msg(e.message);
          });
        });
      });
      document.querySelectorAll('[data-vhost-cert-select]').forEach(select => {
        select.addEventListener('change', () => {
          setVhostCertificateFromSelect(select).catch(e => {
            if (!isAuthLost(e)) msg(e.message);
            select.value = select.dataset.currentCertificate || '';
          });
        });
      });
      document.querySelectorAll('[data-vhost-cert-issue]').forEach(button => {
        button.addEventListener('click', () => {
          issueVhostCertificate(button.dataset.vhostCertIssue || '', button.dataset.currentCertificate || '', button).catch(e => {
            if (!isAuthLost(e)) msg(e.message);
          });
        });
      });
    }

    function renderVhostNameCell(row) {
      const aliases = (row.derived_aliases || []).map(name => `<span class="pill derived vhost">${escapeHtml(name)}</span>`).join('');
      return `<div class="vhost-name-cell">
        <div class="vhost-name-main">
          <span class="pill vhost" title="${escapeHtml(row.vhost)}">${escapeHtml(row.vhost)}</span>
          <button type="button" class="vhost-delete" data-vhost-delete="${escapeHtml(row.vhost)}" title="Delete ${escapeHtml(row.vhost)}">Del</button>
        </div>
        ${aliases ? `<div class="vhost-pill-row">${aliases}</div>` : ''}
      </div>`;
    }

    function renderVhostCertificateCell(row) {
      const cert = row.certificate || {};
      const certId = row.certificate_id || cert.id || cert.name || '';
      const links = certId ? `<div class="vhost-cert-links">
        <a class="linkbtn" href="/download/ca/cert/${encodeURIComponent(certId)}.crt">crt</a>
        ${cert.has_private_key ? `<a class="linkbtn" href="/download/ca/key/${encodeURIComponent(certId)}.key">key</a>` : ''}
      </div>` : '';
      const validity = cert.not_after ? `<span class="muted vhost-cert-validity">${escapeHtml(certStatusLabel(daysUntil(cert.not_after)))}</span>` : '';
      return `<div class="vhost-cert">
        <div class="vhost-cert-main">
          <select class="vhost-cert-select" data-vhost-cert-select="${escapeHtml(row.vhost)}" data-current-certificate="${escapeHtml(certId)}">
            ${renderCertificateOptions(certId, row)}
          </select>
          <button type="button" data-vhost-cert-issue="${escapeHtml(row.vhost)}" data-current-certificate="${escapeHtml(certId)}">Issue</button>
        </div>
        <div class="vhost-cert-meta">${links}${validity}</div>
      </div>`;
    }

    function renderVhostEditor() {
      const select = $('vhost-new-host');
      const current = select.value || '';
      select.innerHTML = renderVhostHostOptions(current);
    }

    function renderVhostHostOptions(selectedHostFqdn) {
      return state.hosts
        .slice()
        .filter(host => (host.status || '') !== 'retired')
        .sort((a, b) => String(a.id || '').localeCompare(String(b.id || '')))
        .map(host => {
          const fqdn = host.fqdn || '';
          const selected = fqdn === selectedHostFqdn ? ' selected' : '';
          return `<option value="${escapeHtml(fqdn)}"${selected}>${escapeHtml(fqdn)}</option>`;
        }).join('');
    }

    function renderCertificateOptions(selectedCertificateId, row) {
      const byId = new Map();
      (state.certificates || []).forEach(cert => {
        const id = certificateIdOf(cert);
        if (id) byId.set(id, cert);
      });
      if (row && row.certificate) {
        const id = certificateIdOf(row.certificate);
        if (id && !byId.has(id)) byId.set(id, row.certificate);
      }
      const certs = Array.from(byId.values())
        .filter(cert => certMatchesRow(cert, row) || certificateIdOf(cert) === selectedCertificateId)
        .sort((a, b) => {
          const ar = certRelevance(a, row);
          const br = certRelevance(b, row);
          if (ar !== br) return ar - br;
          return String(a.name || a.id || '').localeCompare(String(b.name || b.id || ''));
        });
      const options = ['<option value="">no certificate</option>'].concat(certs.map(cert => {
        const id = certificateIdOf(cert);
        const label = compactCertificateLabel(cert, row);
        const selected = id === selectedCertificateId ? ' selected' : '';
        return `<option value="${escapeHtml(id)}"${selected}>${escapeHtml(label)}</option>`;
      }));
      return options.join('');
    }

    function certificateIdOf(cert) {
      return cert ? (cert.id || cert.name || '') : '';
    }

    function certDnsNames(cert) {
      return (cert && Array.isArray(cert.dns_names) ? cert.dns_names : [])
        .map(name => String(name || '').toLowerCase())
        .filter(Boolean);
    }

    function certRelevance(cert, row) {
      if (!row) return 9;
      const names = new Set(certDnsNames(cert));
      const id = String(certificateIdOf(cert)).toLowerCase();
      const commonName = String(cert.common_name || '').toLowerCase();
      const vhost = String(row.vhost || '').toLowerCase();
      const host = String(row.host_fqdn || row.fqdn || '').toLowerCase();
      const vhostShort = shortAliasForFqdn(vhost);
      const aliasNames = []
        .concat(Array.isArray(row.aliases) ? row.aliases : [])
        .concat(Array.isArray(row.derived_aliases) ? row.derived_aliases : [])
        .map(name => String(name || '').toLowerCase())
        .filter(Boolean);
      if (vhost) {
        if (names.has(vhost) || commonName === vhost || id.startsWith(vhost + '-')) return 0;
        if (host && (names.has(host) || commonName === host || String(cert.host_fqdn || '').toLowerCase() === host || id.startsWith(host + '-'))) return 1;
        if ((vhostShort && names.has(vhostShort)) || aliasNames.some(name => names.has(name) || commonName === name || id.startsWith(name + '-'))) return 2;
        return 9;
      }
      if (host && (names.has(host) || commonName === host || String(cert.host_fqdn || '').toLowerCase() === host || id.startsWith(host + '-'))) return 0;
      if (aliasNames.some(name => names.has(name) || commonName === name || id.startsWith(name + '-'))) return 1;
      return 9;
    }

    function certMatchesRow(cert, row) {
      return certRelevance(cert, row) < 9;
    }

    function compactCertificateLabel(cert, row) {
      const relevance = certRelevance(cert, row);
      const days = daysUntil(cert.not_after);
      const suffix = days === null ? '' : ` (${certStatusLabel(days)})`;
      const name = certificateDisplayName(cert);
      if (row && row.vhost) {
        if (relevance === 0) return `${name}${suffix}`;
        if (relevance === 1) return `host ${name}${suffix}`;
        if (relevance === 2) return `alias ${name}${suffix}`;
      } else {
        if (relevance === 0) return `${name}${suffix}`;
        if (relevance === 1) return `alias ${name}${suffix}`;
      }
      return `${shortCertificateName(cert)}${suffix}`;
    }

    function certificateDisplayName(cert) {
      const commonName = String(cert.common_name || '').trim();
      if (commonName) return commonName;
      const dnsNames = certDnsNames(cert);
      if (dnsNames.length) return dnsNames[0];
      return shortCertificateName(cert);
    }

    function shortCertificateName(cert) {
      const name = String(cert.common_name || cert.name || cert.id || '');
      const suffix = '.madagascar.xdev.ro';
      return name.endsWith(suffix) ? name.slice(0, -suffix.length) : name;
    }

    function shortAliasForFqdn(name) {
      const suffix = '.madagascar.xdev.ro';
      name = String(name || '').toLowerCase();
      return name.endsWith(suffix) ? name.slice(0, -suffix.length) : '';
    }

    function hostByFqdn(fqdn) {
      fqdn = String(fqdn || '').toLowerCase();
      return state.hosts.find(host => String(host.fqdn || '').toLowerCase() === fqdn) || null;
    }

    function hostUpsertPayload(host, overrides = {}) {
      const aliases = overrides.aliases !== undefined ? overrides.aliases : (host.aliases || []);
      const payload = {
        id: host.id || '',
        fqdn: host.fqdn || '',
        status: overrides.status !== undefined ? overrides.status : (host.status || 'active'),
        ip: overrides.ip !== undefined ? overrides.ip : (host.ip || ''),
        aliases,
        roles: Array.isArray(overrides.roles) ? overrides.roles : (host.roles || []),
        sources: [],
        monitoring: overrides.monitoring !== undefined ? overrides.monitoring : (host.monitoring || 'pending'),
        notes: overrides.notes !== undefined ? overrides.notes : (host.notes || ''),
      };
      if (overrides.vhosts !== undefined) payload.vhosts = overrides.vhosts;
      return payload;
    }

    function aliasEditorValues() {
      return (hostField('aliases').value || '')
        .split(/[\s,]+/)
        .map(value => String(value || '').trim().toLowerCase())
        .filter(Boolean);
    }

    function appendAliasInEditor() {
      const fqdn = String(hostField('fqdn').value || '').trim().toLowerCase();
      const derived = shortAliasForFqdn(fqdn);
      const alias = String(prompt(fqdn ? `Alias nou pentru ${fqdn}` : 'Alias nou', '') || '').trim().toLowerCase();
      if (!alias) return;
      if (fqdn && alias === fqdn) {
        msg('fqdn-ul hostului este deja numele principal');
        return;
      }
      if (derived && alias === derived) {
        msg('aliasul derivat din fqdn se genereaza automat');
        return;
      }
      const aliases = aliasEditorValues();
      if (aliases.includes(alias)) {
        msg(`aliasul ${alias} este deja in editor`);
        return;
      }
      hostField('aliases').value = aliases.concat(alias).join('\n');
      hostField('aliases').dispatchEvent(new Event('input', { bubbles: true }));
      hostField('aliases').focus();
    }

    async function addHostAlias(hostFqdn) {
      if (!await ensureAuthenticated('Autentifica-te inainte de salvare.')) return;
      const host = hostByFqdn(hostFqdn);
      if (!host) return;
      const alias = String(prompt(`Alias nou pentru ${host.fqdn}`, '') || '').trim().toLowerCase();
      if (!alias) return;
      if (alias === String(host.fqdn || '').toLowerCase()) {
        msg('fqdn-ul hostului este deja prezent');
        return;
      }
      const aliases = Array.from(new Set([...(host.aliases || []), alias]));
      await api('/api/hosts/upsert', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(hostUpsertPayload(host, { aliases })),
      });
      msg(`alias ${alias} adaugat pe ${host.fqdn}`);
      await refresh();
    }

    async function removeHostAlias(hostFqdn, alias) {
      if (!await ensureAuthenticated('Autentifica-te inainte de stergere.')) return;
      const host = hostByFqdn(hostFqdn);
      alias = String(alias || '').trim().toLowerCase();
      if (!host || !alias) return;
      if (!confirm(`Sterg aliasul ${alias} de pe ${host.fqdn}?`)) return;
      const aliases = (host.aliases || []).filter(name => String(name || '').toLowerCase() !== alias);
      await api('/api/hosts/upsert', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify(hostUpsertPayload(host, { aliases })),
      });
      msg(`alias ${alias} sters de pe ${host.fqdn}`);
      await refresh();
    }

    async function deleteHostInline(id) {
      if (!await ensureAuthenticated('Autentifica-te inainte de stergere.')) return;
      const host = state.hosts.find(entry => String(entry.id || '') === String(id || ''));
      if (!host) return;
      if (!confirm(`Delete ${host.fqdn || host.id || id}?`)) return;
      await api('/api/hosts/delete', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ id: host.id || id }),
      });
      if (hostEditorTarget === String(host.id || '')) closeHostForm(true);
      msg(`host ${host.fqdn || host.id || id} deleted`);
      await refresh();
    }

    async function setHostCertificateFromSelect(select) {
      if (!await ensureAuthenticated('Autentifica-te inainte de salvare.')) {
        select.value = select.dataset.currentCertificate || '';
        return;
      }
      const hostFqdn = select.dataset.hostCertSelect || '';
      const certificateId = select.value || '';
      const current = select.dataset.currentCertificate || '';
      if (!hostFqdn || certificateId === current) return;
      if (!certificateId && current && !confirm(`Sterg asocierea certificatului de pe ${hostFqdn}?`)) {
        select.value = current;
        return;
      }
      select.disabled = true;
      try {
        await api('/api/hosts/certificate', {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ host_fqdn: hostFqdn, certificate_id: certificateId }),
        });
        msg(certificateId ? `certificatul ${certificateId} asociat cu ${hostFqdn}` : `certificatul scos de pe ${hostFqdn}`);
        await refresh();
      } finally {
        select.disabled = false;
      }
    }

    async function issueHostCertificate(hostFqdn, currentCertificateId, button) {
      if (!await ensureAuthenticated('Autentifica-te inainte de emitere.')) return;
      if (!hostFqdn) return;
      if (currentCertificateId && !confirm(`Emitem un certificat nou pentru ${hostFqdn} si inlocuim asocierea curenta?`)) return;
      if (button) button.disabled = true;
      try {
        const result = await api('/api/hosts/issue-certificate', {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ host_fqdn: hostFqdn }),
        });
        msg(`certificatul ${result.certificate_id || ''} emis pentru ${hostFqdn}`);
        await refresh();
      } finally {
        if (button) button.disabled = false;
      }
    }

    async function reassignVhostFromSelect(select) {
      const vhost = select.dataset.vhostSelect || '';
      const fromHost = select.dataset.currentHost || '';
      const toHost = select.value || '';
      if (!vhost || !toHost || toHost === fromHost) return;
      select.disabled = true;
      try {
        await api('/api/vhosts/reassign', {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ vhost_fqdn: vhost, host_fqdn: toHost }),
        });
        msg(`vhost ${vhost} moved`);
        await refresh();
      } finally {
        select.disabled = false;
      }
    }

    async function addVhostInline() {
      if (!await ensureAuthenticated('Autentifica-te inainte de salvare.')) return;
      const nameInput = $('vhost-new-name');
      const hostSelect = $('vhost-new-host');
      const vhost = (nameInput.value || '').trim().toLowerCase();
      const hostFqdn = hostSelect.value || '';
      if (!vhost || !hostFqdn) {
        msg('completeaza vhost si host');
        return;
      }
      if (!isValidVhostName(vhost)) {
        msg('vhost invalid: foloseste un nume sub madagascar.xdev.ro');
        nameInput.focus();
        return;
      }
      if (state.hosts.some(host => (host.fqdn || '').toLowerCase() === vhost)) {
        msg('vhost invalid: numele este deja host real');
        nameInput.focus();
        return;
      }
      $('vhost-add').disabled = true;
      nameInput.disabled = true;
      hostSelect.disabled = true;
      try {
        await api('/api/vhosts/upsert', {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ vhost_fqdn: vhost, host_fqdn: hostFqdn }),
        });
        nameInput.value = '';
        msg(`vhost ${vhost} saved`);
        await refresh();
      } catch (e) {
        if (!isAuthLost(e)) msg(vhostErrorMessage(e));
      } finally {
        $('vhost-add').disabled = false;
        nameInput.disabled = false;
        hostSelect.disabled = false;
      }
    }

    function isValidVhostName(name) {
      name = String(name || '').trim().toLowerCase().replace(/\.$/, '');
      if (!(name === 'madagascar.xdev.ro' || name.endsWith('.madagascar.xdev.ro'))) return false;
      if (name.length > 253) return false;
      return name.split('.').every(label => /^[a-z0-9](?:[a-z0-9-]*[a-z0-9])?$/.test(label));
    }

    function vhostErrorMessage(error) {
      const code = error && error.code ? error.code : '';
      if (code === 'invalid_vhost') return 'vhost invalid: foloseste un nume sub madagascar.xdev.ro';
      if (code === 'vhost_matches_host') return 'vhost invalid: numele este deja host real';
      if (code === 'invalid_target_host') return 'host tinta invalid';
      if (code === 'missing_target_host') return 'alege hostul tinta';
      return error && error.message ? error.message : 'vhost add failed';
    }

    async function setVhostCertificateFromSelect(select) {
      if (!await ensureAuthenticated('Autentifica-te inainte de salvare.')) {
        select.value = select.dataset.currentCertificate || '';
        return;
      }
      const vhost = select.dataset.vhostCertSelect || '';
      const certificateId = select.value || '';
      const current = select.dataset.currentCertificate || '';
      if (!vhost || certificateId === current) return;
      if (!certificateId && current && !confirm(`Clear certificate from ${vhost}?`)) {
        select.value = current;
        return;
      }
      select.disabled = true;
      try {
        await api('/api/vhosts/certificate', {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ vhost_fqdn: vhost, certificate_id: certificateId }),
        });
        msg(certificateId ? `certificate ${certificateId} linked to ${vhost}` : `certificate cleared from ${vhost}`);
        await refresh();
      } finally {
        select.disabled = false;
      }
    }

    async function issueVhostCertificate(vhost, currentCertificateId, button) {
      if (!await ensureAuthenticated('Autentifica-te inainte de emitere.')) return;
      if (!vhost) return;
      if (currentCertificateId && !confirm(`Issue a new certificate for ${vhost} and replace the current association?`)) return;
      if (button) button.disabled = true;
      try {
        const result = await api('/api/vhosts/issue-certificate', {
          method: 'POST',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({ vhost_fqdn: vhost }),
        });
        msg(`certificate ${result.certificate_id || ''} issued for ${vhost}`);
        await refresh();
      } finally {
        if (button) button.disabled = false;
      }
    }

    async function deleteVhostInline(vhost) {
      if (!await ensureAuthenticated('Autentifica-te inainte de stergere.')) return;
      if (!vhost || !confirm(`Delete ${vhost}?`)) return;
      await api('/api/vhosts/delete', {
        method: 'POST',
        headers: { 'Content-Type': 'application/json' },
        body: JSON.stringify({ vhost_fqdn: vhost, confirm: vhost }),
      });
      msg(`vhost ${vhost} deleted`);
      await refresh();
    }

    async function editHost(id) {
      if (!await ensureAuthenticated('Autentifica-te inainte de editare.')) return;
      const host = state.hosts.find(h => h.id === id);
      if (!host) return;
      if (!canSwitchHostEditor(id)) return;
      clearHostFormMessage();
      for (const key of ['id', 'fqdn', 'status', 'ip', 'monitoring', 'notes']) hostField(key).value = host[key] || '';
      hostField('aliases').value = (host.aliases || []).join('\n');
      hostField('roles').value = (host.roles || []).join(' ');
      activateHostForm(`Edit host ${host.fqdn || host.id || ''}`.trim(), 'edit', id, 'fqdn');
    }

    async function newHost() {
      if (!await ensureAuthenticated('Autentifica-te inainte de adaugarea unui host.')) return;
      if (!canSwitchHostEditor('__new__')) return;
      resetHostForm(true);
      activateHostForm('New host', 'new', '__new__', 'id');
    }

    function activateHostForm(title, mode, target, focusField = 'id', scroll = true) {
      hostFormMode = mode || 'new';
      hostEditorTarget = target || '';
      hostFormTitle.textContent = title || 'New host';
      syncHostFormActions();
      renderHosts();
      hostFormSnapshot = hostFormState();
      if (scroll) hostEditorRow.scrollIntoView({ block: 'nearest', behavior: 'smooth' });
      hostField(focusField).focus();
    }

    function resetHostForm(force = false) {
      if (hostFormBusy && !force) return;
      hostForm.reset();
      clearHostFormMessage();
      hostField('status').value = 'active';
      hostField('monitoring').value = 'pending';
      hostFormSnapshot = force ? '' : hostFormState();
    }

    function closeHostForm(force = false) {
      if (hostFormBusy && !force) return;
      if (!force && hostFormDirty() && !confirm('Discard unsaved host changes?')) return;
      hostEditorTarget = '';
      hostFormMode = 'new';
      hostFormSnapshot = '';
      clearHostFormMessage();
      syncHostFormActions();
      mountHostEditor();
    }

    function canSwitchHostEditor(target) {
      if (hostFormBusy) return false;
      if (!hostEditorTarget) return true;
      if (!hostFormDirty()) return true;
      if (hostEditorTarget === target) return confirm('Discard unsaved host changes and reload this editor?');
      return confirm('Discard unsaved host changes?');
    }

    function mountHostEditor() {
      hostEditorRow.remove();
      if (!hostEditorTarget) {
        hostFormShell.hidden = true;
        return;
      }
      hostEditorCell.colSpan = 8;
      const tbody = $('hosts');
      if (!tbody) return;
      if (hostEditorTarget === '__new__') {
        tbody.prepend(hostEditorRow);
      } else {
        const rows = Array.from(tbody.querySelectorAll('tr[data-id]'));
        const targetRow = rows.find(row => row.dataset.id === hostEditorTarget);
        if (targetRow) targetRow.after(hostEditorRow);
        else tbody.prepend(hostEditorRow);
      }
      hostFormShell.hidden = false;
    }

    function hostField(name) {
      return hostForm.elements.namedItem(name);
    }

    function hostFormState() {
      return JSON.stringify(formObject(hostForm));
    }

    function hostFormDirty() {
      return !!hostFormSnapshot && hostFormState() !== hostFormSnapshot;
    }

    function setHostFormBusy(busy) {
      hostFormBusy = !!busy;
      syncHostFormActions();
    }

    function syncHostFormActions() {
      saveHostButton.disabled = hostFormBusy;
      deleteHostButton.disabled = hostFormBusy || hostFormMode !== 'edit';
      cancelHostButton.disabled = hostFormBusy;
      hostAddAliasEditorButton.disabled = hostFormBusy;
    }

    function setHostFormMessage(text, isError = false) {
      hostFormMessage.textContent = text || '';
      hostFormMessage.classList.toggle('error', !!isError);
    }

    function clearHostFormMessage() {
      setHostFormMessage('');
    }

    function formObject(form) {
      return Object.fromEntries(new FormData(form).entries());
    }

    function escapeHtml(value) {
      value = value == null ? '' : String(value);
      return value.replace(/[&<>"']/g, ch => ({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;',"'":'&#039;'}[ch]));
    }

    const ACCOUNT_STORAGE_KEY = 'mla_last_account';

    // OTP digit boxes — auto-advance, backspace, paste
    const otpDigits = Array.from(document.querySelectorAll('.otp-digit'));
    const otpHidden = $('otp-hidden');
    const loginAccount = $('login-account');

    if (loginAccount) {
      const rememberedAccount = localStorage.getItem(ACCOUNT_STORAGE_KEY);
      if (rememberedAccount && !loginAccount.value) loginAccount.value = rememberedAccount;
      loginAccount.addEventListener('input', () => {
        const value = (loginAccount.value || '').trim();
        if (value) localStorage.setItem(ACCOUNT_STORAGE_KEY, value);
      });
    }

    function setOtpDigit(idx, value) {
      const digit = (value || '').replace(/\D/g, '').slice(0, 1);
      otpDigits[idx].value = digit;
      otpDigits[idx].classList.toggle('filled', !!digit);
    }

    // Move focus to the next empty box: forward from idx, then wrapping to the
    // start. This lets out-of-order entry continue (e.g. after the last box,
    // jump back to the first still-empty box). Stays put when all boxes are full.
    function advanceFocus(idx) {
      for (let i = idx + 1; i < otpDigits.length; i++) {
        if (!otpDigits[i].value) { otpDigits[i].focus(); return; }
      }
      for (let i = 0; i <= idx; i++) {
        if (!otpDigits[i].value) { otpDigits[i].focus(); return; }
      }
    }

    // Spread multiple digits across boxes starting at startIdx. Used for paste
    // and for Safari OTP autofill, which drops the whole code into the first box.
    function fillOtp(text, startIdx = 0) {
      const digits = (text || '').replace(/\D/g, '').split('');
      if (!digits.length) return;
      let last = startIdx;
      for (let i = 0; i < digits.length && startIdx + i < otpDigits.length; i++) {
        last = startIdx + i;
        setOtpDigit(last, digits[i]);
      }
      syncOtpFields();
      advanceFocus(last);
      maybeSubmitOtp();
    }

    function getOtp() { return otpDigits.map(i => i.value).join(''); }
    function syncOtpFields() { if (otpHidden) otpHidden.value = getOtp(); }
    function otpReady() { return otpDigits.every(i => /^\d$/.test(i.value)); }
    function maybeSubmitOtp() {
      if (otpReady()) setTimeout(() => $('login-form').requestSubmit(), 300);
    }
    function clearOtp() {
      otpDigits.forEach(i => { i.value = ''; i.classList.remove('filled'); });
      if (otpHidden) otpHidden.value = '';
      // Same conditional focus as on load: don't steal focus to the OTP boxes for
      // an unknown operator, so Safari's autofill anchor on the username stays.
      if (loginAccount && !loginAccount.value) loginAccount.focus();
      else otpDigits[0].focus();
    }

    otpDigits.forEach((input, idx) => {
      input.addEventListener('input', () => {
        $('login-error').textContent = '';
        // A single box may receive several digits at once (autofill / typing fast).
        if (input.value.replace(/\D/g, '').length > 1) {
          fillOtp(input.value, idx);
          return;
        }
        setOtpDigit(idx, input.value);
        syncOtpFields();
        if (input.value) advanceFocus(idx);
        maybeSubmitOtp();
      });

      input.addEventListener('paste', (e) => {
        e.preventDefault();
        $('login-error').textContent = '';
        const text = (e.clipboardData || window.clipboardData).getData('text');
        fillOtp(text, idx);
      });

      input.addEventListener('keydown', (e) => {
        if (e.key === 'Backspace') {
          e.preventDefault();
          $('login-error').textContent = '';
          if (input.value) { setOtpDigit(idx, ''); }
          else if (idx > 0) { setOtpDigit(idx - 1, ''); otpDigits[idx - 1].focus(); }
          syncOtpFields();
        } else if (e.key === 'ArrowLeft' && idx > 0) {
          e.preventDefault();
          otpDigits[idx - 1].focus();
        } else if (e.key === 'ArrowRight' && idx < otpDigits.length - 1) {
          e.preventDefault();
          otpDigits[idx + 1].focus();
        }
      });
    });

    // Focus the first OTP box only for a returning operator (username known).
    // For an unknown operator, leave focus on the username field so Safari can
    // present its OTP autofill anchored there without being dismissed by a focus
    // change (pbx-admin pattern).
    if (loginAccount && loginAccount.value) otpDigits[0].focus();
    else if (loginAccount) loginAccount.focus();
    else otpDigits[0].focus();

    document.querySelectorAll('[data-page-link]').forEach(link => {
      link.addEventListener('click', async (event) => {
        event.preventDefault();
        if (!await ensureAuthenticated('Autentifica-te pentru a schimba sectiunea.')) return;
        showPage(link.dataset.pageLink, true);
      });
    });

    window.addEventListener('popstate', () => {
      ensureAuthenticated('Autentifica-te pentru a schimba sectiunea.')
        .then(authenticated => { if (authenticated) showPage(currentPage()); })
        .catch(e => { if (!isAuthLost(e)) msg(e.message); });
    });

    async function copyText(text) {
      if (navigator.clipboard && window.isSecureContext) {
        await navigator.clipboard.writeText(text);
        return;
      }
      const input = document.createElement('textarea');
      input.value = text;
      input.setAttribute('readonly', '');
      input.style.position = 'fixed';
      input.style.left = '-10000px';
      document.body.appendChild(input);
      input.select();
      document.execCommand('copy');
      document.body.removeChild(input);
    }

    $('copy-build').addEventListener('click', async () => {
      try {
        await copyText($('copy-build').dataset.buildDetails || '');
        if (state.authenticated) msg('build details copied');
      } catch (e) {
        if (state.authenticated) msg('copy failed');
      }
    });

    $('login-form').addEventListener('submit', async (event) => {
      event.preventDefault();
      if (!otpReady() || $('login-form').classList.contains('busy')) return;
      $('login-form').classList.add('busy');
      $('login-error').textContent = '';
      try {
        await api('/api/login', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ otp: getOtp() }) });
        await refresh();
      } catch (e) {
        showLogin(e.message === 'invalid_otp' ? 'Cod incorect.' : e.message);
      } finally {
        $('login-form').classList.remove('busy');
      }
    });

    $('logout').addEventListener('click', async () => {
      await api('/api/logout', { method: 'POST' }).catch(() => {});
      window.location.replace('/?logged_out=' + Date.now());
    });

    $('refresh').addEventListener('click', () => refresh().catch(e => {
      if (!isAuthLost(e)) msg(e.message);
    }));
    $('filter').addEventListener('input', renderHosts);
    $('vhost-filter').addEventListener('input', renderVhosts);
    $('vhost-add').addEventListener('click', () => {
      addVhostInline().catch(e => {
        if (!isAuthLost(e)) msg(e.message);
      });
    });
    $('vhost-new-name').addEventListener('keydown', (event) => {
      if (event.key !== 'Enter') return;
      event.preventDefault();
      addVhostInline().catch(e => {
        if (!isAuthLost(e)) msg(e.message);
      });
    });
    $('new-host').addEventListener('click', () => {
      newHost().catch(e => {
        if (!isAuthLost(e)) msg(e.message);
      });
    });
    $('debug-db-refresh').addEventListener('click', () => renderDebugDatabase().catch(e => {
      if (!isAuthLost(e)) msg(e.message);
    }));
    hostAddAliasEditorButton.addEventListener('click', appendAliasInEditor);
    cancelHostButton.addEventListener('click', () => closeHostForm());

    hostForm.addEventListener('submit', async (event) => {
      event.preventDefault();
      if (!await ensureAuthenticated('Autentifica-te inainte de salvare. Modificarile raman in formular.')) return;
      setHostFormBusy(true);
      setHostFormMessage('Saving...');
      try {
        await api('/api/hosts/upsert', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify(formObject(event.target)) });
        msg('host saved');
        await refresh();
        closeHostForm(true);
      } catch (e) {
        if (isAuthLost(e)) return;
        setHostFormMessage(e.message, true);
        msg(e.message);
      } finally {
        setHostFormBusy(false);
      }
    });

    hostForm.addEventListener('invalid', (event) => {
      setHostFormMessage('Complete the required host fields before saving.', true);
    }, true);

    hostForm.addEventListener('input', () => {
      if (hostFormMessage.classList.contains('error')) clearHostFormMessage();
    });

    deleteHostButton.addEventListener('click', async () => {
      const id = hostField('id').value;
      if (!id || !confirm(`Delete ${id}?`)) return;
      setHostFormBusy(true);
      setHostFormMessage('Deleting...');
      try {
        await api('/api/hosts/delete', { method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ id }) });
        msg('host deleted');
        await refresh();
        closeHostForm(true);
      } catch (e) {
        if (isAuthLost(e)) return;
        setHostFormMessage(e.message, true);
        msg(e.message);
      } finally {
        setHostFormBusy(false);
      }
    });

    resetHostForm(true);
    closeHostForm(true);

    $('write-tsv').addEventListener('click', async () => {
      if (!confirm('Write config/local-hosts.tsv from the runtime registry?')) return;
      try {
        await api('/api/render/local-hosts-tsv', { method: 'POST' });
        msg('local-hosts.tsv written');
      } catch (e) {
        if (!isAuthLost(e)) msg(e.message);
      }
    });

    refresh().catch(e => {
      if (!isAuthLost(e)) showLogin(e.message);
    });
  </script>
</body>
</html>
HTML
    $html =~ s/__HOST_MANAGER_BUILD_TITLE__/$build_title/g;
    $html =~ s/__HOST_MANAGER_BUILD__/$build/g;
    $html =~ s/__HOST_MANAGER_BUILD_DETAILS__/$build_details/g;
    return $html;
}