#!/bin/sh

DB=${1:-users.sq3}
CSV=${2:-teams.csv}
sqlite3 ${DB} <<-EOF
	.mode csv
	PRAGMA foreign_keys=on;
	CREATE TABLE IF NOT EXISTS teams(team primary key);
	CREATE TEMPORARY TABLE guest(team);
	INSERT INTO guest VALUES('guest');
	INSERT INTO teams SELECT team FROM GUEST
			  WHERE team NOT IN (SELECT team FROM teams);

	CREATE TABLE IF NOT EXISTS users(
	  user primary key, gecos, email, team,
	  FOREIGN KEY(team) REFERENCES teams(team)
	  	ON DELETE CASCADE ON UPDATE CASCADE);
	CREATE TEMPORARY TABLE teamcsv(teamno,teamname,uid,name);
	.import $CSV teamcsv
	DELETE FROM teamcsv WHERE teamno NOT GLOB '[0-9]*';
	INSERT INTO teams SELECT distinct teamname FROM teamcsv
			  WHERE teamname NOT IN (SELECT team FROM teams);
	INSERT INTO users SELECT uid, name, uid||'@itl.koeki-u.ac.jp', teamname
			 FROM teamcsv WHERE uid NOT IN (SELECT user FROM users);

	CREATE TABLE IF NOT EXISTS skey(
	  user, skey, expire,
	  UNIQUE(user, skey),
	  FOREIGN KEY(user) REFERENCES users(user)
	  	ON DELETE CASCADE ON UPDATE CASCADE);
	CREATE TABLE IF NOT EXISTS tmpkey(
	  user, tmpkey, token, expire,
	  UNIQUE(user, tmpkey),
	  FOREIGN KEY(user) REFERENCES users(user)
	  	ON DELETE CASCADE ON UPDATE CASCADE);
EOF