<?php

declare(strict_types=1);

require_once __DIR__ . DIRECTORY_SEPARATOR . 'admin_member_auth.php';
require_once __DIR__ . DIRECTORY_SEPARATOR . 'fate_member_email.php';
require_once __DIR__ . DIRECTORY_SEPARATOR . 'fate_member_tier.php';

function fate_admin_members_qualified_table(): ?string
{
    if (!fate_admin_member_auth_ready()) {
        return null;
    }
    $db = FATE_MEMBER_DATABASE;
    $tbl = FATE_MEMBER_TABLE;

    return sprintf('`%s`.`%s`', str_replace('`', '``', $db), str_replace('`', '``', $tbl));
}

/** @param array<string,mixed> $row */
function fate_admin_members_is_pending_review(array $row): bool
{
    return fate_member_row_pending_approval($row);
}

/** @param array<string,mixed> $row */
function fate_admin_members_review_status_label(array $row): string
{
    if (!fate_member_manual_approval_enabled() || !isset($row['approved_at'])) {
        $active = (int) ($row['_mem_active'] ?? 0) === 1;

        return $active ? '已啟用' : '已停權';
    }
    if (fate_admin_members_is_pending_review($row)) {
        return '待審核';
    }
    $active = (int) ($row['_mem_active'] ?? 0) === 1;

    return $active ? '已通過' : '已停權';
}

function fate_admin_members_review_badge_class(array $row): string
{
    $label = fate_admin_members_review_status_label($row);
    if ($label === '待審核') {
        return 'badge badge--pending';
    }
    if ($label === '已停權') {
        return 'badge badge--off';
    }

    return 'badge badge--ok';
}

function fate_admin_members_table_exists(PDO $pdo): bool
{
    $qt = fate_admin_members_qualified_table();
    if ($qt !== null) {
        try {
            $pdo->query('SELECT 1 FROM ' . $qt . ' LIMIT 1');

            return true;
        } catch (Throwable) {
            /* 常數庫名與 PDO 連線庫不一致時再試 PDO 目前庫 */
        }
    }

    if (!fate_admin_member_auth_ready()) {
        return false;
    }

    if (!function_exists('fate_db_pdo_current_database')) {
        require_once __DIR__ . DIRECTORY_SEPARATOR . 'fate_db_connect.php';
    }

    $db = fate_db_pdo_current_database($pdo);
    if ($db === null || !fate_admin_mysql_ident_ok($db)) {
        return false;
    }

    $fallback = sprintf(
        '`%s`.`%s`',
        str_replace('`', '``', $db),
        str_replace('`', '``', FATE_MEMBER_TABLE)
    );
    try {
        $pdo->query('SELECT 1 FROM ' . $fallback . ' LIMIT 1');

        return true;
    } catch (Throwable) {
        return false;
    }
}

/**
 * @return array{
 *   total:int,
 *   active:int,
 *   inactive:int,
 *   by_role:array{admin:int,mod:int,member:int,other:int},
 *   email_verified:?int,
 *   email_pending:?int,
 *   email_none:?int,
 *   review_pending:?int,
 *   formal:?int,
 *   registered_tier:?int,
 *   blacklist:?int,
 *   registered_7d:?int,
 *   registered_30d:?int,
 *   login_7d:?int,
 *   login_30d:?int,
 *   features:array{email:bool,email_verified:bool,created_at:bool,last_login:bool,member_tier:bool}
 * }
 */
