SkinsPins/app/database.py

import os
import aiosqlite

DATABASE_PATH = os.getenv("DATABASE_PATH", "/data/skinspins.db")


async def get_db():
    async with aiosqlite.connect(DATABASE_PATH) as db:
        db.row_factory = aiosqlite.Row
        yield db


async def init_db():
    async with aiosqlite.connect(DATABASE_PATH) as db:
        await db.executescript("""
            CREATE TABLE IF NOT EXISTS users (
                id TEXT PRIMARY KEY,
                email TEXT UNIQUE NOT NULL,
                name TEXT,
                handicap REAL,
                avatar TEXT,
                is_onboarded INTEGER NOT NULL DEFAULT 0,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS invitations (
                id TEXT PRIMARY KEY,
                email TEXT UNIQUE NOT NULL,
                invited_by TEXT NOT NULL,
                created_at TEXT NOT NULL,
                FOREIGN KEY (invited_by) REFERENCES users(id)
            );

            CREATE TABLE IF NOT EXISTS magic_link_tokens (
                id TEXT PRIMARY KEY,
                email TEXT NOT NULL,
                token TEXT UNIQUE NOT NULL,
                expires_at TEXT NOT NULL,
                used_at TEXT
            );

            CREATE TABLE IF NOT EXISTS sessions (
                id TEXT PRIMARY KEY,
                user_id TEXT NOT NULL,
                token TEXT UNIQUE NOT NULL,
                expires_at TEXT NOT NULL,
                created_at TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(id)
            );

            CREATE TABLE IF NOT EXISTS courses (
                id TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                holes INTEGER NOT NULL DEFAULT 18,
                location TEXT,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS course_holes (
                id TEXT PRIMARY KEY,
                course_id TEXT NOT NULL,
                hole_number INTEGER NOT NULL,
                par INTEGER NOT NULL DEFAULT 4,
                UNIQUE (course_id, hole_number),
                FOREIGN KEY (course_id) REFERENCES courses(id)
            );

            CREATE TABLE IF NOT EXISTS course_tees (
                id TEXT PRIMARY KEY,
                course_id TEXT NOT NULL,
                name TEXT NOT NULL,
                slope INTEGER NOT NULL,
                rating REAL NOT NULL,
                FOREIGN KEY (course_id) REFERENCES courses(id)
            );

            CREATE TABLE IF NOT EXISTS games (
                id TEXT PRIMARY KEY,
                format TEXT NOT NULL DEFAULT 'bingo_bango_bongo',
                holes_count INTEGER NOT NULL,
                status TEXT NOT NULL DEFAULT 'lobby',
                created_by TEXT NOT NULL,
                created_at TEXT NOT NULL,
                course_id TEXT,
                FOREIGN KEY (created_by) REFERENCES users(id),
                FOREIGN KEY (course_id) REFERENCES courses(id)
            );

            CREATE TABLE IF NOT EXISTS game_players (
                id TEXT PRIMARY KEY,
                game_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                handicap_snapshot REAL,
                joined_at TEXT NOT NULL,
                UNIQUE (game_id, user_id),
                FOREIGN KEY (game_id) REFERENCES games(id),
                FOREIGN KEY (user_id) REFERENCES users(id)
            );

            CREATE TABLE IF NOT EXISTS hole_scores (
                id TEXT PRIMARY KEY,
                game_id TEXT NOT NULL,
                hole_number INTEGER NOT NULL,
                user_id TEXT NOT NULL,
                strokes INTEGER,
                bingo INTEGER NOT NULL DEFAULT 0,
                bango REAL NOT NULL DEFAULT 0.0,
                bongo INTEGER NOT NULL DEFAULT 0,
                updated_by TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                UNIQUE (game_id, hole_number, user_id),
                FOREIGN KEY (game_id) REFERENCES games(id),
                FOREIGN KEY (user_id) REFERENCES users(id),
                FOREIGN KEY (updated_by) REFERENCES users(id)
            );
        """)
        await db.commit()
        # Migrations for columns added after initial release
        for migration in [
            "ALTER TABLE games ADD COLUMN course_id TEXT REFERENCES courses(id)",
            "ALTER TABLE game_players ADD COLUMN tee_id TEXT REFERENCES course_tees(id)",
            "ALTER TABLE course_holes ADD COLUMN stroke_index INTEGER",
            "ALTER TABLE games ADD COLUMN carryover INTEGER NOT NULL DEFAULT 1",
            "ALTER TABLE game_players ADD COLUMN playing_handicap INTEGER",
            "ALTER TABLE game_players ADD COLUMN strokes_allocation TEXT",
            "ALTER TABLE magic_link_tokens ADD COLUMN code TEXT",
            "ALTER TABLE games ADD COLUMN skin_value REAL",
            "ALTER TABLE games ADD COLUMN skin_double_back_nine INTEGER NOT NULL DEFAULT 0",
            "ALTER TABLE games ADD COLUMN skins_net INTEGER NOT NULL DEFAULT 0",
            "ALTER TABLE games ADD COLUMN side_skins_enabled INTEGER NOT NULL DEFAULT 0",
            "ALTER TABLE games ADD COLUMN start_hole INTEGER NOT NULL DEFAULT 1",
            "ALTER TABLE course_tees ADD COLUMN gender TEXT",
            "ALTER TABLE course_tees ADD COLUMN stroke_indices TEXT",
            "ALTER TABLE course_tees ADD COLUMN front_slope INTEGER",
            "ALTER TABLE course_tees ADD COLUMN front_rating REAL",
            "ALTER TABLE course_tees ADD COLUMN back_slope INTEGER",
            "ALTER TABLE course_tees ADD COLUMN back_rating REAL",
        ]:
            try:
                await db.execute(migration)
                await db.commit()
            except Exception as e:
                if "duplicate column name" not in str(e).lower():
                    raise