db.sql

CREATE OR REPLACE FUNCTION match_pois_hybrid(
    query_embedding vector(384),
    match_threshold float DEFAULT 0.5,
    match_count int DEFAULT 200,
    alpha_weight float DEFAULT 0.7,
    beta_weight float DEFAULT 0.3,

    p_city text DEFAULT NULL,
    p_neighborhood text DEFAULT NULL,
    p_main_category text DEFAULT NULL,
    p_sub_category text DEFAULT NULL,
    p_cuisine text DEFAULT NULL,

    p_min_rating float DEFAULT NULL,
    p_rank int DEFAULT NULL,
    p_tags text[] DEFAULT ARRAY[]::text[],
    p_avoid text[] DEFAULT ARRAY[]::text[],

    p_open_now boolean DEFAULT NULL,
    p_is_24_7 boolean DEFAULT NULL,

    p_route_enabled boolean DEFAULT false,
    p_route_origin text DEFAULT NULL,
    p_route_stops text[] DEFAULT ARRAY[]::text[],
    p_route_destination text DEFAULT NULL,

    p_user_lat float DEFAULT NULL,
    p_user_lng float DEFAULT NULL,
    p_proximity_enabled boolean DEFAULT false,
    p_max_distance_km float DEFAULT NULL,

    p_is_restaurant_query boolean DEFAULT false,
    p_is_istanbul_route boolean DEFAULT false,
    p_use_route_stops boolean DEFAULT false,
    p_destination_priority boolean DEFAULT false
)
RETURNS TABLE (
    id uuid,
    display_title text,
    city text,
    neighborhood text,
    main_category text,
    sub_category text,
    cuisine text,
    total_score float,
    rank int,
    tags text[],
    latitude float,
    longitude float,
    is_24_7 boolean,
    semantic_similarity float,
    metadata_score float,
    candidate_score float
)
LANGUAGE sql
AS $$
WITH base AS (
    SELECT
        p.id,
        p.display_title,
        p.city,
        p.neighborhood,
        p.main_category,
        p.sub_category,
        r.cuisine_type AS cuisine,
        p.total_score,
        p.rank,
        p.tags,
        p.latitude,
        p.longitude,
        p.is_24_7,
        p.embedding,

        1 - (p.embedding <=> query_embedding) AS semantic_similarity,

        (
            CASE WHEN p_min_rating IS NOT NULL AND p.total_score >= p_min_rating THEN 1 ELSE 0 END +
            CASE WHEN p_rank IS NOT NULL AND p.rank <= p_rank THEN 1 ELSE 0 END +
            CASE WHEN array_length(p_tags, 1) IS NOT NULL AND p.tags @> p_tags THEN 1 ELSE 0 END +
            CASE WHEN p_open_now IS TRUE THEN 1 ELSE 0 END +
            CASE WHEN p_is_24_7 IS TRUE AND p.is_24_7 IS TRUE THEN 1 ELSE 0 END
        )::float / 5.0 AS metadata_score

    FROM places p

    LEFT JOIN restaurants r
        ON r.place_id = p.id
        AND (
            p_is_restaurant_query IS TRUE
            OR p_main_category = 'Restaurant'
            OR p_cuisine IS NOT NULL
        )

    LEFT JOIN restaurant_dishes rd
        ON rd.restaurant_id = r.id
        AND p_cuisine IS NOT NULL
        AND rd.display_title ILIKE '%' || p_cuisine || '%'

    WHERE
        -- permanently closed filtre
        COALESCE(p.permanently_closed, false) = false

        -- semantic threshold
        AND (1 - (p.embedding <=> query_embedding)) >= match_threshold

        -- category filtreleri
        AND (p_main_category IS NULL OR p.main_category = p_main_category)
        AND (p_sub_category IS NULL OR p.sub_category = p_sub_category)
        AND (
            p_cuisine IS NULL
            OR r.cuisine_type = p_cuisine
            OR rd.display_title ILIKE '%' || p_cuisine || '%'
        )

        -- location filtreleri
        AND (
            p_is_istanbul_route IS TRUE
            OR p_city IS NULL
            OR p.city = p_city
        )
        AND (
            p_is_istanbul_route IS TRUE
            OR p_neighborhood IS NULL
            OR p.neighborhood = p_neighborhood
        )

        -- route stops aggregation
        AND (
            p_use_route_stops IS FALSE
            OR p.city = ANY(p_route_stops)
            OR p.neighborhood = ANY(p_route_stops)
        )

        -- destination priority
        AND (
            p_destination_priority IS FALSE
            OR p.city = p_route_destination
            OR p.neighborhood = p_route_destination
        )

        -- total_score / rank
        AND (p_min_rating IS NULL OR p.total_score >= p_min_rating)
        AND (p_rank IS NULL OR p.rank <= p_rank)

        -- tags contains
        AND (
            array_length(p_tags, 1) IS NULL
            OR p.tags @> p_tags
        )

        -- avoid negative filter
        AND (
            array_length(p_avoid, 1) IS NULL
            OR NOT (p.tags && p_avoid)
        )

        -- 24/7
        AND (
            p_is_24_7 IS NULL
            OR p.is_24_7 = p_is_24_7
        )

        -- proximity null-safe
        AND (
            p_proximity_enabled IS FALSE
            OR p_user_lat IS NULL
            OR p_user_lng IS NULL
            OR p_max_distance_km IS NULL
            OR (
                6371 * acos(
                    cos(radians(p_user_lat))
                    * cos(radians(p.latitude))
                    * cos(radians(p.longitude) - radians(p_user_lng))
                    + sin(radians(p_user_lat))
                    * sin(radians(p.latitude))
                )
            ) <= p_max_distance_km
        )
)

