#!/bin/sh

autoremovestop=2

# Setup variables for running time
if $isCGI; then
  case "$S4WORLDLIST" in
    *:*:*)
      worldlistfile=cache/worldlist
      worldgrpfile=cache/worldgrps
      worldoptionfile=cache/worldoption
      worldnamefile=cache/worldname
      if [ ! -e $worldlistfile -o $worldlistfile -ot s4-config.sh \
	     -o ! -e $worldoptionfile -o $worldoptionfile -ot s4-config.sh \
	     -o s4-world.sh -nt $worldoptionfile ]
      then
	echo S4MASTERURL=$S4MASTERURL >> tmp/debug.out
	cat <<-EOF > $worldlistfile
	<div><table>
	 <tr><th>World List</th></tr>
	 <tr><td title="Base World$nl拠点となるWorldです">&rArr;
	  <a href="${S4MASTERURL:-$URL}">Base</a></td></tr>
	EOF
	true > $worldoptionfile
	for i in $S4WORLDLIST; do
	  echo $i | {
	    IFS=: read name short d
	    cgi="s4-world-$short$cgiext"
	    conf="s4-config-$short.sh"
	    cat<<-EOF >>$worldlistfile
	    <tr><td title="$d">&rArr; <a href="$cgi">$name</a></td></tr>
		EOF
	    cat<<-EOF >>$worldoptionfile
	    <option title="$d" value="$conf">$name</option>
		EOF
	    echo "$name" > $worldnamefile.$short
	  }
	done
	if [ -s "$worldoptionfile" ]; then
	  echo "<option value=\"s4-config.sh\">Base</option>" >> $worldoptionfile
	  echo "</table>$nl</div>" >> $worldlistfile
	  sed 's/href="\([^>]*\)"/href="\1?grps"/' $worldlistfile \
	      > $worldgrpfile
	else
	  true > $worldoptionfile; true > $worldlistfile	# Remove contents
	  rm -f ${worldnamefile}.*
	fi
      fi
      if [ -s "$worldlistfile" ]; then
	S4WORLDS="▼spaste(\`$worldlistfile')"
	S4WORLDNAME=${S4WORLD:+`cat $worldnamefile.$S4WORLD`}
	S4WORLDGRPS="▼spaste(\`$worldgrpfile')"
      fi
      ;;
  esac
fi


syncaccount_1() {

  n_m=`query "SELECT printf('%d:%d',\
  	      (SELECT count(*) FROM m.user), \
	      (SELECT count(*) FROM user));"`
  n=${n_m%:*}; m=${n_m#*:}		# n:m
  if [ -z "$n" -o "$n" -lt 2 ]; then
    err "Skipping account sync because m.user cannot be reached[n=$n]."
    return -3
  fi
  if [ -z "$forceusersync" -a ! -f db/forceusersync ]; then
    if [ $((m-n)) -le $((autoremovestop+0)) ]; then
      # If if-condition evaluation fails, fall through to else-caluse
      rm -f db/forceusersync
    else
      err "More than $autoremovestop users vanished($((m-n)))."
      err "Automatic removal canceled.  If you want to sync user accounts"
      err "forcibly, touch db/forceusersync file each time of world update."
      return -4
    fi
  else
    rm -f db/forceusersync	# Remove flag file for future
  fi
  err "`gdate +%S.%3N` Starting account synchronization"
  SYNC_M=1

  if [ -s "$syncflag" ]; then
    prevsync=`tail -1 $syncflag|colrm 20`	# 2020-06-21 12:30:00 = 19cols
  else
    prevsync="2000-01-01 00:00:00"
  fi
  syncall=${db%.*}.syncall
  err syncallfile=$syncall
  if [ -e $syncall ]; then
    rm -f "$syncall"
    err "Force update user_m for all users"
  else
    case "$prevsync" in
      [2-9][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9]\ [012][0-9]:??:??)
      SYNCCOND="WHERE name in (SELECT name FROM m.user_s WHERE key='profupdate' AND val > '$prevsync')"
      err "prevsync=$prevsync"
      limited=`query "SELECT DISTINCT name FROM m.user_s $SYNCCOND;"`
      if [ -n "$limited" ]; then
	err synccond limited to "$limited"
      else
	err synccond canceled for user_m
	SYNCCOND="WHERE NULL"
	SYNC_M=
      fi
      err "Touch $syncall (owner=`id -un`) to update all user_m."
      ;;
      *)
	echo arere ;;
    esac
  fi
