DELIMITER $$

DROP PROCEDURE IF EXISTS `subs`.`HHS`$$
CREATE PROCEDURE `subs`.`HHS` (IN do VARCHAR(50),IN tc VARCHAR(50), IN sr VARCHAR(50))
BEGIN
SET @base = (select Base from `subs`.`version`);
SET @name = (select Name from `subs`.`version`);


 IF tc RLIKE '.*Public.*' AND sr RLIKE '.*Any.*' THEN
SELECT
CASE
  WHEN a.URI='data.gov/539/' THEN 'top'
  ELSE 'sub' END AS "Zone",
CASE
  WHEN a.URI='data.gov/539/222/.*' THEN concat(do,' ',@name) 
  WHEN a.Name RLIKE '.*Public Lands.*' THEN concat(do,' ',@name)
  ELSE concat(a.Name, ' Region') END AS "Stewardship",
concat(@base,a.URI) AS "Key",
CASE
  WHEN a.URI='data.gov/539/222/.*' THEN 'Federal Territory' 
  WHEN a.Name RLIKE '.*Public Lands.*' THEN left(a.Name,locate(' Public',a.Name))
  ELSE a.Name END AS "Value",
count(DISTINCT b.URI) AS "SubRegionCount"
FROM state_box a, county_box b
WHERE locate(a.URI,b.URI) >0
GROUP BY Stewardship,Value
Having Stewardship=concat(do,' ',@name)
UNION
SELECT
'top' AS "Zone",
'United States' AS "Stewardship",
concat(@base,URI) AS "Key",
'United States Federal Territory' AS "Value",
1 AS "SubRegionCount"
FROM state_box
WHERE URI='data.gov/539/'
UNION
SELECT
'sub' AS "Zone",
concat(do,' ',@name) AS "Stewardship",
concat(@base,URI) AS "Key",
Name AS "Region",
1 AS "SubRegionCount"
FROM state_box
WHERE Name RLIKE '.*District of Columbia.*'
UNION
SELECT
'sub' AS "Zone",
concat(do,' ',@name) AS "Stewardship",
concat(@base,'data.gov/539/491/533/2/') AS "Key",
'Wildlife Refuges Pacific Ocean' AS "Value",
8 AS "SubRegionCount"
FROM state_box
UNION
SELECT
'sub' AS "Zone",
concat(do,' ',@name) AS "Stewardship",
concat(@base,'data.gov/539/491/533/1/') AS "Key",
'Wildlife Refuges Caribbean Sea Atlantic Ocean' AS "Value",
1 AS "SubRegionCount"
FROM state_box
ORDER BY Value;
ELSEIF tc RLIKE '.*Private.*' AND sr RLIKE '.*Any.*' THEN
SELECT
CASE
  WHEN a.URI='data.gov/539/' THEN 'top'
  ELSE 'sub' END AS "Zone",
concat(@base,a.URI) AS "Key",
CASE
  WHEN a.URI='data.gov/539/' THEN 'United States of America' 
  WHEN a.Name RLIKE '.*Wildlife.*' THEN 'United States' 
  WHEN a.Name RLIKE '.*Public Lands.*' THEN 'United States'
  ELSE a.Name END AS "Value",
CASE
   WHEN count(DISTINCT b.URI) =1 THEN 1
   ELSE (count(DISTINCT b.URI) DIV 2) END AS "SubRegionCount",
CASE 
  WHEN a.URI='data.gov/539/' THEN 'United States Private Holdings'
  WHEN a.Name RLIKE '.*District of Columbia.*' THEN 'District of Columbia Private Holdings'
  WHEN a.Name RLIKE '.*Public Lands.*' THEN left(a.Name,locate(' Public',a.Name))
  ELSE a.Name END AS "PrivateHoldings"
FROM state_box a, county_box b
WHERE locate(a.URI,b.URI) >0
GROUP BY PrivateHoldings
Having Value!='United States'
ORDER BY PrivateHoldings;

ELSEIF sr NOT RLIKE '.*Any.*' THEN
SELECT
'sub' AS "Zone",

concat(do,' ',tc,' and ',left(State,locate(' Public',State))) AS "Stewardship",
concat(@base,URI) AS "Key",
County AS "Value",
ROUND((((PointWE - West)*100)/(East-West)),2) AS "W%",
ROUND((((PointSN - South)*100)/(North-South)),2) AS "N%"

FROM county_box where State RLIKE concat('.*',sr,'.*Public.*');


END IF;

END$$

DELIMITER ;