USE `vokesscp`;


##############################################################################################################################################################################################
# VIEWS for often used queries                                                                                                                                                               #
##############################################################################################################################################################################################


CREATE VIEW `VIEW_ipsInUse` AS
      SELECT
            `i`.`id`, `i`.`ip`, `i`.`useForApache`, `i`.`useForPostfix`, `i`.`useForMySQL`, `i`.`useForProFTPd`,
            IFNULL((
                  SELECT IF(COUNT(`av`.`id`) > 0, 'true', 'false')
                        FROM `apache_virtualhosts` AS `av`
                        RIGHT JOIN `customer_ips_to_accounts` AS `cita` ON `av`.`customerIpToAccountKey` = `cita`.`id`
                        RIGHT JOIN `ips_to_customers` AS `itc` ON `cita`.`ipToCustomerKey` = `itc`.`id`
                        WHERE `itc`.`ipKey` = `i`.`id`
                        GROUP BY `itc`.`ipKey`
            ), 'false') AS `inUseForApache`,
            IFNULL((
                  SELECT IF(COUNT(`ph`.`id`) > 0, 'true', 'false')
                        FROM `postfix_hostnames` AS `ph`
                        RIGHT JOIN `customer_ips_to_accounts` AS `cita` ON `ph`.`customerIpToAccountKey` = `cita`.`id`
                        RIGHT JOIN `ips_to_customers` AS `itc` ON `cita`.`ipToCustomerKey` = `itc`.`id`
                        WHERE `itc`.`ipKey` = `i`.`id`
                        GROUP BY `itc`.`ipKey`
            ), 'false') AS `inUseForPostfix`,
            IFNULL((
                  SELECT IF(COUNT(`md`.`id`) > 0, 'true', 'false')
                        FROM `mysql_domains` AS `md`
                        RIGHT JOIN `customer_ips_to_accounts` AS `cita` ON `md`.`customerIpToAccountKey` = `cita`.`id`
                        RIGHT JOIN `ips_to_customers` AS `itc` ON `cita`.`ipToCustomerKey` = `itc`.`id`
                        WHERE `itc`.`ipKey` = `i`.`id`
                        GROUP BY `itc`.`ipKey`
            ), 'false') AS `inUseForMySQL`,
            IFNULL((
                  SELECT IF(COUNT(`pv`.`id`) > 0, 'true', 'false')
                        FROM `proftpd_virtualhosts` AS `pv`
                        RIGHT JOIN `customer_ips_to_accounts` AS `cita` ON `pv`.`customerIpToAccountKey` = `cita`.`id`
                        RIGHT JOIN `ips_to_customers` AS `itc` ON `cita`.`ipToCustomerKey` = `itc`.`id`
                        WHERE `itc`.`ipKey` = `i`.`id`
                        GROUP BY `itc`.`ipKey`
            ), 'false') AS `inUseForProFTPd`
      FROM `ips` AS `i`;


CREATE VIEW `VIEW_customerIpFeatures` AS
      SELECT
            `itc`.`customerKey`, `itc`.`id` AS `ipToCustomerKey`, IFNULL(`itc`.`exclusiveToCustomer`, 'false'), `i`.`id` AS `ipId`, `i`.`ip`, `i`.`useForApache`, `i`.`useForPostfix`, `i`.`useForMySQL`, `i`.`useForProFTPd`
      FROM `ips` AS `i`
      INNER JOIN `ips_to_customers` AS `itc` ON `i`.`id` = `itc`.`ipKey`;


CREATE VIEW `VIEW_accountIpFeatures` AS
      SELECT
            `cita`.`accountKey`, `cita`.`id` AS `customerIpToAccountKey`, IFNULL(`cita`.`exclusiveToAccount`, 'false'), `itc`.`customerKey`, `itc`.`id` AS `ipToCustomerKey`, `itc`.`exclusiveToCustomer`, `i`.`id` AS `ipId`,
            `i`.`ip`, `i`.`useForApache`, `i`.`useForPostfix`, `i`.`useForMySQL`, `i`.`useForProFTPd`
      FROM `ips` AS `i`
      INNER JOIN `ips_to_customers` AS `itc` ON `i`.`id` = `itc`.`ipKey`
      INNER JOIN `customer_ips_to_accounts` AS `cita` ON `itc`.`id` = `cita`.`ipToCustomerKey`;