import sqlite3
from datetime import datetime

def get_db_connection():
    return sqlite3.connect('bharat_play.db')

def save_submission(submission_data):
    conn = get_db_connection()
    c = conn.cursor()
    try:
        c.execute('''
            INSERT INTO submissions 
            (name, title, description, category, player_count, age_group, 
             equipment_needed, input_type, audio_url, image_url, video_url, 
             lat, lon, timestamp)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (
            submission_data['name'],
            submission_data['title'],
            submission_data['description'],
            submission_data['category'],
            submission_data['player_count'],
            submission_data['age_group'],
            submission_data['equipment_needed'],
            submission_data['input_type'],
            submission_data['audio_url'],
            submission_data['image_url'],
            submission_data['video_url'],
            submission_data['lat'],
            submission_data['lon'],
            datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        ))
        conn.commit()
        return True
    except Exception as e:
        print(f"Error saving submission: {e}")
        return False
    finally:
        conn.close()

def get_user_submissions(username):
    conn = get_db_connection()
    c = conn.cursor()
    try:
        # First check if the table exists
        c.execute('''SELECT name FROM sqlite_master 
                    WHERE type='table' AND name='submissions';''')
        if not c.fetchone():
            print("Submissions table does not exist")
            return []
            
        # If table exists, get the submissions
        c.execute('''SELECT * FROM submissions 
                    WHERE name = ? 
                    ORDER BY timestamp DESC''', (username,))
        columns = [description[0] for description in c.description]
        results = []
        for row in c.fetchall():
            results.append(dict(zip(columns, row)))
        return results
    except Exception as e:
        print(f"Error getting submissions: {e}")
        return []
    finally:
        conn.close()

def get_all_submissions():
    conn = get_db_connection()
    c = conn.cursor()
    try:
        c.execute('SELECT * FROM submissions ORDER BY timestamp DESC')
        columns = [description[0] for description in c.description]
        results = []
        for row in c.fetchall():
            results.append(dict(zip(columns, row)))
        return results
    finally:
        conn.close()