DROP VIEW IF EXISTS public.general_tag_timeline_view;
CREATE OR REPLACE VIEW public.general_tag_timeline_view AS
SELECT u.id_item,
  u.id_user,
  u.item_type,
  u.type AS unit_type,
  u.id AS id_unit,
  array_agg(DISTINCT r.id_general_tag) AS id_general_tag,
  u.takentime
FROM unit u
  JOIN many_unit_has_many_general_tag r ON u.id = r.id_unit
WHERE u.is_major = true
GROUP BY u.id_user, u.id;
DROP VIEW IF EXISTS public.person_timeline_view;
CREATE OR REPLACE VIEW public.person_timeline_view AS
SELECT u.id_item,
  u.id_user,
  u.item_type,
  u.type AS unit_type,
  u.id AS id_unit,
  array_agg(DISTINCT f.id_person) AS id_person,
  u.takentime
FROM unit u
  JOIN face f ON u.id = f.id_unit
WHERE u.is_major = true
GROUP BY u.id_user, u.id;
DROP FUNCTION IF EXISTS public.update_user_flag_after_live_photo_update_func() CASCADE;
CREATE OR REPLACE FUNCTION public.update_user_flag_after_live_photo_update_func()
RETURNS TRIGGER AS $$
DECLARE
  affected_id_user integer;
BEGIN
  IF (TG_OP = 'INSERT') THEN
    SELECT DISTINCT n.id_user from new_table n where n.item_type=3 INTO affected_id_user;
  ELSIF (TG_OP = 'UPDATE') THEN
    SELECT DISTINCT n.id_user
      FROM new_table n
      JOIN old_table o ON n.id_user = o.id_user
      WHERE n.is_major <> o.is_major
      INTO affected_id_user;
  END IF;
  IF (affected_id_user IS NULL) THEN
    RETURN NULL;
  END IF;
  INSERT INTO public.user_flag (id_user, flag, value)
  VALUES
   (affected_id_user, 'need_recount_concept_item', 'true'),
   (affected_id_user, 'need_recount_person_item', 'true')
  ON CONFLICT (id_user, flag) DO UPDATE SET value = 'true';
  RETURN NULL;
END
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS update_user_flag_after_live_photo_update_trigger ON public.unit CASCADE;
DROP TRIGGER IF EXISTS update_user_flag_after_live_photo_update_insert_trigger ON public.unit CASCADE;
CREATE TRIGGER update_user_flag_after_live_photo_update_insert_trigger
AFTER INSERT ON public.unit
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE public.update_user_flag_after_live_photo_update_func();
DROP TRIGGER IF EXISTS update_user_flag_after_live_photo_update_update_trigger ON public.unit CASCADE;
CREATE TRIGGER update_user_flag_after_live_photo_update_update_trigger
AFTER UPDATE ON public.unit
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE public.update_user_flag_after_live_photo_update_func();
DROP FUNCTION IF EXISTS public.update_user_flag_after_delete_item_update_func() CASCADE;
CREATE OR REPLACE FUNCTION public.update_user_flag_after_delete_item_update_func()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.user_flag (id_user, flag, value)
  VALUES
    (NEW.id_user, 'need_recount_concept_item', 'true'),
    (NEW.id_user, 'need_recount_person_item', 'true')
  ON CONFLICT (id_user, flag) DO UPDATE SET value = 'true';
  RETURN NULL;
END
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS update_user_flag_after_delete_item_update_trigger ON public.delete_item CASCADE;
CREATE TRIGGER update_user_flag_after_delete_item_update_trigger
AFTER INSERT OR UPDATE ON public.delete_item
FOR EACH ROW
EXECUTE PROCEDURE public.update_user_flag_after_delete_item_update_func();