import sqlite3
import os
from mutagen.mp3 import MP3
from mutagen.id3 import ID3

# Connect to SQLite database (or create one)
conn = sqlite3.connect("music_metadata4.db")
cursor = conn.cursor()

# Create a table to store metadata
cursor.execute("""
CREATE TABLE IF NOT EXISTS music (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT,
    artist TEXT,
    album TEXT,
    year TEXT,
    genre TEXT,
    track_number TEXT,
    duration REAL
)
""")

def extract_and_store(mp3_file, artist_name, album_name):
    """ Extract metadata from an MP3 file and store it in SQLite """
    try:
        audio = MP3(mp3_file, ID3=ID3)
        tags = audio.tags

        title = tags.get("TIT2", ["Unknown"])[0] if "TIT2" in tags else "Unknown"
        year = tags.get("TYER", ["Unknown"])[0] if "TYER" in tags else "Unknown"
        genre = tags.get("TCON", ["Unknown"])[0] if "TCON" in tags else "Unknown"
        track_number = tags.get("TRCK", ["Unknown"])[0] if "TRCK" in tags else "Unknown"
        duration = round(audio.info.length, 2)  # Track duration in seconds

        cursor.execute("""
        INSERT INTO music (title, artist, album, year, genre, track_number, duration)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        """, (title, artist_name, album_name, year, genre, track_number, duration))
        conn.commit()

        print(f"Processed: {mp3_file} (Artist: {artist_name}, Album: {album_name})")

    except Exception as e:
        print(f"Error processing {mp3_file}: {e}")

def process_directory(directory):
    """ Process all MP3 files in /Artist/Album/Track.mp3 structure """
    for artist_name in os.listdir(directory):
        artist_path = os.path.join(directory, artist_name)
        if os.path.isdir(artist_path):  # Ensure it's an artist folder
            for album_name in os.listdir(artist_path):
                album_path = os.path.join(artist_path, album_name)
                if os.path.isdir(album_path):  # Ensure it's an album folder
                    for filename in os.listdir(album_path):
                        if filename.endswith(".mp3"):
                            print("Working on " + str(album_name) + " - " + str(filename), end="\r")
                            extract_and_store(os.path.join(album_path, filename), artist_name, album_name)

# Example usage: Update this with your actual music folder path
music_folder = "M:/"
process_directory(music_folder)

# Close connection
conn.close()