function fate_admin_members_overview(PDO $pdo): array
{
    $qt = fate_admin_members_qualified_table();
    if ($qt === null) {
        throw new RuntimeException('會員表設定未就緒');
    }

    $ac = FATE_MEMBER_ACTIVE_COLUMN;
    $hasEmail = fate_members_table_has_column($pdo, 'EMAIL');
    $hasEmailVerified = fate_members_table_has_column($pdo, 'EMAIL_VERIFIED_AT');
    $hasCreated = fate_members_table_has_column($pdo, 'CREATED_AT');
    $hasLastLogin = fate_members_table_has_column($pdo, 'LAST_LOGIN_AT');
    $hasMemberTier = fate_member_tier_column_exists($pdo);

    $empty = static fn (): array => [
        'total' => 0,
        'active' => 0,
        'inactive' => 0,
        'by_role' => ['admin' => 0, 'mod' => 0, 'member' => 0, 'other' => 0],
        'email_verified' => null,
        'email_pending' => null,
        'email_none' => null,
        'review_pending' => null,
        'formal' => null,
        'registered_tier' => null,
        'blacklist' => null,
        'registered_7d' => null,
        'registered_30d' => null,
        'login_7d' => null,
        'login_30d' => null,
        'features' => [
            'email' => $hasEmail,
            'email_verified' => $hasEmailVerified,
            'created_at' => $hasCreated,
            'last_login' => $hasLastLogin,
            'manual_review' => fate_member_manual_approval_enabled() && fate_members_table_has_column($pdo, 'APPROVED_AT'),
            'member_tier' => $hasMemberTier,
        ],
    ];

    $out = $empty();

    $out['total'] = (int) $pdo->query('SELECT COUNT(*) FROM ' . $qt)->fetchColumn();
    $stActive = $pdo->query(
        sprintf('SELECT COUNT(*) FROM %s WHERE `%s` = 1', $qt, str_replace('`', '``', $ac))
    );
    $out['active'] = (int) $stActive->fetchColumn();
    $out['inactive'] = max(0, $out['total'] - $out['active']);

    $roleSql = sprintf(
        "SELECT LOWER(TRIM(COALESCE(`role`, ''))) AS r, COUNT(*) AS c FROM %s GROUP BY r",
        $qt
    );
    foreach ($pdo->query($roleSql) as $row) {
        if (!is_array($row)) {
            continue;
        }
        $r = (string) ($row['r'] ?? '');
        $c = (int) ($row['c'] ?? 0);
        if ($r === 'admin') {
            $out['by_role']['admin'] += $c;
        } elseif ($r === 'mod') {
            $out['by_role']['mod'] += $c;
        } elseif ($r === '' || $r === 'member') {
            $out['by_role']['member'] += $c;
        } else {
            $out['by_role']['other'] += $c;
        }
    }

    if ($hasEmail && $hasEmailVerified) {
        $out['email_verified'] = (int) $pdo->query(
            sprintf(
                "SELECT COUNT(*) FROM %s WHERE `email` IS NOT NULL AND TRIM(`email`) <> '' AND `email_verified_at` IS NOT NULL",
                $qt
            )
        )->fetchColumn();
        $out['email_pending'] = (int) $pdo->query(
            sprintf(
                "SELECT COUNT(*) FROM %s WHERE `email` IS NOT NULL AND TRIM(`email`) <> '' AND `email_verified_at` IS NULL",
                $qt
            )
        )->fetchColumn();
        $out['email_none'] = (int) $pdo->query(
            sprintf(
                "SELECT COUNT(*) FROM %s WHERE `email` IS NULL OR TRIM(`email`) = ''",
                $qt
            )
        )->fetchColumn();
    }

    if (!empty($out['features']['manual_review'])) {
        $out['review_pending'] = (int) $pdo->query(
            sprintf("SELECT COUNT(*) FROM %s WHERE `approved_at` IS NULL", $qt)
        )->fetchColumn();
    }

    if ($hasMemberTier) {
        $acEsc = str_replace('`', '``', FATE_MEMBER_ACTIVE_COLUMN);
        $out['formal'] = (int) $pdo->query(
            sprintf(
                "SELECT COUNT(*) FROM %s WHERE `member_tier` = 'formal' AND `%s` = 1",
                $qt,
                $acEsc
            )
        )->fetchColumn();
        $out['registered_tier'] = (int) $pdo->query(
            sprintf("SELECT COUNT(*) FROM %s WHERE `member_tier` = 'register'", $qt)
        )->fetchColumn();
        $out['blacklist'] = (int) $pdo->query(
            sprintf("SELECT COUNT(*) FROM %s WHERE `member_tier` = 'blacklist'", $qt)
        )->fetchColumn();
    }

    if ($hasCreated) {
        $out['registered_7d'] = (int) $pdo->query(
            sprintf("SELECT COUNT(*) FROM %s WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL 7 DAY)", $qt)
        )->fetchColumn();
        $out['registered_30d'] = (int) $pdo->query(
            sprintf("SELECT COUNT(*) FROM %s WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL 30 DAY)", $qt)
        )->fetchColumn();
    }

    if ($hasLastLogin) {
        $out['login_7d'] = (int) $pdo->query(
            sprintf("SELECT COUNT(*) FROM %s WHERE `last_login_at` >= DATE_SUB(NOW(), INTERVAL 7 DAY)", $qt)
        )->fetchColumn();
        $out['login_30d'] = (int) $pdo->query(
            sprintf("SELECT COUNT(*) FROM %s WHERE `last_login_at` >= DATE_SUB(NOW(), INTERVAL 30 DAY)", $qt)
        )->fetchColumn();
    }

    return $out;
}

/**
 * 緊湊概況文字（純文字片段，模板以 · 串接）。
 *
 * @param array<string,mixed> $overview
 * @return list<string>
 */
