storage.py

import sqlite3
import json
from typing import List, Dict, Any, Optional

DB_PATH = "games.db"

def get_connection(db_path: str = DB_PATH) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    return conn

def init_db(db_path: str = DB_PATH) -> None:
    conn = get_connection(db_path)
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS games (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL,
            description TEXT NOT NULL,
            purchase_link TEXT NOT NULL,
            media_links TEXT NOT NULL,
            tags TEXT NOT NULL
        )
    """)
    conn.commit()
    conn.close()

def _row_to_dict(row: sqlite3.Row) -> Dict[str, Any]:
    return {
        "id": row["id"],
        "name": row["name"],
        "description": row["description"],
        "purchase_link": row["purchase_link"],
        "media_links": json.loads(row["media_links"]),
        "tags": json.loads(row["tags"])
    }

def add_game(name: str, description: str, purchase_link: str, media_links: List[str], tags: List[str], db_path: str = DB_PATH) -> Optional[int]:
    conn = get_connection(db_path)
    cursor = conn.cursor()
    try:
        cursor.execute(
            "INSERT INTO games (name, description, purchase_link, media_links, tags) VALUES (?, ?, ?, ?, ?)",
            (name.strip(), description.strip(), purchase_link.strip(), json.dumps(media_links), json.dumps(tags))
        )
        conn.commit()
        game_id = cursor.lastrowid
        return game_id
    except sqlite3.IntegrityError:
        return None
    finally:
        conn.close()

def get_game_by_id(game_id: int, db_path: str = DB_PATH) -> Optional[Dict[str, Any]]:
    conn = get_connection(db_path)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM games WHERE id = ?", (game_id,))
    row = cursor.fetchone()
    conn.close()
    return _row_to_dict(row) if row else None

def get_game_by_name(name: str, db_path: str = DB_PATH) -> Optional[Dict[str, Any]]:
    conn = get_connection(db_path)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM games WHERE LOWER(name) = LOWER(?)", (name.strip(),))
    row = cursor.fetchone()
    conn.close()
    return _row_to_dict(row) if row else None

def get_all_games(db_path: str = DB_PATH) -> List[Dict[str, Any]]:
    conn = get_connection(db_path)
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM games ORDER BY name ASC")
    rows = cursor.fetchall()
    conn.close()
    return [_row_to_dict(row) for row in rows]

def update_game(game_id: int, name: str, description: str, purchase_link: str, media_links: List[str], tags: List[str], db_path: str = DB_PATH) -> bool:
    conn = get_connection(db_path)
    cursor = conn.cursor()
    try:
        cursor.execute(
            "UPDATE games SET name = ?, description = ?, purchase_link = ?, media_links = ?, tags = ? WHERE id = ?",
            (name.strip(), description.strip(), purchase_link.strip(), json.dumps(media_links), json.dumps(tags), game_id)
        )
        conn.commit()
        return cursor.rowcount > 0
    except sqlite3.IntegrityError:
        return False
    finally:
        conn.close()

def remove_game(game_id: int, db_path: str = DB_PATH) -> bool:
    conn = get_connection(db_path)
    cursor = conn.cursor()
    cursor.execute("DELETE FROM games WHERE id = ?", (game_id,))
    conn.commit()
    success = cursor.rowcount > 0
    conn.close()
    return success

def get_all_tags(db_path: str = DB_PATH) -> List[str]:
    games = get_all_games(db_path)
    tag_set = set()
    for game in games:
        for tag in game["tags"]:
            if tag and tag.strip():
                tag_set.add(tag.strip().lower())
    return sorted(list(tag_set))

def add_tag_to_game(game_id: int, tag: str, db_path: str = DB_PATH) -> bool:
    game = get_game_by_id(game_id, db_path)
    if not game:
        return False
    clean_tag = tag.strip().lower()
    if clean_tag not in game["tags"]:
        game["tags"].append(clean_tag)
        return update_game(game["id"], game["name"], game["description"], game["purchase_link"], game["media_links"], game["tags"], db_path)
    return True

def remove_tag_from_game(game_id: int, tag: str, db_path: str = DB_PATH) -> bool:
    game = get_game_by_id(game_id, db_path)
    if not game:
        return False
    clean_tag = tag.strip().lower()
    if clean_tag in game["tags"]:
        game["tags"] = [t for t in game["tags"] if t != clean_tag]
        return update_game(game["id"], game["name"], game["description"], game["purchase_link"], game["media_links"], game["tags"], db_path)
    return True

def filter_games_by_tags(tags: List[str], db_path: str = DB_PATH) -> List[Dict[str, Any]]:
    games = get_all_games(db_path)
    if not tags:
        return games
    clean_tags = set(t.strip().lower() for t in tags if t.strip())
    if not clean_tags:
        return games
    return [g for g in games if any(t in clean_tags for t in g["tags"])]

def parse_list_input(text: str) -> List[str]:
    if not text:
        return []
    items = []
    for line in text.replace("\r", "\n").split("\n"):
        for part in line.split(","):
            for subpart in part.split():
                cleaned = subpart.strip()
                if cleaned and cleaned not in items:
                    items.append(cleaned)
    return items

def parse_tags_input(text: str) -> List[str]:
    if not text:
        return []
    items = []
    for line in text.replace("\r", "\n").split("\n"):
        for part in line.split(","):
            cleaned = part.strip().lower()
            if cleaned and cleaned not in items:
                items.append(cleaned)
    return items