/**
	This tag will take the provided database and all it's tables and grant the
	provided group access to both the database and it's tables. Will return true
	if it was successful in performing the operation.

	AssignDatabaseToGroup('database name', 'group name');

	@param	string	#database the name of the database you wish to grant access to
	@param	string	#group	the name of the group you wish to allow access to the database
	@return	always returns true unless an error was thrown
	@author	Adam Randall
	@version	1.0.0
*/
define_tag('AssignDatabaseToGroup',
	-namespace = '_global_Admin_',
	-required  = 'database',
	-required  = 'group');

	admin_reloaddatasource('MySQLDS');

	fail_if(!admin_currentgroups->find('ADMINISTRATORS')->size, -9960, 'Must be run as the global admin');
	fail_if(!#database->size, -9956, 'Invalid Database Name');
	fail_if(!#group->size,    -9956, 'Invalid Group Name');

	local(
		'i',
		'id_host'        = 0,
		'id_db'          = 0,
		'id_group'       = 0,
		'ids_table'      = array);

	inline(-database = 'lasso_internal');

		// fine the ID of the specified database name or database alias
		inline(
			-log      = 'none',
			-table    = 'security_datasource_databases',
			-sql      = '\
				select \
					id, \
					id_host
				from \
					security_datasource_databases \
				where \
					(\
						name like \'' encode_sql92(#database) '\' and \
						(alias = \'\' or alias is null)\
					) or \
					alias like \'' encode_sql92(#database) '\'');
			if(found_count);
				#id_db   = integer(field('id'));
				#id_host = integer(field('id_host'));
			/if;
		/inline;

		// check to see if we found the database
		fail_if(#id_db <= 0, -9956, 'The specified database name/alias was not found');

		// find the ID of the specified group
		inline(
			-log      = 'none',
			-table    = 'security_groups',
			-sql      = '\
				select \
					id \
				from \
					security_groups \
				where \
					name like \'' encode_sql92(#group) '\'');
			found_count ? #id_group = integer(field('id'));
		/inline;

		// check to see if we found the group
		fail_if(#id_group <= 0, -9956, 'The specified group was not found');

		// find the group host permissions
		inline(
			-log   = 'none',
			-table = 'security_group_host_map',
			-sql   = 'select * from security_group_host_map where id_group = ' #id_group ' and id_host = ' #id_host);
			// if found, add Q to the allow field
			if(found_count);
				if(field('allow') !>> 'Q');
					inline(
						-log = 'none',
						-sql = 'update security_group_host_map set allow = ' (field('allow')->size ? ',Q' | 'Q') ' where id = ' field('id'));
						fail_if(error_code != 0, error_code, 'Updating Host Group Permissions: ' error_msg);
					/inline;
				/if;
			else;
				// if not found, add the new host record with Q in the Allow field
				inline(
					-log = 'none',
					-sql = 'insert into security_group_host_map values (null, ' #id_host ', ' #id_group ', Q, null)');
					fail_if(error_code != 0, error_code, 'Inserting Host Group Permissions: ' error_msg);
				/inline;
			/if;
		/inline;

		// delete existing group db permissions
		inline(
			-log      = 'none',
			-sql      = 'delete from security_group_db_map where id_group = ' #id_group ' and id_database = ' #id_db);
			fail_if(error_code != 0, error_code, 'Deleting DB Group Permissions: ' error_msg);
		/inline;

		// insert new group db permissions
		inline(
			-log      = 'none',
			-table    = 'security_group_db_map',
			-sql      = 'insert into security_group_db_map values (null, ' #id_db ', ' #id_group ', \'I,S,A,U,D,X,Q\', null, null)');
			fail_if(error_code != 0, error_code, 'Insert DB Group Permissions: ' error_msg);
		/inline;

		// find all existing group table ids that are bound to our db id
		inline(
			-log        = 'none',
			-table      = 'security_group_table_map',
			-sql        = '\
				select \
					a.id as id, \
					b.id as id_table \
				from \
					security_group_table_map as a \
					left join security_database_tables as b on (a.id_table = b.id) \
				where \
					a.id_group = ' #id_group ' and \
					b.id_database = ' #id_db,
			-maxrecords = 'all');
			records;
				#ids_table->insert(field('id'));
			/records;
		/inline;

		// delete found group table ids
		if(#ids_table->size);
			inline(
				-log      = 'none',
				-sql      = 'delete from security_group_table_map where id in (' #ids_table->join(',') ')');
				fail_if(error_code != 0, error_code, 'Deleting Table Group Permissions: ' error_msg);
			/inline;
		/if;

		// find all table ids
		inline(
			-log        = 'none',
			-table      = 'security_database_tables',
			-sql        = 'select id from security_database_tables where id_database = ' #id_db,
			-maxrecords = 'all');
			records;
				// add the new table permissions
				inline(
					-log      = 'none',
					-table    = 'security_group_table_map',
					-sql      = 'insert into security_group_table_map values (null, ' field('id') ', ' #id_group ', \'I,S,A,U,D,Q\', null, null)');
					fail_if(error_code != 0, error_code, 'Insert Table Group Permissions: ' error_msg);
				/inline;
			/records;
		/inline;

	/inline;

	admin_refreshsecurity;

	return(true);

/define_tag;