function fate_admin_members_summary_segments(array $overview): array
{
    $fmt = static fn (int $v): string => number_format($v);

    $parts = [
        sprintf(
            '共 %s 人（啟用 %s／未啟用 %s）',
            $fmt((int) ($overview['total'] ?? 0)),
            $fmt((int) ($overview['active'] ?? 0)),
            $fmt((int) ($overview['inactive'] ?? 0))
        ),
    ];

    $roleLine = sprintf(
        'admin %s、mod %s、一般 %s',
        $fmt((int) ($overview['by_role']['admin'] ?? 0)),
        $fmt((int) ($overview['by_role']['mod'] ?? 0)),
        $fmt((int) ($overview['by_role']['member'] ?? 0))
    );
    if ((int) ($overview['by_role']['other'] ?? 0) > 0) {
        $roleLine .= '、其他 ' . $fmt((int) $overview['by_role']['other']);
    }
    $parts[] = $roleLine;

    $features = is_array($overview['features'] ?? null) ? $overview['features'] : [];
    if (!empty($features['member_tier']) && ($overview['formal'] ?? null) !== null) {
        $parts[] = sprintf(
            '正式會員 %s、註冊會員 %s',
            $fmt((int) $overview['formal']),
            $fmt((int) ($overview['registered_tier'] ?? 0))
        );
        if (($overview['blacklist'] ?? null) !== null && (int) $overview['blacklist'] > 0) {
            $parts[] = '黑名單 ' . $fmt((int) $overview['blacklist']);
        }
    }

    if (!empty($features['manual_review']) && ($overview['review_pending'] ?? null) !== null) {
        $parts[] = '待審核 ' . $fmt((int) $overview['review_pending']);
    }

    if (!empty($features['email']) && !empty($features['email_verified'])) {
        $parts[] = sprintf(
            '信箱已驗 %s、待驗 %s、未填 %s',
            $fmt((int) ($overview['email_verified'] ?? 0)),
            $fmt((int) ($overview['email_pending'] ?? 0)),
            $fmt((int) ($overview['email_none'] ?? 0))
        );
    }

    $recent = [];
    if (!empty($features['created_at'])) {
        $recent[] = '7日註冊 ' . $fmt((int) ($overview['registered_7d'] ?? 0));
        $recent[] = '30日註冊 ' . $fmt((int) ($overview['registered_30d'] ?? 0));
    }
    if (!empty($features['last_login'])) {
        $recent[] = '7日登入 ' . $fmt((int) ($overview['login_7d'] ?? 0));
        $recent[] = '30日登入 ' . $fmt((int) ($overview['login_30d'] ?? 0));
    }
    if ($recent !== []) {
        $parts[] = implode('，', $recent);
    }

    return $parts;
}

/**
 * @param array{role?:string,status?:string,review?:string,verify?:string,tier?:string,q?:string,page?:int,per_page?:int} $opts
 * @return array{rows:list<array<string,mixed>>,total:int,page:int,per_page:int,pages:int}
 */
function fate_admin_members_list(PDO $pdo, array $opts = []): array
{
    $qt = fate_admin_members_qualified_table();
    if ($qt === null) {
        throw new RuntimeException('會員表設定未就緒');
    }

    $page = max(1, (int) ($opts['page'] ?? 1));
    $perPage = max(10, min(100, (int) ($opts['per_page'] ?? 40)));
    $roleFilter = isset($opts['role']) ? strtolower(trim((string) $opts['role'])) : '';
    $statusFilter = isset($opts['status']) ? strtolower(trim((string) $opts['status'])) : '';
    $reviewFilter = isset($opts['review']) ? strtolower(trim((string) $opts['review'])) : '';
    $verifyFilter = isset($opts['verify']) ? strtolower(trim((string) $opts['verify'])) : '';
    $tierFilter = isset($opts['tier']) ? strtolower(trim((string) $opts['tier'])) : '';
    $q = isset($opts['q']) ? trim((string) $opts['q']) : '';

    $ac = FATE_MEMBER_ACTIVE_COLUMN;
    $hasEmail = fate_members_table_has_column($pdo, 'EMAIL');
    $hasEmailVerified = fate_members_table_has_column($pdo, 'EMAIL_VERIFIED_AT');
    $hasApproved = fate_member_manual_approval_enabled() && fate_members_table_has_column($pdo, 'APPROVED_AT');
    $hasCreated = fate_members_table_has_column($pdo, 'CREATED_AT');
    $hasLastLogin = fate_members_table_has_column($pdo, 'LAST_LOGIN_AT');
    $hasLastIp = fate_members_table_has_column($pdo, 'LAST_LOGIN_IP');
    $hasMemberTier = fate_member_tier_column_exists($pdo);

    $cols = ['`id`', '`username`', '`display_name`', '`role`', sprintf('`%s` AS `_mem_active`', str_replace('`', '``', $ac))];
    if ($hasEmail) {
        $cols[] = '`email`';
    }
    if ($hasEmailVerified) {
        $cols[] = '`email_verified_at`';
    }
    if ($hasMemberTier) {
        $cols[] = '`member_tier`';
    }
    if ($hasApproved) {
        $cols[] = '`approved_at`';
        $cols[] = '`approved_by`';
    }
    if ($hasCreated) {
        $cols[] = '`created_at`';
    }
    if ($hasLastLogin) {
        $cols[] = '`last_login_at`';
    }
    if ($hasLastIp) {
        $cols[] = '`last_login_ip`';
    }

    $where = [];
    $params = [];

    if ($hasApproved && $reviewFilter === 'pending') {
        $where[] = '`approved_at` IS NULL';
    } elseif ($hasApproved && $reviewFilter === 'approved') {
        $where[] = '`approved_at` IS NOT NULL';
    }

    if ($hasEmail && $hasEmailVerified && $verifyFilter === 'email_pending') {
        $where[] = "`email` IS NOT NULL AND TRIM(`email`) <> '' AND `email_verified_at` IS NULL";
    } elseif ($hasEmail && $hasEmailVerified && $verifyFilter === 'email_verified') {
        $where[] = "`email` IS NOT NULL AND TRIM(`email`) <> '' AND `email_verified_at` IS NOT NULL";
    }

    if ($hasMemberTier && $tierFilter === 'formal') {
        $where[] = "`member_tier` = 'formal'";
    } elseif ($hasMemberTier && $tierFilter === 'register') {
        $where[] = "`member_tier` = 'register'";
    } elseif ($hasMemberTier && $tierFilter === 'blacklist') {
        $where[] = "`member_tier` = 'blacklist'";
    }

    if ($roleFilter === 'admin') {
        $where[] = "LOWER(TRIM(COALESCE(`role`, ''))) = 'admin'";
    } elseif ($roleFilter === 'mod') {
        $where[] = "LOWER(TRIM(COALESCE(`role`, ''))) = 'mod'";
    } elseif ($roleFilter === 'member') {
        $where[] = "(TRIM(COALESCE(`role`, '')) = '' OR LOWER(TRIM(`role`)) = 'member')";
    }

    if ($statusFilter === 'active') {
        $where[] = sprintf('`%s` = 1', str_replace('`', '``', $ac));
    } elseif ($statusFilter === 'inactive') {
        $where[] = sprintf('`%s` <> 1', str_replace('`', '``', $ac));
    }

    if ($q !== '') {
        $where[] = '(`username` LIKE ? OR `display_name` LIKE ?)';
        $like = '%' . $q . '%';
        $params[] = $like;
        $params[] = $like;
    }

    $whereSql = $where !== [] ? ' WHERE ' . implode(' AND ', $where) : '';

    $countSql = 'SELECT COUNT(*) FROM ' . $qt . $whereSql;
    $stCount = $pdo->prepare($countSql);
    $stCount->execute($params);
    $total = (int) $stCount->fetchColumn();
    $pages = $total > 0 ? (int) ceil($total / $perPage) : 1;
    if ($page > $pages) {
        $page = $pages;
    }
    $offset = ($page - 1) * $perPage;

    $listSql = 'SELECT ' . implode(', ', $cols) . ' FROM ' . $qt . $whereSql
        . ' ORDER BY `id` DESC LIMIT ' . (int) $perPage . ' OFFSET ' . (int) $offset;
    $stList = $pdo->prepare($listSql);
    $stList->execute($params);
    $rows = $stList->fetchAll(PDO::FETCH_ASSOC);

    return [
        'rows' => is_array($rows) ? $rows : [],
        'total' => $total,
        'page' => $page,
        'per_page' => $perPage,
        'pages' => max(1, $pages),
    ];
}

