Database per collezionisti gratuito Parte 3

Prossimo passo: progettare il database. Prima di creare le tabelle voglio che decidiamo bene come gestire la collezione. Una proposta di base è:

Catalogo Ufficiale (contiene tutte le carte esistenti)
- sets
- cards

Collezione Paolo (Marco.. Giada etc..) contiene solo quelle possedute
- collection

Carta: Charizard ex 125/197
- Quantità: 3
- Lingua: ITA
- Condizione: Near Mint

e una seconda riga (per le carte aggiuntive con lingua diversa)
- Quantità: 1
- Lingua: ENG
- Condizione: Mint

Il DB sarà anche multiutente

Creazione schema database V1

Entriamo in PostgreSQL:
docker exec -it pokemon-postgres psql -U pokemon -d pokemon

Creiamo la Tabella utenti digitando:
CREATE TABLE users (
    id              BIGSERIAL PRIMARY KEY,
    username        VARCHAR(50) UNIQUE NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    display_name    VARCHAR(100),
    is_admin        BOOLEAN DEFAULT FALSE,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Per la Tabella Espansioni digitare:
CREATE TABLE sets (
    id              VARCHAR(50) PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    series          VARCHAR(255),
    release_date    DATE,
    total_cards     INTEGER,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Per la Tabella Carte digitare:
CREATE TABLE cards (
    id              VARCHAR(100) PRIMARY KEY,
    set_id          VARCHAR(50) NOT NULL,

    card_number     VARCHAR(20),
    name            VARCHAR(255) NOT NULL,

    rarity          VARCHAR(100),
    supertype       VARCHAR(100),

    image_small     TEXT,
    image_large     TEXT,

    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_cards_set
      FOREIGN KEY (set_id)
      REFERENCES sets(id)
);

Per la Tabella Collezioni digitare:
CREATE TABLE collection (
    id              BIGSERIAL PRIMARY KEY,

    user_id         BIGINT NOT NULL,
    card_id         VARCHAR(100) NOT NULL,

    quantity        INTEGER NOT NULL DEFAULT 1,

    language        VARCHAR(10) NOT NULL,
    condition       VARCHAR(30) NOT NULL,

    variant         VARCHAR(30) NOT NULL,

    graded          BOOLEAN DEFAULT FALSE,

    notes           TEXT,

    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_collection_user
      FOREIGN KEY (user_id)
      REFERENCES users(id),

    CONSTRAINT fk_collection_card
      FOREIGN KEY (card_id)
      REFERENCES cards(id)
);

Per la Tabella Wishlist digitare:
CREATE TABLE wishlist (
    id              BIGSERIAL PRIMARY KEY,

    user_id         BIGINT NOT NULL,
    card_id         VARCHAR(100) NOT NULL,

    priority        SMALLINT DEFAULT 1,
    notes           TEXT,

    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_wishlist_user
      FOREIGN KEY (user_id)
      REFERENCES users(id),

    CONSTRAINT fk_wishlist_card
      FOREIGN KEY (card_id)
      REFERENCES cards(id)
);

Per la Tabella Indici digitare:
CREATE INDEX idx_cards_name
ON cards(name);

CREATE INDEX idx_cards_set
ON cards(set_id);

CREATE INDEX idx_collection_user
ON collection(user_id);

CREATE INDEX idx_collection_card
ON collection(card_id);

Primo utente amministratore:
INSERT INTO users (
    username,
    email,
    password_hash,
    display_name,
    is_admin
)
VALUES (
    'paolo',
    'paolo@local',
    'TEMP',
    'Paolo',
    TRUE
);

Verifica finale:
\dt

Dovresti vedere:
cards
collection
sets
users
wishlist

Per verifica l'utente:
SELECT * FROM users;

Prossimo passo: Backend FastAPI

/opt/pokemon

├── backend
├── frontend
├── postgres
├── imports
└── docker-compose.yml

Creiamo il backend:
cd /opt/pokemon/backend
mkdir app
cd app
touch main.py

Verifica: (se non hai tree: apt install tree -y)
tree /opt/pokemon/backend

Dovresti vedere:
backend
└── app
    └── main.py

Esegui:
mkdir -p /opt/pokemon/backend/app
touch /opt/pokemon/backend/app/main.py

Adesso creiamo il primo backend minimale e lo facciamo girare in Docker. Passo 1: file Python

nano /opt/pokemon/backend/app/main.py

e inserisci:
from fastapi import FastAPI

app = FastAPI(
    title="Pokemon Collection API"
)


@app.get("/")
def root():
    return {
        "app": "Pokemon Collection",
        "status": "running"
    }


@app.get("/health")
def health():
    return {
        "status": "ok"
    }

Salva ed esci con CTRL X e poi Y e invio

nano /opt/pokemon/backend/requirements.txt

e inserisci:
fastapi
uvicorn[standard]

Salva ed esci con CTRL X e poi Y e invio

nano /opt/pokemon/backend/Dockerfile

e inserisci:
FROM python:3.13-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY app ./app
CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "8000"]

Salva ed esci con CTRL X e poi Y e invio

nano /opt/pokemon/docker-compose.yml

e sostituisci il contenuto con:
services:

  postgres:
    image: postgres:17
    container_name: pokemon-postgres
    restart: unless-stopped

    environment:
      POSTGRES_DB: pokemon
      POSTGRES_USER: pokemon
      POSTGRES_PASSWORD: pokemon123

    ports:
      - "5432:5432"

    volumes:
      - ./postgres:/var/lib/postgresql/data

  api:
    build: ./backend
    container_name: pokemon-api
    restart: unless-stopped

    ports:
      - "8000:8000"

    depends_on:
      - postgres

Salva ed esci con CTRL X e poi Y e invio

Build e avvio

cd /opt/pokemon
docker-compose up -d --build

partirà la compilazione, se è andato tutto bene vedrai:
Container pokemon-postgres  Running                                                                                                          Container pokemon-api       Started

Per ulteriore verifixa comando:
curl http://localhost:8000/health
la risposta dovrebbe essere {"status":"ok"}

Se tutto funziona sarà già visibile l'accesso web al db dall'indirizzo http://ip vm:8000/docs dove ip vm è l'indirizzo ip della tua virtual machine o PC, dovrebbe comparire la pagina "Pokemon Collection API"

Creiamo il file database.py

nano /opt/pokemon/backend/app/database.py

con dentro:
from sqlalchemy import create_engine

DATABASE_URL = "postgresql://pokemon:pokemon123@postgres:5432/pokemon"

engine = create_engine(DATABASE_URL)

Salva ed esci con CTRL X e poi Y e invio

Creiamo il file requirements.txt

nano /opt/pokemon/backend/requirements.txt

Sostituisci il contenuto con:
fastapi
uvicorn[standard]
sqlalchemy
psycopg2-binary

Ricostruisci il container

cd /opt/pokemon
docker-compose down
docker-compose build --no-cache
docker-compose up -d

Verifichiamo aprendo la pagina web http://vm ip:8000/db dovrebbe comparire {"users":1}

Creiamo le API reali

nano /opt/pokemon/backend/app/main.py

Aggiungi in calce:
@app.get("/users")
def get_users():

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    id,
                    username,
                    display_name,
                    is_admin
                FROM users
                ORDER BY id
            """)
        )

        rows = result.mappings().all()

    return rows


@app.get("/sets")
def get_sets():

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT *
                FROM sets
                ORDER BY name
            """)
        )

        rows = result.mappings().all()

    return rows


