63 lines
2.1 KiB
MySQL
63 lines
2.1 KiB
MySQL
|
-- Migration V6: Update region_permit_risk_vw to include remark
|
||
|
|
BEGIN;
|
||
|
|
|
||
|
|
DROP VIEW IF EXISTS public.region_permit_risk_vw;
|
||
|
|
|
||
|
|
CREATE VIEW public.region_permit_risk_vw AS
|
||
|
|
SELECT
|
||
|
|
rpr.region_id,
|
||
|
|
r.name AS region_name,
|
||
|
|
rpr.permit_id,
|
||
|
|
p.name AS permit_name,
|
||
|
|
rk.id AS risk_id,
|
||
|
|
rk.risk_content,
|
||
|
|
rk.legal_basis,
|
||
|
|
rk.document_no,
|
||
|
|
rk.summary,
|
||
|
|
rk.remark,
|
||
|
|
COALESCE(theme.theme_ids, ARRAY[]::uuid[]) AS theme_ids,
|
||
|
|
COALESCE(theme.theme_names, ARRAY[]::text[]) AS theme_names,
|
||
|
|
COALESCE(scopes.scope_ids, ARRAY[]::uuid[]) AS scope_ids,
|
||
|
|
COALESCE(scopes.scope_descriptions, ARRAY[]::text[]) AS scope_descriptions,
|
||
|
|
COALESCE(subitems.subitem_ids, ARRAY[]::uuid[]) AS subitem_ids,
|
||
|
|
details.permit_status,
|
||
|
|
details.subitem_summary,
|
||
|
|
details.responsible_contact,
|
||
|
|
details.jurisdiction_scope,
|
||
|
|
details.updated_at AS permit_detail_updated_at,
|
||
|
|
concat_ws('::', rpr.region_id::text, rpr.permit_id::text, rpr.risk_id::text) AS permit_risk_key
|
||
|
|
FROM region_permit_risks rpr
|
||
|
|
JOIN regions r ON r.id = rpr.region_id
|
||
|
|
JOIN permits p ON p.id = rpr.permit_id
|
||
|
|
JOIN risks rk ON rk.id = rpr.risk_id
|
||
|
|
LEFT JOIN region_permit_details details
|
||
|
|
ON details.region_id = rpr.region_id
|
||
|
|
AND details.permit_id = rpr.permit_id
|
||
|
|
LEFT JOIN LATERAL (
|
||
|
|
SELECT
|
||
|
|
array_agg(DISTINCT rtp.theme_id) AS theme_ids,
|
||
|
|
array_agg(DISTINCT t.name) AS theme_names
|
||
|
|
FROM region_theme_permits rtp
|
||
|
|
JOIN themes t ON t.id = rtp.theme_id
|
||
|
|
WHERE rtp.region_id = rpr.region_id
|
||
|
|
AND rtp.permit_id = rpr.permit_id
|
||
|
|
) AS theme ON TRUE
|
||
|
|
LEFT JOIN LATERAL (
|
||
|
|
SELECT
|
||
|
|
array_agg(DISTINCT rps.scope_id) AS scope_ids,
|
||
|
|
array_agg(DISTINCT bs.description) AS scope_descriptions
|
||
|
|
FROM region_permit_scopes rps
|
||
|
|
JOIN business_scopes bs ON bs.id = rps.scope_id
|
||
|
|
WHERE rps.region_id = rpr.region_id
|
||
|
|
AND rps.permit_id = rpr.permit_id
|
||
|
|
) AS scopes ON TRUE
|
||
|
|
LEFT JOIN LATERAL (
|
||
|
|
SELECT
|
||
|
|
array_agg(DISTINCT rpsub.subitem_id) AS subitem_ids
|
||
|
|
FROM region_permit_subitems rpsub
|
||
|
|
WHERE rpsub.region_id = rpr.region_id
|
||
|
|
AND rpsub.permit_id = rpr.permit_id
|
||
|
|
) AS subitems ON TRUE;
|
||
|
|
|
||
|
|
COMMIT;
|