#399

Reviewed-on: #408
Co-authored-by: miteruzo <miteruzo@naver.com>
Co-committed-by: miteruzo <miteruzo@naver.com>

backend/app/controllers/post_imports_controller.rb

@@ -0,0 +1,107 @@
+class PostImportsController < ApplicationController
+  before_action :require_member!
+
+  def parse
+    source =
+        if params[:format] == 'google_sheets'
+          PostImportGoogleSheetsFetcher.fetch!(params[:source], rate_key: current_user.id)
+        else
+          params[:source]
+        end
+    format = params[:format] == 'google_sheets' ? 'csv' : params[:format]
+    parsed = PostImportSourceParser.new(source:, format:,
+                                        has_header: params[:has_header], json_path: params[:json_path]).parse
+    render json: parsed
+  rescue ArgumentError => e
+    render_bad_request(e.message)
+  end
+
+  def preview
+    parsed = parsed_params
+    url_column = params[:url_column].presence || (parsed[:columns].length == 1 ? 0 : nil)
+    return render_bad_request('URL 列を選択してください．') if url_column.nil?
+
+    rows = PostImportPreviewer.new(parsed:, url_column:, mappings: params[:mappings],
+                                   existing: params[:existing]).preview
+    render json: parsed.slice(:columns).merge(rows:)
+  rescue ArgumentError => e
+    render_bad_request(e.message)
+  end
+
+  def validate
+    rows = validate_rows_params
+    changed_row = Integer(params[:changed_row], exception: false)
+    result = PostImportPreviewer.new(parsed: { columns: [], rows: [] }, url_column: 0,
+                                     mappings: {}, existing: params[:existing]).preview_rows(
+                                         rows:, fetch_metadata: changed_row,
+                                         metadata_cache: { })
+    render json: { rows: result }
+  rescue ArgumentError => e
+    render_bad_request(e.message)
+  end
+
+  def create
+    result = PostImportRunner.new(actor: current_user, rows: params[:rows].to_a,
+                                  existing: params[:existing]).run
+    render json: result, status: result[:created].positive? ? :created : :ok
+  rescue ArgumentError => e
+    render_bad_request(e.message)
+  end
+
+  private
+
+  def require_member!
+    return head :unauthorized unless current_user
+    return if current_user.gte_member?
+
+    head :forbidden
+  end
+
+  def parsed_params
+    value = params.require(:parsed).to_unsafe_h.deep_transform_keys { _1.to_s.underscore }
+    columns = Array(value['columns'])
+    rows = Array(value['rows'])
+    raise ArgumentError, '解析結果の形式が不正です．' unless columns.all?(Hash) && rows.all?(Hash)
+    raise ArgumentError, '取込件数が多すぎます．' if rows.length > PostImportSourceParser::MAX_ROWS
+    raise ArgumentError, '列数が多すぎます．' if columns.length > 50
+    unless rows.all? { |row| Array(row['values']).length <= columns.length &&
+                               Array(row['values']).all? { _1.is_a?(String) && _1.bytesize <= PostImportSourceParser::MAX_CELL_BYTES } }
+      raise ArgumentError, '解析結果のセルが不正です．'
+    end
+    raise ArgumentError, '解析結果が大きすぎます．' if rows.sum { Array(_1['values']).sum(&:bytesize) } > PostImportSourceParser::MAX_BYTES
+
+    { columns: columns.map { |column| column.slice('key', 'label') },
+      rows: rows.map { |row| { source_row: row['source_row'], values: Array(row['values']) } } }
+  end
+
+  def validate_rows_params
+    rows = Array(params[:rows])
+    raise ArgumentError, '取込件数が多すぎます．' if rows.length > PostImportSourceParser::MAX_ROWS
+
+    rows.map do |row|
+      value = ActionController::Parameters.new(row).permit(:source_row, :sourceRow, :url,
+                                                            attributes: {}, provenance: {}, tag_sources: {},
+                                                            fetch_warnings: [], validation_warnings: [])
+      normalised = value.to_h.deep_transform_keys { _1.to_s.underscore }
+      attributes = normalised.fetch('attributes', { })
+      provenance = normalised.fetch('provenance', { })
+      allowed = PostImportPreviewer::FIELDS
+      unless attributes.is_a?(Hash) && provenance.is_a?(Hash) &&
+             (attributes.keys - allowed).empty? &&
+             (provenance.keys - (allowed + ['url'])).empty?
+        raise ArgumentError, '行の形式が不正です．'
+      end
+      unless provenance.values.all? { _1.in?(['automatic', 'mapped', 'fixed', 'manual']) }
+        raise ArgumentError, '値の由来が不正です．'
+      end
+      raise ArgumentError, 'セルが大きすぎます．' if [normalised['url'], *attributes.values].any? { _1.to_s.bytesize > PostImportSourceParser::MAX_CELL_BYTES }
+      if normalised['tag_sources'].present? &&
+         (!normalised['tag_sources'].is_a?(Hash) ||
+          (normalised['tag_sources'].keys - ['automatic', 'mapped', 'fixed', 'manual']).present?)
+        raise ArgumentError, 'タグ由来の形式が不正です．'
+      end
+
+      normalised.symbolize_keys
+    end
+  end
+end

backend/app/controllers/posts_controller.rb

@@ -142,37 +142,13 @@ class PostsController < ApplicationController
     return head :unauthorized unless current_user
     return head :forbidden unless current_user.gte_member?
 
-    # TODO: サイトに応じて thumbnail_base 設定
-    title = params[:title].presence
-    url = params[:url]
-    thumbnail = params[:thumbnail]
-    tag_names = params[:tags].to_s.split
-    original_created_from = params[:original_created_from]
-    original_created_before = params[:original_created_before]
-    parent_post_ids = parse_parent_post_ids
-    resized_thumbnail = thumbnail.present? ? Post.resized_thumbnail_attachment(thumbnail) : nil
-
-    post = Post.new(title:, url:, thumbnail_base: nil, uploaded_user: current_user,
-                    original_created_from:, original_created_before:)
-    post.thumbnail.attach(resized_thumbnail) if resized_thumbnail
-
-    ApplicationRecord.transaction do
-      post.save!
-
-      Tag.normalise_tags!(tag_names, deny_deprecated: true, with_sections: true) =>
-          { tags:, sections: }
-      TagVersioning.record_tag_snapshots!(tags, created_by_user: current_user)
-
-      tags = Tag.expand_parent_tags(tags).reject(&:deprecated?)
-      post.video_ms = normalise_video_ms(tags)
-      validate_video_sections!(post.video_ms, sections)
-      post.save!
-      sync_post_tags!(post, tags, sections)
-
-      sync_parent_posts!(post, parent_post_ids)
-
-      PostVersionRecorder.record!(post:, event_type: :create, created_by_user: current_user)
-    end
+    post = PostCreator.new(actor: current_user,
+                           attributes: { title: params[:title], url: params[:url],
+                                         thumbnail: params[:thumbnail], tags: params[:tags],
+                                         original_created_from: params[:original_created_from],
+                                         original_created_before: params[:original_created_before],
+                                         parent_post_ids: parse_parent_post_ids,
+                                         video_ms: params[:video_ms], duration: params[:duration] }).create!
 
     post.reload
     render json: PostRepr.base(post), status: :created
@@ -182,7 +158,7 @@ class PostsController < ApplicationController
     render_unprocessable_entity '廃止済みタグは付与できません．', field: :tags
   rescue Tag::SectionLiteralParseError
     render_validation_error fields: { tags: ['タグ区間の記法が不正です．'] }
-  rescue VideoMsParseError
+  rescue PostCreator::VideoMsParseError
     render_validation_error fields: { video_ms: ['動画時間の記法が不正です．'] }
   rescue MiniMagick::Error
     render_validation_error fields: { thumbnail: ['サムネイル画像の変換に失敗しました．'] }

backend/app/controllers/tags_controller.rb

@@ -200,45 +200,14 @@ class TagsController < ApplicationController
              .find_by(id: params[:id])
     return head :not_found unless tag
 
-    rows = normalise_deerjikist_rows(tag)
-    return if performed?
-
     ApplicationRecord.transaction do