@app.get("/cards")
def get_cards():

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT *
                FROM cards
                ORDER BY name
                LIMIT 100
            """)
        )

        rows = result.mappings().all()

    return rows

Salva ed esci con CTRL X e poi Y e invio
Ricostruzione solita con:
cd /opt/pokemon
docker-compose up -d --build

finita la ricompilazione dovrebbe dare nuovamente "Runnig" e "Started" Verifichiamo aprendo la pagina web http://vm ip:8000/db dovrebbe comparire:
[
  {
    "id": 1,
    "username": "paolo",
    "display_name": "Paolo",
    "is_admin": true
  }
]

Preparare l’importer

nano /opt/pokemon/backend/requirements.txt

che contiene già 4 righe, aggiungiamo in fondo:
requests

Salva ed esci e ricostruiamo nuovamente l'indice:
cd /opt/pokemon
docker-compose build --no-cache api
docker-compose up -d

Creare lo script di importazione

nano /opt/pokemon/imports/import_sets.py

Inserire:
import requests
import psycopg2

DB_HOST = "127.0.0.1"
DB_NAME = "pokemon"
DB_USER = "pokemon"
DB_PASS = "pokemon123"

url = "https://api.pokemontcg.io/v2/sets"

response = requests.get(url)
data = response.json()["data"]

conn = psycopg2.connect(
    host=DB_HOST,
    dbname=DB_NAME,
    user=DB_USER,
    password=DB_PASS
)

cur = conn.cursor()

for s in data:

    cur.execute("""
        INSERT INTO sets (
            id,
            name,
            series,
            release_date,
            total_cards
        )
        VALUES (%s,%s,%s,%s,%s)
        ON CONFLICT (id)
        DO NOTHING
    """, (
        s["id"],
        s["name"],
        s.get("series"),
        s.get("releaseDate"),
        s.get("total")
    ))

conn.commit()

print(f"Importati {len(data)} set")

cur.close()
conn.close()

Salva ed esci
Inizia adesso la fase di importazione dati vera..
apt install -y python3-requests python3-psycopg2
python3 /opt/pokemon/imports/import_sets.py

Mi aspetto una risposta del tipo:
Importati 174 set

Entra in PostgreSQL per una verifica rapida:
docker exec -it pokemon-postgres psql -U pokemon -d pokemon

al prompt pokemon=# digitare:
SELECT COUNT(*) FROM sets;

dovrebbe rispondere nuovamente 174 per uscire \q

nano /opt/pokemon/imports/import_cards.py

e inseriamo:
import requests
import psycopg2
import time

DB_HOST = "127.0.0.1"
DB_NAME = "pokemon"
DB_USER = "pokemon"
DB_PASS = "pokemon123"

conn = psycopg2.connect(
    host=DB_HOST,
    dbname=DB_NAME,
    user=DB_USER,
    password=DB_PASS
)

cur = conn.cursor()

page = 1
page_size = 100
total_imported = 0

while True:

    print(f"\nPagina {page}")

    while True:

        try:

            response = requests.get(
                "https://api.pokemontcg.io/v2/cards",
                params={
                    "page": page,
                    "pageSize": page_size
                },
                timeout=60
            )

            if response.status_code != 200:

                print(
                    f"Errore HTTP {response.status_code} sulla pagina {page}"
                )

                time.sleep(10)

                continue

            data = response.json()

            break

        except Exception as e:

            print(f"Errore: {e}")

            time.sleep(10)

    cards = data["data"]

    if not cards:

        print("\nImport completato")
        break

    for card in cards:

        cur.execute("""
            INSERT INTO cards (
                id,
                set_id,
                card_number,
                name,
                rarity,
                supertype,
                image_small,
                image_large
            )
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s)
            ON CONFLICT (id)
            DO NOTHING
        """, (
            card["id"],
            card["set"]["id"],
            card.get("number"),
            card.get("name"),
            card.get("rarity"),
            card.get("supertype"),
            card.get("images", {}).get("small"),
            card.get("images", {}).get("large")
        ))

    conn.commit()

    total_imported += len(cards)

    print(f"Importate finora: {total_imported}")

    page += 1

    time.sleep(1)

print(f"\nTotale carte elaborate: {total_imported}")

cur.close()
conn.close()

Salva ed esci e lancia lo script (ho inserito un controllo di errore che non interrompe la copia, su ogni pagina da 100 carte riproverà finchè non riesce).
python3 /opt/pokemon/imports/import_cards.py

L'importazione durerà diverse ore, quando sarà conclusa verifichiamo entrando nel DB:
docker exec -it pokemon-postgres psql -U pokemon -d pokemon
SELECT COUNT(*) FROM cards;

Mi attendo una risposta del tipo:
 count
-------
 20479
(1 row)

Usciamo dal DB con \q

Adesso facciamo la prima funzione utile

nano /opt/pokemon/backend/app/main.py

Alla fine del file aggiungere in calce:
@app.get("/cards/search")
def search_cards(name: str):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    c.id,
                    c.name,
                    c.card_number,
                    c.rarity,
                    s.name AS set_name,
                    c.image_small
                FROM cards c
                JOIN sets s
                    ON s.id = c.set_id
                WHERE LOWER(c.name) LIKE LOWER(:search)
                ORDER BY c.name
                LIMIT 50
            """),
            {"search": f"%{name}%"}
        )

        return result.mappings().all()

