CREATE OR REPLACE FUNCTION public.update_filter_with_conflict_delete(p_old_id_filter INTEGER, p_new_id_filter INTEGER, p_id_user INTEGER, p_max_id_unit INTEGER, p_filter_type TEXT)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    -- remove conflict rows first
    DELETE FROM filter f
    USING filter conflict
    WHERE
        f.filter_type = p_filter_type
        AND f.id_user = p_id_user
        AND f.id_filter = p_old_id_filter
        AND f.id_unit <= p_max_id_unit
        AND conflict.id_unit     = f.id_unit
        AND conflict.id_filter   = p_new_id_filter
        AND conflict.filter_type = f.filter_type
        AND conflict.id_user     = f.id_user
        AND conflict.ctid <> f.ctid;
    UPDATE filter
    SET id_filter = p_new_id_filter
    WHERE
        filter_type = p_filter_type
        AND id_user = p_id_user
        AND id_filter = p_old_id_filter
        AND id_unit <= p_max_id_unit;
END;
$$;
CREATE INDEX IF NOT EXISTS similar_timeline_id_item_idx ON public.similar_timeline(id_item);
CREATE OR REPLACE FUNCTION public.rename_fk_constraints(replaced_patterns text[])
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  r RECORD;
  new_name text;
  exists_check int;
BEGIN
  FOR r IN
    SELECT
      con.conname AS old_name,
      c.relname   AS table_name,
      n.nspname   AS schema_name
    FROM pg_constraint con
    JOIN pg_class c ON c.oid = con.conrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE con.contype = 'f'
      AND con.conname = ANY (replaced_patterns)
  LOOP
    new_name := r.table_name || '_' || r.old_name;
    SELECT 1 INTO exists_check
    FROM pg_constraint
    WHERE conname = new_name;
    IF exists_check IS NULL THEN
      EXECUTE format(
        'ALTER TABLE %I.%I RENAME CONSTRAINT %I TO %I;',
          r.schema_name,
          r.table_name,
          r.old_name,
          new_name
      );
    END IF;
  END LOOP;
END;
$$;
SELECT rename_fk_constraints(ARRAY[
  'folder_id_user_fk',
  'album_id_user_fk'
]);
SELECT rename_fk_constraints(ARRAY[
  'id_user_fk',
  'unit_fk',
  'composite_unit_fk',
  'share_fk',
  'concept_fk',
  'user_id_fk',
  'person_fk',
  'folder_fk',
  'id_unit_fk',
  'id_task_fk',
  'unit_id_user_id_fk',
  'item_fk',
  'fk_id_task'
]);
CREATE OR REPLACE FUNCTION public.set_person_item_count_fk_refer_to_production_tables_func()
  RETURNS VOID
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
BEGIN
  TRUNCATE person_item_count;
  ALTER TABLE person_item_count
  DROP CONSTRAINT IF EXISTS person_item_count_person_fk,
  ADD CONSTRAINT person_item_count_person_fk FOREIGN KEY (id_person) REFERENCES person(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
END
$$;
DROP FUNCTION IF EXISTS public.drop_constraint_for_all_condition_album_item_tables();