CREATE OR REPLACE FUNCTION public.redefine_migration_constraint_func(production_tables TEXT[])
  RETURNS VOID
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  table_name TEXT;
  r RECORD;
  referenced_table_name TEXT;
BEGIN
  CREATE TEMP TABLE _migration_table_fk_def (
    tb_name TEXT,
    fk_name TEXT,
    fk_def TEXT
  );
  -- change the definition of foreign key constraint to *_migration table
  FOR table_name IN SELECT unnest(production_tables) LOOP
    -- insert table name, fk name and fk defination into _migration_table_fk_def
    INSERT INTO _migration_table_fk_def (tb_name, fk_name, fk_def)
    SELECT CAST(conrelid::regclass AS text) AS tb_name,
           conname AS fk_name,
           pg_get_constraintdef(c.oid) AS fk_def
    FROM pg_constraint c JOIN pg_namespace n ON n.oid = c.connamespace
    WHERE CAST(conrelid::regclass AS text) = table_name AND contype = 'f';
    -- if referenced table is in production_tables, rename it to `referenced_table_name`_migration
    FOR r IN SELECT * FROM _migration_table_fk_def WHERE tb_name = table_name LOOP
      -- return `REFERENCES` and `referenced_table_name` from fk_def, something like: "REFERENCES person_group"
      referenced_table_name := (SELECT substring(r.fk_def, '(REFERENCES[[:space:]]+[A-Za-z0-9_]+)'));
      if split_part(referenced_table_name, ' ', 2) IN (SELECT unnest(production_tables)) THEN
        UPDATE _migration_table_fk_def
        SET fk_def = replace(fk_def, referenced_table_name, referenced_table_name || '_migration')
        WHERE fk_name = r.fk_name;
      END IF;
    END LOOP;
    FOR r IN SELECT * FROM _migration_table_fk_def WHERE tb_name = table_name LOOP
      EXECUTE format('ALTER TABLE %s_migration DROP CONSTRAINT IF EXISTS %I', table_name, r.fk_name);
      EXECUTE format('ALTER TABLE %s_migration ADD CONSTRAINT %I %s', table_name, r.fk_name, r.fk_def);
    END LOOP;
  END LOOP;
  DROP TABLE  _migration_table_fk_def;
END
$$;
CREATE OR REPLACE FUNCTION public.create_production_index_mapping_func(production_tables TEXT[])
  RETURNS VOID
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  table_name TEXT;
BEGIN
  CREATE TABLE IF NOT EXISTS production_table_index_mapping (
      tb_name TEXT,
      index_name TEXT,
      index_def TEXT
  );
  TRUNCATE production_table_index_mapping;
  FOR table_name IN SELECT unnest(production_tables) LOOP
    INSERT INTO production_table_index_mapping (tb_name, index_name, index_def)
      SELECT tablename, indexname,
              split_part(indexdef, 'USING ', 2)
      FROM pg_indexes
      WHERE tablename = table_name;
  END LOOP;
END
$$;
CREATE OR REPLACE FUNCTION public.create_person_migration_migration_tables_func()
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  production_tables CONSTANT TEXT[] := ARRAY['face', 'person_group', 'cluster', 'person'];
  table_name TEXT;
BEGIN
  FOR table_name IN SELECT unnest(production_tables) LOOP
    EXECUTE format('CREATE TABLE IF NOT EXISTS %s_migration (LIKE %s INCLUDING ALL)', table_name, table_name);
    EXECUTE format('TRUNCATE %s_migration', table_name);
  END LOOP;
  PERFORM public.redefine_migration_constraint_func(production_tables);
  PERFORM public.create_production_index_mapping_func(production_tables);
  DROP TRIGGER IF EXISTS face_delete_unit_version_trigger ON public.face_migration CASCADE;
  CREATE TRIGGER face_update_unit_version_trigger
    AFTER UPDATE
    ON public.face_migration
    FOR EACH ROW
    EXECUTE PROCEDURE public.face_update_unit_version_func();
END
$$;
CREATE OR REPLACE FUNCTION public.remove_person_migration_tables_id_sequence_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
  ALTER TABLE IF EXISTS face_migration
    ALTER COLUMN id DROP DEFAULT;
  ALTER TABLE IF EXISTS person_group_migration
    ALTER COLUMN id DROP DEFAULT;
  ALTER TABLE IF EXISTS cluster_migration
    ALTER COLUMN id DROP DEFAULT;
  ALTER TABLE IF EXISTS person_migration
    ALTER COLUMN id DROP DEFAULT;