Ricostruire il DB:
cd /opt/pokemon
docker-compose up -d --build

finita la ricompilazione dovrebbe dare nuovamente "Runnig" e "Started" Verifichiamo aprendo la pagina web http://vm ip:8000/cards/search?name=pikachu dovrebbe mostrare tutte le carte trovate con risultati del tipo:
[{"id":"smp-SM109","name":"Ash's Pikachu","card_number":"SM109","rarity":"Promo","set_name":"SM Black Star Promos","image_small":"https://images.pokemontcg.io/smp/SM109.png"}, .....

Adesso creiamo la prima API che modifica il database

nano /opt/pokemon/backend/app/main.py

cancellate tutto e sostituite con:
from fastapi import FastAPI
from pydantic import BaseModel
from sqlalchemy import text

from app.database import engine

app = FastAPI(
    title="Pokemon Collection API"
)

class CollectionAdd(BaseModel):
    user_id: int
    card_id: str
    language: str
    condition: str
    variant: str
    graded: bool = False
    quantity: int = 1

@app.get("/")
def root():
    return {
        "app": "Pokemon Collection",
        "status": "running"
    }


@app.get("/health")
def health():
    return {
        "status": "ok"
    }


@app.get("/db")
def db_test():

    with engine.connect() as conn:

        result = conn.execute(
            text("SELECT COUNT(*) FROM users")
        )

        count = result.scalar()

    return {
        "users": count
    }

@app.get("/users")
def get_users():

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    id,
                    username,
                    display_name,
                    is_admin
                FROM users
                ORDER BY id
            """)
        )

        rows = result.mappings().all()

    return rows


@app.get("/sets")
def get_sets():

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT *
                FROM sets
                ORDER BY name
            """)
        )

        rows = result.mappings().all()

    return rows

