DROP TABLE IF EXISTS public.one_filter_has_many_unit CASCADE;
CREATE TABLE IF NOT EXISTS public.one_filter_has_many_unit AS
  SELECT
    u.id_unit id_unit,
    aperture.id_filter id_aperture,
    camera.id_filter id_camera,
    exposure.id_filter id_exposure_time,
    flash.id_filter id_flash,
    focal.id_filter id_focal_length,
    folder.id_filter id_folder,
    iso.id_filter id_iso,
    item.id_filter id_item_type,
    lens.id_filter id_lens,
    takentime.id_filter id_takentime,
    u.id_user id_user
  FROM (
    SELECT id_unit, id_user FROM filter GROUP BY id_unit, id_user
  ) u
  LEFT JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'aperture'
  ) aperture
  ON u.id_unit = aperture.id_unit
  LEFT JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'camera'
  ) camera
  ON u.id_unit = camera.id_unit
  LEFT JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'exposure_time'
  ) exposure
  ON u.id_unit = exposure.id_unit
  LEFT JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'flash'
  ) flash
  ON u.id_unit = flash.id_unit
  LEFT JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'focal_length'
  ) focal
  ON u.id_unit = focal.id_unit
  LEFT JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'folder'
  ) folder
  ON u.id_unit = folder.id_unit
  LEFT JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'iso'
  ) iso
  ON u.id_unit = iso.id_unit
  LEFT JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'item_type'
  ) item
  ON u.id_unit = item.id_unit
  LEFT JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'lens'
  ) lens
  ON u.id_unit = lens.id_unit
  LEFT JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'takentime'
  ) takentime
  ON u.id_unit = takentime.id_unit;
ALTER TABLE public.one_filter_has_many_unit
  DROP CONSTRAINT IF EXISTS one_filter_has_many_unit_pk,
  DROP CONSTRAINT IF EXISTS unit_fk;
ALTER TABLE public.one_filter_has_many_unit
  ADD CONSTRAINT one_filter_has_many_unit_pk PRIMARY KEY (id_unit),
  ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit) REFERENCES unit (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
DROP FUNCTION IF EXISTS public.update_one_filter_has_many_unit_func() CASCADE;
CREATE FUNCTION public.update_one_filter_has_many_unit_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  one_filter_has_many_unit_table text;
  target_column text;
  filter_type text;
BEGIN
  filter_type = COALESCE(NEW.filter_type, OLD.filter_type);
  IF (filter_type IN ('aperture', 'camera', 'exposure_time', 'flash', 'focal_length', 'folder', 'iso', 'item_type', 'lens', 'takentime')) THEN
    one_filter_has_many_unit_table = TG_TABLE_SCHEMA || '.one_filter_has_many_unit';
    target_column = 'id_' || filter_type;
    IF (TG_OP = 'INSERT') THEN
      EXECUTE format('INSERT INTO %s (id_unit, %s, id_user) VALUES (%L, %L, %L) ON CONFLICT (id_unit) DO UPDATE SET %s = %L', one_filter_has_many_unit_table, target_column, NEW.id_unit, NEW.id_filter, NEW.id_user, target_column, NEW.id_filter);
    ELSIF (TG_OP = 'DELETE') THEN
      EXECUTE format('UPDATE %s SET %s = NULL WHERE id_unit = %L', one_filter_has_many_unit_table, target_column, OLD.id_unit);
    ELSIF (TG_OP = 'UPDATE') THEN
      EXECUTE format('UPDATE %s SET %s = %L WHERE id_unit = %L AND id_user = %L', one_filter_has_many_unit_table, target_column, NEW.id_filter, NEW.id_unit, NEW.id_user);
    END IF;
  END IF;
  RETURN NULL;
END;
$$;
DROP TRIGGER IF EXISTS update_one_filter_has_many_unit_trigger ON public.filter CASCADE;
CREATE TRIGGER update_one_filter_has_many_unit_trigger
  AFTER INSERT OR DELETE OR UPDATE
  ON public.filter
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_one_filter_has_many_unit_func();
DROP TABLE IF EXISTS public.many_unit_has_many_administrative CASCADE;
CREATE TABLE public.many_unit_has_many_administrative AS
  SELECT
    u.id_unit AS id_unit,
    administrative.id_filter AS id_administrative,
    u.id_user AS id_user
  FROM (
    SELECT id_unit, id_user FROM filter GROUP BY id_unit, id_user
  ) u
  INNER JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'administrative'
  ) administrative
  ON u.id_unit = administrative.id_unit;
