water/server/scripts/migrate_rental_user_member_fields.php

<?php
require dirname(__DIR__).'/vendor/autoload.php';
\Dotenv\Dotenv::createUnsafeMutable(dirname(__DIR__))->load();
\support\App::loadAllConfig(['route']);
use support\think\Db;

/**
 * 会员开通/续费后台能力：sa_rental_user 加 member_expire_at/member_card_type 字段 + 回填。
 * 配合 20261009_add_rental_user_member_fields.sql 与 20261009_add_member_activation_button_permission.sql。
 * 可重复执行。
 */

if (!in_array('--apply', $argv, true)) {
    echo "Plan: add sa_rental_user.member_expire_at / member_card_type + backfill from sa_member_activation_record + insert UserList button menu.\n";
    echo "Run with --apply to execute.\n";
    exit;
}

$db = Db::connect();
$dbName = $db->getConfig('database');

// 1) ALTER TABLE sa_rental_user
$hasExpireAt = (int) $db->scalar(
    "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME='sa_rental_user' AND COLUMN_NAME='member_expire_at'",
    [$dbName]
);
$hasCardType = (int) $db->scalar(
    "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME='sa_rental_user' AND COLUMN_NAME='member_card_type'",
    [$dbName]
);
if (($hasExpireAt === 0) !== ($hasCardType === 0)) {
    throw new RuntimeException('会员字段仅存在一个，请先核对表结构后再执行迁移');
}
if ($hasExpireAt === 0) {
    $db->exec("ALTER TABLE `sa_rental_user`
        ADD COLUMN `member_expire_at` DATETIME NULL COMMENT '会员到期时间' AFTER `card_gift_balance`,
        ADD COLUMN `member_card_type` TINYINT NOT NULL DEFAULT 0 COMMENT '当前会员卡类型 0月卡 1季卡 2半年卡 3年卡' AFTER `member_expire_at`,
        ADD KEY `idx_rental_user_member_expire` (`member_expire_at`)");
    echo "Added columns member_expire_at / member_card_type to sa_rental_user.\n";
} else {
    echo "Columns already exist; skipping ALTER.\n";
}

// 2) Backfill
$updated = $db->exec("UPDATE `sa_rental_user` u
    INNER JOIN (
        SELECT r.user_id,
               CAST(SUBSTRING_INDEX(GROUP_CONCAT(r.card_type ORDER BY r.expire_time DESC), ',', 1) AS UNSIGNED) AS latest_card_type,
               MAX(r.expire_time) AS latest_expire_time
        FROM `sa_member_activation_record` r
        WHERE r.`delete_time` IS NULL
          AND r.`status` = 1
          AND r.`expire_time` >= NOW()
        GROUP BY r.user_id
    ) latest ON latest.user_id = u.id
    AND u.`member_expire_at` IS NULL
    SET u.`member_card_type` = latest.latest_card_type,
        u.`member_expire_at` = latest.latest_expire_time");
echo "Backfilled sa_rental_user.member_expire_at / member_card_type from activation records: {$updated} rows.\n";

// 3) Insert UserList 下的「用户会员开通」按钮菜单
$menuId = (int) $db->scalar(
    "SELECT id FROM sa_system_menu WHERE slug='core:user:list:memberActivation' AND delete_time IS NULL LIMIT 1"
);
if ($menuId === 0) {
    $parentId = (int) $db->scalar(
        "SELECT id FROM sa_system_menu WHERE `code`='UserList' AND delete_time IS NULL LIMIT 1"
    );
    if ($parentId === 0) {
        echo "WARN: UserList menu not found; skipping button menu insert.\n";
    } else {
        $now = date('Y-m-d H:i:s');
        $db->execute(
            "INSERT INTO sa_system_menu
                (parent_id, name, code, slug, type, path, component, method, icon, sort,
                 link_url, is_iframe, is_keep_alive, is_hidden, is_fixed_tab, is_full_page,
                 generate_id, generate_key, status, remark, created_by, updated_by, create_time, update_time, delete_time)
             VALUES (?, ?, ?, ?, ?, NULL, NULL, ?, NULL, ?, NULL, 2, 2, 2, 2, 2, 0, NULL, 1, ?, 1, 1, ?, ?, NULL)",
            [$parentId, '用户会员开通', 'RentalUserMemberActivation', 'core:user:list:memberActivation', 3, 'POST', 110,
             '管理员对当前可见小程序租借用户开通或续费会员', $now, $now]
        );
        $menuId = (int) $db->getPdo()->lastInsertId();
        echo "Inserted menu #{$menuId} (用户会员开通).\n";
    }
} else {
    echo "Menu core:user:list:memberActivation already exists (#{$menuId}); updating label.\n";
    $db->execute(
        "UPDATE sa_system_menu SET name=?, method='POST', remark=?, update_time=NOW()
         WHERE id=? AND delete_time IS NULL",
        ['用户会员开通', '管理员对当前可见小程序租借用户开通或续费会员', $menuId]
    );
}

// 4) 自动赋给已有 UserList 权限的角色
if ($menuId > 0) {
    $granted = $db->execute(
        "INSERT INTO sa_system_role_menu (role_id, menu_id)
         SELECT DISTINCT srm.role_id, ? FROM sa_system_role_menu srm
         INNER JOIN sa_system_menu sm ON sm.id = srm.menu_id AND sm.code='UserList' AND sm.delete_time IS NULL
         LEFT JOIN sa_system_role_menu existing
                   ON existing.role_id = srm.role_id AND existing.menu_id = ?
         WHERE existing.id IS NULL",
        [$menuId, $menuId]
    );
    echo "Granted button permission to {$granted} role-menu pairs.\n";
}

echo "Done.\n";