@app.get("/cards")
def get_cards():

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT *
                FROM cards
                ORDER BY name
                LIMIT 100
            """)
        )

        rows = result.mappings().all()

    return rows

@app.get("/cards/search")
def search_cards(name: str):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    c.id,
                    c.name,
                    c.card_number,
                    c.rarity,
                    s.name AS set_name,
                    c.image_small
                FROM cards c
                JOIN sets s
                    ON s.id = c.set_id
                WHERE LOWER(c.name) LIKE LOWER(:search)
                ORDER BY c.name
                LIMIT 50
            """),
            {"search": f"%{name}%"}
        )

        return result.mappings().all()

@app.post("/collection/add")
def add_to_collection(item: CollectionAdd):

    with engine.connect() as conn:

        conn.execute(
            text("""
                INSERT INTO collection (
                    user_id,
                    card_id,
                    quantity,
                    language,
                    condition,
                    variant,
                    graded
                )
                VALUES (
                    :user_id,
                    :card_id,
                    :quantity,
                    :language,
                    :condition,
                    :variant,
                    :graded
                )
                ON CONFLICT (
                user_id,
                card_id,
                language,
                condition,
                variant,
                graded
                )
                DO UPDATE
                SET quantity = collection.quantity + EXCLUDED.quantity
            """),
            {
                "user_id": item.user_id,
                "card_id": item.card_id,
                "quantity": item.quantity,
                "language": item.language,
                "condition": item.condition,
                "variant": item.variant,
                "graded": item.graded
            }
        )

        conn.commit()

    return {
        "success": True,
        "card_id": item.card_id
    }

