ALTER TABLE public.unit
  DROP CONSTRAINT IF EXISTS unit_id_item_type_uk CASCADE,
  ADD CONSTRAINT unit_id_item_type_uk UNIQUE (id, item_type);
CREATE TABLE IF NOT EXISTS public.many_unit_has_many_favorite_user(
  id_unit integer NOT NULL,
  id_user integer NOT NULL,
  id_favorite_user integer NOT NULL
);
CREATE INDEX IF NOT EXISTS id_user_id_favorite_user_idx ON public.many_unit_has_many_favorite_user (id_user, id_favorite_user);
CREATE INDEX IF NOT EXISTS id_unit_id_user_id_favorite_user_idx ON public.many_unit_has_many_favorite_user (id_unit, id_user, id_favorite_user);
ALTER TABLE public.many_unit_has_many_favorite_user
  DROP CONSTRAINT IF EXISTS favorite_pk CASCADE,
  DROP CONSTRAINT IF EXISTS unit_id_user_id_fk CASCADE,
  ADD CONSTRAINT favorite_pk PRIMARY KEY (id_unit, id_favorite_user),
  ADD CONSTRAINT unit_id_user_id_fk FOREIGN KEY (id_unit, id_user) REFERENCES public.unit (id, id_user) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
CREATE TABLE IF NOT EXISTS public.favorite_timeline(
  id_item integer NOT NULL,
  id_user integer NOT NULL,
  item_type integer NOT NULL,
  unit_type integer NOT NULL,
  id_unit integer NOT NULL,
  id_favorite_user integer[] NOT NULL,
  takentime bigint NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS id_user_idx ON public.favorite_timeline (id_user);
CREATE INDEX IF NOT EXISTS id_favorite_user_idx ON public.favorite_timeline USING GIN (id_favorite_user);
ALTER TABLE public.favorite_timeline
  DROP CONSTRAINT IF EXISTS favorite_timeline_pk CASCADE,
  DROP CONSTRAINT IF EXISTS unit_id_item_id_fk CASCADE,
  DROP CONSTRAINT IF EXISTS unit_id_user_id_fk CASCADE,
  DROP CONSTRAINT IF EXISTS unit_id_item_type_fk CASCADE,
  DROP CONSTRAINT IF EXISTS unit_id_takentime_fk CASCADE,
  ADD CONSTRAINT favorite_timeline_pk PRIMARY KEY (id_unit),
  ADD CONSTRAINT unit_id_item_id_fk FOREIGN KEY (id_unit, id_item) REFERENCES public.unit (id, id_item) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
  ADD CONSTRAINT unit_id_user_id_fk FOREIGN KEY (id_unit, id_user) REFERENCES public.unit (id, id_user) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
  ADD CONSTRAINT unit_id_item_type_fk FOREIGN KEY (id_unit, item_type) REFERENCES public.unit (id, item_type) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE,
  ADD CONSTRAINT unit_id_takentime_fk FOREIGN KEY (id_unit, takentime) REFERENCES public.unit (id, takentime) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
CREATE OR REPLACE FUNCTION public.create_user_event_history_table_by_id_user_func (id_user integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  id_user_bucket INT;
  user_event_history_table text;
  user_event_history_table_name text;
BEGIN
  id_user_bucket := CASE WHEN id_user = 0 THEN 0 ELSE (id_user - 1) % 13 + 1 END;
  user_event_history_table = 'public.user_event_history_' || id_user_bucket;
  user_event_history_table_name = 'user_event_history_' || id_user_bucket;
  IF EXISTS (
    SELECT 1
    FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name = user_event_history_table_name
  ) THEN
    RETURN;
  END IF;
  EXECUTE format('CREATE TABLE %s (
    version BIGINT PRIMARY KEY,
    id_user INTEGER NOT NULL,
    target_type SMALLINT NOT NULL,
    target_id INTEGER[] NOT NULL,
    target_id_user INTEGER NOT NULL,
    trigger_id_user INTEGER,
    event_type TEXT NOT NULL,
    event_detail JSON)', user_event_history_table
  );
  EXECUTE format('CREATE INDEX IF NOT EXISTS user_event_history_%s_version_id_user_idx ON %s (id_user, version);', id_user_bucket, user_event_history_table);
END
$$;
DO $$
  DECLARE
    id_user INTEGER;
    max_id_user INTEGER;
    loop_limit INTEGER;
  BEGIN
    SELECT MAX(id) INTO max_id_user FROM public.user_info;
    loop_limit := LEAST(max_id_user, 13);
    FOR id_user IN 0..loop_limit LOOP
      EXECUTE format('SELECT public.create_user_event_history_table_by_id_user_func(%s);', id_user);
    END LOOP;
END $$;
DROP TRIGGER IF EXISTS create_version_time_by_id_user_trigger ON public.user_info CASCADE;
DROP FUNCTION IF EXISTS public.create_version_time_by_id_user_trigger_func CASCADE;
CREATE OR REPLACE FUNCTION public.create_version_time_by_id_user_func (id_user integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  new_version_time_table text;
  new_version_time_table_name text;
  trigger_name text;
  pk_name text;
  idx_name text;
  new_sequence_name text;
  id_user_bucket integer;
BEGIN
  id_user_bucket := CASE WHEN id_user = 0 THEN 0 ELSE (id_user - 1) % 13 + 1 END;
  new_version_time_table = 'public.version_time_' || id_user_bucket;
  new_version_time_table_name = 'version_time_' || id_user_bucket;
  pk_name = 'version_id_' || id_user_bucket || '_primary';
  idx_name = 'version_time_' || id_user_bucket || '_idx';
  new_sequence_name = 'public.version_time_' || id_user_bucket || '_version_seq';
  trigger_name = 'delete_expired_version_time_' || id_user_bucket || '_data_trigger';
  -- check if the sequence and table already exists
  RAISE NOTICE 'Checking if sequence % and table % exist', new_sequence_name, new_version_time_table;
  IF EXISTS (
    SELECT 1
    FROM information_schema.tables
    WHERE table_schema = 'public'
    AND table_name = new_version_time_table_name
  ) THEN
    RETURN;
  END IF;
  EXECUTE format('CREATE SEQUENCE %s START WITH 1', new_sequence_name);
  EXECUTE format('CREATE TABLE %s (version BIGINT NOT NULL DEFAULT nextval(''%s''), modified_time BIGINT NOT NULL)',
    new_version_time_table, new_sequence_name, pk_name);
  EXECUTE format('CREATE UNIQUE INDEX %s ON %s USING btree (version, modified_time ASC NULLS LAST)', idx_name, new_version_time_table);
  EXECUTE format('CREATE TRIGGER %s BEFORE INSERT ON %s FOR EACH ROW EXECUTE PROCEDURE public.delete_expired_version_time_data_func()',
    trigger_name, new_version_time_table);
END;
$$;
CREATE OR REPLACE FUNCTION public.create_version_related_table_trigger_func()
  RETURNS TRIGGER
  LANGUAGE plpgsql
  AS $$
BEGIN
  -- take care, version_time table should be created before user_event_history table
  PERFORM public.create_version_time_by_id_user_func(NEW.id);
  PERFORM public.create_user_event_history_table_by_id_user_func(NEW.id);
  RETURN NEW;
END
$$;
DROP TRIGGER IF EXISTS create_version_related_table_trigger ON public.user_info CASCADE;
CREATE TRIGGER create_version_related_table_trigger
  AFTER INSERT ON public.user_info
  FOR EACH ROW
  EXECUTE PROCEDURE public.create_version_related_table_trigger_func();