-- 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;