Salvare e uscire e ricostruire il DB:
cd /opt/pokemon
docker-compose up -d --build

finita la ricompilazione dovrebbe dare nuovamente "Runnig" e "Started" Verifichiamo aprendo la pagina web http://vm ip:8000/docs dovrebbe mostrare una pagina come la seguente

Creiamo la Collection basata sul multiutente

nano /opt/pokemon/backend/app/main.py

Aggiungere alla fine del file:
@app.get("/collection")
def get_collection(user_id: int):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    col.id,
                    c.name,
                    c.card_number,
                    s.name AS set_name,
                    col.quantity,
                    col.language,
                    col.condition,
                    col.variant,
                    col.graded
                FROM collection col
                JOIN cards c
                    ON c.id = col.card_id
                JOIN sets s
                    ON s.id = c.set_id
                WHERE col.user_id = :user_id
                ORDER BY c.name
            """),
            {"user_id": user_id}
        )

        return result.mappings().all()

Salvare e uscire e ricostruire il DB:
cd /opt/pokemon
docker-compose up -d --build

Aggiungere l’endpoint DELETE

nano /opt/pokemon/backend/app/main.py

Aggiungere alla fine del file:
@app.delete("/collection/{item_id}")
def delete_collection_item(item_id: int):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                DELETE FROM collection
                WHERE id = :item_id
            """),
            {"item_id": item_id}
        )

        conn.commit()

    return {
        "success": True,
        "deleted_id": item_id
    }

Salvare e uscire e ricostruire il DB:
cd /opt/pokemon
docker-compose up -d --build

Aggiungi il modello Pydantic

nano /opt/pokemon/backend/app/main.py

Aggiungere al principio del file:
from fastapi.responses import FileResponse

Sotto class CollectionAdd(BaseModel): aggiungere:
class CollectionUpdate(BaseModel):
    quantity: int
    language: str
    condition: str
    variant: str
    graded: bool

Aggiungere alla fine del file:
@app.put("/collection/{item_id}")
def update_collection_item(
    item_id: int,
    item: CollectionUpdate
):

    with engine.connect() as conn:

        conn.execute(
            text("""
                UPDATE collection
                SET
                    quantity = :quantity,
                    language = :language,
                    condition = :condition,
                    variant = :variant,
                    graded = :graded
                WHERE id = :item_id
            """),
            {
                "item_id": item_id,
                "quantity": item.quantity,
                "language": item.language,
                "condition": item.condition,
                "variant": item.variant,
                "graded": item.graded
            }
        )

        conn.commit()

    return {
        "success": True,
        "updated_id": item_id
    }

