CREATE OR REPLACE FUNCTION public.create_temp_table_for_delete_task(task_id integer)
RETURNS VOID AS
$$
DECLARE
  temp_table_name TEXT;
BEGIN
  temp_table_name := 'temp_table_for_delete_task_' || task_id;
  EXECUTE 'CREATE TABLE ' || temp_table_name || ' (id INTEGER)';
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.insert_into_temp_table_for_delete_task(task_id integer, VARIADIC ids int[])
RETURNS VOID AS
$$
DECLARE
  sql_statement TEXT;
BEGIN
  sql_statement := 'INSERT INTO temp_table_for_delete_task_' || task_id || ' (id) VALUES ';
  sql_statement := sql_statement || '(' || array_to_string(ids, '),(') || ')';
  EXECUTE sql_statement;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.drop_temp_table_for_delete_task(task_id integer)
RETURNS VOID AS
$$
DECLARE
  temp_table_name TEXT;
BEGIN
  temp_table_name := 'temp_table_for_delete_task_' || task_id;
  EXECUTE 'DROP TABLE ' || temp_table_name;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.decrease_person_album_count_for_delete_bg_task(task_id integer, user_id integer)
RETURNS VOID AS
$$
DECLARE
  temp_table_name TEXT;
  query TEXT;
BEGIN
  temp_table_name := 'temp_table_for_delete_task_' || task_id;
  query := 'UPDATE person_item_count
  SET item_count = person_item_count.item_count - subquery.item_count
  FROM (
    SELECT id_person, id_user, COUNT(DISTINCT(id_item)) AS item_count
    FROM person_face_view
    WHERE id_user = ' || user_id || ' AND id_item IN (' || 'SELECT id FROM ' || quote_ident(temp_table_name) || ')
    GROUP BY id_person, id_user
  ) AS subquery
  WHERE person_item_count.id_person = subquery.id_person;';
  EXECUTE query;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.increase_person_album_count_by_item_id(item_id integer, user_id integer)
RETURNS VOID AS
$$
BEGIN
  UPDATE person_item_count
  SET item_count = item_count + subquery.count
  FROM (
    SELECT id_person, COUNT(DISTINCT(id_item)) AS count
    FROM person_face_view
    WHERE id_item = item_id AND id_user = user_id
    GROUP BY id_person
  ) AS subquery
  WHERE person_item_count.id_person = subquery.id_person;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.decrease_concept_album_count_for_delete_bg_task(task_id integer, user_id integer)
RETURNS VOID AS
$$
DECLARE
  temp_table_name TEXT;
  query TEXT;
BEGIN
  temp_table_name := 'temp_table_for_delete_task_' || task_id;
  query := 'UPDATE concept_album_additional
  SET item_count = item_count - subquery.count
  FROM (
    SELECT id_concept, m.id_user, COUNT(*) AS count
    FROM many_unit_has_many_concept m
    JOIN unit ON unit.id = m.id_unit
    WHERE m.id_user = ' || user_id || ' AND unit.is_major AND unit.id_item IN (' || 'SELECT id FROM ' || quote_ident(temp_table_name) ||')
    GROUP BY id_concept, m.id_user
  ) AS subquery
  WHERE concept_album_additional.id_concept = subquery.id_concept AND concept_album_additional.id_user = subquery.id_user;';
  EXECUTE query;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.increase_concept_album_count_by_item_id(item_id integer, user_id integer)
RETURNS VOID AS
$$
BEGIN
  UPDATE concept_album_additional
  SET item_count = item_count + subquery.count
  FROM (
    SELECT id_concept, id_user, COUNT(*) AS count
    FROM many_unit_has_many_concept m
    JOIN unit ON unit.id = m.id_unit
    WHERE m.id_user = user_id AND unit.is_major AND unit.id_item = item_id
    GROUP BY id_concept
  ) AS subquery
  WHERE concept_album_additional.id_concept = subquery.id_concept AND concept_album_additional.id_user = user_id;
END;
$$ LANGUAGE plpgsql;