"""
性能优化脚本 - 为 site_tags 表添加索引
"""
from app import create_app, db

app = create_app()

def add_index_safe(index_name, table_name, column_name):
    """安全添加索引，如果已存在则跳过"""
    try:
        # 先检查索引是否存在
        result = db.session.execute(db.text(f"""
            SELECT COUNT(*) as cnt FROM information_schema.statistics
            WHERE table_schema = DATABASE()
            AND table_name = '{table_name}'
            AND index_name = '{index_name}'
        """)).fetchone()

        if result[0] > 0:
            print(f"⊙ {index_name} 索引已存在，跳过")
            return

        # 创建索引
        db.session.execute(db.text(f"""
            CREATE INDEX {index_name} ON {table_name}({column_name});
        """))
        db.session.commit()
        print(f"✓ 已添加 {index_name} 索引")
    except Exception as e:
        db.session.rollback()
        print(f"✗ 添加 {index_name} 失败: {e}")

with app.app_context():
    print("开始添加索引...\n")

    # 为 site_tags 表添加索引
    add_index_safe('idx_site_tags_site_id', 'site_tags', 'site_id')
    add_index_safe('idx_site_tags_tag_id', 'site_tags', 'tag_id')

    # 为 sites 表添加常用查询字段索引
    add_index_safe('idx_sites_is_active', 'sites', 'is_active')
    add_index_safe('idx_sites_view_count', 'sites', 'view_count')

    print("\n索引添加完成！")
