package datasql

const (
	SelectAllCommands   = "SELECT command_id, name FROM ea_rules_commands"
	SelectAllPrivileges = "SELECT privilege_id, name FROM ea_rules_privileges"

	SelectAllDefaultRules = `SELECT commands.name AS commandName,
					privileges.name AS privilegeName,
					commands.command_id,
					privileges.privilege_id
					FROM ea_rules_default rules
					JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
					JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id`
	SelectDefaultRulesByCommandName = `SELECT commands.name AS commandName,
					privileges.name AS privilegeName,
					commands.command_id,
					privileges.privilege_id
					FROM ea_rules_default rules
					JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
					JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
					WHERE commands.name = $1`
	SelectPrivilegeByName  = "SELECT privilege_id, name FROM ea_rules_privileges WHERE name = $1 LIMIT 1"
	SelectPrivilegesByName = "SELECT privilege_id, name FROM ea_rules_privileges WHERE name = ANY ($1);"
	SelectCommandByName    = "SELECT command_id, name FROM ea_rules_commands WHERE name = $1 LIMIT 1"
	SelectCommandsByName   = "SELECT command_id, name FROM ea_rules_commands WHERE name = ANY ($1);"
	InsertRuleDefault      = `INSERT INTO ea_rules_default (command_id, privilege_id) 
							SELECT command_id,privilege_id
							FROM ea_rules_commands comms, ea_rules_privileges privs
							WHERE comms.name = $1
							AND privs.name = $2
							ON CONFLICT DO NOTHING`
	InsertPrivilege   = `INSERT INTO ea_rules_privileges (name) VALUES ($1) ON CONFLICT DO NOTHING;`
	InsertCommand     = `INSERT INTO ea_rules_commands (name) VALUES ($1) ON CONFLICT DO NOTHING;`
	DeleteCommand     = "DELETE FROM ea_rules_commands WHERE name = $1"
	DeletePrivilege   = "DELETE FROM ea_rules_privileges WHERE name = $1"
	DeleteDefaultRule = `
	DELETE FROM ea_rules_default
	WHERE command_id IN
		(
			SELECT commands.command_id
			FROM ea_rules_commands commands
			WHERE commands.name = $1
		)
	AND privilege_id IN
		(
			SELECT privileges.privilege_id
			FROM ea_rules_privileges privileges
			WHERE privileges.name = $2
		)
	;`

	SelectBannerByName = "SELECT banner_edge_id, banner_name FROM banners WHERE banner_name = $1 LIMIT 1"

	DeletePrivilegeFromBannerRule = `
		DELETE FROM ea_rules
		WHERE banner_edge_id IN
			(
				SELECT banners.banner_edge_id
				from banners
				WHERE banners.banner_name = $1
			)
		AND command_id IN
			(
				SELECT commands.command_id
				FROM ea_rules_commands commands
				WHERE commands.name = $2
			)
		AND privilege_id IN
			(
				SELECT privileges.privilege_id
				FROM ea_rules_privileges privileges
				WHERE privileges.name = $3
			)
		;
		`

	SelectAllRulesForBanner = `SELECT commands.name AS commandName,
		privileges.name AS privilegeName,
		commands.command_id,
		privileges.privilege_id
		FROM ea_rules rules
		JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
		JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
		JOIN banners banners ON rules.banner_edge_id = banners.banner_edge_id
		WHERE banners.banner_name = $1
		;`

	SelectAllRulesFarAllBanners = `SELECT banners.banner_name AS bannerName,
		commands.name AS commandName,
		privileges.name AS privilegeName,
		banners.banner_edge_id,
		commands.command_id,
		privileges.privilege_id
		FROM ea_rules rules
		JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
		JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
		JOIN banners banners ON rules.banner_edge_id = banners.banner_edge_id
		;`

	SelectAllBannerRulesForCommand = `SELECT banners.banner_name AS bannerName,
		commands.name AS commandName,
		privileges.name AS privilegeName,
		banners.banner_edge_id,
		commands.command_id,
		privileges.privilege_id
		FROM ea_rules rules
		JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
		JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
		JOIN banners banners ON rules.banner_edge_id = banners.banner_edge_id
		WHERE commands.name = $1
		;`

	SelectBannerRulesForCommandAndBanner = `SELECT banners.banner_name AS bannerName,
		commands.name AS commandName,
		privileges.name AS privilegeName,
		banners.banner_edge_id,
		commands.command_id,
		privileges.privilege_id
		FROM ea_rules rules
		JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
		JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
		JOIN banners banners ON rules.banner_edge_id = banners.banner_edge_id
		WHERE banners.banner_name = $1
		AND commands.name = $2
		;`

	InsertBannerRule = `INSERT INTO ea_rules (banner_edge_id, command_id, privilege_id) 
		SELECT banner_edge_id,command_id,privilege_id
		FROM banners, ea_rules_commands comms, ea_rules_privileges privs
		WHERE
			banners.banner_name = $1
			AND comms.name = $2
			AND privs.name = $3
		ON CONFLICT DO NOTHING
		;`

	GetIDsForRuleSegment = `
		SELECT
			'command' as type, commands.command_id
			FROM ea_rules_commands commands
			WHERE commands.name = $1
		UNION ALL
		SELECT
			'privilege' as type, privileges.privilege_id
			FROM ea_rules_privileges privileges
			WHERE privileges.name = $2
		;`

	GetIDsForBannerSegment = `
		SELECT
			'banner' as type, banners.banner_edge_id as ID
			FROM banners
			WHERE banners.banner_name = $1
		UNION ALL
		SELECT
			'command' as type, commands.command_id
			FROM ea_rules_commands commands
			WHERE commands.name = $2
		UNION ALL
		SELECT
			'privilege' as type, privileges.privilege_id
			FROM ea_rules_privileges privileges
			WHERE privileges.name = $3
		;`
	SelectPrivNamesForCommandAndBanner = `
	SELECT 
		privileges.name AS privilegeName
	FROM ea_rules_default rules
	JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
	JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
	WHERE
		commands.name = $1
		AND commands.type = $2
	UNION
	SELECT
		privileges.name AS bannerPrivilegeName
	FROM ea_rules rules
	JOIN ea_rules_commands commands ON rules.command_id = commands.command_id
	JOIN ea_rules_privileges privileges ON rules.privilege_id = privileges.privilege_id
	JOIN banners banners ON rules.banner_edge_id = banners.banner_edge_id
	WHERE
		banners.banner_edge_id = $3
		AND commands.name = $1
		AND commands.type = $2
	;`
)