#399

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

.gitea/ISSUE_TEMPLATE.md

@@ -0,0 +1,35 @@
+## 背景
+
+なぜ必要か。
+
+## 対象範囲
+
+- backend:
+- frontend:
+- docs:
+- migration:
+
+## やること
+
+- [ ] 
+
+## 受け入れ条件
+
+- [ ] 
+
+## 実行すべき確認
+
+- [ ] `cd backend && bundle exec rspec`
+- [ ] `cd frontend && npm run build`
+- [ ] `cd frontend && npm run lint`
+
+## 禁止事項
+
+- unrelated refactor はしない
+- 既存 API response shape を壊さない
+- 認証・認可・BAN を弱めない
+
+## Codex への指示
+
+この issue を読んで実装してください。
+不明点があれば、実装前に調査結果と選択肢を提示してください。

backend/AGENTS.md

@@ -0,0 +1,225 @@
+# backend/AGENTS.md
+
+## Scope
+
+These rules apply to work under `backend/`.
+
+This is a Rails API app using Active Record, RSpec, request specs,
+service objects, representation classes, and version tables for post/tag/wiki
+history.
+
+## Commands
+
+Use commands backed by files and dependencies in this directory:
+
+```sh
+bin/setup
+bin/dev
+bin/rails
+bin/rake
+bin/rubocop
+bin/brakeman
+bundle exec rspec
+```
+
+Common checks:
+
+```sh
+bundle exec rspec
+bin/rubocop
+bin/brakeman
+```
+
+Common Rails commands:
+
+```sh
+bin/rails db:prepare
+bin/rails db:migrate
+bin/rails routes
+bin/rails server
+```
+
+After backend behavior changes, run the relevant RSpec files. For broad backend changes, run:
+
+```sh
+bundle exec rspec
+```
+
+If a command cannot be run or fails, report the exact command and failure.
+
+Do not create, modify, or run tests unless the user explicitly asks for test
+work. When the user asks for tests, keep working and rerun them until they
+pass or the remaining failure is clearly blocked.
+
+## Rails structure
+
+- `app/controllers`: API controllers.
+- `app/models`: Active Record models and concerns.
+- `app/representations`: JSON response shaping.
+- `app/services`: domain services such as version recorders, wiki commit,
+  YouTube sync, and similarity calculation.
+- `config/routes.rb`: public API routes.
+- `db/migrate`: migrations.
+- `db/schema.rb`: schema snapshot.
+- `lib/tasks`: custom Rake tasks.
+- `spec`: RSpec tests.
+
+Before changing behavior, inspect the matching route, controller, model,
+service, representation, and spec.
+
+## Ruby style
+
+- Prefer precise, minimal changes.
+- Use single quotes unless interpolation or escaping makes double quotes better.
+- Do not put a space before Ruby method-call parentheses.
+- For `render`-family method calls, omit parentheses even when passing
+  keyword arguments.
+- Never put a line break immediately before `)` in Ruby.
+- Do not use `%w` or `%i` in new Ruby code.
+- Never write a Ruby line longer than 99 characters.
+- Aim to keep Ruby lines within 79 characters where practical.
+- For small Ruby method definitions that take keyword arguments, match the
+  local no-parentheses style when nearby code uses it.
+- When an `if` condition is split across multiple lines and combines clauses
+  with `&&` or `||`, wrap the whole condition in parentheses.
+- Treat Ruby hash `{ ... }` style and Ruby block `{ ... }` style as separate
+  rules.
+- Do not format Ruby hashes like Ruby blocks.
+- For Ruby hashes, keep the closing `}` on the same line as the final pair.
+- Keep the first pair on the same line as `{` by default.
+- Short Ruby hashes may stay visually compact across two lines with the first
+  pair kept on the opening line and aligned continuation pairs below it.
+- If the hash would exceed the line limit, break after `{` and indent pairs
+  by 4 spaces.
+- Put one logical pair per line when the expression would otherwise become
+  dense.
+- For Ruby arrays, never put whitespace or a line break immediately before `]`.
+- Keep the first element on the same line as `[` by default.
+- If an array would exceed the line limit, break after `[` and indent
+  elements by 4 spaces.
+- For Ruby blocks, use 2-space indentation for the block body.
+- Keep comments short and useful; avoid narrating obvious code.
+- Do not add production dependencies without approval.
+
+## Authentication and authorization
+
+- Authentication is handled through the `X-Transfer-Code` header in
+  `ApplicationController#authenticate_user`.
+- `current_user` is set by looking up `User.inheritance_code`.
+- Do not bypass or weaken the `X-Transfer-Code` flow unless the task
+  explicitly changes authentication.
+- Unauthenticated write actions should return `:unauthorized` consistently
+  with existing controllers.
+- Role checks use `User` enum roles: `guest`, `member`, and `admin`.
+- Use `current_user.gte_member?` for member-or-admin write permissions where
+  existing controllers do so.
+- Use `current_user.admin?` only for admin-only paths, such as tag child relationship changes.
+- Do not replace role checks with looser presence checks.
+
+## BAN and IP BAN
+
+- `ApplicationController` runs these before actions in order:
+  - `reject_banned_ip_address!`
+  - `authenticate_user`
+  - `reject_banned_user!`
+- User and IP bans use `banned_at`, not a boolean `banned` column.
+- `User#banned?` and `IpAddress#banned?` check `banned_at.present?`.
+- Do not weaken BAN or IP BAN behavior.
+- If changing request authentication or controller before actions, add or
+  update request specs covering banned users and banned IP addresses only when
+  the user explicitly asks for tests.
+
+## RSpec
+
+- Prefer RSpec for new backend tests.
+- Put API behavior coverage under `spec/requests`.
+- Put model behavior under `spec/models`.
+- Put service behavior under `spec/services`.
+- Put Rake task coverage under `spec/tasks`.
+- `spec/rails_helper.rb` loads `spec/support/**/*.rb`.
+- Request specs include `AuthHelper` and `JsonHelper`.
+- `AuthHelper#sign_in_as(user)` stubs
+  `ApplicationController#current_user`; use it when matching existing
+  request spec style.
+- Add or update request specs for API behavior changes only when the user
+  explicitly asks for tests, especially status codes, permissions, response
+  shape, and version conflict behavior.
+
+## Migrations
+
+- Keep migrations and `db/schema.rb` consistent.
+- Use reversible migrations where practical; otherwise define explicit `up` and `down`.
+- For data backfills inside migrations, follow the existing pattern of
+  defining migration-local `ActiveRecord::Base` classes with
+  `self.table_name`.
+- Preserve existing indexes, foreign keys, check constraints, and null constraints.
+- Be careful with MySQL-specific options already present in migrations, such as `after:`.
+- Do not edit old migrations just to change current behavior unless
+  explicitly requested; add a new migration.
+
+## Version tables
+
+- Versioned records include posts, tags, nico tags, and wiki pages.
+- Current records have `version_no`; version tables have positive
+  `version_no` with unique indexes scoped to the parent record.
+- Version event types are `create`, `update`, `discard`, and `restore`.
+- Version rows are readonly through the `VersionRecord` concern.
+- Use the existing recorder services instead of manually inserting version
+  rows in application code:
+  - `PostVersionRecorder`
+  - `TagVersionRecorder`
+  - `NicoTagVersionRecorder`
+  - `WikiVersionRecorder`
+  - `TagVersioning`
+- `VersionRecorder` locks the current record, validates sequence consistency,
+  skips unchanged update snapshots, creates the next version row, and updates
+  the record `version_no`.
+- Do not update versioned records without considering whether a version snapshot must be created.
+- For optimistic concurrency paths, preserve `base_version_no`, `force`, and
+  `merge` semantics. Cover conflicts in request specs only when the user
+  explicitly asks for tests.
+
+## Domain cautions
+
+- Posts have tag snapshots, parent post implications, original-created ranges,
+  viewed state, and version conflict behavior.
+- Tags have canonical names, aliases through `TagName`, categories, parent
+  implications, discard behavior, and version snapshots.
+- Nico tags have separate relation/version behavior; do not treat them like
+  normal editable tags without checking existing code.
+- Wiki pages involve page content, revisions/history, version rows,
+  title/tag-name behavior, and diff/restore paths.
+- Materials, theatres, and comments have user and permission checks; inspect
+  the controller before changing them.
+
+## API responses
+
+- Use representation classes under `app/representations` when existing endpoints do.
+- Keep response keys consistent with existing JSON contracts.
+- Frontend code expects camelCase conversion client-side, while Rails params
+  and JSON keys are generally snake_case.
+- Preserve existing HTTP status conventions:
+  `:unauthorized` for no user, `:forbidden` for insufficient role or banned
+  user, `:not_found` for missing records, and `:unprocessable_entity` for
+  validation failures.
+- For diagnostic or internal helper JSON, prefer a deliberately light response
+  shape over full representation classes when callers only need identifiers,
+  labels, URLs, or weights.
+
+## Active Record performance
+
+- When a controller action serializes nested associations, preload the
+  associations it will touch instead of allowing N+1 queries.
+- Be sensitive to N+1 queries in all backend work.
+- Avoid introducing N+1 queries, and proactively fix existing N+1 issues when
+  you find them in the code path you are editing.
+- When an association may already be preloaded, prefer loaded-association
+  checks that reuse the preloaded data without losing the efficient database
+  path.
+
+## Files to avoid in routine work
+
+- Do not inspect or edit `tmp/`, `log/`, `storage/`, `vendor/`, or dependency
+  directories unless explicitly needed.
+- Do not modify generated schema or migration output without the corresponding
+  migration when schema changes are made.

backend/Gemfile

@@ -69,3 +69,5 @@ gem 'discard'
 gem "rspec-rails", "~> 8.0", :groups => [:development, :test]
 
 gem 'aws-sdk-s3', require: false
+
+gem 'rails-i18n', '~> 8.0.0'

backend/Gemfile.lock

@@ -306,6 +306,9 @@ GEM
     rails-html-sanitizer (1.6.2)
       loofah (~> 2.21)
       nokogiri (>= 1.15.7, != 1.16.7, != 1.16.6, != 1.16.5, != 1.16.4, != 1.16.3, != 1.16.2, != 1.16.1, != 1.16.0.rc1, != 1.16.0)
+    rails-i18n (8.0.2)
+      i18n (>= 0.7, < 2)
+      railties (>= 8.0.0, < 9)
     railties (8.0.2)
       actionpack (= 8.0.2)
       activesupport (= 8.0.2)
@@ -477,6 +480,7 @@ DEPENDENCIES
   puma (>= 5.0)
   rack-cors
   rails (~> 8.0.2)
+  rails-i18n (~> 8.0.0)
   rspec-rails (~> 8.0)
   rubocop-rails-omakase
   sprockets-rails

backend/app/controllers/application_controller.rb

@@ -1,14 +1,19 @@
 class ApplicationController < ActionController::API
-  before_action :authenticate_user
+  rescue_from ActiveRecord::RecordInvalid, with: :render_record_invalid
+  rescue_from ActiveRecord::RecordNotUnique, with: :render_record_not_unique
 
-  def current_user
-    @current_user
-  end
+  before_action :reject_banned_ip_address!
+  before_action :authenticate_user
+  before_action :reject_banned_user!
+
+  def current_user = @current_user
 
   private
 
   def authenticate_user
     code = request.headers['X-Transfer-Code'] || request.headers['HTTP_X_TRANSFER_CODE']
+    return if code.blank?
+
     @current_user = User.find_by(inheritance_code: code)
   end
 
@@ -22,4 +27,62 @@ class ApplicationController < ActionController::API
       s.in?(['', '1', 'true', 'on', 'yes'])
     end
   end
+
+  def render_bad_request message = 'リクエストが不正です．'
+    render json: { type: 'bad_request',
+                   message:,
+                   errors: { },
+                   base_errors: [message] },
+           status: :bad_request
+  end
+
+  def render_unprocessable_entity message = '入力を確認してください．', field: nil
+    render_validation_error(fields: field ? { field => [message] } : { },
+                            base: field ? [] : [message])
+  end
+
+  def render_record_invalid error
+    render_validation_error error.record
+  end
+
+  def render_record_not_unique _error = nil
+    render_validation_error base: ['すでに存在してゐます．']
+  end
+
+  def reject_banned_ip_address!
+    ip_address = IpAddress.find_by(ip_address: IPAddr.new(request.remote_ip).hton)
+    return unless ip_address&.banned?
+
+    head :forbidden
+  end
+
+  def reject_banned_user!
+    return unless current_user&.banned?
+
+    head :forbidden
+  end
+
+  def render_validation_error record = nil, fields: { }, base: [], status: :unprocessable_entity
+    errors = { }
+
+    if record
+      record.errors.each do |error|
+        errors[error.attribute] ||= []
+        errors[error.attribute] << error.message
+      end
+    end
+
+    fields.each do |attr, messages|
+      errors[attr.to_sym] ||= []
+      errors[attr.to_sym].concat(Array(messages))
+    end
+
+    base_errors = Array(base) + Array(errors.delete(:base))
+
+    render json: { type: 'validation_error',
+                   message: '入力内容を確認してください．',
+                   errors:,
+                   base_errors: },
+           status:
+  end
 end

backend/app/controllers/deerjikists_controller.rb

@@ -2,7 +2,8 @@ class DeerjikistsController < ApplicationController
   def show
     platform = params[:platform].to_s.strip
     code = params[:code].to_s.strip
-    return head :bad_request if platform.blank? || code.blank?
+    return render_bad_request('platform は必須です．') if platform.blank?
+    return render_bad_request('code は必須です．') if code.blank?
 
     deerjikist = Deerjikist
                  .joins(:tag)
@@ -22,7 +23,9 @@ class DeerjikistsController < ApplicationController
     platform = params[:platform].to_s.strip
     code = params[:code].to_s.strip
     tag_id = params[:tag_id].to_i
-    return head :bad_request if platform.blank? || code.blank? || tag_id <= 0
+    return render_bad_request('platform は必須です．') if platform.blank?
+    return render_bad_request('code は必須です．') if code.blank?
+    return render_bad_request('tag_id が不正です．') if tag_id <= 0
 
     deerjikist = Deerjikist.find_or_initialize_by(platform:, code:).tap do |d|
       d.tag_id = tag_id
@@ -38,7 +41,8 @@ class DeerjikistsController < ApplicationController
 
     platform = params[:platform].to_s.strip
     code = params[:code].to_s.strip
-    return head :bad_request if platform.blank? || code.blank?
+    return render_bad_request('platform は必須です．') if platform.blank?
+    return render_bad_request('code は必須です．') if code.blank?
 
     Deerjikist.find([platform, code]).destroy!
 

backend/app/controllers/gekanator_games_controller.rb

@@ -0,0 +1,272 @@
+class GekanatorGamesController < ApplicationController
+  def create
+    return head :unauthorized unless current_user
+
+    guessed_post_id = params.require(:guessed_post_id)
+    correct_post_id = params[:correct_post_id].presence
+    answers = params.require(:answers).as_json
+
+    game = GekanatorGame.new(
+        user: current_user,
+        guessed_post_id:,
+        correct_post_id:,
+        won: correct_post_id.present? && guessed_post_id.to_i == correct_post_id.to_i,
+        question_count: answers.length,
+        answers:)
+
+    if game.invalid?
+      render json: { errors: game.errors.full_messages }, status: :unprocessable_entity
+      return
+    end
+
+    learned_example_count = 0
+
+    ActiveRecord::Base.transaction do
+      game.save!
+      learned_example_count = learn_answers_from_game!(game)
+    end
+
+    render json: {
+      id: game.id,
+      learned_example_count:
+    }, status: :created
+  rescue ActiveRecord::RecordInvalid => e
+    render json: { errors: e.record.errors.full_messages }, status: :unprocessable_entity
+  end
+
+  def extra_questions
+    game = find_owned_game
+    return if performed?
+
+    questions =
+        GekanatorQuestion
+        .accepted
+        .includes(:gekanator_question_examples)
+        .where(kind: 'post_similarity', source: 'user_suggested')
+        .to_a
+
+    selected =
+        prioritized_extra_questions(
+            questions,
+            post_id: game.correct_post_id,
+            user: current_user,
+            limit: 6)
+
+    render json: {
+      questions: selected.map { |question| extra_question_json(question) }
+    }
+  end
+
+  def extra_question_answers
+    game = find_owned_game
+    return if performed?
+
+    answer_params = params.require(:answers)
+    if !answer_params.is_a?(Array)
+      return render_validation_error fields: { answers: ['配列で指定してください．'] }
+    end
+
+    answers = answer_params.map { |answer|
+      {
+        question_id: answer.require(:question_id).to_i,
+        answer: answer.require(:answer)
+      }
+    }
+    questions = GekanatorQuestion.where(id: answers.map { _1[:question_id] })
+    question_by_id = questions.index_by(&:id)
+    if questions.length != answers.length
+      return render_validation_error fields: { answers: ['質問が見つかりません．'] }
+    end
+    if questions.any? { |question| question.status != 'accepted' || question.kind != 'post_similarity' }
+      return render_validation_error fields: { answers: ['質問が不正です．'] }
+    end
+
+    ActiveRecord::Base.transaction do
+      answers.each do |item|
+        question = question_by_id[item[:question_id]]
+        example =
+            GekanatorQuestionExample.find_or_initialize_by(
+                gekanator_question: question,
+                post: game.correct_post,
+                user: current_user)
+        example.record_answer!(
+            answer: item[:answer],
+            source: 'post_game_extra',
+            gekanator_game: game)
+        example.save!
+      end
+    end
+
+    render json: { count: answers.length }, status: :created
+  end
+
+  private
+
+  def extra_question_json question
+    {
+      id: question.id,
+      text: question.text,
+      source: question.source,
+      priority_weight: question.priority_weight
+    }
+  end
+
+  def prioritized_extra_questions questions, post_id:, user:, limit:
+    answered_question_ids =
+        GekanatorQuestionExample
+        .where(user:, gekanator_question_id: questions.map(&:id))
+        .distinct
+        .pluck(:gekanator_question_id)
+    unanswered, answered =
+        questions.partition { |question| !answered_question_ids.include?(question.id) }
+    selected = weighted_sample_questions(unanswered, post_id:, limit:)
+    return selected if selected.length >= limit
+
+    selected + weighted_sample_questions(
+        answered.reject { |question| selected.any? { _1.id == question.id } },
+        post_id:,
+        limit: limit - selected.length)
+  end
+
+  def weighted_sample_questions questions, post_id:, limit:
+    remaining = questions.uniq(&:id)
+    selected = []
+
+    while selected.length < limit && remaining.any?
+      weighted =
+          remaining.map { |question|
+            [question, selection_weight_for(question, post_id: post_id)]
+          }
+      total_weight = weighted.sum { |_question, weight| weight }
+      break if total_weight <= 0
+
+      target = rand * total_weight
+      cumulative = 0.0
+      chosen =
+          weighted.find do |_question, weight|
+            cumulative += weight
+            cumulative >= target
+          end&.first || weighted.first.first
+
+      selected << chosen
+      remaining.reject! { |question| question.id == chosen.id }
+    end
+
+    selected
+  end
+
+  def selection_weight_for question, post_id:
+    sample_count =
+        question.gekanator_question_examples.sum { |example|
+          next 0 unless example.post_id == post_id
+
+          example.sample_count.presence || 1
+        }
+
+    question.priority_weight.to_f / (1.0 + sample_count * 0.15)
+  end
+
+  def find_owned_game
+    return head :unauthorized unless current_user
+
+    game = GekanatorGame.find_by(id: params[:id])
+    return head :not_found unless game
+    if !current_user.admin? && game.user_id != current_user.id
+      return head :not_found
+    end
+
+    game
+  end
+
+  def learn_answers_from_game! game
+    correct_post = game.correct_post
+    return 0 if correct_post.blank?
+
+    accepted_questions =
+        GekanatorQuestion
+        .accepted
+        .index_by { |question| public_question_id_for(question) }
+    learned_count = 0
+
+    Array(game.answers).each do |answer|
+      answer_value = answer['answer'].to_s
+      next if answer_value.blank? || answer_value == 'unknown'
+
+      question_id = game_answer_question_id(answer)
+      next if question_id.blank?
+
+      question = accepted_questions[question_id.to_s]
+      next unless learnable_game_answer_question?(question)
+
+      example =
+          GekanatorQuestionExample.find_or_initialize_by(
+              gekanator_question: question,
+              post: correct_post,
+              user: current_user)
+      example.record_answer!(
+          answer: answer_value,
+          source: 'post_game_answer',
+          gekanator_game: game)
+      example.save!
+      learned_count += 1
+    end
+
+    learned_count
+  end
+
+  def public_question_id_for question
+    condition = normalize_condition(question.condition)
+
+    case condition[:type]
+    when 'tag'
+      "tag:#{condition[:key]}"
+    when 'source'
+      "source:#{condition[:host]}"
+    when 'original-year'
+      "original-year:#{condition[:year]}"
+    when 'original-month'
+      "original-month:#{condition[:month]}"
+    when 'original-month-day'
+      "original-month-day:#{condition[:monthDay] || condition[:month_day]}"
+    when 'title-length-at-least'
+      "title:length-at-least:#{condition[:length]}"
+    when 'title-length-greater-than'
+      "title:length-at-least:#{condition[:length].to_i + 1}"
+    when 'title-has-ascii'
+      'title:ascii'
+    when 'title-contains'
+      "title:contains:#{condition[:text]}"
+    when 'post-similarity'
+      "post-similarity:#{question.id}"
+    else
+      "catalog:#{question.id}"
+    end
+  end
+
+  def normalize_condition condition
+    json = condition.deep_dup.as_json
+
+    if json['type'] == 'original-month-day' && json['monthDay'].blank?
+      json['monthDay'] = json.delete('month_day')
+    end
+
+    json.deep_symbolize_keys
+  end
+
+  def learnable_game_answer_question? question
+    return false if question.nil?
+    return true if question.kind == 'post_similarity'
+    return false unless question.kind == 'tag'
+
+    condition = normalize_condition(question.condition)
+    key = condition[:key].to_s
+    !key.start_with?('nico:')
+  end
+
+  def game_answer_question_id answer
+    answer['question_id'] ||
+      answer[:question_id] ||
+      answer['questionId'] ||
+      answer[:questionId]
+  end
+end

backend/app/controllers/gekanator_posts_controller.rb

@@ -0,0 +1,71 @@
+class GekanatorPostsController < ApplicationController
+  def index
+    posts =
+        Post
+        .preload(:post_similarities, tags: :tag_name)
+        .with_attached_thumbnail
+        .order(Arel.sql(
+            'COALESCE(posts.original_created_before - INTERVAL 1 MINUTE, ' \
+            'posts.original_created_from, posts.created_at) DESC, posts.id DESC'))
+        .to_a
+
+    active_tags_by_post_id =
+        posts.each_with_object({ }) do |post, h|
+          h[post.id] = post.tags.reject(&:deprecated?)
+        end
+
+    render json: {
+      posts: posts.map { |post|
+        post_json(post,
+                  active_tags_by_post_id:)
+      }
+    }
+  end
+
+  private
+
+  def post_json post, active_tags_by_post_id:
+    {
+      id: post.id,
+      url: post.url,
+      title: post.title,
+      thumbnail: thumbnail_url(post),
+      thumbnail_base: post.thumbnail_base,
+      original_created_from: post.original_created_from,
+      original_created_before: post.original_created_before,
+      post_similarity_edges: post_similarity_edges_json(
+          post,
+          active_tags_by_post_id:),
+      tags: active_tags_by_post_id.fetch(post.id, []).map { |tag| tag_json(tag) }
+    }
+  end
+
+  def post_similarity_edges_json post, active_tags_by_post_id:
+    post
+        .post_similarities
+        .filter_map do |similarity|
+          next unless active_tags_by_post_id.key?(similarity.target_post_id)
+
+          {
+            target_post_id: similarity.target_post_id,
+            cos: similarity.cos.to_f
+          }
+        end
+  end
+
+  def tag_json tag
+    {
+      id: tag.id,
+      name: tag.name,
+      category: tag.category
+    }
+  end
+
+  def thumbnail_url post
+    return nil unless post.thumbnail.attached?
+
+    rails_storage_proxy_url(post.thumbnail, only_path: false)
+  rescue
+    nil
+  end
+end

backend/app/controllers/gekanator_question_suggestions_controller.rb

@@ -0,0 +1,95 @@
+class GekanatorQuestionSuggestionsController < ApplicationController
+  def create
+    return head :unauthorized unless current_user
+
+    game = GekanatorGame.find_by(id: params.require(:gekanator_game_id))
+    return head :not_found unless game
+    if !current_user.admin? && game.user_id != current_user.id
+      return head :not_found
+    end
+
+    existing_question_id = params[:existing_question_id].presence
+    if existing_question_id
+      question = GekanatorQuestion.accepted.find_by(id: existing_question_id)
+      return head :not_found unless question
+      unless learnable_existing_question?(question)
+        return render_validation_error fields: { existing_question_id: ['質問が不正です．'] }
+      end
+
+      example =
+          GekanatorQuestionExample.find_or_initialize_by(
+              gekanator_question: question,
+              post: game.correct_post,
+              user: current_user)
+      example.record_answer!(
+          answer: params.require(:answer),
+          source: 'post_game_extra',
+          gekanator_game: game)
+
+      if example.save
+        render json: {
+          id: question.id,
+          count: game.question_suggestions.count
+        }, status: :created
+      else
+        render_validation_error example
+      end
+      return
+    end
+
+    suggestion = GekanatorQuestionSuggestion.new(
+        gekanator_game: game,
+        user: current_user,
+        question_text: params.require(:question_text),
+        answer: params.require(:answer))
+
+    if suggestion.valid?
+      ActiveRecord::Base.transaction do
+        suggestion.save!
+        Gekanator::QuestionSuggestionPromoter.call(
+            suggestion: suggestion,
+            user: current_user)
+      end
+
+      render json: {
+        id: suggestion.id,
+        count: game.question_suggestions.count
+      }, status: :created
+    else
+      render_validation_error suggestion
+    end
+  end
+
+  def ai_convert
+    return head :not_found unless current_user&.admin?
+
+    suggestion = GekanatorQuestionSuggestion.find_by(id: params[:id])
+    return head :not_found unless suggestion
+    if Gekanator::AiRunBudget.exceeded_after_next_run?
+      suggestion.gekanator_ai_runs.create!(
+          model: 'budget_guard',
+          status: 'blocked_budget',
+          input_tokens: 0,
+          output_tokens: 0,
+          estimated_cost_jpy: 0)
+      return head :payment_required
+    end
+
+    Gekanator::QuestionSuggestionAiConverter.call(
+        suggestion: suggestion,
+        user: current_user)
+    head :no_content
+  rescue NotImplementedError
+    head :not_implemented
+  end
+
+  private
+
+  def learnable_existing_question? question
+    return true if question.kind == 'post_similarity'
+    return false unless question.kind == 'tag'
+
+    key = question.condition.as_json['key'].to_s
+    !key.start_with?('nico:')
+  end
+end

backend/app/controllers/gekanator_questions_controller.rb

@@ -0,0 +1,147 @@
+class GekanatorQuestionsController < ApplicationController
+  def index
+    questions =
+        GekanatorQuestion
+        .accepted
+        .includes(:gekanator_question_examples)
+        .order(priority_weight: :desc, id: :asc)
+        .to_a
+    deprecated_tag_keys = deprecated_tag_keys_for(questions)
+
+    render json: {
+      questions: questions.filter_map { |question|
+        json = question_json(question)
+        next if hidden_question?(json[:condition], deprecated_tag_keys)
+
+        json
+      }
+    }
+  end
+
+  private
+
+  def question_json question
+    condition = condition_json(question.condition).deep_symbolize_keys
+    json = {
+      record_id: question.id,
+      id: question_id_for(question, condition),
+      text: question_text_for(question, condition),
+      kind: question.kind,
+      condition: condition,
+      source: question.source,
+      priority_weight: question.priority_weight
+    }
+    if question.kind == 'post_similarity' || question.kind == 'tag'
+      json[:example_answers] = example_answers_json(question)
+    end
+    json
+  end
+
+  def question_id_for question, condition
+    case condition[:type]
+    when 'tag'
+      "tag:#{ condition[:key] }"
+    when 'source'
+      "source:#{ condition[:host] }"
+    when 'original-year'
+      "original-year:#{ condition[:year] }"
+    when 'original-month'
+      "original-month:#{ condition[:month] }"
+    when 'original-month-day'
+      "original-month-day:#{ condition[:monthDay] || condition[:month_day] }"
+    when 'title-length-at-least'
+      "title:length-at-least:#{ condition[:length] }"
+    when 'title-length-greater-than'
+      "title:length-at-least:#{ condition[:length].to_i + 1 }"
+    when 'title-has-ascii'
+      'title:ascii'
+    when 'title-contains'
+      "title:contains:#{ condition[:text] }"
+    when 'post-similarity'
+      "post-similarity:#{ question.id }"
+    else
+      "catalog:#{ question.id }"
+    end
+  end
+
+  def condition_json condition
+    json = condition.deep_dup.as_json
+
+    if json['type'] == 'original-month-day' && json['monthDay'].blank?
+      json['monthDay'] = json.delete('month_day')
+    end
+
+    if json['type'] == 'title-length-greater-than'
+      json['type'] = 'title-length-at-least'
+      json['length'] = json['length'].to_i + 1
+    end
+
+    json
+  end
+
+  def question_text_for question, condition
+    return question.text unless question.kind == 'title'
+
+    case condition[:type]
+    when 'title-length-at-least'
+      "タイトルは #{ condition[:length] } 文字以上?"
+    when 'title-contains'
+      "題名に「#{ condition[:text] }」が含まれる?"
+    else
+      question.text
+    end
+  end
+
+  def example_answers_json question
+    question
+        .gekanator_question_examples
+        .group_by(&:post_id)
+        .transform_values { |examples| aggregate_answer(examples) }
+  end
+
+  def aggregate_answer examples
+    examples
+        .group_by(&:answer)
+        .map { |answer, grouped| [answer, grouped.sum(&:weight), grouped.max_by(&:updated_at)&.updated_at] }
+        .sort_by { |(_answer, weight, updated_at)| [-weight, -(updated_at&.to_f || 0)] }
+        .first
+        &.first
+  end
+
+  def deprecated_tag_keys_for questions
+    tag_keys = questions.filter_map { |question|
+      condition = condition_json(question.condition)
+      next unless condition['type'] == 'tag'
+
+      condition['key'].to_s.presence
+    }.uniq
+    return {} if tag_keys.empty?
+
+    categories = []
+    names = []
+    tag_keys.each do |key|
+      category, name = parse_tag_key(key)
+      categories << category
+      names << name
+    end
+
+    Tag
+        .joins(:tag_name)
+        .where(category: categories.uniq)
+        .where(tag_names: { name: names.uniq })
+        .where.not(deprecated_at: nil)
+        .pluck('tags.category', 'tag_names.name')
+        .each_with_object({ }) do |(category, name), h|
+          h["#{ category }:#{ name }"] = true
+        end
+  end
+
+  def hidden_question? condition, deprecated_tag_keys
+    condition[:type] == 'tag' && deprecated_tag_keys[condition[:key].to_s]
+  end
+
+  def parse_tag_key key
+    parts = key.to_s.split(':')
+    [parts.first.to_s, parts.drop(1).join(':')]
+  end
+end

backend/app/controllers/material_sync_suppressions_controller.rb

@@ -0,0 +1,49 @@
+class MaterialSyncSuppressionsController < ApplicationController
+  def index
+    return head :unauthorized unless current_user
+    return head :forbidden unless current_user.gte_member?
+
+    suppressions =
+        MaterialSyncSuppression
+        .includes(:created_by_user)
+        .order(created_at: :desc, id: :desc)
+
+    render json: { suppressions: suppressions.map { |s| repr(s) } }
+  end
+
+  def create
+    return head :unauthorized unless current_user
+    return head :forbidden unless current_user.gte_member?
+
+    suppression =
+        MaterialSyncSuppressionRegistrar.create!(suppression_params,
+                                                 created_by_user: current_user)
+
+    render json: repr(suppression), status: :created
+  rescue ActiveRecord::RecordInvalid => e
+    render_validation_error e.record
+  end
+
+  private
+
+  def suppression_params
+    params.permit(:source_kind,
+                  :source_uri,
+                  :drive_path,
+                  :drive_file_id,
+                  :reason)
+  end
+
+  def repr suppression
+    { id: suppression.id,
+      source_kind: suppression.source_kind,
+      source_uri: suppression.source_uri,
+      drive_path: suppression.drive_path,
+      drive_file_id: suppression.drive_file_id,
+      normalized_source_key: suppression.normalized_source_key,
+      reason: suppression.reason,
+      created_by_user: suppression.created_by_user&.as_json(UserRepr::BASE),
+      created_at: suppression.created_at,
+      updated_at: suppression.updated_at }
+  end
+end

backend/app/controllers/material_versions_controller.rb

@@ -0,0 +1,54 @@
+class MaterialVersionsController < ApplicationController
+  def index
+    page = (params[:page].presence || 1).to_i
+    limit = (params[:limit].presence || 20).to_i
+
+    page = 1 if page < 1
+    limit = 1 if limit < 1
+
+    offset = (page - 1) * limit
+
+    q = MaterialVersion.includes(:created_by_user)
+    q = q.where(material_id: params[:material_id]) if params[:material_id].present?
+    q = q.where(tag_name: params[:tag].to_s.strip) if params[:tag].present?
+    q = q.where(event_type: params[:event_type]) if params[:event_type].present?
+
+    count = q.except(:order, :limit, :offset).count
+    versions =
+        q
+        .order(Arel.sql('material_versions.created_at DESC, material_versions.id DESC'))
+        .limit(limit)
+        .offset(offset)
+
+    render json: { versions: serialise_versions(versions), count: }
+  end
+
+  private
+
+  def serialise_versions rows
+    rows.map do |row|
+      { id: row.id,
+        material_id: row.material_id,
+        version_no: row.version_no,
+        event_type: row.event_type,
+        tag_id: row.tag_id,
+        tag_name: row.tag_name,
+        tag_category: row.tag_category,
+        url: row.url,
+        source_kind: row.source_kind,
+        source_uri: row.source_uri,
+        source_path: row.source_path,
+        source_file_id: row.source_file_id,
+        normalized_source_key: row.normalized_source_key,
+        file_blob_id: row.file_blob_id,
+        file_filename: row.file_filename,
+        file_content_type: row.file_content_type,
+        file_byte_size: row.file_byte_size,
+        file_sha256: row.file_sha256,
+        export_paths_json: row.export_paths_hash,
+        discarded_at: row.discarded_at,
+        created_by_user: row.created_by_user&.as_json(UserRepr::BASE),
+        created_at: row.created_at }
+    end
+  end
+end