@app.get("/stats")
def get_stats(user_id: int):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    COALESCE(SUM(quantity), 0) AS total_cards,
                    COUNT(*) AS unique_cards,
                    COUNT(DISTINCT c.set_id) AS sets_started
                FROM collection col
                JOIN cards c
                    ON c.id = col.card_id
                WHERE col.user_id = :user_id
            """),
            {"user_id": user_id}
        )

        stats = result.mappings().first()

        return stats

@app.get("/set/{set_id}/cards")
def get_set_cards(set_id: str):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    id,
                    name,
                    card_number,
                    rarity,
                    image_small
                FROM cards
                WHERE set_id = :set_id
                ORDER BY
                    CASE
                        WHEN card_number ~ '^[0-9]+$'
                        THEN card_number::integer
                    END NULLS LAST,
                    card_number
            """),
            {"set_id": set_id}
        )

        return result.mappings().all()

@app.get("/dashboard")
def dashboard():

    return FileResponse(
        "app/static/dashboard.html"
    )

Salvare e uscire e ricostruire il DB:
cd /opt/pokemon
docker-compose up -d --build

Creiamo la Dashboard

mkdir -p /opt/pokemon/backend/app/static
nano /opt/pokemon/backend/app/static/dashboard.html

Inserire:
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>Pokemon Dashboard</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chartjs-plugin-datalabels@2"></script>
</head>
<body>

<h1>Pokemon Dashboard</h1>

<canvas id="statsChart" width="800" height="400"></canvas>

<script>
async function loadStats() {

    const response = await fetch('/stats?user_id=1');
    const stats = await response.json();

    Chart.register(ChartDataLabels);

    new Chart(document.getElementById('statsChart'), {
        type: 'bar',
        data: {
            labels: [
                'Carte Totali',
                'Carte Diverse',
                'Set Iniziati'
            ],
            datasets: [{
                label: 'Statistiche',
                data: [
                    stats.total_cards,
                    stats.unique_cards,
                    stats.sets_started
                ],
                backgroundColor: [
                    '#1976d2',
                    '#388e3c',
                    '#f57c00'
                ]
            }]
        },
        options: {
            responsive: true,
            plugins: {
                datalabels: {
                    anchor: 'end',
                    align: 'top',
                    color: '#000',
                    font: {
                        weight: 'bold',
                        size: 16
                    }
                }
            },
            scales: {
                y: {
                    beginAtZero: true
                }
            }
        }
    });
}

loadStats();
</script>

</body>
</html>

Salva e chiudi

Ricarica i file nel container:
cd /opt/pokemon
docker-compose down
docker-compose build --no-cache
docker-compose up -d

Verifichiamo aprendo la pagina web http://vm ip:8000/dashboard dovrebbe mostrare una pagina con un grafico

Creiamo finalmente la vera Home Page

nano /opt/pokemon/backend/app/static/index.html

Inseriamo:
<!DOCTYPE html>
<html lang="it">
<head>
    <meta charset="utf-8">
    <title>Pokemon DB</title>
    <link rel="stylesheet" href="/static/style.css">
</head>
<body>

<div class="header">
    <div class="logo">Pokemon DB</div>
    <div class="user">Paolo ▼</div>
</div>

<div class="container">

    <div class="set-info">
        <label>Set:</label>
        <select>
            <option>Pitch Black</option>
        </select>
    </div>

    <div class="toolbar">
        <button>Ricerca</button>
        <button>Aggiungi</button>
        <button>Collezione</button>
        <button>Statistiche</button>
    </div>

    <div class="set-info">
        <h2>Pitch Black</h2>
        <p>Tutte le carte del set</p>
    </div>

    <div id="card-grid" class="card-grid"></div>

</div>

<script>
async function loadCards() {

    const response = await fetch('/set/me5/cards');
    const cards = await response.json();

    const grid = document.getElementById('card-grid');

    cards.forEach(card => {

        const div = document.createElement('div');
        div.className = 'card';

        div.innerHTML = `
            <img src="${card.image_small}" alt="${card.name}">

            <div class="card-body">
                <div class="card-number">#${card.card_number}</div>
                <div class="card-name">${card.name}</div>
                <div class="card-rarity">${card.rarity || ''}</div>
            </div>

            <button>Aggiungi</button>
        `;

        grid.appendChild(div);
    });
}

