import sqlite3
import os
from datetime import datetime

DB_PATH = os.path.join(os.path.dirname(__file__), "..", "bharat_play.db")

def get_db_connection():
    return sqlite3.connect(DB_PATH)

def save_submission(submission_data):
    conn = get_db_connection()
    try:
        c = conn.cursor()
        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"❌ Submission save error: {e}")
        return False
    finally:
        conn.close()

def get_user_submissions(username):
    conn = get_db_connection()
    try:
        c = conn.cursor()
        c.execute('''SELECT * FROM submissions WHERE name = ? ORDER BY timestamp DESC''', (username,))
        columns = [desc[0] for desc in c.description]
        return [dict(zip(columns, row)) for row in c.fetchall()]
    except Exception as e:
        print(f"❌ Error fetching user submissions: {e}")
        return []
    finally:
        conn.close()

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