SELECT
    base.id,
    base.display_title,
    base.city,
    base.neighborhood,
    base.main_category,
    base.sub_category,
    base.cuisine,
    base.total_score,
    base.rank,
    base.tags,
    base.latitude,
    base.longitude,
    base.is_24_7,
    base.semantic_similarity,
    base.metadata_score,

    (
        alpha_weight * base.semantic_similarity
        + beta_weight * base.metadata_score
    ) AS candidate_score

FROM base
ORDER BY candidate_score DESC
LIMIT match_count;
$$;

CREATE OR REPLACE FUNCTION match_pois_metadata_only(
    match_count int DEFAULT 200,

    p_city text DEFAULT NULL,
    p_neighborhood text DEFAULT NULL,
    p_main_category text DEFAULT NULL,
    p_sub_category text DEFAULT NULL,
    p_cuisine text DEFAULT NULL,

    p_min_rating float DEFAULT NULL,
    p_rank int DEFAULT NULL,
    p_tags text[] DEFAULT ARRAY[]::text[],
    p_avoid text[] DEFAULT ARRAY[]::text[],

    p_open_now boolean DEFAULT NULL,
    p_is_24_7 boolean DEFAULT NULL,

    p_route_enabled boolean DEFAULT false,
    p_route_origin text DEFAULT NULL,
    p_route_stops text[] DEFAULT ARRAY[]::text[],
    p_route_destination text DEFAULT NULL,

    p_user_lat float DEFAULT NULL,
    p_user_lng float DEFAULT NULL,
    p_proximity_enabled boolean DEFAULT false,
    p_max_distance_km float DEFAULT NULL,

    p_is_restaurant_query boolean DEFAULT false,
    p_is_istanbul_route boolean DEFAULT false,
    p_use_route_stops boolean DEFAULT false,
    p_destination_priority boolean DEFAULT false
)
RETURNS TABLE (
    id uuid,
    display_title text,
    city text,
    neighborhood text,
    main_category text,
    sub_category text,
    cuisine text,
    total_score float,
    rank int,
    tags text[],
    latitude float,
    longitude float,
    is_24_7 boolean,
    metadata_score float,
    candidate_score float
)
LANGUAGE sql
AS $$
SELECT
    p.id,
    p.display_title,
    p.city,
    p.neighborhood,
    p.main_category,
    p.sub_category,
    r.cuisine_type AS cuisine,
    p.total_score,
    p.rank,
    p.tags,
    p.latitude,
    p.longitude,
    p.is_24_7,

    (
        CASE WHEN p_min_rating IS NOT NULL AND p.total_score >= p_min_rating THEN 1 ELSE 0 END +
        CASE WHEN p_rank IS NOT NULL AND p.rank <= p_rank THEN 1 ELSE 0 END +
        CASE WHEN array_length(p_tags, 1) IS NOT NULL AND p.tags @> p_tags THEN 1 ELSE 0 END +
        CASE WHEN p_is_24_7 IS TRUE AND p.is_24_7 IS TRUE THEN 1 ELSE 0 END
    )::float / 4.0 AS metadata_score,

    (
        CASE WHEN p_min_rating IS NOT NULL AND p.total_score >= p_min_rating THEN 1 ELSE 0 END +
        CASE WHEN p_rank IS NOT NULL AND p.rank <= p_rank THEN 1 ELSE 0 END +
        CASE WHEN array_length(p_tags, 1) IS NOT NULL AND p.tags @> p_tags THEN 1 ELSE 0 END +
        CASE WHEN p_is_24_7 IS TRUE AND p.is_24_7 IS TRUE THEN 1 ELSE 0 END
    )::float / 4.0 AS candidate_score

FROM places p

LEFT JOIN restaurants r
    ON r.place_id = p.id
    AND (
        p_is_restaurant_query IS TRUE
        OR p_main_category = 'Restaurant'
        OR p_cuisine IS NOT NULL
    )

LEFT JOIN restaurant_dishes rd
    ON rd.restaurant_id = r.id
    AND p_cuisine IS NOT NULL
    AND rd.display_title ILIKE '%' || p_cuisine || '%'

WHERE
    COALESCE(p.permanently_closed, false) = false

    AND (p_main_category IS NULL OR p.main_category = p_main_category)
    AND (p_sub_category IS NULL OR p.sub_category = p_sub_category)

    AND (
        p_cuisine IS NULL
        OR r.cuisine_type = p_cuisine
        OR rd.display_title ILIKE '%' || p_cuisine || '%'
    )

    AND (
        p_is_istanbul_route IS TRUE
        OR p_city IS NULL
        OR p.city = p_city
    )

    AND (
        p_is_istanbul_route IS TRUE
        OR p_neighborhood IS NULL
        OR p.neighborhood = p_neighborhood
    )

    AND (
        p_use_route_stops IS FALSE
        OR p.city = ANY(p_route_stops)
        OR p.neighborhood = ANY(p_route_stops)
    )

    AND (
        p_destination_priority IS FALSE
        OR p.city = p_route_destination
        OR p.neighborhood = p_route_destination
    )

    AND (p_min_rating IS NULL OR p.total_score >= p_min_rating)
    AND (p_rank IS NULL OR p.rank <= p_rank)

    AND (
        array_length(p_tags, 1) IS NULL
        OR p.tags @> p_tags
    )

    AND (
        array_length(p_avoid, 1) IS NULL
        OR NOT (p.tags && p_avoid)
    )

    AND (
        p_is_24_7 IS NULL
        OR p.is_24_7 = p_is_24_7
    )

ORDER BY candidate_score DESC
LIMIT match_count;
$$;