import sqlite3
import hashlib
import os

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, '..', 'bharat_play.db')

def init_db():
    try:
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        
        # Create users table
        c.execute('''CREATE TABLE IF NOT EXISTS users
                    (id INTEGER PRIMARY KEY AUTOINCREMENT,
                     username TEXT UNIQUE NOT NULL,
                     password TEXT NOT NULL)''')
        
        # Create submissions table
        c.execute('''CREATE TABLE IF NOT EXISTS submissions
                    (id INTEGER PRIMARY KEY AUTOINCREMENT,
                     name TEXT NOT NULL,
                     title TEXT NOT NULL,
                     description TEXT,
                     category TEXT,
                     player_count TEXT,
                     age_group TEXT,
                     equipment_needed TEXT,
                     input_type TEXT,
                     audio_url TEXT,
                     image_url TEXT,
                     video_url TEXT,
                     lat REAL,
                     lon REAL,
                     timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)''')
        
        conn.commit()
        print("Database initialized successfully")
    except Exception as e:
        print(f"Error initializing database: {e}")
        raise e
    finally:
        if 'conn' in locals():
            conn.close()

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

def hash_password(password):
    return hashlib.sha256(password.encode()).hexdigest()

def login_user(username, password):
    conn = get_connection()
    cursor = conn.cursor()
    try:
        hashed_pw = hash_password(password)
        cursor.execute('SELECT * FROM users WHERE username=? AND password=?', 
                      (username, hashed_pw))
        result = cursor.fetchone()
        return bool(result)
    finally:
        conn.close()

def signup_user(username, password):
    if not username or not password:
        return False
        
    conn = get_connection()
    cursor = conn.cursor()
    try:
        hashed_pw = hash_password(password)
        cursor.execute(
            "INSERT INTO users (username, password) VALUES (?, ?)",
            (username, hashed_pw)
        )
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False
    except Exception as e:
        print(f"Error in signup: {e}")
        return False
    finally:
        conn.close()