#!/bin/sh
# s4 - migration module
# (C)2020 by HIROSE, Yuuji

srcdb=`unset DB; [ -f $1 ] && . ./$1 && echo ${DB:-db/cgi.sq3}`
dst=`unset DB; [ -f $2 ] && . ./$2 && echo "${DB:-db/cgi.sq3}|$URL"`
dstdb=${dst%\|*}
dsturl=${dst##*\|}
type htmlescape >/dev/null 2>&1 || DB=$srcdb . `dirname $1`/s4-funcs.sh
case "$2" in
  s4-config.sh) world=Base ;;
  *)		world=${2##*-config-}; world=${world%.*} ;;
esac
htmlworld=`echo "$world"|htmlescape`

err "--- Migration Started with \$1=$1 \$2=$2 at `date` ---"
err srcdb=$srcdb dstdb=$dstdb
err URL=$URL
err dstURL=$dsturl

shift 2

query "ATTACH DATABASE \"$dstdb\" AS dst;" || abort "Cannot attach db #{dstdb}"

if [ -z "$tmpd" ]; then
  tmpd=`tmpd=$tmpdir mktempd`	# If called from command line
  if [ -z "$tmpd" -o ! -w "$tmpd" ]; then
    err "-- Cannot detect temporary directory [$tmpd] --"
    exit 2
  fi
  trap "rm -r $tmpd" EXIT INT HUP
fi
failure=0
faillist=""
for grid; do
  grid=$((0 + $grid))
  gnamesql="(SELECT gname FROM main.grp WHERE rowid=$grid)"
  grp=`query "SELECT gname FROM main.grp WHERE rowid=$grid;"`
  if [ -z "$grp" ]; then
    echo "ID=$grid not found, skipped" | html p
    continue
  fi
  htmlgrp=`echo "$grp"|htmlescape`
  qgrp=`sqlquote "$grp"`
  if [ -n "`query \"SELECT gname FROM dst.grp WHERE gname=$qgrp;\"`" ]; then
    echo "[$htmlgrp]グループがWorld[$htmlworld]にあるので中止します。"|html p
    failure=$((failure+1))
    continue
  fi
  echo "Copying $grid..."
  query "BEGIN;"
  query "REPLACE INTO dst.grp SELECT * FROM main.grp WHERE rowid=$grid;"
  destrowid=`query "SELECT last_insert_rowid();"`
  for tbl in grp_s grp_m grp_mem grp_mem_s grp_mem_m \
		   grp_adm grp_adm_s grp_adm_m; do
    query "REPLACE INTO dst.$tbl SELECT * FROM main.$tbl
	   WHERE gname=$gnamesql;"
  done
  blogs=`query "SELECT group_concat(\"'\"||id||\"'\", ',')
  	        FROM main.blog_s WHERE key='owner' AND val=$gnamesql;"`
  echo blogs=$blogs
  for tbl in blog blog_s blog_m; do
    query <<-EOF
	REPLACE INTO dst.$tbl
	  SELECT * FROM main.$tbl
	  WHERE id IN ($blogs);
	EOF
  done
  for tbl in article article_s article_m; do
    query <<-EOF
	REPLACE INTO dst.$tbl
	  SELECT * FROM main.$tbl
	  WHERE id IN (SELECT id FROM main.article WHERE blogid IN ($blogs))
	  ORDER BY rowid;
	EOF
  done
  ## Check the equality of two DBs
  echo "grid=$grid grp=$grp qgrp=$qgrp" | htmlescape
  # grp
  d1=$(query <<-EOF
	SELECT * FROM main.grp
	       	 NATURAL LEFT JOIN main.grp_s
		 NATURAL LEFT JOIN main.grp_m
	WHERE gname=$qgrp
	EXCEPT
	SELECT * FROM dst.grp
	       	 NATURAL LEFT JOIN dst.grp_s
		 NATURAL LEFT JOIN dst.grp_m
	WHERE gname=$qgrp;
	EOF
	)
  err DONE 
  err d1="$d1"
  # blog
  d2=$(query <<-EOF
	SELECT * FROM main.blog
	       	 NATURAL LEFT JOIN main.blog_s
		 NATURAL LEFT JOIN main.blog_m
	WHERE id IN (SELECT id FROM main.blog_s
	      	     WHERE key='owner' AND val=$qgrp)
	EXCEPT
	SELECT * FROM dst.blog
	       	 NATURAL LEFT JOIN dst.blog_s
		 NATURAL LEFT JOIN dst.blog_m
	WHERE id IN (SELECT id FROM dst.blog_s
	      	     WHERE key='owner' AND val=$qgrp);
	EOF
    )
  err d2="$d2"
  # article
  d3=$(query <<-EOF
	SELECT * FROM main.article
	       	 NATURAL LEFT JOIN main.article_s
	       	 NATURAL LEFT JOIN main.article_m
	WHERE blogid IN ($blogs)
	EXCEPT
	SELECT * FROM dst.article
	       	 NATURAL LEFT JOIN dst.article_s
	       	 NATURAL LEFT JOIN dst.article_m
	WHERE blogid IN ($blogs);
	EOF
       )
  err d3="$d3"
  if [ -z "$d1$d2$d3" ]; then
    echo "Copy and verification done, rewriting URL in articles..."
    echo "Old URL: $URL"
    echo "New URL: $dsturl"
    query <<-EOF
	UPDATE dst.article_s
	SET val=replace(val,
			'${URL}?grp+$grid',
			'${dsturl}?grp+$destrowid')
	WHERE key='text' AND val LIKE '%${URL}%';
	EOF
    # Create blog-rowid conversion table
    sedfile=$tmpd/arttrans.sed
    # sedfile=tmp/arttrans.sed
    query <<-EOF > $sedfile
	WITH arttrans AS (
	  SELECT s.rowid srcrid, d.rowid dstrid
	  FROM main.article s JOIN dst.article d ON s.id=d.id
	  WHERE s.id in (SELECT id
	  	     	 FROM article WHERE blogid IN ($blogs))
	) SELECT printf("/^>/s/\#%s($|[^0-9])/\#%s\1/g", srcrid, dstrid)
	  FROM arttrans;
	EOF
    query <<-EOF > $tmpd/repl.art.rowid
	SELECT rowid FROM dst.article_s
	WHERE key='text' AND val GLOB '>*#[1-9]*'
	  AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
	EOF
    sql=$tmpd/update.sql
    for arid in `cat $tmpd/repl.art.rowid`; do
      newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
              | unhexize | sed -Ef "$sedfile" | hexize`
      echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;"
    done >$sql
    # Rewrite blog-links in the group
    #  Create sed script
    sedfile2=${sedfile}2
    query <<-EOF > $sedfile2
	WITH blogtrans AS (
	  SELECT s.rowid srcrid, d.rowid dstrid
	  FROM main.blog s JOIN dst.blog d ON s.id=d.id
	  WHERE s.id IN ($blogs)
	) SELECT printf('s/(\?replyblog)\+%s($|[^0-9])/\1+%s\2/g',
		         srcrid, dstrid)
	  FROM blogtrans;
	EOF
    bloglinks=$tmpd/bloglinks.rowid
    query <<-EOF > $bloglinks
	SELECT rowid FROM dst.article_s
	WHERE key='text' AND val LIKE '%?replyblog+%'
	  AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
	EOF
    for arid in `cat $bloglinks`; do
      newval=`query "SELECT hex(replace(val, '$URL', '$dsturl')) 
      		     FROM dst.article_s WHERE rowid=$arid;" \
              | unhexize | sed -Ef "$sedfile2" | hexize`
      echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;" >>$sql
    done
    # Rewrite showattc Direct-Links
    attclinks=$(
      query <<-EOF
	SELECT group_concat(rowid, ',') FROM dst.article_s
	WHERE key='text' AND val LIKE '%?showattc+article_m+%'
	  AND id IN (SELECT id FROM article WHERE blogid IN ($blogs));
	EOF
      )
    if [ -n "$attclinks" ]; then
      err attclinks=$attclinks
      sedfile3=${sedfile}3
      query <<-EOF > $sedfile3
	WITH attctrans AS (
	  SELECT s.rowid srcarid, d.rowid dstarid
	  FROM main.article_m s NATURAL JOIN dst.article_m d
	  WHERE d.id IN (SELECT id FROM dst.article
	  	     	 WHERE blogid IN ($blogs))
	) SELECT printf('s/(\?showattc\+article_m)\+%s($|[^0-9])/\1+%s\2/g',
			 srcarid, dstarid)
	  FROM attctrans;
	EOF
      echo $attclinks | tr , '\n' | while read arid; do
	newval=`query "SELECT hex(val) FROM dst.article_s WHERE rowid=$arid;" \
              | unhexize | sed -Ef "$sedfile3" | hexize`
	echo "UPDATE dst.article_s SET val=X'$newval' WHERE rowid=$arid;"
      done >> $sql
    fi
    if [ -z "`query \".read $sql\"`" ]; then
      echo "URL rewriting done."
      echo -n "Removing the group in the old world..."
      query <<-EOF
	SAVEPOINT rmgroup;
	DELETE FROM main.article WHERE blogid IN ($blogs);
	DELETE FROM main.blog WHERE id IN ($blogs);
	DELETE FROM main.grp WHERE rowid=$grid;
	RELEASE SAVEPOINT rmgroup;
	EOF
      s=`query "SELECT * FROM main.grp WHERE rowid=$grid;"`
      if [ -z "$s" ]; then
	echo "Done."
	echo "Success!!"
	query "END;"
	clean_orphaned
	echo "Done."
      else
	echo Removal failed
	echo "現行グループ消去ができませんでした。"
	echo "書き込みの多いグループの場合は空いている時間帯に試して下さい。"
	query "ROLLBACK;"
	failure=$((failure+1))
	faillist="$faillist${faillist:+ }$grid"
      fi
    else
      failure==$((failure+1))
      echo "Replacing failed."
      query "ROLLBACK;"
    fi
  else
    failure=$((failure + 1))
    echo "Fail!"
    query "ROLLBACK;"
  fi
done

err "Migration ended at `date` with failure=$failure"
tty >/dev/null 2>&1 && test -n "$faillist" && echo "Skipped: $faillist"
return $failure