import sqlite3
import os
from pathlib import Path

# 定义路径
db_path = Path(r'C:\Users\jerry\PyCharmProjects\footviz\footviz\data\football.db')
team_player_images_dir = Path(r'C:\Users\jerry\PyCharmProjects\footviz\footviz-vue\public\team_player_images')
team_visuals_dir = Path(r'C:\Users\jerry\PyCharmProjects\footviz\footviz-vue\public\team-visuals')

# 连接数据库
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# 获取数据库中的所有球队名称
cursor.execute("SELECT name FROM Teams")
db_teams = set(row[0] for row in cursor.fetchall())

# 获取静态资源中的球队名称
static_teams_images = set(os.listdir(team_player_images_dir))
static_teams_visuals = set(os.listdir(team_visuals_dir))

# 移除非目录项
static_teams_images = {item for item in static_teams_images if (team_player_images_dir / item).is_dir()}
static_teams_visuals = {item for item in static_teams_visuals if (team_visuals_dir / item).is_dir()}

print("数据库中的球队数量:", len(db_teams))
print("team_player_images目录中的球队数量:", len(static_teams_images))
print("team-visuals目录中的球队数量:", len(static_teams_visuals))

print("\n数据库中的球队:")
for team in sorted(db_teams):
    print(f"  {team}")

print("\nteam_player_images目录中的球队:")
for team in sorted(static_teams_images):
    print(f"  {team}")

print("\nteam-visuals目录中的球队:")
for team in sorted(static_teams_visuals):
    print(f"  {team}")

# 检查匹配情况
print("\n=== 匹配情况分析 ===")

# 数据库中但静态资源中没有的球队
missing_in_images = db_teams - static_teams_images
missing_in_visuals = db_teams - static_teams_visuals

print(f"\n数据库中有但team_player_images中没有的球队数量: {len(missing_in_images)}")
if missing_in_images:
    for team in sorted(missing_in_images):
        print(f"  {team}")

print(f"\n数据库中有但team-visuals中没有的球队数量: {len(missing_in_visuals)}")
if missing_in_visuals:
    for team in sorted(missing_in_visuals):
        print(f"  {team}")

# 静态资源中有但数据库中没有的球队
extra_in_images = static_teams_images - db_teams
extra_in_visuals = static_teams_visuals - db_teams

print(f"\nteam_player_images中有但数据库中没有的球队数量: {len(extra_in_images)}")
if extra_in_images:
    for team in sorted(extra_in_images):
        print(f"  {team}")

print(f"\nteam-visuals中有但数据库中没有的球队数量: {len(extra_in_visuals)}")
if extra_in_visuals:
    for team in sorted(extra_in_visuals):
        print(f"  {team}")

# 完全匹配的球队
perfect_match = db_teams & static_teams_images & static_teams_visuals
print(f"\n数据库、team_player_images和team-visuals完全匹配的球队数量: {len(perfect_match)}")
for team in sorted(perfect_match):
    print(f"  {team}")

conn.close()