237c7802e5
Some checks failed
Deploy / deploy (push) Has been cancelled
Full-stack web application for Telegram management - Frontend: Vue 3 + Vben Admin - Backend: NestJS - Features: User management, group broadcast, statistics 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <noreply@anthropic.com>
67 lines
4.7 KiB
SQL
67 lines
4.7 KiB
SQL
-- Create Telegram Users Table
|
|
USE tg_manage;
|
|
|
|
-- 创建 Telegram 用户表
|
|
CREATE TABLE IF NOT EXISTS `tg_telegram_users` (
|
|
`id` bigint NOT NULL COMMENT 'Telegram用户ID',
|
|
`username` varchar(100) DEFAULT NULL COMMENT '用户名',
|
|
`first_name` varchar(100) DEFAULT NULL COMMENT '名',
|
|
`last_name` varchar(100) DEFAULT NULL COMMENT '姓',
|
|
`phone` varchar(20) DEFAULT NULL COMMENT '电话号码',
|
|
`bio` text COMMENT '个人简介',
|
|
`is_bot` tinyint(1) DEFAULT '0' COMMENT '是否机器人',
|
|
`is_verified` tinyint(1) DEFAULT '0' COMMENT '是否认证账号',
|
|
`is_premium` tinyint(1) DEFAULT '0' COMMENT '是否Premium用户',
|
|
`is_scam` tinyint(1) DEFAULT '0' COMMENT '是否诈骗账号',
|
|
`is_fake` tinyint(1) DEFAULT '0' COMMENT '是否虚假账号',
|
|
`is_restricted` tinyint(1) DEFAULT '0' COMMENT '是否受限账号',
|
|
`is_support` tinyint(1) DEFAULT '0' COMMENT '是否官方支持账号',
|
|
`language_code` varchar(10) DEFAULT NULL COMMENT '语言代码',
|
|
`status` enum('online','offline','recently','last_week','last_month') DEFAULT 'offline' COMMENT '在线状态',
|
|
`profile_photo` text COMMENT '头像URL',
|
|
`last_seen_at` datetime DEFAULT NULL COMMENT '最后上线时间',
|
|
`created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
`updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_username` (`username`),
|
|
KEY `idx_phone` (`phone`),
|
|
KEY `idx_status` (`status`),
|
|
KEY `idx_is_bot` (`is_bot`),
|
|
KEY `idx_is_verified` (`is_verified`),
|
|
KEY `idx_is_premium` (`is_premium`),
|
|
KEY `idx_created_at` (`created_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Telegram用户表';
|
|
|
|
-- 插入一些示例数据
|
|
INSERT INTO `tg_telegram_users` (`id`, `username`, `first_name`, `last_name`, `phone`, `bio`, `is_bot`, `is_verified`, `is_premium`, `is_scam`, `is_fake`, `is_restricted`, `is_support`, `language_code`, `status`, `profile_photo`, `last_seen_at`) VALUES
|
|
(777000, 'Telegram', 'Telegram', NULL, NULL, 'Official Telegram Support', 0, 1, 0, 0, 0, 0, 1, 'en', 'online', 'https://telegram.org/img/t_logo.png', NOW()),
|
|
(1087968824, 'GroupAnonymousBot', 'Group', 'Anonymous Bot', NULL, 'Anonymous Admin', 1, 1, 0, 0, 0, 0, 1, 'en', 'online', NULL, NOW()),
|
|
(136817688, 'Channel_Bot', 'Channel', 'Bot', NULL, 'Telegram Channel Bot', 1, 1, 0, 0, 0, 0, 1, 'en', 'online', NULL, NOW()),
|
|
(12345678, 'john_doe', 'John', 'Doe', '+1234567890', 'Software Developer', 0, 0, 1, 0, 0, 0, 0, 'en', 'recently', NULL, DATE_SUB(NOW(), INTERVAL 30 MINUTE)),
|
|
(87654321, 'jane_smith', 'Jane', 'Smith', '+0987654321', 'Designer & Content Creator', 0, 1, 1, 0, 0, 0, 0, 'en', 'last_week', NULL, DATE_SUB(NOW(), INTERVAL 3 DAY)),
|
|
(11111111, 'scammer_account', 'Fake', 'User', NULL, 'This is a scam account', 0, 0, 0, 1, 0, 0, 0, 'en', 'offline', NULL, DATE_SUB(NOW(), INTERVAL 30 DAY)),
|
|
(22222222, 'fake_profile', 'Fake', 'Profile', NULL, 'Impersonating someone else', 0, 0, 0, 0, 1, 0, 0, 'en', 'offline', NULL, DATE_SUB(NOW(), INTERVAL 15 DAY)),
|
|
(33333333, 'restricted_user', 'Restricted', 'User', NULL, 'Account has been restricted', 0, 0, 0, 0, 0, 1, 0, 'zh', 'last_month', NULL, DATE_SUB(NOW(), INTERVAL 20 DAY)),
|
|
(44444444, 'premium_user', 'Premium', 'Member', '+1122334455', 'Telegram Premium subscriber', 0, 0, 1, 0, 0, 0, 0, 'es', 'online', NULL, NOW()),
|
|
(55555555, 'verified_celeb', 'Celebrity', 'Account', NULL, 'Verified public figure', 0, 1, 1, 0, 0, 0, 0, 'fr', 'recently', NULL, DATE_SUB(NOW(), INTERVAL 2 HOUR)),
|
|
(66666666, 'echo_bot', 'Echo', 'Bot', NULL, 'A simple echo bot', 1, 0, 0, 0, 0, 0, 0, 'en', 'online', NULL, NOW()),
|
|
(77777777, 'music_lover', 'Music', 'Lover', '+2233445566', 'I love music and concerts', 0, 0, 0, 0, 0, 0, 0, 'de', 'last_week', NULL, DATE_SUB(NOW(), INTERVAL 5 DAY)),
|
|
(88888888, 'tech_guru', 'Tech', 'Guru', NULL, 'Technology enthusiast and blogger', 0, 1, 0, 0, 0, 0, 0, 'ja', 'recently', NULL, DATE_SUB(NOW(), INTERVAL 1 HOUR)),
|
|
(99999999, 'crypto_trader', 'Crypto', 'Trader', '+3344556677', 'Cryptocurrency trader and investor', 0, 0, 1, 0, 0, 0, 0, 'ko', 'offline', NULL, DATE_SUB(NOW(), INTERVAL 12 HOUR)),
|
|
(10101010, 'news_channel', 'News', 'Channel', NULL, 'Daily news and updates', 1, 1, 0, 0, 0, 0, 0, 'en', 'online', NULL, NOW());
|
|
|
|
-- 验证数据
|
|
SELECT COUNT(*) as total_users FROM tg_telegram_users;
|
|
SELECT
|
|
status,
|
|
COUNT(*) as count
|
|
FROM tg_telegram_users
|
|
GROUP BY status;
|
|
|
|
SELECT
|
|
SUM(CASE WHEN is_bot = 1 THEN 1 ELSE 0 END) as bots,
|
|
SUM(CASE WHEN is_verified = 1 THEN 1 ELSE 0 END) as verified,
|
|
SUM(CASE WHEN is_premium = 1 THEN 1 ELSE 0 END) as premium,
|
|
SUM(CASE WHEN is_scam = 1 THEN 1 ELSE 0 END) as scam,
|
|
SUM(CASE WHEN is_fake = 1 THEN 1 ELSE 0 END) as fake
|
|
FROM tg_telegram_users; |