function fate_admin_members_role_label(?string $role): string
{
    $r = strtolower(trim((string) $role));
    if ($r === 'admin') {
        return '管理員';
    }
    if ($r === 'mod') {
        return '版主';
    }
    if ($r === '' || $r === 'member') {
        return '一般會員';
    }

    return $role !== '' ? $role : '一般會員';
}

function fate_admin_members_role_class(?string $role): string
{
    $r = strtolower(trim((string) $role));
    if ($r === 'admin') {
        return 'badge badge--admin';
    }
    if ($r === 'mod') {
        return 'badge badge--mod';
    }

    return 'badge badge--member';
}

function fate_admin_members_mask_email(?string $email): string
{
    $em = trim((string) $email);
    if ($em === '') {
        return '—';
    }
    if (!filter_var($em, FILTER_VALIDATE_EMAIL)) {
        return '（格式異常）';
    }
    $at = strpos($em, '@');
    if ($at === false || $at < 2) {
        return '***' . substr($em, max(0, $at));
    }
    $local = substr($em, 0, $at);
    $domain = substr($em, $at);
    $shown = substr($local, 0, 2) . '***';

    return $shown . $domain;
}

function fate_admin_members_format_dt(?string $raw): string
{
    if ($raw === null || trim($raw) === '') {
        return '—';
    }

    return trim($raw);
}

/**
 * 會員列／編輯面板 AJAX 回傳用。
 *
 * @param array<string,mixed> $row
 * @param array{
 *   has_manual_review?:bool,
 *   has_email?:bool,
 *   has_email_verified?:bool,
 *   has_last_login?:bool,
 *   acting_admin_id?:int,
 *   edit_url?:string
 * } $ctx
 * @return array<string,mixed>
 */