##  num=$(sqlite3 -bail -cmd 'PRAGMA FOREIGN_KEYS=on' $db <<EOF
  result=$(query <<EOF
BEGIN;
DElETE FROM main.user WHERE rowid NOT IN (SELECT rowid FROM m.user);
INSERT INTO main.user(rowid, name)
       SELECT rowid, name FROM m.user
       WHERE m.user.rowid NOT IN (SELECT rowid FROM user);
/* PrimaryKey:name cannot be changed, but ensure the integrity of user */
UPDATE user SET name = (SELECT name FROM m.user WHERE main.user.rowid=m.user.rowid) WHERE name != (SELECT name from m.user WHERE main.user.rowid=m.user.rowid);
DELETE FROM main.user_s WHERE rowid NOT IN (SELECT rowid FROM m.user_s);
REPLACE INTO main.user_s(rowid, name, key, type, val, bin)
	SELECT rowid,* FROM m.user_s;
${SYNC_M:+
DELETE FROM main.user_m WHERE rowid NOT IN (SELECT rowid FROM m.user_m);
REPLACE INTO main.user_m(rowid, name, key, type, val, bin)
	SELECT rowid,* FROM m.user_m    $SYNCCOND
	   EXCEPT
	SELECT rowid,* FROM main.user_m $SYNCCOND
	;} /* SYNC_M closed here */
SELECT 'OK';
END;
EOF
	)
  err "`gdate +%S.%3N` Update account tables finished with result=[$result]"
  test x"$result" = x"OK" && num=$(query <<EOF
/* Compare user tables */
WITH master AS (
  SELECT p.rowid,* FROM m.user p
  			 NATURAL LEFT JOIN m.user_s
	 ${SYNC_M:+	 NATURAL LEFT JOIN m.user_m}
  $SYNCCOND
), thisworld AS (
  SELECT p.rowid,* FROM user p
		  	NATURAL LEFT JOIN user_s
 	 ${SYNC_M:+	NATURAL LEFT JOIN user_m}
  $SYNCCOND
), m_a AS (
  SELECT * FROM master EXCEPT SELECT * FROM thisworld
), a_m AS (
  SELECT * FROM thisworld EXCEPT SELECT * FROM master
) SELECT (SELECT count(*) FROM m_a) + (SELECT count(*) FROM a_m);
EOF
     )
  if [ -n "$num" -a "$num" -eq 0 ]; then
    err "`gdate +%S.%3N` Account synchronization done in difference $num"
    echo "`date '+%F %T'`: Sync done by process $$" >> $syncflag
  else
    err "Account synch failed or bailed with num=[$num]"
  fi
  return $num
}