loadCards();
</script>

</body>
</html>


Salva ed esci poi
nano /opt/pokemon/backend/app/static/style.css

e inserisci:
body {
    margin: 0;
    padding: 0;
    background: #eef2f5;
    font-family: Arial, sans-serif;
}

.header {
    background: white;
    height: 70px;
    padding: 0 30px;

    display: flex;
    justify-content: space-between;
    align-items: center;

    box-shadow: 0 2px 10px rgba(0,0,0,0.1);
}

.logo {
    font-size: 28px;
    font-weight: bold;
    color: #1976d2;
}

.user {
    font-size: 18px;
}

.container {
    padding: 20px;
}

.set-info {
    background: white;
    padding: 20px;
    border-radius: 12px;
    margin-bottom: 20px;
}

.toolbar {
    background: white;
    padding: 15px;
    border-radius: 12px;
    margin-bottom: 20px;

    display: flex;
    gap: 10px;
}

.toolbar button {
    border: none;
    border-radius: 8px;
    padding: 10px 15px;
    cursor: pointer;
}

.card-grid {
    display: grid;
    grid-template-columns: repeat(auto-fill, 180px);
    gap: 20px;
}

.card {
    width: 180px;
}

.card img {
    width: 100%;
    display: block;
}

.card-body {
    padding: 10px;
}

.card-number {
    font-weight: bold;
}

.card-name {
    margin-top: 5px;
    font-size: 18px;
}

.card-rarity {
    color: #666;
    margin-top: 5px;
}

.card button {
    width: 100%;
    border: none;
    padding: 12px;
    background: #1976d2;
    color: white;
    cursor: pointer;
}

Salva ed esci poi sostituisci il primo blocco di main.py con questo:
from fastapi.responses import FileResponse
from fastapi.staticfiles import StaticFiles
from fastapi import FastAPI
from pydantic import BaseModel
from sqlalchemy import text

from app.database import engine

app = FastAPI(
    title="Pokemon Collection API"
)
app.mount(
    "/static",
    StaticFiles(directory="app/static"),
    name="static"
)

Ricarica i file nel container:
cd /opt/pokemon
docker-compose down
docker-compose build --no-cache
docker-compose up -d

Verifichiamo aprendo la pagina web http://vm ip:8000/index dovrebbe mostrare una bozza della pagina simile a questa

Un miglioramento potrebbe essere che le carte possedute non si vedono a colori ma in bianco e nero, per farlo servono tre modifiche

Modificare il file main.py in modo da sotiture il blocco @app.get("/set/{set_id}/cards") con questo:

@app.get("/set/{set_id}/cards")
def get_set_cards(set_id: str):

    with engine.connect() as conn:

        result = conn.execute(
            text("""
                SELECT
                    c.id,
                    c.name,
                    c.card_number,
                    c.rarity,
                    c.image_small,

                    EXISTS (
                        SELECT 1
                        FROM collection col
                        WHERE col.card_id = c.id
                        AND col.user_id = 1
                    ) AS owned

                FROM cards c

                WHERE c.set_id = :set_id

                ORDER BY
                    CASE
                        WHEN c.card_number ~ '^[0-9]+$'
                        THEN c.card_number::integer
                    END NULLS LAST,
                    c.card_number
            """),
            {"set_id": set_id}
        )

        return result.mappings().all()

Modificare il file index.html in modo da sostiture la riga:
div.className = 'card';
con questa:
div.className = card.owned
    ? 'card'
    : 'card not-owned';

Modificare il file css e aggiungere:
.not-owned img {
    filter: grayscale(100%);
    opacity: 0.30;
}

Ricompilare:
docker-compose down
docker-compose build --no-cache
docker-compose up -d

Adesso la pagina dovrebbe essere così perchè non abbiamo ancora inserito alcuna carta

Il resto della guida nei prossimi giorni..