-- 数据库表名规范化脚本
-- 执行前请备份数据库！

USE tg_manage;

-- 禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;

-- 1. 备份当前表（创建备份表）
CREATE TABLE IF NOT EXISTS backup_tg_account AS SELECT * FROM tg_account WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_firstname AS SELECT * FROM tg_firstname WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_lastname AS SELECT * FROM tg_lastname WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_group AS SELECT * FROM tg_group WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_message AS SELECT * FROM tg_message WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_config AS SELECT * FROM tg_config WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_script AS SELECT * FROM tg_script WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_script_article AS SELECT * FROM tg_script_article WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_script_project AS SELECT * FROM tg_script_project WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_script_task AS SELECT * FROM tg_script_task WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_dc AS SELECT * FROM tg_dc WHERE 1=0;
CREATE TABLE IF NOT EXISTS backup_tg_telegram_users AS SELECT * FROM tg_telegram_users WHERE 1=0;

-- 插入备份数据
INSERT INTO backup_tg_account SELECT * FROM tg_account;
INSERT INTO backup_tg_firstname SELECT * FROM tg_firstname;
INSERT INTO backup_tg_lastname SELECT * FROM tg_lastname;
INSERT INTO backup_tg_group SELECT * FROM tg_group;
INSERT INTO backup_tg_message SELECT * FROM tg_message;
INSERT INTO backup_tg_config SELECT * FROM tg_config;
INSERT INTO backup_tg_script SELECT * FROM tg_script;
INSERT INTO backup_tg_script_article SELECT * FROM tg_script_article;
INSERT INTO backup_tg_script_project SELECT * FROM tg_script_project;
INSERT INTO backup_tg_script_task SELECT * FROM tg_script_task;
INSERT INTO backup_tg_dc SELECT * FROM tg_dc;
INSERT INTO backup_tg_telegram_users SELECT * FROM tg_telegram_users;

-- 2. 重命名表到新的规范化名称
RENAME TABLE tg_account TO accounts;
RENAME TABLE tg_firstname TO firstnames;
RENAME TABLE tg_lastname TO lastnames;
RENAME TABLE tg_group TO chat_groups;
RENAME TABLE tg_message TO messages;
RENAME TABLE tg_config TO configs;
RENAME TABLE tg_script TO scripts;
RENAME TABLE tg_script_article TO script_articles;
RENAME TABLE tg_script_project TO script_projects;
RENAME TABLE tg_script_task TO script_tasks;
RENAME TABLE tg_dc TO data_centers;
RENAME TABLE tg_telegram_users TO telegram_users;

-- 3. 删除旧的c_前缀表（如果存在）
DROP TABLE IF EXISTS c_tg_account;
DROP TABLE IF EXISTS c_firstname;
DROP TABLE IF EXISTS c_lastname;
DROP TABLE IF EXISTS c_group;
DROP TABLE IF EXISTS c_message;
DROP TABLE IF EXISTS c_config;
DROP TABLE IF EXISTS c_script;
DROP TABLE IF EXISTS c_script_article;
DROP TABLE IF EXISTS c_script_project;
DROP TABLE IF EXISTS c_script_task;

-- 4. 删除m_前缀重复表（如果存在）
DROP TABLE IF EXISTS m_tg_account;
DROP TABLE IF EXISTS m_firstname;
DROP TABLE IF EXISTS m_lastname;
DROP TABLE IF EXISTS m_group;
DROP TABLE IF EXISTS m_message;
DROP TABLE IF EXISTS m_config;

-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;

-- 5. 验证重命名结果
SELECT 'Table normalization completed successfully!' as status;

-- 显示所有表
SHOW TABLES;

-- 显示每个表的记录数
SELECT 'accounts' as table_name, COUNT(*) as count FROM accounts
UNION ALL
SELECT 'firstnames', COUNT(*) FROM firstnames
UNION ALL
SELECT 'lastnames', COUNT(*) FROM lastnames
UNION ALL
SELECT 'chat_groups', COUNT(*) FROM chat_groups
UNION ALL
SELECT 'messages', COUNT(*) FROM messages
UNION ALL
SELECT 'configs', COUNT(*) FROM configs
UNION ALL
SELECT 'scripts', COUNT(*) FROM scripts
UNION ALL
SELECT 'script_articles', COUNT(*) FROM script_articles
UNION ALL
SELECT 'script_projects', COUNT(*) FROM script_projects
UNION ALL
SELECT 'script_tasks', COUNT(*) FROM script_tasks
UNION ALL
SELECT 'data_centers', COUNT(*) FROM data_centers
UNION ALL
SELECT 'telegram_users', COUNT(*) FROM telegram_users;