<?php
declare(strict_types=1);

require_once __DIR__ . '/config.php';

session_name('delivery_admin_sid');
session_start();

function db(): PDO
{
    static $pdo = null;
    if ($pdo instanceof PDO) {
        return $pdo;
    }
    $dsn = 'mysql:host=' . DB_HOST . ';port=' . DB_PORT . ';dbname=' . DB_NAME . ';charset=utf8mb4';
    $pdo = new PDO($dsn, DB_USER, DB_PASS, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_PERSISTENT => true,
    ]);
    return $pdo;
}

function public_delivery_db(): PDO
{
    static $pdo = null;
    if ($pdo instanceof PDO) {
        return $pdo;
    }
    $dsn = 'mysql:host=' . DELIVERY_PUBLIC_DB_HOST . ';port=' . DELIVERY_PUBLIC_DB_PORT . ';dbname=' . DELIVERY_PUBLIC_DB_NAME . ';charset=utf8mb4';
    $pdo = new PDO($dsn, DELIVERY_PUBLIC_DB_USER, DELIVERY_PUBLIC_DB_PASS, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
    return $pdo;
}

function delivery_public_sync_url(): string
{
    $value = getenv('DELIVERY_PUBLIC_SYNC_URL');
    return $value !== false && $value !== '' ? $value : DELIVERY_PUBLIC_SYNC_URL;
}

function delivery_public_sync_secret(): string
{
    $value = getenv('DELIVERY_PUBLIC_SYNC_SECRET');
    return $value !== false && $value !== '' ? $value : DELIVERY_PUBLIC_SYNC_SECRET;
}

function ensure_public_delivery_schema(): void
{
    static $done = false;
    if ($done) {
        return;
    }
    $pdo = public_delivery_db();
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS deliveries (
            id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
            token VARCHAR(64) NOT NULL UNIQUE,
            access_password VARCHAR(64) NOT NULL,
            access_password_hash VARCHAR(255) NOT NULL,
            gameclub_url TEXT NULL,
            account_count INT NOT NULL,
            criteria_json JSON NULL,
            status VARCHAR(20) NOT NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            expires_at DATETIME NULL,
            synced_at DATETIME NOT NULL,
            INDEX idx_status_created (status, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
    );
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS delivery_accounts (
            row_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            delivery_id BIGINT UNSIGNED NOT NULL,
            account_index INT NOT NULL,
            account_id VARCHAR(255) NOT NULL,
            pass_value TEXT NULL,
            mail TEXT NULL,
            mail_pass TEXT NULL,
            auth_token LONGTEXT NULL,
            twofa TEXT NULL,
            bk_code TEXT NULL,
            moved_at DATETIME NULL,
            delivery_status VARCHAR(20) NOT NULL DEFAULT 'reserved',
            UNIQUE KEY uq_delivery_account (delivery_id, account_index),
            INDEX idx_delivery (delivery_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
    );
    ensure_public_delivery_column('delivery_accounts', 'delivery_status', "VARCHAR(20) NOT NULL DEFAULT 'reserved'");
    $done = true;
}

function ensure_public_delivery_column(string $table, string $column, string $definition): void
{
    $pdo = public_delivery_db();
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?'
    );
    $stmt->execute([$table, $column]);
    if ((int)$stmt->fetchColumn() === 0) {
        $pdo->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
    }
}

function h(?string $value): string
{
    return htmlspecialchars((string)$value, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

function delivery_datetime_jst(?string $value): string
{
    if (!$value) {
        return '-';
    }
    return (new DateTimeImmutable($value, new DateTimeZone('UTC')))
        ->setTimezone(new DateTimeZone('Asia/Tokyo'))
        ->format('Y/m/d H:i') . ' JST';
}

function delivery_is_expired(?string $value): bool
{
    if (!$value) {
        return false;
    }
    $expiresAt = new DateTimeImmutable($value, new DateTimeZone('UTC'));
    $now = new DateTimeImmutable('now', new DateTimeZone('UTC'));
    return $expiresAt <= $now;
}

function admin_password(): string
{
    $env = getenv('DELIVERY_ADMIN_PASSWORD');
    return $env !== false && $env !== '' ? $env : DEFAULT_ADMIN_PASSWORD;
}

function stored_admin_password_hash(): ?string
{
    static $hash = false;
    if ($hash !== false) {
        return $hash ?: null;
    }

    $stmt = db()->query("SELECT password_hash FROM delivery_admin_credentials WHERE credential_id = 1");
    $value = $stmt->fetchColumn();
    $hash = is_string($value) && $value !== '' ? $value : '';
    return $hash ?: null;
}

function admin_password_matches(string $password): bool
{
    $hash = stored_admin_password_hash();
    return $hash !== null ? password_verify($password, $hash) : hash_equals(admin_password(), $password);
}

function change_admin_password(string $currentPassword, string $newPassword, string $confirmation): void
{
    if (!admin_password_matches($currentPassword)) {
        throw new RuntimeException('現在のパスワードが正しくありません。');
    }
    if (strlen($newPassword) < 12) {
        throw new RuntimeException('新しいパスワードは12文字以上で入力してください。');
    }
    if ($newPassword !== $confirmation) {
        throw new RuntimeException('新しいパスワードと確認用パスワードが一致しません。');
    }

    $stmt = db()->prepare(
        'INSERT INTO delivery_admin_credentials (credential_id, password_hash)
         VALUES (1, ?)
         ON DUPLICATE KEY UPDATE password_hash = VALUES(password_hash), updated_at = CURRENT_TIMESTAMP'
    );
    $stmt->execute([password_hash($newPassword, PASSWORD_DEFAULT)]);
}

function is_admin(): bool
{
    return !empty($_SESSION['delivery_admin']);
}

function require_admin(): void
{
    if (!is_admin()) {
        header('Location: /delivery_admin/?login=1');
        exit;
    }
}

function random_token(int $bytes = 12): string
{
    return rtrim(strtr(base64_encode(random_bytes($bytes)), '+/', '-_'), '=');
}

function random_password(): string
{
    return (string)random_int(100000, 999999);
}

function ensure_schema(): void
{
    static $done = false;
    if ($done) {
        return;
    }
    $done = true;
    if (!DELIVERY_SCHEMA_AUTO_MIGRATE) {
        return;
    }
    $pdo = db();
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS deliveries (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            token VARCHAR(64) NOT NULL UNIQUE,
            access_password VARCHAR(64) NOT NULL,
            access_password_hash VARCHAR(255) NOT NULL,
            gameclub_url TEXT NULL,
            account_count INT NOT NULL,
            criteria_json JSON NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'active',
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            expires_at DATETIME NULL,
            INDEX idx_status_created (status, created_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
    );
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS delivery_filter_presets (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL UNIQUE,
            filters_json JSON NOT NULL,
            is_default TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
    );
    ensure_column('delivery_filter_presets', 'is_default', 'TINYINT(1) NOT NULL DEFAULT 0');
    $pdo->exec(
        "CREATE TABLE IF NOT EXISTS delivery_public_sync_queue (
            delivery_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
            attempts INT UNSIGNED NOT NULL DEFAULT 0,
            last_error TEXT NULL,
            next_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            INDEX idx_next_attempt (next_attempt_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
    );

    $exists = (int)$pdo->query("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'acc_rep_delivery'")->fetchColumn();
    if ($exists === 0) {
        $pdo->exec("CREATE TABLE acc_rep_delivery LIKE acc_rep");
    }
    ensure_column('acc_rep_delivery', 'delivery_id', 'BIGINT UNSIGNED NULL');
    ensure_column('acc_rep_delivery', 'source_table', "VARCHAR(64) NOT NULL DEFAULT 'acc_rep'");
    ensure_column('acc_rep_delivery', 'moved_at', 'DATETIME NULL');
    ensure_column('acc_rep_delivery', 'delivery_status', "VARCHAR(20) NOT NULL DEFAULT 'reserved'");
    $pdo->exec('UPDATE deliveries SET expires_at = DATE_ADD(created_at, INTERVAL ' . DELIVERY_ACCESS_DAYS . ' DAY) WHERE expires_at IS NULL');
}

function ensure_column(string $table, string $column, string $definition): void
{
    $pdo = db();
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = ? AND column_name = ?'
    );
    $stmt->execute([$table, $column]);
    if ((int)$stmt->fetchColumn() === 0) {
        $pdo->exec("ALTER TABLE `{$table}` ADD COLUMN `{$column}` {$definition}");
    }
}

function acc_rep_columns(): array
{
    $pdo = db();
    $sourceColumns = array_map(
        static fn(array $row): string => $row['Field'],
        $pdo->query('SHOW COLUMNS FROM acc_rep')->fetchAll()
    );
    $deliveryColumns = array_flip(array_map(
        static fn(array $row): string => $row['Field'],
        $pdo->query('SHOW COLUMNS FROM acc_rep_delivery')->fetchAll()
    ));

    // The account tables can be extended independently during operations.
    // Only copy columns currently available on both sides so issuance remains usable.
    return array_values(array_filter(
        $sourceColumns,
        static fn(string $column): bool => isset($deliveryColumns[$column])
    ));
}

function move_accounts_to_delivery(PDO $pdo, array $accounts, array $columns, int $deliveryId, string $deliveryStatus): array
{
    if (!$accounts) {
        return [];
    }

    $quotedColumns = array_map(static fn(string $column): string => "`{$column}`", $columns);
    $insertColumns = array_merge($quotedColumns, ['`delivery_id`', '`source_table`', '`moved_at`', '`delivery_status`']);
    $rowPlaceholders = '(' . rtrim(str_repeat('?,', count($insertColumns)), ',') . ')';
    $values = [];
    $ids = [];
    $movedAt = date('Y-m-d H:i:s');

    foreach ($accounts as $account) {
        foreach ($columns as $column) {
            $values[] = $account[$column] ?? null;
        }
        $values[] = $deliveryId;
        $values[] = 'acc_rep';
        $values[] = $movedAt;
        $values[] = $deliveryStatus;
        $ids[] = $account['id'];
    }

    $sql = 'INSERT INTO acc_rep_delivery (' . implode(',', $insertColumns) . ') VALUES '
        . implode(',', array_fill(0, count($accounts), $rowPlaceholders));
    $pdo->prepare($sql)->execute($values);

    return $ids;
}

function normalize_filters(array $input): array
{
    $filters = [
        'status' => '0',
        'shadow' => '',
        'shadow_saisin' => '0',
        'bio_mode' => '',
        'mail_server_mode' => '',
        'follow_min' => '',
        'follow_max' => '',
        'follower_min' => '',
        'follower_max' => '',
    ];

    foreach ($filters as $key => $_) {
        if (isset($input[$key])) {
            $filters[$key] = trim((string)$input[$key]);
        }
    }

    foreach (['shadow', 'shadow_saisin', 'status'] as $key) {
        if (!in_array($filters[$key], ['', '0', '1', '3', '99'], true)) {
            $filters[$key] = '';
        }
    }

    if (!in_array($filters['mail_server_mode'], ['', 'OK', 'OTHER'], true)) {
        $filters['mail_server_mode'] = '';
    }
    if (!in_array($filters['bio_mode'], ['', 'OK', 'OTHER'], true)) {
        $filters['bio_mode'] = '';
    }

    foreach (['follow_min', 'follow_max', 'follower_min', 'follower_max'] as $key) {
        if ($filters[$key] !== '' && !preg_match('/^\d+$/', $filters[$key])) {
            $filters[$key] = '';
        }
    }

    return $filters;
}

function default_criteria_sql(array $filters = []): string
{
    $filters = normalize_filters($filters);
    $conditions = [
        "pass IS NOT NULL AND pass != ''",
        "mail IS NOT NULL AND mail != ''",
        "auth_token IS NOT NULL AND auth_token != ''",
        "status != 99",
    ];

    if ($filters['shadow'] !== '') {
        $conditions[] = 'shadow = ' . (int)$filters['shadow'];
    }
    if ($filters['status'] !== '') {
        $conditions[] = 'status = ' . (int)$filters['status'];
    }
    if ($filters['shadow_saisin'] !== '') {
        $conditions[] = 'shadow_saisin = ' . (int)$filters['shadow_saisin'];
    }
    if ($filters['mail_server_mode'] === 'OK') {
        $conditions[] = "mail_server = 'OK'";
    } elseif ($filters['mail_server_mode'] === 'OTHER') {
        $conditions[] = "(mail_server IS NULL OR mail_server != 'OK')";
    }
    if ($filters['bio_mode'] === 'OK') {
        $conditions[] = "bio = 'ok'";
    } elseif ($filters['bio_mode'] === 'OTHER') {
        $conditions[] = "(bio IS NULL OR bio != 'ok')";
    }

    foreach (['follow', 'follower'] as $column) {
        $minKey = $column . '_min';
        $maxKey = $column . '_max';
        if ($filters[$minKey] !== '') {
            $conditions[] = "`{$column}` >= " . (int)$filters[$minKey];
        }
        if ($filters[$maxKey] !== '') {
            $conditions[] = "`{$column}` <= " . (int)$filters[$maxKey];
        }
    }

    return implode("\n        AND ", $conditions);
}

function build_delivery_message(array $delivery): string
{
    $url = DELIVERY_PUBLIC_BASE . '/' . $delivery['token'];
    return "ご購入ありがとうございます。\n"
        . "以下の専用納品ページよりアカウント情報をご確認ください。\n\n"
        . "管理番号: " . delivery_management_no((int)$delivery['id']) . "\n\n"
        . "納品ページ:\n{$url}\n\n"
        . "パスワード:\n{$delivery['access_password']}\n\n"
        . "メール認証コードもページ内のボタンから取得できます。\n"
        . "ログイン確認後、受取評価をお願いいたします。";
}

function delivery_management_no(int $deliveryId): string
{
    return 'DL-' . str_pad((string)$deliveryId, 6, '0', STR_PAD_LEFT);
}

function delivery_id_from_management_no(string $value): ?int
{
    $value = trim($value);
    if (preg_match('/^(?:DL-?)?(\d+)$/i', $value, $matches) !== 1) {
        return null;
    }
    $id = (int)$matches[1];
    return $id > 0 ? $id : null;
}

function delivery_search_condition(string $search): array
{
    $search = trim($search);
    if ($search === '') {
        return ['', []];
    }

    $managementId = delivery_id_from_management_no($search);
    $urlMatch = '%' . $search . '%';
    if ($managementId !== null) {
        return [' AND (id = ? OR gameclub_url LIKE ?)', [$managementId, $urlMatch]];
    }
    return [' AND gameclub_url LIKE ?', [$urlMatch]];
}

function count_matching_accounts(array $filters = []): int
{
    $criteria = default_criteria_sql($filters);
    return (int)db()->query("SELECT COUNT(*) FROM acc_rep WHERE {$criteria}")->fetchColumn();
}

function list_filter_presets(): array
{
    ensure_schema();
    return db()->query('SELECT * FROM delivery_filter_presets ORDER BY is_default DESC, updated_at DESC, id DESC')->fetchAll();
}

function save_filter_preset(string $name, array $filters): void
{
    $name = trim($name);
    if ($name === '') {
        throw new RuntimeException('プリセット名を入力してください。');
    }
    if (mb_strlen($name) > 100) {
        throw new RuntimeException('プリセット名は100文字以内で入力してください。');
    }
    $isDefault = ((string)($filters['preset_default'] ?? '') === '1');
    $filters = normalize_filters($filters);
    $json = json_encode($filters, JSON_UNESCAPED_UNICODE);
    if ($isDefault) {
        db()->exec('UPDATE delivery_filter_presets SET is_default = 0 WHERE is_default = 1');
    }
    $stmt = db()->prepare(
        'INSERT INTO delivery_filter_presets (name, filters_json, is_default)
         VALUES (?, ?, ?)
         ON DUPLICATE KEY UPDATE filters_json = VALUES(filters_json), is_default = VALUES(is_default), updated_at = CURRENT_TIMESTAMP'
    );
    $stmt->execute([$name, $json, $isDefault ? 1 : 0]);
}

function delete_filter_preset(int $id): void
{
    ensure_schema();
    $stmt = db()->prepare('DELETE FROM delivery_filter_presets WHERE id = ?');
    $stmt->execute([$id]);
}

function create_delivery(string $gameclubUrl, int $count, array $filters = []): array
{
    if ($count < 1 || $count > 200) {
        throw new RuntimeException('アカウント数は1から200の範囲で指定してください。');
    }

    ensure_schema();
    $pdo = db();
    $token = random_token();
    $password = random_password();
    $expiresAt = (new DateTimeImmutable('now', new DateTimeZone('UTC')))->modify('+' . DELIVERY_ACCESS_DAYS . ' days')->format('Y-m-d H:i:s');
    $filters = normalize_filters($filters);
    $criteria = default_criteria_sql($filters);
    $columns = acc_rep_columns();

    $pdo->beginTransaction();
    try {
        $normalizedUrl = rtrim($gameclubUrl, '/');
        $duplicateStmt = $pdo->prepare(
            "SELECT id FROM deliveries
             WHERE status = 'active'
               AND gameclub_url IN (?, ?, ?)
             ORDER BY id DESC
             LIMIT 1
             FOR UPDATE"
        );
        $duplicateStmt->execute([$gameclubUrl, $normalizedUrl, $normalizedUrl . '/']);
        $duplicateId = $duplicateStmt->fetchColumn();
        if ($duplicateId !== false) {
            throw new RuntimeException(
                'このチャットURLには有効な納品ページがすでにあります（' . delivery_management_no((int)$duplicateId) . '）。既存ページをキャンセルしてから再発行してください。'
            );
        }

        $stmt = $pdo->prepare(
            "SELECT * FROM acc_rep
             WHERE {$criteria}
             ORDER BY COALESCE(shadow_checked_at, '1970-01-01') ASC, RAND()
             LIMIT {$count}
             FOR UPDATE"
        );
        $stmt->execute();
        $accounts = $stmt->fetchAll();
        if (count($accounts) < $count) {
            throw new RuntimeException('条件に合う在庫が足りません。取得可能: ' . count($accounts) . ' 件');
        }

        $deliveryStmt = $pdo->prepare(
            'INSERT INTO deliveries
             (token, access_password, access_password_hash, gameclub_url, account_count, criteria_json, status, expires_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
        );
        $deliveryStmt->execute([
            $token,
            $password,
            password_hash($password, PASSWORD_DEFAULT),
            $gameclubUrl,
            $count,
            json_encode(['preset' => 'x_normal', 'filters' => $filters, 'sql' => $criteria], JSON_UNESCAPED_UNICODE),
            'active',
            $expiresAt,
        ]);
        $deliveryId = (int)$pdo->lastInsertId();

        $ids = move_accounts_to_delivery($pdo, $accounts, $columns, $deliveryId, 'reserved');

        $deletePlaceholders = rtrim(str_repeat('?,', count($ids)), ',');
        $delete = $pdo->prepare("DELETE FROM acc_rep WHERE id IN ({$deletePlaceholders})");
        $delete->execute($ids);

        $delivery = get_delivery($deliveryId);
        $pdo->commit();
        sync_delivery_public_best_effort($deliveryId);
        return $delivery;
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function extend_delivery_expiry(int $deliveryId, int $days): array
{
    if (!in_array($days, [7, 30, 90], true)) {
        throw new RuntimeException('表示期限の延長日数は7日、30日、90日のいずれかを指定してください。');
    }

    ensure_schema();
    $pdo = db();
    $pdo->beginTransaction();
    try {
        $stmt = $pdo->prepare('SELECT * FROM deliveries WHERE id = ? FOR UPDATE');
        $stmt->execute([$deliveryId]);
        $delivery = $stmt->fetch();
        if (!$delivery) {
            throw new RuntimeException('納品データが見つかりません。');
        }
        if ($delivery['status'] !== 'active') {
            throw new RuntimeException('有効な納品ページだけ表示期限を延長できます。');
        }

        $now = new DateTimeImmutable('now', new DateTimeZone('UTC'));
        $currentExpiry = null;
        if (!empty($delivery['expires_at'])) {
            $currentExpiry = new DateTimeImmutable((string)$delivery['expires_at'], new DateTimeZone('UTC'));
        }
        $base = $currentExpiry instanceof DateTimeImmutable && $currentExpiry > $now
            ? $currentExpiry
            : $now;
        $newExpiry = $base->modify('+' . $days . ' days')->format('Y-m-d H:i:s');

        $update = $pdo->prepare('UPDATE deliveries SET expires_at = ?, updated_at = NOW() WHERE id = ?');
        $update->execute([$newExpiry, $deliveryId]);
        $updated = get_delivery($deliveryId);
        $pdo->commit();
        sync_delivery_public_best_effort($deliveryId);
        return $updated;
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function add_delivery_replacements(int $deliveryId, int $count): int
{
    if ($count < 1 || $count > 20) {
        throw new RuntimeException('交換品の追加数は1から20の範囲で指定してください。');
    }

    ensure_schema();
    $pdo = db();
    $columns = acc_rep_columns();

    $pdo->beginTransaction();
    try {
        $deliveryStmt = $pdo->prepare('SELECT * FROM deliveries WHERE id = ? FOR UPDATE');
        $deliveryStmt->execute([$deliveryId]);
        $delivery = $deliveryStmt->fetch();
        if (!$delivery || $delivery['status'] !== 'active') {
            throw new RuntimeException('有効な納品だけに交換品を追加できます。');
        }

        $criteriaData = json_decode((string)$delivery['criteria_json'], true);
        $filters = is_array($criteriaData) && is_array($criteriaData['filters'] ?? null)
            ? $criteriaData['filters']
            : [];
        $criteria = default_criteria_sql($filters);
        $accountsStmt = $pdo->prepare(
            "SELECT * FROM acc_rep
             WHERE {$criteria}
             ORDER BY COALESCE(shadow_checked_at, '1970-01-01') ASC, RAND()
             LIMIT {$count}
             FOR UPDATE"
        );
        $accountsStmt->execute();
        $accounts = $accountsStmt->fetchAll();
        if (count($accounts) < $count) {
            throw new RuntimeException('交換品に合う在庫が足りません。取得可能: ' . count($accounts) . ' 件');
        }

        $ids = move_accounts_to_delivery($pdo, $accounts, $columns, $deliveryId, 'replacement');

        $delete = $pdo->prepare('DELETE FROM acc_rep WHERE id IN (' . rtrim(str_repeat('?,', count($ids)), ',') . ')');
        $delete->execute($ids);
        $pdo->commit();
        sync_delivery_public_best_effort($deliveryId);
        return count($accounts);
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function add_delivery_replacements_by_ids(int $deliveryId, string $rawAccountIds): int
{
    $accountIds = preg_split('/[\s,]+/', trim($rawAccountIds), -1, PREG_SPLIT_NO_EMPTY) ?: [];
    $accountIds = array_values(array_unique($accountIds));
    if (!$accountIds || count($accountIds) > 200) {
        throw new RuntimeException('指定IDは1件から200件まで入力してください。');
    }

    ensure_schema();
    $pdo = db();
    $columns = acc_rep_columns();
    $pdo->beginTransaction();
    try {
        $deliveryStmt = $pdo->prepare('SELECT status FROM deliveries WHERE id = ? FOR UPDATE');
        $deliveryStmt->execute([$deliveryId]);
        if ($deliveryStmt->fetchColumn() !== 'active') {
            throw new RuntimeException('有効な納品にだけ交換品を追加できます。');
        }

        $placeholders = implode(',', array_fill(0, count($accountIds), '?'));
        $accountsStmt = $pdo->prepare("SELECT * FROM acc_rep WHERE id IN ({$placeholders}) FOR UPDATE");
        $accountsStmt->execute($accountIds);
        $accounts = $accountsStmt->fetchAll();
        if (count($accounts) !== count($accountIds)) {
            $found = array_fill_keys(array_map('strtolower', array_column($accounts, 'id')), true);
            $missing = array_values(array_filter(
                $accountIds,
                static fn(string $id): bool => !isset($found[strtolower($id)])
            ));
            throw new RuntimeException('在庫にないIDが含まれています: ' . implode(', ', $missing));
        }

        $ids = move_accounts_to_delivery($pdo, $accounts, $columns, $deliveryId, 'replacement');
        $delete = $pdo->prepare('DELETE FROM acc_rep WHERE id IN (' . $placeholders . ')');
        $delete->execute($ids);
        $pdo->commit();
        sync_delivery_public_best_effort($deliveryId);
        return count($accounts);
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function disable_delivery_replacement(int $deliveryId, string $accountId): void
{
    if ($accountId === '') {
        throw new RuntimeException('無効化する交換品を選択してください。');
    }

    ensure_schema();
    $pdo = db();
    $pdo->beginTransaction();
    try {
        $deliveryStmt = $pdo->prepare('SELECT status FROM deliveries WHERE id = ? FOR UPDATE');
        $deliveryStmt->execute([$deliveryId]);
        if ($deliveryStmt->fetchColumn() !== 'active') {
            throw new RuntimeException('有効な納品ではありません。');
        }

        $accountStmt = $pdo->prepare(
            "SELECT id FROM acc_rep_delivery
             WHERE delivery_id = ? AND id = ? AND delivery_status = 'replacement'
             FOR UPDATE"
        );
        $accountStmt->execute([$deliveryId, $accountId]);
        if (!$accountStmt->fetchColumn()) {
            throw new RuntimeException('有効な交換品が見つかりません。');
        }

        $update = $pdo->prepare(
            "UPDATE acc_rep_delivery SET delivery_status = 'replacement_invalid' WHERE delivery_id = ? AND id = ?"
        );
        $update->execute([$deliveryId, $accountId]);
        $pdo->commit();
        sync_delivery_public_best_effort($deliveryId);
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function get_delivery(int|string $idOrToken): array
{
    $pdo = db();
    if (is_numeric($idOrToken)) {
        $stmt = $pdo->prepare('SELECT * FROM deliveries WHERE id = ?');
    } else {
        $stmt = $pdo->prepare('SELECT * FROM deliveries WHERE token = ?');
    }
    $stmt->execute([$idOrToken]);
    $row = $stmt->fetch();
    if (!$row) {
        throw new RuntimeException('納品データが見つかりません。');
    }
    return $row;
}

function list_deliveries(int $limit = 50): array
{
    ensure_schema();
    $stmt = db()->prepare('SELECT * FROM deliveries ORDER BY created_at DESC LIMIT ?');
    $stmt->bindValue(1, $limit, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

function list_deliveries_for_admin(string $search = ''): array
{
    ensure_schema();
    [$condition, $searchParams] = delivery_search_condition($search);
    $stmt = db()->prepare(
        "SELECT * FROM deliveries
         WHERE status IN ('active', 'canceled'){$condition}
         ORDER BY created_at DESC, id DESC"
    );
    $stmt->execute($searchParams);
    return $stmt->fetchAll();
}

function daily_delivery_stats(int $days = 30): array
{
    $days = max(1, min(90, $days));
    $timezone = new DateTimeZone('Asia/Tokyo');
    $today = new DateTimeImmutable('today', $timezone);
    $from = $today->modify('-' . ($days - 1) . ' days');
    $fromUtc = $from->setTimezone(new DateTimeZone('UTC'))->format('Y-m-d H:i:s');

    $stmt = db()->prepare(
        "SELECT DATE(CONVERT_TZ(created_at, '+00:00', '+09:00')) AS sale_date,
                COUNT(*) AS delivery_count,
                COALESCE(SUM(account_count), 0) AS account_count,
                SUM(status = 'active') AS active_count,
                SUM(status = 'canceled') AS canceled_count
         FROM deliveries
         WHERE created_at >= ?
         GROUP BY sale_date
         ORDER BY sale_date ASC"
    );
    $stmt->execute([$fromUtc]);

    $byDate = [];
    foreach ($stmt->fetchAll() as $row) {
        $byDate[$row['sale_date']] = [
            'date' => $row['sale_date'],
            'delivery_count' => (int)$row['delivery_count'],
            'account_count' => (int)$row['account_count'],
            'active_count' => (int)$row['active_count'],
            'canceled_count' => (int)$row['canceled_count'],
        ];
    }

    $stats = [];
    for ($offset = $days - 1; $offset >= 0; $offset--) {
        $date = $today->modify('-' . $offset . ' days')->format('Y-m-d');
        $stats[] = $byDate[$date] ?? [
            'date' => $date,
            'delivery_count' => 0,
            'account_count' => 0,
            'active_count' => 0,
            'canceled_count' => 0,
        ];
    }
    return $stats;
}

function count_deliveries_by_status(string $status, string $search = ''): int
{
    ensure_schema();
    [$condition, $searchParams] = delivery_search_condition($search);
    $sql = 'SELECT COUNT(*) FROM deliveries WHERE status = ?' . $condition;
    $params = [$status];
    array_push($params, ...$searchParams);
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return (int)$stmt->fetchColumn();
}

function list_deliveries_by_status(string $status, int $limit, int $offset, string $search = ''): array
{
    ensure_schema();
    [$condition, $searchParams] = delivery_search_condition($search);
    $sql = 'SELECT * FROM deliveries WHERE status = ?' . $condition;
    $sql .= ' ORDER BY created_at DESC LIMIT ? OFFSET ?';
    $stmt = db()->prepare($sql);
    $stmt->bindValue(1, $status, PDO::PARAM_STR);
    $parameter = 2;
    foreach ($searchParams as $value) {
        $stmt->bindValue($parameter++, $value);
    }
    $stmt->bindValue($parameter++, $limit, PDO::PARAM_INT);
    $stmt->bindValue($parameter, $offset, PDO::PARAM_INT);
    $stmt->execute();
    return $stmt->fetchAll();
}

function delivery_accounts(int $deliveryId): array
{
    $stmt = db()->prepare(
        'SELECT id, pass, mail, mail_pass, auth_token, `2fa`, bk_code, moved_at, delivery_status
         FROM acc_rep_delivery
         WHERE delivery_id = ?
         ORDER BY moved_at ASC, id ASC'
    );
    $stmt->execute([$deliveryId]);
    return $stmt->fetchAll();
}

function replacement_accounts_by_delivery(array $deliveryIds): array
{
    $deliveryIds = array_values(array_unique(array_filter(
        array_map('intval', $deliveryIds),
        static fn(int $id): bool => $id > 0
    )));
    if (!$deliveryIds) {
        return [];
    }

    $placeholders = implode(',', array_fill(0, count($deliveryIds), '?'));
    $stmt = db()->prepare(
        "SELECT delivery_id, id
         FROM acc_rep_delivery
         WHERE delivery_status = 'replacement' AND delivery_id IN ({$placeholders})
         ORDER BY moved_at ASC, id ASC"
    );
    $stmt->execute($deliveryIds);

    $byDelivery = [];
    foreach ($stmt->fetchAll() as $account) {
        $byDelivery[(int)$account['delivery_id']][] = $account;
    }
    return $byDelivery;
}

function sync_delivery_to_public_db(int $deliveryId): void
{
    $delivery = get_delivery($deliveryId);
    $source = db();
    $accountsStmt = $source->prepare(
        'SELECT id, pass, mail, mail_pass, auth_token, `2fa`, bk_code, moved_at, delivery_status
         FROM acc_rep_delivery WHERE delivery_id = ? ORDER BY moved_at ASC, id ASC'
    );
    $accountsStmt->execute([$deliveryId]);
    $accounts = $accountsStmt->fetchAll();

    if (delivery_public_sync_url() !== '') {
        sync_delivery_to_remote_public_db($delivery, $accounts);
        return;
    }

    sync_delivery_payload_to_public_db($delivery, $accounts);
}

function sync_delivery_to_remote_public_db(array $delivery, array $accounts): void
{
    $payload = json_encode(['delivery' => $delivery, 'accounts' => $accounts], JSON_UNESCAPED_UNICODE | JSON_THROW_ON_ERROR);
    $timestamp = (string)time();
    $signature = hash_hmac('sha256', $timestamp . '.' . $payload, delivery_public_sync_secret());
    $headers = [
        'Content-Type: application/json',
        'X-Delivery-Timestamp: ' . $timestamp,
        'X-Delivery-Signature: ' . $signature,
    ];

    if (function_exists('curl_init')) {
        $ch = curl_init(delivery_public_sync_url());
        curl_setopt_array($ch, [
            CURLOPT_POST => true,
            CURLOPT_POSTFIELDS => $payload,
            CURLOPT_HTTPHEADER => $headers,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_CONNECTTIMEOUT => 5,
            CURLOPT_TIMEOUT => 20,
        ]);
        $response = curl_exec($ch);
        $status = (int)curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
        $error = curl_error($ch);
        curl_close($ch);
        if ($response === false || $status < 200 || $status >= 300) {
            throw new RuntimeException('公開用DBの同期に失敗しました: ' . ($error !== '' ? $error : 'HTTP ' . $status));
        }
        return;
    }

    $context = stream_context_create(['http' => [
        'method' => 'POST',
        'header' => implode("\r\n", $headers),
        'content' => $payload,
        'timeout' => 20,
        'ignore_errors' => true,
    ]]);
    $response = @file_get_contents(delivery_public_sync_url(), false, $context);
    $statusLine = $http_response_header[0] ?? '';
    preg_match('/\s(\d{3})\s/', $statusLine, $matches);
    $status = isset($matches[1]) ? (int)$matches[1] : 0;
    if ($response === false || $status < 200 || $status >= 300) {
        throw new RuntimeException('公開用DBの同期に失敗しました: HTTP ' . $status);
    }
}

function sync_delivery_payload_to_public_db(array $delivery, array $accounts): void
{
    ensure_public_delivery_schema();
    $deliveryId = (int)$delivery['id'];

    $target = public_delivery_db();
    $target->beginTransaction();
    try {
        $upsert = $target->prepare(
            'INSERT INTO deliveries
             (id, token, access_password, access_password_hash, gameclub_url, account_count, criteria_json, status, created_at, updated_at, expires_at, synced_at)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
             ON DUPLICATE KEY UPDATE token = VALUES(token), access_password = VALUES(access_password), access_password_hash = VALUES(access_password_hash),
                 gameclub_url = VALUES(gameclub_url), account_count = VALUES(account_count), criteria_json = VALUES(criteria_json), status = VALUES(status),
                 created_at = VALUES(created_at), updated_at = VALUES(updated_at), expires_at = VALUES(expires_at), synced_at = NOW()'
        );
        $upsert->execute([
            $deliveryId, $delivery['token'], $delivery['access_password'], $delivery['access_password_hash'],
            $delivery['gameclub_url'], $delivery['account_count'], $delivery['criteria_json'], $delivery['status'],
            $delivery['created_at'], $delivery['updated_at'], $delivery['expires_at'],
        ]);

        $target->prepare('DELETE FROM delivery_accounts WHERE delivery_id = ?')->execute([$deliveryId]);
        if ($delivery['status'] === 'active' && $accounts) {
            $insert = $target->prepare(
                'INSERT INTO delivery_accounts
                 (delivery_id, account_index, account_id, pass_value, mail, mail_pass, auth_token, twofa, bk_code, moved_at, delivery_status)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
            );
            foreach ($accounts as $index => $account) {
                $insert->execute([
                    $deliveryId, $index, (string)($account['id'] ?? ''), $account['pass'] ?? null, $account['mail'] ?? null,
                    $account['mail_pass'] ?? null, $account['auth_token'] ?? null, $account['2fa'] ?? null,
                    $account['bk_code'] ?? null, $account['moved_at'] ?? null, $account['delivery_status'] ?? 'reserved',
                ]);
            }
        }
        $target->commit();
    } catch (Throwable $e) {
        if ($target->inTransaction()) {
            $target->rollBack();
        }
        throw $e;
    }
}

function queue_delivery_public_sync(int $deliveryId, string $error): void
{
    $stmt = db()->prepare(
        'INSERT INTO delivery_public_sync_queue (delivery_id, attempts, last_error, next_attempt_at)
         VALUES (?, 1, ?, NOW())
         ON DUPLICATE KEY UPDATE attempts = attempts + 1, last_error = VALUES(last_error), next_attempt_at = NOW()'
    );
    $stmt->execute([$deliveryId, mb_substr($error, 0, 1000)]);
}

function sync_delivery_public_best_effort(int $deliveryId): void
{
    try {
        sync_delivery_to_public_db($deliveryId);
        db()->prepare('DELETE FROM delivery_public_sync_queue WHERE delivery_id = ?')->execute([$deliveryId]);
    } catch (Throwable $e) {
        queue_delivery_public_sync($deliveryId, $e->getMessage());
    }
}

function retry_delivery_public_sync(int $limit = 100): int
{
    ensure_schema();
    $stmt = db()->prepare(
        'SELECT delivery_id FROM delivery_public_sync_queue
         WHERE next_attempt_at <= NOW() ORDER BY updated_at ASC LIMIT ?'
    );
    $stmt->bindValue(1, $limit, PDO::PARAM_INT);
    $stmt->execute();
    $count = 0;
    foreach ($stmt->fetchAll() as $row) {
        sync_delivery_public_best_effort((int)$row['delivery_id']);
        $count++;
    }
    return $count;
}

function sync_all_deliveries_to_public_db(): int
{
    ensure_schema();
    $ids = db()->query('SELECT id FROM deliveries ORDER BY id ASC')->fetchAll(PDO::FETCH_COLUMN);
    foreach ($ids as $id) {
        sync_delivery_public_best_effort((int)$id);
    }
    return count($ids);
}

function get_public_delivery(string $token): array
{
    ensure_public_delivery_schema();
    $stmt = public_delivery_db()->prepare('SELECT * FROM deliveries WHERE token = ?');
    $stmt->execute([$token]);
    $row = $stmt->fetch();
    if (!$row) {
        throw new RuntimeException('納品データが見つかりません。');
    }
    return $row;
}

function public_delivery_accounts(int $deliveryId): array
{
    ensure_public_delivery_schema();
    $stmt = public_delivery_db()->prepare(
        'SELECT account_id AS id, pass_value AS pass, mail, mail_pass, auth_token, twofa AS `2fa`, bk_code, moved_at, delivery_status
         FROM delivery_accounts WHERE delivery_id = ? ORDER BY account_index ASC'
    );
    $stmt->execute([$deliveryId]);
    return $stmt->fetchAll();
}

function cancel_delivery(int $deliveryId): void
{
    ensure_schema();
    $pdo = db();
    $columns = acc_rep_columns();
    $quotedColumns = array_map(static fn(string $c): string => "`{$c}`", $columns);

    $pdo->beginTransaction();
    try {
        $deliveryStmt = $pdo->prepare("SELECT * FROM deliveries WHERE id = ? FOR UPDATE");
        $deliveryStmt->execute([$deliveryId]);
        $delivery = $deliveryStmt->fetch();
        if (!$delivery) {
            throw new RuntimeException('納品データが見つかりません。');
        }
        if ($delivery['status'] !== 'active') {
            throw new RuntimeException('active状態の納品だけキャンセルできます。');
        }

        $accStmt = $pdo->prepare("SELECT * FROM acc_rep_delivery WHERE delivery_id = ? FOR UPDATE");
        $accStmt->execute([$deliveryId]);
        $accounts = $accStmt->fetchAll();

        if ($accounts) {
            $placeholders = '(' . rtrim(str_repeat('?,', count($columns)), ',') . ')';
            $insert = $pdo->prepare('INSERT INTO acc_rep (' . implode(',', $quotedColumns) . ') VALUES ' . $placeholders);
            foreach ($accounts as $account) {
                $values = [];
                foreach ($columns as $column) {
                    $values[] = $account[$column] ?? null;
                }
                $insert->execute($values);
            }
            $delete = $pdo->prepare('DELETE FROM acc_rep_delivery WHERE delivery_id = ?');
            $delete->execute([$deliveryId]);
        }

        $update = $pdo->prepare("UPDATE deliveries SET status = 'canceled' WHERE id = ?");
        $update->execute([$deliveryId]);
        $pdo->commit();
        sync_delivery_public_best_effort($deliveryId);
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

function find_public_token(): string
{
    $path = parse_url($_SERVER['REQUEST_URI'] ?? '', PHP_URL_PATH) ?: '';
    if (preg_match('#^/delivery/([A-Za-z0-9_-]+)#', $path, $m)) {
        return $m[1];
    }
    return trim((string)($_GET['token'] ?? $_GET['t'] ?? ''));
}

function verify_delivery_access(array $delivery): bool
{
    $key = 'delivery_access_' . $delivery['token'];
    if (!empty($_SESSION[$key])) {
        return true;
    }
    if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['password'])) {
        $password = (string)$_POST['password'];
        if (password_verify($password, $delivery['access_password_hash']) || hash_equals($delivery['access_password'], $password)) {
            $_SESSION[$key] = true;
            return true;
        }
    }
    return false;
}

function account_rows_text(array $accounts): string
{
    $out = '';
    foreach ($accounts as $account) {
        $mail = (string)$account['mail'];
        $out .= "-----\n";
        $out .= "ID:" . $account['id'] . "\n\n";
        $out .= "PASS:" . $account['pass'] . "\n\n";
        $out .= "MAIL:" . $mail . "\n\n";
        $out .= "MAILCODE:https://yntak2.com/mailcheck/?mail=" . rawurlencode($mail) . "\n\n";
        if (!empty($account['2fa'])) {
            $out .= "2FA:" . $account['2fa'] . "\n\n";
        }
        $out .= "AUTH_TOKEN:" . $account['auth_token'] . "\n";
    }
    return $out;
}