function fate_admin_members_row_api_payload(array $row, array $ctx = []): array
{
    $mid = (int) ($row['id'] ?? 0);
    $actingAdminId = (int) ($ctx['acting_admin_id'] ?? 0);
    $hasManualReview = !empty($ctx['has_manual_review']);
    $hasEmailVerified = !empty($ctx['has_email_verified']);
    $active = (int) ($row['_mem_active'] ?? 0) === 1;
    $roleRaw = isset($row['role']) ? (string) $row['role'] : '';
    $pendingReview = $hasManualReview && fate_admin_members_is_pending_review($row);
    $emailStatus = fate_admin_members_email_status_label($row);
    $roleInput = fate_admin_members_role_input_value($roleRaw);
    $tierRaw = fate_member_tier_from_row($row);
    $isFormal = $active && !$pendingReview && $tierRaw === 'formal';
    $isBlacklisted = $tierRaw === 'blacklist';

    return [
        'id' => $mid,
        'username' => (string) ($row['username'] ?? ''),
        'display_name' => (string) ($row['display_name'] ?? ''),
        'role' => $roleInput,
        'role_label' => fate_admin_members_role_label($roleRaw),
        'role_class' => fate_admin_members_role_class($roleRaw),
        'member_tier' => $tierRaw,
        'member_tier_label' => fate_member_tier_label($tierRaw),
        'member_tier_class' => fate_member_tier_badge_class($tierRaw),
        'is_formal_member' => $isFormal,
        'is_blacklisted' => $isBlacklisted,
        'review_label' => fate_admin_members_review_status_label($row),
        'review_badge_class' => fate_admin_members_review_badge_class($row),
        'email_masked' => fate_admin_members_mask_email(isset($row['email']) ? (string) $row['email'] : null),
        'email_raw' => isset($row['email']) ? trim((string) $row['email']) : '',
        'email_status' => $emailStatus,
        'last_login' => fate_admin_members_format_dt(isset($row['last_login_at']) ? (string) $row['last_login_at'] : null),
        'pending_review' => $pendingReview,
        'needs_email_verify' => $hasEmailVerified && $emailStatus === '待驗證',
        'email_verified' => $emailStatus === '已驗證',
        'active' => $active,
        'is_self' => $actingAdminId > 0 && $actingAdminId === $mid,
        'can_toggle_active' => $actingAdminId !== $mid && !$pendingReview,
        'show_approve' => $pendingReview,
        'edit_url' => (string) ($ctx['edit_url'] ?? ''),
    ];
}

/**
 * @param array<string,mixed>|null $overview
 * @return array{review_pending:?int,email_pending:?int}
 */
function fate_admin_members_overview_api_payload(?array $overview): array
{
    if (!is_array($overview)) {
        return ['review_pending' => null, 'email_pending' => null];
    }

    return [
        'review_pending' => isset($overview['review_pending']) ? (int) $overview['review_pending'] : null,
        'email_pending' => isset($overview['email_pending']) ? (int) $overview['email_pending'] : null,
    ];
}

/** @param array<string,mixed> $row */
function fate_admin_members_email_status_label(array $row): string
{
    if (!isset($row['email'])) {
        return '—';
    }
    $em = trim((string) ($row['email'] ?? ''));
    if ($em === '') {
        return '未填';
    }
    if (!isset($row['email_verified_at'])) {
        return '已填';
    }
    $ev = $row['email_verified_at'];

    return ($ev !== null && trim((string) $ev) !== '') ? '已驗證' : '待驗證';
}

/** @return list<string> */
function fate_admin_members_allowed_role_values(): array
{
    return ['member', 'mod', 'admin'];
}

function fate_admin_members_normalize_role_input(string $input): string
{
    $r = strtolower(trim($input));
    if ($r === 'admin') {
        return 'admin';
    }
    if ($r === 'mod') {
        return 'mod';
    }

    return '';
}

function fate_admin_members_role_input_value(?string $stored): string
{
    $r = strtolower(trim((string) $stored));
    if ($r === 'admin' || $r === 'mod') {
        return $r;
    }

    return 'member';
}

/** @return array<string,mixed>|null */
function fate_admin_members_fetch_by_id(PDO $pdo, int $memberId): ?array
{
    if ($memberId <= 0) {
        return null;
    }

    $qt = fate_admin_members_qualified_table();
    if ($qt === null) {
        return null;
    }

    $ac = FATE_MEMBER_ACTIVE_COLUMN;
    $hasEmail = fate_members_table_has_column($pdo, 'EMAIL');
    $hasEmailVerified = fate_members_table_has_column($pdo, 'EMAIL_VERIFIED_AT');
    $hasCreated = fate_members_table_has_column($pdo, 'CREATED_AT');
    $hasLastLogin = fate_members_table_has_column($pdo, 'LAST_LOGIN_AT');
    $hasLastIp = fate_members_table_has_column($pdo, 'LAST_LOGIN_IP');
    $hasMemberTier = fate_member_tier_column_exists($pdo);

    $cols = ['`id`', '`username`', '`display_name`', '`role`', sprintf('`%s` AS `_mem_active`', str_replace('`', '``', $ac))];
    if ($hasEmail) {
        $cols[] = '`email`';
    }
    if ($hasEmailVerified) {
        $cols[] = '`email_verified_at`';
    }
    if ($hasMemberTier) {
        $cols[] = '`member_tier`';
    }
    $hasApproved = fate_member_manual_approval_enabled() && fate_members_table_has_column($pdo, 'APPROVED_AT');
    if ($hasApproved) {
        $cols[] = '`approved_at`';
        $cols[] = '`approved_by`';
    }
    if ($hasCreated) {
        $cols[] = '`created_at`';
    }
    if ($hasLastLogin) {
        $cols[] = '`last_login_at`';
    }
    if ($hasLastIp) {
        $cols[] = '`last_login_ip`';
    }

    $sql = 'SELECT ' . implode(', ', $cols) . ' FROM ' . $qt . ' WHERE `id` = ? LIMIT 1';
    $st = $pdo->prepare($sql);
    $st->execute([$memberId]);
    $row = $st->fetch(PDO::FETCH_ASSOC);

    return is_array($row) ? $row : null;
}

