import sqlite3
import json
import os

DATABASE_FILE_NAME = "song_check_data.db"
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DATABASE_FILE_URL = os.path.join(BASE_DIR, DATABASE_FILE_NAME)

def insertData(entry):
    conn = sqlite3.connect(DATABASE_FILE_URL)
    # Create a cursor object to execute SQL commands
    cursor = conn.cursor()

    cursor.execute('''CREATE TABLE IF NOT EXISTS songs (
        all_ok TEXT,
        created_by TEXT,
        song_id TEXT,
        song_title TEXT,
        arrangement_name TEXT,
        created_at TEXT,
        arrangement_id TEXT PRIMARY KEY,
        song_url TEXT,
        verse_start TEXT,
        uniqe_verse_elements TEXT,
        line_length TEXT,
        has_lyrics TEXT,
        verse_result TEXT,
        sequence_ok TEXT,
        lyrics_enabled TEXT
    )''')
    # Iterate over the JSON data and insert it into the table

    cursor.execute('''INSERT OR REPLACE INTO songs (
        all_ok, created_by, song_id, song_title, arrangement_name, created_at,
        arrangement_id, song_url, verse_start, uniqe_verse_elements, line_length,
        has_lyrics, verse_result, sequence_ok, lyrics_enabled
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''', (
        entry["all_ok"], entry["created_by"], entry["song_id"],
        entry["song_title"], entry["arrangement_name"], entry["created_at"],
        entry["arrangement_id"], entry["song_url"], entry["verse_start"],
        entry["uniqe_verse_elements"], entry["line_length"],
        entry["has_lyrics"], json.dumps(entry["verse_result"]), entry["sequence_ok"], entry["lyrics_enabled"]
    ))

    # Commit changes and close the database connection
    conn.commit()
    conn.close()


def return_all_data():
    # Connect to the SQLite database
    conn = sqlite3.connect(DATABASE_FILE_URL)  # Replace with your database name

    # Create a cursor object to execute SQL queries
    cursor = conn.cursor()

    # Execute a SELECT query to retrieve data from the 'songs' table
    cursor.execute("SELECT * FROM songs")

    # Fetch all the rows from the query result
    rows = cursor.fetchall()

    # Create a list to store the data in JSON format
    data_list = []

    # Loop through the rows and convert each row to a dictionary
    for row in rows:
        song_data = {
            "all_ok": row[0],
            "created_by": row[1],
            "song_id": row[2],
            "song_title": row[3],
            "arrangement_name": row[4],
            "created_at": row[5],
            "arrangement_id": row[6],
            "song_url": row[7],
            "verse_start": row[8],
            "unique_verse_elements": row[9],
            "line_length": row[10],
            "has_lyrics": row[11],
            "verse_result": row[12],
            "sequence_ok": row[13],
            "lyrics_enabled": row[14]

        }
        data_list.append(song_data)

    # Convert the list of dictionaries to JSON format
    json_data = json.dumps(data_list, indent=4)

    # Close the database connection
    conn.close()

    # Print or return the JSON data
    return json_data  # You can also return it or write it to a file if needed

def return_arrangments_which_are_ok():
    conn = sqlite3.connect(DATABASE_FILE_URL) 
    cursor = conn.cursor()
    cursor.execute("SELECT song_id, arrangement_id, song_title FROM songs WHERE all_ok = 'True';")
    rows = cursor.fetchall()
    data_list = []
    for row in rows:
        song_data = {
            "song_id": row[0],
            "arrangement_id": row[1],
            "song_title": row[2]
        }
        data_list.append(song_data)
    json_data = json.dumps(data_list, indent=4)
    conn.close()

    return json_data