import sqlite3
import os

DB_PATH = 'clicks.db'

# SQL statements for table creation
CREATE_PLAYLISTS = '''
CREATE TABLE IF NOT EXISTS Playlists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    created_date TEXT NOT NULL
);
'''

CREATE_CLICKS = '''
CREATE TABLE IF NOT EXISTS Clicks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    playlist_id INTEGER NOT NULL,
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,
    timestamp FLOAT NOT NULL,
    variable_name TEXT DEFAULT NULL,
    FOREIGN KEY (playlist_id) REFERENCES Playlists(id)
);
'''

CREATE_SCREENSHOTS = '''
CREATE TABLE IF NOT EXISTS Screenshots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    playlist_id INTEGER NOT NULL,
    path TEXT NOT NULL,
    is_manual BOOLEAN NOT NULL,
    FOREIGN KEY (playlist_id) REFERENCES Playlists(id)
);
'''

CREATE_KEYBOARDEVENTS = '''
CREATE TABLE IF NOT EXISTS KeyboardEvents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    playlist_id INTEGER NOT NULL,
    key TEXT NOT NULL,
    event_type TEXT NOT NULL,
    timestamp FLOAT NOT NULL,
    FOREIGN KEY (playlist_id) REFERENCES Playlists(id)
);
'''

CREATE_ACTIONTRIGGERS = '''
CREATE TABLE IF NOT EXISTS ActionTriggers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    playlist_id INTEGER NOT NULL,
    action_type TEXT NOT NULL,
    timestamp FLOAT NOT NULL,
    payload TEXT DEFAULT NULL,
    FOREIGN KEY (playlist_id) REFERENCES Playlists(id)
);
'''

def get_connection():
    """Get a connection to the SQLite database."""
    return sqlite3.connect(DB_PATH)

def init_db():
    """Initialize the database and create tables if they do not exist."""
    conn = get_connection()
    cur = conn.cursor()
    cur.execute(CREATE_PLAYLISTS)
    cur.execute(CREATE_CLICKS)
    cur.execute(CREATE_SCREENSHOTS)
    cur.execute(CREATE_KEYBOARDEVENTS)
    cur.execute(CREATE_ACTIONTRIGGERS)
    conn.commit()
    conn.close()

def clear_session_db():
    """Clear all session data from SQLite database on startup."""
    try:
        conn = get_connection()
        cur = conn.cursor()
        
        # Count existing records before clearing
        cur.execute("SELECT COUNT(*) FROM Playlists")
        playlist_count = cur.fetchone()[0]
        cur.execute("SELECT COUNT(*) FROM Clicks")
        click_count = cur.fetchone()[0]
        cur.execute("SELECT COUNT(*) FROM KeyboardEvents")
        key_count = cur.fetchone()[0]
        cur.execute("SELECT COUNT(*) FROM Screenshots")
        screenshot_count = cur.fetchone()[0]
        cur.execute("SELECT COUNT(*) FROM ActionTriggers")
        trigger_count = cur.fetchone()[0]
        
        if playlist_count > 0 or click_count > 0 or key_count > 0 or screenshot_count > 0 or trigger_count > 0:
            print(f"🗑️ Clearing SQLite session data: {playlist_count} playlists, {click_count} clicks, {key_count} keys, {screenshot_count} screenshots, {trigger_count} triggers")
        
        # Clear all data from session tables (in reverse order due to foreign keys)
        cur.execute("DELETE FROM Screenshots")
        cur.execute("DELETE FROM ActionTriggers")
        cur.execute("DELETE FROM KeyboardEvents") 
        cur.execute("DELETE FROM Clicks")
        cur.execute("DELETE FROM Playlists")
        
        # Reset auto-increment counters
        cur.execute("DELETE FROM sqlite_sequence WHERE name IN ('Playlists', 'Clicks', 'Screenshots', 'KeyboardEvents', 'ActionTriggers')")
        
        conn.commit()
        conn.close()
        
        if playlist_count > 0 or click_count > 0 or key_count > 0 or screenshot_count > 0 or trigger_count > 0:
            print("✅ SQLite session database cleared - ready for new session")
        else:
            print("📝 SQLite session database ready (was already empty)")
        
    except Exception as e:
        print(f"⚠️ Error clearing session database: {e}")
        # Don't fail startup if clearing fails
        pass

if __name__ == "__main__":
    # Initialize database when run directly
    init_db()
    # print("Database initialized.")