DROP VIEW IF EXISTS public.level_1_person_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_2_person_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_3_person_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_4_person_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_5_person_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_6_person_timeline_view CASCADE;
CREATE OR REPLACE FUNCTION public.level_person_timeline(start_time bigint, end_time bigint, person_id integer, user_id integer, level_param integer)
RETURNS TABLE (
    level integer,
    admin smallint,
    id_unit integer,
    item_count bigint,
    id_item integer
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        CASE
            WHEN level_param = 1 THEN g.level_1
            WHEN level_param = 2 THEN g.level_2
            WHEN level_param = 3 THEN g.level_3
            WHEN level_param = 4 THEN g.level_4
            WHEN level_param = 5 THEN g.level_5
            WHEN level_param = 6 THEN g.level_6
            ELSE NULL
        END AS level,
        CASE
            WHEN level_param = 1 THEN g.admin_1
            WHEN level_param = 2 THEN g.admin_2
            WHEN level_param = 3 THEN g.admin_3
            WHEN level_param = 4 THEN g.admin_4
            WHEN level_param = 5 THEN g.admin_5
            WHEN level_param = 6 THEN g.admin_6
            ELSE NULL
        END AS admin,
        MIN(u.id) AS id_unit,
        COUNT(DISTINCT u.id_item) AS item_count,
        MIN(u.id_item) AS id_item
    FROM
        public.unit u
    JOIN
        public.face f ON u.id = f.id_unit
    JOIN
        public.geocoding g ON u.id_geocoding = g.id
    WHERE
        u.takentime BETWEEN start_time AND end_time
        AND f.id_person = person_id
        AND u.id_user = user_id
        AND u.is_major = true
    GROUP BY
        level, admin
    ORDER BY
        item_count DESC NULLS LAST,
        id_item ASC NULLS LAST
    LIMIT 4;
END;
$$ LANGUAGE plpgsql;
DROP VIEW IF EXISTS public.level_1_concept_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_2_concept_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_3_concept_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_4_concept_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_5_concept_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_6_concept_timeline_view CASCADE;
CREATE OR REPLACE FUNCTION public.level_concept_timeline(start_time bigint, end_time bigint, concept_id integer, user_id integer, level_param integer)
RETURNS TABLE (
    level integer,
    admin smallint,
    id_unit integer,
    item_count bigint,
    id_item integer
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        CASE
            WHEN level_param = 1 THEN g.level_1
            WHEN level_param = 2 THEN g.level_2
            WHEN level_param = 3 THEN g.level_3
            WHEN level_param = 4 THEN g.level_4
            WHEN level_param = 5 THEN g.level_5
            WHEN level_param = 6 THEN g.level_6
            ELSE NULL
        END AS level,
        CASE
            WHEN level_param = 1 THEN g.admin_1
            WHEN level_param = 2 THEN g.admin_2
            WHEN level_param = 3 THEN g.admin_3
            WHEN level_param = 4 THEN g.admin_4
            WHEN level_param = 5 THEN g.admin_5
            WHEN level_param = 6 THEN g.admin_6
            ELSE NULL
        END AS admin,
        MIN(u.id) AS id_unit,
        COUNT(DISTINCT u.id_item) AS item_count,
        MIN(u.id_item) AS id_item
    FROM
        public.unit u
    JOIN
        many_unit_has_many_concept r ON u.id = r.id_unit
    JOIN
        public.geocoding g ON u.id_geocoding = g.id
    WHERE
        u.takentime BETWEEN start_time AND end_time
        AND r.id_concept = concept_id
        AND u.id_user = user_id
        AND u.is_major = true
    GROUP BY
        level, admin
    ORDER BY
        item_count DESC NULLS LAST,
        id_item ASC NULLS LAST
    LIMIT 4;
END;
$$ LANGUAGE plpgsql;
DROP VIEW IF EXISTS public.level_1_general_tag_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_2_general_tag_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_3_general_tag_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_4_general_tag_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_5_general_tag_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_6_general_tag_timeline_view CASCADE;
CREATE OR REPLACE FUNCTION public.level_general_tag_timeline(start_time bigint, end_time bigint, general_tag_id integer, user_id integer, level_param integer)
RETURNS TABLE (
    level integer,
    admin smallint,
    id_unit integer,
    item_count bigint,
    id_item integer
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        CASE
            WHEN level_param = 1 THEN g.level_1
            WHEN level_param = 2 THEN g.level_2
            WHEN level_param = 3 THEN g.level_3
            WHEN level_param = 4 THEN g.level_4
            WHEN level_param = 5 THEN g.level_5
            WHEN level_param = 6 THEN g.level_6
            ELSE NULL
        END AS level,
        CASE
            WHEN level_param = 1 THEN g.admin_1
            WHEN level_param = 2 THEN g.admin_2
            WHEN level_param = 3 THEN g.admin_3
            WHEN level_param = 4 THEN g.admin_4
            WHEN level_param = 5 THEN g.admin_5
            WHEN level_param = 6 THEN g.admin_6
            ELSE NULL
        END AS admin,
        MIN(u.id) AS id_unit,
        COUNT(DISTINCT u.id_item) AS item_count,
        MIN(u.id_item) AS id_item
    FROM
        public.unit u
    JOIN
        many_unit_has_many_general_tag r ON u.id = r.id_unit
    JOIN
        public.geocoding g ON u.id_geocoding = g.id
    WHERE
        u.takentime BETWEEN start_time AND end_time
        AND r.id_general_tag = general_tag_id
        AND u.id_user = user_id
        AND u.is_major = true
    GROUP BY
        level, admin
    ORDER BY
        item_count DESC NULLS LAST,
        id_item ASC NULLS LAST
    LIMIT 4;
END;
$$ LANGUAGE plpgsql;
DROP VIEW IF EXISTS public.level_1_search_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_2_search_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_3_search_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_4_search_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_5_search_timeline_view CASCADE;
DROP VIEW IF EXISTS public.level_6_search_timeline_view CASCADE;
CREATE OR REPLACE FUNCTION public.level_search_timeline(start_time bigint, end_time bigint, _ integer, user_id integer, level_param integer)
RETURNS TABLE (
    level integer,
    admin smallint,
    id_unit integer,
    item_count bigint,
    id_item integer
) AS $$
BEGIN
    RETURN QUERY
    SELECT
        CASE
            WHEN level_param = 1 THEN g.level_1
            WHEN level_param = 2 THEN g.level_2
            WHEN level_param = 3 THEN g.level_3
            WHEN level_param = 4 THEN g.level_4
            WHEN level_param = 5 THEN g.level_5
            WHEN level_param = 6 THEN g.level_6
            ELSE NULL
        END AS level,
        CASE
            WHEN level_param = 1 THEN g.admin_1
            WHEN level_param = 2 THEN g.admin_2
            WHEN level_param = 3 THEN g.admin_3
            WHEN level_param = 4 THEN g.admin_4
            WHEN level_param = 5 THEN g.admin_5
            WHEN level_param = 6 THEN g.admin_6
            ELSE NULL
        END AS admin,
        MIN(u.id) AS id_unit,
        COUNT(DISTINCT u.id_item) AS item_count,
        MIN(u.id_item) AS id_item
    FROM
        public.unit u
    JOIN
        search_timeline s ON u.id = s.id_unit
    JOIN
        public.geocoding g ON u.id_geocoding = g.id
    WHERE
        u.takentime BETWEEN start_time AND end_time
        AND u.id_user = user_id
        AND u.is_major = true
    GROUP BY
        level, admin
    ORDER BY
        item_count DESC NULLS LAST,
        id_item ASC NULLS LAST
    LIMIT 4;
END;
$$ LANGUAGE plpgsql;