ALTER TABLE public.many_unit_has_many_administrative
  DROP CONSTRAINT IF EXISTS many_unit_has_many_administrative_pk,
  DROP CONSTRAINT IF EXISTS unit_fk;
ALTER TABLE public.many_unit_has_many_administrative
  ADD CONSTRAINT many_unit_has_many_administrative_pk PRIMARY KEY (id_unit, id_administrative),
  ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit) REFERENCES unit (id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE;
DROP FUNCTION IF EXISTS public.update_many_unit_has_many_administrative_func() CASCADE;
CREATE FUNCTION public.update_many_unit_has_many_administrative_func()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  many_unit_has_many_administrative_table text;
  filter_type text;
BEGIN
  filter_type = COALESCE(NEW.filter_type, OLD.filter_type);
  IF (filter_type = 'administrative') THEN
    many_unit_has_many_administrative_table = TG_TABLE_SCHEMA || '.many_unit_has_many_administrative';
    IF (TG_OP = 'INSERT') THEN
      EXECUTE format('INSERT INTO %s (id_unit, id_administrative, id_user) VALUES (%L, %L, %L) ON CONFLICT (id_unit, id_administrative) DO NOTHING', many_unit_has_many_administrative_table, NEW.id_unit, NEW.id_filter, NEW.id_user);
    ELSIF (TG_OP = 'DELETE') THEN
      EXECUTE format('DELETE FROM %s WHERE id_unit = %L and id_administrative = %L', many_unit_has_many_administrative_table, OLD.id_unit, OLD.id_filter);
    ELSIF (TG_OP = 'UPDATE') THEN
      EXECUTE format('UPDATE %s SET id_administrative = %L WHERE id_unit = %L AND id_user = %L', many_unit_has_many_administrative_table, NEW.id_filter, NEW.id_unit, NEW.id_user);
    END IF;
  END IF;
  RETURN NULL;
END;
$$;
DROP TRIGGER IF EXISTS update_many_unit_has_many_administrative_trigger ON public.filter CASCADE;
CREATE TRIGGER update_many_unit_has_many_administrative_trigger
  AFTER INSERT OR DELETE OR UPDATE
  ON public.filter
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_many_unit_has_many_administrative_func();
DROP TABLE IF EXISTS public.person_item_count CASCADE;
CREATE TABLE public.person_item_count AS
  SELECT face.id_person,
    face.id_user,
    count(DISTINCT unit.id_item) AS item_count
  FROM face,
    unit
  WHERE unit.id = face.id_unit AND unit.id_user = face.id_user AND unit.is_major = true AND face.id_person IS NOT NULL
  GROUP BY face.id_person, face.id_user;
ALTER TABLE public.person_item_count DROP CONSTRAINT IF EXISTS person_item_count_pk;
ALTER TABLE public.person_item_count ADD CONSTRAINT person_item_count_pk PRIMARY KEY (id_person, id_user);
DROP FUNCTION IF EXISTS public.update_person_item_count_func() CASCADE;
CREATE FUNCTION public.update_person_item_count_func()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
BEGIN
  INSERT INTO public.person_item_count
    SELECT face.id_person, face.id_user, count(DISTINCT unit.id_item) AS item_count
			FROM face,
        unit
      WHERE unit.id = face.id_unit AND unit.id_user = face.id_user AND unit.is_major = true AND
        face.id_person IN (SELECT id_person FROM new_face UNION ALL SELECT id_person FROM old_face)
    GROUP BY face.id_person, face.id_user
  ON CONFLICT (id_person, id_user) DO UPDATE SET item_count = excluded.item_count;
	DELETE FROM public.person_item_count WHERE id_person IN (SELECT id_person FROM old_face EXCEPT SELECT id_person FROM face);
  RETURN NULL;
END;
$$;
DROP TRIGGER IF EXISTS update_person_item_count_trigger ON public.face CASCADE;
CREATE TRIGGER update_person_item_count_trigger
  AFTER UPDATE
  ON public.face
  REFERENCING
    NEW TABLE AS new_face
    OLD TABLE AS old_face
  FOR EACH STATEMENT EXECUTE PROCEDURE public.update_person_item_count_func();
DROP FUNCTION IF EXISTS public.delete_person_item_count_func() CASCADE;
CREATE FUNCTION public.delete_person_item_count_func()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  new_count integer;
BEGIN
  SELECT count(DISTINCT unit.id_item) AS item_count FROM face,
    unit
  WHERE unit.id = OLD.id_unit AND unit.id_user = OLD.id_user AND face.id_person = OLD.id_person
  GROUP BY OLD.id_person, OLD.id_user
  INTO new_count;
  IF (new_count > 0) THEN
    UPDATE public.person_item_count SET item_count = new_count WHERE id_person = OLD.id_person AND id_user = OLD.id_user;
  ELSE
    DELETE FROM public.person_item_count WHERE id_person = OLD.id_person AND id_user = OLD.id_user;
  END IF;
  RETURN NULL;
END;
$$;
DROP TRIGGER IF EXISTS delete_person_item_count_trigger ON public.face CASCADE;
CREATE TRIGGER delete_person_item_count_trigger
  AFTER DELETE
  ON public.face
  FOR EACH ROW EXECUTE PROCEDURE public.delete_person_item_count_func();
DROP VIEW IF EXISTS public.person_album_view;
CREATE VIEW public.person_album_view AS
  SELECT person_item_count.id_person,
    person.id_user,
    person.name,
    person.hidden,
    person.cover,
    person_item_count.item_count,
    person.normalized_name
   FROM person,
    person_item_count
  WHERE person_item_count.id_person = person.id AND person_item_count.id_user = person.id_user;
DROP TABLE IF EXISTS public.many_unit_has_many_person CASCADE;
CREATE TABLE public.many_unit_has_many_person AS
  SELECT
    u.id_unit AS id_unit,
    person.id_filter AS id_person,
    u.id_user AS id_user
  FROM (
    SELECT id_unit, id_user FROM filter GROUP BY id_unit, id_user
  ) u
  INNER JOIN (
    SELECT id_unit, id_filter FROM filter WHERE filter_type = 'person'
  ) person
  ON person.id_unit = u.id_unit;
ALTER TABLE public.many_unit_has_many_person
  DROP CONSTRAINT IF EXISTS many_unit_has_many_person_pk,
  DROP CONSTRAINT IF EXISTS unit_fk;
ALTER TABLE public.many_unit_has_many_person
  ADD CONSTRAINT many_unit_has_many_person_pk PRIMARY KEY (id_unit, id_person),
  ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit) REFERENCES unit (id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
DROP FUNCTION IF EXISTS public.update_many_unit_has_many_person_func() CASCADE;
CREATE FUNCTION public.update_many_unit_has_many_person_func()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  many_unit_has_many_person_table text;
BEGIN
  IF (NEW.filter_type = 'person') THEN
    many_unit_has_many_person_table = TG_TABLE_SCHEMA || '.many_unit_has_many_person';
    IF (TG_OP = 'INSERT') THEN
      EXECUTE format('INSERT INTO %s (id_unit, id_person, id_user) VALUES (%L, %L, %L) ON CONFLICT (id_unit, id_person) DO NOTHING', many_unit_has_many_person_table, NEW.id_unit, NEW.id_filter, NEW.id_user);
    ELSIF (TG_OP = 'DELETE') THEN
      EXECUTE format('DELETE FROM %s WHERE id_unit = %L and id_person = %L', many_unit_has_many_person, OLD.id_unit, OLD.id_filter);
    ELSIF (TG_OP = 'UPDATE') THEN
      EXECUTE format('UPDATE %s SET id_person = %L WHERE id_unit = %L AND id_user = %L', many_unit_has_many_person, NEW.id_filter, NEW.id_unit, NEW.id_user);
    END IF;
  END IF;
  RETURN NULL;
END;
$$;
DROP TRIGGER IF EXISTS update_many_unit_has_many_person_trigger ON public.filter CASCADE;
CREATE TRIGGER update_many_unit_has_many_person_trigger
  AFTER INSERT OR DELETE OR UPDATE
  ON public.filter
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_many_unit_has_many_person_func();