CREATE VIEW nursery_view AS
SELECT
    n.id AS id,
    comp.business_name AS business_name,
    addr.id AS address_id,
    city."name" AS city_name,
    city.id AS city_id
FROM
    nursery AS n
INNER JOIN
    company AS comp ON comp.id = n.company_id
INNER JOIN
    address AS addr ON addr.id = comp.address_id
INNER JOIN
    city ON city.id = addr.city_id;



CREATE VIEW company_view AS
SELECT
    comp.id,
    comp.business_name,
    addr.id AS address_id,
    city."name" AS city_name,
    city.id AS city_id
FROM
    company AS comp
INNER JOIN
    address AS addr ON addr.id = comp.address_id
INNER JOIN
    city ON city.id = addr.city_id;


create view plant_inventory_view as
select
	npi.nursery_id || '_' || "type" || '_' || npi.specie_id as id,
	SUM(npi.quantity) as quantity,
	npi.specie_id,
	npi.nursery_id as nursery_id,
	ns.city_id,
	"type"
from
	nursery_plant_inventory as npi
inner join
    nursery_view as ns on
	npi.nursery_id = ns.id
group by
	npi.specie_id,
	npi.nursery_id,
	"type",
	ns.city_id;



create view specie_availability_view as select
	nursery_id || '_' || "specie_id" || '_' || "type" as id,
	nursery_id,
	city_id,
	specie_id,
	coalesce (sum(case
		when piv."type" = 'INCREASE' then piv.quantity
	end), 0) as increase,
	coalesce (sum (case
		when piv."type" = 'DECREASE' then -piv.quantity
	end
	), 0) as decrease,
	sum(case when piv.type = 'INCREASE' then piv.quantity else -piv.quantity end) as total
from
	plant_inventory_view as piv
group by
	piv.nursery_id,
	specie_id,
	city_id,
	"type"
order by
	nursery_id 