-      tag.lock!
-
-      requested_keys = rows.map { |row| [row[:platform], row[:code]] }.uniq
-      row_indexes_by_key = rows_by_key(rows)
-      locked_deerjikists = lock_deerjikists_for_tag_update(tag.id, requested_keys)
-      current_deerjikists = locked_deerjikists.filter { |deerjikist|
-        deerjikist.tag_id == tag.id
-      }
-      requested_deerjikists = locked_deerjikists.filter { |deerjikist|
-        row_indexes_by_key.key?([deerjikist.platform, deerjikist.code])
-      }
-
-      render_deerjikist_conflicts(requested_deerjikists, row_indexes_by_key, tag)
-      raise ActiveRecord::Rollback if performed?
-
-      requested_keys_set = requested_keys.to_set
-      current_deerjikists.each do |deerjikist|
-        key = [deerjikist.platform, deerjikist.code]
-        deerjikist.destroy! unless requested_keys_set.include?(key)
-      end
-
-      existing_keys = requested_deerjikists.to_h { |deerjikist|
-        [[deerjikist.platform, deerjikist.code], true]
-      }
-      requested_keys.each do |platform, code|
-        next if existing_keys[[platform, code]]
-
-        deerjikist = Deerjikist.new(platform:, code:, tag:)
-        row_index = row_indexes_by_key[[platform, code]].first
-        begin
-          render_deerjikist_form_record_invalid(deerjikist, row_index) unless deerjikist.save
-        rescue ActiveRecord::RecordNotUnique
-          conflicts = lock_deerjikists_for_tag_update(tag.id, [[platform, code]])
-          render_deerjikist_conflicts(conflicts, row_indexes_by_key, tag)
-        end
+      tag.deerjikists = []
+      params[:_json].each.with_index do |item, i|
+        platform = item[:platform]
+        code = normalise_deerjikist_code(platform, item[:code])
+        deerjikist = Deerjikist.find_or_initialize_by(platform:, code:)
+        deerjikist.tag = tag
+        render_deerjikist_form_record_invalid(deerjikist, i) unless deerjikist.save
         raise ActiveRecord::Rollback if performed?
       end
     end
@@ -684,7 +653,6 @@ class TagsController < ApplicationController
   end
 
   def normalise_deerjikist_code platform, code
-    code = code.to_s
     return code if platform != 'youtube' || code[0] != '@'
 
     url = "https://www.youtube.com/#{ code }"
@@ -701,82 +669,6 @@ class TagsController < ApplicationController
     nil
   end
 
-  def normalise_deerjikist_rows tag
-    rows = []
-
-    params[:_json].each.with_index do |item, index|
-      platform = item[:platform]
-      unless Deerjikist.platforms.key?(platform)
-        render_deerjikist_platform_invalid(index)
-        return rows
-      end
-
-      code = normalise_deerjikist_code(platform, item[:code])
-      deerjikist = Deerjikist.new(platform:, code:, tag:)
-      unless deerjikist.valid?
-        render_deerjikist_form_record_invalid(deerjikist, index)
-        return rows
-      end
-
-      rows << { index:, platform:, code: }
-    end
-
-    rows
-  end
-
-  def lock_deerjikists_for_tag_update tag_id, keys
-    clauses = ['tag_id = ?']
-    values = [tag_id]
-
-    keys.each do |platform, code|
-      clauses << '(platform = ? AND code = ?)'
-      values << platform << code
-    end
-
-    Deerjikist
-      .where(clauses.join(' OR '), *values)
-      .order(:platform, :code)
-      .lock
-      .to_a
-  end
-
-  def render_deerjikist_conflicts deerjikists, row_indexes_by_key, tag
-    conflicts = deerjikists.filter { |deerjikist| deerjikist.tag_id != tag.id }
-    return if conflicts.empty?
-
-    tag_names_by_id = Tag
-                      .joins(:tag_name)
-                      .where(id: conflicts.map(&:tag_id).uniq)
-                      .pluck('tags.id', 'tag_names.name')
-                      .to_h
-    fields = { }
-
-    conflicts.each do |deerjikist|
-      message = "この情報は既に「#{ tag_names_by_id[deerjikist.tag_id] }」に紐づいてゐます．"
-      row_indexes_by_key[[deerjikist.platform, deerjikist.code]].each do |index|
-        field = :"deerjikists.#{ index }.code"
-        fields[field] ||= []
-        fields[field] << message
-      end
-    end
-
-    render_validation_error fields:
-  end
-
-  def render_deerjikist_platform_invalid index
-    render_validation_error fields: {
-      :"deerjikists.#{ index }.platform" => ['値が不正です．'],
-    }
-  end
-
-  def rows_by_key rows
-    rows.each_with_object({ }) do |row, result|
-      key = [row[:platform], row[:code]]
-      result[key] ||= []
-      result[key] << row[:index]
-    end
-  end
-
   def render_deerjikist_form_record_invalid deerjikist, index
     fields = { }
 

backend/app/services/post_creator.rb

@@ -0,0 +1,112 @@
+class PostCreator
+  class VideoMsParseError < ArgumentError; end
+
+  def initialize actor:, attributes:
+    @actor = actor
+    @attributes = attributes.symbolize_keys
+  end
+
+  def create!
+    post = Post.new(title: @attributes[:title].presence,
+                    url: @attributes[:url],
+                    thumbnail_base: @attributes[:thumbnail_base].presence,
+                    uploaded_user: @actor,
+                    original_created_from: @attributes[:original_created_from].presence,
+                    original_created_before: @attributes[:original_created_before].presence)
+    thumbnail = @attributes[:thumbnail]
+    post.thumbnail.attach(Post.resized_thumbnail_attachment(thumbnail)) if thumbnail.present?
+
+    ApplicationRecord.transaction do
+      post.save!
+      Tag.normalise_tags!(tag_names, deny_deprecated: true, with_sections: true) =>
+          { tags:, sections: }
+      TagVersioning.record_tag_snapshots!(tags, created_by_user: @actor)
+      tags = Tag.expand_parent_tags(tags).reject(&:deprecated?)
+      post.video_ms = normalise_video_ms(tags)
+      validate_video_sections!(post.video_ms, sections)
+      post.save!
+      sync_post_tags!(post, tags, sections)
+      sync_parent_posts!(post, parent_post_ids)
+      PostVersionRecorder.record!(post:, event_type: :create, created_by_user: @actor)
+    end
+    post
+  end
+
+  private
+
+  def tag_names = @attributes[:tags].to_s.split
+
+  def parent_post_ids
+    Array(@attributes[:parent_post_ids]).flat_map { _1.to_s.split }.map { |token|
+      id = Integer(token, exception: false)
+      raise ArgumentError, "親投稿 Id. が不正です: #{ token }" if id.nil? || id <= 0
+
+      id
+    }.uniq
+  end
+
+  def normalise_video_ms tags
+    return nil unless tags.any? { _1.id == Tag.video.id }
+
+    video_ms = @attributes[:video_ms]
+    if video_ms.present?
+      value = Integer(video_ms, exception: false)
+      raise VideoMsParseError unless value&.positive?
+
+      return value
+    end
+    duration = @attributes[:duration]
+    return nil if duration.blank?
+
+    return duration.to_i if duration.is_a?(Numeric) && duration.to_i.positive?
+
+    value = Tag.time_to_ms!(duration.to_s, tag_name: '動画時間')
+    raise VideoMsParseError unless value.positive?
+
+    value
+  rescue Tag::SectionLiteralParseError
+    raise VideoMsParseError
+  end
+
+  def validate_video_sections! video_ms, sections
+    return unless video_ms
+
+    sections.each_value do |ranges|
+      ranges.each do |begin_ms, end_ms|
+        next if begin_ms < video_ms && (!end_ms || end_ms <= video_ms)
+
+        post = Post.new
+        post.errors.add :video_ms, 'タグ区間が動画時間の範囲外です．'
+        raise ActiveRecord::RecordInvalid, post
+      end
+    end
+  end
+
+  def sync_post_tags! post, desired_tags, sections
+    desired_ids = desired_tags.map(&:id).to_set
+    current_ids = post.tags.pluck(:id).to_set
+    Tag.where(id: desired_ids - current_ids).find_each do |tag|
+      PostTag.create_or_find_by!(post:, tag:, created_user: @actor)
+    end
+    PostTagSection.where(post_id: post.id).destroy_all
+    sections.each do |tag_id, ranges|
+      ranges.each { |begin_ms, end_ms| PostTagSection.create!(post_id: post.id, tag_id:, begin_ms:, end_ms:) }
+    end
+    PostTag.where(post_id: post.id, tag_id: (current_ids - desired_ids).to_a).kept.find_each do |post_tag|
+      post_tag.discard_by!(@actor)
+    end
+  end
+
+  def sync_parent_posts! post, ids
+    if ids.include?(post.id)
+      post.errors.add :parent_post_ids, '自分自身を親投稿にはできません．'
+      raise ActiveRecord::RecordInvalid, post
+    end
+    missing = ids - Post.where(id: ids).pluck(:id)
+    if missing.present?
+      post.errors.add :parent_post_ids, "存在しない親投稿 Id. があります: #{ missing.join(' ') }"
+      raise ActiveRecord::RecordInvalid, post
+    end
+    ids.each { |parent_post_id| PostImplication.create_or_find_by!(post:, parent_post_id:) }
+  end
+end

