#!/bin/sh

DB=${1:-users.sq3}
CSV=${2:-teams.csv}
sqlite3 ${DB} <<-EOF
	.mode csv
	PRAGMA foreign_keys=on;
	.read inittbl.sql
	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 TEMPORARY TABLE teamcsv(teamno,teamname,user,name);
	.import $CSV teamcsv
	DELETE FROM teamcsv WHERE name LIKE 'name' OR name LIKE 'gecos';
	INSERT INTO teams SELECT distinct teamname FROM teamcsv
			  WHERE teamname NOT IN (SELECT team FROM teams);
	INSERT INTO users SELECT user, name, user||'@itl.koeki-u.ac.jp', teamname
			 FROM teamcsv WHERE user NOT IN (SELECT user FROM users);

EOF