akmon/scripts/training-event-simulator/ak_device和ak_users.md

create table
  public.ak_devices (
    id uuid not null default gen_random_uuid (),
    user_id uuid null,
    device_type character varying(32) not null,
    device_name character varying(64) null,
    device_mac character varying(64) null,
    bind_time timestamp with time zone null default now(),
    status character varying(16) null default 'active'::character varying,
    extra jsonb null,
    constraint ak_devices_pkey primary key (id),
    constraint ak_devices_user_id_fkey foreign key (user_id) references ak_users (id) on delete cascade
  ) tablespace pg_default;

create index if not exists idx_devices_user_id on public.ak_devices using btree (user_id) tablespace pg_default;

create table
  public.ak_users (
    id uuid not null default gen_random_uuid (),
    username character varying(64) not null,
    email character varying(128) not null,
    password_hash character varying(256) not null default gen_random_uuid (),
    gender character varying(16) null default 'other'::character varying,
    birthday date null,
    height_cm integer null,
    weight_kg integer null,
    avatar_url text null,
    region_id uuid null,
    school_id uuid null,
    grade_id uuid null,
    class_id uuid null,
    role character varying(32) null default 'student'::character varying,
    created_at timestamp with time zone null default now(),
    updated_at timestamp with time zone null default now(),
    auth_id uuid null default uid (),
    preferred_language uuid null,
    bio text null,
    phone text null,
    status character varying null,
    mall_status integer null default 1,
    mall_type integer null default 1,
    last_login_ip inet null,
    total_orders integer null default 0,
    total_spent numeric(12, 2) null default 0.00,
    user_level integer null default 1,
    points integer null default 0,
    verified_status integer null default 0,
    nickname character varying null,
    user_type character varying null,
    registration_source character varying null,
    real_name character varying null,
    qq character varying null,
    wechat character varying null,
    alipay character varying null,
    constraint ak_users_pkey primary key (id),
    constraint ak_users_email_key unique (email),
    constraint ak_users_auth_id_key unique (auth_id),
    constraint ak_users_auth_id_fkey foreign key (auth_id) references users (id) on delete cascade,
    constraint ak_users_class_id_fkey foreign key (class_id) references ak_classes (id),
    constraint ak_users_grade_id_fkey foreign key (grade_id) references ak_grades (id),
    constraint ak_users_preferred_language_fkey foreign key (preferred_language) references ak_languages (id),
    constraint ak_users_region_id_fkey foreign key (region_id) references ak_regions (id),
    constraint ak_users_school_id_fkey foreign key (school_id) references ak_schools (id),
    constraint chk_ak_users_verified_status check ((verified_status = any (array[0, 1, 2]))),
    constraint ak_users_total_orders_check check ((total_orders >= 0)),
    constraint ak_users_total_spent_check check ((total_spent >= (0)::numeric)),
    constraint ak_users_user_level_check check (
      (
        (user_level >= 1)
        and (user_level <= 10)
      )
    ),
    constraint ak_users_verified_status_check check ((verified_status = any (array[0, 1, 2]))),
    constraint ak_users_points_check check ((points >= 0)),
    constraint chk_ak_users_mall_status check ((mall_status = any (array[1, 2]))),
    constraint chk_ak_users_mall_type check ((mall_type = any (array[1, 2, 3])))
  ) tablespace pg_default;

create index if not exists idx_users_region_id on public.ak_users using btree (region_id) tablespace pg_default;

create index if not exists idx_users_school_id on public.ak_users using btree (school_id) tablespace pg_default;

create index if not exists idx_users_grade_id on public.ak_users using btree (grade_id) tablespace pg_default;

create index if not exists idx_users_class_id on public.ak_users using btree (class_id) tablespace pg_default;

create index if not exists idx_ak_users_mall_status on public.ak_users using btree (mall_status) tablespace pg_default;

create index if not exists idx_ak_users_mall_type on public.ak_users using btree (mall_type) tablespace pg_default;

create index if not exists idx_ak_users_total_orders on public.ak_users using btree (total_orders desc) tablespace pg_default;

create index if not exists idx_ak_users_total_spent on public.ak_users using btree (total_spent desc) tablespace pg_default;

create index if not exists idx_ak_users_level on public.ak_users using btree (user_level) tablespace pg_default;

create index if not exists idx_ak_users_points on public.ak_users using btree (points desc) tablespace pg_default;

create index if not exists idx_ak_users_verified on public.ak_users using btree (verified_status) tablespace pg_default;

create trigger tr_create_user_message_preferences
after insert on ak_users for each row
execute function create_default_message_preferences ();


class_id为bc333301-78cd-4ef0-a123-456789012345

帮我把classid为特定的所有ak_users生成模拟的ak_devices的记录

WITH target_users AS (
    SELECT u.id AS user_id
    FROM ak_users u
    LEFT JOIN ak_devices d ON d.user_id = u.id
    WHERE u.class_id = 'bc333301-78cd-4ef0-a123-456789012345'
      AND d.id IS NULL
)
INSERT INTO ak_devices (
    user_id,
    device_type,
    device_name,
    device_mac,
    bind_time,
    status,
    extra
)
SELECT
    tu.user_id,
    'wristband' AS device_type,
    CONCAT('模拟手环-', RIGHT(tu.user_id::text, 6)) AS device_name,
    CONCAT('FAKE-MAC-', UPPER(REPLACE(SUBSTRING(tu.user_id::text, 1, 12), '-', ''))) AS device_mac,
    NOW() AS bind_time,
    'active' AS status,
    jsonb_build_object(
        'source', 'mock-seed',
        'note', 'Generated for demo class bc333301-78cd-4ef0-a123-456789012345'
    ) AS extra
FROM target_users tu;