backend/app/services/post_import_google_sheets_fetcher.rb

@@ -0,0 +1,59 @@
+class PostImportGoogleSheetsFetcher
+  HOST = 'docs.google.com'.freeze
+  PUBLICATION_ID = /\A[A-Za-z0-9_-]+\z/
+  GID = /\A\d+\z/
+  RATE_LIMIT = 10
+  RATE_WINDOW = 1.minute
+
+  def self.fetch! raw_url, rate_key:
+    new(raw_url, rate_key:).fetch!
+  end
+
+  def initialize raw_url, rate_key:
+    @raw_url = raw_url.to_s
+    @rate_key = rate_key.to_s
+  end
+
+  def fetch!
+    publication_id, gid = parse!
+    throttle!
+    csv_url = "https://#{ HOST }/spreadsheets/d/e/#{ publication_id }/pub?gid=#{ gid }&single=true&output=csv"
+    response = Preview::HttpFetcher.fetch(csv_url,
+                                          max_bytes: PostImportSourceParser::MAX_BYTES,
+                                          allowed_hosts: [HOST])
+    raise ArgumentError, 'スプレッドシートを CSV として取得できませんでした．' unless csv?(response)
+
+    response.body
+  rescue Preview::UrlSafety::UnsafeUrl, Preview::HttpFetcher::FetchFailed => e
+    raise ArgumentError, "スプレッドシートを取得できませんでした．ウェブ公開 URL を確認するか、ファイル保存又はコピー貼付けを使用してください: #{ e.message }"
+  end
+
+  private
+
+  def parse!
+    uri = URI.parse(@raw_url)
+    unless uri.scheme == 'https' && uri.host&.downcase == HOST && uri.query.blank?
+      raise ArgumentError, '公開 Google スプレッドシート URL を指定してください．'
+    end
+    match = uri.path.match(%r{\A/spreadsheets/d/e/([^/]+)/pubhtml\z})
+    gid = URI.decode_www_form(uri.fragment.to_s).to_h['gid']
+    unless match && PUBLICATION_ID.match?(match[1]) && GID.match?(gid.to_s)
+      raise ArgumentError, '公開 Google スプレッドシート URL の publication_id 又は gid が不正です．'
+    end
+
+    [match[1], gid]
+  rescue URI::InvalidURIError
+    raise ArgumentError, '公開 Google スプレッドシート URL が不正です．'
+  end
+
+  def throttle!
+    key = "post-import-google-sheets:#{ @rate_key }"
+    count = Rails.cache.increment(key, 1, expires_in: RATE_WINDOW, initial: 0)
+    raise ArgumentError, 'スプレッドシート取得の回数が多すぎます．しばらくしてから再試行してください．' if count > RATE_LIMIT
+  end
+
+  def csv? response
+    type = response.content_type.to_s.downcase
+    type.include?('text/csv') || type.include?('application/csv')
+  end
+end

backend/app/services/post_import_previewer.rb

@@ -0,0 +1,160 @@
+class PostImportPreviewer
+  FIELDS = %w[title thumbnail_base original_created_from original_created_before duration tags parent_post_ids].freeze
+
+  def initialize parsed:, url_column:, mappings: { }, existing: 'skip'
+    @parsed = parsed
+    @url_column = url_column.to_i
+    @mappings = mappings.to_h.stringify_keys
+    @existing = existing == 'error' ? 'error' : 'skip'
+  end
+
+  def preview
+    preview_rows(rows: @parsed[:rows])
+  end
+
+  def preview_rows rows:, fetch_metadata: true, metadata_cache: { }
+    urls = { }
+    rows.map do |row|
+      row = row.symbolize_keys
+      values = row[:values] || []
+      attributes = row[:attributes]&.stringify_keys || FIELDS.to_h { |field| [field, mapped_value(field, values)] }
+      provenance = row[:provenance]&.stringify_keys || mapped_provenance(attributes)
+      tag_sources = row[:tag_sources]&.stringify_keys || initial_tag_sources(attributes, provenance)
+      tag_sources['manual'] = attributes['tags'].to_s if provenance['tags'] == 'manual'
+      url = row[:url].presence || values[@url_column].to_s.strip
+      provenance['url'] ||= row[:url].present? ? 'manual' : 'mapped'
+      attributes['url'] = url
+      fetch_warnings = Array(row[:fetch_warnings]).dup
+      should_fetch = fetch_metadata == true || fetch_metadata.to_i == row[:source_row].to_i
+      metadata, fetch_warnings = metadata_for(url, should_fetch, metadata_cache, fetch_warnings)
+      metadata.each do |field, value|
+        if field == 'tags' && provenance[field] != 'manual'
+          tag_sources['automatic'] = value.to_s
+          attributes[field] = merged_tags(tag_sources)
+          next
+        end
+        next unless provenance[field] == 'automatic' || attributes[field].blank?
+
+        attributes[field] = value
+        provenance[field] = 'automatic'
+      end
+      post = Post.new(url:)
+      post.valid?
+      normal_url = post.url
+      errors = post.errors.to_hash.transform_values(&:dup)
+      validation_warnings = []
+      errors[:url] = ['URL が重複しています．'] if normal_url.present? && urls.key?(normal_url)
+      urls[normal_url] = true if normal_url.present?
+      if normal_url.present? && Post.exists?(url: normal_url)
+        @existing == 'error' ? errors[:url] = ['既存投稿です．'] : validation_warnings << '既存投稿のためスキップします．'
+      end
+      validate_basic_data(attributes, errors)
+      validate_preview_tags(merged_tags(tag_sources), errors, validation_warnings)
+      validate_parents(attributes['parent_post_ids'], errors)
+      attributes.delete('url')
+      attributes['tags'] = merged_tags(tag_sources)
+      { source_row: row[:source_row], url: normal_url || url, attributes:, provenance:, tag_sources:,
+        fetch_warnings:, validation_warnings:, warnings: fetch_warnings + validation_warnings, errors:,
+        status: errors.present? ? 'error' : ((fetch_warnings + validation_warnings).present? ? 'warning' : 'ready'),
+        existing: normal_url.present? && Post.exists?(url: normal_url) }
+    end
+  end
+
+  def metadata_for url, fetch_metadata, cache, previous_warnings
+    return [{ }, previous_warnings] unless fetch_metadata
+
+    cache[url] ||= fetch_metadata(url)
+  end
+
+  private
+
+  def mapped_value field, values
+    mapping = @mappings[field]
+    return '' if mapping.blank?
+    return mapping['value'].to_s if mapping['kind'] == 'fixed'
+
+    Array(mapping['columns']).filter_map { |index| values[index.to_i].to_s.presence }.join(field == 'tags' ? ' ' : '')
+  end
+
+  def mapped_provenance attributes
+    attributes.to_h.to_h { |field, value|
+      mapping = @mappings[field.to_s]
+      origin =
+          if value.present? && mapping&.[]('kind') == 'fixed'
+            'fixed'
+          elsif value.present?
+            'mapped'
+          else
+            'automatic'
+          end
+      [field.to_s, origin]
+    }
+  end
+
+  def initial_tag_sources attributes, provenance
+    origin = provenance['tags'] == 'fixed' ? 'fixed' : 'mapped'
+    { 'automatic' => '', 'mapped' => origin == 'mapped' ? attributes['tags'].to_s : '',
+      'fixed' => origin == 'fixed' ? attributes['tags'].to_s : '', 'manual' => '' }
+  end
+
+  def merged_tags sources
+    %w[automatic mapped fixed manual].flat_map { sources[_1].to_s.split }.uniq.join(' ')
+  end
+
+  def fetch_metadata url
+    return [{ }, ['URL が空です．']] if url.blank?
+
+    data = PostMetadataFetcher.fetch(url).stringify_keys
+    warnings = []
+    warnings << 'タイトルを取得できませんでした．' if data['title'].blank?
+    warnings << 'サムネールを取得できませんでした．' if data['thumbnail_base'].blank?
+    [data.compact, warnings]
+  rescue Preview::UrlSafety::UnsafeUrl, Preview::HttpFetcher::FetchFailed, Preview::HttpFetcher::ResponseTooLarge => e
+    [{ }, ["メタデータを取得できませんでした: #{ e.message }"]]
+  end
+
+  def validate_preview_tags raw, errors, warnings
+    names = raw.to_s.split
+    return if names.empty?
+    if names.any? { _1.downcase.start_with?('nico:') }
+      errors[:tags] = ['ニコニコ・タグは直接指定できません．']
+      return
+    end
+    parsed = names.map { |name| TagName.canonicalise(name.sub(/\[.*\]\z/, '')).first }
+    existing = Tag.joins(:tag_name).where(tag_names: { name: parsed }).includes(:tag_name).to_a
+    deprecated = existing.select(&:deprecated?).map(&:name)
+    errors[:tags] = ["廃止済みタグがあります: #{ deprecated.join(' ') }"] if deprecated.present?
+    known = existing.reject(&:deprecated?).map(&:name)
+    new_tags = parsed.uniq - known
+    warnings << "新規タグを作成します: #{ new_tags.join(' ') }" if new_tags.present?
+  rescue Tag::SectionLiteralParseError
+    errors[:tags] = ['タグ区間の記法が不正です．']
+  end
+
+  def validate_basic_data attributes, errors
+    post = Post.new(title: attributes['title'].presence,
+                    url: attributes['url'],
+                    thumbnail_base: attributes['thumbnail_base'].presence,
+                    original_created_from: attributes['original_created_from'].presence,
+                    original_created_before: attributes['original_created_before'].presence,
+                    video_ms: parse_duration(attributes['duration'], errors))
+    post.valid?
+    post.errors.each { |error| (errors[error.attribute] ||= []) << error.message }
+  end
+
+  def parse_duration value, errors
+    return nil if value.blank?
+
+    value.is_a?(Numeric) ? value.to_i : Tag.time_to_ms!(value.to_s, tag_name: '動画時間')
+  rescue Tag::SectionLiteralParseError
+    errors[:video_ms] = ['動画時間の記法が不正です．']
+    nil
+  end
+
+  def validate_parents raw, errors
+    ids = raw.to_s.split.map { Integer(_1, exception: false) }
+    return if ids.compact.empty? && raw.to_s.blank?
+    errors[:parent_post_ids] = ['親投稿 Id. が不正です．'] and return if ids.any? { _1.nil? || _1 <= 0 }
+    errors[:parent_post_ids] = ['存在しない親投稿 Id. があります．'] if Post.where(id: ids).count != ids.uniq.length
+  end
+end

