package db

type transactionName int

const (
	CreateUser transactionName = iota
	CreateUserSecret
	CreateTag
	CreateRole
	RemoveUser
	RemoveRole
	RemoveUnusedTags
	AssignRoleToUser
	AssignTagToTask
	RemoveRoleFromUser
	RemoveTagFromTask
	UpdateSetting
	GetSingleUser
	GetAllUsers
	GetSingleTask
	GetAllTasks
	GetSingleUserWithSecretAndRoles
	GetAllTagsRelatedToTask
)

var commonTransactions = [...]struct {
	Name transactionName
	Cmd  string
}{
	{ // Create a user (including salted secret)
		Name: CreateUser,
		Cmd:  "INSERT INTO Users (userID, name, email) VALUES (?, ?, ?)",
	},
	{ // Create user secrets
		Name: CreateUserSecret,
		Cmd:  "INSERT INTO UserSecrets (userID, saltAndHash) VALUES (?, ?)",
	},
	{ // Create a tag
		Name: CreateTag,
		Cmd:  "INSERT INTO Tags (name) VALUES (?)",
	},
	{ // Create a role
		Name: CreateRole,
		Cmd:  "INSERT INTO Roles (role) VALUES (?)",
	},
	{ // Remove a user
		Name: RemoveUser,
		Cmd:  "DELETE FROM Users WHERE userID = ?",
	},
	{ // Remove a role
		Name: RemoveRole,
		Cmd:  "DELETE FROM Roles WHERE role = ?",
	},
	{ // Remove unused tags (assigned to no tasks)
		Name: RemoveUnusedTags,
		Cmd:  "DELETE FROM Tags WHERE tagID NOT IN (SELECT tagID FROM TaskTags)",
	},
	{ // Assign a new role to a user
		Name: AssignRoleToUser,
		Cmd:  "INSERT INTO UserRoles (userID, role) VALUES (?, ?)",
	},
	{ // Assign a new tag to a task
		Name: AssignTagToTask,
		Cmd:  "INSERT INTO TaskTags (taskID, tagID) VALUES (?, ?)",
	},
	{ // Remove a role from a user
		Name: RemoveRoleFromUser,
		Cmd:  "DELETE FROM UserRoles WHERE userID = ? AND role = ?",
	},
	{ // Remove a tag from a task
		Name: RemoveTagFromTask,
		Cmd:  "DELETE FROM TaskTags WHERE taskID = ? AND tagID = ?",
	},
	{ // Update a setting KeyPair
		Name: UpdateSetting,
		Cmd:  "UPDATE Settings SET value = ? WHERE key = ?",
	},
	{ // Get a single user
		Name: GetSingleUser,
		Cmd:  "SELECT * FROM Users WHERE userID = ?",
	},
	{ // Get all users
		Name: GetAllUsers,
		Cmd:  "SELECT * FROM Users",
	},
	{ // Get a single task
		Name: GetSingleTask,
		Cmd:  "SELECT * FROM Tasks WHERE taskID = ?",
	},
	{ // Get all tasks
		Name: GetAllTasks,
		Cmd:  "SELECT * FROM Tasks",
	},
	{ // Get a single user with secret info and roles
		Name: GetSingleUserWithSecretAndRoles,
		Cmd: `SELECT u.*, us.saltAndHash, ur.role
				FROM Users u
				JOIN UserSecrets us ON u.userID = us.userID
				LEFT JOIN UserRoles ur ON u.userID = ur.userID
				WHERE u.userID = ?`,
	},
	{ // Get all tags related to a task
		Name: GetAllTagsRelatedToTask,
		Cmd: `SELECT t.* FROM Tags t
				JOIN TaskTags tt ON t.tagID = tt.tagID
				WHERE tt.taskID = ?`,
	},
}