import json
import sqlite3
from pathlib import Path

def load_questions(json_path, db_path):
    # Connect to SQLite database
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    
    # Create questions table if not exists
    c.execute('''CREATE TABLE IF NOT EXISTS questions
                 (id INTEGER PRIMARY KEY,
                  text TEXT,
                  option1 TEXT,
                  option2 TEXT,
                  option3 TEXT,
                  option4 TEXT,
                  correct_index INTEGER,
                  requirement TEXT,
                  explanation TEXT)''')
    
    # Load JSON data
    with open(json_path) as f:
        questions = json.load(f)
    
    # Insert questions
    for q in questions:
        # Handle null options and different JSON structure
        options = [str(o).strip() if o else "" for o in q['options']]
        while len(options) < 4:  # Ensure we always have 4 options
            options.append("")
            
        c.execute('''INSERT INTO questions 
                     (id, text, option1, option2, option3, option4, correct_index, requirement, explanation)
                     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)''',
                  (q['id'], 
                   q['question'], 
                   options[0], 
                   options[1], 
                   options[2], 
                   options[3],
                   q['correct_index'], 
                   q.get('requirement', ''), 
                   q.get('explanation', '')))
    
    conn.commit()
    conn.close()
    print(f"Loaded {len(questions)} questions into database")

if __name__ == "__main__":
    project_root = Path(__file__).parent.parent
    json_path = project_root / "quiz.json"
    db_path = project_root / "demoon.db"
    
    load_questions(json_path, db_path)