-- 初始化数据库脚本

-- 设置字符集
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;

-- 插入示例数据（仅开发环境）
-- 这些数据在生产环境中应该被删除或注释掉