backend/app/services/post_import_runner.rb

@@ -0,0 +1,49 @@
+class PostImportRunner
+  MAX_ROWS = PostImportSourceParser::MAX_ROWS
+  ATTRIBUTE_KEYS = %w[title thumbnail_base original_created_from original_created_before duration tags parent_post_ids video_ms].freeze
+  def initialize actor:, rows:, existing: 'skip'
+    @actor = actor
+    @rows = rows
+    @existing = existing == 'error' ? 'error' : 'skip'
+  end
+
+  def run
+    raise ArgumentError, "取込件数は #{ MAX_ROWS } 件までです．" if @rows.length > MAX_ROWS
+
+    results = @rows.map { |row| run_row(row.to_h.deep_transform_keys { _1.to_s.underscore }) }
+    { created: results.count { _1[:status] == 'created' }, skipped: results.count { _1[:status] == 'skipped' },
+      failed: results.count { _1[:status] == 'failed' }, rows: results }
+  end
+
+  private
+
+  def run_row row
+    attributes = row.fetch('attributes', { }).to_h.transform_keys { _1.to_s.underscore }
+    raise ArgumentError, '取込項目が不正です．' unless (attributes.keys - ATTRIBUTE_KEYS).empty?
+    raise ArgumentError, '取込項目が大きすぎます．' if attributes.values.any? { _1.to_s.bytesize > PostImportSourceParser::MAX_CELL_BYTES }
+    raise ArgumentError, 'URL が長すぎます．' if row['url'].to_s.bytesize > PostImportSourceParser::MAX_CELL_BYTES
+    preview = PostImportPreviewer.new(parsed: { columns: [], rows: [] }, url_column: 0,
+                                      mappings: {}, existing: @existing).preview_rows(
+                                          rows: [{ source_row: row['source_row'], url: row['url'],
+                                                   attributes:, provenance: row['provenance'],
+                                                   tag_sources: row['tag_sources'] }],
+                                          fetch_metadata: false).first
+    return { source_row: row['source_row'], status: 'failed', errors: preview[:errors] } if preview[:errors].present?
+    return row.slice('source_row').merge(status: 'skipped') if @existing == 'skip' && preview[:existing]
+    attributes['tags'] = preview[:attributes]['tags']
+    attributes['url'] = row['url']
+    post = PostCreator.new(actor: @actor, attributes:).create!
+    { source_row: row['source_row'], status: 'created', post: PostRepr.base(post) }
+  rescue ActiveRecord::RecordInvalid => e
+    { source_row: row['source_row'], status: 'failed', errors: e.record.errors.to_hash }
+  rescue Tag::NicoTagNormalisationError
+    { source_row: row['source_row'], status: 'failed', errors: { tags: ['ニコニコ・タグは直接指定できません．'] } }
+  rescue Tag::DeprecatedTagNormalisationError
+    { source_row: row['source_row'], status: 'failed', errors: { tags: ['廃止済みタグは付与できません．'] } }
+  rescue ArgumentError, PostCreator::VideoMsParseError
+    { source_row: row['source_row'], status: 'failed', errors: { base: ['入力値が不正です．'] } }
+  rescue StandardError => e
+    Rails.logger.error("post_import_runner_failure #{ { error: e.class.name, message: e.message }.to_json }")
+    { source_row: row['source_row'], status: 'failed', errors: { base: ['登録中にエラーが発生しました．'] } }
+  end
+end

backend/app/services/post_import_source_parser.rb

@@ -0,0 +1,56 @@
+require 'csv'
+
+class PostImportSourceParser
+  MAX_ROWS = 100
+  MAX_BYTES = 1.megabyte
+  MAX_CELL_BYTES = 20.kilobytes
+
+  def initialize source:, format:, has_header: false, json_path: nil
+    @source = source.to_s
+    @format = format.to_s
+    @has_header = ActiveModel::Type::Boolean.new.cast(has_header)
+    @json_path = json_path.to_s
+  end
+
+  def parse
+    raise ArgumentError, '入力が大きすぎます．' if @source.bytesize > MAX_BYTES
+
+    @format == 'json' ? parse_json : parse_delimited
+  end
+
+  private
+
+  def parse_delimited
+    separator = @format == 'csv' ? ',' : "\t"
+    table = CSV.parse(@source, col_sep: separator).map { |row| row.map { _1.to_s.strip } }
+    raise ArgumentError, '入力が空です．' if table.empty?
+
+    headers = @has_header ? table.shift : nil
+    ensure_limits!(table)
+    width = table.map(&:length).max.to_i
+    { columns: (0...width).map { |index| { key: index.to_s,
+                                          label: "#{ ('A'.ord + index).chr }：#{ headers&.[](index).presence || '列' }" } },
+      rows: table.each_with_index.map { |values, index| { source_row: index + (@has_header ? 2 : 1), values: } } }
+  rescue CSV::MalformedCSVError => e
+    raise ArgumentError, "CSV・TSV の形式が不正です: #{ e.message }"
+  end
+
+  def parse_json
+    value = JSON.parse(@source, max_nesting: 20)
+    @json_path.split('.').reject(&:blank?).each { |part| value = value.is_a?(Array) ? value[Integer(part)] : value.fetch(part) }
+    raise ArgumentError, 'JSON の投稿候補は配列にしてください．' unless value.is_a?(Array)
+
+    ensure_limits!(value)
+    keys = value.grep(Hash).flat_map(&:keys).map(&:to_s).uniq
+    { columns: keys.each_with_index.map { |key, index| { key:, label: "#{ ('A'.ord + index).chr }：#{ key }" } },
+      rows: value.each_with_index.map { |item, index| { source_row: index + 1,
+                                                         values: keys.map { item.is_a?(Hash) ? item[_1] || item[_1.to_sym] : nil } } } }
+  rescue JSON::ParserError, KeyError, ArgumentError => e
+    raise ArgumentError, "JSON の形式が不正です: #{ e.message }"
+  end
+
+  def ensure_limits! rows
+    raise ArgumentError, "取込件数は #{ MAX_ROWS } 件までです．" if rows.length > MAX_ROWS
+    raise ArgumentError, 'セルが大きすぎます．' if rows.flatten.any? { _1.to_s.bytesize > MAX_CELL_BYTES }
+  end
+end

backend/app/services/post_metadata_fetcher.rb

