CREATE TABLE IF NOT EXISTS public.async_db_migration_task (
    id SERIAL PRIMARY KEY,
    id_user INTEGER NOT NULL,
    target_version INTEGER NOT NULL,
    payload JSON
);
ALTER TABLE public.async_db_migration_task
  DROP CONSTRAINT IF EXISTS async_db_migration_task_user_fk;
ALTER TABLE public.async_db_migration_task
  ADD CONSTRAINT async_db_migration_task_user_fk FOREIGN KEY (id_user) REFERENCES public.user_info (id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.metadata
  ADD COLUMN IF NOT EXISTS normalized_description text NOT NULL DEFAULT '';
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');
    -- collect all index_def fist and drop duplicate indexes
    DECLARE
      idx_seen TEXT[] := ARRAY[]::TEXT[];
    BEGIN
      FOR r IN SELECT indexname AS index_name, split_part(indexdef, 'USING ', 2) AS index_def FROM pg_indexes WHERE tablename = table_name LOOP
        IF r.index_def = ANY(idx_seen) THEN
          -- found existed, drop index
          EXECUTE format('DROP INDEX IF EXISTS %I', r.index_name);
        ELSE
          -- not found, rename to production index name
          production_index_name := (SELECT index_name FROM production_table_index_mapping WHERE tb_name = table_name AND index_def = r.index_def LIMIT 1);
          EXECUTE format('ALTER INDEX %I RENAME TO %s', r.index_name, production_index_name);
          idx_seen := array_append(idx_seen, r.index_def);
        END IF;
      END LOOP;
    END;
  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
$$;
DROP INDEX IF EXISTS face_unit_face_list_idx;
CREATE INDEX IF NOT EXISTS face_unit_face_list_idx ON public.face
(
  id_user,
  id_unit,
  id
) WHERE is_manual = false;
DROP INDEX IF EXISTS face_migration_id_user_id_unit_id_idx;
DO $$
BEGIN
  IF to_regclass('public.face_migration') IS NOT NULL THEN
    EXECUTE 'CREATE INDEX IF NOT EXISTS face_migration_id_user_id_unit_id_idx ON public.face_migration (id_user, id_unit, id) WHERE is_manual = false';
  END IF;
END
$$;