#!/bin/sh
db=score.sq3
query() {
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' $db "$@"
}

create() {			# テーブルの作成と初期データ登録を行なう関数
  tbl=`query "SELECT name FROM sqlite_master WHERE name='students';"`
  if [ -z "$tbl" ]; then	# $tbl が空文字列なら
    query<<-EOF
	CREATE TABLE students(sid PRIMARY KEY, name);
	CREATE TABLE lectpts(
	  sid, nlec, pts,
	  UNIQUE(sid, nlec),
	  FOREIGN KEY(sid) REFERENCES students(sid)
	);
	EOF
    if [ -s students.csv ]; then	# students.csv ファイルが空でなければ
      query<<-EOF
	.mode csv
	.import students.csv students
	EOF
    fi
  fi
}

drop() {			# テーブル削除を行なう関数
  query<<-EOF
	-- 外部キー制約があるので子テーブルを先に削除
	DROP TABLE IF EXISTS lectpts;
	DROP TABLE IF EXISTS students;
	EOF
  create			# 再初期化
}

delete() {			# レコードの削除を行なう関数
  query "DELETE FROM lectpts WHERE sid='$1' AND nlec=$2;"
}

summary() {			# 集計表を出す関数
  query<<-EOF
	SELECT l.sid,s.name,sum(l.pts)
	FROM lectpts l JOIN students s ON l.sid=s.sid
	GROUP BY l.sid;
	EOF
}

list() {			# 指定した学生の得点リストを出す関数
  # ヘッダを出す
  printf "sid\t"
  query<<-EOF
	SELECT group_concat(printf('%2d', nlec), ' ')
	FROM (SELECT DISTINCT nlec FROM lectpts ORDER BY nlec);
	EOF
  # 続いて $1 に指定した学生の得点を横並びで出す
  printf "$1\t"
  query<<-EOF
	SELECT group_concat(printf('%2d', coalesce(b.pts,0)), ' ')
	FROM (SELECT DISTINCT nlec FROM lectpts ORDER BY nlec) a
	LEFT JOIN (SELECT nlec,pts FROM lectpts WHERE sid='$1') b
	ON a.nlec=b.nlec;
	EOF
}

while getopts ilsd i; do	# i l s d オプションが有効
  case "$i" in
    i)	job=drop ;;		# -i が指定されたら... 以下同様
    l)	job=list ;;
    s)	job=summary ;;
    d)	job=delete ;;
    \?)	exit 1 ;;		# 無効なオプションは異常終了
  esac
done
shift $((OPTIND-1))		# オプション解析の終わった引数をずらす

case $job in
  "")				# オプション指定なしの場合
    if [ -n "$3" ]; then	# 第3引数=点数 まで与えられたら登録処理
      query "REPLACE INTO lectpts VALUES('$1', $2, $3);"
    fi
    ;;
  *)
    $job "$@"
esac