-- Views for moving wayleave map layers from queries in .php scripts to views that allows using the standard map_get_v2.php api


CREATE OR REPLACE VIEW wayleave.vw_agreement_polygons AS
SELECT id, agreement_id, label, geom
FROM wayleave.agreement_polygons
WHERE is_deleted = false;

CREATE OR REPLACE VIEW wayleave.vw_uprns_from_boundary AS
SELECT apu.agreement_id, apu.uprn, apu.is_approved, abp.address AS address_full, abp.geom,
       CASE WHEN r.uprn IS NOT NULL THEN true ELSE false END AS released
FROM wayleave.agreement_polygon_uprns apu
JOIN basedata.abp abp ON abp.uprn = apu.uprn
LEFT JOIN wayleave.agreement_releases r
       ON r.agreement_id = apu.agreement_id AND r.uprn = apu.uprn AND r.is_deleted = false
WHERE apu.is_assigned = true;

CREATE OR REPLACE VIEW wayleave.vw_uprns_from_direct AS
SELECT au.agreement_id, au.uprn, abp.address AS address_full, abp.geom,
       CASE WHEN r.uprn IS NOT NULL THEN true ELSE false END AS released
FROM wayleave.agreement_uprns au
JOIN basedata.abp abp ON abp.uprn = au.uprn
LEFT JOIN wayleave.agreement_releases r
       ON r.agreement_id = au.agreement_id AND r.uprn = au.uprn AND r.is_deleted = false
WHERE au.is_deleted = false;

CREATE OR REPLACE VIEW wayleave.vw_uprns_from_stocklists AS
SELECT ast.agreement_id, sp.uprn, abp.address AS address_full, abp.geom,
       CASE WHEN r.uprn IS NOT NULL THEN true ELSE false END AS released
FROM wayleave.agreement_stocklists ast
JOIN stocklists.stocklist_premises sp ON sp.stocklist_id = ast.stocklist_id
JOIN basedata.abp abp ON abp.uprn = sp.uprn
LEFT JOIN wayleave.agreement_releases r
       ON r.agreement_id = ast.agreement_id AND r.uprn = sp.uprn AND r.is_deleted = false
WHERE ast.is_deleted = false;

CREATE OR REPLACE VIEW wayleave.vw_uprns_from_titles AS
SELECT alr.agreement_id, abp.uprn, abp.address AS address_full, abp.geom,
       CASE WHEN r.uprn IS NOT NULL THEN true ELSE false END AS released
FROM wayleave.agreement_landregistry alr
JOIN basedata.abp abp ON abp.title_no = alr.title_number
LEFT JOIN wayleave.agreement_releases r
       ON r.agreement_id = alr.agreement_id AND r.uprn = abp.uprn AND r.is_deleted = false
WHERE alr.is_deleted = false AND abp.uprn IS NOT NULL;