backend/app/controllers/materials_controller.rb

@@ -1,4 +1,11 @@
+require 'set'
+
+
 class MaterialsController < ApplicationController
+  rescue_from MaterialZipExporter::EmptyExportError, with: :render_zip_empty
+  rescue_from MaterialZipExporter::DuplicatePathError, with: :render_zip_duplicate_path
+  rescue_from MaterialZipExporter::MissingFileError, with: :render_zip_missing_file
+
   def index
     page = (params[:page].presence || 1).to_i
     limit = (params[:limit].presence || 20).to_i
@@ -8,53 +15,101 @@ class MaterialsController < ApplicationController
 
     offset = (page - 1) * limit
 
-    tag_id = params[:tag_id].presence
-    parent_id = params[:parent_id].presence
+    filters = material_index_filters
+    tag_graph = material_index_tag_graph(filters)
+    q = Material.includes(:material_export_items,
+                          thumbnail_attachment: :blob,
+                          file_attachment: :blob,
+                          tag: :tag_name)
+    q = q.where(tag_id: nil) if filters[:tag_state] == 'untagged'
+    q = q.where.not(tag_id: nil) if filters[:tag_state] == 'tagged'
+    q = apply_material_tag_filter(q, filters, tag_graph)
+    q = q.where('materials.created_at >= ?', filters[:created_from]) if filters[:created_from]
+    q = q.where('materials.created_at <= ?', filters[:created_to]) if filters[:created_to]
+    q = q.where('materials.updated_at >= ?', filters[:updated_from]) if filters[:updated_from]
+    q = q.where('materials.updated_at <= ?', filters[:updated_to]) if filters[:updated_to]
 
-    q = Material.includes(:tag, :created_by_user).with_attached_file
-    q = q.where(tag_id:) if tag_id
-    q = q.where(parent_id:) if parent_id
+    q = material_index_join_tag_name(q) if material_index_needs_tag_name?(filters)
+    q = material_index_join_file_blob(q) if material_index_needs_file_blob?(filters)
+    q = apply_material_query(q, filters[:q]) if filters[:q].present?
+    q = apply_material_media_kind(q, filters[:media_kind])
 
-    count = q.count
-    materials = q.order(created_at: :desc, id: :desc).limit(limit).offset(offset)
+    count = q.distinct.count(:id)
+    materials =
+        q
+        .order(Arel.sql(material_index_order_sql(filters)))
+        .limit(limit)
+        .offset(offset)
+        .to_a
 
-    render json: { materials: MaterialRepr.many(materials, host: request.base_url), count: count }
+    response = { materials: MaterialRepr.list_many(materials, host: request.base_url),
+                 count: }
+    if filters[:tag_id]
+      response[:tag_scope] = material_index_tag_scope(filters, tag_graph)
+    end
+    if filters[:group_by] == 'parent_tag'
+      response[:groups] = material_index_groups(materials, tag_graph)
+    end
+
+    render json: response
   end
 
   def show
     material =
         Material
-        .includes(:tag)
+        .includes(:tag, :material_export_items)
+        .with_attached_thumbnail
         .with_attached_file
         .find_by(id: params[:id])
     return head :not_found unless material
 
-    wiki_page_body = material.tag.tag_name.wiki_page&.current_revision&.body
+    wiki_page_body = material.tag&.tag_name&.wiki_page&.current_revision&.body
 
     render json: MaterialRepr.base(material, host: request.base_url).merge(wiki_page_body:)
   end
 
   def create
     return head :unauthorized unless current_user
+    return head :forbidden unless current_user.gte_member?
 
     tag_name_raw = params[:tag].to_s.strip
     file = params[:file]
+    file_sha256 = MaterialFileSha256.from_upload(file)
     url = params[:url].to_s.strip.presence
-    return head :bad_request if tag_name_raw.blank? || (file.blank? && url.blank?)
+    return render_unprocessable_entity('タグは必須です．', field: :tag) if tag_name_raw.blank?
+    if file.blank? && url.blank?
+      return render_validation_error fields: { file: ['ファイルまたは URL は必須です．'],
+                                               url: ['ファイルまたは URL は必須です．'] }
+    end
+    block = MaterialImportBlockMatcher.match_for_sha256(file_sha256)
+    return render_material_import_block(block) if block
 
-    tag_name = TagName.find_undiscard_or_create_by!(name: tag_name_raw)
-    tag = tag_name.tag
-    tag = Tag.create!(tag_name:, category: :material) unless tag
+    uploaded_blob = build_uploaded_material_blob!(file, file_sha256)
+    material = nil
 
-    material = Material.new(tag:, url:,
-                            created_by_user: current_user,
-                            updated_by_user: current_user)
-    material.file.attach(file)
+    begin
+      Material.transaction do
+        tag = resolve_material_tag!(tag_name_raw)
+        material = Material.new(tag:, url:,
+                                created_by_user: current_user,
+                                updated_by_user: current_user)
+        material.file.attach(uploaded_blob) if uploaded_blob
+        material.save!
+        TagVersioning.record_tag_snapshot!(tag, created_by_user: current_user)
+        upsert_export_paths!(material)
+        MaterialVersionRecorder.record!(material:, event_type: :create,
+                                        created_by_user: current_user)
+      end
+    rescue StandardError
+      uploaded_blob&.purge_later
+      raise
+    end
 
-    if material.save
+    if material
+      log_thumbnail_generation(material, MaterialThumbnailGenerator.generate!(material))
       render json: MaterialRepr.base(material, host: request.base_url), status: :created
     else
-      render json: { errors: material.errors.full_messages }, status: :unprocessable_entity
+      render_validation_error material
     end
   end
 
@@ -67,25 +122,42 @@ class MaterialsController < ApplicationController
 
     tag_name_raw = params[:tag].to_s.strip
     file = params[:file]
-    url = params[:url].to_s.strip.presence
-    return head :bad_request if tag_name_raw.blank? || (file.blank? && url.blank?)
+    file_sha256 = MaterialFileSha256.from_upload(file)
+    url = params.key?(:url) ? params[:url].to_s.strip.presence : material.url
+    return render_unprocessable_entity('タグは必須です．', field: :tag) if tag_name_raw.blank?
+    if file.blank? && url.blank? && !material.file.attached?
+      return render_validation_error fields: { file: ['ファイルまたは URL は必須です．'],
+                                               url: ['ファイルまたは URL は必須です．'] }
+    end
+    block = MaterialImportBlockMatcher.match_for_sha256(file_sha256)
+    return render_material_import_block(block) if block
 
-    tag_name = TagName.find_undiscard_or_create_by!(name: tag_name_raw)
-    tag = tag_name.tag
-    tag = Tag.create!(tag_name:, category: :material) unless tag
+    uploaded_blob = build_uploaded_material_blob!(file, file_sha256)
 
-    material.update!(tag:, url:, updated_by_user: current_user)
-    if file
-      material.file.attach(file)
-    else
-      material.file.purge
+    begin
+      Material.transaction do
+        MaterialVersionRecorder.ensure_snapshot!(material, created_by_user: current_user)
+        tag = resolve_material_tag!(tag_name_raw)
+        material.assign_attributes(tag:, url:, updated_by_user: current_user)
+        if uploaded_blob
+          material.file.attach(uploaded_blob)
+        elsif params.key?(:url) && url.present? && file.blank?
+          material.file.detach
+        end
+        material.save!
+        TagVersioning.record_tag_snapshot!(tag, created_by_user: current_user)
+        upsert_export_paths!(material)
+        MaterialVersionRecorder.record!(material:, event_type: :update,
+                                        created_by_user: current_user)
+      end
+    rescue StandardError
+      uploaded_blob&.purge_later
+      raise
     end
 
-    if material.save
-      render json: MaterialRepr.base(material, host: request.base_url)
-    else
-      render json: { errors: material.errors.full_messages }, status: :unprocessable_entity
-    end
+    log_thumbnail_generation(material, MaterialThumbnailGenerator.generate!(material))
+
+    render json: MaterialRepr.base(material, host: request.base_url)
   end
 
   def destroy
@@ -95,7 +167,381 @@ class MaterialsController < ApplicationController
     material = Material.find_by(id: params[:id])
     return head :not_found unless material
 
-    material.discard
+    Material.transaction do
+      MaterialVersionRecorder.ensure_snapshot!(material, created_by_user: current_user)
+      material.discard!
+      MaterialVersionRecorder.record!(material:, event_type: :discard,
+                                      created_by_user: current_user)
+    end
     head :no_content
   end
+
+  def download
+    zip = MaterialZipExporter.new(profile: params[:profile],
+                                  tag_id: params[:tag_id]).export
+    profile = params[:profile].presence || 'legacy_drive'
+    send_data zip,
+              type: 'application/zip',
+              disposition: 'attachment',
+              filename: "btrc-materials-#{ profile }.zip"
+  end
+
+  private
+
+  def material_index_filters
+    tag_state = params[:tag_state].to_s.presence
+    tag_state = 'untagged' if bool?(:unclassified)
+    tag_state = 'all' unless ['all', 'tagged', 'untagged'].include?(tag_state)
+
+    media_kind = params[:media_kind].to_s.presence
+    unless ['all', 'image', 'video', 'audio', 'file_other', 'url_only'].include?(media_kind)
+      media_kind = 'all'
+    end
+
+    sort = params[:sort].to_s.presence
+    unless ['created_at', 'updated_at', 'tag_name', 'media_kind', 'file_byte_size',
+            'version_no', 'id'].include?(sort)
+      sort = 'created_at'
+    end
+
+    direction = params[:direction].to_s.downcase
+    direction = 'desc' unless ['asc', 'desc'].include?(direction)
+
+    group_by = params[:group_by].to_s.presence
+    group_by = 'none' unless ['none', 'parent_tag'].include?(group_by)
+
+    tag_id = params[:tag_id].to_i
+    tag_id = nil if tag_id <= 0
+
+    { q: params[:q].to_s.strip.presence,
+      tag_state:,
+      media_kind:,
+      tag_id:,
+      include_descendants: bool?(:include_descendants),
+      group_by:,
+      created_from: parse_time_param(:created_from),
+      created_to: parse_time_param(:created_to),
+      updated_from: parse_time_param(:updated_from),
+      updated_to: parse_time_param(:updated_to),
+      sort:,
+      direction: }
+  end
+
+  def parse_time_param name
+    value = params[name].to_s.strip
+    return nil if value.blank?
+
+    Time.zone.parse(value)
+  rescue ArgumentError
+    nil
+  end
+
+  def resolve_material_tag! tag_name_raw
+    tag_name = TagName.find_undiscard_or_create_by!(name: tag_name_raw)
+    tag = tag_name.tag
+    tag || Tag.create!(tag_name:, category: :material)
+  end
+
+  def material_index_needs_tag_name? filters
+    filters[:q].present? || filters[:sort] == 'tag_name'
+  end
+
+  def material_index_needs_file_blob? filters
+    filters[:q].present? ||
+        filters[:media_kind] != 'all' ||
+        ['media_kind', 'file_byte_size'].include?(filters[:sort])
+  end
+
+  def material_index_join_tag_name q
+    q.left_joins(tag: :tag_name)
+  end
+
+  def material_index_join_file_blob q
+    q.joins(<<~SQL.squish)
+      LEFT JOIN active_storage_attachments material_file_attachments
+        ON material_file_attachments.record_type = 'Material'
+       AND material_file_attachments.record_id = materials.id
+       AND material_file_attachments.name = 'file'
+      LEFT JOIN active_storage_blobs material_file_blobs
+        ON material_file_blobs.id = material_file_attachments.blob_id
+    SQL
+  end
+
+  def apply_material_query q, term
+    like = "%#{ ActiveRecord::Base.sanitize_sql_like(term) }%"
+    q.where('tag_names.name LIKE :q OR materials.url LIKE :q OR ' \
+            'material_file_blobs.filename LIKE :q',
+            q: like)
+  end
+
+  def apply_material_media_kind q, media_kind
+    case media_kind
+    when 'image'
+      q.where('material_file_blobs.content_type LIKE ?', 'image/%')
+    when 'video'
+      q.where('material_file_blobs.content_type LIKE ?', 'video/%')
+    when 'audio'
+      q.where('material_file_blobs.content_type LIKE ?', 'audio/%')
+    when 'file_other'
+      q.where('material_file_attachments.id IS NOT NULL')
+       .where.not('material_file_blobs.content_type LIKE ?', 'image/%')
+       .where.not('material_file_blobs.content_type LIKE ?', 'video/%')
+       .where.not('material_file_blobs.content_type LIKE ?', 'audio/%')
+    when 'url_only'
+      q.where('material_file_attachments.id IS NULL').where.not(url: [nil, ''])
+    else
+      q
+    end
+  end
+
+  def apply_material_tag_filter q, filters, tag_graph
+    return q unless filters[:tag_id]
+
+    tag_ids =
+        if tag_graph
+          tag_graph[:scope_tag_ids]
+        else
+          [filters[:tag_id]]
+        end
+
+    q.where(tag_id: tag_ids)
+  end
+
+  def material_index_tag_graph filters
+    return nil unless filters[:tag_id]
+
+    children_by_parent_id = Hash.new { |h, k| h[k] = [] }
+    TagImplication.pluck(:parent_tag_id, :tag_id).each do |parent_id, child_id|
+      children_by_parent_id[parent_id] << child_id
+    end
+
+    scope_tag_ids =
+        if filters[:include_descendants]
+          collect_material_descendant_ids(filters[:tag_id], children_by_parent_id)
+        else
+          Set.new([filters[:tag_id]])
+        end
+    tags_by_id =
+        Tag
+        .joins(:tag_name)
+        .where(id: scope_tag_ids)
+        .pluck('tags.id', 'tag_names.name', 'tags.category', 'tags.deprecated_at')
+        .each_with_object({ }) do |(id, name, category, deprecated_at), hash|
+          hash[id] = { id:, name:, category:, deprecated: deprecated_at.present? }
+        end
+
+    selected_tag = tags_by_id[filters[:tag_id]]
+    return nil unless selected_tag
+
+    group_tag_id_by_tag_id = { }
+    children_by_parent_id[filters[:tag_id]].each do |child_tag_id|
+      assign_material_group_branch(child_tag_id,
+                                   children_by_parent_id:,
+                                   tags_by_id:,
+                                   group_tag_id_by_tag_id:,
+                                   current_group_tag_id: nil,
+                                   seen: Set.new([filters[:tag_id]]))
+    end
+
+    { selected_tag_id: filters[:tag_id],
+      selected_tag:,
+      scope_tag_ids: scope_tag_ids.to_a,
+      tags_by_id:,
+      group_tag_id_by_tag_id: }
+  end
+
+  def collect_material_descendant_ids selected_tag_id, children_by_parent_id
+    ids = Set.new([selected_tag_id])
+    stack = [selected_tag_id]
+
+    until stack.empty?
+      tag_id = stack.pop
+      children_by_parent_id[tag_id].each do |child_tag_id|
+        next if ids.include?(child_tag_id)
+
+        ids << child_tag_id
+        stack << child_tag_id
+      end
+    end
+
+    ids
+  end
+
+  def assign_material_group_branch tag_id, children_by_parent_id:, tags_by_id:,
+                                   group_tag_id_by_tag_id:, current_group_tag_id:, seen:
+    return if seen.include?(tag_id)
+
+    tag = tags_by_id[tag_id]
+    return unless tag
+
+    seen = seen.dup << tag_id
+    next_group_tag_id = current_group_tag_id
+    next_group_tag_id = tag_id if next_group_tag_id.nil? && !tag[:deprecated]
+    group_tag_id_by_tag_id[tag_id] = next_group_tag_id if next_group_tag_id
+
+    children_by_parent_id[tag_id].each do |child_tag_id|
+      assign_material_group_branch(child_tag_id,
+                                   children_by_parent_id:,
+                                   tags_by_id:,
+                                   group_tag_id_by_tag_id:,
+                                   current_group_tag_id: next_group_tag_id,
+                                   seen:)
+    end
+  end
+
+  def material_index_groups materials, tag_graph
+    return [] unless tag_graph
+
+    groups_by_key = { }
+
+    materials.each do |material|
+      next unless material.tag_id
+
+      group_tag_id =
+          if material.tag_id == tag_graph[:selected_tag_id]
+            tag_graph[:selected_tag_id]
+          else
+            tag_graph[:group_tag_id_by_tag_id][material.tag_id] || tag_graph[:selected_tag_id]
+          end
+
+      group_tag = tag_graph[:tags_by_id][group_tag_id]
+      next unless group_tag
+
+      key = "tag:#{ group_tag_id }"
+      group =
+          groups_by_key[key] ||= { key:,
+                                   tag: group_tag_repr(group_tag),
+                                   material_ids: [],
+                                   count: 0 }
+      group[:material_ids] << material.id
+      group[:count] += 1
+    end
+
+    groups_by_key.values.sort_by { |group| group[:tag][:name] }
+  end
+
+  def material_index_tag_scope filters, tag_graph
+    return nil unless tag_graph
+
+    {
+      tag: group_tag_repr(tag_graph[:selected_tag]),
+      include_descendants: filters[:include_descendants]
+    }
+  end
+
+  def group_tag_repr group_tag
+    { id: group_tag[:id],
+      name: group_tag[:name],
+      category: group_tag[:category] }
+  end
+
+  def material_index_order_sql filters
+    direction = filters[:direction] == 'asc' ? 'ASC' : 'DESC'
+    sort_sql =
+        case filters[:sort]
+        when 'tag_name'
+          'tag_names.name'
+        when 'media_kind'
+          material_media_kind_sql
+        when 'file_byte_size'
+          'material_file_blobs.byte_size'
+        when 'updated_at'
+          'materials.updated_at'
+        when 'version_no'
+          'materials.version_no'
+        when 'id'
+          'materials.id'
+        else
+          'materials.created_at'
+        end
+
+    "#{ sort_sql } #{ direction }, materials.id #{ direction }"
+  end
+
+  def material_media_kind_sql
+    "CASE " \
+      "WHEN material_file_attachments.id IS NULL AND materials.url IS NOT NULL THEN 5 " \
+      "WHEN material_file_blobs.content_type LIKE 'image/%' THEN 1 " \
+      "WHEN material_file_blobs.content_type LIKE 'video/%' THEN 2 " \
+      "WHEN material_file_blobs.content_type LIKE 'audio/%' THEN 3 " \
+      "WHEN material_file_attachments.id IS NOT NULL THEN 4 " \
+      "ELSE 6 END"
+  end
+
+  def upsert_export_paths! material
+    raw = params[:export_paths]
+    return if raw.blank?
+
+    export_paths = raw.respond_to?(:to_unsafe_h) ? raw.to_unsafe_h : raw.to_h
+    export_paths.each do |profile, export_path|
+      profile = profile.to_s
+      export_path = export_path.to_s.strip
+      item = material.material_export_items.find_or_initialize_by(profile:)
+
+      if export_path.blank?
+        item.destroy! if item.persisted?
+        next
+      end
+
+      item.export_path = export_path
+      item.enabled = true
+      item.created_by_user ||= current_user
+      item.save!
+    end
+  end
+
+  def render_zip_empty
+    render_unprocessable_entity('ZIP export 対象の素材がありません．')
+  end
+
+  def render_zip_duplicate_path error
+    render_unprocessable_entity("ZIP export path が重複してゐます: #{ error.message }")
+  end
+
+  def render_zip_missing_file error
+    missing_files = error.missing_files.map do |missing_file|
+      { material_id: missing_file.material_id,
+        export_path: missing_file.export_path,
+        blob_id: missing_file.blob_id,
+        filename: missing_file.filename }
+    end
+
+    render json: { type: 'validation_error',
+                   message: 'ZIP export に必要な素材ファイルが欠損しています．',
+                   errors: { },
+                   base_errors: ['ZIP export に必要な素材ファイルが欠損しています．'],
+                   missing_files: },
+           status: :unprocessable_entity
+  end
+
+  def build_uploaded_material_blob! file, file_sha256
+    return nil unless file
+
+    file.tempfile.rewind
+
+    blob = ActiveStorage::Blob.create_and_upload!(
+        io: file.tempfile,
+        filename: file.original_filename,
+        content_type: file.content_type,
+    )
+
+    MaterialFileSha256.assign_metadata_sha256!(blob, file_sha256)
+
+    blob
+  ensure
+    file.tempfile.rewind if file&.tempfile
+  end
+
+  def render_material_import_block block
+    render_validation_error fields: { file: ["抑止された素材です: #{ block.reason }"] }
+  end
+
+  def log_thumbnail_generation material, result
+    material.reload
+    Rails.logger.info(
+        "Material thumbnail generation: material_id=#{ material.id } " \
+        "result=#{ result } " \
+        "thumbnail_attached=#{ material.thumbnail.attached? } " \
+        "content_type=#{ material.content_type }")
+  end
 end

backend/app/controllers/nico_tags_controller.rb

@@ -1,26 +1,69 @@
 class NicoTagsController < ApplicationController
   def index
-    limit = (params[:limit] || 20).to_i
-    cursor = params[:cursor].presence
+    name = params[:name].presence
+    linked_tag = params[:linked_tag].presence
+    link_status = params[:link_status].presence
+    order = params[:order].to_s.split(':', 2).map(&:strip)
+    order[0] = 'updated_at' unless order[0].in?(['name', 'created_at', 'updated_at'])
+    unless order[1].in?(['asc', 'desc'])
+      order[1] = order[0] == 'name' ? 'asc' : 'desc'
+    end
+    page = (params[:page].presence || 1).to_i
+    limit = (params[:limit].presence || 20).to_i
+
+    page = 1 if page < 1
+    limit = 1 if limit < 1
+
+    post_tag_max_sql =
+        PostTag
+        .select('tag_id, MAX(created_at) AS max_created_at')
+        .group('tag_id')
+        .to_sql
 
     q = Tag.nico_tags
+           .joins(:tag_name)
+           .joins("LEFT JOIN (#{ post_tag_max_sql }) post_tag_max " \
+                  'ON post_tag_max.tag_id = tags.id')
            .includes(:tag_name, tag_name: :wiki_page, linked_tags: { tag_name: :wiki_page })
-           .order(updated_at: :desc)
-    q = q.where('tags.updated_at < ?', Time.iso8601(cursor)) if cursor
-
-    tags = q.limit(limit + 1).to_a
-
-    next_cursor = nil
-    if tags.size > limit
-      next_cursor = tags.last.updated_at.iso8601(6)
-      tags = tags.first(limit)
+    q = q.where('tag_names.name LIKE ?', "%#{ name }%") if name
+    if linked_tag
+      linked_tag_ids =
+        Tag
+        .joins(:tag_name)
+        .where('tag_names.name LIKE ?', "%#{ linked_tag }%")
+        .pluck(:id)
+      linked_nico_tag_ids = NicoTagRelation.where(tag_id: linked_tag_ids).pluck(:nico_tag_id)
+      q = q.where(id: linked_nico_tag_ids)
+    end
+    if link_status.in?(['linked', 'unlinked'])
+      exists_sql =
+          'EXISTS (SELECT 1 FROM nico_tag_relations ' \
+          'WHERE nico_tag_relations.nico_tag_id = tags.id)'
+      q = link_status == 'linked' ? q.where(exists_sql) : q.where("NOT #{ exists_sql }")
     end
 
+    count = q.count
+    sort_sql =
+        case order[0]
+        when 'name'
+          'tag_names.name'
+        when 'updated_at'
+          'post_tag_max.max_created_at'
+        else
+          "tags.#{ order[0] }"
+        end
+    tags = q.reselect('tags.*',
+                      Arel.sql('post_tag_max.max_created_at AS recent_post_tag_created_at'))
+            .order(Arel.sql("#{ sort_sql } #{ order[1] }, tags.id #{ order[1] }"))
+            .limit(limit)
+            .offset((page - 1) * limit)
+            .to_a
+
     render json: { tags: tags.map { |tag|
-      TagRepr.base(tag).merge(linked_tags: tag.linked_tags.map { |lt|
-        TagRepr.base(lt)
-      })
-    }, next_cursor: }
+      TagRepr.base(tag).merge(
+          recent_post_tag_created_at: tag.recent_post_tag_created_at,
+          linked_tags: tag.linked_tags.map { |lt| TagRepr.base(lt) })
+    }, count: }
   end
 
   def update
@@ -30,14 +73,18 @@ class NicoTagsController < ApplicationController
     id = params[:id].to_i
 
     tag = Tag.find(id)
-    return head :bad_request unless tag.nico?
+    return render_bad_request('ニコニコ・タグを指定してください．') unless tag.nico?
 
     linked_tag_names = params[:tags].to_s.split
-    linked_tags = Tag.normalise_tags(linked_tag_names, with_tagme: false,
-                                                       with_no_deerjikist: false)
-    return head :bad_request if linked_tags.any? { |t| t.nico? }
+    linked_tags = nil
 
     ApplicationRecord.transaction do
+      linked_tags = Tag.normalise_tags!(linked_tag_names, with_tagme: false,
+                                                          with_no_deerjikist: false)
+      if linked_tags.any? { |t| t.nico? }
+        raise Tag::NicoTagNormalisationError
+      end
+
       TagVersioning.record_tag_snapshots!(linked_tags, created_by_user: current_user)
 
       tag.linked_tags = linked_tags
@@ -47,5 +94,21 @@ class NicoTagsController < ApplicationController
     end
 
     render json: tag.linked_tags.map { |t| TagRepr.base(t) }, status: :ok
+  rescue Tag::NicoTagNormalisationError
+    render_validation_error fields: { tags: ['ニコニコ・タグ同士は連携できません．'] }
+  rescue ActiveRecord::RecordInvalid => e
+    render_nico_tag_form_record_invalid e.record
+  end
+
+  private
+
+  def render_nico_tag_form_record_invalid record
+    if record.is_a?(TagName) || record.is_a?(Tag)
+      render_validation_error fields: { tags: record.errors.full_messages.map { |message|
+        "タグ名 “#{ record.name }”: #{ message }"
+      } }
+    else
+      render_validation_error record
+    end
   end
 end

backend/app/controllers/post_imports_controller.rb

@@ -0,0 +1,45 @@
+class PostImportsController < ApplicationController
+  before_action :require_member!
+
+  def preview
+    rows = PostImportPreviewer.new(existing: params[:existing]).preview_rows(
+        rows: PostImportUrlListParser.parse(params[:source]))
+    render json: { rows: }
+  rescue ArgumentError => e
+    render_bad_request(e.message)
+  end
+
+  def validate
+    rows = normalised_import_rows(allow_warning_fields: true)
+    changed_row = Integer(params[:changed_row], exception: false)
+    result =
+        PostImportPreviewer.new(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: normalised_import_rows,
+        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 normalised_import_rows allow_warning_fields: false
+    PostImportRowNormaliser.normalise!(params[:rows], allow_warning_fields:)
+  end
+end

backend/app/controllers/post_versions_controller.rb

@@ -25,6 +25,7 @@ class PostVersionsController < ApplicationController
         SQL
         .select('post_versions.*', 'prev.title AS prev_title', 'prev.url AS prev_url',
                 'prev.thumbnail_base AS prev_thumbnail_base', 'prev.tags AS prev_tags',
+                'prev.video_ms AS prev_video_ms',
                 'prev.original_created_from AS prev_original_created_from',
                 'prev.original_created_before AS prev_original_created_before')
     q = q.where('post_versions.post_id = ?', post_id) if post_id
@@ -74,6 +75,10 @@ class PostVersionsController < ApplicationController
           current: row.thumbnail_base,
           prev: row.attributes['prev_thumbnail_base']
         },
+        video_ms: {
+          current: row.video_ms,
+          prev: row.attributes['prev_video_ms']
+        },
         tags: build_version_tags(cur_tags, prev_tags),
         original_created_from: {
           current: row.original_created_from&.iso8601,

backend/app/controllers/posts_controller.rb

@@ -1,6 +1,10 @@
 class PostsController < ApplicationController
   Event = Struct.new(:post, :tag, :user, :change_type, :timestamp, keyword_init: true)
 
+  class VideoMsParseError < ArgumentError
+    ;
+  end
+
   def index
     url = params[:url].presence
     title = params[:title].presence
@@ -44,7 +48,10 @@ class PostsController < ApplicationController
         filtered_posts
         .joins("LEFT JOIN (#{ pt_max_sql }) pt_max ON pt_max.post_id = posts.id")
         .reselect('posts.*', Arel.sql("#{ updated_at_all_sql } AS updated_at_all"))
-        .preload(tags: [:materials, { tag_name: :wiki_page }])
+        .preload(:uploaded_user, :parents, :children,
+                 active_post_tags: [:sections,
+                                    { tag: [:deerjikists, :materials,
+                                            { tag_name: :wiki_page }] }])
         .with_attached_thumbnail
 
     q = q.where('posts.url LIKE ?', "%#{ url }%") if url
@@ -95,7 +102,11 @@ class PostsController < ApplicationController
   end
 
   def random
-    post = filtered_posts.preload(tags: [:materials, { tag_name: :wiki_page }])
+    post = filtered_posts.preload(:uploaded_user, :parents, :children,
+                                  active_post_tags: [:sections,
+                                                     { tag: [:deerjikists, :materials,
+                                                             { tag_name: :wiki_page }] }])
+                         .with_attached_thumbnail
                          .order('RAND()')
                          .first
     return head :not_found unless post
@@ -104,47 +115,57 @@ class PostsController < ApplicationController
   end
 
   def show
-    post = Post.includes(tags: [:materials, { tag_name: :wiki_page }]).find_by(id: params[:id])
+    post =
+        Post
+        .includes(:uploaded_user, :parents, :children,
+                  active_post_tags: [:sections,
+                                     { tag: [:deerjikists, :materials,
+                                             { tag_name: :wiki_page }] }])
+        .with_attached_thumbnail
+        .find_by(id: params[:id])
     return head :not_found unless post
 
-    render json: PostRepr.base(post, current_user)
-                         .merge(tags: build_tag_tree_for(post.tags),
-                                related: post.related(limit: 20))
+    parent_posts = post.parents.with_attached_thumbnail.order(:id).to_a
+    child_posts = post.children.with_attached_thumbnail.order(:id).to_a
+    sibling_posts = sibling_posts_by_parent(parent_posts.map(&:id))
+    related = post.related(limit: 20).to_a
+
+    render json: PostRepr.detail(post, current_user,
+                                 parent_posts:,
+                                 child_posts:,
+                                 sibling_posts:,
+                                 related:)
+                         .merge(tags: build_tag_tree_for(post))
   end
 
   def create
     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]
-
-    post = Post.new(title:, url:, thumbnail_base: nil, uploaded_user: current_user,
-                    original_created_from:, original_created_before:)
-    post.thumbnail.attach(thumbnail)
-
-    ApplicationRecord.transaction do
-      post.save!
-      tags = Tag.normalise_tags(tag_names)
-      TagVersioning.record_tag_snapshots!(tags, created_by_user: current_user)
-
-      tags = Tag.expand_parent_tags(tags)
-      sync_post_tags!(post, tags)
-      post.resized_thumbnail!
-      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
-  rescue ActiveRecord::RecordInvalid
-    render json: { errors: post.errors.full_messages }, status: :unprocessable_entity
   rescue Tag::NicoTagNormalisationError
-    head :bad_request
+    render_validation_error fields: { tags: 'ニコニコ・タグは直接指定できません．' }
+  rescue Tag::DeprecatedTagNormalisationError
+    render_unprocessable_entity '廃止済みタグは付与できません．', field: :tags
+  rescue Tag::SectionLiteralParseError
+    render_validation_error fields: { tags: ['タグ区間の記法が不正です．'] }
+  rescue PostCreator::VideoMsParseError
+    render_validation_error fields: { video_ms: ['動画時間の記法が不正です．'] }
+  rescue MiniMagick::Error
+    render_validation_error fields: { thumbnail: ['サムネイル画像の変換に失敗しました．'] }
+  rescue ArgumentError => e
+    render_validation_error fields: { parent_post_ids: [e.message] }
+  rescue ActiveRecord::RecordInvalid => e
+    render_post_form_record_invalid e.record
   end
 
   def viewed
@@ -165,35 +186,84 @@ class PostsController < ApplicationController
     return head :unauthorized unless current_user
     return head :forbidden unless current_user.gte_member?
 
+    force = bool?(:force)
+    merge = bool?(:merge)
+    return render_bad_request('force と merge は同時に指定できません．') if force && merge
+
+    base_version_no = parse_base_version_no
+    return render_bad_request('base_version_no は必須です．') if !(force) && !(base_version_no)
+
     title = params[:title].presence
     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
 
-    post = Post.find(params[:id].to_i)
+    post = nil
+    conflict_json = nil
 
     ApplicationRecord.transaction do
-      PostVersionRecorder.ensure_snapshot!(post, created_by_user: current_user)
+      post = Post.lock.find(params[:id].to_i)
 
-      post.update!(title:, original_created_from:, original_created_before:)
+      base_version = nil
+      base_snapshot = nil
+      current_snapshot = nil
+      unless force
+        base_version = post.post_versions.find_by!(version_no: base_version_no)
 
-      normalised_tags = Tag.normalise_tags(tag_names, with_tagme: false)
-      TagVersioning.record_tag_snapshots!(normalised_tags, created_by_user: current_user)
+        base_snapshot = post_snapshot_from_version(base_version)
+        current_snapshot = post_snapshot_from_record(post)
+      end
+      incoming_snapshot = post_incoming_snapshot(title:,
+                                                 original_created_from:,
+                                                 original_created_before:,
+                                                 tag_names:,
+                                                 video_ms_param: params[:video_ms],
+                                                 duration_param: params[:duration],
+                                                 parent_post_ids:)
 