END;
$$;
CREATE OR REPLACE FUNCTION public.read_only_table()
RETURNS TRIGGER
LANGUAGE plpgsql
VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
    RAISE EXCEPTION 'Table is read-only';
END;
$$;
CREATE OR REPLACE FUNCTION public.create_read_only_trigger_func(table_name text)
RETURNS VOID
LANGUAGE plpgsql
SECURITY INVOKER
COST 1
AS $$
BEGIN
  EXECUTE format('CREATE TRIGGER read_only_trigger
  BEFORE INSERT OR UPDATE ON %I
  FOR EACH STATEMENT
  EXECUTE PROCEDURE public.read_only_table()', table_name);
END;
$$;
CREATE OR REPLACE FUNCTION public.drop_read_only_trigger_func(table_name text)
RETURNS VOID
LANGUAGE plpgsql
SECURITY INVOKER
COST 1
AS $$
BEGIN
  EXECUTE format('DROP TRIGGER IF EXISTS read_only_trigger ON %I CASCADE', table_name);
END;
$$;
CREATE OR REPLACE FUNCTION public.set_person_table_read_only_top_down_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
  PERFORM public.drop_read_only_trigger_func('face');
  PERFORM public.drop_read_only_trigger_func('person_group');
  PERFORM public.drop_read_only_trigger_func('cluster');
  PERFORM public.drop_read_only_trigger_func('person');
  PERFORM public.create_read_only_trigger_func('face');
  PERFORM public.create_read_only_trigger_func('person_group');
  PERFORM public.create_read_only_trigger_func('cluster');
  PERFORM public.create_read_only_trigger_func('person');
END
$$;
CREATE OR REPLACE FUNCTION public.unset_person_table_read_only_top_down_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
  PERFORM public.drop_read_only_trigger_func('face');
  PERFORM public.drop_read_only_trigger_func('person_group');
  PERFORM public.drop_read_only_trigger_func('cluster');
  PERFORM public.drop_read_only_trigger_func('person');
END
$$;
CREATE OR REPLACE FUNCTION public.set_person_migration_original_tables_backup_func()
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  production_tables CONSTANT TEXT[] := ARRAY['face', 'person_group', 'cluster', 'person'];
  var_table_name TEXT;
  r RECORD;
BEGIN
  FOR var_table_name IN SELECT unnest(production_tables) LOOP
    EXECUTE format('DROP TABLE IF EXISTS %s_backup CASCADE', var_table_name);
    EXECUTE format('ALTER SEQUENCE %s_id_seq RENAME TO %s_id_seq_backup', var_table_name, var_table_name);
    FOR r in SELECT indexname AS index_name FROM pg_indexes WHERE tablename = var_table_name LOOP
      EXECUTE format('ALTER INDEX %I RENAME TO %s_backup', r.index_name, r.index_name);
    END LOOP;
    FOR r in SELECT constraint_name
             FROM information_schema.table_constraints
             WHERE table_name = var_table_name AND constraint_type = 'FOREIGN KEY'
    LOOP
      EXECUTE format('ALTER TABLE %I DROP CONSTRAINT %I CASCADE', var_table_name, r.constraint_name);
    END LOOP;
    FOR r in SELECT DISTINCT trigger_name FROM information_schema.triggers WHERE event_object_table = var_table_name LOOP
      EXECUTE format('DROP TRIGGER %I ON %I CASCADE', r.trigger_name, var_table_name);
    END LOOP;
    FOR r in SELECT rulename AS rule_name FROM pg_rewrite pr JOIN pg_class c
          ON c.oid = pr.ev_class WHERE c.relname = var_table_name
    LOOP
      EXECUTE format('DROP RULE %I ON %I CASCADE', r.rule_name, var_table_name);
    END LOOP;
    IF var_table_name = 'face' OR var_table_name = 'person_group' THEN
      EXECUTE format('ALTER TABLE %I DROP COLUMN feature', var_table_name);
    END IF;
    EXECUTE format('ALTER TABLE %I RENAME TO %s_backup', var_table_name, var_table_name);
  END LOOP;
END
$$;
CREATE OR REPLACE FUNCTION public.set_person_migration_tables_func()
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  production_tables CONSTANT TEXT[] := ARRAY['face', 'person_group', 'cluster', 'person'];
  table_name TEXT;
  r RECORD;
  production_index_name TEXT;
BEGIN
  FOR table_name IN SELECT unnest(production_tables) LOOP
    EXECUTE format('ALTER TABLE %s_migration RENAME TO %s', table_name, table_name);
    EXECUTE format('CREATE SEQUENCE %s_id_seq OWNED BY %I.id', table_name, table_name);
    EXECUTE format('SELECT setval(%L, max(id)) FROM %I', table_name || '_id_seq', table_name);
    EXECUTE format('ALTER TABLE %I ALTER COLUMN id SET DEFAULT nextval(%L)', table_name, table_name || '_id_seq');
    FOR r in SELECT indexname AS index_name,
                    split_part(indexdef, 'USING ', 2) AS index_def
                    FROM pg_indexes WHERE tablename = table_name LOOP
      production_index_name := (SELECT index_name FROM production_table_index_mapping WHERE tb_name = table_name AND index_def = r.index_def);
      EXECUTE format('ALTER INDEX %I RENAME TO %s', r.index_name, production_index_name);
    END LOOP;
  END LOOP;
  DROP TABLE production_table_index_mapping;
  CREATE TRIGGER delete_face_trigger
    AFTER DELETE
    ON public.face
    FOR EACH ROW
    EXECUTE PROCEDURE public.delete_face_trigger_func();
  CREATE TRIGGER update_face_trigger
    AFTER UPDATE
    ON public.face
    FOR EACH ROW
    EXECUTE PROCEDURE public.update_face_func();
  DROP RULE IF EXISTS drop_face_lo_on_update ON public.face CASCADE;
  CREATE RULE drop_face_lo_on_update AS ON UPDATE
    TO public.face
    WHERE NEW.picture != OLD.picture
    DO ALSO (SELECT lo_unlink(OLD.picture));
  DROP RULE IF EXISTS drop_face_lo_on_delete ON public.face CASCADE;
  CREATE RULE drop_face_lo_on_delete AS ON DELETE
    TO public.face
    DO ALSO (SELECT lo_unlink(OLD.picture));
  DROP TABLE IF EXISTS face_migration_manual_map;
END
$$;
CREATE OR REPLACE FUNCTION public.delete_redundant_migration_person_data_func(user_id integer)
RETURNS VOID AS
$$
BEGIN
  DELETE FROM person_group_migration
  WHERE id_user = user_id AND
        NOT EXISTS (SELECT 1 FROM face_migration WHERE face_migration.id_person_group = person_group_migration.id);
  DELETE FROM cluster_migration
  WHERE id_user = user_id AND
        NOT EXISTS (SELECT 1 FROM person_group_migration WHERE person_group_migration.id_cluster = cluster_migration.id);
  DELETE FROM person_migration
  WHERE id_user = user_id AND
        NOT EXISTS (SELECT 1 FROM cluster_migration WHERE cluster_migration.id_person = person_migration.id);
END;
$$ LANGUAGE plpgsql;
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_fk,
  ADD CONSTRAINT person_fk FOREIGN KEY (id_person) REFERENCES person(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
END
$$;
CREATE OR REPLACE FUNCTION public.define_person_album_view_func()
  RETURNS VOID
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
BEGIN
  CREATE OR REPLACE 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;
END
$$;
CREATE OR REPLACE FUNCTION public.define_person_face_view_func()
  RETURNS VOID
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
BEGIN
  CREATE OR REPLACE VIEW public.person_face_view
  AS
    SELECT
      face.id,
      face.id_user,
      id_person,
      id_unit,
      score,
      is_major,
      id_item
    FROM face, unit
  WHERE unit.id = face.id_unit AND unit.id_user = face.id_user;
END
$$;
CREATE OR REPLACE FUNCTION public.define_person_timeline_view_func()
  RETURNS VOID
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
BEGIN
  CREATE OR REPLACE VIEW public.person_timeline_view
  AS
    SELECT
      u.id_item,
      u.id_user,
      u.item_type,
      u.type AS unit_type,
      u.id AS id_unit,
      array_agg(DISTINCT f.id_person) AS id_person,
      u.takentime
    FROM unit u
    JOIN face f ON u.id = f.id_unit
  WHERE u.is_major = true
  GROUP BY u.id_user, u.id;
END
$$;
CREATE OR REPLACE FUNCTION public.create_face_migration_manual_map_table_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
  CREATE TABLE IF NOT EXISTS face_migration_manual_map
  AS
    SELECT
      id,
      id_user,
      false AS visited FROM face_migration WHERE is_manual = true;
  IF NOT EXISTS (
    SELECT
      constraint_name
    FROM
      information_schema.table_constraints
    WHERE
      table_name = 'face_migration_manual_map' AND constraint_type = 'PRIMARY KEY'
  ) THEN
    ALTER TABLE face_migration_manual_map ADD PRIMARY KEY (id);
  END IF;
END
$$;