#!/bin/bash
DBNAME=$1
echo $DBNAME
echo "SELECT 'in lineup, not in players', player, CONCAT(rnd, '-', segment), tabl, position FROM (
    SELECT openN player, rnd, segment, CONCAT(tabl, 'o') tabl, 'N' position FROM segments
    UNION
    SELECT openS player, rnd, segment, CONCAT(tabl, 'o') tabl, 'S' position FROM segments
    UNION
    SELECT openE player, rnd, segment, CONCAT(tabl, 'o') tabl, 'E' position FROM segments
    UNION
    SELECT openW player, rnd, segment, CONCAT(tabl, 'o') tabl, 'W' position FROM segments
    UNION
    SELECT closeN player, rnd, segment, CONCAT(tabl, 'z') tabl, 'N' position FROM segments
    UNION
    SELECT closeS player, rnd, segment, CONCAT(tabl, 'z') tabl, 'S' position FROM segments
    UNION
    SELECT closeE player, rnd, segment, CONCAT(tabl, 'z') tabl, 'E' position FROM segments
    UNION
    SELECT closeW player, rnd, segment, CONCAT(tabl, 'z') tabl, 'W' position FROM segments
) lineup_players WHERE player NOT IN (SELECT id FROM players) AND player > 0;" | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME -N
ROUNDS=$(echo "SELECT roundcnt FROM admin;" | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME -N)
SEGMENTS=$(echo "SELECT segmentsperround FROM admin;" | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME -N)
for R in $( eval echo {1..$ROUNDS} )
do
    for SEGMENT in $( eval echo {1..$SEGMENTS} )
    do
        ROUND=$(printf "%02d" $R)
        echo "SELECT 'in butler, not in players', id, CONCAT($R, '-', $SEGMENT) FROM butler WHERE id NOT IN (SELECT id FROM players) AND seg${ROUND}_${SEGMENT} IS NOT NULL;" | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME -N
        echo "SELECT 'in butler, not in lineup', id, CONCAT($R, '-', $SEGMENT) FROM butler WHERE id NOT IN (
SELECT openN FROM segments WHERE rnd = $R AND segment = $SEGMENT
UNION
SELECT openS FROM segments WHERE rnd = $R AND segment = $SEGMENT
UNION
SELECT openE FROM segments WHERE rnd = $R AND segment = $SEGMENT
UNION
SELECT openW FROM segments WHERE rnd = $R AND segment = $SEGMENT
UNION
SELECT closeN FROM segments WHERE rnd = $R AND segment = $SEGMENT
UNION
SELECT closeS FROM segments WHERE rnd = $R AND segment = $SEGMENT
UNION
SELECT closeE FROM segments WHERE rnd = $R AND segment = $SEGMENT
UNION
SELECT closeW FROM segments WHERE rnd = $R AND segment = $SEGMENT
) AND seg${ROUND}_${SEGMENT} IS NOT NULL;" | mysql ${LIGA_MYSQL_CONNECTION_OPTS} $DBNAME -N
    done
done