#!/bin/sh

if [ $# -eq 0 ]; then
  echo "usage: $(basename $0) <db file> \"YYYY-MM-DD HH:MM\""
  exit 1
fi

db=$1
sqlite=sqlite3

# timestamp
date=$(date +%s --date="$2")

echo "${sqlite} ${db} \"DELETE FROM Events WHERE storage_time<'$date';\""
${sqlite} ${db} "DELETE FROM Events WHERE storage_time<'$date';"

echo "${sqlite} ${db} \"SELECT event_id FROM GroupCache;\""
ids=$(${sqlite} ${db} "SELECT event_id FROM GroupCache;")

for id in $ids; do
  echo "${sqlite} ${db} \"SELECT remote_uid FROM Events WHERE id='$id';\""
  number=$(${sqlite} ${db} "SELECT remote_uid FROM Events WHERE id='$id';")

  echo "${sqlite} ${db} \"SELECT COUNT(id) FROM Events WHERE remote_uid='$number';\""
  total_events=$(${sqlite} ${db} "SELECT COUNT(id) FROM Events WHERE remote_uid='$number';")

  echo "${sqlite} ${db} \"UPDATE GroupCache SET total_events='$total_events' WHERE event_id='$id';\""
  ${sqlite} ${db} "UPDATE GroupCache SET total_events='$total_events' WHERE event_id='$id';"

  echo "${sqlite} ${db} \"UPDATE GroupCache SET read_events='$total_events' WHERE event_id='$id';\""
  ${sqlite} ${db} "UPDATE GroupCache SET read_events='$total_events' WHERE event_id='$id';"
done

exit 0