function fate_admin_members_count_admins(PDO $pdo, ?int $excludeId = null): int
{
    $qt = fate_admin_members_qualified_table();
    if ($qt === null) {
        return 0;
    }
    $sql = "SELECT COUNT(*) FROM {$qt} WHERE LOWER(TRIM(COALESCE(`role`, ''))) = 'admin'";
    $params = [];
    if ($excludeId !== null && $excludeId > 0) {
        $sql .= ' AND `id` <> ?';
        $params[] = $excludeId;
    }
    $st = $pdo->prepare($sql);
    $st->execute($params);

    return (int) $st->fetchColumn();
}

/**
 * @param array{display_name?:string,role?:string,member_tier?:string,active?:bool,email_verified?:?bool,approve?:bool} $patch
 * @return array{ok:bool,err?:string}
 */
function fate_admin_members_update(PDO $pdo, int $memberId, array $patch, int $actingAdminId): array
{
    if ($memberId <= 0) {
        return ['ok' => false, 'err' => '無效的會員 ID'];
    }

    $row = fate_admin_members_fetch_by_id($pdo, $memberId);
    if ($row === null) {
        return ['ok' => false, 'err' => '找不到該會員'];
    }

    $qt = fate_admin_members_qualified_table();
    if ($qt === null) {
        return ['ok' => false, 'err' => '會員表設定未就緒'];
    }

    $ac = FATE_MEMBER_ACTIVE_COLUMN;
    $sets = [];
    $params = [];

    if (array_key_exists('display_name', $patch)) {
        $name = trim((string) $patch['display_name']);
        if (function_exists('mb_substr')) {
            $name = mb_substr($name, 0, 120);
        } elseif (strlen($name) > 120) {
            $name = substr($name, 0, 120);
        }
        $sets[] = '`display_name` = ?';
        $params[] = $name;
    }

    if (array_key_exists('role', $patch)) {
        $newRole = fate_admin_members_normalize_role_input((string) $patch['role']);
        $oldRole = fate_admin_members_normalize_role_input((string) ($row['role'] ?? ''));
        if ($oldRole === 'admin' && $newRole !== 'admin') {
            $others = fate_admin_members_count_admins($pdo, $memberId);
            if ($others < 1) {
                return ['ok' => false, 'err' => '不可變更：系統至少需保留一位管理員'];
            }
        }
        $sets[] = '`role` = ?';
        $params[] = $newRole;
    }

    if (array_key_exists('active', $patch)) {
        $active = !empty($patch['active']);
        if (!$active && $actingAdminId > 0 && $actingAdminId === $memberId) {
            return ['ok' => false, 'err' => '不可停用自己的帳號'];
        }
        if (!$active && fate_admin_members_normalize_role_input((string) ($row['role'] ?? '')) === 'admin') {
            $others = fate_admin_members_count_admins($pdo, $memberId);
            $willStayAdmin = !array_key_exists('role', $patch) || fate_admin_members_normalize_role_input((string) $patch['role']) === 'admin';
            if ($willStayAdmin && $others < 1) {
                return ['ok' => false, 'err' => '不可停用最後一位管理員'];
            }
        }
        $sets[] = sprintf('`%s` = ?', str_replace('`', '``', $ac));
        $params[] = $active ? 1 : 0;
    }

    if (array_key_exists('member_tier', $patch) && fate_member_tier_column_exists($pdo)) {
        $sets[] = '`member_tier` = ?';
        $params[] = fate_member_tier_normalize((string) $patch['member_tier']);
    }

    if (array_key_exists('email_verified', $patch) && fate_members_table_has_column($pdo, 'EMAIL_VERIFIED_AT')) {
        if ($patch['email_verified'] === true) {
            $sets[] = '`email_verified_at` = NOW()';
            if (!array_key_exists('member_tier', $patch) && fate_member_tier_column_exists($pdo)) {
                if (fate_member_tier_from_row($row) === 'register') {
                    $sets[] = "`member_tier` = 'formal'";
                }
            }
        } elseif ($patch['email_verified'] === false) {
            $sets[] = '`email_verified_at` = NULL';
            if (!array_key_exists('member_tier', $patch) && fate_member_tier_column_exists($pdo)) {
                if (fate_member_tier_from_row($row) !== 'blacklist') {
                    $sets[] = "`member_tier` = 'register'";
                }
            }
        }
    }

    if (!empty($patch['approve']) && fate_members_table_has_column($pdo, 'APPROVED_AT')) {
        $sets[] = '`approved_at` = NOW()';
        if (fate_members_table_has_column($pdo, 'APPROVED_BY')) {
            $sets[] = '`approved_by` = ' . ($actingAdminId > 0 ? (int) $actingAdminId : 'NULL');
        }
        if (!array_key_exists('active', $patch)) {
            $sets[] = sprintf('`%s` = 1', str_replace('`', '``', $ac));
        }
    }

    if ($sets === []) {
        return ['ok' => false, 'err' => '沒有可更新的欄位'];
    }

    $sql = 'UPDATE ' . $qt . ' SET ' . implode(', ', $sets) . ' WHERE `id` = ? LIMIT 1';
    $params[] = $memberId;

    try {
        $st = $pdo->prepare($sql);
        $st->execute($params);
    } catch (Throwable $e) {
        return ['ok' => false, 'err' => $e->getMessage()];
    }

    if ($st->rowCount() < 1 && $sets !== []) {
        // 資料相同時 rowCount 可能為 0，仍視為成功
    }

    return ['ok' => true];
}

