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>
81 lines
2.7 KiB
SQL
81 lines
2.7 KiB
SQL
-- 初始化数据库脚本
|
|
|
|
-- 设置字符集
|
|
SET NAMES utf8mb4;
|
|
SET CHARACTER SET utf8mb4;
|
|
|
|
-- 创建数据库（如果不存在）
|
|
CREATE DATABASE IF NOT EXISTS `tg_manage`
|
|
CHARACTER SET utf8mb4
|
|
COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- 使用数据库
|
|
USE `tg_manage`;
|
|
|
|
-- 创建初始管理员用户
|
|
-- 密码是: admin123 (bcrypt加密)
|
|
INSERT IGNORE INTO `admins` (`id`, `username`, `password`, `role`, `status`, `created_at`, `updated_at`)
|
|
VALUES (
|
|
1,
|
|
'admin',
|
|
'$2b$12$LQv3c1yqBWVHxkd0LQ1u/ue5csar/oU8.vo/1B2F3nCpEHE.sN.K6',
|
|
'admin',
|
|
'active',
|
|
NOW(),
|
|
NOW()
|
|
);
|
|
|
|
-- 创建系统配置表数据
|
|
INSERT IGNORE INTO `configs` (`key`, `value`, `description`, `type`, `created_at`, `updated_at`)
|
|
VALUES
|
|
('system.name', 'Telegram管理系统', '系统名称', 'string', NOW(), NOW()),
|
|
('system.version', '2.0.0', '系统版本', 'string', NOW(), NOW()),
|
|
('system.maintenance', 'false', '维护模式', 'boolean', NOW(), NOW()),
|
|
('telegram.api_id', '', 'Telegram API ID', 'string', NOW(), NOW()),
|
|
('telegram.api_hash', '', 'Telegram API Hash', 'string', NOW(), NOW()),
|
|
('proxy.check_interval', '300000', '代理检查间隔(ms)', 'number', NOW(), NOW()),
|
|
('sms.default_platform', '1', '默认短信平台ID', 'number', NOW(), NOW()),
|
|
('task.max_concurrent', '5', '最大并发任务数', 'number', NOW(), NOW()),
|
|
('analytics.retention_days', '90', '分析数据保留天数', 'number', NOW(), NOW());
|
|
|
|
-- 创建索引优化
|
|
CREATE INDEX IF NOT EXISTS `idx_analytics_records_timestamp` ON `analytics_records` (`timestamp`);
|
|
CREATE INDEX IF NOT EXISTS `idx_analytics_records_event_type` ON `analytics_records` (`event_type`);
|
|
CREATE INDEX IF NOT EXISTS `idx_analytics_records_user_id` ON `analytics_records` (`user_id`);
|
|
CREATE INDEX IF NOT EXISTS `idx_analytics_summaries_date` ON `analytics_summaries` (`date`);
|
|
CREATE INDEX IF NOT EXISTS `idx_task_executions_status` ON `task_executions` (`status`);
|
|
CREATE INDEX IF NOT EXISTS `idx_proxy_check_logs_check_time` ON `proxy_check_logs` (`check_time`);
|
|
|
|
-- 创建视图用于快速查询
|
|
CREATE OR REPLACE VIEW `v_active_tg_accounts` AS
|
|
SELECT
|
|
id,
|
|
phone,
|
|
first_name,
|
|
last_name,
|
|
username,
|
|
status,
|
|
last_active_at,
|
|
created_at
|
|
FROM `tg_accounts`
|
|
WHERE `status` = 'active'
|
|
AND `deleted_at` IS NULL;
|
|
|
|
CREATE OR REPLACE VIEW `v_recent_tasks` AS
|
|
SELECT
|
|
t.id,
|
|
t.name,
|
|
t.type,
|
|
t.status,
|
|
t.created_at,
|
|
te.status as execution_status,
|
|
te.started_at,
|
|
te.completed_at,
|
|
te.error_message
|
|
FROM `tasks` t
|
|
LEFT JOIN `task_executions` te ON t.id = te.task_id
|
|
WHERE t.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
|
|
ORDER BY t.created_at DESC;
|
|
|
|
-- 插入示例数据（仅开发环境）
|
|
-- 这些数据在生产环境中应该被删除或注释掉 |