package main

import (
	"database/sql"
	"log"

	"github.com/alecthomas/kingpin"
	_ "github.com/go-sql-driver/mysql"
)

var (
	user = kingpin.Flag("user", "The database user").Short('u').Required().String()
	password = kingpin.Flag("password", "The database password").Short('p').Required().String()
	host = kingpin.Flag("host", "The database host").Short('h').Default("localhost").String()
	port = kingpin.Flag("port", "The database port").Default("3306").String()
	database = kingpin.Flag("database", "The database to connect to").Default("invisionapp").String()
	lockHistoryTable = kingpin.Flag("lockHistory", "The name of the lock history table").Default("lock_monitor").String()
	lockDuration = kingpin.Flag("lockDuration", "The lock duration to monitor for").Default("3").Int()
)

func main() {
	kingpin.Version("0.0.1")
	kingpin.Parse()

	db := connect()

	defer db.Close()

	log.Println("Attempting to obtain unique process lock")
	if (lockProcess(db)) {
		log.Println("success")
	} else {
		log.Fatal("failed to obtain process lock; another process is already running.")
	}

	if (lockTableExists(db)) {
		log.Println("History table already exists:", *lockHistoryTable)
	} else {
		log.Println("Creating history table:", *lockHistoryTable)
		createLockTable(db)
	}

	monitorLocks(db)
}

func connect() *sql.DB {
	var dburl string = *user + ":" + *password + "@tcp(" + *host + ":" + *port + ")/" + *database + "?autocommit=true"

	db, err := sql.Open("mysql", dburl)

	if err != nil {
		log.Fatal(err)
	}

	log.Print("connecting to [" + dburl + "]... ")
	err = db.Ping()

	if err != nil {
		log.Fatal(err)
	}

	log.Println("connected")

	return db
}

func lockProcess(db *sql.DB) bool {
	var lockResult int64 = 0

	stmt, err := db.Prepare("SELECT GET_LOCK(?, 10)");

	if err != nil {
		log.Fatal(err)
	}

	rows, err := stmt.Query(*lockHistoryTable)

	if err != nil {
		log.Fatal(err)
	}

	if rows.Next() {
		err = rows.Scan(&lockResult)

		if err != nil {
			log.Fatal(err)
		}
	}

	err = rows.Err()

	if err != nil {
		log.Fatal(err)
	}

	rows.Close()

	return lockResult == 1
}

func lockTableExists(db *sql.DB) bool {
	var tableCount int64 = 0

	stmt, err := db.Prepare(
		`SELECT
		 	COUNT(*)
		FROM
		    information_schema.tables
		WHERE
		    table_type = 'BASE TABLE'
			AND table_schema = DATABASE()
			AND table_name = ?`)

	if err != nil {
		log.Fatal(err)
	}

	rows, err := stmt.Query(*lockHistoryTable)

	if err != nil {
		log.Fatal(err)
	}

	if rows.Next() {
		err = rows.Scan(&tableCount)

		if err != nil {
			log.Fatal(err)
		}
	}

	err = rows.Err()

	if err != nil {
		log.Fatal(err)
	}

	rows.Close()

	return tableCount != 0
}

func createLockTable(db *sql.DB) {
	result, err := db.Exec(
		`CREATE TABLE ` + *lockHistoryTable + ` (
		    id int NOT NULL AUTO_INCREMENT,
		    requesting_trx_id VARCHAR(18) NOT NULL,
		    requesting_thread_id BIGINT(21) UNSIGNED NOT NULL,
		    requesting_trx_started DATETIME NOT NULL,
		    requesting_duration BIGINT(21) NOT NULL,
		    requesting_query VARCHAR(1024) NOT NULL,
		    blocking_trx_id VARCHAR(18) NOT NULL,
		    blocking_thread_id BIGINT(21) UNSIGNED NOT NULL,
		    blocking_trx_started DATETIME NOT NULL,
		    blocking_duration BIGINT(21) NOT NULL,
		    blocking_query VARCHAR(1024) NOT NULL,
		    PRIMARY KEY (id)
		)  ENGINE=INNODB DEFAULT CHARSET=UTF8;`)

	_ = result

	if err != nil {
		log.Fatal(err)
	}
}

func monitorLocks(db *sql.DB) {
	var monitorSql string =
	`SELECT
	    requesting_trx.trx_id requesting_trx_id,
	    requesting_trx.trx_mysql_thread_id requesting_thread_id,
	    requesting_trx.trx_started requesting_trx_started,
	    TIMESTAMPDIFF(SECOND, requesting_trx.trx_started, NOW()) requesting_duration,
	    requesting_trx.trx_query requesting_query,
	    blocking_trx.trx_id blocking_trx_id,
	    blocking_trx.trx_mysql_thread_id blocking_thread_id,
	    blocking_trx.trx_started blocking_trx_started,
	    TIMESTAMPDIFF(SECOND, blocking_trx.trx_started, NOW()) blocking_duration,
	    blocking_trx.trx_query blocking_query
	FROM
	    information_schema.innodb_lock_waits trx_waits
		JOIN
	    information_schema.innodb_trx requesting_trx ON trx_waits.requesting_trx_id = requesting_trx.trx_id
		JOIN
	    information_schema.innodb_trx blocking_trx ON trx_waits.blocking_trx_id = blocking_trx.trx_id
	HAVING requesting_duration >= ?;`

	_ = monitorSql
}