/**
 * @return array{ok:bool,err?:string,active?:bool}
 */
/**
 * @return array{ok:bool,err?:string}
 */
function fate_admin_members_approve(PDO $pdo, int $memberId, int $actingAdminId): array
{
    return fate_admin_members_update($pdo, $memberId, ['approve' => true, 'active' => true], $actingAdminId);
}

function fate_admin_members_toggle_active(PDO $pdo, int $memberId, int $actingAdminId): array
{
    $row = fate_admin_members_fetch_by_id($pdo, $memberId);
    if ($row === null) {
        return ['ok' => false, 'err' => '找不到該會員'];
    }
    $currently = (int) ($row['_mem_active'] ?? 0) === 1;

    return fate_admin_members_update($pdo, $memberId, ['active' => !$currently], $actingAdminId);
}

/**
 * 後台手動新增會員。
 *
 * @param array{
 *   username:string,
 *   password:string,
 *   display_name?:string,
 *   email?:string,
 *   role?:string,
 *   member_tier?:string,
 *   active?:bool,
 *   email_verified?:bool,
 *   approve?:bool
 * } $input
 * @return array{ok:bool,id:?int,err?:string}
 */
function fate_admin_members_create(PDO $pdo, array $input, int $actingAdminId): array
{
    if (!fate_admin_member_auth_ready()) {
        return ['ok' => false, 'id' => null, 'err' => '會員資料庫未設定完成。'];
    }

    require_once __DIR__ . DIRECTORY_SEPARATOR . 'fate_member_register.php';
    require_once __DIR__ . DIRECTORY_SEPARATOR . 'fate_member_password.php';
    fate_member_email_bootstrap_schema($pdo);

    $cols = fate_member_table_columns($pdo);
    if ($cols === []) {
        return ['ok' => false, 'id' => null, 'err' => '無法讀取會員資料表結構。'];
    }
    if (!isset($cols['username'], $cols['password'])) {
        return ['ok' => false, 'id' => null, 'err' => '會員表需含 username、password 欄位。'];
    }

    $uv = fate_public_member_validate_username_candidate((string) ($input['username'] ?? ''));
    if (!$uv['ok']) {
        return ['ok' => false, 'id' => null, 'err' => $uv['err'] ?? '帳號格式不正確'];
    }
    $username = $uv['username'];

    $plainPwd = trim((string) ($input['password'] ?? ''));
    if ($plainPwd === '') {
        return ['ok' => false, 'id' => null, 'err' => '請輸入密碼。'];
    }
    $pwLen = function_exists('mb_strlen') ? mb_strlen($plainPwd, 'UTF-8') : strlen($plainPwd);
    if ($pwLen < 8) {
        return ['ok' => false, 'id' => null, 'err' => '密碼至少 8 字元。'];
    }
    $plainPwd = substr($plainPwd, 0, 72);

    $dn = trim((string) ($input['display_name'] ?? ''));
    if ($dn === '') {
        $dn = $username;
    }
    if (function_exists('mb_substr')) {
        $dn = mb_substr($dn, 0, 120);
    } elseif (strlen($dn) > 120) {
        $dn = substr($dn, 0, 120);
    }

    $mailNorm = strtolower(trim((string) ($input['email'] ?? '')));
    $wantEmailVerified = !empty($input['email_verified']);
    if ($mailNorm !== '') {
        $validEv = filter_var($mailNorm, FILTER_VALIDATE_EMAIL);
        if ($validEv === false) {
            return ['ok' => false, 'id' => null, 'err' => '請填寫有效的 Email。'];
        }
        $mailNorm = strtolower(trim((string) $validEv));
    } elseif ($wantEmailVerified) {
        return ['ok' => false, 'id' => null, 'err' => '請填寫 Email 才能標記為已驗證。'];
    }

    $db = FATE_MEMBER_DATABASE;
    $tbl = FATE_MEMBER_TABLE;
    $dbEsc = str_replace('`', '``', $db);
    $tblEsc = str_replace('`', '``', $tbl);

    try {
        $st = $pdo->prepare(sprintf('SELECT `id` FROM `%s`.`%s` WHERE `username` = ? LIMIT 1', $dbEsc, $tblEsc));
        $st->execute([$username]);
        if ((int) $st->fetchColumn() > 0) {
            return ['ok' => false, 'id' => null, 'err' => '此帳號已被使用，請換一個。'];
        }
    } catch (Throwable $e) {
        return ['ok' => false, 'id' => null, 'err' => '無法查詢會員表：' . $e->getMessage()];
    }

    if ($mailNorm !== '' && isset($cols['email'])) {
        $existingMail = fate_member_lookup_by_email($pdo, $mailNorm);
        if ($existingMail['found']) {
            return ['ok' => false, 'id' => null, 'err' => '此 Email 已被使用。'];
        }
    }

    $role = fate_admin_members_normalize_role_input((string) ($input['role'] ?? 'member'));
    $active = array_key_exists('active', $input) ? !empty($input['active']) : true;
    $approve = array_key_exists('approve', $input) ? !empty($input['approve']) : true;
    $hasManualReview = fate_member_manual_approval_enabled() && isset($cols['approved_at']);

    if ($hasManualReview && !$approve) {
        $active = false;
    }

    $hash = fate_member_password_hash_new($plainPwd);

    /** @var array<string,mixed> $rowVals */
    $rowVals = [
        'username' => $username,
        'password' => $hash,
    ];

    if (isset($cols['display_name'])) {
        $rowVals['display_name'] = $dn;
    }
    if (isset($cols['role'])) {
        $rowVals['role'] = $role;
    }
    if ($mailNorm !== '' && isset($cols['email'])) {
        $rowVals['email'] = $mailNorm;
    }
    if (isset($cols['email_verified_at'])) {
        if ($wantEmailVerified && $mailNorm !== '') {
            $rowVals['email_verified_at'] = date('Y-m-d H:i:s');
        } else {
            $rowVals['email_verified_at'] = null;
        }
    }
    if (fate_member_tier_column_exists($pdo)) {
        $tierIn = isset($input['member_tier']) ? (string) $input['member_tier'] : '';
        if ($tierIn !== '') {
            $rowVals['member_tier'] = fate_member_tier_normalize($tierIn);
        } elseif ($wantEmailVerified) {
            $rowVals['member_tier'] = 'formal';
        } else {
            $rowVals['member_tier'] = 'register';
        }
    }
    if ($hasManualReview) {
        if ($approve) {
            $rowVals['approved_at'] = date('Y-m-d H:i:s');
            if (isset($cols['approved_by'])) {
                $rowVals['approved_by'] = $actingAdminId > 0 ? $actingAdminId : null;
            }
        } else {
            $rowVals['approved_at'] = null;
            if (isset($cols['approved_by'])) {
                $rowVals['approved_by'] = null;
            }
        }
    }

    $acColLow = strtolower(FATE_MEMBER_ACTIVE_COLUMN);
    $activeInserted = false;
    if ($acColLow !== '' && isset($cols[$acColLow])) {
        $rowVals[FATE_MEMBER_ACTIVE_COLUMN] = $active ? 1 : 0;
        $activeInserted = true;
    }
    if (!$activeInserted && isset($cols['status'])) {
        $rowVals['status'] = $active ? 1 : 0;
        $activeInserted = true;
    }
    if (!$activeInserted) {
        return ['ok' => false, 'id' => null, 'err' => '會員表缺少啟用欄位。'];
    }

    try {
        $colNames = array_keys($rowVals);
        $quoted = [];
        foreach ($colNames as $cn) {
            $quoted[] = '`' . str_replace('`', '``', (string) $cn) . '`';
        }
        $holders = implode(', ', array_fill(0, count($rowVals), '?'));
        $sql = sprintf(
            'INSERT INTO `%s`.`%s` (%s) VALUES (%s)',
            $dbEsc,
            $tblEsc,
            implode(', ', $quoted),
            $holders
        );
        $ins = $pdo->prepare($sql);
        $ins->execute(array_values($rowVals));
        $newId = (int) $pdo->lastInsertId();
        if ($newId <= 0) {
            return ['ok' => false, 'id' => null, 'err' => '新增失敗：未取得會員 ID'];
        }

        return ['ok' => true, 'id' => $newId];
    } catch (Throwable $e) {
        $msg = $e->getMessage();
        if (str_contains($msg, '1062') && str_contains($msg, 'email')) {
            return ['ok' => false, 'id' => null, 'err' => '此 Email 已被使用。'];
        }
        if (str_contains($msg, '1062') && str_contains($msg, 'username')) {
            return ['ok' => false, 'id' => null, 'err' => '此帳號已被使用，請換一個。'];
        }

        return ['ok' => false, 'id' => null, 'err' => '新增失敗：' . $msg];
    }
}

