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

create() {		# テーブル作成と外部キーテーブル初期化
  tbl=`query "SELECT name FROM sqlite_master WHERE name='datelist';"`
  if [ -z "$tbl" ]; then	# $tbl が空文字列なら
    query<<-EOF
	CREATE TABLE datelist (date PRIMARY KEY);
	CREATE TABLE sched (
	  name, date, okng,
	  UNIQUE(name, date),
	  FOREIGN KEY(date) REFERENCES datelist(date)
	);
	EOF
  fi
  ndate=`query "SELECT count(date) FROM datelist;"`
  if [ "$ndate" -eq 0 -a -s datelist.csv ]; then
    query<<-EOF
	.mode csv
	.import datelist.csv datelist
	EOF
  fi
}

add() {			# レコード登録機能
  who="$1"
  { echo "BEGIN;"
    while IFS="=" read date value; do
      case $value in
	[Yy][Ee][Ss])	val='○' ;;
	[Nn][Oo])		val='×' ;;
	*)		val='-' ;;
      esac
      echo "REPLACE INTO sched VALUES('$who', '$date', '$val');"
    done
    echo "COMMIT;"
  } | query
}

list() {		# gensql.sh を利用してHTMLの表を出力
  echo '<table border="1">'
  ./gensql.sh | sqlite3 -header -html $db
  echo "</table>"
}

delete() {		# 削除機能
  who="$1"		# 第1引数 = 削除したい人
  query "DELETE FROM sched WHERE name='$who';"
}

while getopts a:id:l i; do
  case "$i" in
    a)	job=add
	who=$OPTARG ;;
    i)	job=create ;;
    l)	job=list ;;
    d)	job=delete who=$OPTARG ;;
  esac
done
shift $((OPTIND-1))

case $job in
  add|delete)		# オプション指定なしの場合
    $job $who
    ;;
  *)
    $job "$@" ;;
esac