export-pion

#!/usr/bin/env python3
# Účast a body ve všech soutěžích ročníku (anonymně)

from collections import defaultdict
from dataclasses import dataclass
from decimal import Decimal
from sqlalchemy import and_
from sqlalchemy.orm import joinedload
import sys
from typing import DefaultDict, Tuple

import mo.csv
import mo.db as db
from mo.util import assert_not_none
from mo.util_format import format_decimal

sess = db.get_session()

points_by_uid_roundid: DefaultDict[Tuple[int, int], Decimal] = defaultdict(Decimal)

sols = (sess.query(db.Solution)
        .options(joinedload(db.Solution.task).joinedload(db.Task.round))
        .all())

for sol in sols:
    if sol.points is not None:
        points_by_uid_roundid[sol.user_id, sol.task.round.master_round_id] += sol.points

res = (sess.query(db.Participant, db.Participation, db.Contest, db.Round)
       .select_from(db.Participant)
       .join(db.Participation, db.Participation.user_id == db.Participant.user_id)
       .join(db.Contest, db.Contest.contest_id == db.Participation.contest_id)
       .join(db.Round, and_(db.Round.round_id == db.Contest.round_id, db.Round.year == db.Participant.year))
       .options(joinedload(db.Participant.user))
       .options(joinedload(db.Participant.school_place).joinedload(db.Place.parent_place).joinedload(db.Place.parent_place).joinedload(db.Place.parent_place))
       .options(joinedload(db.Participant.school_place).joinedload(db.Place.school))
       .options(joinedload(db.Participation.place))
       .filter(db.Round.master_round_id == db.Round.round_id)
       .filter(db.Participation.state == db.PartState.active)
       .all())


@dataclass
class Row(mo.csv.Row):
    rocnik: str = ""
    kategorie: str = ""
    kolo_seq: str = ""
    kolo: str = ""
    misto: str = ""
    id_mista: str = ""
    nuts_mista: str = ""
    id_ucastnika: str = ""
    trida: str = ""
    nazev_skoly: str = ""
    id_skoly: str = ""
    izo_skoly: str = ""
    mesto_skoly: str = ""
    okres_skoly: str = ""
    kraj_skoly: str = ""
    nuts_skoly: str = ""
    odhad_pohlavi: str = ""
    body: str = ""


output = []
for pant, pion, ct, rnd in res:
    female_guess = pant.user.last_name.endswith('á')
    if (pion.user_id, rnd.round_id) in points_by_uid_roundid:
        points = assert_not_none(format_decimal(points_by_uid_roundid[pion.user_id, rnd.round_id]))
    else:
        points = ""
    output.append(Row(
        rocnik=str(rnd.year),
        kategorie=rnd.category,
        kolo_seq=str(rnd.seq),
        kolo=rnd.round_type.friendly_name() if rnd.round_type != db.RoundType.other else rnd.name,
        misto=ct.place.name,
        id_mista=str(ct.place.place_id),
        nuts_mista=str(ct.place.nuts) if ct.place.nuts else "",
        id_ucastnika=str(pion.user_id),
        trida=pant.grade,
        nazev_skoly=pant.school_place.name,
        id_skoly=str(pant.school),
        izo_skoly=pant.school_place.school.red_izo or "",
        mesto_skoly=pant.school_place.parent_place.name,
        okres_skoly=pant.school_place.parent_place.parent_place.name,
        kraj_skoly=pant.school_place.parent_place.parent_place.parent_place.name,
        nuts_skoly=pant.school_place.parent_place.parent_place.nuts,
        odhad_pohlavi="Ž" if female_guess else "M",
        body=points,
    ))

output.sort(key=lambda o: (o.rocnik, o.kategorie, o.kolo_seq, o.id_mista, o.id_ucastnika))
mo.csv.write(sys.stdout, mo.csv.FileFormat.en_csv, Row, output)