DROP VIEW v_parkings_api_v2;

CREATE VIEW v_parkings_api_v2 AS
SELECT
    -- all original columns except those we override
    p.id,
    p.source,
    p.source_id,
    p.data_provider,
    p.date_modified,
    p.address,
    p.location,
    p.area_served,
    p.total_spot_number,
    p.create_batch_id,
    p.created_at,
    p.created_by,
    p.update_batch_id,
    p.updated_at,
    p.updated_by,
    p.valid_from,
    p.valid_to,
    p.parking_type,
    p.centroid,
    p."security",
    p.max_vehicle_dimensions,
    p.covered,
    p.contact,
    p.parking_policy,
    p.sanitized_location,
    p.area,
    p.address_updated_at,
    p.active,
    -- overrides for tsk_v2
    CASE WHEN p.source = 'tsk_v2' THEN SPLIT_PART(p.name, '; ', 1) ELSE p.source_id END AS source_id_mapped,
    p.name,
    CASE
        WHEN p.source = 'tsk_v2' AND p.category = 'violet' THEN 'zone_mixed'
        WHEN p.source = 'tsk_v2' AND p.category = 'blue'   THEN 'zone_residential'
        WHEN p.source = 'tsk_v2' AND p.category = 'orange' THEN 'zone_visitors'
        WHEN p.source = 'tsk_v2' THEN 'zone_other'
        ELSE p.zone_type
        END AS zone_type,
    CASE
        WHEN p.source = 'tsk_v2' AND p.category = 'violet' THEN 'zone_mixed'
        WHEN p.source = 'tsk_v2' AND p.category = 'blue'   THEN 'zone_residential'
        WHEN p.source = 'tsk_v2' AND p.category = 'orange' THEN 'zone_visitors'
        WHEN p.source = 'tsk_v2' THEN 'zone_other'
        ELSE p.category
        END AS category
FROM parkings p
JOIN parking_sources ps ON p.source = ps.source
WHERE p.active = true and ps.legacy_api_allowed = true;