@@ -0,0 +1,48 @@
+class PostMetadataFetcher
+  def self.fetch raw_url
+    uri, = Preview::UrlSafety.validate(raw_url)
+    response = Preview::HttpFetcher.fetch(uri.to_s, max_bytes: Preview::ThumbnailFetcher::HTML_MAX_BYTES)
+    metadata = Preview::HtmlMetadataExtractor.extract(response)
+    document = Nokogiri::HTML.parse(response.body)
+    content = -> name { document.at_css("meta[property='#{ name }'], meta[name='#{ name }']")&.[]('content')&.strip.presence }
+    duration = content.call('og:video:duration') || content.call('video:duration')
+    published = content.call('article:published_time') || content.call('date')
+    created_range = original_created_range(published)
+    platform_tags = platform_tags(uri)
+    { title: metadata[:title],
+      thumbnail_base: Preview::KnownSiteExtractor.thumbnail_url(uri) || metadata[:image_url],
+      original_created_from: created_range&.first&.iso8601,
+      original_created_before: created_range&.last&.iso8601,
+      duration: duration&.to_f&.then { _1.positive? ? (_1 * 1_000).round : nil },
+      tags: platform_tags.join(' ') }
+  end
+
+  def self.platform_tags uri
+    return ['動画', 'YouTube'] if Preview::KnownSiteExtractor.youtube_video_id(uri)
+    return ['動画', 'ニコニコ'] if Preview::KnownSiteExtractor.niconico_video_id(uri)
+
+    []
+  end
+
+  def self.original_created_range value
+    return nil if value.blank?
+
+    raw = value.to_s.strip
+    from = Time.zone.parse(raw)
+    zone = '(?:Z|[+-]\d{2}:?\d{2})?'
+    before = case raw
+             when /\A\d{4}\z/ then from + 1.year
+             when /\A\d{4}-\d{2}\z/ then from + 1.month
+             when /\A\d{4}-\d{2}-\d{2}\z/ then from + 1.day
+             when /\A\d{4}-\d{2}-\d{2}T\d{2}#{ zone }\z/ then from + 1.hour
+             when /\A\d{4}-\d{2}-\d{2}T\d{2}:\d{2}#{ zone }\z/ then from + 1.minute
+             when /\A\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}#{ zone }\z/ then from + 1.second
+             when /\A\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d+#{ zone }\z/ then from + 0.001
+             else return nil
+             end
+    [from, before]
+  rescue ArgumentError, TypeError
+    nil
+  end
+  private_class_method :platform_tags, :original_created_range
+end

backend/app/services/preview/http_fetcher.rb

@@ -12,8 +12,12 @@ module Preview
 
     Response = Data.define(:body, :content_type, :url)
 
-    def self.fetch(raw_url, max_bytes: DEFAULT_MAX_BYTES, redirects: MAX_REDIRECTS)
+    def self.fetch(raw_url, max_bytes: DEFAULT_MAX_BYTES, redirects: MAX_REDIRECTS,
+                   allowed_hosts: nil)
       uri, addresses = UrlSafety.validate(raw_url)
+      if allowed_hosts && !allowed_hosts.include?(uri.host.downcase)
+        raise FetchFailed, '許可されてゐない redirect 先です．'
+      end
       response = request(uri, addresses.first, max_bytes)
 
       if response.is_a?(Net::HTTPRedirection)
@@ -50,7 +54,7 @@ module Preview
               raise FetchFailed, 'redirect 先が不正です．'
             end
 
-        return fetch(redirect_url, max_bytes:, redirects: redirects - 1)
+        return fetch(redirect_url, max_bytes:, redirects: redirects - 1, allowed_hosts:)
       end
 
       unless response.is_a?(Net::HTTPSuccess)

backend/app/services/preview/known_site_extractor.rb

@@ -4,6 +4,15 @@ module Preview
       youtube_thumbnail(uri)
     end
 
+    def self.youtube_video_id(uri)
+      case uri.host&.downcase
+      when 'youtu.be'
+        uri.path.split('/').reject(&:blank?).first
+      when 'www.youtube.com', 'youtube.com', 'm.youtube.com'
+        uri.path == '/watch' ? URI.decode_www_form(uri.query.to_s).to_h['v'] : nil
+      end&.then { _1 if _1.match?(/\A[A-Za-z0-9_-]{6,20}\z/) }
+    end
+
     def self.niconico_video_id(uri)
       case uri.host&.downcase
       when 'www.nicovideo.jp', 'nicovideo.jp'
@@ -14,14 +23,8 @@ module Preview
     end
 
     def self.youtube_thumbnail(uri)
-      id =
-          case uri.host&.downcase
-          when 'youtu.be'
-            uri.path.split('/').reject(&:blank?).first
-          when 'www.youtube.com', 'youtube.com', 'm.youtube.com'
-            uri.path == '/watch' ? URI.decode_www_form(uri.query.to_s).to_h['v'] : nil
-          end
-      return unless id&.match?(/\A[A-Za-z0-9_-]{6,20}\z/)
+      id = youtube_video_id(uri)
+      return unless id
 
       "https://i.ytimg.com/vi/#{ id }/hqdefault.jpg"
     end

backend/config/routes.rb

@@ -33,6 +33,13 @@ Rails.application.routes.draw do
     get :thumbnail
   end
 
+  scope 'posts/import', controller: :post_imports do
+    post :parse
+    post :preview
+    post :validate
+    post '', action: :create
+  end
+
   resources :wiki_pages, path: 'wiki', only: [:index, :show, :create, :update] do
     collection do
       get :search

backend/spec/requests/tags_deerjikists_spec.rb

@@ -226,14 +226,6 @@ RSpec.describe 'Tags deerjikists API', type: :request do
               [platform2, code2],
             )
         end
-
-        it 'locks the tag before replacing the complete list' do
-          expect_any_instance_of(Tag).to receive(:lock!).and_call_original
-
-          do_request
-
-          expect(response).to have_http_status(:ok)
-        end
       end
 
       context 'when tag already has deerjikists' do
@@ -307,81 +299,6 @@ RSpec.describe 'Tags deerjikists API', type: :request do
         end
       end
 
-      context 'when platform is outside the enum' do
-        let(:payload) do
-          [
-            { platform: 'invalid', code: code1 },
-          ]
-        end
-
-        it 'returns 422 with an indexed platform error without changing the list' do
-          Deerjikist.create!(platform: platform1, code: code1, tag: tag)
-
-          expect {
-            do_request
-          }.not_to change { Deerjikist.where(tag: tag).map { |d| [d.platform, d.code] } }
-
-          expect(response).to have_http_status(:unprocessable_entity)
-          expect(json.fetch('errors')).to include(
-            'deerjikists.0.platform' => [be_present],
-          )
-        end
-      end
-
-      context 'when a requested deerjikist belongs to another tag' do
-        let!(:other_tag) { create(:tag, category: :deerjikist) }
-        let!(:owned_deerjikist) do
-          Deerjikist.create!(platform: platform1, code: code1, tag: tag)
-        end
-        let!(:conflicting_deerjikist) do
-          Deerjikist.create!(platform: platform2, code: code2, tag: other_tag)
-        end
-        let(:payload) do
-          [
-            { platform: 'nico', code: 'new-code' },
-            { platform: platform2, code: code2 },
-          ]
-        end
-
-        before do
-          other_tag.tag_name.update!(name: 'existing-deerjikist')
-        end
-
-        it 'returns an indexed 422 error and rolls back the complete replacement' do
-          expect {
-            do_request
-          }.not_to change { Deerjikist.order(:platform, :code).pluck(:platform, :code, :tag_id) }
-
-          expect(response).to have_http_status(:unprocessable_entity)
-          expect(json.fetch('errors')).to include(
-            'deerjikists.1.code' => [include('existing-deerjikist')],
-          )
-          expect(owned_deerjikist.reload.tag_id).to eq(tag.id)
-          expect(conflicting_deerjikist.reload.tag_id).to eq(other_tag.id)
-        end
-      end
-
-      context 'when a requested deerjikist already belongs to the same tag' do
-        let!(:existing_deerjikist) do
-          Deerjikist.create!(platform: platform1, code: code1, tag: tag)
-        end
-        let(:payload) do
-          [
-            { platform: platform1, code: code1 },
-          ]
-        end
-
-        it 'keeps the existing row' do
-          expect {
-            do_request
-          }.not_to change { existing_deerjikist.reload.created_at }
-
-          expect(response).to have_http_status(:ok)
-          expect(Deerjikist.where(tag: tag).pluck(:platform, :code))
-            .to eq([[platform1, code1]])
-        end
-      end
-
       context 'when youtube code is handle' do
         let(:channel_id) { 'UCabcdefghijklmnopqrstuv' }
         let(:payload) do

frontend/src/App.tsx

@@ -43,6 +43,7 @@ import PostDetailPage from '@/pages/posts/PostDetailPage'
 import PostHistoryPage from '@/pages/posts/PostHistoryPage'
 import PostListPage from '@/pages/posts/PostListPage'
 import PostNewPage from '@/pages/posts/PostNewPage'
