backend/database.py

import sqlite3
import json
import os
from typing import List, Dict, Any, Union

DB_PATH = os.path.join(os.path.dirname(
    os.path.abspath(__file__)), "portfolio.sqlite")


def query_sqlite(table_name: str) -> Union[List[Dict[str, Any]], Dict[str, Any]]:
    """Fetches all rows from a SQLite table and formats them."""
    try:
        conn = sqlite3.connect(DB_PATH)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        cursor.execute(f"SELECT * FROM {table_name}")
        rows = [dict(row) for row in cursor.fetchall()]
        conn.close()

        if table_name == "skills":
            formatted_skills = {}
            for row in rows:
                try:
                    # Parse the JSON string back to a list
                    skills_array = json.loads(row['skill_list'])
                    formatted_skills[row['category']] = skills_array
                except:
                    formatted_skills[row['category']] = []
            return formatted_skills

        if table_name == "certifications":
            return [row['name'] for row in rows]

        return rows
    except Exception as e:
        print(f"Database Error: {e}")
        return []