-      tags = post.tags.nico.to_a + normalised_tags
-      tags = Tag.expand_parent_tags(tags)
-      sync_post_tags!(post, tags)
-      PostVersionRecorder.record!(post:, event_type: :update, created_by_user: current_user)
+      snapshot_to_apply =
+          if force || post.version_no == base_version_no || current_snapshot == base_snapshot
+            incoming_snapshot
+          else
+            changes = post_snapshot_changes(base_snapshot, current_snapshot, incoming_snapshot)
+            conflicts = changes.select { |change| change[:conflict] }
+
+            if merge && conflicts.empty?
+              merge_post_snapshots(base_snapshot, current_snapshot, incoming_snapshot)
+            else
+              conflict_json = post_conflict_json(post:,
+                                                 base_version_no:,
+                                                 base_snapshot:,
+                                                 current_snapshot:,
+                                                 incoming_snapshot:,
+                                                 changes:,
+                                                 conflicts:)
+              raise ActiveRecord::Rollback
+            end
+          end
+
+      apply_post_snapshot!(post, snapshot_to_apply)
     end
 
+    return render json: conflict_json, status: :conflict if conflict_json
+
     post.reload
-    json = post.as_json
-    json['tags'] = build_tag_tree_for(post.tags)
+    json = PostRepr.base(post, current_user)
+    json['tags'] = build_tag_tree_for(post)
     render json:, status: :ok
-  rescue ActiveRecord::RecordInvalid
-    render json: post.errors, status: :unprocessable_entity
   rescue Tag::NicoTagNormalisationError
-    head :bad_request
+    render_validation_error fields: { tags: ['ニコニコ・タグは直接指定できません．'] }
+  rescue Tag::DeprecatedTagNormalisationError
+    render_unprocessable_entity '廃止済みタグは付与できません．', field: :tags
+  rescue Tag::SectionLiteralParseError
+    render_validation_error fields: { tags: ['タグ区間の記法が不正です．'] }
+  rescue VideoMsParseError
+    render_validation_error fields: { video_ms: ['動画時間の記法が不正です．'] }
+  rescue ArgumentError => e
+    render_validation_error fields: { parent_post_ids: [e.message] }
+  rescue ActiveRecord::RecordInvalid => e
+    render_post_form_record_invalid e.record
   end
 
   def changes
@@ -211,7 +281,7 @@ class PostsController < ApplicationController
     pts = pts.where(post_id: id) if id.present?
     pts = pts.where(tag_id:) if tag_id.present?
     pts = pts.includes(:post, :created_user, :deleted_user,
-                       tag: [:materials, { tag_name: :wiki_page }])
+                       tag: [:deerjikists, :materials, { tag_name: :wiki_page }])
 
     events = []
     pts.each do |pt|
@@ -288,7 +358,7 @@ class PostsController < ApplicationController
   def tagged_post_ids_for(name) =
       Post.joins(tags: :tag_name).where(tag_names: { name: }).select(:id)
 
-  def sync_post_tags! post, desired_tags
+  def sync_post_tags! post, desired_tags, sections
     desired_tags.each do |t|
       t.save! if t.new_record?
     end
@@ -307,13 +377,21 @@ class PostsController < ApplicationController
       end
     end
 
+    PostTagSection.where(post_id: post.id).destroy_all
+    sections.each do |tag_id, ranges|
+      ranges.each do |begin_ms, end_ms|
+        PostTagSection.create!(post_id: post.id, tag_id:, begin_ms:, end_ms:)
+      end
+    end
+
     PostTag.where(post_id: post.id, tag_id: to_remove.to_a).kept.find_each do |pt|
       pt.discard_by!(current_user)
     end
   end
 
-  def build_tag_tree_for tags
-    tags = tags.to_a
+  def build_tag_tree_for post
+    post_tags = post.active_post_tags.reject { |post_tag| post_tag.tag.deprecated? }
+    tags = post_tags.map(&:tag)
     tag_ids = tags.map(&:id)
 
     implications = TagImplication.where(parent_tag_id: tag_ids, tag_id: tag_ids)
@@ -328,6 +406,9 @@ class PostsController < ApplicationController
     root_ids = tag_ids - child_ids
 
     tags_by_id = tags.index_by(&:id)
+    sections_by_tag_id = post_tags.to_h { |post_tag|
+      [post_tag.tag_id, post_tag.sections.as_json(only: [:begin_ms, :end_ms])]
+    }
 
     memo = { }
 
@@ -335,8 +416,10 @@ class PostsController < ApplicationController
       tag = tags_by_id[tag_id]
       return nil unless tag
 
+      sections = sections_by_tag_id.fetch(tag_id, [])
+
       if path.include?(tag_id)
-        return TagRepr.base(tag).merge(children: [])
+        return TagRepr.inline(tag).merge(children: [], sections:)
       end
 
       if memo.key?(tag_id)
@@ -348,9 +431,347 @@ class PostsController < ApplicationController
 
       children = child_ids.filter_map { |cid| build_node.(cid, new_path) }
 
-      memo[tag_id] = TagRepr.base(tag).merge(children:)
+      memo[tag_id] = TagRepr.inline(tag).merge(children:, sections:)
     end
 
     root_ids.filter_map { |id| build_node.call(id, []) }
   end
+
+  def sibling_posts_by_parent parent_post_ids
+    return { } if parent_post_ids.blank?
+
+    implications =
+        PostImplication
+        .where(parent_post_id: parent_post_ids)
+        .includes(post: { thumbnail_attachment: :blob })
+        .order(:parent_post_id, :post_id)
+
+    implications.group_by(&:parent_post_id).transform_values { |items|
+      items.map(&:post)
+    }
+  end
+
+  def parse_parent_post_ids
+    raise ArgumentError, 'parent_post_ids は必須です．' unless params.key?(:parent_post_ids)
+
+    params[:parent_post_ids].to_s.split.map { |token|
+      id = Integer(token, exception: false)
+      raise ArgumentError, "親投稿 Id. が不正です: #{ token }" if id.nil? || id <= 0
+
+      id
+    }.uniq
+  end
+
+  def sync_parent_posts! post, parent_post_ids
+    if parent_post_ids.include?(post.id)
+      post.errors.add :parent_post_ids, '自分自身を親投稿にはできません．'
+      raise ActiveRecord::RecordInvalid, post
+    end
+
+    existing_ids = Post.where(id: parent_post_ids).pluck(:id)
+    missing_ids = parent_post_ids - existing_ids
+
+    if missing_ids.present?
+      post.errors.add :parent_post_ids,
+                      "存在しない親投稿 Id. があります: #{ missing_ids.join(' ') }"
+      raise ActiveRecord::RecordInvalid, post
+    end
+
+    current_ids = post.parent_posts.pluck(:id)
+
+    ids_to_add = parent_post_ids - current_ids
+    ids_to_remove = current_ids - parent_post_ids
+
+    PostImplication.where(post_id: post.id, parent_post_id: ids_to_remove).delete_all
+
+    ids_to_add.each do |parent_post_id|
+      PostImplication.create_or_find_by!(post_id: post.id, parent_post_id:)
+    end
+  end
+
+  def parse_base_version_no
+    version_no = Integer(params[:base_version_no], exception: false)
+    if version_no&.positive?
+      version_no
+    else
+      nil
+    end
+  end
+
+  def post_snapshot_from_version version
+    { title: version.title,
+      video_ms: version.respond_to?(:video_ms) ? version.video_ms : nil,
+      original_created_from: snapshot_time(version.original_created_from),
+      original_created_before: snapshot_time(version.original_created_before),
+      tag_names: editable_tag_names_from_version(version),
+      parent_post_ids: snapshot_parent_post_ids_from_version(version) }
+  end
+
+  def editable_tag_names_from_version version
+    version.tags.to_s.split.reject { |name| name.downcase.start_with?('nico:') }.sort
+  end
+
+  def post_snapshot_from_record post
+    { title: post.title,
+      video_ms: post.video_ms,
+      original_created_from: snapshot_time(post.original_created_from),
+      original_created_before: snapshot_time(post.original_created_before),
+      tag_names: editable_tag_names_from_post(post),
+      parent_post_ids: post.parent_posts.order(:id).pluck(:id) }
+  end
+
+  def editable_tag_names_from_post post
+    post
+        .post_tags
+        .kept
+        .joins(tag: :tag_name)
+        .merge(Tag.not_nico)
+        .merge(Tag.where(deprecated_at: nil))
+        .includes(:sections, tag: :tag_name)
+        .order('tag_names.name')
+        .map do |post_tag|
+      name = post_tag.tag.tag_name.name
+      sections = post_tag.sections.sort_by(&:begin_ms)
+
+      next name if sections.empty?
+
+      "#{ name }#{ sections.map { Post.section_literal(_1) }.join }"
+    end
+  end
+
+  def post_incoming_snapshot title:, original_created_from:, original_created_before:,
+                             tag_names:, video_ms_param:, duration_param:, parent_post_ids:
+    Tag.normalise_tags!(tag_names, with_tagme: false, deny_deprecated: true,
+                                        with_sections: true) =>
+        { tags:, sections: }
+
+    tags = Tag.expand_parent_tags(tags).reject(&:deprecated?)
+    video_ms = normalise_video_ms(tags, video_ms_param:, duration_param:)
+    validate_video_sections!(video_ms, sections)
+
+    { title:,
+      video_ms:,
+      original_created_from: snapshot_time(original_created_from),
+      original_created_before: snapshot_time(original_created_before),
+      tag_names: tags.uniq(&:id).map { |tag|
+        "#{ tag.name }#{ sections[tag.id].to_a.map { section_literal(_1) }.join }"
+      }.sort,
+      parent_post_ids: parent_post_ids.sort }
+  end
+
+  def snapshot_parent_post_ids_from_version version
+    if version.respond_to?(:parent_post_ids)
+      version.parent_post_ids.to_s.split.map { |id| id.to_i }.sort
+    elsif version.respond_to?(:parent_id) && version.parent_id
+      [version.parent_id]
+    else
+      []
+    end
+  end
+
+  def snapshot_time value
+    return nil if value.blank?
+
+    value = Time.zone.parse(value.to_s) if value in String
+    value&.in_time_zone&.iso8601(6)
+  rescue ArgumentError, TypeError
+    value.to_s
+  end
+
+  def section_literal section
+    "[#{ Post.ms_to_time(section[0]) }-#{ section[1] ? Post.ms_to_time(section[1]) : '' }]"
+  end
+
+  def post_conflict_json post:, base_version_no:, base_snapshot:,
+                         current_snapshot:, incoming_snapshot:, changes:, conflicts:
+    { error: 'conflict',
+      message: '競合が発生しました．',
+      post_id: post.id,
+      base_version_no:,
+      current_version_no: post.version_no,
+      base: base_snapshot,
+      current: current_snapshot,
+      mine: incoming_snapshot,
+      changes:,
+      conflicts:,
+      mergeable: conflicts.empty? }
+  end
+
+  def post_snapshot_changes base_snapshot, current_snapshot, incoming_snapshot
+    [scalar_snapshot_change(:title, 'タイトル',
+                            base_snapshot, current_snapshot, incoming_snapshot),
+     scalar_snapshot_change(:video_ms, '動画時間',
+                            base_snapshot, current_snapshot, incoming_snapshot),
+     scalar_snapshot_change(:original_created_from, 'オリジナルの作成日時（以降）',
+                            base_snapshot, current_snapshot, incoming_snapshot),
+     scalar_snapshot_change(:original_created_before, 'オリジナルの作成日時（より前）',
+                            base_snapshot, current_snapshot, incoming_snapshot),
+     set_snapshot_change(:tag_names, 'タグ',
+                         base_snapshot, current_snapshot, incoming_snapshot),
+     set_snapshot_change(:parent_post_ids, '親投稿',
+                         base_snapshot, current_snapshot, incoming_snapshot)].compact
+  end
+
+  def scalar_snapshot_change field, label, base_snapshot, current_snapshot, incoming_snapshot
+    base = base_snapshot[field]
+    current = current_snapshot[field]
+    mine = incoming_snapshot[field]
+
+    return nil if current == base && mine == base
+
+    { field:, label:, base:, current:, mine:,
+      changed_by_current: current != base,
+      changed_by_me: mine != base,
+      conflict: scalar_snapshot_conflict?(base, current, mine) }
+  end
+
+  def scalar_snapshot_conflict? base, current, mine
+    current != base && mine != base && current != mine
+  end
+
+  def set_snapshot_change field, label, base_snapshot, current_snapshot, incoming_snapshot
+    base = base_snapshot[field].to_a
+    current = current_snapshot[field].to_a
+    mine = incoming_snapshot[field].to_a
+
+    added_by_current = current - base
+    removed_by_current = base - current
+    added_by_me = mine - base
+    removed_by_me = base - mine
+
+    if (added_by_current.empty? &&
+        removed_by_current.empty? &&
+        added_by_me.empty? &&
+        removed_by_me.empty?)
+      return nil
+    end
+
+    { field:, label:, base:, current:, mine:, added_by_current:, removed_by_current:,
+      added_by_me:, removed_by_me:,
+      changed_by_current: added_by_current.present? || removed_by_current.present?,
+      changed_by_me: added_by_me.present? || removed_by_me.present?,
+      conflict: set_snapshot_conflict?(added_by_current:,
+                                       removed_by_current:,
+                                       added_by_me:,
+                                       removed_by_me:) }
+  end
+
+  def set_snapshot_conflict? added_by_current:, removed_by_current:,
+                             added_by_me:, removed_by_me:
+    (added_by_current & removed_by_me).present? || (removed_by_current & added_by_me).present?
+  end
+
+  def apply_post_snapshot! post, snapshot
+    PostVersionRecorder.ensure_snapshot!(post, created_by_user: current_user)
+
+    post.update!(title: snapshot[:title],
+                 video_ms: snapshot[:video_ms],
+                 original_created_from: snapshot[:original_created_from],
+                 original_created_before: snapshot[:original_created_before])
+
+    Tag.normalise_tags!(snapshot[:tag_names], with_tagme: false,
+                                               deny_deprecated: true,
+                                               with_sections: true) =>
+        { tags: editable_tags, sections: }
+    TagVersioning.record_tag_snapshots!(editable_tags, created_by_user: current_user)
+
+    readonly_tags = post.tags.nico.to_a
+
+    tags = readonly_tags + editable_tags
+    tags = Tag.expand_parent_tags(tags).reject(&:deprecated?)
+
+    post.video_ms = tags.any? { _1.id == Tag.video.id } ? snapshot[:video_ms] : nil
+    validate_video_sections!(post.video_ms, sections)
+    post.save!
+    sync_post_tags!(post, tags, sections)
+    sync_parent_posts!(post, snapshot[:parent_post_ids])
+
+    PostVersionRecorder.record!(post:, event_type: :update, created_by_user: current_user)
+  end
+
+  def merge_post_snapshots base_snapshot, current_snapshot, incoming_snapshot
+    [:title, :video_ms, :original_created_from, :original_created_before].map {
+      [_1, merge_scalar_snapshot_value(base_snapshot[_1],
+                                       current_snapshot[_1],
+                                       incoming_snapshot[_1])]
+    }.to_h.merge([:tag_names, :parent_post_ids].map {
+      [_1, merge_set_snapshot_value(base_snapshot[_1],
+                                    current_snapshot[_1],
+                                    incoming_snapshot[_1])]
+    }.to_h)
+  end
+
+  def merge_scalar_snapshot_value base, current, mine
+    return mine if current == base
+    return current if mine == base || current == mine
+
+    raise ArgumentError, '競合してゐる項目はマージできません．'
+  end
+
+  def merge_set_snapshot_value base, current, mine
+    base = base.to_a
+    current = current.to_a
+    mine = mine.to_a
+
+    added_by_current = current - base
+    removed_by_current = base - current
+    added_by_me = mine - base
+    removed_by_me = base - mine
+
+    merged = base + added_by_current + added_by_me
+    merged -= removed_by_current
+    merged -= removed_by_me
+
+    merged.uniq.sort
+  end
+
+  def render_post_form_record_invalid record
+    if record.is_a?(TagName) || record.is_a?(Tag)
+      render_validation_error fields: { tags: record.errors.full_messages.map { |message|
+        "タグ名 “#{ record.name }”: #{ message }"
+      } }
+    else
+      render_validation_error record
+    end
+  end
+
+  def normalise_video_ms tags, video_ms_param: params[:video_ms], duration_param: params[:duration]
+    return nil unless tags.any? { _1.id == Tag.video.id }
+
+    if video_ms_param.present?
+      video_ms = Integer(video_ms_param, exception: false)
+      raise VideoMsParseError unless video_ms&.positive?
+
+      return video_ms
+    end
+
+    return nil if duration_param.blank?
+
+    video_ms = Tag.time_to_ms!(duration_param.to_s, tag_name: '動画時間')
+    raise VideoMsParseError unless video_ms.positive?
+
+    video_ms
+  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|
+        if begin_ms >= video_ms
+          post = Post.new
+          post.errors.add :video_ms, 'タグ区間の開始が動画時間以上です．'
+          raise ActiveRecord::RecordInvalid, post
+        end
+
+        if end_ms && end_ms > video_ms
+          post = Post.new
+          post.errors.add :video_ms, 'タグ区間の終端が動画時間を超えてゐます．'
+          raise ActiveRecord::RecordInvalid, post
+        end
+      end
+    end
+  end
 end

backend/app/controllers/preview_controller.rb

@@ -1,46 +1,57 @@
 class PreviewController < ApplicationController
+  before_action :require_member!
+
   def title
-    # TODO: # 既知サイトなら決まったフォーマットで title 取得するやぅに．
-    return head :unauthorized unless current_user
+    return render_bad_request('URL は必須です．') if params[:url].blank?
 
