"""审计字段数据库迁移脚本"""
import pymysql

conn = pymysql.connect(
    host='10.1.1.122',
    user='nginxserver',
    password='mokee.',
    database='nginxserver',
    charset='utf8mb4',
    autocommit=True
)
cur = conn.cursor()

tables = ['forwarding_rules', 'dns_zone_config', 'certificates']
columns = [
    ('created_by', 'VARCHAR(100) DEFAULT NULL', '创建人'),
    ('updated_by', 'VARCHAR(100) DEFAULT NULL', '更新人'),
]

for table in tables:
    for col_name, col_def, col_comment in columns:
        cur.execute(f"""SELECT COLUMN_NAME FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA='nginxserver' AND TABLE_NAME='{table}' AND COLUMN_NAME='{col_name}'""")
        if not cur.fetchone():
            cur.execute(f"ALTER TABLE {table} ADD COLUMN {col_name} {col_def} COMMENT '{col_comment}'")
            print(f'{table}.{col_name} 已添加')
        else:
            print(f'{table}.{col_name} 已存在，跳过')

# 新建 dns_records 表
cur.execute("""SELECT TABLE_NAME FROM information_schema.TABLES
    WHERE TABLE_SCHEMA='nginxserver' AND TABLE_NAME='dns_records'""")
if not cur.fetchone():
    cur.execute("""
        CREATE TABLE dns_records (
            id            BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
            record_id     VARCHAR(128) NOT NULL COMMENT '阿里云解析记录ID',
            domain_zone   VARCHAR(255) NOT NULL COMMENT '主域名',
            rr            VARCHAR(255) NOT NULL COMMENT '主机记录',
            record_type   VARCHAR(20)  NOT NULL COMMENT '记录类型: A/CNAME/TXT',
            record_value  VARCHAR(500) NOT NULL COMMENT '记录值',
            ttl           INT          DEFAULT 600 COMMENT 'TTL(秒)',
            status        TINYINT      DEFAULT 1 COMMENT '0-已删除, 1-正常',
            created_by    VARCHAR(100) DEFAULT NULL COMMENT '创建人',
            updated_by    VARCHAR(100) DEFAULT NULL COMMENT '更新人',
            created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
            updated_at    DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
            UNIQUE KEY uk_record_id (record_id),
            INDEX idx_domain_zone (domain_zone),
            INDEX idx_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        COMMENT='DNS解析记录本地审计表'
    """)
    print('dns_records 表已创建')
else:
    print('dns_records 表已存在，跳过')

# 验证
print()
print('=== 验证结果 ===')
cur.execute("""SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA='nginxserver'
      AND TABLE_NAME IN ('forwarding_rules', 'dns_zone_config', 'certificates')
      AND COLUMN_NAME IN ('created_by', 'updated_by')
    ORDER BY TABLE_NAME, COLUMN_NAME""")
for row in cur.fetchall():
    print(row)

print()
cur.execute('SHOW CREATE TABLE dns_records')
row = cur.fetchone()
if row:
    print('dns_records 表已就绪')

cur.close()
conn.close()
print('\n数据库迁移完成！')