/**
 * @return array{
 *   username:string,
 *   password:string,
 *   display_name:string,
 *   email:string,
 *   role:string,
 *   member_tier:string,
 *   active:bool,
 *   email_verified:bool,
 *   approve:bool
 * }
 */
function fate_admin_members_create_input_from_request(array $source): array
{
    $roleIn = isset($source['role']) && is_string($source['role']) ? $source['role'] : 'member';
    $statusIn = isset($source['status']) && is_string($source['status']) ? $source['status'] : '1';
    $approveIn = isset($source['approve']) && is_string($source['approve']) ? $source['approve'] : '1';

    return [
        'username' => isset($source['username']) && is_string($source['username']) ? $source['username'] : '',
        'password' => isset($source['password']) && is_string($source['password']) ? $source['password'] : '',
        'display_name' => isset($source['display_name']) && is_string($source['display_name']) ? $source['display_name'] : '',
        'email' => isset($source['email']) && is_string($source['email']) ? $source['email'] : '',
        'role' => $roleIn,
        'member_tier' => isset($source['member_tier']) && is_string($source['member_tier']) ? $source['member_tier'] : 'register',
        'active' => $statusIn === '1',
        'email_verified' => !empty($source['email_verified']),
        'approve' => $approveIn === '1',
    ];
}