-    url = params[:url]
-    return head :bad_request unless url.present?
-
-    unless url.start_with?(/http(s)?:\/\//)
-      url = 'http://' + url
-    end
-
-    html = URI.open(url, open_timeout: 5, read_timeout: 5).read
-    doc = Nokogiri::HTML.parse(html)
-    title = doc.at('title')&.text&.strip
-
-    render json: { title: title }
-  rescue => e
-    render json: { error: e.message }, status: :bad_request
+    render json: { title: Preview::ThumbnailFetcher.title(params[:url]) }
+  rescue Preview::UrlSafety::UnsafeUrl => e
+    render_bad_request(e.message)
+  rescue Preview::HttpFetcher::FetchTimeout => e
+    render_preview_error(e.message, :gateway_timeout)
+  rescue Preview::HttpFetcher::ResponseTooLarge => e
+    render_preview_error(e.message, :payload_too_large)
+  rescue Preview::HttpFetcher::FetchFailed => e
+    render_preview_error(e.message, :bad_gateway)
   end
 
   def thumbnail
-    # TODO: 既知ドメインであれば指定のアドレスからサムネールを取得するやぅにする．
+    return render_bad_request('URL は必須です．') if params[:url].blank?
 
+    image = MiniMagick::Image.read(Preview::ThumbnailFetcher.fetch(params[:url]))
+    image.auto_orient
+    image.resize '180x180>'
+    image.format 'png'
+    width, height = image.dimensions
+    raise Preview::ThumbnailFetcher::GenerationFailed, 'サムネール画像の変換に失敗しました．' if width > 180 || height > 180
+
+    send_data image.to_blob, type: 'image/png', disposition: 'inline'
+  rescue Preview::UrlSafety::UnsafeUrl => e
+    render_bad_request(e.message)
+  rescue Preview::HttpFetcher::FetchTimeout => e
+    render_preview_error(e.message, :gateway_timeout)
+  rescue Preview::HttpFetcher::ResponseTooLarge => e
+    render_preview_error(e.message, :payload_too_large)
+  rescue Preview::HttpFetcher::FetchFailed => e
+    render_preview_error(e.message, :bad_gateway)
+  rescue Preview::ThumbnailFetcher::GenerationFailed, MiniMagick::Error => e
+    render_unprocessable_entity(e.message)
+  end
+
+  private
+
+  def require_member!
     return head :unauthorized unless current_user
+    return if current_user.gte_member?
 
-    url = params[:url]
-    return head :bad_request if url.blank?
+    head :forbidden
+  end
 
-    unless url.start_with?(/http(s)?:\/\//)
-      url = 'http://' + url
-    end
-
-    path = Rails.root.join('tmp', "thumb_#{ SecureRandom.hex }.png")
-    system("node #{ Rails.root }/lib/screenshot.js #{ Shellwords.escape(url) } #{ path }")
-
-    if File.exist?(path)
-      image = MiniMagick::Image.open(path)
-      image.resize '180x180'
-      File.delete(path) rescue nil
-      send_file image.path, type: 'image/png', disposition: 'inline'
-    else
-      render json: { error: 'Failed to generate thumbnail' }, status: :internal_server_error
-    end
+  def render_preview_error(message, status)
+    render json: { type: status.to_s,
+                   message:,
+                   errors: { },
+                   base_errors: [message] },
+           status:
   end
 end

backend/app/controllers/tag_children_controller.rb

@@ -5,11 +5,12 @@ class TagChildrenController < ApplicationController
 
     parent_id = params[:parent_id]
     child_id = params[:child_id]
-    return head :bad_request if parent_id.blank? || child_id.blank?
+    return render_bad_request('parent_id は必須です．') if parent_id.blank?
+    return render_bad_request('child_id は必須です．') if child_id.blank?
 
     parent = Tag.find(parent_id)
     child = Tag.find(child_id)
-    return head :bad_request if parent.nico? || child.nico?
+    return render_bad_request('ニコニコ・タグの階層は変更できません．') if parent.nico? || child.nico?
 
     ApplicationRecord.transaction do
       TagVersioning.ensure_snapshot!(child, created_by_user: current_user)
@@ -27,11 +28,12 @@ class TagChildrenController < ApplicationController
 
     parent_id = params[:parent_id]
     child_id = params[:child_id]
-    return head :bad_request if parent_id.blank? || child_id.blank?
+    return render_bad_request('parent_id は必須です．') if parent_id.blank?
+    return render_bad_request('child_id は必須です．') if child_id.blank?
 
     parent = Tag.find(parent_id)
     child = Tag.find(child_id)
-    return head :bad_request if parent.nico? || child.nico?
+    return render_bad_request('ニコニコ・タグの階層は変更できません．') if parent.nico? || child.nico?
 
     ApplicationRecord.transaction do
       TagVersioning.ensure_snapshot!(child, created_by_user: current_user)

backend/app/controllers/tag_versions_controller.rb

@@ -17,6 +17,7 @@ class TagVersionsController < ApplicationController
               AND prev.version_no = tag_versions.version_no - 1
         SQL
       .select('tag_versions.*', 'prev.name AS prev_name', 'prev.category AS prev_category',
+              'prev.deprecated_at AS prev_deprecated_at',
               'prev.aliases AS prev_aliases', 'prev.parent_tag_ids AS prev_parent_tag_ids')
     q = q.where('tag_versions.tag_id = ?', tag_id) if tag_id
 
@@ -62,6 +63,8 @@ class TagVersionsController < ApplicationController
         event_type: row.event_type,
         name: { current: row.name, prev: row.attributes['prev_name'] },
         category: { current: row.category, prev: row.attributes['prev_category'] },
+        deprecated_at: { current: row.deprecated_at&.iso8601,
+                         prev: row.attributes['prev_deprecated_at']&.iso8601 },
         aliases: build_version_values(cur_aliases, prev_aliases, key: :name),
         parent_tags:,
         created_at: row.created_at.iso8601,

backend/app/controllers/tags_controller.rb

@@ -1,3 +1,8 @@
+require 'net/http'
+require 'uri'
+require 'set'
+
+
 class TagsController < ApplicationController
   def index
     post_id = params[:post]
@@ -10,6 +15,8 @@ class TagsController < ApplicationController
     post_count_between[1] = nil if post_count_between[1] < 0
     created_between = params[:created_from].presence, params[:created_to].presence
     updated_between = params[:updated_from].presence, params[:updated_to].presence
+    deprecated_given = params.key?(:deprecated)
+    deprecated = bool?(:deprecated)
 
     order = params[:order].to_s.split(':', 2).map(&:strip)
     unless order[0].in?(['name', 'category', 'post_count', 'created_at', 'updated_at'])
@@ -44,6 +51,9 @@ class TagsController < ApplicationController
     q = q.where('tags.created_at <= ?', created_between[1]) if created_between[1]
     q = q.where('tags.updated_at >= ?', updated_between[0]) if updated_between[0]
     q = q.where('tags.updated_at <= ?', updated_between[1]) if updated_between[1]
+    if deprecated_given
+      q = deprecated ? q.where.not(deprecated_at: nil) : q.where(deprecated_at: nil)
+    end
 
     sort_sql =
         case order[0]
@@ -72,39 +82,20 @@ class TagsController < ApplicationController
   def with_depth
     parent_tag_id = params[:parent].to_i
     parent_tag_id = nil if parent_tag_id <= 0
+    material_filter = material_filter_param(default: 'any')
+
+    graph = build_with_depth_graph(material_filter)
 
     tag_ids =
         if parent_tag_id
-          TagImplication.where(parent_tag_id:).select(:tag_id)
+          visible_child_tag_ids(parent_tag_id, graph)
         else
-          Tag.where.not(id: TagImplication.select(:tag_id)).select(:id)
+          visible_root_tag_ids(graph)
         end
 
-    tags =
-        Tag
-        .joins(:tag_name)
-        .includes(:tag_name, :materials, tag_name: :wiki_page)
-        .where(category: [:meme, :character, :material])
-        .where(id: tag_ids)
-        .order('tag_names.name')
-        .distinct
-        .to_a
-
-    has_children_tag_ids =
-        if tags.empty?
-          []
-        else
-          TagImplication
-              .joins(:tag)
-              .where(parent_tag_id: tags.map(&:id),
-                     tags: { category: [:meme, :character, :material] })
-              .distinct
-              .pluck(:parent_tag_id)
-        end
-
-    render json: tags.map { |tag|
-      TagRepr.base(tag).merge(has_children: has_children_tag_ids.include?(tag.id), children: [])
-    }
+    render json: tag_ids
+                 .sort_by { |tag_id| graph[:tags_by_id][tag_id][:name] }
+                 .map { |tag_id| with_depth_lightweight_repr(tag_id, graph) }
   end
 
   def autocomplete
@@ -129,6 +120,7 @@ class TagsController < ApplicationController
 
     base = Tag.joins(:tag_name)
               .includes(:tag_name, :materials, tag_name: :wiki_page)
+              .where(deprecated_at: nil)
     base = base.where('tags.post_count > 0') if present_only
 
     canonical_hit =
@@ -164,7 +156,7 @@ class TagsController < ApplicationController
 
   def show_by_name
     name = params[:name].to_s.strip
-    return head :bad_request if name.blank?
+    return render_bad_request('name は必須です．') if name.blank?
 
     tag = Tag.joins(:tag_name)
              .includes(:tag_name, :materials, tag_name: :wiki_page)
@@ -182,31 +174,62 @@ class TagsController < ApplicationController
              .find_by(id: params[:id])
     return head :not_found unless tag
 
-    render json: DeerjikistRepr.many(tag.deerjikists)
+    render json: { tag: TagRepr.base(tag),
+                   deerjikists: DeerjikistRepr.many(tag.deerjikists) }
   end
 
   def deerjikists_by_name
     name = params[:name].to_s.strip
-    return head :bad_request if name.blank?
+    return render_bad_request('name は必須です．') if name.blank?
 
     tag = Tag.joins(:tag_name)
              .includes(:tag_name, tag_name: :wiki_page)
              .find_by(tag_names: { name: })
     return head :not_found unless tag
 
-    render json: DeerjikistRepr.many(tag.deerjikists)
+    render json: { tag: TagRepr.base(tag),
+                   deerjikists: DeerjikistRepr.many(tag.deerjikists) }
+  end
+
+  def update_deerjikists
+    return head :unauthorized unless current_user
+    return head :forbidden unless current_user.gte_member?
+
+    tag = Tag.joins(:tag_name)
+             .includes(:tag_name, tag_name: :wiki_page)
+             .find_by(id: params[:id])
+    return head :not_found unless tag
+
+    ApplicationRecord.transaction do
+      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
+
+    return if performed?
+
+    render json: DeerjikistRepr.many(tag.reload.deerjikists)
   end
 
   def materials_by_name
     name = params[:name].to_s.strip
-    return head :bad_request if name.blank?
+    return render_bad_request('name は必須です．') if name.blank?
+    material_filter = material_filter_param(default: 'any')
 
     tag = Tag.joins(:tag_name)
              .includes(:tag_name, :materials, tag_name: :wiki_page)
              .find_by(tag_names: { name: })
     return head :not_found unless tag
 
-    render json: build_tag_children(tag)
+    graph = build_with_depth_graph(material_filter)
+
+    render json: build_tag_children(tag, graph:)
   end
 
   def update_all
@@ -218,38 +241,52 @@ class TagsController < ApplicationController
 
     name = params[:name].to_s.strip
     category = params[:category].to_s.strip
-    return head :unprocessable_entity if name.blank? || category.blank?
+    return render_unprocessable_entity('名前は必須です．', field: :name) if name.blank?
+    return render_unprocessable_entity('カテゴリは必須です．', field: :category) if category.blank?
+    return render_unprocessable_entity '廃止状態は必須です．', field: :deprecated unless params.key?(:deprecated)
 
-    if name != tag.name &&
-        tag.in?([Tag.tagme, Tag.bot, Tag.no_deerjikist, Tag.video, Tag.niconico])
-      return render json: { error: 'システム・タグの名称は変更できません．' },
-                    status: :unprocessable_entity
-    end
-
-    if tag.nico? || category == 'nico'
-      return render json: { error: 'ニコタグは変更できません．' },
-                    status: :unprocessable_entity
-    end
+    return unless validate_tag_rename(tag, name)
 
     alias_names = params[:aliases].to_s.split.uniq
     parent_names = params[:parent_tags].to_s.split.uniq
+    deprecated = bool?(:deprecated)
+
+    if tag.nico? && deprecated
+      return render_unprocessable_entity 'ニコタグは廃止できません．', field: :deprecated
+    end
+
+    if tag.nico? || category == 'nico'
+      return render_unprocessable_entity 'ニコタグは変更できません．', field: :category
+    end
 
     ApplicationRecord.transaction do
       TagVersioning.ensure_snapshot!(tag, created_by_user: current_user)
 
       old_name = tag.name
+      name_changed = name != old_name
+      wiki_page = tag.tag_name.wiki_page if name_changed
 
-      tag.update!(category:)
-      tag.tag_name.update!(name:)
+      if tag.deprecated? == deprecated
+        tag.update!(category:)
+      else
+        tag.update!(category:, deprecated_at: deprecated ? Time.current : nil)
+      end
+      rename_tag_name!(tag, name) if name_changed
 
-      alias_names << old_name if name != old_name
+      alias_names << old_name if name_changed
       alias_names.delete(name)
 
       update_aliases!(tag, alias_names)
       update_parent_tags!(tag, parent_names)
 
       tag.reload
-      record_tag_version!(tag, event_type: :update, created_by_user: current_user)
+
+      record_tag_version!(
+          tag,
+          event_type: :update,
+          created_by_user: current_user,
+          name_changed:,
+          wiki_page:)
     end
 
     render json: TagRepr.base(tag.reload)
@@ -261,21 +298,42 @@ class TagsController < ApplicationController
 
     name = params[:name].presence
     category = params[:category].presence
+    deprecated_given = params.key?(:deprecated)
+    deprecated = bool?(:deprecated)
 
     tag = Tag.find(params[:id])
 
+    if tag.nico? && deprecated_given && deprecated
+      return render_unprocessable_entity 'ニコタグは廃止できません．', field: :deprecated
+    end
+
+    return unless validate_tag_rename(tag, name)
+
     if tag.nico? || (category.present? && category == 'nico')
-      return render json: { error: 'ニコタグは変更できません．' },
-                    status: :unprocessable_entity
+      return render_unprocessable_entity 'ニコタグは変更できません．', field: :category
     end
 
     ApplicationRecord.transaction do
       TagVersioning.ensure_snapshot!(tag, created_by_user: current_user)
 
-      tag.tag_name.update!(name:) if name.present?
-      tag.update!(category:) if category.present?
+      old_name = tag.name
+      name_changed = name.present? && name != old_name
+      wiki_page = tag.tag_name.wiki_page if name_changed
 
-      record_tag_version!(tag, event_type: :update, created_by_user: current_user)
+      rename_tag_name!(tag, name) if name_changed
+      tag.update!(category:) if category.present?
+      if deprecated_given && tag.deprecated? != deprecated
+        tag.update!(deprecated_at: deprecated ? Time.current : nil)
+      end
+
+      tag.reload
+
+      record_tag_version!(
+          tag,
+          event_type: :update,
+          created_by_user: current_user,
+          name_changed:,
+          wiki_page:)
     end
 
     render json: TagRepr.base(tag.reload)
@@ -283,31 +341,282 @@ class TagsController < ApplicationController
 
   private
 
-  def build_tag_children tag
+  def material_filter_param default:
+    value = params[:material_filter].to_s.presence
+    return default unless ['present', 'missing', 'any'].include?(value)
+
+    value
+  end
+
+  def build_with_depth_graph material_filter
+    children_by_parent_id = Hash.new { |h, k| h[k] = [] }
+    parent_ids_by_child_id = Hash.new { |h, k| h[k] = [] }
+
+    TagImplication.pluck(:parent_tag_id, :tag_id).each do |parent_id, child_id|
+      children_by_parent_id[parent_id] << child_id
+      parent_ids_by_child_id[child_id] << parent_id
+    end
+
+    tag_ids = (children_by_parent_id.keys +
+               parent_ids_by_child_id.keys +
+               Tag.where(category: ['meme', 'character', 'material']).pluck(:id)).uniq
+
+    material_tag_ids = Material.unscoped.kept.where.not(tag_id: nil).distinct.pluck(:tag_id).to_set
+
+    tags_by_id = Tag.joins(:tag_name)
+                    .where(id: tag_ids)
+                    .pluck('tags.id', 'tag_names.name', 'tags.category', 'tags.deprecated_at')
+                    .each_with_object({ }) do |(id, name, category, deprecated_at), h|
+      h[id] = { name:, category:, deprecated: deprecated_at.present?,
+                has_material: material_tag_ids.include?(id) }
+    end
+
+    { children_by_parent_id:, parent_ids_by_child_id:, tags_by_id:,
+      visible_child_tag_ids_by_parent_id: { },
+      visible_subtree_by_tag_id: { },
+      material_filter: }
+  end
+
+  def visible_root_tag_ids graph
+    root_ids = Set.new
+
+    graph[:tags_by_id].each do |tag_id, attrs|
+      next unless visible_root_tag?(tag_id, graph)
+      next unless visible_subtree?(tag_id, graph)
+
+      if attrs[:deprecated]
+        collect_visible_child_tag_ids(tag_id, graph, root_ids, Set.new)
+      else
+        root_ids << tag_id
+      end
+    end
+
+    root_ids.to_a
+  end
+
+  def visible_root_tag? tag_id, graph
+    seen = Set.new([tag_id])
+    stack = graph[:parent_ids_by_child_id][tag_id].dup
+
+    until stack.empty?
+      parent_id = stack.pop
+      next if seen.include?(parent_id)
+
+      seen << parent_id
+
+      parent = graph[:tags_by_id][parent_id]
+      next unless parent
+
+      return false unless parent[:deprecated]
+
+      stack.concat(graph[:parent_ids_by_child_id][parent_id])
+    end
+
+    true
+  end
+
+  def visible_child_tag_ids parent_tag_id, graph
+    cache = graph[:visible_child_tag_ids_by_parent_id]
+    return cache[parent_tag_id] if cache.key?(parent_tag_id)
+
+    visible_ids = Set.new
+
+    graph[:children_by_parent_id][parent_tag_id].each do |child_tag_id|
+      collect_visible_child_tag_ids(child_tag_id, graph, visible_ids, Set.new([parent_tag_id]))
+    end
+
+    cache[parent_tag_id] = visible_ids.to_a
+  end
+
+  def collect_visible_child_tag_ids tag_id, graph, visible_ids, seen
+    return if seen.include?(tag_id)
+
+    seen = seen.dup << tag_id
+    tag = graph[:tags_by_id][tag_id]
+    return unless tag
+
+    if tag[:deprecated]
+      graph[:children_by_parent_id][tag_id].each do |child_tag_id|
+        collect_visible_child_tag_ids(child_tag_id, graph, visible_ids, seen)
+      end
+      return
+    end
+
+    if with_depth_visible_tag?(tag, graph[:material_filter])
+      visible_ids << tag_id
+      return
+    end
+
+    visible_ids << tag_id if graph[:children_by_parent_id][tag_id].any? { |child_tag_id|
+      visible_subtree?(child_tag_id, graph)
+    }
+  end
+
+  def with_depth_visible_tag? tag, material_filter
+    return false unless tag[:category].in?(['meme', 'character', 'material']) && !tag[:deprecated]
+
+    case material_filter
+    when 'present'
+      tag[:has_material]
+    when 'missing'
+      !tag[:has_material]
+    else
+      true
+    end
+  end
+
+  def visible_subtree? tag_id, graph
+    cache = graph[:visible_subtree_by_tag_id]
+    return cache[tag_id] if cache.key?(tag_id)
+
+    tag = graph[:tags_by_id][tag_id]
+    return cache[tag_id] = false unless tag
+
+    if tag[:deprecated]
+      return cache[tag_id] =
+                 graph[:children_by_parent_id][tag_id].any? { |child_tag_id|
+                   visible_subtree?(child_tag_id, graph)
+                 }
+    end
+
+    cache[tag_id] =
+        with_depth_visible_tag?(tag, graph[:material_filter]) ||
+        graph[:children_by_parent_id][tag_id].any? { |child_tag_id|
+          visible_subtree?(child_tag_id, graph)
+        }
+  end
+
+  def with_depth_lightweight_repr tag_id, graph
+    tag = graph[:tags_by_id][tag_id]
+
+    { id: tag_id,
+      name: tag[:name],
+      category: tag[:category],
+      deprecated: tag[:deprecated],
+      has_material: tag[:has_material],
+      has_children: visible_child_tag_ids(tag_id, graph).present?,
+      children: [] }
+  end
+
+  def build_tag_children tag, graph: nil
     material = tag.materials.first
-    file = nil
-    content_type = nil
-    if material&.file&.attached?
-      file = rails_storage_proxy_url(material.file, only_path: false)
-      content_type = material.file.blob.content_type
+    tag_graph = graph && graph[:tags_by_id][tag.id]
+    material =
+        nil if tag_graph && !with_depth_visible_tag?(tag_graph, graph[:material_filter])
+
+    children = tag.children.sort_by(&:name)
+    if graph
+      children = children.filter { |child_tag| visible_subtree?(child_tag.id, graph) }
     end
 
     TagRepr.base(tag).merge(
-        children: tag.children.sort_by { _1.name }.map { build_tag_children(_1) },
-        material: material.as_json&.merge(file:, content_type:))
+        children: children.map { build_tag_children(_1, graph:) },
+        has_material: tag_graph ? tag_graph[:has_material] : material.present?,
+        material: material && MaterialRepr.base(material, host: request.base_url))
   end
 
-  def record_tag_version! tag, event_type:, created_by_user:
+  def record_tag_version! tag, event_type:, created_by_user:, name_changed: false, wiki_page: nil
     if tag.nico?
       NicoTagVersionRecorder.record!(tag:, event_type:, created_by_user:)
-    else
-      TagVersionRecorder.record!(tag:, event_type:, created_by_user:)
+      return
     end
+
+    TagVersionRecorder.record!(tag:, event_type:, created_by_user:)
+
+    return unless name_changed
+
+    wiki_page ||= tag.tag_name.wiki_page
+    return unless wiki_page&.wiki_versions&.exists?
+
+    WikiVersionRecorder.record!(
+        page: wiki_page,
+        event_type: :update,
+        created_by_user:)
+  end
+
+  def validate_tag_rename tag, name
+    return true if name.blank? || name == tag.name
+
+    if tag.in?([Tag.tagme, Tag.bot, Tag.no_deerjikist, Tag.video, Tag.niconico])
+      render_unprocessable_entity 'システム・タグの名称は変更できません．', field: :name
+      return false
+    end
+
+    target_tag_name = TagName.with_discarded.find_by(name:)
+    return true if target_tag_name.nil?
+    return true if target_tag_name.canonical_id?
+
+    render_unprocessable_entity 'その名前は既に使はれてゐます．', field: :name
+    false
+  end
+
+  def rename_tag_name! tag, name
+    return if name == tag.name
+
+    current_tag_name = tag.tag_name
+    target_tag_name = TagName.with_discarded.find_by(name:)
+
+    if target_tag_name.nil?
+      current_tag_name.update!(name:)
+      return
+    end
+
+    promote_tag_alias!(
+        tag,
+        current_tag_name:,
+        promoted_tag_name: target_tag_name)
+  end
+
+  def promote_tag_alias! tag, current_tag_name:, promoted_tag_name:
+    old_owner_tag = promoted_tag_name.canonical&.tag
+
+    if old_owner_tag && old_owner_tag != tag
+      TagVersioning.ensure_snapshot!(old_owner_tag, created_by_user: current_user)
+    end
+
+    promoted_tag_name.undiscard! if promoted_tag_name.discarded?
+    promoted_tag_name.update!(canonical: nil)
+
+    TagName.with_discarded
+           .where(canonical_id: current_tag_name.id)
+           .where.not(id: promoted_tag_name.id)
+           .find_each do |alias_tag_name|
+      alias_tag_name.update!(canonical: promoted_tag_name)
+    end
+
+    current_tag_name.wiki_page&.update!(tag_name: promoted_tag_name)
+    tag.update!(tag_name: promoted_tag_name)
+    current_tag_name.association(:wiki_page).reset
+    current_tag_name.association(:tag).reset
+    current_tag_name.reload.update!(canonical: promoted_tag_name)
+
+    return unless old_owner_tag && old_owner_tag != tag
+
+    record_tag_version!(old_owner_tag.reload, event_type: :update, created_by_user: current_user)
   end
 
   def update_aliases! tag, alias_names
+    alias_names = alias_names.uniq
+
+    affected_tags = [tag]
+
     current_aliases = tag.tag_name.aliases.to_a
 
+    current_aliases.each do |alias_tag_name|
+      next if alias_names.include?(alias_tag_name.name)
+
+      affected_tags << alias_tag_name.canonical&.tag
+    end
+
+    alias_names.each do |alias_name|
+      alias_tag_name = TagName.find_undiscard_or_create_by!(name: alias_name)
+      affected_tags << alias_tag_name.canonical&.tag
+    end
+
+    affected_tags.compact.uniq.each do |affected_tag|
+      TagVersioning.ensure_snapshot!(affected_tag, created_by_user: current_user)
+    end
+
     current_aliases.each do |alias_tag_name|
       next if alias_names.include?(alias_tag_name.name)
 
@@ -318,12 +627,16 @@ class TagsController < ApplicationController
       alias_tag_name = TagName.find_undiscard_or_create_by!(name: alias_name)
       alias_tag_name.update!(canonical: tag.tag_name)
     end
+
+    affected_tags.compact.uniq.each do |affected_tag|
+      record_tag_version!(affected_tag, event_type: :update, created_by_user: current_user)
+    end
   end
 
   def update_parent_tags! tag, parent_names
-    parent_tags = Tag.normalise_tags(parent_names, with_tagme: false,
-                                                   with_no_deerjikist: false,
-                                                   deny_nico: true)
+    parent_tags = Tag.normalise_tags!(parent_names, with_tagme: false,
+                                                    with_no_deerjikist: false,
+                                                    deny_nico: true)
 
     old_parent_tags = tag.parents.to_a
 
@@ -338,4 +651,40 @@ class TagsController < ApplicationController
       TagImplication.create!(tag:, parent_tag:)
     end
   end
+
+  def normalise_deerjikist_code platform, code
+    return code if platform != 'youtube' || code[0] != '@'
+
+    url = "https://www.youtube.com/#{ code }"
+
+    html = Net::HTTP.get(URI(url))
+
+    canonical = html[
+        /<link rel="canonical" href="https:\/\/www\.youtube\.com\/channel\/(UC[a-zA-Z0-9_-]{22})"/,
+        1]
+    return canonical if canonical
+
+    html[/"channelId":"(UC[a-zA-Z0-9_-]{22})"/, 1] || html[/\bUC[a-zA-Z0-9_-]{22}\b/]
+  rescue
+    nil
+  end
+
+  def render_deerjikist_form_record_invalid deerjikist, index
+    fields = { }
+
+    deerjikist.errors.each do |error|
+      field =
+          case error.attribute
+          when :platform, :code
+            "deerjikists.#{ index }.#{ error.attribute }"
+          else
+            :deerjikists
+          end
+
+      fields[field] ||= []
+      fields[field] << error.full_message
+    end
+
+    render_validation_error fields:
+  end
 end

backend/app/controllers/theatre_comments_controller.rb

@@ -1,21 +1,28 @@
 class TheatreCommentsController < ApplicationController
   def index
+    limit = params[:limit].to_i
+    limit = 20 if limit <= 0
+
     no_gt = params[:no_gt].to_i
-    no_gt = 0 if no_gt.negative?
+    no_gt = 0 if no_gt < 0
 
     comments = TheatreComment
                .where(theatre_id: params[:theatre_id])
                .where('no > ?', no_gt)
                .order(no: :desc)
+               .limit(limit)
 
-    render json: comments.as_json(include: { user: { only: [:id, :name] } })
+    render json: comments.map {
+      _1.as_json(include: { user: { only: [:id, :name] } })
+          .merge(content: _1.discarded? ? nil : _1.content, deleted: _1.discarded?)
+    }
   end
 
   def create
     return head :unauthorized unless current_user
 
     content = params[:content]
-    return head :unprocessable_entity if content.blank?
+    return render_unprocessable_entity('本文は必須です．', field: :content) if content.blank?
 
     theatre = Theatre.find_by(id: params[:theatre_id])
     return head :not_found unless theatre
@@ -29,4 +36,19 @@ class TheatreCommentsController < ApplicationController
 
     render json: comment, status: :created
   end
+
+  def destroy
+    return head :unauthorized unless current_user
+
+    theatre_id = params[:theatre_id].to_i
+    no = params[:id].to_i
+
+    comment = TheatreComment.find_by(theatre_id:, no:)
+    return head :not_found unless comment
+    return head :forbidden unless comment.user == current_user
+
+    comment.discard!
+
+    head :no_content
+  end
 end

backend/app/controllers/theatre_programmes_controller.rb

@@ -0,0 +1,22 @@
+class TheatreProgrammesController < ApplicationController
+  def index
+    limit = params[:limit].to_i
+    limit = 100 if limit <= 0
+
+    position_gt = params[:position_gt].to_i
+    position_gt = 0 if position_gt < 0
+
+    programmes = TheatreProgramme
+                 .where(theatre_id: params[:theatre_id])
+                 .where('position > ?', position_gt)
+                 .includes(:post)
+                 .order(position: :desc).limit(100)
+                 .limit(limit)
+
+    render json: programmes.map { |programme|
+      programme.as_json.merge(post: { id: programme.post.id,
+                                      title: programme.post.title,
+                                      url: programme.post.url })
+    }
+  end
+end

backend/app/controllers/theatre_skip_events_controller.rb

@@ -0,0 +1,22 @@
+class TheatreSkipEventsController < ApplicationController
+  def index
+    limit = params[:limit].to_i
+    limit = 50 if limit <= 0
+
+    events =
+        TheatreSkipEvent
+        .where(theatre_id: params[:theatre_id])
+        .includes(:post, tags: :tag_name)
+        .order(created_at: :desc)
+        .limit(limit)
+
+    render json: events.map { |event|
+      { id: event.id,
+        theatre_id: event.theatre_id,
+        post: { id: event.post.id, title: event.post.title, url: event.post.url },
+        tags: event.tags.map { |tag| { id: tag.id, name: tag.name } },
+        programme_position: event.programme_position,
+        created_at: event.created_at }
+    }
+  end
+end

backend/app/controllers/theatres_controller.rb

@@ -31,9 +31,7 @@ class TheatresController < ApplicationController
       post_started_at = theatre.current_post_started_at
     end
 
-    render json: {
-        host_flg:, post_id:, post_started_at:,
-        watching_users: theatre.watching_users.as_json(only: [:id, :name]) }
+    render json: theatre_info_json(theatre, host_flg:, post_id:, post_started_at:)
   end
 
   def next_post
@@ -43,12 +41,119 @@ class TheatresController < ApplicationController
     return head :not_found unless theatre
     return head :forbidden if theatre.host_user != current_user
 
-    post = Post.where("url LIKE '%nicovideo.jp%'")
-           .or(Post.where("url LIKE '%youtube.com%'"))
-           .order('RAND()')
-           .first
-    theatre.update!(current_post: post, current_post_started_at: Time.current)
+    ApplicationRecord.transaction do
+      theatre.lock!
+      TheatrePostAdvancer.call(theatre:)
+    end
 
     head :no_content
   end
+
+  def skip_vote
+    return head :unauthorized unless current_user
+
+    theatre = Theatre.find_by(id: params[:id])
+    return head :not_found unless theatre
+    requested_post_id = params[:post_id].to_i
+    return head :unprocessable_entity if requested_post_id <= 0
+
+    skipped = false
+    conflicted = false
+
+    ApplicationRecord.transaction do
+      theatre.lock!
+      if theatre.current_post
+        TheatreWatchingUser.find_or_initialize_by(theatre:, user: current_user).tap {
+          _1.expires_at = 30.seconds.from_now
+        }.save!
+
+        if theatre.current_post_id != requested_post_id
+          conflicted = true
+          next
+        end
+
+        TheatreSkipVote.find_or_create_by!(theatre:, post_id: requested_post_id, user: current_user)
+
+        vote_status = skip_vote_status(theatre)
+        if vote_status[:votes_count] >= vote_status[:required_count]
+          TheatreSkipFinalizer.call(theatre:, user: current_user)
+          TheatrePostAdvancer.call(theatre:)
+          skipped = true
+        end
+      end
+    end
+
+    theatre.reload
+    return render json: theatre_info_json(theatre, skipped: false), status: :conflict if conflicted
+
+    render json: theatre_info_json(theatre, skipped:)
+  end
+
+  def unskip_vote
+    return head :unauthorized unless current_user
+
+    theatre = Theatre.find_by(id: params[:id])
+    return head :not_found unless theatre
+    requested_post_id = params[:post_id].to_i
+    return head :unprocessable_entity if requested_post_id <= 0
+
+    conflicted = false
+
+    theatre.with_lock do
+      if theatre.current_post
+        if theatre.current_post_id != requested_post_id
+          conflicted = true
+        else
+          TheatreSkipVote.where(theatre:, post_id: requested_post_id, user: current_user).delete_all
+        end
+      end
+    end
+
+    theatre.reload
+    return render json: theatre_info_json(theatre, skipped: false), status: :conflict if conflicted
+
+    render json: theatre_info_json(theatre, skipped: false)
+  end
+
+  def post_selection_weights
+    theatre = Theatre.find_by(id: params[:id])
+    return head :not_found unless theatre
+
+    render json: TheatrePostSelector.new(theatre:).weight_json
+  end
+
+  private
+
+  def theatre_info_json(theatre, host_flg: nil, post_id: nil, post_started_at: nil, skipped: nil)
+    host_flg = theatre.host_user_id == current_user&.id if host_flg.nil?
+    post_id = theatre.current_post_id if post_id.nil?
+    post_started_at = theatre.current_post_started_at if post_started_at.nil?
+
+    json = { host_flg:,
+             post_id:,
+             post_started_at:,
+             post_elapsed_ms: post_started_at ? ((Time.current - post_started_at) * 1000).floor : nil,
+             watching_users: theatre.watching_users.as_json(only: [:id, :name]),
+             skip_vote: skip_vote_status(theatre) }
+    json[:skipped] = skipped unless skipped.nil?
+    json
+  end
+
+  def skip_vote_status(theatre)
+    watching_user_ids = theatre.watching_users.ids
+    watching_users_count = watching_user_ids.size
+    required_count = (watching_users_count / 2) + 1
+    post = theatre.current_post
+    votes =
+        if post
+          TheatreSkipVote.where(theatre:, post:, user_id: watching_user_ids)
+        else
+          TheatreSkipVote.none
+        end
+
+    { votes_count: post ? votes.count : 0,
+      required_count:,
+      watching_users_count:,
+      voted: post && current_user ? votes.exists?(user_id: current_user.id) : false }
+  end
 end

backend/app/controllers/user_settings_controller.rb

@@ -0,0 +1,61 @@
+class UserSettingsController < ApplicationController
+  wrap_parameters false
+
+  def show
+    return head :unauthorized unless current_user
+
+    render json: current_setting.serializable_hash
+  end
+
+  def update
+    return head :unauthorized unless current_user
+
+    raw_attributes = editable_raw_attributes
+    field_errors = validate_raw_attributes(raw_attributes)
+    return render_validation_error fields: field_errors if field_errors.present?
+
+    setting = current_setting
+    setting.assign_attributes(raw_attributes)
+
+    if setting.save
+      render json: setting.serializable_hash, status: :ok
+    else
+      render_validation_error setting
+    end
+  end
+
+  private
+
+  def current_setting
+    Setting.find_or_create_by!(user: current_user) do |setting|
+      setting.assign_attributes(Setting.defaults)
+    end
+  rescue ActiveRecord::RecordNotUnique
+    Setting.find_by!(user: current_user)
+  end
+
+  def editable_raw_attributes
+    request.request_parameters.slice(*Setting::EDITABLE_ATTRIBUTES)
+  end
+
+  def validate_raw_attributes raw_attributes
+    raw_attributes.each_with_object({ }) do |(key, value), errors|
+      next if value_matches_type?(key, value)
+
+      errors[key.to_sym] = ['値の型が不正です．']
+    end
+  end
+
+  def value_matches_type? key, value
+    case Setting::TYPE_BY_ATTRIBUTE.fetch(key)
+    when :string
+      value.is_a?(String)
+    when :integer
+      value.is_a?(Integer)
+    when :boolean
+      value == true || value == false
+    else
+      false
+    end
+  end
+end

backend/app/controllers/user_theme_slots_controller.rb

@@ -0,0 +1,38 @@
+class UserThemeSlotsController < ApplicationController
+  wrap_parameters false
+
+  def index
+    return head :unauthorized unless current_user
+
+    render json: current_user
+                 .theme_slots
+                 .order(:base_theme, :slot_no)
+                 .map { |slot| slot.serializable_hash }
+  end
+
+  def update
+    return head :unauthorized unless current_user
+
+    base_theme = params[:base_theme].to_s
+    slot_no = params[:slot_no].to_i
+    tokens = params[:tokens]
+
+    unless UserThemeSlot::BASE_THEMES.include?(base_theme)
+      return render_validation_error fields: { base_theme: ['値が不正です．'] }
+    end
+    unless UserThemeSlot::SLOT_NOS.include?(slot_no)
+      return render_validation_error fields: { slot_no: ['値が不正です．'] }
+    end
+    unless tokens.is_a?(ActionController::Parameters) || tokens.is_a?(Hash)
+      return render_validation_error fields: { tokens: ['JSON object で指定してください．'] }
+    end
+
+    slot = UserThemeSlot.find_or_initialize_by(user: current_user,
+                                               base_theme:,
+                                               slot_no:)
+    slot.tokens = tokens.is_a?(ActionController::Parameters) ? tokens.to_unsafe_h : tokens
+    slot.save!
+
+    render json: slot.serializable_hash, status: :ok
+  end
+end

backend/app/controllers/users_controller.rb

@@ -1,18 +1,22 @@
 class UsersController < ApplicationController
   def create
-    user = User.create!(inheritance_code: SecureRandom.uuid, role: 'guest')
+    user = nil
+    User.transaction do
+      user = User.create!(inheritance_code: SecureRandom.uuid, role: :guest)
+      attach_ip_address!(user)
+    end
+
     render json: { code: user.inheritance_code,
-                   user: user.slice(:id, :name, :inheritance_code, :role) }
+                   user: user.slice(:id, :name, :inheritance_code, :role) },
+           status: :created
   end
 
   def verify
-    ip_bin = IPAddr.new(request.remote_ip).hton
-    ip_address = IpAddress.find_or_create_by!(ip_address: ip_bin)
-
     user = User.find_by(inheritance_code: params[:code])
     return render json: { valid: false } unless user
+    return head :forbidden if user.banned?
 
-    UserIp.find_or_create_by!(user:, ip_address:)
+    attach_ip_address!(user)
 
     render json: { valid: true, user: user.slice(:id, :name, :inheritance_code, :role) }
   end
@@ -38,12 +42,21 @@ class UsersController < ApplicationController
     return head :unauthorized if user&.id != params[:id].to_i
 
     name = params[:name]
-    return head :bad_request if name.blank?
+    return render_unprocessable_entity('名前は必須です．', field: :name) if name.blank?
 
     if user.update(name:)
-      render json: user.slice(:id, :name, :inheritance_code, :role), status: :created
+      render json: user.slice(:id, :name, :inheritance_code, :role), status: :ok
     else
-      render json: user.errors, status: :unprocessable_entity
+      render_validation_error user
     end
   end
+
+  private
+
+  def attach_ip_address! user
+    ip_bin = IPAddr.new(request.remote_ip).hton
+    ip_address = IpAddress.create_or_find_by!(ip_address: ip_bin)
+
+    UserIp.create_or_find_by!(user:, ip_address:)
+  end
 end

backend/app/controllers/wiki_pages_controller.rb

@@ -4,17 +4,18 @@ class WikiPagesController < ApplicationController
   def index
     title = params[:title].to_s.strip
     if title.blank?
-      return render json: WikiPageRepr.base(WikiPage.joins(:tag_name).includes(:tag_name))
+      return render json: WikiPageRepr.base(
+          WikiPage.joins(:tag_name).includes(tag_name: :tag))
     end
 
-    q = WikiPage.joins(:tag_name).includes(:tag_name)
+    q = WikiPage.joins(:tag_name).includes(tag_name: :tag)
         .where('tag_names.name LIKE ?', "%#{ WikiPage.sanitize_sql_like(title) }%")
     render json: WikiPageRepr.base(q.limit(20))
   end
 
   def show
     page = WikiPage.joins(:tag_name)
-                   .includes(:tag_name)
+                   .includes(tag_name: :tag)
                    .find_by(id: params[:id])
     render_wiki_page_or_404 page
   end
@@ -22,7 +23,7 @@ class WikiPagesController < ApplicationController
   def show_by_title
     title = params[:title].to_s.strip
     page = WikiPage.joins(:tag_name)
-                   .includes(:tag_name)
+                   .includes(tag_name: :tag)
                    .find_by(tag_name: { name: title })
     render_wiki_page_or_404 page
   end
@@ -46,17 +47,17 @@ class WikiPagesController < ApplicationController
 
   def diff
     id = params[:id]
-    return head :bad_request if id.blank?
+    return render_bad_request('id は必須です．') if id.blank?
 
     from = params[:from].presence
     to = params[:to].presence
 
-    page = WikiPage.joins(:tag_name).includes(:tag_name).find(id)
+    page = WikiPage.joins(:tag_name).includes(tag_name: :tag).find(id)
 
     from_rev = from && page.wiki_revisions.find(from)
     to_rev = to ? page.wiki_revisions.find(to) : page.current_revision
     if ((from_rev && !(from_rev.content?)) || !(to_rev&.content?))
-      return head :unprocessable_entity
+      return render_unprocessable_entity('差分を表示できない版です．')
     end
 
     diffs = Diff::LCS.sdiff(from_rev&.body&.lines || [], to_rev.body.lines)
@@ -76,6 +77,7 @@ class WikiPagesController < ApplicationController
 
     render json: { wiki_page_id:      page.id,
                    title:             page.title,
+                   deprecated_at:     page.deprecated_at,
                    older_revision_id: from_rev&.id,
                    newer_revision_id: to_rev.id,
                    diff:              diff_json }
@@ -85,22 +87,27 @@ class WikiPagesController < ApplicationController
     return head :unauthorized unless current_user
     return head :forbidden unless current_user.gte_member?
 
-    name = params[:title]&.strip
+    title = params[:title].to_s.strip
     body = params[:body].to_s
+    message = params[:message].presence
 
-    return head :unprocessable_entity if name.blank? || body.blank?
+    return render_unprocessable_entity('タイトルは必須です．', field: :title) if title.blank?
+    return render_unprocessable_entity('本文は必須です．', field: :body) if body.blank?
 
-    tag_name = TagName.find_undiscard_or_create_by!(name:)
-    page = WikiPage.new(tag_name:, created_user: current_user, updated_user: current_user)
-    if page.save
-      message = params[:message].presence
-      Wiki::Commit.content!(page:, body:, created_user: current_user, message:)
+    tag_name = TagName.find_undiscard_or_create_by!(name: title)
 
-      render json: WikiPageRepr.base(page), status: :created
-    else
-      render json: { errors: page.errors.full_messages },
-             status: :unprocessable_entity
-    end
+    page =
+        Wiki::Commit.create_content!(
+            tag_name:,
+            body:,
+            created_by_user: current_user,
+            message:)
+
+    render json: WikiPageRepr.base(page), status: :created
+  rescue ActiveRecord::RecordInvalid => e
+    render_validation_error e.record
+  rescue ActiveRecord::RecordNotUnique
+    render_record_not_unique
   end
 
   def update
@@ -110,22 +117,38 @@ class WikiPagesController < ApplicationController
     title = params[:title]&.strip
     body = params[:body].to_s
 
-    return head :unprocessable_entity if title.blank? || body.blank?
+    return render_unprocessable_entity('タイトルは必須です．', field: :title) if title.blank?
+    return render_unprocessable_entity('本文は必須です．', field: :body) if body.blank?
 
     page = WikiPage.find(params[:id])
-    base_revision_id = page.current_revision.id
+    base_revision_id = params[:base_revision_id].presence
 
-    if params[:title].present? && params[:title].strip != page.title
-      return head :unprocessable_entity
+    ApplicationRecord.transaction do
+      page.lock!
+
+      old_title = page.title
+
+      tag = Tag.find_by(tag_name_id: page.tag_name_id)
+
+      if tag && title != old_title
+        TagVersioning.ensure_snapshot!(tag, created_by_user: current_user)
+      end
+
+      page.tag_name.update!(name: title) if title != old_title
+
+      message = params[:message].presence
+      Wiki::Commit.content!(page:,
+                            body:,
+                            created_user: current_user,
+                            message:,
+                            base_revision_id:)
+
+      if tag && title != old_title
+        tag.reload
+        TagVersionRecorder.record!(tag:, event_type: :update, created_by_user: current_user)
+      end
     end
 
-    message = params[:message].presence
-    Wiki::Commit.content!(page:,
-                          body:,
-                          created_user: current_user,
-                          message:,
-                          base_revision_id:)
-
     head :ok
   end
 
@@ -136,7 +159,7 @@ class WikiPagesController < ApplicationController
   def changes
     id = params[:id].presence
     q = WikiRevision.joins(wiki_page: :tag_name)
-                    .includes(:created_user, wiki_page: :tag_name)
+                    .includes(:created_user, wiki_page: { tag_name: :tag })
                     .order(id: :desc)
     q = q.where(wiki_page_id: id) if id
 
