import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('gacha_game.db')
cursor = conn.cursor()

# Create tables
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    has_rolled BOOLEAN NOT NULL DEFAULT 0
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS characters (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    rarity INTEGER NOT NULL,
    weight REAL NOT NULL,
    file_id TEXT NOT NULL
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS pulls (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    character_id INTEGER,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (character_id) REFERENCES characters(id)
)
''')

cursor.execute("""
        CREATE TABLE IF NOT EXISTS config (
            key TEXT PRIMARY KEY,
            value TEXT
        )
    """)

""" # Insert example characters into the database if they don't already exist
characters = [
    ('Murakami-san', 1, 0.35),
    ('Mastodon-kun', 2, 0.25),
    ('Pleroma-tan', 3, 0.2),
    ('Misskey-tan', 4, 0.15),
    ('Syuilo-mama', 5, 0.05)
]


cursor.executemany('''
INSERT OR IGNORE INTO characters (name, rarity, weight) VALUES (?, ?, ?)
''', characters)
"""

# Commit changes and close
conn.commit()
conn.close()