hackathon/gen.sh

#! /bin/env bash

FILE=db.sqlite
DB=sq://$FILE
DEST=./models

SCHEMA=0
OPTIND=1
while getopts "s" opt; do
case "$opt" in
  s) SCHEMA=1
esac
done

popd &> /dev/null

mkdir -p $DEST
rm -rf $DEST/*.xo.go

if [[ "$SCHEMA" = "1" ]]; then

  echo "Regenerating Schema..."
  (set -ex;
    rm $FILE
    usql -f sql/schema.sql $DB
  )
fi

set -ex

xo schema $DB -o $DEST
xo schema $DB --template yaml -o $DEST

# get all users
xo query $DB -M -B -T User -2 -a -F Users -o $DEST << ENDSQL
SELECT u.* FROM users u
ENDSQL

# get user by token
xo query $DB -M -B -T User -1 -2 -a -F UserByToken -o $DEST << ENDSQL
SELECT
  u.*
FROM tokens t
JOIN users u ON t.user_id = u.id
WHERE t.token = %%token string%%
ENDSQL

# delete a specific token
xo query $DB -M -B -X -2 -a -F DeleteToken -o $DEST -S token.xo.go << ENDSQL
DELETE FROM tokens
WHERE token = %%token string%%
ENDSQL

# delete all tokens for a user
xo query $DB -M -B -X -2 -a -F DeleteTokens -o $DEST -S token.xo.go << ENDSQL
DELETE FROM tokens
WHERE user_id = %%user_id string%%
ENDSQL


FIELDS='QuestionID string,P1Awarded sql.NullInt64,P2Awarded sql.NullInt64'
xo query $DB -M -B -2 -Z "$FIELDS" -T AllPartsData -F AllQuestionPartsData -o $DEST << ENDSQL
WITH attempts AS (
  SELECT *
  FROM question_attempt
  WHERE correct = true
)
SELECT DISTINCT
  a.question_id as question_id,
  MAX(p1.points_awarded) AS p1_awarded,
  MAX(p2.points_awarded) AS p2_awarded
FROM attempts a

LEFT JOIN attempts AS p1
ON p1.id = a.id 
AND p1.question_part = 1

LEFT JOIN attempts AS p2
ON p2.id = a.id 
AND p2.question_part = 2

WHERE a.user_id = %%user_id string%%

GROUP BY a.user_id, a.question_id
ENDSQL

FIELDS='P1Awarded int, P1Answer string, P2Awarded int, P2Answer string'
xo query $DB -M -B -2 -1 -Z "$FIELDS" -T PartsData -F QuestionPartsData -o $DEST << ENDSQL
WITH attempts AS (
  SELECT *
  FROM question_attempt
  WHERE question_id = %%question_id string%%
  AND   correct = true
)
SELECT
  COALESCE(p1.points_awarded, 0) AS p1_awarded,
  COALESCE(p1.answer, '') AS p1_answer,
  COALESCE(p2.points_awarded, 0) AS p2_awarded,
  COALESCE(p2.answer, '') AS p2_answer
FROM users u

LEFT JOIN attempts AS p1
ON p1.user_id = u.id 
AND p1.question_part = 1

LEFT JOIN attempts AS p2
ON p2.user_id = u.id 
AND p2.question_part = 2

WHERE
  u.id = %%user_id string%%
ENDSQL

FIELDS='ByUser int,Correct bool'
xo query $DB -M -B -2 -1 -Z "$FIELDS" -T Submissions -F QuestionSubmissions -o $DEST << ENDSQL
WITH attempts AS (
  SELECT * FROM question_attempt
  WHERE user_id = %%user_id string%%
    AND question_id = %%question_id string%%
    AND question_part = %%question_part int%%
)
SELECT
  c.count AS by_user, 
  COALESCE(a.correct, FALSE) AS correct
FROM (
  SELECT COUNT(*) as count from attempts
) c
LEFT JOIN attempts a ON a.correct
ENDSQL

FIELDS='Points int'
xo query $DB -M -B -2 -1 -Z "$FIELDS" -T Points -F UserPoints -o $DEST -S userpoints.xo.go << ENDSQL
SELECT COALESCE(SUM(points_awarded), 0) FROM question_attempt
WHERE user_id=%%user_id string%%
ENDSQL


FIELDS='Name string, Points int,LastValid Time'
xo query $DB -M -B -2 -Z "$FIELDS" -T LeaderboardEntry -F Leaderboard -o $DEST -S leaderboard.xo.go << ENDSQL
SELECT u.name, 
       COALESCE(SUM(qa.points_awarded), 0) AS points,
	   COALESCE(MAX(qa.submitted_at), u.created_at) as last_valid_submission
FROM users u
LEFT JOIN question_attempt qa ON u.id = qa.user_id AND qa.correct
GROUP BY u.id
ORDER BY COALESCE(SUM(qa.points_awarded), 0) DESC,
         MAX(qa.submitted_at) ASC
ENDSQL