-- CREATE TABLES IN postfix.sql FIRST 
 
-- TEST domain: expect `1 = 1`
SELECT 1 FROM `mailserver`.`virtual_domains` 
WHERE `virtual_domains`.`name`='domain.tld';

-- TEST user: expext `1 = 1`
SELECT 1 FROM `mailserver`.`virtual_users`
INNER JOIN `mailserver`.`virtual_domains` 
	ON `virtual_domains`.`id` = `virtual_users`.`id` 
	AND `virtual_domains`.`name` = SUBSTRING_INDEX('user@domain.tld', '@', -1)
WHERE `virtual_users`.`username` = SUBSTRING_INDEX('user@domain.tld', '@', 1);

-- TEST alias: expext `destination = user@domain.tld`
SELECT CONCAT(`virtual_users`.`username`, '@', `virtual_domains`.`name`) as destination 
FROM `mailserver`.`virtual_aliases`
INNER JOIN `mailserver`.`virtual_users` 
	ON `virtual_users`.`id` = `virtual_aliases`.`user_id`
INNER JOIN `mailserver`.`virtual_domains` 
	ON `virtual_domains`.`id` = `virtual_users`.`domain_id` 
	AND `virtual_domains`.`name` = SUBSTRING_INDEX('alias@domain.tld', '@', -1) 
WHERE `virtual_aliases`.`alias` = SUBSTRING_INDEX('alias@domain.tld', '@', 1);

-- TEST email 2 email: expect `email = user@domain.tld`
SELECT CONCAT(`virtual_users`.`username`, '@', `virtual_domains`.`name`) as email 
FROM `mailserver`.`virtual_users`
INNER JOIN `mailserver`.`virtual_domains` 
	ON `virtual_domains`.`id` = `virtual_users`.`domain_id` 
	AND `virtual_domains`.`name` = SUBSTRING_INDEX('user@domain.tld', '@', -1) 
WHERE `virtual_users`.`username` = SUBSTRING_INDEX('user@domain.tld', '@', 1);