# database.py
import sqlite3
import os
import tempfile
from datetime import datetime

# 🔁 Shared database path and upload folder
DB_PATH = os.path.join(tempfile.gettempdir(), "submissions.db")
UPLOAD_DIR = os.path.join(tempfile.gettempdir(), "uploads")
os.makedirs(UPLOAD_DIR, exist_ok=True)

def init_db():
    """Initialize the submissions table if it doesn't exist."""
    conn = sqlite3.connect(DB_PATH)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS submissions (
            category TEXT,
            name TEXT,
            title TEXT,
            description TEXT,
            input_type TEXT,
            audio TEXT,
            image TEXT,
            video TEXT,
            timestamp TEXT
        )
    """)
    conn.commit()
    conn.close()

def save_submission(category, name, title, description, input_type,
                    audio_path=None, image_path=None, video_path=None):
    """Save a user-submitted record to the database."""
    conn = sqlite3.connect(DB_PATH)
    conn.execute("""
        INSERT INTO submissions (
            category, name, title, description, input_type,
            audio, image, video, timestamp
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        category, name, title, description, input_type,
        audio_path, image_path, video_path,
        datetime.now().isoformat()
    ))
    conn.commit()
    conn.close()