from flask import g, render_template, redirect, url_for, request, flash
from sqlalchemy.orm import aliased, joinedload

import mo.db as db
from mo.rights import Right, roles_by_type
from mo.web.jinja import user_url
import mo.users
from mo.web.table import Table, Row, Column
from mo.web import app


@app.route('/org/')
def org_index():
    if 'place' in request.args:
        code = request.args['place']
        place = db.get_place_by_code(code)
        if place is not None:
            return redirect(url_for('org_place', id=place.place_id))
        else:
            flash(f'Místo s kódem {code} neexistuje', 'danger')

    if 'uid' in request.args:
        try:
            uid = int(request.args['uid'])
            user = mo.users.user_by_uid(uid)
            if user is not None:
                return redirect(user_url(user))
            flash(f'Uživatel s ID {uid} neexistuje', 'danger')
        except ValueError:
            flash('ID uživatele musí být číslo', 'danger')

    sess = db.get_session()

    roles = (sess.query(db.UserRole)
             .filter_by(user_id=g.user.user_id)
             .options(joinedload(db.UserRole.place))
             .all())

    contests = []
    for role in roles:
        q = (sess.query(db.Contest, db.UserRole.role, db.Round)
                .select_from(db.Contest)
                .filter_by(place_id=role.place_id)
                .join(db.UserRole, db.UserRole.user_role_id == role.user_role_id)
                .join(db.Round))
        if role.year:
            q = q.filter(db.Round.year == role.year)
        if role.category:
            q = q.filter(db.Round.category == role.category)
        if role.seq:
            q = q.filter(db.Round.seq == role.seq)
        contests += q.options(joinedload(db.Contest.place)).all()

    contests.sort(key=lambda r: (r[2].year, r[2].category, r[2].seq, r[2].part))

    return render_template('org_index.html', contests=contests, Right=Right,
            role_names=db.role_type_names, gatekeeper=g.gatekeeper)


school_export_columns = (
    Column(key='code', name='kod'),
    Column(key='name', name='nazev'),
    Column(key='town_code', name='kod_obce'),
    Column(key='town', name='obec'),
    Column(key='red_izo', name='red_izo'),
    Column(key='ico', name='ico'),
    Column(key='official_name', name='ofic_nazev'),
    Column(key='address', name='adresa'),
    Column(key='is_zs', name='typ_zs'),
    Column(key='is_ss', name='typ_ss'),
)


@app.route('/org/export/skoly')
def org_export_skoly():
    sess = db.get_session()
    format = request.args.get('format', 'en_csv')

    def gen_rows():
        town = aliased(db.Place)
        for p, s, t in (
                sess.query(db.Place, db.School, town)
                .filter(db.Place.type == db.PlaceType.school)
                .filter(db.Place.place_id == db.School.place_id)
                .filter(db.Place.parent == town.place_id)
                .yield_per(100)):
            yield Row(keys={
                'code': p.get_code(),
                'name': p.name,
                'red_izo': s.red_izo,
                'ico': s.ico,
                'official_name': s.official_name,
                'address': s.address,
                'is_zs': int(s.is_zs),
                'is_ss': int(s.is_ss),
                'town_code': t.get_code(),
                'town': t.name,
            })

    table = Table(school_export_columns, gen_rows(), 'skoly')
    return table.send_as(format, streaming=True)