syncaccount() {
  forceusersync=$1
  ## err "db=$db mas=$S4MASTERDB sessdb=$sessdb"
  # If in parent world, no need to do rest of jobs
  if [ -z "$S4MASTERDB" -o ! -s "$S4MASTERDB" ]; then
    return
  fi
  # Confim child
  if [ "$db" -ef "$S4MASTERDB" ]; then
    return		# Points to the same file
  fi
  # File based sync check precedes to DB count check for performance issue
  syncflag=${db%.*}.synctime
  runflag=${db%.*}.run
  userupdateflag=`dirname $S4MASTERDB`/`basename $userupdateflag`
  test ! -e "$userupdateflag"			&& return
  [ -z "$forceusersync" -a -f "$syncflag" -a \
       "$syncflag" -nt "$userupdateflag" ] && return
  if [ -s "$runflag" ]; then
    limit=`cat $runflag|tr -c -d 0-9`
    err syncaccount: limit=$limit
    if [ -n "$limit" -a "$limit" -gt `date +%s` ]; then
      err "World $S4WORLD account sync withholded by process $$"
      return		# Running sync by other process not leaching timeout
    fi
  fi
  echo $((`date +%s` + 10)) > $runflag	# Setting running flag by timeout 10s
  err syncaccount: setrunflag[$runflag]=`cat $runflag`
  query<<-EOF
	.bail ON
	ATTACH DATABASE "$S4MASTERDB" AS m;
	EOF
  syncaccount_1
  rc=$?
  test -e "$runflag" && rm -f "$runflag"
  query<<-EOF
	DETACH DATABASE m;
	.bail OFF
	EOF
  return $rc
}

getworldDB() {	# $1=conf
  (unset DB; . "$1"; echo ${DB:-$defaultdb})
}

worldnameDBlist() {
  echo Base:Base:`getworldDB ./s4-config.sh`
  for w in $S4WORLDLIST; do
    case "$w" in
      *:*:*:*)	continue ;;
      *:*:*)	w=${w%:*}; name=${w%:*}; world=${w#*:} ;;
      *)	continue ;;
    esac
    d=`getworldDB "./s4-config-$world.sh"`
    test -s "$d" && echo ${world}:${name}:$d
  done
}

grepgrpworld() {
  world=$1; wname=$2; exp=$3; tdb=$4
  case $world in
    Base)	cgi=${S4MASTERURL:-$URL} ;;
    *)		cgi=s4-world-$world$cgiext ;;
  esac
  case $exp in
    mem:*)
      arg=${exp#*:}
      cond="user = '$arg'"
      guide="「${S4WORLDNAME:-Base}」以外の世界の所属グループ"
      s="(<a href=\"$cgi?stage=grps&kwd=mem:$arg\">所属絞</a>)" ;;
    *)
      arg=`sqlquote "%$3%"`
      cond="gname LIKE $arg"
      guide="$exp を含むグループは別世界にもあります"
      s="(<a href=\"$cgi?stage=grps&kwd=$exp\">絞込</a>)" ;;
  esac
  query <<-EOF |
	ATTACH DATABASE "$tdb" AS td;
	SELECT DISTINCT td.grp.rowid,hex(gname)
	FROM td.grp NATURAL JOIN td.grp_mem
	WHERE $cond;
	DETACH DATABASE td;
	EOF
  while IFS='|' read rowid hgname; do
    # echo rowid=$rowid - `echo "$hgname"|unhexize|htmlescape`
    htmlgn=`echo $hgname|unhexize|htmlescape`
    printf '<a href="%s?grp+%d">%s</a> ' "$cgi" "$rowid" "$htmlgn"
  done | {
    read ans
    if [ -n "$ans" ]; then
      w=`echo $wname|htmlescape`
      u="<a href=\"$cgi?grps\"><span class=\"pre\">$w</span></a>"
      cat<<-EOF
	GUIDE:<h2>`echo "$guide"|htmlescape`</h2>
	<tr>
	 <tr><td>$u $s</td>
	 <td>$ans</td>
	</dl>
	EOF
    fi
  }
}

peekgrpworlds() (
  # $1=(Pattern|mem:User)
  # err "pgw-1=[$1]"
  for wd in `worldnameDBlist`; do
    world=${wd%%:*}; wd=${wd#*:}
    worldname=${wd%:*}
    d=${wd#*:}
    if [ ! $db -ef $d -a -s $d ]; then
      grepgrpworld "$world" "$worldname" "$1" "$d"
    fi
  done | {
    result=`cat`
    if [ -n "$result" ]; then
      cat<<-EOF
	`echo "$result"|sed 's/^GUIDE://;2q'`
	<table class="b">
	 `echo "$result"|grep -v '^GUIDE:'`
	</table>
	EOF
    fi
  }
)