#!/bin/bash

function initialize-schema() {
cat - <<EOF
DROP DATABASE IF EXISTS memory1;
CREATE DATABASE memory1; 
USE memory1;
CREATE TABLE \`sys_rollback_context\` (
  \`from_version\` varchar(100) DEFAULT NULL,
  \`to_version\` varchar(100) DEFAULT NULL,
  \`type\` varchar(40) DEFAULT NULL,
  \`state\` varchar(40) DEFAULT NULL,
  \`error\` mediumtext,
  \`sys_id\` char(32) NOT NULL DEFAULT '',
  \`sys_updated_by\` varchar(40) DEFAULT NULL,
  \`sys_updated_on\` datetime DEFAULT NULL,
  \`sys_created_by\` varchar(40) DEFAULT NULL,
  \`sys_created_on\` datetime DEFAULT NULL,
  \`sys_mod_count\` int(11) DEFAULT NULL,
  PRIMARY KEY (\`sys_id\`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE \`sys_rollback\` (
  \`context\` varchar(32) DEFAULT NULL,
  \`table_name\` varchar(80) DEFAULT NULL,
  \`storage_table_name\` varchar(30) DEFAULT NULL,
  \`element_name\` varchar(80) DEFAULT NULL,
  \`storage_alias\` varchar(30) DEFAULT NULL,
  \`document_id\` char(32) DEFAULT NULL,
  \`number\` int(11) NOT NULL AUTO_INCREMENT,
  \`operation\` varchar(40) DEFAULT NULL,
  \`txn_id\` varchar(32) DEFAULT NULL,
  \`oldvalue\` mediumtext,
  \`newvalue\` mediumtext,
  \`row_mod_count\` int(11) DEFAULT NULL,
  \`when\` datetime DEFAULT NULL,
  PRIMARY KEY (\`number\`),
  KEY \`number\` (\`number\`),
  KEY \`context\` (\`context\`),
  KEY \`document_id\` (\`document_id\`)
) ENGINE=InnoDB AUTO_INCREMENT=7001 DEFAULT CHARSET=utf8;
EOF
}

function use-memory1() {
    cat - <<-EOF
	USE memory1;
	EOF
}

function create-table() {
    local i=${1:-create-table N}
    printf "create-table %d\n" ${i} >&2
    cat - <<-EOF
	CREATE TABLE IF NOT EXISTS \`asdf${i}\` (
	\`pk_id\` int AUTO_INCREMENT PRIMARY KEY,
	\`value\` varchar(40) DEFAULT NULL,
	\`sys_id\` char(32) NOT NULL DEFAULT '',
	\`sys_updated_by\` varchar(40) DEFAULT NULL,
	\`sys_updated_on\` datetime DEFAULT NULL,
	\`sys_created_by\` varchar(40) DEFAULT NULL,
	\`sys_created_on\` datetime DEFAULT NULL,
	\`sys_mod_count\` int(11) DEFAULT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	EOF
}

function create-insert-trigger() {
    local i=${1:-create-insert-trigger N}
    printf "create-insert-trigger %d\n" ${i} >&2
    cat - <<-EOF
	DELIMITER //
	CREATE TRIGGER sn\$aftins\$rbk\$asdf${i} AFTER INSERT ON asdf${i}
	FOR EACH ROW
	BEGIN
	      DECLARE txnId VARCHAR(32);
	      SET txnId = REPLACE(UUID(), "-", "");
	      INSERT INTO sys_rollback (context, operation, table_name, storage_table_name, element_name, storage_alias, document_id, txn_id, oldvalue, newvalue, row_mod_count, \`when\`) VALUES (null, 'INSERT', 'asdf0', 'asdf0', 'value', 'value', NEW.sys_id, txnId, NULL, NEW.value, NEW.sys_mod_count, NOW());
	      INSERT INTO sys_rollback (context, operation, table_name, storage_table_name, element_name, storage_alias, document_id, txn_id, oldvalue, newvalue, row_mod_count, \`when\`) VALUES (null, 'INSERT', 'asdf0', 'asdf0', 'sys_updated_on', 'sys_updated_on', NEW.sys_id, txnId, NULL, NEW.sys_updated_on, NEW.sys_mod_count, NOW());
	      INSERT INTO sys_rollback (context, operation, table_name, storage_table_name, element_name, storage_alias, document_id, txn_id, oldvalue, newvalue, row_mod_count, \`when\`) VALUES (null, 'INSERT', 'asdf0', 'asdf0', 'sys_created_on', 'sys_created_on', NEW.sys_id, txnId, NULL, NEW.sys_created_on, NEW.sys_mod_count, NOW());
	      INSERT INTO sys_rollback (context, operation, table_name, storage_table_name, element_name, storage_alias, document_id, txn_id, oldvalue, newvalue, row_mod_count, \`when\`) VALUES (null, 'INSERT', 'asdf0', 'asdf0', 'sys_id', 'sys_id', NEW.sys_id, txnId, NULL, NEW.sys_id, NEW.sys_mod_count, NOW());
	      INSERT INTO sys_rollback (context, operation, table_name, storage_table_name, element_name, storage_alias, document_id, txn_id, oldvalue, newvalue, row_mod_count, \`when\`) VALUES (null, 'INSERT', 'asdf0', 'asdf0', 'sys_mod_count', 'sys_mod_count', NEW.sys_id, txnId, NULL, NEW.sys_mod_count, NEW.sys_mod_count, NOW());
	      INSERT INTO sys_rollback (context, operation, table_name, storage_table_name, element_name, storage_alias, document_id, txn_id, oldvalue, newvalue, row_mod_count, \`when\`) VALUES (null, 'INSERT', 'asdf0', 'asdf0', 'sys_updated_by', 'sys_updated_by', NEW.sys_id, txnId, NULL, NEW.sys_updated_by, NEW.sys_mod_count, NOW());
	      INSERT INTO sys_rollback (context, operation, table_name, storage_table_name, element_name, storage_alias, document_id, txn_id, oldvalue, newvalue, row_mod_count, \`when\`) VALUES (null, 'INSERT', 'asdf0', 'asdf0', 'sys_created_by', 'sys_created_by', NEW.sys_id, txnId, NULL, NEW.sys_created_by, NEW.sys_mod_count, NOW());
	END;//
	DELIMITER ;
	EOF
}

function drop-insert-trigger() {
    local i=${1:-drop-insert-trigger N}
    printf "drop-insert-trigger %d\n" ${i} >&2
    cat - <<-EOF
	DROP TRIGGER IF EXISTS sn\$aftins\$rbk\$asdf${i};
	EOF
}

# function start-rollback-context() {
#     printf "start-rollback-context\n"
#     cat - <<-EOF
# 	SET @rollbackContext = REPLACE(UUID(), "-", "");
# 	INSERT INTO sys_rollback_context (\`sys_id\`, \`sys_updated_by\`, \`sys_created_on\`, \`sys_mod_count\`, \`sys_updated_on\`, \`state\`, \`to_version\`, \`type\`, \`from_version\`, \`sys_created_by\`) VALUES(@rollbackContext, 'admin', '2016-01-11 23:47:18', 0, '2016-01-11 23:47:18', 'staged', '2016-01-11 23:47:17 UTC', 'upgrade', 'glide-.zip', 'admin');
# 	COMMIT;
# 	EOF
# }

# function end-rollback-context() {
#     printf "end-rollback-context\n" >&2
#     cat - <<-EOF
# 	SET @rollbackContext = NULL;
# 	EOF
# }

function insert-tuple() {
    local i=${1:-insert-tuple N}
    # printf "insert-tuple %d\n" ${i} >&2
    cat - <<-EOF
	INSERT INTO asdf${i} (\`sys_id\`, \`sys_updated_by\`, \`sys_created_on\`, \`sys_mod_count\`, \`sys_updated_on\`, \`value\`, \`sys_created_by\`) VALUES (REPLACE(UUID(), "-", ""), 'admin', '2016-01-11 23:47:19', 0, '2016-01-11 23:47:19', '1', 'admin');
	EOF

}

INITIALIZE=0
RUN=0
CREATE=0
DROP=0

for i in "$@"
do
case $i in
    -i)
    INITIALIZE=1
    shift
    ;;
    -r)
    RUN=1
	shift
    ;;
	-c)
    CREATE=1
	shift
    ;;
    -d)
    DROP=1
	shift
    ;;
    *)
    # unknown option
	printf "Unknown option %s\n" $i >&2
    ;;
esac
done

echo "Options:" >&2
echo "Initialize: $INITIALIZE" >&2
echo "Run: $RUN" >&2
echo "Create: $CREATE" >&2
echo "Drop: $DROP" >&2

if [ $INITIALIZE -ne "0" ]
then
	initialize-schema

	use-memory1
fi

if [ $CREATE -ne "0" ]
then
	use-memory1
	
	for (( i=0 ; i < 10000 ; ++i ))
	do
		create-table ${i}
	done

	for (( i=0 ; i < 10000 ; ++i ))
	do
		create-insert-trigger ${i}
	done
fi

if [ $RUN -ne "0" ]
then
	use-memory1
	
	for (( i=0 ; i < 10000 ; ++i ))
	do
		insert-tuple ${i}
	done
fi

if [ $DROP -ne "0" ]
then
	use-memory1
	
	for (( i=0 ; i < 10000 ; ++i ))
	do
		drop-insert-trigger ${i}
	done

	cat - <<-EOF
	DROP DATABASE memory1;
	EOF
fi