DROP TABLE IF EXISTS public.similar_timeline;
CREATE TABLE public.similar_timeline AS
SELECT
  unit.id AS id_unit,
  unit.id_item AS id_item,
  unit.id_user,
  unit.takentime,
  item.id_similar_group,
  similar_group.item_count AS similar_group_item_count
FROM
  unit
LEFT JOIN item ON id_item = item.id
LEFT JOIN similar_group ON item.id_similar_group = similar_group.id
WHERE
  unit.is_major IS TRUE
  AND item.is_similar_top_pick is NOT FALSE;
ALTER TABLE public.similar_timeline
DROP CONSTRAINT IF EXISTS similar_timeline_pkey,
ADD CONSTRAINT similar_timeline_pkey PRIMARY KEY (id_unit, id_item, id_user);
ALTER TABLE public.similar_timeline
DROP CONSTRAINT IF EXISTS similar_timeline_composite_unit_user_fk,
ADD CONSTRAINT similar_timeline_composite_unit_user_fk FOREIGN KEY (id_unit, id_user)
REFERENCES unit(id, id_user) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.similar_timeline
DROP CONSTRAINT IF EXISTS similar_timeline_item_fk,
ADD CONSTRAINT similar_timeline_item_fk FOREIGN KEY (id_item)
REFERENCES public.item(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.unit
DROP CONSTRAINT IF EXISTS unit_id_takentime_fk CASCADE,
ADD CONSTRAINT unit_id_takentime_fk UNIQUE (id, takentime);
ALTER TABLE public.similar_timeline
DROP CONSTRAINT IF EXISTS similar_unit_item_fk,
DROP CONSTRAINT IF EXISTS similar_unit_takentime_fk,
ADD CONSTRAINT similar_unit_item_fk FOREIGN KEY (id_unit, id_item) REFERENCES public.unit(id, id_item) ON UPDATE CASCADE,
ADD CONSTRAINT similar_unit_takentime_fk FOREIGN KEY (id_unit, takentime) REFERENCES public.unit(id, takentime) ON UPDATE CASCADE;
CREATE OR REPLACE FUNCTION public.update_similar_timeline_after_unit_insert_func()
  RETURNS trigger
  LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO public.similar_timeline (id_unit, id_item, id_user, takentime)
  SELECT
    NEW.id,
    NEW.id_item,
    NEW.id_user,
    NEW.takentime
  FROM item
  WHERE NEW.id_item = item.id
  AND item.is_similar_top_pick is NOT FALSE
  ON CONFLICT (id_unit, id_item, id_user)
  DO UPDATE SET
    takentime = EXCLUDED.takentime;
  RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS update_similar_timeline_after_unit_insert_trigger ON unit CASCADE;
CREATE TRIGGER update_similar_timeline_after_unit_insert_trigger
AFTER INSERT ON public.unit
FOR EACH ROW EXECUTE FUNCTION public.update_similar_timeline_after_unit_insert_func();
CREATE OR REPLACE FUNCTION public.update_similar_timeline_after_item_update_func()
  RETURNS trigger
  LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE public.similar_timeline
  SET
    id_similar_group = NEW.id_similar_group
  WHERE id_item = NEW.id;
  IF (NEW.id_similar_group IS NULL) THEN
     UPDATE public.similar_timeline
      SET
        similar_group_item_count = NULL
      WHERE id_item = NEW.id;
  END IF;
  RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS update_similar_timeline_after_item_update_trigger ON item CASCADE;
CREATE TRIGGER update_similar_timeline_after_item_update_trigger
AFTER UPDATE OF id_similar_group ON public.item
FOR EACH ROW EXECUTE FUNCTION public.update_similar_timeline_after_item_update_func();
CREATE OR REPLACE FUNCTION public.delete_similar_timeline_after_item_update_func()
  RETURNS trigger
  LANGUAGE plpgsql
AS $$
BEGIN
  IF
    NEW.is_similar_top_pick IS FALSE
  THEN
    DELETE FROM public.similar_timeline WHERE id_item = NEW.id;
  END IF;
  RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS delete_similar_timeline_after_item_update_trigger ON item CASCADE;
CREATE TRIGGER delete_similar_timeline_after_item_update_trigger
AFTER UPDATE OF is_similar_top_pick ON public.item
FOR EACH ROW EXECUTE FUNCTION public.delete_similar_timeline_after_item_update_func();
CREATE OR REPLACE FUNCTION public.insert_similar_timeline_after_item_update_func()
  RETURNS trigger
  LANGUAGE plpgsql
AS $$
DECLARE
  unit_id integer;
  unit_takentime bigint;
  similar_group_item_count bigint;
BEGIN
  IF
    NEW.is_similar_top_pick IS TRUE OR NEW.id_similar_group IS NULL
  THEN
    SELECT id, takentime INTO unit_id, unit_takentime FROM unit WHERE NEW.id = unit.id_item AND unit.is_major IS TRUE;
    SELECT
      item_count INTO similar_group_item_count
    FROM
      similar_group
    WHERE
      NEW.id_similar_group = similar_group.id;
    INSERT INTO public.similar_timeline
    VALUES (unit_id, NEW.id, NEW.id_user, unit_takentime, NEW.id_similar_group, similar_group_item_count)
    ON CONFLICT (id_unit, id_item, id_user)
    DO UPDATE SET
      takentime = EXCLUDED.takentime,
      id_similar_group = EXCLUDED.id_similar_group,
      similar_group_item_count = EXCLUDED.similar_group_item_count;
  END IF;
  RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS insert_similar_timeline_after_item_update_trigger ON item CASCADE;
CREATE TRIGGER insert_similar_timeline_after_item_update_trigger
AFTER UPDATE OF is_similar_top_pick, id_similar_group ON public.item
FOR EACH ROW EXECUTE FUNCTION public.insert_similar_timeline_after_item_update_func();
CREATE OR REPLACE FUNCTION public.update_similar_timeline_after_similar_group_update_func()
  RETURNS trigger
  LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE public.similar_timeline
  SET
    similar_group_item_count = NEW.item_count
  WHERE id_similar_group = NEW.id;
  RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS update_similar_timeline_after_similar_group_update_trigger ON similar_group CASCADE;
CREATE TRIGGER update_similar_timeline_after_similar_group_update_trigger
AFTER UPDATE OF item_count ON public.similar_group
FOR EACH ROW EXECUTE FUNCTION public.update_similar_timeline_after_similar_group_update_func();
CREATE OR REPLACE FUNCTION public.delete_item_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  delete_item_table text;
  new_version bigint;
  id_user integer;
BEGIN
  delete_item_table = TG_TABLE_SCHEMA || '.delete_item';
  SELECT id from user_info where id = OLD.id_user INTO id_user;
  if (id_user IS NOT NULL) THEN
    SELECT public.get_next_version_func(TG_TABLE_SCHEMA, id_user) INTO new_version;
    EXECUTE format('INSERT INTO %s (id_item, id_user, version) VALUES (%L, %L, %L)', delete_item_table, OLD.id, OLD.id_user, new_version);
  END IF;
  UPDATE similar_group
  SET item_count = GREATEST(item_count - 1, 0), version = new_version
  WHERE id = OLD.id_similar_group;
  IF (OLD.is_similar_top_pick IS TRUE) THEN
    PERFORM public.update_similar_group_top_pick_func(OLD.id_similar_group);
  END IF;
  RETURN NULL;
END
$$;
CREATE OR REPLACE FUNCTION public.insert_similar_timeline_by_unit_id(target_id_unit integer)
  RETURNS void
  LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO public.similar_timeline (id_unit, id_item, id_user, takentime)
  SELECT
    unit.id,
    unit.id_item,
    unit.id_user,
    unit.takentime
  FROM
    unit, item
  WHERE unit.id = target_id_unit AND unit.id_item = item.id
  AND unit.is_major is TRUE
  AND item.is_similar_top_pick is NOT FALSE
  ON CONFLICT (id_unit, id_item, id_user)
  DO NOTHING;
END;
$$;
CREATE OR REPLACE FUNCTION public.delete_similar_timeline_by_unit_id(target_id_unit integer, target_id_item integer)
  RETURNS void
  LANGUAGE plpgsql
AS $$
BEGIN
  DELETE FROM public.similar_timeline
  WHERE id_unit = target_id_unit
  AND id_item = target_id_item
  AND (
    EXISTS (
      SELECT 1
      FROM unit
      WHERE id = target_id_unit
      AND is_major IS FALSE
    )
    OR EXISTS (
      SELECT 1
      FROM item
      WHERE id = target_id_item
      AND is_similar_top_pick IS FALSE
    )
  );
END;
$$;