473 lines
18 KiB
Python
473 lines
18 KiB
Python
"""
|
|
sync.py — Push/pull synkronisering mellem lokal app og server.
|
|
|
|
POST /sync/push — send lokal data op til server
|
|
GET /sync/pull — hent server-data ned til app
|
|
"""
|
|
import uuid
|
|
import logging
|
|
from datetime import datetime, timezone
|
|
from fastapi import APIRouter, Depends
|
|
from sqlalchemy.orm import Session
|
|
from pydantic import BaseModel
|
|
from typing import Optional
|
|
|
|
from app.core.database import get_db
|
|
from app.core.security import get_current_user
|
|
from app.models import (
|
|
User, Song, Dance, DanceLevel, Project, ProjectSong,
|
|
PlaylistShare, CommunityDance, SongDance, SongAltDance,
|
|
)
|
|
|
|
router = APIRouter(prefix="/sync", tags=["sync"])
|
|
logger = logging.getLogger(__name__)
|
|
|
|
|
|
# ── Schemas ───────────────────────────────────────────────────────────────────
|
|
|
|
class SongData(BaseModel):
|
|
local_id: str
|
|
title: str
|
|
artist: str = ""
|
|
album: str = ""
|
|
bpm: int = 0
|
|
duration_sec: int = 0
|
|
mbid: str = ""
|
|
acoustid: str = ""
|
|
|
|
class DanceData(BaseModel):
|
|
name: str
|
|
level_name: str = ""
|
|
choreographer: str = ""
|
|
video_url: str = ""
|
|
stepsheet_url: str = ""
|
|
notes: str = ""
|
|
|
|
class SongDanceData(BaseModel):
|
|
song_local_id: str
|
|
dance_name: str
|
|
level_name: str = ""
|
|
dance_order: int = 1
|
|
|
|
class SongAltDanceData(BaseModel):
|
|
song_local_id: str
|
|
dance_name: str
|
|
level_name: str = ""
|
|
note: str = ""
|
|
user_rating: Optional[int] = None
|
|
|
|
class PlaylistSongData(BaseModel):
|
|
song_local_id: str
|
|
song_title: str = ""
|
|
song_artist: str = ""
|
|
position: int
|
|
status: str = "pending"
|
|
is_workshop: bool = False
|
|
dance_override: str = ""
|
|
|
|
class PlaylistData(BaseModel):
|
|
local_id: str
|
|
name: str
|
|
description: str = ""
|
|
tags: str = ""
|
|
visibility: str = "private"
|
|
songs: list[PlaylistSongData] = []
|
|
|
|
class PushPayload(BaseModel):
|
|
songs: list[SongData] = []
|
|
dances: list[DanceData] = []
|
|
song_dances: list[SongDanceData] = []
|
|
song_alts: list[SongAltDanceData] = []
|
|
playlists: list[PlaylistData] = []
|
|
deleted_playlists: list[str] = [] # server-IDs (Project.id)
|
|
songs_with_dances_synced: list[str] = [] # sang-IDs der er fuldt synkroniseret
|
|
|
|
|
|
# ── Hjælpefunktion: find eller opret sang globalt ─────────────────────────────
|
|
|
|
def _find_or_create_song(db: Session, title: str, artist: str = "",
|
|
mbid: str = "", acoustid: str = "",
|
|
album: str = "", bpm: int = 0,
|
|
duration_sec: int = 0) -> Song:
|
|
"""
|
|
Match-hierarki:
|
|
1. MBID — sikreste
|
|
2. AcoustID
|
|
3. Titel + artist
|
|
4. Opret ny
|
|
"""
|
|
if mbid:
|
|
song = db.query(Song).filter_by(mbid=mbid).first()
|
|
if song:
|
|
return song
|
|
|
|
if acoustid:
|
|
song = db.query(Song).filter_by(acoustid=acoustid).first()
|
|
if song:
|
|
# Tilføj mbid hvis den mangler
|
|
if mbid and not song.mbid:
|
|
song.mbid = mbid
|
|
return song
|
|
|
|
if title:
|
|
song = db.query(Song).filter(
|
|
Song.title == title,
|
|
Song.artist == artist,
|
|
).first()
|
|
if song:
|
|
# Opdater med bedre data hvis tilgængeligt
|
|
if mbid and not song.mbid:
|
|
song.mbid = mbid
|
|
if acoustid and not song.acoustid:
|
|
song.acoustid = acoustid
|
|
if bpm and not song.bpm:
|
|
song.bpm = bpm
|
|
return song
|
|
|
|
# Opret ny global sang
|
|
song = Song(
|
|
title=title, artist=artist, album=album,
|
|
bpm=bpm, duration_sec=duration_sec,
|
|
mbid=mbid or None,
|
|
acoustid=acoustid or None,
|
|
)
|
|
db.add(song)
|
|
db.flush()
|
|
return song
|
|
|
|
|
|
# ── Push ──────────────────────────────────────────────────────────────────────
|
|
|
|
@router.post("/push")
|
|
def push(
|
|
payload: PushPayload,
|
|
db: Session = Depends(get_db),
|
|
me: User = Depends(get_current_user),
|
|
):
|
|
"""Upload lokal data til server. Returnerer server-IDs."""
|
|
import sqlalchemy as _sa
|
|
|
|
song_id_map = {} # local_id → server Song.id
|
|
dance_id_map = {} # "name|level_id" → Dance.id
|
|
level_map = {} # level_name.lower() → DanceLevel.id
|
|
|
|
# ── Dans-niveauer ─────────────────────────────────────────────────────────
|
|
for lvl in db.query(DanceLevel).all():
|
|
level_map[lvl.name.lower()] = lvl.id
|
|
|
|
# ── Sange (globale) ───────────────────────────────────────────────────────
|
|
for s in payload.songs:
|
|
if not s.title:
|
|
continue
|
|
song = _find_or_create_song(
|
|
db, s.title, s.artist,
|
|
mbid=s.mbid, acoustid=s.acoustid,
|
|
album=s.album, bpm=s.bpm, duration_sec=s.duration_sec,
|
|
)
|
|
song_id_map[s.local_id] = song.id
|
|
|
|
# ── Danse ─────────────────────────────────────────────────────────────────
|
|
for d in payload.dances:
|
|
level_id = level_map.get(d.level_name.lower()) if d.level_name else None
|
|
key = f"{d.name.lower()}|{level_id}"
|
|
existing = db.query(Dance).filter_by(name=d.name, level_id=level_id).first()
|
|
if existing:
|
|
if d.choreographer: existing.choreographer = d.choreographer
|
|
if d.video_url: existing.video_url = d.video_url
|
|
if d.stepsheet_url: existing.stepsheet_url = d.stepsheet_url
|
|
dance_id_map[key] = existing.id
|
|
else:
|
|
dance = Dance(
|
|
name=d.name, level_id=level_id,
|
|
choreographer=d.choreographer,
|
|
video_url=d.video_url,
|
|
stepsheet_url=d.stepsheet_url,
|
|
notes=d.notes,
|
|
)
|
|
db.add(dance)
|
|
db.flush()
|
|
dance_id_map[key] = dance.id
|
|
|
|
# ── Sang-dans tags — synkroniser fuldt per sang ──────────────────────────
|
|
# Slet eksisterende tags for sange der er med i push, genindsæt fra klient
|
|
synced_song_ids = set()
|
|
for sd in payload.song_dances:
|
|
song_id = song_id_map.get(sd.song_local_id)
|
|
if not song_id:
|
|
continue
|
|
if song_id not in synced_song_ids:
|
|
db.execute(_sa.text("DELETE FROM song_dances WHERE song_id=:sid"),
|
|
{"sid": song_id})
|
|
synced_song_ids.add(song_id)
|
|
level_id = level_map.get(sd.level_name.lower()) if sd.level_name else None
|
|
key = f"{sd.dance_name.lower()}|{level_id}"
|
|
dance_id = dance_id_map.get(key)
|
|
if not dance_id:
|
|
continue
|
|
db.execute(_sa.text(
|
|
"INSERT IGNORE INTO song_dances (id, song_id, dance_id, dance_order) "
|
|
"VALUES (:id, :song_id, :dance_id, :dance_order)"
|
|
), {"id": str(uuid.uuid4()), "song_id": song_id,
|
|
"dance_id": dance_id, "dance_order": sd.dance_order})
|
|
|
|
# Sange der er fuldt synkroniseret men har ingen dans-tags — slet på server
|
|
for local_id in payload.songs_with_dances_synced:
|
|
song_id = song_id_map.get(local_id)
|
|
if song_id and song_id not in synced_song_ids:
|
|
db.execute(_sa.text("DELETE FROM song_dances WHERE song_id=:sid"),
|
|
{"sid": song_id})
|
|
|
|
for sa in payload.song_alts:
|
|
song_id = song_id_map.get(sa.song_local_id)
|
|
if not song_id:
|
|
continue
|
|
level_id = level_map.get(sa.level_name.lower()) if sa.level_name else None
|
|
key = f"{sa.dance_name.lower()}|{level_id}"
|
|
dance_id = dance_id_map.get(key)
|
|
if not dance_id:
|
|
continue
|
|
# Opdater community rating hvis bruger har givet en vurdering
|
|
if sa.user_rating and 1 <= sa.user_rating <= 5:
|
|
from app.models import CommunityDanceAlt, DanceAltRating
|
|
song_obj = db.query(Song).filter_by(id=song_id).first()
|
|
if song_obj:
|
|
alt = db.query(CommunityDanceAlt).filter_by(
|
|
song_title=song_obj.title,
|
|
song_artist=song_obj.artist,
|
|
alt_dance_id=dance_id,
|
|
).first()
|
|
if not alt:
|
|
alt = CommunityDanceAlt(
|
|
id=str(uuid.uuid4()),
|
|
song_mbid=song_obj.mbid or None,
|
|
song_title=song_obj.title,
|
|
song_artist=song_obj.artist,
|
|
alt_dance_id=dance_id,
|
|
submitted_by=me.id,
|
|
avg_rating=float(sa.user_rating),
|
|
rating_count=1,
|
|
)
|
|
db.add(alt)
|
|
db.flush()
|
|
existing_r = db.query(DanceAltRating).filter_by(
|
|
alternative_id=alt.id, user_id=me.id
|
|
).first()
|
|
if existing_r:
|
|
old_score = existing_r.score
|
|
existing_r.score = sa.user_rating
|
|
total = alt.avg_rating * alt.rating_count - old_score + sa.user_rating
|
|
alt.avg_rating = total / alt.rating_count
|
|
else:
|
|
db.add(DanceAltRating(
|
|
id=str(uuid.uuid4()),
|
|
alternative_id=alt.id,
|
|
user_id=me.id,
|
|
score=sa.user_rating,
|
|
))
|
|
total = alt.avg_rating * alt.rating_count + sa.user_rating
|
|
alt.rating_count += 1
|
|
alt.avg_rating = total / alt.rating_count
|
|
db.execute(_sa.text(
|
|
"INSERT IGNORE INTO song_alt_dances (id, song_id, dance_id, note) "
|
|
"VALUES (:id, :song_id, :dance_id, :note)"
|
|
), {"id": str(uuid.uuid4()), "song_id": song_id,
|
|
"dance_id": dance_id, "note": sa.note or ""})
|
|
|
|
# ── Playlister ────────────────────────────────────────────────────────────
|
|
playlist_id_map = {}
|
|
for pl in payload.playlists:
|
|
# Find eksisterende via server-ID (local_id er api_project_id på klienten)
|
|
existing = None
|
|
if pl.local_id:
|
|
existing = db.query(Project).filter_by(
|
|
id=pl.local_id, owner_id=me.id
|
|
).first()
|
|
if not existing:
|
|
existing = db.query(Project).filter_by(
|
|
owner_id=me.id, name=pl.name
|
|
).first()
|
|
|
|
if existing:
|
|
existing.name = pl.name
|
|
existing.description = pl.description
|
|
existing.visibility = pl.visibility
|
|
if pl.songs:
|
|
db.query(ProjectSong).filter_by(project_id=existing.id).delete()
|
|
project = existing
|
|
else:
|
|
project = Project(
|
|
owner_id=me.id, name=pl.name,
|
|
description=pl.description, visibility=pl.visibility,
|
|
)
|
|
db.add(project)
|
|
db.flush()
|
|
playlist_id_map[pl.local_id] = project.id
|
|
|
|
for ps in pl.songs:
|
|
# Find sang via song_id_map eller titel+artist
|
|
song_id = song_id_map.get(ps.song_local_id)
|
|
if not song_id and ps.song_title:
|
|
song = _find_or_create_song(db, ps.song_title, ps.song_artist)
|
|
song_id = song.id
|
|
if not song_id:
|
|
continue
|
|
db.add(ProjectSong(
|
|
project_id=project.id, song_id=song_id,
|
|
position=ps.position, status=ps.status,
|
|
is_workshop=ps.is_workshop,
|
|
dance_override=ps.dance_override,
|
|
))
|
|
|
|
# ── Slet playlister ───────────────────────────────────────────────────────
|
|
for project_id in payload.deleted_playlists:
|
|
proj = db.query(Project).filter_by(id=project_id, owner_id=me.id).first()
|
|
if proj:
|
|
db.query(ProjectSong).filter_by(project_id=proj.id).delete()
|
|
db.delete(proj)
|
|
|
|
db.commit()
|
|
|
|
return {
|
|
"status": "ok",
|
|
"songs_synced": len(song_id_map),
|
|
"playlists_synced": len(playlist_id_map),
|
|
"song_id_map": {k: str(v) for k, v in song_id_map.items()},
|
|
"playlist_id_map": {k: str(v) for k, v in playlist_id_map.items()},
|
|
}
|
|
|
|
|
|
# ── Pull ──────────────────────────────────────────────────────────────────────
|
|
|
|
@router.get("/pull")
|
|
def pull(
|
|
db: Session = Depends(get_db),
|
|
me: User = Depends(get_current_user),
|
|
):
|
|
"""Hent server-data til lokal app."""
|
|
|
|
# Dans-niveauer
|
|
levels = [
|
|
{"id": l.id, "name": l.name, "sort_order": l.sort_order}
|
|
for l in db.query(DanceLevel).order_by(DanceLevel.sort_order).all()
|
|
]
|
|
|
|
# Danse
|
|
dances = [
|
|
{
|
|
"name": d.name,
|
|
"level_id": d.level_id,
|
|
"choreographer": d.choreographer,
|
|
"video_url": d.video_url,
|
|
"stepsheet_url": d.stepsheet_url,
|
|
"notes": d.notes,
|
|
"use_count": d.use_count,
|
|
}
|
|
for d in db.query(Dance).order_by(Dance.use_count.desc()).limit(500).all()
|
|
]
|
|
|
|
# Delte playlister
|
|
shared_ids = {
|
|
s.project_id for s in db.query(PlaylistShare).filter(
|
|
(PlaylistShare.shared_with_id == me.id) |
|
|
(PlaylistShare.invited_email == me.email)
|
|
).all()
|
|
}
|
|
|
|
shared = []
|
|
for p in db.query(Project).filter(Project.id.in_(shared_ids)).all():
|
|
if p.owner_id == me.id:
|
|
continue
|
|
owner = db.query(User).filter_by(id=p.owner_id).first()
|
|
shared.append({
|
|
"server_id": p.id,
|
|
"name": p.name,
|
|
"owner": owner.username if owner else "?",
|
|
"songs": [
|
|
{
|
|
"song_id": str(ps.song_id),
|
|
"title": ps.song.title,
|
|
"artist": ps.song.artist,
|
|
"mbid": ps.song.mbid or "",
|
|
"acoustid": ps.song.acoustid or "",
|
|
"bpm": ps.song.bpm,
|
|
"duration_sec": ps.song.duration_sec,
|
|
"position": ps.position,
|
|
"status": ps.status,
|
|
"is_workshop": ps.is_workshop,
|
|
"dance_override": ps.dance_override or "",
|
|
}
|
|
for ps in sorted(p.project_songs, key=lambda x: x.position)
|
|
if ps.song
|
|
],
|
|
})
|
|
|
|
# Egne playlister
|
|
my_playlists = []
|
|
for p in db.query(Project).filter_by(owner_id=me.id).all():
|
|
my_playlists.append({
|
|
"server_id": p.id,
|
|
"name": p.name,
|
|
"description": p.description or "",
|
|
"songs": [
|
|
{
|
|
"song_id": str(ps.song_id),
|
|
"title": ps.song.title,
|
|
"artist": ps.song.artist,
|
|
"mbid": ps.song.mbid or "",
|
|
"acoustid": ps.song.acoustid or "",
|
|
"bpm": ps.song.bpm,
|
|
"duration_sec": ps.song.duration_sec,
|
|
"position": ps.position,
|
|
"status": ps.status,
|
|
"is_workshop": ps.is_workshop,
|
|
"dance_override": ps.dance_override or "",
|
|
}
|
|
for ps in sorted(p.project_songs, key=lambda x: x.position)
|
|
if ps.song
|
|
],
|
|
})
|
|
|
|
logger.info(f"Pull: {len(my_playlists)} playlister for {me.username}")
|
|
|
|
# Dans-tags (brugerens egne)
|
|
song_tags = []
|
|
for sd in db.query(SongDance).all():
|
|
dance = db.query(Dance).filter_by(id=sd.dance_id).first()
|
|
if not dance:
|
|
continue
|
|
level = db.query(DanceLevel).filter_by(id=dance.level_id).first() if dance.level_id else None
|
|
song_tags.append({
|
|
"song_id": sd.song_id,
|
|
"dance_name": dance.name,
|
|
"choreographer": dance.choreographer or "",
|
|
"level_name": level.name if level else "",
|
|
"dance_order": sd.dance_order,
|
|
})
|
|
|
|
# Community alternativ-danse (top 500 mest ratede)
|
|
from app.models import CommunityDanceAlt, DanceAltRating
|
|
community_alts = []
|
|
for alt in db.query(CommunityDanceAlt).order_by(
|
|
CommunityDanceAlt.avg_rating.desc()
|
|
).limit(500).all():
|
|
my_rating = db.query(DanceAltRating).filter_by(
|
|
alternative_id=alt.id, user_id=me.id
|
|
).first()
|
|
community_alts.append({
|
|
"song_mbid": alt.song_mbid or "",
|
|
"song_title": alt.song_title,
|
|
"song_artist": alt.song_artist,
|
|
"dance_name": alt.alt_dance.name if alt.alt_dance else "",
|
|
"avg_rating": round(alt.avg_rating, 1),
|
|
"rating_count": alt.rating_count,
|
|
"my_rating": my_rating.score if my_rating else None,
|
|
})
|
|
|
|
return {
|
|
"levels": levels,
|
|
"dances": dances,
|
|
"shared": shared,
|
|
"my_playlists": my_playlists,
|
|
"song_tags": song_tags,
|
|
"community_alts": community_alts,
|
|
} |