@@ -144,7 +167,9 @@ class WikiPagesController < ApplicationController
       { revision_id:  rev.id,
         pred:         rev.base_revision_id,
         succ:         nil,
-        wiki_page:    { id: rev.wiki_page_id, title: rev.wiki_page.title },
+        wiki_page:    { id: rev.wiki_page_id,
+                        title: rev.wiki_page.title,
+                        deprecated_at: rev.wiki_page.deprecated_at },
         user:         rev.created_user && { id: rev.created_user.id, name: rev.created_user.name },
         kind:         rev.kind,
         message:      rev.message,

backend/app/models/gekanator_ai_run.rb

@@ -0,0 +1,21 @@
+class GekanatorAiRun < ApplicationRecord
+  STATUSES = ['pending', 'running', 'succeeded', 'failed', 'blocked_budget'].freeze
+
+  belongs_to :gekanator_question_suggestion
+
+  validates :model, presence: true, length: { maximum: 255 }
+  validates :status, presence: true, inclusion: { in: STATUSES }
+  validates :input_tokens,
+            presence: true,
+            numericality: { only_integer: true, greater_than_or_equal_to: 0 }
+  validates :output_tokens,
+            presence: true,
+            numericality: { only_integer: true, greater_than_or_equal_to: 0 }
+  validates :estimated_cost_jpy,
+            presence: true,
+            numericality: { greater_than_or_equal_to: 0 }
+
+  scope :this_month, lambda {
+    where(created_at: Time.current.beginning_of_month..Time.current.end_of_month)
+  }
+end

backend/app/models/gekanator_game.rb

@@ -0,0 +1,15 @@
+class GekanatorGame < ApplicationRecord
+  belongs_to :user
+  belongs_to :guessed_post, class_name: 'Post'
+  belongs_to :correct_post, class_name: 'Post'
+  has_many :question_suggestions,
+           class_name: 'GekanatorQuestionSuggestion',
+           dependent: :delete_all
+  has_many :question_examples,
+           class_name: 'GekanatorQuestionExample',
+           dependent: :delete_all
+
+  validates :answers, presence: true
+  validates :question_count, numericality: { greater_than_or_equal_to: 0 }
+  validates :won, inclusion: { in: [true, false] }
+end

backend/app/models/gekanator_question.rb

@@ -0,0 +1,23 @@
+class GekanatorQuestion < ApplicationRecord
+  KINDS = ['tag', 'source', 'title', 'original_date', 'post_similarity'].freeze
+  SOURCES = ['user_suggested', 'ai_generated', 'admin_curated'].freeze
+  STATUSES = ['pending', 'accepted', 'rejected', 'disabled'].freeze
+
+  belongs_to :gekanator_question_suggestion, optional: true
+  belongs_to :created_by, class_name: 'User', optional: true
+  has_many :gekanator_question_examples, dependent: :delete_all
+
+  validates :kind, presence: true, inclusion: { in: KINDS }
+  validates :source, presence: true, inclusion: { in: SOURCES }
+  validates :status, presence: true, inclusion: { in: STATUSES }
+  validates :text, presence: true, length: { maximum: 1000 }
+  validates :condition, presence: true
+  validates :priority_weight,
+            presence: true,
+            numericality: {
+              greater_than: 0,
+              less_than_or_equal_to: 3
+            }
+
+  scope :accepted, -> { where(status: 'accepted') }
+end

backend/app/models/gekanator_question_example.rb

@@ -0,0 +1,97 @@
+class GekanatorQuestionExample < ApplicationRecord
+  ANSWERS = GekanatorQuestionSuggestion::ANSWERS
+  NON_UNKNOWN_ANSWERS = ANSWERS - ['unknown']
+  SOURCES = ['initial_suggestion', 'post_game_answer', 'post_game_extra'].freeze
+
+  belongs_to :gekanator_question
+  belongs_to :post
+  belongs_to :user
+  belongs_to :gekanator_game, optional: true
+
+  validates :answer, presence: true, inclusion: { in: ANSWERS }
+  validates :answer_counts, presence: true
+  validates :sample_count,
+            presence: true,
+            numericality: {
+              only_integer: true,
+              greater_than: 0
+            }
+  validates :source, presence: true, inclusion: { in: SOURCES }
+  validates :weight,
+            presence: true,
+            numericality: {
+              greater_than: 0
+            }
+
+  before_validation :normalize_learning_state
+
+  def record_answer!(answer:, source:, gekanator_game: nil)
+    answer = answer.to_s
+    raise ArgumentError, 'invalid answer' unless ANSWERS.include?(answer)
+
+    counts = normalized_answer_counts
+    counts[answer] += 1
+
+    self.answer_counts = counts
+    self.sample_count = counts.values.sum
+    self.gekanator_game = gekanator_game if gekanator_game.present?
+    self.source = source
+
+    apply_aggregated_answer!(preferred_answer: answer)
+    self
+  end
+
+  private
+
+  def normalize_learning_state
+    counts = normalized_answer_counts
+
+    if counts.values.sum.zero? && answer.present?
+      counts[answer] = 1
+    end
+
+    self.answer_counts = counts
+    self.sample_count = counts.values.sum
+
+    apply_aggregated_answer!
+  end
+
+  def apply_aggregated_answer!(preferred_answer: nil)
+    counts = normalized_answer_counts
+    known_counts = counts.slice(*NON_UNKNOWN_ANSWERS)
+    known_total = known_counts.values.sum
+
+    if known_total.zero?
+      self.answer = 'unknown'
+      self.weight = 0.1
+      return
+    else
+      max_count = known_counts.values.max
+      candidates = known_counts.select { |_answer, count| count == max_count }.keys
+      self.answer =
+          if preferred_answer.present? && candidates.include?(preferred_answer)
+            preferred_answer
+          elsif answer.present? && candidates.include?(answer)
+            answer
+          else
+            candidates.first
+          end
+    end
+
+    consensus = max_count.to_f / known_total
+    self.weight = Math.sqrt(known_total) * consensus
+  end
+
+  def normalized_answer_counts
+    base = ANSWERS.index_with(0)
+
+    answer_counts.to_h.each do |key, value|
+      answer_key = key.to_s
+      next unless ANSWERS.include?(answer_key)
+
+      base[answer_key] = value.to_i
+    end
+
+    base
+  end
+end

backend/app/models/gekanator_question_suggestion.rb

@@ -0,0 +1,12 @@
+class GekanatorQuestionSuggestion < ApplicationRecord
+  ANSWERS = ['yes', 'no', 'partial', 'probably_no', 'unknown'].freeze
+
+  belongs_to :gekanator_game
+  belongs_to :user
+  has_many :gekanator_questions, dependent: :nullify
+  has_many :gekanator_ai_runs, dependent: :destroy
+
+  validates :question_text, presence: true, length: { maximum: 1000 }
+  validates :answer, presence: true, inclusion: { in: ANSWERS }
+  validates :processed, inclusion: { in: [true, false] }
+end

backend/app/models/ip_address.rb

@@ -1,6 +1,10 @@
 class IpAddress < ApplicationRecord
   validates :ip_address, presence: true, length: { maximum: 16 }
-  validates :banned, inclusion: { in: [true, false] }
 
-  has_many :users
+  has_many :user_ips, dependent: :destroy
+  has_many :users, through: :user_ips
+
+  def banned? = banned_at.present?
+  def ban! = banned? || update!(banned_at: Time.current)
+  def unban! = update!(banned_at: nil)
 end

backend/app/models/material.rb

@@ -1,6 +1,9 @@
 class Material < ApplicationRecord
   include MyDiscard
 
+  SOURCE_KINDS = ['uri', 'google_drive_path', 'google_drive_file',
+                  'legacy_drive_path'].freeze
+
   default_scope -> { kept }
 
   belongs_to :parent, class_name: 'Material', optional: true
@@ -10,9 +13,18 @@ class Material < ApplicationRecord
   belongs_to :created_by_user, class_name: 'User', optional: true
   belongs_to :updated_by_user, class_name: 'User', optional: true
 
-  has_one_attached :file, dependent: :purge
+  has_many :material_versions, dependent: :destroy
+  has_many :material_export_items, dependent: :destroy
 
-  validates :tag_id, presence: true, uniqueness: true
+  has_one_attached :file, dependent: :purge
+  has_one_attached :thumbnail, dependent: :purge
+
+  before_validation :assign_normalized_source_key
+
+  validates :tag_id, uniqueness: true, allow_nil: true
+  validates :source_kind,
+            inclusion: { in: SOURCE_KINDS },
+            allow_blank: true
 
   validate :file_must_be_attached
   validate :tag_must_be_material_category
@@ -23,8 +35,37 @@ class Material < ApplicationRecord
     file.blob.content_type
   end
 
+  def file_byte_size
+    return nil unless file&.attached?
+
+    file.blob.byte_size
+  end
+
+  def file_filename
+    return nil unless file&.attached?
+
+    file.blob.filename.to_s
+  end
+
+  def snapshot_export_paths
+    material_export_items.order(:profile).pluck(:profile, :export_path).to_h
+  end
+
   private
 
+  def assign_normalized_source_key
+    if source_kind.blank?
+      self.normalized_source_key = nil
+      return
+    end
+
+    self.normalized_source_key =
+        MaterialSyncSuppression.normalize_source_key(source_kind:,
+                                                     source_uri:,
+                                                     drive_path: source_path,
+                                                     drive_file_id: source_file_id)
+  end
+
   def file_must_be_attached
     return if url.present? || file.attached?
 

backend/app/models/material_export_item.rb

@@ -0,0 +1,48 @@
+class MaterialExportItem < ApplicationRecord
+  VALID_PROFILES = ['legacy_drive'].freeze
+
+  belongs_to :material
+  belongs_to :created_by_user, class_name: 'User', optional: true
+
+  validates :profile, presence: true, inclusion: { in: VALID_PROFILES }
+  validates :export_path, presence: true, uniqueness: { scope: :profile }
+  validates :material_id, uniqueness: { scope: :profile }
+  validate :export_path_must_be_relative_safe_path
+
+  scope :enabled, -> { where(enabled: true) }
+
+  private
+
+  def export_path_must_be_relative_safe_path
+    return if export_path.blank?
+
+    if export_path.start_with?('/')
+      errors.add(:export_path, '絶対パスは使へません．')
+    end
+
+    if export_path.match?(/\A[A-Za-z]:\//)
+      errors.add(:export_path, '絶対パスは使へません．')
+    end
+
+    if export_path.include?('\\')
+      errors.add(:export_path, '/ 区切りで指定してください．')
+    end
+
+    if export_path.include?("\0")
+      errors.add(:export_path, 'NUL は使へません．')
+    end
+
+    parts = export_path.split('/')
+    if export_path.include?('//')
+      errors.add(:export_path, '空の path segment は使へません．')
+    end
+
+    if parts.any? { |part| part.in?(['.', '..']) }
+      errors.add(:export_path, '.. は使へません．')
+    end
+
+    if export_path.end_with?('/')
+      errors.add(:export_path, 'directory path は使へません．')
+    end
+  end
+end

backend/app/models/material_import_block.rb

@@ -0,0 +1,29 @@
+class MaterialImportBlock < ApplicationRecord
+  MATCH_KINDS = ['sha256', 'exact_path', 'path_prefix', 'manual'].freeze
+  REASONS = [
+    'copyright_high_risk',
+    'copyright_takedown',
+    'adult_or_sensitive',
+    'personal_information',
+    'malware_or_dangerous_file',
+    'duplicate_or_low_quality',
+    'source_owner_request',
+    'other'
+  ].freeze
+
+  belongs_to :created_by_user, class_name: 'User', optional: true
+
+  validates :match_kind, presence: true, inclusion: { in: MATCH_KINDS }
+  validates :reason, presence: true, inclusion: { in: REASONS }
+  validates :sha256, length: { is: 64 }, allow_blank: true
+  validate :match_value_must_be_present
+
+  private
+
+  def match_value_must_be_present
+    return if match_kind == 'manual'
+    return if sha256.present? || external_path_pattern.present?
+
+    errors.add(:base, 'sha256 または external_path_pattern は必須です．')
+  end
+end

backend/app/models/material_sync_source.rb

@@ -0,0 +1,45 @@
+class MaterialSyncSource < ApplicationRecord
+  SOURCE_KINDS = ['uri', 'google_drive_path', 'google_drive_file', 'legacy_drive_path'].freeze
+
+  belongs_to :created_by_user, class_name: 'User', optional: true
+  belongs_to :updated_by_user, class_name: 'User', optional: true
+
+  validates :name, presence: true
+  validates :source_kind,
+            presence: true,
+            inclusion: { in: SOURCE_KINDS }
+  validates :profile, presence: true, inclusion: { in: MaterialExportItem::VALID_PROFILES }
+  validate :source_value_must_be_present
+
+  scope :enabled, -> { where(enabled: true) }
+
+  def normalized_source_key
+    MaterialSyncSuppression.normalize_source_key(source_kind:,
+                                                 source_uri:,
+                                                 source_path:,
+                                                 source_file_id:)
+  end
+
+  private
+
+  def source_value_must_be_present
+    return if source_value_present?
+
+    errors.add(:base, '同期元を指定してください．')
+  end
+
+  def source_value_present?
+    case source_kind
+    when 'uri'
+      source_uri.present?
+    when 'google_drive_file'
+      source_file_id.present? || source_uri.present?
+    when 'google_drive_path'
+      source_file_id.present? || source_uri.present?
+    when 'legacy_drive_path'
+      source_path.present?
+    else
+      normalized_source_key.present?
+    end
+  end
+end

backend/app/models/material_sync_suppression.rb

@@ -0,0 +1,105 @@
+class MaterialSyncSuppression < ApplicationRecord
+  SOURCE_KINDS = [
+    'uri',
+    'google_drive_path',
+    'google_drive_path_prefix',
+    'google_drive_file',
+    'legacy_drive_path',
+    'legacy_drive_path_prefix'
+  ].freeze
+
+  REASONS = [
+    'copyright_high_risk',
+    'copyright_takedown',
+    'adult_or_sensitive',
+    'personal_information',
+    'malware_or_dangerous_file',
+    'duplicate_or_low_quality',
+    'source_owner_request',
+    'other'
+  ].freeze
+
+  belongs_to :created_by_user, class_name: 'User', optional: true
+
+  before_validation :assign_normalized_source_key
+
+  validates :source_kind, presence: true, inclusion: { in: SOURCE_KINDS }
+  validates :reason, presence: true, inclusion: { in: REASONS }
+  validates :normalized_source_key, presence: true, uniqueness: true
+  validate :source_value_must_be_present
+  validate :drive_path_must_be_safe_relative_path
+
+  def self.normalize_source_key source_kind:,
+                                source_uri: nil,
+                                drive_path: nil,
+                                drive_file_id: nil,
+                                source_path: nil,
+                                source_file_id: nil
+    kind = source_kind.to_s
+    value =
+        case kind
+        when 'uri'
+          source_uri.to_s.strip
+        when 'google_drive_path', 'google_drive_path_prefix',
+             'legacy_drive_path', 'legacy_drive_path_prefix'
+          (source_path || drive_path).to_s.strip.gsub(%r{/+}, '/')
+        when 'google_drive_file'
+          (source_file_id || drive_file_id).to_s.strip
+        else
+          ''
+        end
+
+    return nil if kind.blank? || value.blank?
+
+    "#{ kind }:#{ value }"
+  end
+
+  private
+
+  def assign_normalized_source_key
+    normalize_path_fields!
+    self.normalized_source_key =
+        self.class.normalize_source_key(source_kind:,
+                                        source_uri:,
+                                        drive_path:,
+                                        drive_file_id:)
+  end
+
+  def source_value_must_be_present
+    return if normalized_source_key.present?
+
+    errors.add(:base, '同期元を指定してください．')
+  end
+
+  def drive_path_must_be_safe_relative_path
+    return unless source_kind.in?(
+        ['google_drive_path', 'google_drive_path_prefix',
+         'legacy_drive_path', 'legacy_drive_path_prefix'])
+
+    value = drive_path.to_s
+    return if value.blank?
+
+    if value.start_with?('/') || value.match?(/\A[A-Za-z]:\//)
+      errors.add(:drive_path, '相対 path を指定してください．')
+    end
+    if value.start_with?('My Drive/', 'マイドライブ/')
+      errors.add(:drive_path, '同期元フォルダからの相対 path を指定してください．')
+    end
+    errors.add(:drive_path, 'NUL は使へません．') if value.include?("\0")
+    errors.add(:drive_path, '連続スラッシュは使へません．') if value.include?('//')
+    errors.add(:drive_path, '末尾スラッシュは使へません．') if value.end_with?('/')
+
+    parts = value.split('/')
+    if parts.any? { |part| part.in?(['.', '..']) }
+      errors.add(:drive_path, '. や .. は使へません．')
+    end
+  end
+
+  def normalize_path_fields!
+    return unless source_kind.in?(
+        ['google_drive_path', 'google_drive_path_prefix',
+         'legacy_drive_path', 'legacy_drive_path_prefix'])
+
+    self.drive_path = drive_path.to_s.tr('\\', '/').strip.presence
+  end
+end

backend/app/models/material_version.rb

@@ -0,0 +1,17 @@
+class MaterialVersion < ApplicationRecord
+  EVENT_TYPE_MAP = { create: 'create',
+                     update: 'update',
+                     discard: 'discard',
+                     restore: 'restore' }.freeze
+
+  include VersionRecord
+
+  belongs_to :material
+  belongs_to :tag, optional: true
+  belongs_to :parent, class_name: 'Material', optional: true
+  belongs_to :updated_by_user, class_name: 'User', optional: true
+
+  def export_paths_hash
+    export_paths_json || {}
+  end
+end

backend/app/models/post.rb

@@ -1,37 +1,127 @@
 class Post < ApplicationRecord
   require 'mini_magick'
+  require 'stringio'
+
+  def self.resized_thumbnail_attachment(upload)
+    upload.rewind
+    image = MiniMagick::Image.read(upload.read)
+    image.resize '180x180'
+    image.format 'jpg'
+
+    { io: StringIO.new(image.to_blob),
+      filename: 'resized_thumbnail.jpg',
+      content_type: 'image/jpeg' }
+  ensure
+    upload.rewind
+  end
 
-  belongs_to :parent, class_name: 'Post', optional: true, foreign_key: 'parent_id'
   belongs_to :uploaded_user, class_name: 'User', optional: true
 
   has_many :post_tags, dependent: :destroy, inverse_of: :post
   has_many :active_post_tags, -> { kept }, class_name: 'PostTag', inverse_of: :post
   has_many :post_tags_with_discarded, -> { with_discarded }, class_name: 'PostTag'
   has_many :tags, through: :active_post_tags
+  has_many :active_tags, -> { where(tags: { deprecated_at: nil }) },
+           through: :active_post_tags, source: :tag
 
   has_many :user_post_views, dependent: :delete_all
   has_many :post_similarities, dependent: :delete_all
   has_many :post_versions
+  has_many :gekanator_guessed_games,
+           class_name: 'GekanatorGame',
+           foreign_key: :guessed_post_id,
+           dependent: :delete_all,
+           inverse_of: :guessed_post
+  has_many :gekanator_correct_games,
+           class_name: 'GekanatorGame',
+           foreign_key: :correct_post_id,
+           dependent: :delete_all,
+           inverse_of: :correct_post
+  has_many :gekanator_question_examples, dependent: :delete_all
+
+  has_many :parent_post_implications,
+           class_name: 'PostImplication',
+           foreign_key: :post_id,
+           dependent: :destroy,
+           inverse_of: :post
+  has_many :parents, through: :parent_post_implications, source: :parent_post
+
+  has_many :child_post_implications,
+           class_name: 'PostImplication',
+           foreign_key: :parent_post_id,
+           dependent: :destroy,
+           inverse_of: :parent_post
+  has_many :children, through: :child_post_implications, source: :post
 
   has_one_attached :thumbnail
 
+  attribute :version_no, :integer, default: 1
+
   before_validation :normalise_url
 
   validates :url, presence: true, uniqueness: true
+  validates :video_ms, numericality: { only_integer: true, greater_than: 0 }, allow_nil: true
 
   validate :validate_original_created_range
   validate :url_must_be_http_url
 
+  def parent_posts = parents
+
+  def child_posts = children
+
+  def sibling_posts
+    parent_post_ids = parent_posts.order(:id).pluck(:id)
+
+    parent_post_ids.to_h { [_1, PostImplication.where(parent_post: _1).map(&:post)] }
+  end
+
   def as_json options = { }
-    super(options).merge({ thumbnail: thumbnail.attached? ?
-                                      Rails.application.routes.url_helpers.rails_blob_url(
-                                          thumbnail, only_path: false) :
-                                      nil })
+    super(options).merge(thumbnail: thumbnail.attached? ?
+                                    Rails.application.routes.url_helpers.rails_blob_url(
+                                        thumbnail, only_path: false) :
+                                    nil)
   rescue
     super(options).merge(thumbnail: nil)
   end
 
-  def snapshot_tag_names = tags.joins(:tag_name).order('tag_names.name').pluck('tag_names.name')
+  def snapshot_tag_names
+    post_tags
+        .kept
+        .joins(tag: :tag_name)
+        .includes(:sections, tag: :tag_name)
+        .order('tag_names.name')
+        .map do |post_tag|
+      name = post_tag.tag.tag_name.name
+      sections = post_tag.sections.sort_by(&:begin_ms)
+
+      next name if sections.empty?
+
+      "#{ name }#{ sections.map { Post.section_literal(_1) }.join }"
+    end
+  end
+
+  def self.section_literal section
+    "[#{ Post.ms_to_time(section.begin_ms) }-#{ section.end_ms ? Post.ms_to_time(section.end_ms) : '' }]"
+  end
+
+  def self.ms_to_time ms
+    total_s = ms / 1_000
+    s = total_s % 60
+    min = (total_s / 60) % 60
+    h = total_s / 3_600
+    remainder_ms = ms % 1_000
+
+    base =
+        if h.positive?
+          '%d:%02d:%02d' % [h, min, s]
+        else
+          '%d:%02d' % [min, s]
+        end
+
+    remainder_ms.positive? ? "#{ base }.#{ remainder_ms.to_s.rjust(3, '0') }" : base
+  end
+
+  def snapshot_parent_post_ids = parents.order(:id).pluck(:id)
 
   def related limit: nil
     ids = post_similarities.order(cos: :desc)
@@ -47,12 +137,7 @@ class Post < ApplicationRecord
   def resized_thumbnail!
     return unless thumbnail.attached?
 
-    image = MiniMagick::Image.read(thumbnail.download)
-    image.resize '180x180'
-    thumbnail.purge
-    thumbnail.attach(io:            File.open(image.path),
-                     filename:      'resized_thumbnail.jpg',
-                     content_type:  'image/jpeg')
+    thumbnail.attach(self.class.resized_thumbnail_attachment(StringIO.new(thumbnail.download)))
   end
 
   private
@@ -67,7 +152,7 @@ class Post < ApplicationRecord
     return if !(f) || !(b)
 
     if f >= b
-      errors.add :original_created_before, 'オリジナルの作成日時の順番がをかしぃです．'
+      errors.add :original_created_at, 'オリジナルの作成日時の順番がをかしぃです．'
     end
   end
 
@@ -88,15 +173,6 @@ class Post < ApplicationRecord
   def normalise_url
     return if url.blank?
 
-    self.url = url.strip
-
-    u = URI.parse(url)
-    return unless u in URI::HTTP
-
-    u.host = u.host.downcase if u.host
-    u.path = u.path.sub(/\/\Z/, '') if u.path.present?
-    self.url = u.to_s
-  rescue URI::InvalidURIError
-    ;
+    self.url = PostUrlNormaliser.normalise(url) || url.strip
   end
 end

backend/app/models/post_implication.rb

@@ -0,0 +1,19 @@
+class PostImplication < ApplicationRecord
+  self.primary_key = :post_id, :parent_post_id
+
+  belongs_to :post, inverse_of: :parent_post_implications
+  belongs_to :parent_post, class_name: 'Post', inverse_of: :child_post_implications
+
+  validates :post_id, presence: true, uniqueness: { scope: :parent_post_id }
+  validates :parent_post_id, presence: true
+
+  validate :parent_post_mustnt_be_itself
+
+  private
+
+  def parent_post_mustnt_be_itself
+    if parent_post_id == post_id
+      errors.add :parent_post_id, '親投稿に同じ投稿を設定することはできません．'
+    end
+  end
+end

backend/app/models/post_tag.rb

@@ -10,6 +10,12 @@ class PostTag < ApplicationRecord
   belongs_to :created_user, class_name: 'User', optional: true
   belongs_to :deleted_user, class_name: 'User', optional: true
 
+  has_many :sections, -> { order(:begin_ms) }, class_name: 'PostTagSection',
+                                               foreign_key: [:post_id, :tag_id],
+                                               primary_key: [:post_id, :tag_id],
+                                               dependent: :delete_all,
+                                               inverse_of: :post_tag
+
   validates :post_id, presence: true
   validates :tag_id, presence: true
   validates :post_id, uniqueness: {

backend/app/models/post_tag_section.rb

@@ -0,0 +1,20 @@
+class PostTagSection < ApplicationRecord
+  self.primary_key = :post_id, :tag_id, :begin_ms
+
+  belongs_to :post
+  belongs_to :tag
+
+  belongs_to :post_tag, -> { kept }, foreign_key: [:post_id, :tag_id],
+                                     primary_key: [:post_id, :tag_id],
+                                     inverse_of: :sections,
+                                     optional: true
+
+  validates :post_id, presence: true
+  validates :tag_id, presence: true
+
+  validates :begin_ms, presence: true,
+                       numericality: { only_integer: true, greater_than_or_equal_to: 0 }
+
+  validates :end_ms, numericality: { only_integer: true, greater_than: :begin_ms },
+                     allow_nil: true
+end

backend/app/models/post_version.rb

@@ -5,6 +5,7 @@ class PostVersion < ApplicationRecord
   belongs_to :parent, class_name: 'Post', optional: true
 
   validates :url, presence: true
+  validates :video_ms, numericality: { only_integer: true, greater_than: 0 }, allow_nil: true
 
   validate :validate_original_created_range
 

backend/app/models/setting.rb

@@ -1,7 +1,52 @@
 class Setting < ApplicationRecord
+  THEMES = ['system', 'light', 'dark'].freeze
+
+  # These remain in the typed settings schema to preserve existing backend
+  # work. The common `/users/settings` page currently surfaces only `theme`.
+  AUTO_FETCH_MODES = ['auto', 'manual', 'off'].freeze
+  WIKI_EDITOR_MODES = ['split', 'write', 'preview'].freeze
+
+  STRING_ATTRIBUTES = [
+    'theme',
+    'auto_fetch_title',
+    'auto_fetch_thumbnail',
+    'wiki_editor_mode',
+  ].freeze
+  INTEGER_ATTRIBUTES = [].freeze
+  BOOLEAN_ATTRIBUTES = [].freeze
+  EDITABLE_ATTRIBUTES =
+      (STRING_ATTRIBUTES + INTEGER_ATTRIBUTES + BOOLEAN_ATTRIBUTES).freeze
+  TYPE_BY_ATTRIBUTE = {
+    'theme' => :string,
+    'auto_fetch_title' => :string,
+    'auto_fetch_thumbnail' => :string,
+    'wiki_editor_mode' => :string,
+  }.freeze
+
   belongs_to :user
 
   validates :user_id, presence: true
-  validates :key, presence: true, length: { maximum: 255 }
-  validates :value, presence: true
+  validates :user_id, uniqueness: true
+
+  validates :theme, inclusion: { in: THEMES }
+  validates :auto_fetch_title, inclusion: { in: AUTO_FETCH_MODES }
+  validates :auto_fetch_thumbnail, inclusion: { in: AUTO_FETCH_MODES }
+  validates :wiki_editor_mode, inclusion: { in: WIKI_EDITOR_MODES }
+
+  def self.defaults
+    {
+      theme: 'system',
+      auto_fetch_title: 'manual',
+      auto_fetch_thumbnail: 'manual',
+      wiki_editor_mode: 'split',
+    }
+  end
+
+  def self.serializable_attributes
+    EDITABLE_ATTRIBUTES.map(&:to_sym)
+  end
+
+  def serializable_hash(options = nil)
+    super({ only: self.class.serializable_attributes }.merge(options || { }))
+  end
 end

backend/app/models/tag.rb

@@ -8,6 +8,25 @@ class Tag < ApplicationRecord
     ;
   end
 
+  class DeprecatedTagNormalisationError < ArgumentError
+    attr_reader :tag_names
+
+    def initialize tag_names
+      @tag_names = Array(tag_names)
+      super('deprecated tags are not allowed')
+    end
+  end
+
+  class SectionLiteralParseError < ArgumentError
+    attr_reader :tag_name, :literal
+
+    def initialize tag_name, literal
+      @tag_name = tag_name
+      @literal = literal
+      super("invalid section literal for tag #{ tag_name }: #{ literal }")
+    end
+  end
+
   has_many :post_tags, inverse_of: :tag
   has_many :active_post_tags, -> { kept }, class_name: 'PostTag', inverse_of: :tag
   has_many :post_tags_with_discarded, -> { with_discarded }, class_name: 'PostTag'
@@ -40,6 +59,8 @@ class Tag < ApplicationRecord
   belongs_to :tag_name
   delegate :wiki_page, to: :tag_name
 
+  attribute :version_no, :integer, default: 1
+
   delegate :name, to: :tag_name, allow_nil: true
   validates :tag_name, presence: true
 
@@ -56,6 +77,7 @@ class Tag < ApplicationRecord
   validate :nico_tag_name_must_start_with_nico
   validate :tag_name_must_be_canonical
   validate :category_must_be_deerjikist_with_deerjikists
+  validate :nico_tags_cannot_be_deprecated
 
   scope :nico_tags, -> { nico }
 
@@ -75,34 +97,78 @@ class Tag < ApplicationRecord
     (self.tag_name ||= build_tag_name).name = val
   end
 
+  def deprecated? = deprecated_at?
+
   def has_wiki = wiki_page.present?
 
   def material_id = materials.first&.id
 
+  def has_deerjikists = deerjikists.loaded? ? deerjikists.any? : deerjikists.exists?
+
   def self.tagme = find_or_create_by_tag_name!('タグ希望', category: :meta)
   def self.bot = find_or_create_by_tag_name!('bot操作', category: :meta)
   def self.no_deerjikist = find_or_create_by_tag_name!('ニジラー情報不詳', category: :meta)
   def self.video = find_or_create_by_tag_name!('動画', category: :meta)
   def self.niconico = find_or_create_by_tag_name!('ニコニコ', category: :meta)
+  def self.youtube = find_or_create_by_tag_name!('YouTube', category: :meta)
 
-  def self.normalise_tags tag_names, with_tagme: true,
-                                     with_no_deerjikist: true,
-                                     deny_nico: true
+  def self.normalise_tags! tag_names, with_tagme: true,
+                                      with_no_deerjikist: true,
+                                      deny_nico: true,
+                                      deny_deprecated: false,
+                                      with_sections: false
     if deny_nico && tag_names.any? { |n| n.downcase.start_with?('nico:') }
       raise NicoTagNormalisationError
     end
 
+    sections = { }
     tags = tag_names.map do |name|
+      raw_name = name
       pf, cat = CATEGORY_PREFIXES.find { |p, _| name.downcase.start_with?(p) } || ['', nil]
-      name = TagName.canonicalise(name.sub(/\A#{ pf }/i, '')).first
+
+      name = name.sub(/\A#{ pf }/i, '')
+
+      sections_by_tag = []
+      while (match = name.match(/\A(\S*?)\[([^\[\]\s]*)-([^\[\]\s]*)\](\S*)\z/))
+        name = "#{ match[1] }#{ match[4] }"
+        next if match[2].empty? && match[3].empty?
+
+        sections_by_tag << normalise_section_range!(
+            begin_raw: match[2],
+            end_raw: match[3],
+            tag_name: name)
+      end
+
+      if name.include?('[') || name.include?(']')
+        raise SectionLiteralParseError.new(raw_name, raw_name)
+      end
+
+      name = TagName.canonicalise(name).first
+
       find_or_create_by_tag_name!(name, category: (cat || :general)).tap do |tag|
+        if deny_deprecated && tag.deprecated?
+          raise DeprecatedTagNormalisationError, [tag.name]
+        end
+
         tag.update!(category: cat) if cat && tag.category != cat
+        next if sections_by_tag.empty?
+
+        sections[tag.id] ||= []
+        sections[tag.id].concat(sections_by_tag)
+        sections[tag.id] = merge_section_ranges(sections[tag.id])
+        sections.delete(tag.id) if sections[tag.id] == [[0, nil]]
       end
     end
 
     tags << Tag.tagme if with_tagme && tags.size < 10 && tags.none?(Tag.tagme)
     tags << Tag.no_deerjikist if with_no_deerjikist && tags.all? { |t| !(t.deerjikist?) }
-    tags.uniq(&:id)
+    tags.uniq!(&:id)
+
+    if with_sections
+      { tags:, sections: }
+    else
+      tags
+    end
   end
 
   def self.expand_parent_tags tags
@@ -128,6 +194,45 @@ class Tag < ApplicationRecord
     (result + tags).uniq { |t| t.id }
   end
 
+  def self.normalise_section_range! begin_raw:, end_raw:, tag_name:
+    begin_ms = begin_raw.empty? ? 0 : time_to_ms!(begin_raw, tag_name:)
+    end_ms = end_raw.empty? ? nil : time_to_ms!(end_raw, tag_name:)
+
+    if end_ms
+      begin_ms, end_ms = end_ms, begin_ms if begin_ms > end_ms
+      end_ms = begin_ms + 1 if begin_ms == end_ms
+    end
+
+    [begin_ms, end_ms]
+  end
+
+  def self.merge_section_ranges ranges
+    sorted_ranges = ranges.sort_by { |begin_ms, end_ms| [begin_ms, end_ms || Float::INFINITY] }
+    merged = []
+
+    sorted_ranges.each do |begin_ms, end_ms|
+      if merged.empty?
+        merged << [begin_ms, end_ms]
+        next
+      end
+
+      last_begin_ms, last_end_ms = merged[-1]
+      if last_end_ms.nil? || begin_ms <= last_end_ms
+        merged[-1] = [last_begin_ms, merge_section_end(last_end_ms, end_ms)]
+      else
+        merged << [begin_ms, end_ms]
+      end
+    end
+
+    merged
+  end
+
+  def self.merge_section_end left_end_ms, right_end_ms
+    return nil if left_end_ms.nil? || right_end_ms.nil?
+
+    [left_end_ms, right_end_ms].max
+  end
+
   def self.find_or_create_by_tag_name! name, category:
     tn = TagName.find_undiscard_or_create_by!(name: name.to_s.strip)
     tn = tn.canonical if tn.canonical_id?
@@ -223,4 +328,36 @@ class Tag < ApplicationRecord
       errors.add :category, 'ニジラーと紐づいてゐるタグはニジラー・カテゴリである必要があります．'
     end
   end
+
+  def self.time_to_ms! str, tag_name:
+    match =
+        case str
+        when /\A(?<seconds>\d+)(?:\.(?<ms>\d{1,3}))?\z/
+          { hours: nil, minutes: nil, seconds: Regexp.last_match[:seconds],
+            ms: Regexp.last_match[:ms] }
+        when /\A(?<minutes>\d+):(?<seconds>[0-5]?\d)(?:\.(?<ms>\d{1,3}))?\z/
+          { hours: nil, minutes: Regexp.last_match[:minutes],
+            seconds: Regexp.last_match[:seconds],
+            ms: Regexp.last_match[:ms] }
+        when /\A(?<hours>\d+):(?<minutes>[0-5]?\d):(?<seconds>[0-5]?\d)(?:\.(?<ms>\d{1,3}))?\z/
+          { hours: Regexp.last_match[:hours],
+            minutes: Regexp.last_match[:minutes],
+            seconds: Regexp.last_match[:seconds],
+            ms: Regexp.last_match[:ms] }
+        end
+
+    raise SectionLiteralParseError.new(tag_name, str) unless match
+
+    total_s = match[:seconds].to_i
+    total_s += match[:minutes].to_i * 60 if match[:minutes]
+    total_s += match[:hours].to_i * 3_600 if match[:hours]
+
+    total_s * 1_000 + match[:ms].to_s.ljust(3, '0')[0, 3].to_i
+  end
+
+  def nico_tags_cannot_be_deprecated
+    if nico? && deprecated_at.present?
+      errors.add :deprecated_at, 'ニコタグは廃止できません．'
+    end
+  end
 end

backend/app/models/tag_implication.rb

@@ -6,6 +6,7 @@ class TagImplication < ApplicationRecord
   validates :parent_tag_id, presence: true
 
   validate :parent_tag_mustnt_be_itself
+  validate :parent_tag_mustnt_create_cycle
 
   private
 
@@ -14,4 +15,27 @@ class TagImplication < ApplicationRecord
       errors.add :parent_tag_id, '親タグは子タグと同一であってはなりません．'
     end
   end
+
+  def parent_tag_mustnt_create_cycle
+    return if tag_id.blank? || parent_tag_id.blank?
+    return if errors[:parent_tag_id].present?
+
+    seen = { }
+    stack = [parent_tag_id]
+
+    until stack.empty?
+      current_id = stack.pop
+      next if seen[current_id]
+
+      seen[current_id] = true
+
+      if current_id == tag_id
+        errors.add :parent_tag_id, '親タグに子孫タグを指定すると循環します．'
+        errors.add :base, 'タグの親子関係が循環します．'
+        return
+      end
+
+      stack.concat(TagImplication.where(tag_id: current_id).pluck(:parent_tag_id))
+    end
+  end
 end

backend/app/models/theatre.rb

@@ -7,6 +7,10 @@ class Theatre < ApplicationRecord
            class_name: 'TheatreWatchingUser', inverse_of: :theatre
   has_many :watching_users, through: :active_theatre_watching_users, source: :user
 
+  has_many :programmes, class_name: 'TheatreProgramme'
+  has_many :skip_votes, class_name: 'TheatreSkipVote', dependent: :delete_all
+  has_many :skip_events, class_name: 'TheatreSkipEvent', dependent: :delete_all
+
   belongs_to :host_user, class_name: 'User', optional: true
   belongs_to :current_post, class_name: 'Post', optional: true
   belongs_to :created_by_user, class_name: 'User'

backend/app/models/theatre_programme.rb

@@ -0,0 +1,6 @@
+class TheatreProgramme < ApplicationRecord
+  self.primary_key = :theatre_id, :position
+
+  belongs_to :theatre
+  belongs_to :post
+end

backend/app/models/theatre_skip_event.rb

@@ -0,0 +1,10 @@
+class TheatreSkipEvent < ApplicationRecord
+  belongs_to :theatre
+  belongs_to :post
+  belongs_to :skipped_by_user, class_name: 'User'
+
+  has_many :voters, class_name: 'TheatreSkipEventVoter', dependent: :delete_all
+  has_many :event_tags, class_name: 'TheatreSkipEventTag', dependent: :delete_all
+  has_many :users, through: :voters
+  has_many :tags, through: :event_tags
+end

backend/app/models/theatre_skip_event_tag.rb

@@ -0,0 +1,6 @@
+class TheatreSkipEventTag < ApplicationRecord
+  self.primary_key = :theatre_skip_event_id, :tag_id
+
+  belongs_to :theatre_skip_event
+  belongs_to :tag
+end

backend/app/models/theatre_skip_event_voter.rb

@@ -0,0 +1,6 @@
+class TheatreSkipEventVoter < ApplicationRecord
+  self.primary_key = :theatre_skip_event_id, :user_id
+
+  belongs_to :theatre_skip_event
+  belongs_to :user
+end

backend/app/models/theatre_skip_vote.rb

@@ -0,0 +1,7 @@
+class TheatreSkipVote < ApplicationRecord
+  self.primary_key = :theatre_id, :post_id, :user_id
+
+  belongs_to :theatre
+  belongs_to :post
+  belongs_to :user
+end

backend/app/models/user.rb

@@ -4,11 +4,11 @@ class User < ApplicationRecord
   validates :name, length: { maximum: 255 }
   validates :inheritance_code, presence: true, length: { maximum: 64 }
   validates :role, presence: true, inclusion: { in: roles.keys }
-  validates :banned, inclusion: { in: [true, false] }
 
   has_many :created_posts,
            class_name: 'Post', foreign_key: :uploaded_user_id, dependent: :nullify
-  has_many :settings
+  has_one :setting, dependent: :destroy
+  has_many :theme_slots, class_name: 'UserThemeSlot', dependent: :destroy
   has_many :user_ips, dependent: :destroy
   has_many :ip_addresses, through: :user_ips
   has_many :user_post_views, dependent: :destroy
@@ -19,5 +19,10 @@ class User < ApplicationRecord
            class_name: 'WikiPage', foreign_key: :updated_user_id, dependent: :nullify
 
   def viewed?(post) = user_post_views.exists?(post_id: post.id)
+
   def gte_member? = member? || admin?
+
+  def banned? = banned_at.present?
+  def ban! = banned? || update!(banned_at: Time.current)
+  def unban! = update!(banned_at: nil)
 end

backend/app/models/user_theme_slot.rb

@@ -0,0 +1,27 @@
+class UserThemeSlot < ApplicationRecord
+  BASE_THEMES = ['light', 'dark'].freeze
+  SLOT_NOS = [1, 2, 3].freeze
+
+  belongs_to :user
+
+  validates :user_id, presence: true
+  validates :base_theme, presence: true, inclusion: { in: BASE_THEMES }
+  validates :slot_no, presence: true, inclusion: { in: SLOT_NOS }
+  validates :tokens, presence: true
+  validates :user_id, uniqueness: { scope: [:base_theme, :slot_no] }
+
+  validate :tokens_must_be_object
+
+  def serializable_hash(options = nil)
+    hash = { only: [:base_theme, :slot_no, :tokens, :created_at, :updated_at] }
+    super(hash.merge(options || {}))
+  end
+
+  private
+
+  def tokens_must_be_object
+    return if tokens.is_a?(Hash)
+
+    errors.add(:tokens, 'は JSON object で指定してください．')
+  end
+end

backend/app/models/version_record.rb

@@ -1,15 +1,23 @@
 module VersionRecord
   extend ActiveSupport::Concern
 
+  DEFAULT_EVENT_TYPE_MAP = { create: 'create',
+                             update: 'update',
+                             discard: 'discard',
+                             restore: 'restore' }.freeze
+
   def readonly? = persisted?
 
   included do
+    event_type_map = if const_defined?(:EVENT_TYPE_MAP, false)
+                       const_get(:EVENT_TYPE_MAP)
+                     else
+                       DEFAULT_EVENT_TYPE_MAP
+                     end
+
     belongs_to :created_by_user, class_name: 'User', optional: true
 
-    enum :event_type, { create: 'create',
-                        update: 'update',
-                        discard: 'discard',
-                        restore: 'restore' }, prefix: true, validate: true
+    enum :event_type, event_type_map, prefix: true, validate: true
 
     validates :version_no, presence: true, numericality: { only_integer: true, greater_than: 0 }
     validates :event_type, presence: true

backend/app/models/wiki_page.rb

@@ -13,10 +13,16 @@ class WikiPage < ApplicationRecord
            foreign_key: :redirect_page_id,
            dependent: :nullify
 
+  has_many :wiki_versions
+
+  attribute :version_no, :integer, default: 1
+
   belongs_to :tag_name
   validates :tag_name, presence: true
+  validates :body, presence: true
 
   def title = tag_name.name
+  def deprecated_at = tag_name.tag&.deprecated_at
 
   def title= val
     (self.tag_name ||= build_tag_name).name = val
@@ -24,11 +30,6 @@ class WikiPage < ApplicationRecord
 
   def current_revision = wiki_revisions.order(id: :desc).first
 
-  def body
-    rev = current_revision
-    rev.body if rev&.content?
-  end
-
   def resolve_redirect limit: 10
     page = self
     visited = Set.new

backend/app/models/wiki_version.rb

@@ -0,0 +1,8 @@
+class WikiVersion < ApplicationRecord
+  include VersionRecord
+
+  belongs_to :wiki_page
+
+  validates :title, presence: true
+  validates :body, presence: true
+end

backend/app/representations/material_repr.rb

@@ -2,7 +2,9 @@
 
 
 module MaterialRepr
-  BASE = { only: [:id, :url, :created_at, :updated_at],
+  BASE = { only: [:id, :url, :version_no, :source_kind, :source_uri,
+                  :source_path, :source_file_id, :normalized_source_key,
+                  :created_at, :updated_at],
            methods: [:content_type],
            include: { tag: TagRepr::BASE,
                       created_by_user: UserRepr::BASE,
@@ -15,10 +17,93 @@ module MaterialRepr
         file: if material.file.attached?
                 Rails.application.routes.url_helpers.rails_storage_proxy_url(
                     material.file, host:)
-              end)
+              end,
+        thumbnail: thumbnail_url(material, host:),
+        thumbnail_fallback_text: thumbnail_fallback_text(material),
+        thumbnail_fallback_kind: thumbnail_fallback_kind(material),
+        media_kind: media_kind(material),
+        file_byte_size: material.file_byte_size,
+        export_paths: export_paths(material),
+        export_items: export_items(material))
   end
 
   def many materials, host:
     materials.map { |m| base(m, host:) }
   end
+
+  def list material, host:
+    { id: material.id,
+      version_no: material.version_no,
+      url: material.url,
+      source_kind: material.source_kind,
+      source_uri: material.source_uri,
+      source_path: material.source_path,
+      source_file_id: material.source_file_id,
+      normalized_source_key: material.normalized_source_key,
+      tag: compact_tag(material.tag),
+      thumbnail: thumbnail_url(material, host:),
+      thumbnail_fallback_text: thumbnail_fallback_text(material),
+      thumbnail_fallback_kind: thumbnail_fallback_kind(material),
+      media_kind: media_kind(material),
+      content_type: material.content_type,
+      file_byte_size: material.file_byte_size,
+      created_at: material.created_at,
+      updated_at: material.updated_at,
+      export_paths: export_paths(material),
+      export_items: export_items(material) }
+  end
+
+  def list_many materials, host:
+    materials.map { |m| list(m, host:) }
+  end
+
+  def export_paths material
+    material.material_export_items.each_with_object({ }) do |item, hash|
+      hash[item.profile] = item.enabled ? item.export_path : ''
+    end
+  end
+
+  def export_items material
+    material.material_export_items.map do |item|
+      { id: item.id,
+        profile: item.profile,
+        export_path: item.export_path,
+        enabled: item.enabled }
+    end
+  end
+
+  def thumbnail_url material, host:
+    return nil unless material.thumbnail.attached?
+
+    Rails.application.routes.url_helpers.rails_storage_proxy_url(
+        material.thumbnail, host:)
+  end
+
+  def thumbnail_fallback_text material
+    material.tag&.name || material.created_at&.strftime('%Y-%m-%d')
+  end
+
+  def thumbnail_fallback_kind material
+    material.tag.present? ? 'tag_name' : 'created_at'
+  end
+
+  def media_kind material
+    return 'url_only' unless material.file.attached?
+
+    content_type = material.file.blob.content_type.to_s
+    return 'image' if content_type.start_with?('image/')
+    return 'video' if content_type.start_with?('video/')
+    return 'audio' if content_type.start_with?('audio/')
+
+    'file_other'
+  end
+
+  def compact_tag tag
+    return nil unless tag
+
+    { id: tag.id,
+      name: tag.name,
+      category: tag.category,
+      deprecated_at: tag.deprecated_at }
+  end
 end

backend/app/representations/post_repr.rb

@@ -2,19 +2,74 @@
 
 
 module PostRepr
-  BASE = { include: { tags: TagRepr::BASE, uploaded_user: UserRepr::BASE } }.freeze
+  BASE_FIELDS = [
+      :id,
+      :version_no,
+      :url,
+      :title,
+      :thumbnail_base,
+      :video_ms,
+      :original_created_from,
+      :original_created_before,
+      :created_at,
+      :updated_at
+  ].freeze
 
   module_function
 
   def base post, current_user = nil
-    json = post.as_json(BASE)
-    return json.merge(viewed: false) unless current_user
+    json = common(post)
+    json['tags'] = tag_json(post)
+    json['uploaded_user'] = post.uploaded_user && UserRepr.base(post.uploaded_user)
+    json['viewed'] = current_user ? current_user.viewed?(post) : false
+    json
+  end
 
-    viewed = current_user.viewed?(post)
-    json.merge(viewed:)
+  def detail post, current_user = nil, parent_posts: [], child_posts: [],
+             sibling_posts: { }, related: []
+    base(post, current_user).merge(
+        'parent_posts' => cards(parent_posts),
+        'child_posts' => cards(child_posts),
+        'sibling_posts' => sibling_posts.transform_keys(&:to_s).transform_values { |posts|
+          cards(posts)
+        },
+        'related' => cards(related))
+  end
+
+  def card post
+    common(post).merge('parent_posts' => [], 'child_posts' => [])
+  end
+
+  def cards posts
+    posts.map { |post| card(post) }
   end
 
   def many posts, current_user = nil
     posts.map { |p| base(p, current_user) }
   end
+
+  def common post
+    BASE_FIELDS.to_h { |field| [field.to_s, post.public_send(field)] }
+               .merge('thumbnail' => thumbnail_url(post))
+  end
+
+  def tag_json post
+    post
+        .active_post_tags
+        .reject { _1.tag.deprecated? }
+        .sort_by { _1.tag.name }
+        .map { |post_tag|
+      TagRepr.inline(post_tag.tag).merge(
+          'children' => [],
+          'sections' => post_tag.sections.as_json(only: [:begin_ms, :end_ms]))
+    }
+  end
+
+  def thumbnail_url post
+    return nil unless post.thumbnail.attached?
+
+    Rails.application.routes.url_helpers.rails_blob_url(post.thumbnail, only_path: false)
+  rescue
+    nil
+  end
 end

backend/app/representations/tag_repr.rb

@@ -2,8 +2,8 @@
 
 
 module TagRepr
-  BASE = { only: [:id, :category, :post_count, :created_at, :updated_at],
-           methods: [:name, :has_wiki, :material_id] }.freeze
+  BASE = { only: [:id, :category, :post_count, :created_at, :updated_at, :deprecated_at],
+           methods: [:name, :has_wiki, :material_id, :has_deerjikists] }.freeze
 
   module_function
 
@@ -12,5 +12,9 @@ module TagRepr
                             parents: tag.parents.map { _1.as_json(BASE) })
   end
 
+  def inline tag
+    tag.as_json(BASE).merge(aliases: [], parents: [])
+  end
+
   def many(tags) = tags.map { |t| base(t) }
 end

backend/app/representations/wiki_page_repr.rb

@@ -2,7 +2,7 @@
 
 
 module WikiPageRepr
-  BASE = { methods: [:title] }.freeze
+  BASE = { methods: [:title, :deprecated_at] }.freeze
 
   module_function
 

backend/app/services/gekanator/ai_run_budget.rb

@@ -0,0 +1,22 @@
+module Gekanator
+  class AiRunBudget
+    MONTHLY_LIMIT_JPY = BigDecimal('450').freeze
+    MAX_RUN_ESTIMATED_COST_JPY = BigDecimal('5').freeze
+
+    def self.remaining_monthly_budget_jpy
+      MONTHLY_LIMIT_JPY - monthly_cost_jpy
+    end
+
+    def self.monthly_cost_jpy
+      GekanatorAiRun.this_month.sum(:estimated_cost_jpy)
+    end
+
+    def self.exceeded?
+      monthly_cost_jpy >= MONTHLY_LIMIT_JPY
+    end
+
+    def self.exceeded_after_next_run?
+      monthly_cost_jpy + MAX_RUN_ESTIMATED_COST_JPY >= MONTHLY_LIMIT_JPY
+    end
+  end
+end

backend/app/services/gekanator/question_suggestion_ai_converter.rb

@@ -0,0 +1,168 @@
+module Gekanator
+  class QuestionSuggestionAiConverter
+    # Temporary heuristic converter for #361.
+    # This creates pending ai_generated questions without external LLM calls;
+    # accepted questions are still distributed only after admin approval.
+    TITLE_LENGTH_RE = /\Aタイトルは\s*(\d+)\s*文字以上[？?]\z/
+    ORIGINAL_YEAR_RE = /\Aオリジナルの投稿年は\s*(\d{4})\s*年[？?]\z/
+    ORIGINAL_MONTH_RE = /\Aオリジナルの投稿月は\s*(\d{1,2})\s*月[？?]\z/
+    ORIGINAL_MONTH_DAY_RE = /\Aオリジナルの投稿日は\s*(\d{1,2})\s*月\s*(\d{1,2})\s*日[？?]\z/
+    TITLE_CONTAINS_RE = /\A題名に「(.+?)」が含まれる[？?]\z/
+    SOURCE_RE = /\A(.+?)\s+の投稿を思[ひい]浮かべて[ゐい]る[？?]\z/
+
+    def self.call(...) = new(...).call
+
+    def initialize suggestion:, user:
+      @suggestion = suggestion
+      @user = user
+    end
+
+    def call
+      suggestion.with_lock do
+        existing = existing_generated_question
+        return existing if existing
+
+        run = suggestion.gekanator_ai_runs.create!(
+            model: 'heuristic_converter_v1',
+            status: 'running',
+            input_tokens: 0,
+            output_tokens: 0,
+            estimated_cost_jpy: 0)
+
+        question_attributes = build_question
+        question =
+            question_attributes &&
+            GekanatorQuestion.create!(
+                **question_attributes,
+                source: 'ai_generated',
+                status: 'pending',
+                gekanator_question_suggestion: suggestion,
+                created_by: user)
+
+        run.update!(status: question ? 'succeeded' : 'failed')
+        question
+      end
+    rescue => error
+      run&.update!(status: 'failed') if run&.persisted? && run.status != 'failed'
+      raise error
+    end
+
+    private
+
+    attr_reader :suggestion, :user
+
+    def existing_generated_question
+      suggestion
+          .gekanator_questions
+          .where(source: 'ai_generated')
+          .order(id: :desc)
+          .first
+    end
+
+    def build_question
+      text = normalized_text
+      return nil if text.blank?
+
+      structured_question_for(text) || post_similarity_question_for(text)
+    end
+
+    def normalized_text
+      suggestion.question_text.to_s.gsub(/[[:space:]]+/, ' ').strip
+    end
+
+    def structured_question_for text
+      case text
+      when TITLE_LENGTH_RE
+        length = Regexp.last_match(1).to_i
+        return nil if length <= 0
+
+        {
+          text:,
+          kind: 'title',
+          condition: {
+            type: 'title-length-at-least',
+            length:
+          },
+          priority_weight: 0.95
+        }
+      when /\A題名に英数字が混じって[ゐい]る[？?]\z/
+        {
+          text: '題名に英数字が混じってゐる?',
+          kind: 'title',
+          condition: { type: 'title-has-ascii' },
+          priority_weight: 0.95
+        }
+      when ORIGINAL_YEAR_RE
+        year = Regexp.last_match(1).to_i
+        {
+          text:,
+          kind: 'original_date',
+          condition: { type: 'original-year', year: },
+          priority_weight: 0.95
+        }
+      when ORIGINAL_MONTH_RE
+        month = Regexp.last_match(1).to_i
+        return nil unless month.between?(1, 12)
+
+        {
+          text:,
+          kind: 'original_date',
+          condition: { type: 'original-month', month: },
+          priority_weight: 0.95
+        }
+      when ORIGINAL_MONTH_DAY_RE
+        month = Regexp.last_match(1).to_i
+        day = Regexp.last_match(2).to_i
+        return nil unless month.between?(1, 12) && day.between?(1, 31)
+
+        {
+          text:,
+          kind: 'original_date',
+          condition: {
+            type: 'original-month-day',
+            monthDay: "#{ month }-#{ day }"
+          },
+          priority_weight: 0.95
+        }
+      when TITLE_CONTAINS_RE
+        title_text = Regexp.last_match(1).to_s.strip
+        return nil if title_text.blank?
+
+        {
+          text: "題名に「#{ title_text }」が含まれる?",
+          kind: 'title',
+          condition: { type: 'title-contains', text: title_text },
+          priority_weight: 0.95
+        }
+      when SOURCE_RE
+        host = Regexp.last_match(1).to_s.strip
+        return nil if host.blank?
+
+        {
+          text:,
+          kind: 'source',
+          condition: { type: 'source', host: },
+          priority_weight: 0.95
+        }
+      else
+        nil
+      end
+    end
+
+    def post_similarity_question_for text
+      return nil if suggestion.answer == 'unknown'
+
+      {
+        text:,
+        kind: 'post_similarity',
+        condition: {
+          type: 'post-similarity',
+          postId: suggestion.gekanator_game.correct_post_id,
+          answer: suggestion.answer,
+          threshold: 0.65
+        },
+        priority_weight: 1.0
+      }
+    end
+  end
+end

backend/app/services/gekanator/question_suggestion_promoter.rb

@@ -0,0 +1,52 @@
+module Gekanator
+  class QuestionSuggestionPromoter
+    def self.call(...) = new(...).call
+
+    def initialize suggestion:, user:
+      @suggestion = suggestion
+      @user = user
+    end
+
+    def call
+      suggestion.with_lock do
+        return promoted_question if suggestion.processed?
+        return suggestion if suggestion.answer == 'unknown'
+
+        question = GekanatorQuestion.create!(
+            text: suggestion.question_text,
+            kind: 'post_similarity',
+            source: 'user_suggested',
+            status: 'accepted',
+            priority_weight: 1.2,
+            condition: {
+              type: 'post-similarity',
+              postId: suggestion.gekanator_game.correct_post_id,
+              answer: suggestion.answer,
+              threshold: 0.65
+            },
+            gekanator_question_suggestion: suggestion,
+            created_by: user)
+        example =
+            GekanatorQuestionExample.new(
+                gekanator_question: question,
+                post: suggestion.gekanator_game.correct_post,
+                user: user)
+        example.record_answer!(
+            answer: suggestion.answer,
+            source: 'initial_suggestion',
+            gekanator_game: suggestion.gekanator_game)
+        example.save!
+        suggestion.update!(processed: true)
+        question
+      end
+    end
+
+    private
+
+    attr_reader :suggestion, :user
+
+    def promoted_question
+      suggestion.gekanator_questions.order(id: :desc).first
+    end
+  end
+end

backend/app/services/google_drive/api_client.rb

@@ -0,0 +1,233 @@
+require 'json'
+require 'jwt'
+require 'net/http'
+require 'openssl'
+require 'tempfile'
+require 'uri'
+
+
+module GoogleDrive
+  class ApiClient
+    DRIVE_ENDPOINT = 'https://www.googleapis.com/drive/v3'
+    TOKEN_ENDPOINT = 'https://oauth2.googleapis.com/token'
+    FOLDER_MIME_TYPE = 'application/vnd.google-apps.folder'
+    NATIVE_FILE_MIME_TYPE_PREFIX = 'application/vnd.google-apps.'
+    DRIVE_SCOPE = 'https://www.googleapis.com/auth/drive.readonly'
+
+    def initialize service_account_email: ENV['GOOGLE_DRIVE_SERVICE_ACCOUNT_EMAIL'],
+                   private_key: ENV['GOOGLE_DRIVE_PRIVATE_KEY'],
+                   private_key_path: ENV['GOOGLE_DRIVE_PRIVATE_KEY_PATH'],
+                   subject: ENV['GOOGLE_DRIVE_SUBJECT']
+      @service_account_email = service_account_email.to_s
+      @private_key = load_private_key(private_key, private_key_path)
+      @subject = subject.to_s.presence
+      @access_token = nil
+    end
+
+    def list_material_files_under_folder folder_id
+      files = []
+      each_material_file_under_folder(folder_id) { |entry| files << entry }
+      files
+    end
+
+    def each_material_file_under_folder folder_id
+      return enum_for(__method__, folder_id) unless block_given?
+
+      walk_folder(folder_id, nil) do |entry, relative_path|
+        next if entry['mimeType'] == FOLDER_MIME_TYPE
+        next if native_file?(entry['mimeType'])
+
+        yield build_file_entry(entry, relative_path)
+      end
+    end
+
+    def fetch_material_file file_id
+      metadata = get_file(file_id)
+      return nil if native_file?(metadata['mimeType'])
+
+      build_file_entry(metadata, metadata['name'])
+    end
+
+    def download_to_tempfile file_id, filename:
+      tempfile = Tempfile.new(['material-sync', File.extname(filename.to_s)])
+      tempfile.binmode
+      request_binary("/files/#{ file_id }",
+                     { alt: 'media', supportsAllDrives: true }) do |chunk|
+        tempfile.write(chunk.b)
+      end
+      tempfile.rewind
+      tempfile
+    rescue StandardError
+      tempfile&.close!
+      raise
+    end
+
+    def extract_file_id value
+      raw = value.to_s.strip
+      return nil if raw.blank?
+      return raw unless raw.include?('/')
+
+      uri = URI.parse(raw)
+      return uri.query.to_s[%r{(?:^|&)id=([^&]+)}, 1] if uri.query.present?
+      return uri.path[%r{/folders/([^/]+)}, 1] if uri.path.include?('/folders/')
+
+      uri.path[%r{/d/([^/]+)}, 1]
+    rescue URI::InvalidURIError
+      nil
+    end
+
+    private
+
+    def walk_folder folder_id, prefix, &block
+      list_children(folder_id).each do |entry|
+        relative_path = MaterialSyncExportPath.build(prefix:,
+                                                     relative_path: entry['name'])
+        if entry['mimeType'] == FOLDER_MIME_TYPE
+          walk_folder(entry['id'], relative_path, &block)
+          next
+        end
+
+        block.call(entry, relative_path)
+      end
+    end
+
+    def build_file_entry entry, relative_path
+      { id: entry['id'],
+        name: entry['name'],
+        mime_type: entry['mimeType'],
+        relative_path: MaterialSyncExportPath.normalize_path(relative_path),
+        sha256_checksum: entry['sha256Checksum'],
+        web_view_link: entry['webViewLink'],
+        web_content_link: entry['webContentLink'] }
+    end
+
+    def list_children folder_id
+      files = []
+      page_token = nil
+
+      loop do
+        response =
+            request_json('/files', {
+                q: "'#{ folder_id }' in parents and trashed = false",
+                fields: 'nextPageToken,files(id,name,mimeType,sha256Checksum,' \
+                        'webViewLink,webContentLink)',
+                orderBy: 'folder,name',
+                pageSize: 1000,
+                supportsAllDrives: true,
+                includeItemsFromAllDrives: true,
+                pageToken: page_token }.compact)
+        files.concat(response.fetch('files'))
+        page_token = response['nextPageToken']
+        break if page_token.blank?
+      end
+
+      files
+    end
+
+    def get_file file_id
+      request_json("/files/#{ file_id }",
+                   fields: 'id,name,mimeType,sha256Checksum,webViewLink,webContentLink',
+                   supportsAllDrives: true)
+    end
+
+    def request_json path, params = {}
+      response = request(:get, path, params:)
+      unless response.is_a?(Net::HTTPSuccess)
+        raise "Google Drive API error: #{ response.code } #{ response.body }"
+      end
+
+      JSON.parse(response.body)
+    end
+
+    def request_binary path, params = {}
+      request(:get, path, params:) do |response|
+        unless response.is_a?(Net::HTTPSuccess)
+          raise "Google Drive download error: #{ response.code } #{ response.body }"
+        end
+
+        response.read_body do |chunk|
+          yield chunk.b
+        end
+      end
+    end
+
+    def request method, path, params: nil
+      uri = URI(DRIVE_ENDPOINT + path)
+      uri.query = URI.encode_www_form(params) if params.present?
+
+      klass =
+          case method
+          when :get
+            Net::HTTP::Get
+          else
+            raise ArgumentError, "Unsupported Google Drive request method: #{ method }"
+          end
+      request = klass.new(uri)
+      request['Authorization'] = "Bearer #{ access_token }"
+
+      Net::HTTP.start(uri.host,
+                      uri.port,
+                      use_ssl: true,
+                      open_timeout: 10,
+                      read_timeout: 60) do |http|
+        if block_given?
+          http.request(request) do |response|
+            return yield response
+          end
+        else
+          http.request(request)
+        end
+      end
+    end
+
+    def access_token
+      @access_token ||= fetch_access_token
+    end
+
+    def fetch_access_token
+      raise 'GOOGLE_DRIVE_SERVICE_ACCOUNT_EMAIL is required' if @service_account_email.blank?
+      raise 'Google Drive private key is required' if @private_key.blank?
+
+      payload = { iss: @service_account_email,
+                  scope: DRIVE_SCOPE,
+                  aud: TOKEN_ENDPOINT,
+                  exp: 1.hour.from_now.to_i,
+                  iat: Time.current.to_i }
+      payload[:sub] = @subject if @subject.present?
+
+      assertion = JWT.encode(payload, @private_key, 'RS256')
+      uri = URI(TOKEN_ENDPOINT)
+      request = Net::HTTP::Post.new(uri)
+      request.set_form_data(
+          grant_type: 'urn:ietf:params:oauth:grant-type:jwt-bearer',
+          assertion:)
+
+      response =
+          Net::HTTP.start(uri.host,
+                          uri.port,
+                          use_ssl: true,
+                          open_timeout: 10,
+                          read_timeout: 30) do |http|
+            http.request(request)
+          end
+      unless response.is_a?(Net::HTTPSuccess)
+        raise "Google OAuth error: #{ response.code } #{ response.body }"
+      end
+
+      JSON.parse(response.body).fetch('access_token')
+    end
+
+    def load_private_key private_key, private_key_path
+      raw = private_key.to_s
+      raw = raw.gsub('\n', "\n") if raw.present?
+      raw = File.read(private_key_path) if raw.blank? && private_key_path.present?
+      return nil if raw.blank?
+
+      OpenSSL::PKey::RSA.new(raw)
+    end
+
+    def native_file? mime_type
+      mime_type.to_s.start_with?(NATIVE_FILE_MIME_TYPE_PREFIX)
+    end
+  end
+end

backend/app/services/material_file_sha256.rb

@@ -0,0 +1,57 @@
+require 'digest'
+require 'json'
+
+class MaterialFileSha256
+  def self.blob_metadata blob
+    metadata = blob.metadata
+    return metadata if metadata.is_a?(Hash)
+    return { } if metadata.blank?
+
+    JSON.parse(metadata)
+  rescue JSON::ParserError
+    { }
+  end
+
+  def self.metadata_sha256 blob
+    blob_metadata(blob)['sha256'].presence
+  end
+
+  def self.assign_metadata_sha256! blob, sha256
+    return if sha256.blank?
+
+    metadata = blob_metadata(blob)
+    metadata['sha256'] = sha256
+    blob.metadata = metadata
+    blob.save! if blob.changed?
+  end
+
+  def self.from_blob blob, allow_download: false
+    sha256 = metadata_sha256(blob)
+    return sha256 if sha256.present?
+    return nil unless allow_download
+
+    begin
+      blob.open do |file|
+        sha256 = Digest::SHA256.file(file.path).hexdigest
+        assign_metadata_sha256!(blob, sha256)
+        sha256
+      end
+    rescue ActiveStorage::FileNotFoundError, ArgumentError => error
+      Rails.logger.warn(
+          "MaterialFileSha256.from_blob failed for blob_id=#{blob.id}: " \
+          "#{error.class}: #{error.message}",
+      )
+      nil
+    end
+  end
+
+  def self.from_upload upload
+    tempfile = upload&.tempfile
+    return nil unless tempfile
+
+    tempfile.rewind
+    Digest::SHA256.file(tempfile.path).hexdigest.tap do
+      tempfile.rewind
+    end
+  end
+end

backend/app/services/material_import_block_matcher.rb

@@ -0,0 +1,7 @@
+class MaterialImportBlockMatcher
+  def self.match_for_sha256 sha256
+    return nil if sha256.blank?
+
+    MaterialImportBlock.find_by(match_kind: 'sha256', sha256:)
+  end
+end

backend/app/services/material_sync_export_path.rb

@@ -0,0 +1,56 @@
+class MaterialSyncExportPath
+  class << self
+    def build prefix: nil, relative_path: nil, filename: nil, source_file_id: nil
+      path = normalize_path(relative_path)
+      path = fallback_filename(filename, source_file_id) if path.blank?
+
+      normalized_prefix = normalize_path(prefix)
+      return path if normalized_prefix.blank?
+      return normalized_prefix if path.blank?
+
+      [normalized_prefix, path].join('/')
+    end
+
+    def normalize_path path
+      raw = path.to_s.delete("\0").tr('\\', '/').strip
+      raw = raw.sub(%r{\A[A-Za-z]:/+}, '')
+      raw = raw.sub(%r{\A/+}, '')
+      return nil if raw.blank?
+
+      segments = raw.split('/').filter_map do |segment|
+        normalized = normalize_segment(segment)
+        normalized.presence
+      end
+      return nil if segments.empty?
+
+      segments.join('/')
+    end
+
+    def uniquify path, source_file_id
+      normalized = normalize_path(path)
+      return normalized if normalized.blank? || source_file_id.blank?
+
+      ext = File.extname(normalized)
+      base = ext.present? ? normalized.delete_suffix(ext) : normalized
+      "#{ base }--#{ source_file_id }#{ ext }"
+    end
+
+    private
+
+    def fallback_filename filename, source_file_id
+      name = normalize_path(filename)
+      return name if name.present?
+      return nil if source_file_id.blank?
+
+      source_file_id.to_s
+    end
+
+    def normalize_segment segment
+      cleaned = segment.to_s.delete("\0").tr('\\/', '_').strip
+      return nil if cleaned.blank? || cleaned == '.'
+      return '__' if cleaned == '..'
+
+      cleaned
+    end
+  end
+end

backend/app/services/material_sync_importer.rb

@@ -0,0 +1,259 @@
+require 'digest'
+
+class MaterialSyncImporter
+  Result = Struct.new(:material, :action, :suppressed, :suppression, keyword_init: true)
+
+  def self.import! attributes
+    new(attributes).import!
+  end
+
+  def initialize attributes
+    @attributes = attributes
+  end
+
+  def import!
+    source = source_attributes
+    key = normalized_source_key(source)
+    raise ArgumentError, 'normalized_source_key is required for material sync' if key.blank?
+
+    suppression = suppression_for(source)
+    if suppression
+      return Result.new(material: nil,
+                        action: :suppressed,
+                        suppressed: true,
+                        suppression:)
+    end
+
+    material =
+        Material
+        .unscoped
+        .find_or_initialize_by(normalized_source_key: key)
+    if unchanged?(material, source)
+      return Result.new(material:,
+                        action: :unchanged,
+                        suppressed: false,
+                        suppression: nil)
+    end
+
+    event_type =
+        if material.new_record?
+          :create
+        elsif material.discarded?
+          :restore
+        else
+          :update
+        end
+    uploaded_blob = uploaded_blob!
+    if @import_block
+      return Result.new(material: nil,
+                        action: :suppressed,
+                        suppressed: true,
+                        suppression: @import_block)
+    end
+
+    Material.transaction do
+      if material.persisted?
+        MaterialVersionRecorder.ensure_snapshot!(material,
+                                                 created_by_user: @attributes[:updated_by_user])
+      end
+      material.assign_attributes(material_attributes_for(material).merge(source))
+      material.discarded_at = nil if material.respond_to?(:discarded_at=)
+      material.file.attach(uploaded_blob) if uploaded_blob
+      material.save!
+      upsert_export_item!(material)
+      MaterialVersionRecorder.record!(material:,
+                                      event_type:,
+                                      created_by_user: @attributes[:updated_by_user])
+    end
+    MaterialThumbnailGenerator.generate!(material)
+
+    Result.new(material:,
+               action: event_type == :create ? :imported : :updated,
+               suppressed: false,
+               suppression: nil)
+  rescue StandardError
+    uploaded_blob&.purge_later
+    raise
+  ensure
+    close_tempfile!
+  end
+
+  private
+
+  def source_attributes
+    { source_kind: @attributes[:source_kind],
+      source_uri: @attributes[:source_uri],
+      source_path: @attributes[:source_path],
+      source_file_id: @attributes[:source_file_id] }
+  end
+
+  def normalized_source_key source
+    MaterialSyncSuppression.normalize_source_key(source_kind: source[:source_kind],
+                                                 source_uri: source[:source_uri],
+                                                 source_path: source[:source_path],
+                                                 source_file_id: source[:source_file_id])
+  end
+
+  def material_attributes_for material
+    attrs =
+        @attributes
+        .except(:source_kind, :source_uri, :source_path, :source_file_id,
+                :file_blob, :file_tempfile, :file_downloader,
+                :filename, :content_type,
+                :file_sha256, :export_path, :profile, :tag, :url)
+
+    if material.new_record?
+      attrs[:tag] = @attributes[:tag] if @attributes.key?(:tag)
+      attrs[:url] = @attributes[:url] if @attributes.key?(:url)
+      return attrs
+    end
+
+    if @attributes.key?(:tag) && @attributes[:tag].present?
+      attrs[:tag] = @attributes[:tag]
+    end
+    if @attributes.key?(:url) && @attributes[:url].present?
+      attrs[:url] = @attributes[:url]
+    end
+
+    attrs
+  end
+
+  def suppression_for source
+    if source[:source_kind] == 'google_drive_file' && source[:source_path].present?
+      return MaterialSyncSuppressionMatcher.match_google_drive_candidate(
+          drive_file_id: source[:source_file_id],
+          relative_path: source[:source_path])
+    end
+
+    MaterialSyncSuppressionMatcher.match(**source)
+  end
+
+  def uploaded_blob!
+    return @attributes[:file_blob] if @attributes[:file_blob]
+
+    tempfile = upload_tempfile
+    return nil unless tempfile
+
+    tempfile.rewind
+    file_sha256 = @attributes[:file_sha256] || Digest::SHA256.file(tempfile.path).hexdigest
+    @attributes[:file_sha256] = file_sha256
+
+    @import_block = MaterialImportBlockMatcher.match_for_sha256(file_sha256)
+    if @import_block
+      tempfile.rewind
+      return nil
+    end
+
+    blob = ActiveStorage::Blob.create_and_upload!(
+        io: tempfile,
+        filename: @attributes[:filename],
+        content_type: @attributes[:content_type])
+    MaterialFileSha256.assign_metadata_sha256!(blob, file_sha256)
+    tempfile.rewind
+    blob
+  end
+
+  def upload_tempfile
+    return @upload_tempfile if defined?(@upload_tempfile)
+
+    @upload_tempfile = @attributes[:file_tempfile]
+    if @upload_tempfile.blank? && @attributes[:file_downloader]
+      @upload_tempfile = @attributes[:file_downloader].call
+    end
+    @upload_tempfile
+  end
+
+  def upsert_export_item! material
+    export_path = resolved_export_path(material)
+    return if export_path.blank?
+
+    item = material.material_export_items.find_or_initialize_by(profile: effective_profile)
+    item.export_path = export_path
+    item.enabled = true
+    item.created_by_user ||= @attributes[:created_by_user]
+    item.save!
+  end
+
+  def export_path_taken_by_other? material, profile, export_path
+    MaterialExportItem
+      .where(profile:, export_path:)
+      .where.not(material_id: material.id)
+      .exists?
+  end
+
+  def close_tempfile!
+    tempfile = upload_tempfile
+    return unless tempfile
+
+    tempfile.close! unless tempfile.closed?
+  rescue StandardError
+    nil
+  end
+
+  def unchanged? material, source
+    return false if material.new_record? || material.discarded?
+    return false unless same_source_attributes?(material, source)
+    return false unless same_export_path?(material)
+    return false unless same_material_attributes?(material)
+
+    same_file_snapshot?(material)
+  end
+
+  def same_source_attributes? material, source
+    material.source_kind == source[:source_kind] \
+      && material.source_uri == source[:source_uri] \
+      && material.source_path == source[:source_path] \
+      && material.source_file_id == source[:source_file_id]
+  end
+
+  def same_export_path? material
+    expected = resolved_export_path(material)
+    current = material.material_export_items.find { |item| item.profile == effective_profile }
+
+    return current.blank? if expected.blank?
+
+    current&.enabled && current.export_path == expected
+  end
+
+  def same_material_attributes? material
+    same_tag_attribute?(material) && same_url_attribute?(material)
+  end
+
+  def same_file_snapshot? material
+    expected_sha256 = @attributes[:file_sha256].to_s.presence
+
+    if expected_sha256.present?
+      return false unless material.file.attached?
+
+      return MaterialFileSha256.metadata_sha256(material.file.blob) == expected_sha256
+    end
+
+    !material.file.attached?
+  end
+
+  def effective_profile
+    @attributes[:profile].presence || 'legacy_drive'
+  end
+
+  def resolved_export_path material
+    export_path = @attributes[:export_path].to_s.strip
+    return export_path if export_path.blank?
+    return export_path unless export_path_taken_by_other?(material, effective_profile, export_path)
+
+    MaterialSyncExportPath.uniquify(export_path, @attributes[:source_file_id])
+  end
+
+  def same_tag_attribute? material
+    return true unless @attributes.key?(:tag)
+    return true if @attributes[:tag].blank?
+
+    material.tag_id == @attributes[:tag].id
+  end
+
+  def same_url_attribute? material
+    return true unless @attributes.key?(:url)
+    return true if @attributes[:url].blank?
+
+    material.url == @attributes[:url]
+  end
+end

backend/app/services/material_sync_runner.rb

@@ -0,0 +1,251 @@
+class MaterialSyncRunner
+  Result = Struct.new(:imported, :updated, :unchanged, :suppressed, :failed,
+                      :errors, keyword_init: true)
+
+  def self.sync_enabled!
+    results = MaterialSyncSource.enabled.order(:id).map { |source| new(source).sync! }
+
+    Result.new(imported: results.sum(&:imported),
+               updated: results.sum(&:updated),
+               unchanged: results.sum(&:unchanged),
+               suppressed: results.sum(&:suppressed),
+               failed: results.sum(&:failed),
+               errors: results.flat_map(&:errors))
+  end
+
+  def initialize source
+    @source = source
+  end
+
+  def sync!
+    result = Result.new(imported: 0, updated: 0, unchanged: 0,
+                        suppressed: 0, failed: 0, errors: [])
+
+    if @source.source_kind == 'google_drive_path'
+      sync_google_drive_path!(result)
+    else
+      candidates.each do |candidate|
+        next if candidate.blank?
+
+        sync_candidate!(candidate, result)
+      end
+    end
+
+    @source.update!(last_synced_at: Time.current)
+    log_result(result)
+    result
+  rescue NotImplementedError, StandardError => e
+    result.failed += 1
+    result.errors << { source_id: @source.id, error: e.message }
+    log_result(result)
+    result
+  end
+
+  private
+
+  def candidates
+    case @source.source_kind
+    when 'uri'
+      [uri_candidate]
+    when 'google_drive_file'
+      [google_drive_file_candidate]
+    when 'legacy_drive_path'
+      raise NotImplementedError, 'legacy_drive_path material sync is not implemented'
+    else
+      raise NotImplementedError, "Unsupported material sync source_kind: #{ @source.source_kind }"
+    end
+  end
+
+  def uri_candidate
+    { source_kind: @source.source_kind,
+      source_uri: @source.source_uri,
+      source_path: @source.source_path,
+      source_file_id: @source.source_file_id,
+      url: @source.source_uri,
+      tag: nil,
+      created_by_user: @source.created_by_user,
+      updated_by_user: @source.updated_by_user || @source.created_by_user }
+  end
+
+  def sync_candidate! candidate, result
+    block = MaterialImportBlockMatcher.match_for_sha256(candidate[:file_sha256])
+    if block
+      result.suppressed += 1
+      Rails.logger.info(
+          material_sync_log(action: 'suppressed',
+                            reason: block.reason,
+                            normalized_source_key: candidate_normalized_source_key(candidate)))
+      return
+    end
+
+    suppression = suppression_for(candidate)
+
+    if suppression
+      result.suppressed += 1
+      Rails.logger.info(
+          material_sync_log(action: 'suppressed',
+                            normalized_source_key: suppression.normalized_source_key))
+      return
+    end
+
+    import = MaterialSyncImporter.import!(candidate)
+    result.public_send("#{ import.action }=", result.public_send(import.action) + 1)
+  rescue StandardError => e
+    result.failed += 1
+    result.errors << { source_id: @source.id,
+                       normalized_source_key: candidate_normalized_source_key(candidate),
+                       error: e.message }
+    Rails.logger.warn(
+        material_sync_log(action: 'failed',
+                          error: e.message,
+                          normalized_source_key: candidate_normalized_source_key(candidate)))
+  ensure
+    close_candidate_tempfile(candidate)
+  end
+
+  def google_drive_path_candidates
+    folder_id = google_drive_folder_id
+    Enumerator.new do |entries|
+      drive_client.each_material_file_under_folder(folder_id).each do |entry|
+        entries << build_google_drive_candidate(entry)
+      end
+    end
+  end
+
+  def sync_google_drive_path! result
+    folder_id = google_drive_folder_id
+    scanned_count = 0
+
+    drive_client.each_material_file_under_folder(folder_id) do |entry|
+      scanned_count += 1
+      sync_candidate!(build_google_drive_candidate(entry), result)
+      log_google_drive_progress(folder_id, scanned_count, result) if progress_log_scan_count?(scanned_count)
+    end
+
+    log_google_drive_progress(folder_id, scanned_count, result, summary: true)
+  end
+
+  def google_drive_file_candidate
+    entry = drive_client.fetch_material_file(google_drive_file_id)
+    return nil unless entry
+
+    build_google_drive_candidate(entry, single_file: true)
+  end
+
+  def build_google_drive_candidate entry, single_file: false
+    relative_path =
+        if single_file
+          nil
+        else
+          entry[:relative_path]
+        end
+    export_path =
+        MaterialSyncExportPath.build(prefix: @source.export_path_prefix,
+                                     relative_path:,
+                                     filename: entry[:name],
+                                     source_file_id: entry[:id])
+
+    { source_kind: 'google_drive_file',
+      source_uri: entry[:web_view_link] || google_drive_file_url(entry[:id]),
+      source_path: relative_path.presence || MaterialSyncExportPath.normalize_path(entry[:name]),
+      source_file_id: entry[:id],
+      filename: entry[:name],
+      content_type: entry[:mime_type],
+      file_downloader: lambda {
+        drive_client.download_to_tempfile(entry[:id], filename: entry[:name])
+      },
+      file_sha256: entry[:sha256_checksum],
+      export_path:,
+      profile: @source.profile,
+      tag: nil,
+      url: nil,
+      created_by_user: @source.created_by_user,
+      updated_by_user: @source.updated_by_user || @source.created_by_user }
+  end
+
+  def candidate_normalized_source_key candidate
+    MaterialSyncSuppression.normalize_source_key(source_kind: candidate[:source_kind],
+                                                 source_uri: candidate[:source_uri],
+                                                 source_path: candidate[:source_path],
+                                                 source_file_id: candidate[:source_file_id])
+  end
+
+  def suppression_for candidate
+    if candidate[:source_kind] == 'google_drive_file' && candidate[:source_path].present?
+      return MaterialSyncSuppressionMatcher.match_google_drive_candidate(
+          drive_file_id: candidate[:source_file_id],
+          relative_path: candidate[:source_path])
+    end
+
+    MaterialSyncSuppressionMatcher.match(source_kind: candidate[:source_kind],
+                                         source_uri: candidate[:source_uri],
+                                         source_path: candidate[:source_path],
+                                         source_file_id: candidate[:source_file_id])
+  end
+
+  def drive_client
+    @drive_client ||= GoogleDrive::ApiClient.new
+  end
+
+  def google_drive_folder_id
+    google_drive_target_id.tap do |id|
+      if id.blank?
+        raise ArgumentError, 'google_drive_path source_file_id or source_uri is required'
+      end
+    end
+  end
+
+  def google_drive_file_id
+    google_drive_target_id.tap do |id|
+      if id.blank?
+        raise ArgumentError, 'google_drive_file source_file_id or source_uri is required'
+      end
+    end
+  end
+
+  def google_drive_target_id
+    @source.source_file_id.presence || drive_client.extract_file_id(@source.source_uri)
+  end
+
+  def google_drive_file_url file_id
+    "https://drive.google.com/file/d/#{ file_id }/view"
+  end
+
+  def close_candidate_tempfile candidate
+    tempfile = candidate[:file_tempfile]
+    return unless tempfile
+
+    tempfile.close! unless tempfile.closed?
+  rescue StandardError
+    nil
+  end
+
+  def log_result result
+    Rails.logger.info(material_sync_log(imported: result.imported,
+                                        updated: result.updated,
+                                        unchanged: result.unchanged,
+                                        suppressed: result.suppressed,
+                                        failed: result.failed))
+  end
+
+  def progress_log_scan_count? scanned_count
+    scanned_count == 1 || (scanned_count % 50).zero?
+  end
+
+  def log_google_drive_progress folder_id, scanned_count, result, summary: false
+    Rails.logger.info(
+        material_sync_log(folder_id:,
+                          scanned_count:,
+                          imported: result.imported,
+                          updated: result.updated,
+                          unchanged: result.unchanged,
+                          suppressed: result.suppressed,
+                          failed: result.failed,
+                          progress: summary ? 'summary' : 'scan'))
+  end
+
+  def material_sync_log fields
+    { material_sync_source_id: @source.id,
+      material_sync_source_name: @source.name }.merge(fields).to_json
+  end
+end

backend/app/services/material_sync_suppression_matcher.rb

@@ -0,0 +1,68 @@
+class MaterialSyncSuppressionMatcher
+  def self.match source_kind:,
+                 source_uri: nil,
+                 drive_path: nil,
+                 drive_file_id: nil,
+                 source_path: nil,
+                 source_file_id: nil
+    key = MaterialSyncSuppression.normalize_source_key(source_kind:,
+                                                       source_uri:,
+                                                       drive_path:,
+                                                       drive_file_id:,
+                                                       source_path:,
+                                                       source_file_id:)
+    return nil if key.blank?
+
+    MaterialSyncSuppression.find_by(normalized_source_key: key)
+  end
+
+  def self.match_google_drive_candidate drive_file_id:, relative_path:
+    normalized_path = validate_relative_path!(relative_path)
+
+    exact = [
+      normalize_key('google_drive_file', drive_file_id),
+      normalize_key('google_drive_path', normalized_path),
+      normalize_key('legacy_drive_path', normalized_path),
+    ].compact
+
+    suppression =
+        MaterialSyncSuppression.find_by(normalized_source_key: exact)
+    return suppression if suppression
+
+    MaterialSyncSuppression
+      .where(source_kind: ['google_drive_path_prefix', 'legacy_drive_path_prefix'])
+      .where('drive_path = :path OR :path LIKE CONCAT(drive_path, "/%")', path: normalized_path)
+      .order(:id)
+      .first
+  end
+
+  def self.suppressed?(...)
+    match(...).present?
+  end
+
+  def self.validate_relative_path! relative_path
+    raw = relative_path.to_s.delete("\0").tr('\\', '/').strip
+    raise ArgumentError, 'relative_path is required' if raw.blank?
+    raise ArgumentError, 'relative_path must be relative' if raw.start_with?('/')
+    raise ArgumentError, 'relative_path must be relative' if raw.match?(/\A[A-Za-z]:\//)
+    if raw.start_with?('My Drive/', 'マイドライブ/')
+      raise ArgumentError, 'relative_path must be relative to source folder'
+    end
+    raise ArgumentError, 'relative_path must not contain //' if raw.include?('//')
+    raise ArgumentError, 'relative_path must not end with /' if raw.end_with?('/')
+
+    parts = raw.split('/')
+    if parts.any? { |part| part.in?(['.', '..']) }
+      raise ArgumentError, 'relative_path must not contain dot segments'
+    end
+
+    raw
+  end
+
+  def self.normalize_key source_kind, value
+    return nil if value.blank?
+
+    MaterialSyncSuppression.normalize_source_key(source_kind:, source_path: value,
+                                                 source_file_id: value)
+  end
+end

backend/app/services/material_sync_suppression_registrar.rb

@@ -0,0 +1,51 @@
+class MaterialSyncSuppressionRegistrar
+  def self.create! attributes, created_by_user:
+    new(attributes, created_by_user:).create!
+  end
+
+  def initialize attributes, created_by_user:
+    @attributes = attributes
+    @created_by_user = created_by_user
+  end
+
+  def create!
+    suppression = nil
+
+    MaterialSyncSuppression.transaction do
+      suppression = MaterialSyncSuppression.create!(
+          @attributes.merge(created_by_user: @created_by_user))
+      discard_existing_materials!(suppression)
+    end
+
+    suppression
+  end
+
+  private
+
+  def discard_existing_materials! suppression
+    matching_materials(suppression).find_each do |material|
+      MaterialVersionRecorder.ensure_snapshot!(material, created_by_user: @created_by_user)
+      material.discard!
+      MaterialVersionRecorder.record!(material:,
+                                      event_type: :discard,
+                                      created_by_user: @created_by_user)
+    end
+  end
+
+  def matching_materials suppression
+    materials = Material.unscoped.kept
+
+    case suppression.source_kind
+    when 'google_drive_path', 'legacy_drive_path'
+      materials.where(source_path: suppression.drive_path)
+    when 'google_drive_path_prefix', 'legacy_drive_path_prefix'
+      path = suppression.drive_path.to_s
+      materials.where(
+          'source_path = :path OR source_path LIKE :prefix',
+          path:,
+          prefix: "#{ path }/%")
+    else
+      materials.where(normalized_source_key: suppression.normalized_source_key)
+    end
+  end
+end

backend/app/services/material_thumbnail_generator.rb

@@ -0,0 +1,126 @@
+# frozen_string_literal: true
+
+require 'mini_magick'
+require 'open3'
+require 'tempfile'
+
+
+class MaterialThumbnailGenerator
+  SIZE = '180x180'
+
+  class << self
+    def generate! material
+      new(material).generate!
+    end
+  end
+
+  def initialize material
+    @material = material
+    @ffmpeg_stderr = []
+  end
+
+  def generate!
+    return log_result(:no_file) unless @material.file.attached?
+    return log_result(:unsupported_content_type) unless image? || video?
+
+    @material.file.blob.open do |file|
+      thumbnail = image? ? image_thumbnail(file.path) : video_thumbnail(file.path)
+      return log_result(:generation_failed) unless thumbnail
+
+      return attach_thumbnail(thumbnail)
+    end
+  rescue ActiveStorage::FileNotFoundError => e
+    log_result(:file_not_found, error: e)
+  rescue MiniMagick::Error => e
+    log_result(:mini_magick_error, error: e)
+  rescue ArgumentError, StandardError => e
+    log_result(:generation_failed, error: e)
+  end
+
+  private
+
+  def image? = content_type.start_with?('image/')
+
+  def video? = content_type.start_with?('video/')
+
+  def content_type
+    return nil unless @material.file.attached?
+
+    @material.file.blob.content_type.to_s
+  end
+
+  def image_thumbnail path
+    image = MiniMagick::Image.open(path)
+    image.resize(SIZE)
+    image.format('jpg')
+    image
+  end
+
+  def video_thumbnail path
+    [1, 0].each do |seconds|
+      tempfile = Tempfile.new(['material-thumbnail', '.jpg'])
+      tempfile.close
+      ok = extract_video_frame(path, tempfile.path, seconds)
+      next unless ok && File.size?(tempfile.path)
+
+      return image_thumbnail(tempfile.path)
+    ensure
+      tempfile&.unlink
+    end
+
+    nil
+  end
+
+  def extract_video_frame input_path, output_path, seconds
+    _stdout, stderr, status =
+        Open3.capture3('ffmpeg',
+                       '-y',
+                       '-ss', seconds.to_s,
+                       '-i', input_path,
+                       '-frames:v', '1',
+                       '-f', 'image2',
+                       output_path)
+    @ffmpeg_stderr << stderr if stderr.present?
+    status.success?
+  rescue Errno::ENOENT => e
+    @ffmpeg_stderr << "ffmpeg unavailable: #{ e.message }"
+    false
+  end
+
+  def attach_thumbnail image
+    blob = nil
+    File.open(image.path) do |io|
+      blob = ActiveStorage::Blob.create_and_upload!(
+          io:,
+          filename: 'material-thumbnail.jpg',
+          content_type: 'image/jpeg')
+    end
+    @material.thumbnail.attach(blob)
+    log_result(:attached)
+  rescue StandardError => e
+    blob&.purge_later
+    log_result(:attach_failed, error: e)
+  end
+
+  def log_result result, error: nil
+    log_payload = { material_id: @material.id,
+                    file_blob_id: file_blob_id,
+                    content_type:,
+                    result:,
+                    error_class: error&.class&.name,
+                    error_message: error&.message,
+                    ffmpeg_stderr: @ffmpeg_stderr.join("\n").presence }
+    if [:attached, :no_file, :unsupported_content_type].include?(result)
+      Rails.logger.info("Material thumbnail generation: #{ log_payload.to_json }")
+    else
+      Rails.logger.warn("Material thumbnail generation: #{ log_payload.to_json }")
+    end
+    result
+  end
+
+  def file_blob_id
+    return nil unless @material.file.attached?
+
+    @material.file.blob.id
+  end
+end

backend/app/services/material_version_recorder.rb

@@ -0,0 +1,73 @@
+class MaterialVersionRecorder < VersionRecorder
+  EVENT_TYPES = ['create', 'update', 'discard', 'restore'].freeze
+
+  def self.record! material:, event_type:, created_by_user:, file_snapshot: nil
+    new(material:, event_type:, created_by_user:, file_snapshot:).record!
+  end
+
+  def initialize material:, event_type:, created_by_user:, file_snapshot: nil
+    @file_snapshot = file_snapshot
+
+    super(record: material, event_type:, created_by_user:)
+  end
+
+  def self.ensure_snapshot! material, created_by_user:
+    return if material.material_versions.exists?
+
+    record!(material:, event_type: :create,
+            created_by_user: material.created_by_user || created_by_user)
+  end
+
+  private
+
+  def version_class = MaterialVersion
+  def version_association = :material_versions
+  def record_key = :material
+
+  def snapshot_attributes
+    blob = @record.file.attached? ? @record.file.blob : nil
+    file_snapshot = build_file_snapshot(blob)
+
+    { url: @record.url,
+      parent: @record.parent,
+      tag: @record.tag,
+      tag_name: @record.tag&.name,
+      tag_category: @record.tag&.category,
+      source_kind: @record.source_kind,
+      source_uri: @record.source_uri,
+      source_path: @record.source_path,
+      source_file_id: @record.source_file_id,
+      normalized_source_key: @record.normalized_source_key,
+      export_paths_json: @record.snapshot_export_paths,
+      discarded_at: @record.discarded_at,
+      file_blob_id: file_snapshot[:file_blob_id],
+      file_filename: file_snapshot[:file_filename],
+      file_content_type: file_snapshot[:file_content_type],
+      file_byte_size: file_snapshot[:file_byte_size],
+      file_checksum: file_snapshot[:file_checksum],
+      file_sha256: file_snapshot[:file_sha256] }
+  end
+
+  def build_file_snapshot blob
+    return @file_snapshot if @file_snapshot
+    return empty_file_snapshot unless blob
+
+    { file_blob_id: blob.id,
+      file_filename: blob.filename.to_s,
+      file_content_type: blob.content_type,
+      file_byte_size: blob.byte_size,
+      file_checksum: blob.checksum,
+      file_sha256: MaterialFileSha256.metadata_sha256(blob) }
+  end
+
+  def empty_file_snapshot
+    { file_blob_id: nil,
+      file_filename: nil,
+      file_content_type: nil,
+      file_byte_size: nil,
+      file_checksum: nil,
+      file_sha256: nil }
+  end
+
+  def event_types = self.class::EVENT_TYPES
+end

backend/app/services/material_zip_exporter.rb

@@ -0,0 +1,148 @@
+require 'stringio'
+require 'zlib'
+
+# Initial implementation keeps every file payload and the final ZIP in memory.
+# Keep this service boundary stable so job/cached export paths can replace it later.
+class MaterialZipExporter
+  Entry = Struct.new(:path, :data, :mtime, keyword_init: true)
+  MissingFile = Struct.new(:material_id, :export_path, :blob_id, :filename, keyword_init: true)
+
+  class EmptyExportError < StandardError; end
+  class DuplicatePathError < StandardError; end
+  class MissingFileError < StandardError
+    attr_reader :missing_files
+
+    def initialize missing_files
+      @missing_files = missing_files
+
+      super("Missing files: #{missing_files.map(&:export_path).join(', ')}")
+    end
+  end
+
+  def initialize profile: 'legacy_drive', tag_id: nil
+    @profile = profile.presence || 'legacy_drive'
+    @tag_id = tag_id.presence
+  end
+
+  def export
+    entries = build_entries
+    raise EmptyExportError if entries.empty?
+
+    ZipWriter.write(entries)
+  end
+
+  private
+
+  def build_entries
+    rows = MaterialExportItem
+           .enabled
+           .includes(material: { file_attachment: :blob })
+           .joins(:material)
+           .merge(Material.kept)
+           .where(profile: @profile)
+           .order(:export_path)
+
+    rows = rows.where(materials: { tag_id: @tag_id }) if @tag_id
+
+    missing_files = []
+
+    entries = rows.filter_map do |item|
+      material = item.material
+      next unless material.file.attached?
+
+      data = download_blob(item, missing_files)
+      next unless data
+
+      Entry.new(path: item.export_path,
+                data:,
+                mtime: material.updated_at || Time.current)
+    end
+
+    raise MissingFileError.new(missing_files) if missing_files.any?
+
+    paths = entries.map(&:path)
+    duplicated = paths.find { |path| paths.count(path) > 1 }
+    raise DuplicatePathError, duplicated if duplicated
+
+    entries
+  end
+
+  def download_blob item, missing_files
+    blob = item.material.file.blob
+
+    blob.download
+  rescue ActiveStorage::FileNotFoundError
+    missing_files << MissingFile.new(
+        material_id: item.material_id,
+        export_path: item.export_path,
+        blob_id: blob.id,
+        filename: blob.filename.to_s,
+    )
+    nil
+  end
+
+  class ZipWriter
+    VERSION_NEEDED = 20
+    GP_FLAG = 0x0800
+    COMPRESSION_STORE = 0
+
+    def self.write entries
+      new(entries).write
+    end
+
+    def initialize entries
+      @entries = entries
+      @central_directory = []
+    end
+
+    def write
+      io = StringIO.new(''.b)
+
+      @entries.each do |entry|
+        write_entry(io, entry)
+      end
+
+      central_start = io.pos
+      @central_directory.each { |header| io.write(header) }
+      central_size = io.pos - central_start
+
+      io.write([0x06054b50, 0, 0, @entries.size, @entries.size,
+                central_size, central_start, 0].pack('VvvvvVVv'))
+      io.string
+    end
+
+    private
+
+    def write_entry io, entry
+      path = entry.path.b
+      data = entry.data.b
+      crc32 = Zlib.crc32(data)
+      dos_time, dos_date = dos_timestamp(entry.mtime)
+      offset = io.pos
+
+      local_header = [0x04034b50, VERSION_NEEDED, GP_FLAG, COMPRESSION_STORE,
+                      dos_time, dos_date, crc32, data.bytesize, data.bytesize,
+                      path.bytesize, 0].pack('VvvvvvVVVvv')
+
+      io.write(local_header)
+      io.write(path)
+      io.write(data)
+
+      @central_directory << central_header(path:, crc32:, size: data.bytesize,
+                                           dos_time:, dos_date:, offset:)
+    end
+
+    def central_header path:, crc32:, size:, dos_time:, dos_date:, offset:
+      [0x02014b50, VERSION_NEEDED, VERSION_NEEDED, GP_FLAG, COMPRESSION_STORE,
+       dos_time, dos_date, crc32, size, size, path.bytesize, 0, 0, 0, 0, 0,
+       offset].pack('VvvvvvvVVVvvvvvVV') + path
+    end
+
+    def dos_timestamp time
+      local = time.to_time
+      dos_time = (local.hour << 11) | (local.min << 5) | (local.sec / 2)
+      dos_date = ((local.year - 1980) << 9) | (local.month << 5) | local.day
+      [dos_time, dos_date]
+    end
+  end
+end

backend/app/services/post_creator.rb

@@ -0,0 +1,118 @@
+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 do |begin_ms, end_ms|
+        PostTagSection.create!(post_id: post.id,
+                               tag_id:,
+                               begin_ms:,
+                               end_ms:)
+      end
+    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_previewer.rb

@@ -0,0 +1,226 @@
+class PostImportPreviewer
+  FIELDS = [
+    'title',
+    'thumbnail_base',
+    'original_created_from',
+    'original_created_before',
+    'duration',
+    'tags',
+    'parent_post_ids',
+  ].freeze
+  def initialize existing: 'skip'
+    @existing = existing == 'error' ? 'error' : 'skip'
+  end
+
+  def preview_rows rows:, fetch_metadata: true, metadata_cache: { }
+    urls = {}
+    rows.map { |row|
+      preview_row(row, urls:, fetch_metadata:, metadata_cache:)
+    }
+  end
+
+  def normalised_url value
+    PostUrlNormaliser.normalise(value)
+  end
+
+  private
+
+  def preview_row row, urls:, fetch_metadata:, metadata_cache:
+    row = row.symbolize_keys
+    attributes = initial_attributes(row)
+    provenance = initial_provenance(row)
+    tag_sources = initial_tag_sources(row, attributes, provenance)
+    url = row[:url].to_s.strip
+    provenance['url'] = 'manual'
+    normal_url = normalised_url(url)
+    url_for_metadata = normal_url || url
+    existing_post = normal_url.present? && Post.exists?(url: normal_url)
+
+    validation_warnings = []
+    validation_errors = {}
+    validation_errors[:url] = ['URL が不正です．'] if normal_url.blank?
+    validation_errors[:url] = ['URL が重複しています．'] if normal_url.present? && urls.key?(normal_url)
+    urls[normal_url] = true if normal_url.present?
+    if normal_url.present? && existing_post
+      if @existing == 'error'
+        validation_errors[:url] = ['既存投稿です．']
+      else
+        validation_warnings << '既存投稿のためスキップします．'
+      end
+    end
+
+    if row[:metadata_url].present? && row[:metadata_url] != url_for_metadata
+      clear_automatic_values!(attributes, provenance, tag_sources)
+    end
+
+    fetch_warnings = Array(row[:fetch_warnings]).dup
+    should_fetch = should_fetch_metadata?(fetch_metadata, row[:source_row].to_i)
+    if should_fetch && !(existing_post && @existing == 'skip')
+      metadata, fetch_warnings =
+        metadata_for(url_for_metadata, metadata_cache, fetch_warnings)
+      apply_metadata!(attributes, provenance, tag_sources, metadata)
+    end
+
+    attributes['url'] = url
+    validate_basic_data(attributes, validation_errors)
+    validate_preview_tags(
+      merged_tags(tag_sources, provenance['tags']),
+      validation_errors,
+      validation_warnings,
+    )
+    validate_parents(attributes['parent_post_ids'], validation_errors)
+    attributes.delete('url')
+    attributes['tags'] = merged_tags(tag_sources, provenance['tags'])
+
+    {
+      source_row: row[:source_row],
+      url: normal_url || url,
+      attributes:,
+      provenance:,
+      tag_sources:,
+      metadata_url: url_for_metadata,
+      fetch_warnings:,
+      validation_warnings:,
+      warnings: fetch_warnings + validation_warnings,
+      validation_errors:,
+      status: validation_errors.present? \
+                ? 'error' \
+                : ((fetch_warnings + validation_warnings).present? ? 'warning' : 'ready'),
+      existing: existing_post,
+    }
+  end
+
+  def should_fetch_metadata? fetch_metadata, source_row
+    case fetch_metadata
+    when true then true
+    when Integer then fetch_metadata == source_row
+    when false, nil then false
+    else raise ArgumentError, 'メタデータ取得対象が不正です．'
+    end
+  end
+
+  def initial_attributes row
+    attributes = row[:attributes]&.stringify_keys || {}
+    FIELDS.to_h { |field| [field, attributes[field].to_s] }
+  end
+
+  def initial_provenance row
+    provenance = row[:provenance]&.stringify_keys || {}
+    FIELDS.to_h { |field| [field, provenance[field].presence || 'automatic'] }
+      .merge('url' => 'manual')
+  end
+
+  def initial_tag_sources row, attributes, provenance
+    sources = row[:tag_sources]&.stringify_keys || { 'automatic' => '', 'manual' => '' }
+    sources['automatic'] = sources['automatic'].to_s
+    sources['manual'] =
+      provenance['tags'] == 'manual' ? attributes['tags'].to_s : sources['manual'].to_s
+    sources
+  end
+
+  def clear_automatic_values! attributes, provenance, tag_sources
+    ['title', 'thumbnail_base', 'original_created_from',
+     'original_created_before', 'duration'].each do |field|
+      attributes[field] = '' if provenance[field] == 'automatic'
+    end
+    tag_sources['automatic'] = ''
+    attributes['tags'] = merged_tags(tag_sources, provenance['tags'])
+  end
+
+  def metadata_for url, cache, previous_warnings
+    cache[url] ||= fetch_metadata(url)
+  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
+    Rails.logger.info(
+      "post_import_metadata_fetch_failure "\
+      "#{ { error: e.class.name, message: e.message }.to_json }",
+    )
+    [{}, ['メタデータを取得できませんでした．']]
+  end
+
+  def apply_metadata! attributes, provenance, tag_sources, metadata
+    metadata.each do |field, value|
+      if field == 'tags'
+        next if provenance['tags'] == 'manual'
+
+        tag_sources['automatic'] = value.to_s
+        attributes['tags'] = merged_tags(tag_sources)
+        next
+      end
+      next unless provenance[field] == 'automatic' || attributes[field].blank?
+
+      attributes[field] = value
+      provenance[field] = 'automatic'
+    end
+  end
+
+  def merged_tags sources, origin = nil
+    return sources['manual'].to_s if origin == 'manual'
+
+    sources['automatic'].to_s
+  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 do |error|
+      next if error.attribute == :url && error.type == :taken
+
+      (errors[error.attribute] ||= []) << error.message
+    end
+  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 }
+    if Post.where(id: ids).count != ids.uniq.length
+      errors[:parent_post_ids] = ['存在しない親投稿 Id. があります．']
+    end
+  end
+end

backend/app/services/post_import_row_normaliser.rb

@@ -0,0 +1,162 @@
+class PostImportRowNormaliser
+  ORIGINS = ['automatic', 'manual'].freeze
+  STRING_FIELDS = [
+    'title',
+    'thumbnail_base',
+    'original_created_from',
+    'original_created_before',
+    'tags',
+    'parent_post_ids',
+  ].freeze
+  FLEXIBLE_FIELDS = ['duration', 'video_ms'].freeze
+  ATTRIBUTE_FIELDS = (STRING_FIELDS + FLEXIBLE_FIELDS).freeze
+
+  def self.normalise! rows, allow_warning_fields: false
+    raise ArgumentError, '取込行の形式が不正です．' unless rows.is_a?(Array)
+    raise ArgumentError, '取込件数が多すぎます．' if rows.length > PostImportUrlListParser::MAX_ROWS
+
+    normalised_rows = rows.map { normalise_row!(_1, allow_warning_fields:) }
+    source_rows = normalised_rows.map { _1['source_row'] }
+    raise ArgumentError, '元行番号が重複しています．' if source_rows.uniq.length != source_rows.length
+    if normalised_rows.sum { row_bytesize(_1) } > PostImportUrlListParser::MAX_BYTES
+      raise ArgumentError, '取込データが大きすぎます．'
+    end
+
+    normalised_rows
+  end
+
+  def self.normalise_row! row, allow_warning_fields:
+    unless row.is_a?(Hash) || row.is_a?(ActionController::Parameters)
+      raise ArgumentError, '取込行の形式が不正です．'
+    end
+
+    parameters =
+        row.is_a?(ActionController::Parameters) ? row : ActionController::Parameters.new(row)
+    permitted = parameters.permit(*permitted_keys(allow_warning_fields))
+    normalised = permitted.to_h.deep_transform_keys { _1.to_s.underscore }
+
+    normalised['source_row'] = normalise_source_row!(normalised['source_row'])
+    normalise_url!(normalised['url'])
+    normalise_metadata_url!(normalised['metadata_url'])
+    normalise_attributes!(normalised.fetch('attributes', { }))
+    normalise_provenance!(normalised.fetch('provenance', { }))
+    normalise_tag_sources!(normalised['tag_sources'])
+    normalise_warning_values!(normalised, allow_warning_fields:)
+    if row_bytesize(normalised) > PostImportUrlListParser::MAX_BYTES
+      raise ArgumentError, '取込行が大きすぎます．'
+    end
+
+    normalised
+  end
+
+  def self.permitted_keys allow_warning_fields
+    keys = [
+      :source_row,
+      :sourceRow,
+      :url,
+      :metadata_url,
+      :metadataUrl,
+      { attributes: {} },
+      { provenance: {} },
+      { tag_sources: {} },
+      { tagSources: {} },
+    ]
+    return keys unless allow_warning_fields
+
+    keys + [
+      { fetch_warnings: [] },
+      { fetchWarnings: [] },
+      { validation_warnings: [] },
+      { validationWarnings: [] },
+    ]
+  end
+  private_class_method :permitted_keys
+
+  def self.normalise_source_row! value
+    source_row = Integer(value, exception: false)
+    raise ArgumentError, '元行番号が不正です．' if source_row.nil? || source_row <= 0
+
+    source_row
+  end
+  private_class_method :normalise_source_row!
+
+  def self.normalise_url! value
+    raise ArgumentError, 'URL の形式が不正です．' unless value.is_a?(String)
+    raise ArgumentError, 'URL が長すぎます．' if value.bytesize > PostImportUrlListParser::MAX_URL_BYTES
+  end
+  private_class_method :normalise_url!
+
+  def self.normalise_metadata_url! value
+    raise ArgumentError, 'metadata_url の形式が不正です．' unless value.nil? || value.is_a?(String)
+    if value.to_s.bytesize > PostImportUrlListParser::MAX_URL_BYTES
+      raise ArgumentError, 'metadata_url が長すぎます．'
+    end
+  end
+  private_class_method :normalise_metadata_url!
+
+  def self.normalise_attributes! attributes
+    raise ArgumentError, 'attributes の形式が不正です．' unless attributes.is_a?(Hash)
+    raise ArgumentError, '取込項目が不正です．' unless (attributes.keys - ATTRIBUTE_FIELDS).empty?
+
+    attributes.each do |key, value|
+      case key
+      when *STRING_FIELDS
+        raise ArgumentError, '取込項目の型が不正です．' unless value.nil? || value.is_a?(String)
+      when *FLEXIBLE_FIELDS
+        unless value.nil? || value.is_a?(String) || value.is_a?(Numeric)
+          raise ArgumentError, '取込項目の型が不正です．'
+        end
+      end
+      if value.to_s.bytesize > PostImportUrlListParser::MAX_URL_BYTES
+        raise ArgumentError, '取込項目が大きすぎます．'
+      end
+    end
+  end
+  private_class_method :normalise_attributes!
+
+  def self.normalise_provenance! provenance
+    raise ArgumentError, 'provenance の形式が不正です．' unless provenance.is_a?(Hash)
+
+    allowed = ATTRIBUTE_FIELDS + ['url']
+    raise ArgumentError, '値の由来が不正です．' unless (provenance.keys - allowed).empty?
+    raise ArgumentError, '値の由来が不正です．' unless provenance.values.all? { ORIGINS.include?(_1) }
+  end
+  private_class_method :normalise_provenance!
+
+  def self.normalise_tag_sources! tag_sources
+    return if tag_sources.nil?
+
+    raise ArgumentError, 'タグ由来の形式が不正です．' unless tag_sources.is_a?(Hash)
+    raise ArgumentError, 'タグ由来の形式が不正です．' unless (tag_sources.keys - ORIGINS).empty?
+    raise ArgumentError, 'タグ由来の形式が不正です．' unless tag_sources.values.all? { _1.is_a?(String) }
+    if tag_sources.values.any? { _1.bytesize > PostImportUrlListParser::MAX_URL_BYTES }
+      raise ArgumentError, 'タグ由来が大きすぎます．'
+    end
+    if tag_sources.values.sum(&:bytesize) > PostImportUrlListParser::MAX_URL_BYTES
+      raise ArgumentError, 'タグ由来が大きすぎます．'
+    end
+  end
+  private_class_method :normalise_tag_sources!
+
+  def self.normalise_warning_values! normalised, allow_warning_fields:
+    return unless allow_warning_fields
+
+    %w[fetch_warnings validation_warnings].each do |field|
+      values = normalised[field]
+      next if values.nil?
+
+      unless values.is_a?(Array) && values.all? { _1.is_a?(String) }
+        raise ArgumentError, '警告の形式が不正です．'
+      end
+      if values.any? { _1.bytesize > PostImportUrlListParser::MAX_URL_BYTES }
+        raise ArgumentError, '警告が大きすぎます．'
+      end
+    end
+  end
+  private_class_method :normalise_warning_values!
+
+  def self.row_bytesize row
+    row.to_json.bytesize
+  end
+  private_class_method :row_bytesize
+end

backend/app/services/post_import_runner.rb

@@ -0,0 +1,64 @@
+class PostImportRunner
+  def initialize actor:, rows:, existing: 'skip'
+    @actor = actor
+    @rows = rows
+    @existing = existing == 'error' ? 'error' : 'skip'
+  end
+
+  def run
+    normalised_rows = PostImportRowNormaliser.normalise!(@rows)
+
+    results = normalised_rows.map { |row| run_row(row) }
+    {
+      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', { }).transform_keys { _1.to_s.underscore }
+    preview = PostImportPreviewer.new(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
+    if @existing == 'skip' && preview[:existing]
+      return row.slice('source_row').merge(status: 'skipped')
+    end
+
+    if preview[:validation_errors].present?
+      return {
+        source_row: row['source_row'],
+        status: 'failed',
+        errors: preview[:validation_errors],
+      }
+    end
+
+    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_url_list_parser.rb

@@ -0,0 +1,23 @@
+class PostImportUrlListParser
+  MAX_ROWS = 100
+  MAX_BYTES = 1.megabyte
+  MAX_URL_BYTES = 20.kilobytes
+
+  def self.parse source
+    raw = source.to_s
+    raise ArgumentError, '入力が大きすぎます．' if raw.bytesize > MAX_BYTES
+
+    rows = raw.split(/\r\n|\n|\r/).each_with_index.filter_map { |line, index|
+      url = line.strip
+      next if url.blank?
+
+      raise ArgumentError, 'URL が長すぎます．' if url.bytesize > MAX_URL_BYTES
+
+      { source_row: index + 1, url: }
+    }
+    raise ArgumentError, 'URL を入力してください．' if rows.empty?
+    raise ArgumentError, "取込件数は #{ MAX_ROWS } 件までです．" if rows.length > MAX_ROWS
+
+    rows
+  end
+end

backend/app/services/post_metadata_fetcher.rb

@@ -0,0 +1,60 @@
+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 = lambda { |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/
+        from + (10**(-Regexp.last_match(1).length))
+      else
+        return nil
+      end
+    [from, before]
+  rescue ArgumentError, TypeError
+    nil
+  end
+  private_class_method :platform_tags, :original_created_range
+end

backend/app/services/post_url_normaliser.rb

@@ -0,0 +1,13 @@
+class PostUrlNormaliser
+  def self.normalise raw_url
+    value = raw_url.to_s.strip
+    uri = URI.parse(value)
+    return nil unless uri.is_a?(URI::HTTP) && uri.host.present?
+
+    uri.host = uri.host.downcase
+    uri.path = uri.path.sub(/\/\z/, '') if uri.path.present?
+    uri.to_s
+  rescue URI::InvalidURIError
+    nil
+  end
+end

backend/app/services/post_version_recorder.rb

@@ -23,8 +23,9 @@ class PostVersionRecorder < VersionRecorder
     { title: @record.title,
       url: @record.url,
       thumbnail_base: @record.thumbnail_base,
+      video_ms: @record.video_ms,
       tags: @record.snapshot_tag_names.join(' '),
-      parent_id: @record.parent_id,
+      parent_post_ids: @record.snapshot_parent_post_ids.join(' '),
       original_created_from: @record.original_created_from,
       original_created_before: @record.original_created_before }
   end

backend/app/services/preview/html_metadata_extractor.rb

@@ -0,0 +1,21 @@
+module Preview
+  class HtmlMetadataExtractor
+    IMAGE_SELECTORS = [
+      'meta[property="og:image"]',
+      'meta[name="twitter:image"]',
+      'meta[name="thumbnail"]'
+    ].freeze
+
+    def self.extract(response)
+      document = Nokogiri::HTML.parse(response.body)
+      image_url = IMAGE_SELECTORS.filter_map {
+        document.at_css(_1)&.[]('content')&.strip.presence
+      }.first
+
+      { title: document.at_css('title')&.text&.strip,
+        image_url: image_url ? URI.join(response.url, image_url).to_s : nil }
+    rescue URI::InvalidURIError
+      { title: document.at_css('title')&.text&.strip, image_url: nil }
+    end
+  end
+end

backend/app/services/preview/http_fetcher.rb

@@ -0,0 +1,129 @@
+require 'net/http'
+require 'json'
+
+module Preview
+  class HttpFetcher
+    class FetchFailed < StandardError; end
+    class FetchTimeout < FetchFailed; end
+    class ResponseTooLarge < FetchFailed; end
+
+    MAX_REDIRECTS = 5
+    DEFAULT_MAX_BYTES = 5.megabytes
+
+    Response = Data.define(:body, :content_type, :url)
+
+    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)
+        location = response['location']
+        if redirects.zero?
+          log_failure(:redirect_limit,
+                      url: uri.to_s,
+                      redirects:,
+                      location:,
+                      content_type: response['content-type'],
+                      content_length: response['content-length'])
+          raise FetchFailed, 'redirect が多すぎます．'
+        end
+
+        if location.blank?
+          log_failure(:blank_redirect_location,
+                      url: uri.to_s,
+                      redirects:,
+                      content_type: response['content-type'],
+                      content_length: response['content-length'])
+          raise FetchFailed, 'redirect 先が不正です．'
+        end
+
+        redirect_url =
+            begin
+              URI.join(uri, location).to_s
+            rescue URI::InvalidURIError => e
+              log_failure(:invalid_redirect_location,
+                          url: uri.to_s,
+                          redirects:,
+                          location:,
+                          error: e.class.name,
+                          message: e.message)
+              raise FetchFailed, 'redirect 先が不正です．'
+            end
+
+        return fetch(redirect_url, max_bytes:, redirects: redirects - 1, allowed_hosts:)
+      end
+
+      unless response.is_a?(Net::HTTPSuccess)
+        log_failure(:http_status,
+                    url: uri.to_s,
+                    code: response.code,
+                    content_type: response['content-type'],
+                    content_length: response['content-length'])
+        raise FetchFailed, "外部サーバーが HTTP #{ response.code } を返しました．"
+      end
+
+      Response.new(response.body, response['content-type'].to_s, uri.to_s)
+    rescue Net::OpenTimeout, Net::ReadTimeout, Timeout::Error => e
+      log_failure(:timeout, url: uri&.to_s || raw_url, error: e.class.name, message: e.message)
+      raise FetchTimeout, e.message
+    rescue SocketError, SystemCallError, OpenSSL::SSL::SSLError, EOFError => e
+      log_failure(:network_error,
+                  url: uri&.to_s || raw_url,
+                  error: e.class.name,
+                  message: e.message)
+      raise FetchFailed, e.message
+    end
+
+    def self.request(uri, ip_address, max_bytes)
+      http = Net::HTTP.new(uri.host, uri.port)
+      http.ipaddr = ip_address
+      http.use_ssl = uri.scheme == 'https'
+      http.open_timeout = 5
+      http.read_timeout = 8
+      http.write_timeout = 5
+
+      request = Net::HTTP::Get.new(uri)
+      request['User-Agent'] = 'BTRC-Hub thumbnail preview'
+      request['Accept'] = 'text/html,image/*;q=0.9,*/*;q=0.1'
+
+      http.request(request) do |response|
+        length = response['content-length'].to_i
+        if length > max_bytes
+          log_failure(:response_too_large,
+                      url: uri.to_s,
+                      content_type: response['content-type'],
+                      content_length: response['content-length'],
+                      max_bytes:)
+          raise ResponseTooLarge, '外部データが大きすぎます．'
+        end
+
+        body = +''
+        response.read_body do |chunk|
+          body << chunk
+          next unless body.bytesize > max_bytes
+
+          log_failure(:response_too_large,
+                      url: uri.to_s,
+                      content_type: response['content-type'],
+                      content_length: response['content-length'],
+                      bytes_read: body.bytesize,
+                      max_bytes:)
+          raise ResponseTooLarge, '外部データが大きすぎます．'
+        end
+        response.instance_variable_set(:@body, body)
+        response.instance_variable_set(:@read, true)
+        return response
+      end
+    end
+
+    def self.log_failure(reason, **payload)
+      Rails.logger.warn("preview_http_fetcher_failure #{ { reason:, **payload }.to_json }")
+    end
+
+    private_class_method :request, :log_failure
+  end
+end

backend/app/services/preview/known_site_extractor.rb

@@ -0,0 +1,34 @@
+module Preview
+  class KnownSiteExtractor
+    def self.thumbnail_url(uri)
+      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'
+        uri.path[%r{\A/watch/(sm\d+)\z}, 1]
+      when 'nico.ms'
+        uri.path[%r{\A/(sm\d+)\z}, 1]
+      end
+    end
+
+    def self.youtube_thumbnail(uri)
+      id = youtube_video_id(uri)
+      return unless id
+
+      "https://i.ytimg.com/vi/#{ id }/hqdefault.jpg"
+    end
+
+    private_class_method :youtube_thumbnail
+  end
+end

backend/app/services/preview/thumbnail_fetcher.rb

@@ -0,0 +1,95 @@
+module Preview
+  class ThumbnailFetcher
+    class GenerationFailed < StandardError; end
+    ALLOWED_IMAGE_CONTENT_TYPES = [
+      'image/jpeg', 'image/png', 'image/gif', 'image/webp'
+    ].freeze
+    HTML_MAX_BYTES = 1.megabyte
+    NICONICO_XML_MAX_BYTES = 256.kilobytes
+
+    def self.fetch(raw_url)
+      uri, = UrlSafety.validate(raw_url)
+
+      known_url = KnownSiteExtractor.thumbnail_url(uri)
+      image = fetch_image_or_nil(known_url) if known_url
+      return image if image
+
+      niconico_url = niconico_thumbnail_url(uri)
+      image = fetch_image_or_nil(niconico_url) if niconico_url
+      return image if image
+
+      page = HttpFetcher.fetch(uri.to_s, max_bytes: HTML_MAX_BYTES)
+      metadata = HtmlMetadataExtractor.extract(page)
+      raise GenerationFailed, 'サムネール画像が見つかりませんでした．' if metadata[:image_url].blank?
+
+      fetch_image!(metadata[:image_url])
+    end
+
+    def self.title(raw_url)
+      uri, = UrlSafety.validate(raw_url)
+      HtmlMetadataExtractor.extract(
+          HttpFetcher.fetch(uri.to_s, max_bytes: HTML_MAX_BYTES))[:title]
+    end
+
+    def self.fetch_image_or_nil(url)
+      return nil if url.blank?
+
+      response = HttpFetcher.fetch(url)
+      return nil unless allowed_image_content_type?(response.content_type)
+
+      response.body
+    rescue HttpFetcher::FetchTimeout
+      raise
+    rescue HttpFetcher::FetchFailed
+      nil
+    end
+
+    def self.fetch_image!(url)
+      response = HttpFetcher.fetch(url)
+      unless allowed_image_content_type?(response.content_type)
+        raise GenerationFailed, 'サムネール画像が見つかりませんでした．'
+      end
+
+      response.body
+    rescue HttpFetcher::FetchTimeout
+      raise
+    rescue HttpFetcher::ResponseTooLarge
+      raise
+    rescue HttpFetcher::FetchFailed
+      raise GenerationFailed, 'サムネール画像を取得できませんでした．'
+    end
+
+    def self.niconico_thumbnail_url(uri)
+      video_id = KnownSiteExtractor.niconico_video_id(uri)
+      return nil if video_id.blank?
+
+      response = HttpFetcher.fetch("https://ext.nicovideo.jp/api/getthumbinfo/#{ video_id }",
+                                   max_bytes: NICONICO_XML_MAX_BYTES)
+      xml = Nokogiri::XML(response.body)
+      return nil unless xml.at_xpath('/nicovideo_thumb_response/@status')&.value == 'ok'
+
+      xml.at_xpath('//thumbnail_url')&.text&.strip.presence
+    rescue HttpFetcher::FetchFailed, HttpFetcher::FetchTimeout => e
+      Rails.logger.info("preview_niconico_getthumbinfo_fallback #{ { url: uri.to_s,
+                                                                     video_id:,
+                                                                     error: e.class.name,
+                                                                     message: e.message }.to_json }")
+      nil
+    rescue Nokogiri::XML::SyntaxError => e
+      Rails.logger.info("preview_niconico_getthumbinfo_fallback #{ { url: uri.to_s,
+                                                                     video_id:,
+                                                                     error: e.class.name,
+                                                                     message: e.message }.to_json }")
+      nil
+    end
+
+    def self.allowed_image_content_type?(content_type)
+      mime_type = content_type.to_s.split(';', 2).first.downcase.strip
+      ALLOWED_IMAGE_CONTENT_TYPES.include?(mime_type)
+    end
+
+    private_class_method :fetch_image_or_nil, :fetch_image!,
+                         :niconico_thumbnail_url,
+                         :allowed_image_content_type?
+  end
+end

backend/app/services/preview/url_safety.rb

@@ -0,0 +1,55 @@
+require 'resolv'
+require 'ipaddr'
+require 'uri'
+
+module Preview
+  class UrlSafety
+    class UnsafeUrl < StandardError; end
+
+    FORBIDDEN_NETWORKS = [
+      '0.0.0.0/8', '10.0.0.0/8', '100.64.0.0/10', '127.0.0.0/8',
+      '169.254.0.0/16', '172.16.0.0/12', '192.0.0.0/24',
+      '192.0.2.0/24', '192.168.0.0/16', '198.18.0.0/15',
+      '198.51.100.0/24', '203.0.113.0/24', '224.0.0.0/4',
+      '240.0.0.0/4', '::/128', '::1/128', 'fc00::/7', 'fe80::/10',
+      'ff00::/8', '2001:db8::/32', '::ffff:0:0/96'
+    ].map { IPAddr.new(_1) }.freeze
+
+    def self.validate(raw_url)
+      value = raw_url.to_s.strip
+      if value.match?(/\A[a-z][a-z0-9+\-.]*:/i)
+        unless value.match?(/\Ahttps?:\/\//i)
+          raise UnsafeUrl, 'http または https の URL を指定してください．'
+        end
+      else
+        value = "http://#{ value }"
+      end
+      uri = URI.parse(value)
+
+      unless ['http', 'https'].include?(uri.scheme&.downcase) && uri.host.present?
+        raise UnsafeUrl, 'http または https の URL を指定してください．'
+      end
+      raise UnsafeUrl, 'userinfo つき URL は使用できません．' if uri.userinfo.present?
+
+      addresses = Resolv.getaddresses(uri.host)
+      raise UnsafeUrl, 'URL のホストを解決できません．' if addresses.empty?
+
+      parsed_addresses = addresses.map { IPAddr.new(_1) }
+      if parsed_addresses.any? { |address| forbidden?(address) }
+        raise UnsafeUrl, '安全でない接続先は使用できません．'
+      end
+
+      [uri, parsed_addresses.map(&:to_s)]
+    rescue Resolv::ResolvError
+      raise UnsafeUrl, 'URL のホストを解決できません．'
+    rescue URI::InvalidURIError, IPAddr::InvalidAddressError
+      raise UnsafeUrl, 'URL が不正です．'
+    end
+
+    def self.forbidden?(address)
+      FORBIDDEN_NETWORKS.any? { _1.include?(address) }
+    end
+
+    private_class_method :forbidden?
+  end
+end

backend/app/services/similarity/calc.rb

@@ -1,6 +1,6 @@
 module Similarity
   class Calc
-    def self.call model, tgt
+    def self.call model, tgt, scope: nil
       similarity_model = "#{ model.name }Similarity".constantize
 
       # 最大保存件数
@@ -8,7 +8,8 @@ module Similarity
 
       similarity_model.delete_all
 
-      posts = model.includes(tgt).select(:id).to_a
+      scope ||= model.all
+      posts = scope.includes(tgt).select(:id).to_a
 
       tag_ids = { }
       tag_cnts = { }

backend/app/services/tag_version_recorder.rb

@@ -16,6 +16,7 @@ class TagVersionRecorder < VersionRecorder
   def snapshot_attributes
     { name: @record.name,
       category: @record.category,
+      deprecated_at: @record.deprecated_at,
       aliases: @record.snapshot_aliases.join(' '),
       parent_tag_ids: @record.snapshot_parent_tag_ids.join(' ') }
   end

backend/app/services/theatre_post_advancer.rb

@@ -0,0 +1,29 @@
+class TheatrePostAdvancer
+  def self.call(theatre:)
+    new(theatre:).call
+  end
+
+  def initialize(theatre:)
+    @theatre = theatre
+  end
+
+  def call
+    previous_post = theatre.current_post
+    post = TheatrePostSelector.new(theatre:).select
+
+    TheatreSkipVote.where(theatre:, post: previous_post).delete_all if previous_post
+
+    theatre.update!(current_post: post, current_post_started_at: post ? Time.current : nil)
+
+    if post
+      position = (theatre.programmes.maximum(:position) || 0) + 1
+      theatre.programmes.create!(position:, post:)
+    end
+
+    post
+  end
+
+  private
+
+  attr_reader :theatre
+end

backend/app/services/theatre_post_selector.rb

@@ -0,0 +1,119 @@
+class TheatrePostSelector
+  Candidate = Struct.new(:post, :weight, :penalty, :tags, keyword_init: true)
+  ELIGIBLE_POST_URL_CONDITION =
+      ["url LIKE '%nicovideo.jp%'",
+       "url LIKE '%youtube.com/watch%'",
+       "url LIKE '%youtu.be/%'"]
+      .join(' OR ')
+
+  def initialize theatre:
+    @theatre = theatre
+  end
+
+  def select
+    candidates = weighted_candidates
+    return nil if candidates.empty?
+
+    total = candidates.sum(&:weight)
+    target = rand * total
+
+    candidates.each do |candidate|
+      target -= candidate.weight
+      return candidate.post if target <= 0
+    end
+
+    candidates.last.post
+  end
+
+  def weight_json limit: 20
+    candidates = weighted_candidates
+    sorted = candidates.sort_by { |candidate| [candidate.weight, candidate.post.id] }
+
+    { tag_penalties: tag_penalty_json,
+      lightest_posts: post_weight_json(sorted.first(limit)),
+      heaviest_posts: post_weight_json(sorted.reverse.first(limit)) }
+  end
+
+  private
+
+  attr_reader :theatre
+
+  def weighted_candidates
+    @weighted_candidates ||= begin
+      penalties = tag_penalties
+      posts = eligible_posts.includes(tags: :tag_name).to_a
+
+      posts.map do |post|
+        post_tags = post.tags.to_a
+        penalty = post_tags.sum { |tag| penalties[tag.id].to_i }
+
+        Candidate.new(
+            post:,
+            penalty:,
+            tags: post_tags,
+            weight: 1.0 / (1.0 + penalty))
+      end
+    end
+  end
+
+  def eligible_posts
+    posts = Post.where(ELIGIBLE_POST_URL_CONDITION)
+    posts = posts.where.not(id: theatre.current_post_id) if theatre.current_post_id
+    posts
+  end
+
+  def active_user_ids
+    @active_user_ids ||= theatre.watching_users.ids
+  end
+
+  def tag_penalties
+    @tag_penalties ||=
+      if active_user_ids.empty?
+        {}
+      else
+        TheatreSkipEventVoter
+          .joins(theatre_skip_event: :event_tags)
+          .where(user_id: active_user_ids)
+          .group('theatre_skip_event_tags.tag_id')
+          .count
+      end
+  end
+
+  def tag_penalty_json
+    return [] if tag_penalties.empty?
+
+    tags = Tag.where(id: tag_penalties.keys).includes(:tag_name).index_by(&:id)
+
+    tag_penalties
+      .map { |tag_id, penalty|
+        tag = tags[tag_id]
+        next unless tag
+
+        { tag: light_tag_json(tag),
+          penalty: }
+      }
+      .compact
+      .sort_by { |row| [-row[:penalty], row[:tag][:name].to_s] }
+  end
+
+  def post_weight_json candidates
+    candidates.map { |candidate|
+      { post: light_post_json(candidate.post),
+        weight: candidate.weight,
+        penalty: candidate.penalty,
+        tags: candidate.tags.map { |tag| light_tag_json(tag) } }
+    }
+  end
+
+  def light_post_json post
+    { id: post.id,
+      title: post.title,
+      url: post.url }
+  end
+
+  def light_tag_json tag
+    { id: tag.id,
+      name: tag.name,
+      category: tag.category }
+  end
+end

backend/app/services/theatre_skip_finalizer.rb

@@ -0,0 +1,40 @@
+class TheatreSkipFinalizer
+  def self.call(theatre:, user:)
+    new(theatre:, user:).call
+  end
+
+  def initialize(theatre:, user:)
+    @theatre = theatre
+    @user = user
+  end
+
+  def call
+    return unless theatre.current_post
+
+    post = theatre.current_post
+    voters = TheatreSkipVote.where(theatre:, post:).includes(:user).map(&:user)
+    return if voters.empty?
+
+    event = TheatreSkipEvent.create!(
+        theatre:,
+        post:,
+        skipped_by_user: user,
+        programme_position: theatre.programmes.maximum(:position))
+
+    voters.uniq(&:id).each do |voter|
+      TheatreSkipEventVoter.create!(theatre_skip_event: event, user: voter)
+    end
+
+    post.tags.find_each do |tag|
+      TheatreSkipEventTag.create!(theatre_skip_event: event, tag:)
+    end
+
+    TheatreSkipVote.where(theatre:, post:).delete_all
+
+    event
+  end
+
+  private
+
+  attr_reader :theatre, :user
+end

backend/app/services/version_recorder.rb

@@ -16,19 +16,20 @@ class VersionRecorder
       @record = record_class.unscoped.lock.find(@record.id)
       latest = latest_version
 
-      if !(latest) && @event_type != 'create'
-        raise "#{ version_class.name } first event must be create"
-      end
-
-      if @event_type == 'create' && latest
-        raise "#{ version_class.name } create event already exists"
-      end
+      validate_version_sequence!(latest)
 
       attrs = snapshot_attributes
 
-      return latest if @event_type == 'update' && latest && same_snapshot?(latest, attrs)
+      if @event_type == 'update' && latest && same_snapshot?(latest, attrs)
+        return latest
+      end
 
-      version_class.create!(base_attributes(latest).merge(record_key => @record).merge(attrs))
+      version = version_class.create!(
+          base_attributes(latest).merge(record_key => @record).merge(attrs))
+
+      update_record_version_no!(version.version_no)
+
+      version
     end
   end
 
@@ -45,10 +46,34 @@ class VersionRecorder
       created_by_user: @created_by_user }
   end
 
-  def same_snapshot?(version, attrs) = attrs.all? { |k, v| version.public_send(k) == v }
+  def update_record_version_no! version_no
+    @record.update_columns(version_no:)
+    @record.version_no = version_no
+  end
+
+  def validate_version_sequence! latest
+    if !(latest) && @event_type != 'create'
+      raise "#{ version_class.name } first event must be create"
+    end
+
+    if @event_type == 'create' && latest
+      raise "#{ version_class.name } create event already exists"
+    end
+
+    return unless latest
+
+    if @record.version_no != latest.version_no
+      raise ("#{ record_class.name }##{ @record.id } version_no is #{ @record.version_no }, " +
+             "but latest #{ version_class.name } version_no is #{ latest.version_no }")
+    end
+  end
+
+  def same_snapshot? version, attrs
+    attrs.all? { |k, v| version.public_send(k) == v }
+  end
 
   def validate_event_type!
-    return if EVENT_TYPES.include?(@event_type)
+    return if event_types.include?(@event_type)
 
     raise ArgumentError, "Invalid event_type: #{ @event_type }"
   end
@@ -59,4 +84,5 @@ class VersionRecorder
   def snapshot_attributes = raise NotImplementedError
 
   def record_class = @record.class
+  def event_types = self.class::EVENT_TYPES
 end

backend/app/services/wiki/commit.rb

@@ -7,6 +7,31 @@ module Wiki
       ;
     end
 
+    def self.create_content! tag_name:, body:, created_by_user:, message: nil
+      normalised = normalise_body(body)
+
+      page = WikiPage.new(tag_name:,
+                          body: normalised,
+                          created_user: created_by_user,
+                          updated_user: created_by_user)
+
+      if normalised.blank?
+        page.errors.add(:body, :blank)
+        raise ActiveRecord::RecordInvalid, page
+      end
+
+      ActiveRecord::Base.transaction do
+        page.save!
+
+        new(page:, created_user: created_by_user).content!(
+            body: normalised,
+            message:,
+            base_revision_id: nil)
+
+        page
+      end
+    end
+
     def self.content! page:, body:, created_user:, message: nil, base_revision_id: nil
       new(page:, created_user:).content!(body:, message:, base_revision_id:)
     end
@@ -21,7 +46,12 @@ module Wiki
     end
 
     def content! body:, message:, base_revision_id:
-      normalised = normalise_body(body)
+      normalised = self.class.normalise_body(body)
+      if normalised.blank?
+        @page.errors.add(:body, :blank)
+        raise ActiveRecord::RecordInvalid, @page
+      end
+
       lines = split_lines(normalised)
 
       line_shas = lines.map { |line| Digest::SHA256.hexdigest(line) }
@@ -37,10 +67,19 @@ module Wiki
           current_id = @page.wiki_revisions.maximum(:id)
           if current_id && current_id != base_revision_id.to_i
             raise Conflict,
-                "競合が発生してゐます（現在の Id.：#{ current_id }，ベース Id.：#{ base_revision_id }）．"
+                "競合が発生してゐます" +
+                "（現在の Id.：#{ current_id }，ベース Id.：#{ base_revision_id }）．"
           end
         end
 
+        @page.update!(body: normalised)
+
+        WikiVersionRecorder.record!(
+            page: @page,
+            event_type: @page.wiki_versions.exists? ? :update : :create,
+            reason: message,
+            created_by_user: @created_user)
+
         rev = WikiRevision.create!(
             wiki_page:        @page,
             base_revision_id:,
@@ -54,65 +93,45 @@ module Wiki
         rows = line_ids.each_with_index.map do |line_id, pos|
           { wiki_revision_id: rev.id, wiki_line_id: line_id, position: pos }
         end
-        WikiRevisionLine.insert_all!(rows)
+        WikiRevisionLine.insert_all!(rows) if rows.any?
 
         rev
       end
     end
 
-    def redirect! redirect_page:, message:, base_revision_id:
-      ActiveRecord::Base.transaction do
-        @page.lock!
+    def redirect!(redirect_page:, message:, base_revision_id:) = raise '廃止しました．'
 
-        if base_revision_id.present?
-          current_id = @page.wiki_revisions.maximum(:id)
-          if current_id && current_id != base_revision_id.to_i
-            raise Conflict,
-                "競合が発生してゐます（現在の Id.：#{ current_id }，ベース Id.：#{ base_revision_id }）．"
-          end
-        end
-
-        WikiRevision.create!(
-            wiki_page:        @page,
-            base_revision_id:,
-            created_user:     @created_user,
-            kind:             :redirect,
-            redirect_page:,
-            message:,
-            lines_count:      0,
-            tree_sha256:      nil)
-      end
+    def self.normalise_body body
+      s = body.to_s
+      s.gsub!(/\r\n?/, "\n")
+      s.encode('UTF-8', invalid: :replace, undef: :replace, replace: '🖕')
+      s.gsub(/\n+$/, '')
     end
 
     private
 
-    def normalise_body body
-      s = body.to_s
-      s.gsub!("\r\n", "\n")
-      s.encode('UTF-8', invalid: :replace, undef: :replace, replace: '🖕')
-    end
-
-    def split_lines body
-      body.split("\n")
-    end
+    def split_lines(body) = body.split("\n")
 
     def upsert_lines! lines, line_shas
       now = Time.current
 
       id_by_sha = WikiLine.where(sha256: line_shas).pluck(:sha256, :id).to_h
 
-      missing_rows = []
+      missing_by_sha = { }
+
       line_shas.each_with_index do |sha, i|
         next if id_by_sha.key?(sha)
+        next if missing_by_sha.key?(sha)
 
-        missing_rows << { sha256:     sha,
-                          body:       lines[i],
-                          created_at: now,
-                          updated_at: now }
+        missing_by_sha[sha] = {
+            sha256:     sha,
+            body:       lines[i],
+            created_at: now,
+            updated_at: now }
       end
 
-      if missing_rows.any?
-        WikiLine.upsert_all(missing_rows)
+      if missing_by_sha.any?
+        WikiLine.upsert_all(missing_by_sha.values)
         id_by_sha = WikiLine.where(sha256: line_shas).pluck(:sha256, :id).to_h
       end
 

backend/app/services/wiki_version_recorder.rb

@@ -0,0 +1,21 @@
+class WikiVersionRecorder < VersionRecorder
+  def self.record! page:, event_type:, reason: nil, created_by_user:
+    new(page:, event_type:, reason:, created_by_user:).record!
+  end
+
+  def initialize page:, event_type:, reason: nil, created_by_user:
+    @reason = reason
+    super(record: page, event_type:, created_by_user:)
+  end
+
+  private
+
+  def version_class = WikiVersion
+  def version_association = :wiki_versions
+  def record_key = :wiki_page
+
+  def snapshot_attributes = {
+      title: @record.title,
+      body: @record.body,
+      reason: @reason }
+end