+import PostImportPage from '@/pages/posts/PostImportPage'
 import PostSearchPage from '@/pages/posts/PostSearchPage'
 import ServiceUnavailable from '@/pages/ServiceUnavailable'
 import SettingPage from '@/pages/users/SettingPage'
@@ -75,6 +76,7 @@ const RouteTransitionWrapper = ({ animationMode, user, setUser }: {
 	    <Route path="/" element={<Navigate to="/posts" replace/>}/>
 	    <Route path="/posts" element={<PostListPage/>}/>
 	    <Route path="/posts/new" element={<PostNewPage user={user}/>}/>
+	    <Route path="/posts/import" element={<PostImportPage user={user}/>}/>
 	    <Route path="/posts/search" element={<PostSearchPage/>}/>
 	    <Route path="/posts/:id" element={<PostDetailRoute user={user}/>}/>
 	    <Route path="/posts/changes" element={<PostHistoryPage/>}/>
@@ -113,6 +115,7 @@ const RouteTransitionWrapper = ({ animationMode, user, setUser }: {
 	  <Route path="/" element={<Navigate to="/posts" replace/>}/>
 	  <Route path="/posts" element={<PostListPage/>}/>
 	  <Route path="/posts/new" element={<PostNewPage user={user}/>}/>
+	  <Route path="/posts/import" element={<PostImportPage user={user}/>}/>
 	  <Route path="/posts/search" element={<PostSearchPage/>}/>
 	  <Route path="/posts/:id" element={<PostDetailRoute user={user}/>}/>
 	  <Route path="/posts/changes" element={<PostHistoryPage/>}/>

frontend/src/components/TopNav.tsx

@@ -43,6 +43,7 @@ export const menuOutline = (
 	  { name: '一覧', to: '/posts' },
 	  { name: '検索', to: '/posts/search' },
 	  { name: '追加', to: '/posts/new' },
+	  { name: 'インポート', to: '/posts/import' },
 	  { name: '全体履歴', to: '/posts/changes' },
 	  { name: 'ヘルプ', to: '/wiki/ヘルプ:広場' }] },
       { name: 'タグ', to: '/tags', subMenu: [

frontend/src/index.css

@@ -239,6 +239,11 @@ body
   background: var(--top-nav-submenu-bg);
 }
 
+.top-nav-mobile-menu .top-nav-submenu
+{
+  background: var(--top-nav-mobile-active-bg);
+}
+
 .top-nav-mobile-menu
 {
   background: var(--top-nav-mobile-menu-bg);

frontend/src/index.test.ts

@@ -0,0 +1,22 @@
+import { readFileSync } from 'node:fs'
+import { resolve } from 'node:path'
+
+import { describe, expect, it } from 'vitest'
+
+
+const indexCss = readFileSync (
+    resolve (process.cwd (), 'src/index.css'),
+    'utf8')
+const compactIndexCss = indexCss.replace (/\s+/g, ' ')
+const mobileActiveRule = '.top-nav-mobile-active {'
+    + ' background: var(--top-nav-mobile-active-bg); }'
+const mobileSubmenuRule = '.top-nav-mobile-menu .top-nav-submenu {'
+    + ' background: var(--top-nav-mobile-active-bg); }'
+
+
+describe ('TopNav mobile menu colours', () => {
+  it ('uses one background variable for active rows and expanded submenus', () => {
+    expect (compactIndexCss).toContain (mobileActiveRule)
+    expect (compactIndexCss).toContain (mobileSubmenuRule)
+  })
+})

frontend/src/lib/settings.test.ts

@@ -89,8 +89,12 @@ describe ('settings', () => {
   })
 
   it ('derives TopNav colours without mixing mobile active backgrounds', () => {
-    expect (buildThemeTokens ('light', { }).topNavMobileActiveBackground).toBe (
+    const tokens = buildThemeTokens ('light', { })
+
+    expect (tokens.topNavMobileActiveBackground).toBe (
 	LIGHT_THEME_TOKENS.topNavRootBackgroundDesktop)
+    expect (tokens.topNavMobileActiveBackground).not.toBe (
+	tokens.topNavRootBackgroundMobile)
     expect (buildThemeTokens ('dark', { }).topNavMobileActiveBackground).toBe (
 	DARK_THEME_TOKENS.topNavActiveBackground)
   })

frontend/src/pages/posts/PostImportPage.tsx

@@ -0,0 +1,327 @@
+import { useEffect, useRef, useState } from 'react'
+import { Helmet } from 'react-helmet-async'
+
+import FieldError from '@/components/common/FieldError'
+import PrefetchLink from '@/components/PrefetchLink'
+import Form from '@/components/common/Form'
+import FormField from '@/components/common/FormField'
+import PageTitle from '@/components/common/PageTitle'
+import MainArea from '@/components/layout/MainArea'
+import { Button } from '@/components/ui/button'
+import { toast } from '@/components/ui/use-toast'
+import { SITE_TITLE } from '@/config'
+import { apiPost } from '@/lib/api'
+import { canEditContent } from '@/lib/users'
+import { inputClass } from '@/lib/utils'
+import Forbidden from '@/pages/Forbidden'
+
+import type { FC } from 'react'
+
+import type { User } from '@/types'
+
+type Props = { user: User | null }
+type Column = { key: string, label: string }
+type ImportRow = {
+    sourceRow: number
+    url: string
+    attributes: Record<string, string>
+    warnings: string[]
+    errors: Record<string, string[]>
+    provenance: Record<string, 'automatic' | 'mapped' | 'fixed' | 'manual'>
+    tagSources?: Record<'automatic' | 'mapped' | 'fixed' | 'manual', string>
+    status: 'ready' | 'warning' | 'error'
+    existing: boolean
+    importStatus?: 'pending' | 'created' | 'skipped' | 'failed' }
+type ParsedImport = { columns: Column[], rows: { sourceRow: number, values: string[] }[] }
+type Phase = 'source' | 'mapping' | 'preview' | 'result'
+
+
+const detectFormat = (source: string): 'csv' | 'tsv' =>
+    source.includes ('\t') ? 'tsv' : (source.includes (',') ? 'csv' : 'tsv')
+
+const publicGoogleSheetURL = (source: string): boolean =>
+    /^https:\/\/docs\.google\.com\/spreadsheets\/d\/e\/[A-Za-z0-9_-]+\/pubhtml#gid=\d+$/.test (
+        source.trim ())
+
+
+const PostImportPage: FC<Props> = ({ user }) => {
+  const [source, setSource] = useState ('')
+  const [phase, setPhase] = useState<Phase> ('source')
+  const [format, setFormat] = useState<'csv' | 'tsv' | 'json' | 'google_sheets'> ('tsv')
+  const [hasHeader, setHasHeader] = useState (false)
+  const [jsonPath, setJsonPath] = useState ('')
+  const [urlColumn, setUrlColumn] = useState ('')
+  const [parsed, setParsed] = useState<ParsedImport | null> (null)
+  const [columns, setColumns] = useState<Column[]> ([])
+  const [rows, setRows] = useState<ImportRow[]> ([])
+  const [results, setResults] = useState<{ sourceRow: number, status: string, post?: { id: number }, errors?: Record<string, string[]> }[]> ([])
+  const [mappings, setMappings] = useState<Record<string, { columns: string[] }>> ({ })
+  const [existing, setExisting] = useState<'skip' | 'error'> ('skip')
+  const [loading, setLoading] = useState (false)
+  const validationGeneration = useRef (0)
+  const editable = canEditContent (user)
+
+  const parseSource = async () => {
+    setLoading (true)
+    try
+    {
+      const data = await apiPost<ParsedImport> ('/posts/import/parse', {
+        source,
+        format,
+        has_header: hasHeader,
+        json_path: jsonPath,
+        existing })
+      setColumns (data.columns)
+      setParsed (data)
+      if (data.columns.length === 1)
+        setUrlColumn ('0')
+      else
+        {
+          const candidate = data.columns.map ((_, index) => ({ index,
+              count: data.rows.filter (row => /^https?:\/\//.test (row.values[index] ?? '')).length }))
+                                      .sort ((a, b) => b.count - a.count)[0]
+          if (candidate?.count)
+            setUrlColumn (String (candidate.index))
+        }
+      setPhase ('mapping')
+    }
+    catch
+    {
+      toast ({ title: '読込みに失敗しました', description: '入力形式を確認してください。' })
+    }
+    finally
+    {
+      setLoading (false)
+    }
+  }
+
+  const preview = async () => {
+    if (!parsed || urlColumn === '')
+      return
+    setLoading (true)
+    try
+    {
+      const data = await apiPost<{ rows: ImportRow[] }> ('/posts/import/preview', {
+        parsed: { columns: parsed.columns, rows: parsed.rows },
+        url_column: urlColumn,
+        mappings,
+        existing })
+      setRows (data.rows)
+      setPhase ('preview')
+    }
+    catch
+    {
+      toast ({ title: '投稿プレビューに失敗しました' })
+    }
+    finally
+    {
+      setLoading (false)
+    }
+  }
+
+  const updateAttribute = async (index: number, field: string, value: string) => {
+    const row = rows[index]
+    const next = { ...row,
+                   attributes: { ...row.attributes, [field]: value },
+                   provenance: { ...row.provenance, [field]: 'manual' } }
+    const nextRows = rows.map ((currentRow, rowIndex) => rowIndex === index ? next : currentRow)
+    setRows (nextRows)
+    const generation = ++validationGeneration.current
+    setLoading (true)
+    try
+    {
+      const validated = await apiPost<{ rows: ImportRow[] }> ('/posts/import/validate',
+                                                                { rows: nextRows, existing,
+                                                                  changed_row: -1 })
+      if (generation === validationGeneration.current)
+        setRows (validated.rows)
+    }
+    catch
+    {
+      toast ({ title: '行の再検証に失敗しました' })
+    }
+    finally
+    {
+      if (generation === validationGeneration.current)
+        setLoading (false)
+    }
+  }
+
+  const updateURL = async (index: number, value: string) => {
+    const row = rows[index]
+    const next = { ...row, url: value, provenance: { ...row.provenance, url: 'manual' } }
+    const nextRows = rows.map ((currentRow, rowIndex) => rowIndex === index ? next : currentRow)
+    setRows (nextRows)
+    const generation = ++validationGeneration.current
+    setLoading (true)
+    try
+    {
+      const validated = await apiPost<{ rows: ImportRow[] }> ('/posts/import/validate',
+                                                                { rows: nextRows, existing,
+                                                                  changed_row: row.sourceRow })
+      if (generation === validationGeneration.current)
+        setRows (validated.rows)
+    }
+    catch
+    {
+      toast ({ title: 'URL の再検証に失敗しました' })
+    }
+    finally
+    {
+      if (generation === validationGeneration.current)
+        setLoading (false)
+    }
+  }
+
+  const submit = async () => {
+    const targets = rows.filter (row => (row as ImportRow & { importStatus?: string }).importStatus !== 'created')
+    if (targets.length === 0)
+      return
+    setLoading (true)
+    try
+    {
+      const result = await apiPost<{ created: number, skipped: number, failed: number,
+                                     rows: { sourceRow: number, status: string, post?: { id: number }, errors?: Record<string, string[]> }[] }> ('/posts/import', { rows: targets, existing })
+      toast ({ title: `登録完了: ${result.created} 件`,
+              description: `スキップ ${result.skipped} 件、失敗 ${result.failed} 件` })
+      setPhase ('result')
+      setResults (result.rows)
+      setRows (current => current.map (row => {
+        const resultRow = result.rows.find (_1 => _1.sourceRow === row.sourceRow)
+        return resultRow ? { ...row, importStatus: resultRow.status } : row
+      }))
+    }
+    catch
+    {
+      toast ({ title: '登録に失敗しました' })
+    }
+    finally
+    {
+      setLoading (false)
+    }
+  }
+
+  if (!(editable))
+    return <Forbidden/>
+
+  return (
+      <MainArea>
+	<Helmet><title>{`投稿インポート | ${SITE_TITLE}`}</title></Helmet>
+	<PageTitle>投稿インポート</PageTitle>
+	{phase === 'source' ? (
+	    <Form>
+	      <p className="text-sm text-neutral-600 dark:text-neutral-300">
+		URL を一行に一件ずつ貼り付けるか、CSV・TSV・JSON を入力してください。
+	      </p>
+	      {publicGoogleSheetURL (source) && (
+		<fieldset className="space-y-1 rounded border p-3 dark:border-neutral-700">
+		  <legend>公開 Google スプレッドシート URL</legend>
+		  <label className="mr-3"><input type="radio" checked={format === 'google_sheets'}
+				 onChange={() => setFormat ('google_sheets')}/>シート内データを読み込む</label>
+		  <label><input type="radio" checked={format !== 'google_sheets'}
+				 onChange={() => setFormat ('tsv')}/>URL 自体を投稿として扱う</label>
+		</fieldset>)}
+	      <FormField label="入力形式">
+		{() => <select value={format} onChange={ev => setFormat (ev.target.value as typeof format)}
+				 className={inputClass (false)}>
+		  <option value="tsv">テキスト・TSV</option><option value="csv">CSV</option>
+		  <option value="json">JSON</option>
+		  <option value="google_sheets">公開 Google スプレッドシート</option>
+		</select>}
+	      </FormField>
+	      <FormField label="入力データ">
+		{() => <textarea value={source} rows={12} className={inputClass (false)}
+			 onChange={ev => { setSource (ev.target.value); if (format !== 'json' && format !== 'google_sheets') setFormat (detectFormat (ev.target.value)) }}/>} 
+	      </FormField>
+	      <input type="file" accept=".txt,.csv,.tsv,.json" onChange={ev => {
+		const file = ev.target.files?.[0]
+		if (file) file.text ().then (text => { setSource (text); setFormat (file.name.endsWith ('.json') ? 'json' : detectFormat (text)) })
+	      }}/>
+	      {format !== 'json' && <label className="flex gap-2"><input type="checkbox" checked={hasHeader}
+				 onChange={ev => setHasHeader (ev.target.checked)}/>見出し行を使用する</label>}
+	      {format === 'json' && <FormField label="投稿候補の位置（例: data.posts）">
+		{() => <input value={jsonPath} onChange={ev => setJsonPath (ev.target.value)} className={inputClass (false)}/>} 
+	      </FormField>}
+	      {format === 'google_sheets' && <p className="text-sm text-neutral-600 dark:text-neutral-300">
+		非公開シートには対応していません。ウェブ公開 URL を使用するか、CSV を保存して選択、又は内容を貼り付けてください。
+	      </p>}
+	      <Button type="button" onClick={parseSource} disabled={loading || !(source)}>列を解析</Button>
+	    </Form>) : phase === 'mapping' && parsed ? (
+	    <Form>
+	      <PageTitle>列の割当て</PageTitle>
+	      <p>URL 列と補助列を指定してから、投稿情報を取得します。</p>
+	      <label>URL 列 <select value={urlColumn} onChange={ev => setUrlColumn (ev.target.value)}>
+		<option value="">選択してください</option>
+		{columns.map ((column, index) => <option key={column.key} value={index}>{column.label}</option>)}
+	      </select></label>
+	      {['title', 'thumbnail_base', 'original_created_from', 'original_created_before', 'duration', 'tags', 'parent_post_ids'].map (field =>
+		<label key={field}>{field}<select value={mappings[field]?.columns?.[0] ?? ''}
+		  onChange={ev => setMappings (current => ({ ...current, [field]: { columns: ev.target.value === '' ? [] : [ev.target.value] } }))}>
+		  <option value="">指定なし</option>
+		  {columns.map ((column, index) => <option key={column.key} value={index}>{column.label}</option>)}
+		</select></label>)}
+	      <div className="overflow-x-auto"><table className="text-sm"><thead><tr>{columns.map (column => <th key={column.key}>{column.label}</th>)}</tr></thead><tbody>{parsed.rows.slice (0, 5).map (row => <tr key={row.sourceRow}>{row.values.map ((value, index) => <td key={index}>{value}</td>)}</tr>)}</tbody></table></div>
+	      <Button type="button" variant="outline" onClick={() => setPhase ('source')}>入力元へ戻る</Button>
+	      <Button type="button" onClick={preview} disabled={loading || urlColumn === ''}>投稿情報を取得して確認</Button>
+	    </Form>) : phase === 'preview' ? (
+	    <div className="space-y-4">
+	      <div className="flex flex-wrap gap-3 rounded border p-3 dark:border-neutral-700">
+		<label>URL 列 <select value={urlColumn} onChange={ev => setUrlColumn (ev.target.value)}>
+		  {columns.map ((column, index) => <option key={column.key} value={index}>{column.label}</option>)}
+		</select></label>
+		<label>既存投稿 <select value={existing} onChange={ev => setExisting (ev.target.value as typeof existing)}>
+		  <option value="skip">スキップ</option><option value="error">エラー</option>
+		</select></label>
+		{['title', 'thumbnail_base', 'original_created_from', 'original_created_before', 'duration', 'tags', 'parent_post_ids'].map (field =>
+		    <label key={field}>{field}<select value={mappings[field]?.columns?.[0] ?? ''}
+			 onChange={ev => setMappings (current => ({ ...current, [field]: { columns: ev.target.value === '' ? [] : [ev.target.value] } }))}>
+		      <option value="">指定なし</option>{columns.map ((column, index) => <option key={column.key} value={index}>{column.label}</option>)}
+		    </select></label>)}
+		<Button type="button" variant="outline" onClick={preview} disabled={loading}>自動取得を更新</Button>
+	      </div>
+	      <div className="grid gap-3 md:grid-cols-2">{rows.map ((row, index) => <RowCard key={row.sourceRow} row={row} index={index} update={updateAttribute} updateURL={updateURL}/>)}</div>
+	      <Button type="button" variant="outline" onClick={() => setPhase ('mapping')}>列割当てへ戻る</Button>
+	      <Button type="button" onClick={submit} disabled={loading}>有効な投稿を一括登録</Button>
+	    </div>) : (
+	    <div className="space-y-3"><p>インポート処理が完了しました。</p>
+	      {results.map (result => <div key={result.sourceRow} className="rounded border p-2 dark:border-neutral-700">行 {result.sourceRow}：{result.status} {result.post && <PrefetchLink to={`/posts/${result.post.id}`}>投稿を開く</PrefetchLink>}<FieldError messages={Object.values (result.errors ?? { }).flat ()}/></div>)}
+	      <Button type="button" onClick={() => setPhase ('preview')}>結果を確認</Button>
+	      <Button type="button" variant="outline" onClick={() => setPhase ('source')}>新しい入力元を選ぶ</Button>
+	    </div>)}
+	</MainArea>)
+}
+
+
+const RowCard = ({ row, index, update, updateURL }: { row: ImportRow, index: number, update: (index: number, field: string, value: string) => void, updateURL: (index: number, value: string) => void }) => (
+    <article className="space-y-2 rounded border bg-white p-3 text-neutral-900 dark:border-neutral-700 dark:bg-neutral-900 dark:text-neutral-100">
+	<p>行 {row.sourceRow}・{row.status}</p>
+	<EditableValue label="URL" value={row.url} origin={row.provenance.url} onCommit={value => updateURL (index, value)}/>
+	<EditableValue label="タイトル" value={row.attributes.title ?? ''} origin={row.provenance.title} onCommit={value => update (index, 'title', value)}/>
+	<EditableValue label="サムネール基底 URL" value={row.attributes.thumbnailBase ?? ''} origin={row.provenance.thumbnailBase} onCommit={value => update (index, 'thumbnailBase', value)}/>
+	{row.attributes.thumbnailBase && <img src={row.attributes.thumbnailBase} alt="サムネール" className="max-h-32" onError={ev => { ev.currentTarget.hidden = true }}/>} 
+	<EditableValue label="作成日時 From" value={row.attributes.originalCreatedFrom ?? ''} origin={row.provenance.originalCreatedFrom} onCommit={value => update (index, 'originalCreatedFrom', value)}/>
+	<EditableValue label="作成日時 Before" value={row.attributes.originalCreatedBefore ?? ''} origin={row.provenance.originalCreatedBefore} onCommit={value => update (index, 'originalCreatedBefore', value)}/>
+	<EditableValue label="動画時間" value={String (row.attributes.duration ?? '')} origin={row.provenance.duration} onCommit={value => update (index, 'duration', value)}/>
+	<EditableValue label="タグ" value={row.attributes.tags ?? ''} origin={row.provenance.tags} onCommit={value => update (index, 'tags', value)}/>
+	<EditableValue label="親投稿" value={row.attributes.parentPostIds ?? ''} origin={row.provenance.parentPostIds} onCommit={value => update (index, 'parentPostIds', value)}/>
+	<FieldError messages={[...row.warnings, ...Object.values (row.errors).flat ()]}/>
+    </article>)
+
+const EditableValue = ({ label, value, origin, onCommit }: { label: string, value: string, origin: string | undefined, onCommit: (value: string) => void }) => {
+  const [editing, setEditing] = useState (false)
+  const [draft, setDraft] = useState (value)
+  const finished = useRef (false)
+  useEffect (() => { if (!editing) setDraft (value) }, [editing, value])
+  const originLabel = { automatic: '自動取得', mapped: '入力データ', fixed: '固定値', manual: '手修正' }[origin ?? 'automatic']
+  const begin = () => { finished.current = false; setDraft (value); setEditing (true) }
+  const commit = () => { if (finished.current) return; finished.current = true; setEditing (false); if (draft !== value) onCommit (draft) }
+  const cancel = () => { if (finished.current) return; finished.current = true; setDraft (value); setEditing (false) }
+  return <div className={origin === 'manual' ? 'rounded bg-amber-100 p-1 dark:bg-amber-900' : 'p-1'}>
+    <span className="text-xs">{label}・{originLabel}</span>
+    {editing ? <input autoFocus value={draft} onChange={ev => setDraft (ev.target.value)} onBlur={commit}
+      onKeyDown={ev => { if (ev.key === 'Escape') cancel (); if (ev.key === 'Enter') { ev.preventDefault (); commit () } } } className={inputClass (false)}/>
+      : <button type="button" className="block w-full text-left" onDoubleClick={begin} onClick={begin}>{value || '（未指定）'}　編輯</button>}
+  </div>
+}
+
+export default PostImportPage

frontend/src/pages/posts/PostNewPage.tsx

@@ -49,6 +49,7 @@ const PostNewPage: FC<Props> = ({ user }) => {
   const [url, setURL] = useState ('')
 
   const thumbnailPreviewRef = useRef ('')
+  const urlRef = useRef ('')
   const videoFlg =
       useMemo (() => tags.split (/\s+/).some (tag => tag.replace (/\[.*\]$/, '') === '動画'),
                [tags])
@@ -84,11 +85,13 @@ const PostNewPage: FC<Props> = ({ user }) => {
   }
 
   const fetchTitle = useCallback (async () => {
-    setTitleLoading (true)
+	const requestedURL = url
+	setTitleLoading (true)
     try
     {
       const data = await apiGet<{ title: string }> ('/preview/title', { params: { url } })
-      setTitle (data.title || '')
+	  if (requestedURL === urlRef.current)
+	    setTitle (current => current || data.title || '')
     }
     finally
     {
@@ -97,6 +100,7 @@ const PostNewPage: FC<Props> = ({ user }) => {
   }, [url])
 
   const fetchThumbnail = useCallback (async () => {
+	const requestedURL = url
     setThumbnailPreview ('')
     setThumbnailFile (null)
     setThumbnailLoading (true)
@@ -107,10 +111,13 @@ const PostNewPage: FC<Props> = ({ user }) => {
       const data = await apiGet<Blob> ('/preview/thumbnail',
 				       { params: { url }, responseType: 'blob' })
       const imageURL = URL.createObjectURL (data)
-      setThumbnailPreview (imageURL)
-      setThumbnailFile (new File ([data],
-				  'thumbnail.png',
-				  { type: data.type || 'image/png' }))
+	  if (requestedURL === urlRef.current)
+	    {
+	      setThumbnailPreview (current => current || imageURL)
+	      setThumbnailFile (current => current || new File ([data],
+								  'thumbnail.png',
+								  { type: data.type || 'image/png' }))
+	    }
     }
     finally
     {
@@ -122,6 +129,20 @@ const PostNewPage: FC<Props> = ({ user }) => {
     thumbnailPreviewRef.current = thumbnailPreview
   }, [thumbnailPreview])
 
+  useEffect (() => {
+	urlRef.current = url
+  }, [url])
+
+  useEffect (() => {
+	if (!(url))
+	  return
+	const timer = window.setTimeout (() => {
+	  fetchTitle ().catch (() => undefined)
+	  fetchThumbnail ().catch (() => undefined)
+	}, 500)
+    return () => window.clearTimeout (timer)
+  }, [fetchThumbnail, fetchTitle, url])
+
   if (!(editable))
     return <Forbidden/>
 
@@ -159,7 +180,7 @@ const PostNewPage: FC<Props> = ({ user }) => {
 			 onChange={ev => setTitle (ev.target.value)}
 			 disabled={titleLoading}/>
 		  <div className="flex flex-wrap items-center gap-2 text-sm">
-		    <span>必要なタイミングで URL から取得できます．</span>
+		    <span>URL 入力後に自動取得します．</span>
 		    <Button
 			type="button"
 			variant="outline"
@@ -176,7 +197,7 @@ const PostNewPage: FC<Props> = ({ user }) => {
 	    {({ describedBy, invalid }) => (
 		<>
 		  <div className="mb-2 flex flex-wrap items-center gap-2 text-sm">
-		    <span>必要なタイミングで URL から取得できます．</span>
+		    <span>URL 入力後に自動取得します．</span>
 		    <Button
 			type="button"
 			variant="outline"