audit_db.py

import sqlite3
import os

db_path = "src-tauri/data/syncify.db"
if not os.path.exists(db_path):
    print(f"Error: {db_path} not found")
    exit(1)

conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# 1. Spotify Track ID count
cursor.execute("SELECT id FROM services WHERE name = 'spotify'")
spotify_service = cursor.fetchone()
if spotify_service:
    spotify_id = spotify_service[0]
    cursor.execute("SELECT COUNT(*) FROM track_sources WHERE service_id = ?", (spotify_id,))
    has_spotify = cursor.fetchone()[0]
    print(f"Tracks with Spotify Source: {has_spotify}")
else:
    print("Spotify service not found in DB")

# 2. Duplicate counts
cursor.execute("""
SELECT COUNT(*) as grupos_duplicados,
       SUM(cnt - 1) as tracks_extra
FROM (
  SELECT isrc, COUNT(*) as cnt
  FROM tracks
  WHERE isrc IS NOT NULL AND isrc != ''
  GROUP BY isrc
  HAVING COUNT(*) > 1
);
""")
res = cursor.fetchone()
print(f"Duplicates (ISRC): Groups={res[0]}, Extra Tracks={res[1]}")

# 3. Artist+Title Duplicates (for comparison)
cursor.execute("""
SELECT COUNT(*) as grupos_duplicados,
       SUM(cnt - 1) as tracks_extra
FROM (
  SELECT title, artist_id, COUNT(*) as cnt
  FROM tracks t
  JOIN track_artists ta ON t.id = ta.track_id AND ta.role = 'primary'
  GROUP BY title, artist_id
  HAVING COUNT(*) > 1
);
""")
res = cursor.fetchone()
print(f"Duplicates (Title+Artist): Groups={res[0]}, Extra Tracks={res[1]}")

conn.close()