lint

Fix Cien resolver

.circleci/config.yml

@@ -0,0 +1,177 @@
+version: 2.1
+
+executors:
+  build:
+    docker:
+      - image: circleci/php:7.3-node-browsers
+        environment:
+          APP_DEBUG: true
+          APP_ENV: testing
+          APP_KEY: base64:f2tcw34GKT8EOtb5myZxJ8QLdgNivmyPhoQIPY2YfK8=
+          DB_CONNECTION: pgsql
+          DB_DATABASE: tissue
+          DB_USERNAME: tissue
+          DB_PASSWORD: tissue
+      - image: circleci/postgres:10-alpine
+        environment:
+          POSTGRES_DB: tissue
+          POSTGRES_USER: tissue
+          POSTGRES_PASSWORD: tissue
+
+commands:
+  initialize:
+    steps:
+      - checkout
+      - run: sudo apt update
+      - run: sudo apt install -y libpq-dev
+      - run: sudo pecl install -f xdebug-2.9.8 && sudo docker-php-ext-enable xdebug
+      - run: sudo docker-php-ext-install zip
+      - run: sudo docker-php-ext-install pdo_pgsql
+      - run:
+          command: |
+            curl -sSL "https://nodejs.org/dist/v12.16.3/node-v12.16.3-linux-x64.tar.xz" | sudo tar --strip-components=2 -xJ -C /usr/local/bin/ node-v12.16.3-linux-x64/bin/node
+            curl https://www.npmjs.com/install.sh | sudo bash
+  restore_composer:
+    steps:
+      - restore_cache:
+          keys:
+            - v1-dependencies-{{ checksum "composer.json" }}
+            - v1-dependencies-
+  save_composer:
+    steps:
+      - save_cache:
+          key: v1-dependencies-{{ checksum "composer.json" }}
+          paths:
+            - ./vendor
+  restore_npm:
+    steps:
+      - restore_cache:
+          keys:
+            - v1-dependencies-{{ checksum "package.json" }}
+            - v1-dependencies-
+  save_npm:
+    steps:
+      - save_cache:
+          key: v1-dependencies-{{ checksum "package.json" }}
+          paths:
+            - ./node_modules
+            - ~/.yarn
+
+jobs:
+  build:
+    executor: build
+    steps:
+      - initialize
+
+      - restore_composer
+      - run: composer install -n --prefer-dist
+      - save_composer
+
+      - restore_npm
+      - run: yarn install
+      - save_npm
+
+      - run: yarn run prod
+
+      - persist_to_workspace:
+          root: .
+          paths:
+            - public
+
+  test:
+    executor: build
+    steps:
+      - initialize
+
+      - restore_composer
+      - restore_npm
+
+      - attach_workspace:
+          at: .
+
+      - run: php artisan migrate
+
+      # Run linter
+      - run:
+          command: |
+            mkdir -p /tmp/php-cs-fixer
+            ./vendor/bin/php-cs-fixer fix --dry-run --diff --format=junit > /tmp/php-cs-fixer/php-cs-fixer.xml
+          when: always
+      - store_test_results:
+          path: /tmp/php-cs-fixer
+
+      # Run stylelint
+      - run:
+          name: stylelint
+          command: yarn run stylelint
+          when: always
+
+      # Run eslint
+      - run:
+          name: eslint
+          command: yarn run eslint
+          when: always
+
+      # Run unit test
+      - run:
+          command: |
+            mkdir -p /tmp/phpunit
+            ./vendor/bin/phpunit --log-junit /tmp/phpunit/phpunit.xml --coverage-clover=/tmp/phpunit/coverage.xml
+          when: always
+      - store_test_results:
+          path: /tmp/phpunit
+      - store_artifacts:
+          path: /tmp/phpunit/coverage.xml
+
+      # Upload coverage
+      - run:
+          command: bash <(curl -s https://codecov.io/bash) -f /tmp/phpunit/coverage.xml
+          when: always
+
+  test_resolver:
+    executor: build
+    environment:
+      TEST_USE_HTTP_MOCK: false
+    steps:
+      - initialize
+
+      - restore_composer
+
+      - attach_workspace:
+          at: .
+
+      - run: php artisan migrate
+
+      # Run unit test
+      - run:
+          command: |
+            mkdir -p /tmp/phpunit
+            ./vendor/bin/phpunit --testsuite MetadataResolver --log-junit /tmp/phpunit/phpunit.xml --coverage-clover=/tmp/phpunit/coverage.xml
+          when: always
+      - store_test_results:
+          path: /tmp/phpunit
+      - store_artifacts:
+          path: /tmp/phpunit/coverage.xml
+
+workflows:
+  version: 2.1
+  test:
+    jobs:
+      - build
+      - test:
+          requires:
+            - build
+  scheduled_resolver_test:
+    triggers:
+      - schedule:
+          cron: "4 0 * * 1"
+          filters:
+            branches:
+              only:
+                - develop
+    jobs:
+      - build
+      - test_resolver:
+          requires:
+            - build
+

.editorconfig

@@ -0,0 +1,21 @@
+root = true
+
+[*]
+indent_style = space
+indent_size = 4
+charset = utf-8
+end_of_line = lf
+trim_trailing_whitespace = true
+insert_final_newline = true
+
+[*.md]
+trim_trailing_whitespace = false
+
+[*.{yml,yaml}]
+indent_size = 2
+
+[*.json]
+indent_size = 2
+
+[composer.json]
+indent_size = 4

.env.example

@@ -2,9 +2,16 @@ APP_NAME=Tissue
 APP_ENV=local
 APP_KEY=
 APP_DEBUG=true
-APP_LOG_LEVEL=debug
 APP_URL=http://localhost
 
+LOG_CHANNEL=stack
+
+# テストにモックを使用するか falseの場合は実際のHTML等を取得してテストする
+TEST_USE_HTTP_MOCK=true
+
+# テスト用のスナップショットを更新する場合はtrueにする (TEST_USE_HTTP_MOCKと重複させないよう注意)
+TEST_UPDATE_SNAPSHOT=false
+
 DB_CONNECTION=pgsql
 DB_HOST=db
 DB_PORT=5432
@@ -30,13 +37,23 @@ MAIL_ENCRYPTION=tls
 MAIL_FROM_ADDRESS=[email protected]
 MAIL_FROM_NAME=Tissue
 
+AWS_ACCESS_KEY_ID=
+AWS_SECRET_ACCESS_KEY=
+AWS_DEFAULT_REGION=us-east-1
+AWS_BUCKET=
+
 SPARKPOST_SECRET=
 
 PUSHER_APP_ID=
 PUSHER_APP_KEY=
 PUSHER_APP_SECRET=
 
+MIX_PUSHER_APP_KEY="${PUSHER_APP_KEY}"
+MIX_PUSHER_APP_CLUSTER="${PUSHER_APP_CLUSTER}"
+
 # (Optional) reCAPTCHA Key
 # https://www.google.com/recaptcha
 NOCAPTCHA_SECRET=
 NOCAPTCHA_SITEKEY=
+
+SENTRY_LARAVEL_DSN=

.eslintrc.js

@@ -0,0 +1,38 @@
+module.exports = {
+    env: {
+        browser: true,
+        es2020: true,
+        node: true,
+    },
+    extends: [
+        'eslint:recommended',
+        'plugin:react/recommended',
+        'plugin:prettier/recommended',
+        'plugin:@typescript-eslint/recommended',
+        'prettier',
+        'prettier/@typescript-eslint',
+        'prettier/react',
+    ],
+    parser: '@typescript-eslint/parser',
+    parserOptions: {
+        ecmaVersion: 11,
+        ecmaFeatures: {
+            jsx: true,
+        },
+        sourceType: 'module',
+    },
+    plugins: ['prettier', 'react', '@typescript-eslint', 'jquery'],
+    rules: {
+        '@typescript-eslint/explicit-module-boundary-types': 0,
+        '@typescript-eslint/no-explicit-any': 0,
+        '@typescript-eslint/no-unused-vars': ['warn', { argsIgnorePattern: '^_' }],
+        'jquery/no-ajax': 2,
+        'jquery/no-ajax-events': 2,
+        'react/prop-types': 0,
+    },
+    settings: {
+        react: {
+            version: 'detect',
+        },
+    },
+};

.github/dependabot.yml

@@ -0,0 +1,14 @@
+version: 2
+updates:
+
+  # Maintain dependencies for npm
+  - package-ecosystem: "npm"
+    directory: "/"
+    schedule:
+      interval: "daily"
+
+  # Maintain dependencies for Composer
+  - package-ecosystem: "composer"
+    directory: "/"
+    schedule:
+      interval: "daily"

.gitignore

@@ -1,15 +1,24 @@
 /node_modules
+/public/css
+/public/fonts
+/public/js
 /public/hot
 /public/storage
+/public/mix-manifest.json
+/public/report.html
+/public/apidoc.html
 /storage/*.key
 /vendor
 /.idea
+/.vscode
 /.vagrant
 Homestead.json
 Homestead.yaml
 npm-debug.log
 yarn-error.log
 .env
+.env.backup
+.phpunit.result.cache
 *.iml
 .php_cs
 .php_cs.cache

.prettierrc

@@ -0,0 +1,5 @@
+{
+  "arrowParens": "always",
+  "singleQuote": true,
+  "printWidth": 120
+}

.stylelintignore

@@ -0,0 +1 @@
+/tests/fixture/*

Dockerfile

@@ -1,10 +1,12 @@
-FROM php:7.1-apache
+FROM node:10-jessie as node
+
+FROM php:7.3-apache
 
 ENV APACHE_DOCUMENT_ROOT /var/www/html/public
 
 RUN apt-get update \
-    && apt-get install -y git libpq-dev unzip \
-    && docker-php-ext-install pdo_pgsql \
+    && apt-get install -y git libpq-dev unzip libicu-dev \
+    && docker-php-ext-install pdo_pgsql intl \
     && pecl install xdebug \
     && curl -sS https://getcomposer.org/installer | php \
     && mv composer.phar /usr/local/bin/composer \
@@ -15,6 +17,16 @@ RUN apt-get update \
 COPY dist/bin /usr/local/bin/
 COPY dist/php.d /usr/local/etc/php/php.d/
 
+COPY --from=node /usr/local/bin/node /usr/local/bin/
+COPY --from=node /usr/local/lib/node_modules /usr/local/lib/node_modules
+COPY --from=node /opt/yarn-* /opt/yarn
+
+RUN ln -s /opt/yarn/bin/yarn /usr/local/bin/yarn \
+    && ln -s ../lib/node_modules/npm/bin/npm-cli.js /usr/local/bin/npm \
+    && ln -s ../lib/node_modules/npm/bin/npx-cli.js /usr/local/bin/npx
+
+
+
 ENTRYPOINT ["tissue-entrypoint.sh"]
 CMD ["apache2-foreground"]
 

Procfile

@@ -0,0 +1 @@
+web: vendor/bin/heroku-php-apache2 public/

README.md

@@ -7,12 +7,12 @@ a.k.a. shikorism.net
 
 ## 構成
 
-- Laravel 5.5
-- Bootstrap 4.2.1
+- Laravel 6
+- Bootstrap 4.4.1
 
 ## 実行環境
 
-- PHP 7.1
+- PHP 7.3
 - PostgreSQL 9.6
 
 ## 開発環境の構築
@@ -33,10 +33,11 @@ docker-compose build
 docker-compose up -d
 ```
 
-4. Composer を使い必要なライブラリをインストールします。
+4. Composer と yarn を使い必要なライブラリをインストールします。
 
 ```
 docker-compose exec web composer install
+docker-compose exec web yarn install
 ```
 
 5. 暗号化キーの作成と、データベースのマイグレーションを行います。
@@ -49,10 +50,17 @@ docker-compose exec web php artisan migrate
 6. ファイルに書き込めるように権限を設定します。
 
 ```
-docker-compose exec web chown -R www-data /var/www/html
+docker-compose exec web chown -R www-data /var/www/html/storage
 ```
 
-7. 最後に `.env` を読み込み直すために起動し直します。
+7. アセットをビルドします。
+
+```
+docker-compose exec web yarn dev
+```
+
+
+8. 最後に `.env` を読み込み直すために起動し直します。
 
 ```
 docker-compose up -d
@@ -68,6 +76,25 @@ docker-compose -f docker-compose.yml -f docker-compose.debug.yml up -d
 
 で起動することにより、DB のポート`5432`を開放してホストマシンから接続できるようになります。
 
+## アセットのリアルタイムビルド
+`yarn watch`を使うとソースファイルを監視して差分があると差分ビルドしてくれます。フロント開発時は活用しましょう。
+```
+docker-compose run --rm web yarn watch
+```
+
+もしファイル変更時に更新されない場合は`yarn watch-poll`を試してみてください。  
+現在Docker環境でのHMRはサポートしてません。Docker外ならおそらく動くでしょう。  
+その他詳しくはlaravel-mixのドキュメントなどを当たってください。
+
+## phpunit によるテスト
+
+変更をしたらPull Requestを投げる前にテストが通ることを確認してください。  
+テストは以下のコマンドで実行できます。
+
+```
+docker-compose exec web composer test
+```
+
 ## 環境構築上の諸注意
 
 - 初版時点では、DB サーバとして PostgreSQL を使うよう .env ファイルを設定するくらいです。

app/CheckinWebhook.php

@@ -0,0 +1,39 @@
+<?php
+
+namespace App;
+
+use Illuminate\Database\Eloquent\Model;
+use Illuminate\Database\Eloquent\SoftDeletes;
+use Illuminate\Support\Str;
+
+class CheckinWebhook extends Model
+{
+    use SoftDeletes;
+
+    /** @var int ユーザーごとの作成数制限 */
+    const PER_USER_LIMIT = 10;
+
+    public $incrementing = false;
+    protected $keyType = 'string';
+
+    protected $fillable = ['name'];
+
+    protected static function boot()
+    {
+        parent::boot();
+
+        self::creating(function (CheckinWebhook $webhook) {
+            $webhook->id = Str::random(64);
+        });
+    }
+
+    public function user()
+    {
+        return $this->belongsTo(User::class);
+    }
+
+    public function isAvailable()
+    {
+        return $this->user !== null;
+    }
+}

app/Console/Commands/DedupTags.php

@@ -0,0 +1,113 @@
+<?php
+
+namespace App\Console\Commands;
+
+use App\Tag;
+use Illuminate\Console\Command;
+use Illuminate\Support\Facades\DB;
+
+class DedupTags extends Command
+{
+    /**
+     * The name and signature of the console command.
+     *
+     * @var string
+     */
+    protected $signature = 'tissue:tag:dedup {--dry-run}';
+
+    /**
+     * The console command description.
+     *
+     * @var string
+     */
+    protected $description = 'Deduplicate tags';
+
+    /**
+     * Create a new command instance.
+     *
+     * @return void
+     */
+    public function __construct()
+    {
+        parent::__construct();
+    }
+
+    /**
+     * Execute the console command.
+     *
+     * @return mixed
+     */
+    public function handle()
+    {
+        if ($this->option('dry-run')) {
+            $this->warn('dry-runモードで実行します。');
+        } else {
+            if (!$this->confirm('dry-runオプションが付いてないけど、本当に実行しますか？')) {
+                return;
+            }
+        }
+
+        DB::transaction(function () {
+            $duplicatedTags = DB::table('tags')
+                ->select('name', DB::raw('count(*)'))
+                ->groupBy('name')
+                ->having(DB::raw('count(*)'), '>=', 2)
+                ->get();
+
+            $this->info($duplicatedTags->count() . ' duplicated tags found.');
+
+            foreach ($duplicatedTags as $tag) {
+                $this->line('Tag name: ' . $tag->name);
+
+                $tagIds = Tag::where('name', $tag->name)->orderBy('id')->pluck('id');
+                $newId = $tagIds->first();
+                $dropIds = $tagIds->slice(1);
+
+                $this->line('  New ID: ' . $newId);
+                $this->line('  Drop IDs: ' . $dropIds->implode(', '));
+
+                if ($this->option('dry-run')) {
+                    continue;
+                }
+
+                // 同じタグ名でIDが違うものについて、全て統一する
+                foreach (['ejaculation_tag', 'metadata_tag'] as $table) {
+                    DB::table($table)
+                        ->whereIn('tag_id', $dropIds)
+                        ->update(['tag_id' => $newId]);
+                }
+                DB::table('tags')->whereIn('id', $dropIds)->delete();
+
+                // 統一した上で、重複しているレコードを削除する
+                DB::delete(
+                    <<<SQL
+DELETE FROM ejaculation_tag
+WHERE id IN (
+    SELECT id
+    FROM (
+        SELECT id, row_number() OVER (PARTITION BY ejaculation_id, tag_id ORDER BY id) AS ord
+        FROM ejaculation_tag
+    ) t
+    WHERE ord > 1
+)
+SQL
+                );
+                DB::delete(
+                    <<<SQL
+DELETE FROM metadata_tag
+WHERE id IN (
+    SELECT id
+    FROM (
+        SELECT id, row_number() OVER (PARTITION BY metadata_url, tag_id ORDER BY id) AS ord
+        FROM metadata_tag
+    ) t
+    WHERE ord > 1
+)
+SQL
+                );
+            }
+        });
+
+        $this->info('Done!');
+    }
+}

app/Console/Commands/DemoteUser.php

@@ -0,0 +1,61 @@
+<?php
+
+namespace App\Console\Commands;
+
+use App\User;
+use Illuminate\Console\Command;
+
+class DemoteUser extends Command
+{
+    /**
+     * The name and signature of the console command.
+     *
+     * @var string
+     */
+    protected $signature = 'tissue:user:demote {username}';
+
+    /**
+     * The console command description.
+     *
+     * @var string
+     */
+    protected $description = 'Demote admin to user';
+
+    /**
+     * Create a new command instance.
+     *
+     * @return void
+     */
+    public function __construct()
+    {
+        parent::__construct();
+    }
+
+    /**
+     * Execute the console command.
+     *
+     * @return mixed
+     */
+    public function handle()
+    {
+        $user = User::where('name', $this->argument('username'))->first();
+        if ($user === null) {
+            $this->error('No user with such username');
+
+            return 1;
+        }
+
+        if (!$user->is_admin) {
+            $this->info('@' . $user->name . ' is already an user.');
+
+            return 0;
+        }
+
+        $user->is_admin = false;
+        if ($user->save()) {
+            $this->info('@' . $user->name . ' is an user now.');
+        } else {
+            $this->error('Something happened.');
+        }
+    }
+}

app/Console/Commands/NormalizeTags.php

@@ -0,0 +1,61 @@
+<?php
+
+namespace App\Console\Commands;
+
+use App\Tag;
+use App\Utilities\Formatter;
+use DB;
+use Illuminate\Console\Command;
+
+class NormalizeTags extends Command
+{
+    /**
+     * The name and signature of the console command.
+     *
+     * @var string
+     */
+    protected $signature = 'tissue:tag:normalize';
+
+    /**
+     * The console command description.
+     *
+     * @var string
+     */
+    protected $description = 'Normalize tags';
+
+    private $formatter;
+
+    /**
+     * Create a new command instance.
+     *
+     * @return void
+     */
+    public function __construct(Formatter $formatter)
+    {
+        parent::__construct();
+        $this->formatter = $formatter;
+    }
+
+    /**
+     * Execute the console command.
+     *
+     * @return mixed
+     */
+    public function handle()
+    {
+        $start = hrtime(true);
+
+        DB::transaction(function () {
+            /** @var Tag $tag */
+            foreach (Tag::query()->cursor() as $tag) {
+                $normalizedName = $this->formatter->normalizeTagName($tag->name);
+                $this->line("{$tag->name} : {$normalizedName}");
+                $tag->normalized_name = $normalizedName;
+                $tag->save();
+            }
+        });
+
+        $elapsed = (hrtime(true) - $start) / 1e+9;
+        $this->info("Done! ({$elapsed} sec)");
+    }
+}

app/Console/Commands/PromoteUser.php

@@ -0,0 +1,61 @@
+<?php
+
+namespace App\Console\Commands;
+
+use App\User;
+use Illuminate\Console\Command;
+
+class PromoteUser extends Command
+{
+    /**
+     * The name and signature of the console command.
+     *
+     * @var string
+     */
+    protected $signature = 'tissue:user:promote {username}';
+
+    /**
+     * The console command description.
+     *
+     * @var string
+     */
+    protected $description = 'Promote user to admin';
+
+    /**
+     * Create a new command instance.
+     *
+     * @return void
+     */
+    public function __construct()
+    {
+        parent::__construct();
+    }
+
+    /**
+     * Execute the console command.
+     *
+     * @return mixed
+     */
+    public function handle()
+    {
+        $user = User::where('name', $this->argument('username'))->first();
+        if ($user === null) {
+            $this->error('No user with such username');
+
+            return 1;
+        }
+
+        if ($user->is_admin) {
+            $this->info('@' . $user->name . ' is already an administrator.');
+
+            return 0;
+        }
+
+        $user->is_admin = true;
+        if ($user->save()) {
+            $this->info('@' . $user->name . ' is an administrator now.');
+        } else {
+            $this->error('Something happened.');
+        }
+    }
+}

app/Console/Kernel.php

@@ -2,6 +2,8 @@
 
 namespace App\Console;
 
+use App\Console\Commands\DemoteUser;
+use App\Console\Commands\PromoteUser;
 use Illuminate\Console\Scheduling\Schedule;
 use Illuminate\Foundation\Console\Kernel as ConsoleKernel;
 
@@ -35,6 +37,8 @@ class Kernel extends ConsoleKernel
      */
     protected function commands()
     {
+        $this->load(__DIR__.'/Commands');
+
         require base_path('routes/console.php');
     }
 }

app/ContentProvider.php

@@ -0,0 +1,24 @@
+<?php
+
+namespace App;
+
+use Illuminate\Database\Eloquent\Model;
+
+class ContentProvider extends Model
+{
+    public $incrementing = false;
+    protected $primaryKey = 'host';
+    protected $keyType = 'string';
+
+    protected $fillable = [
+        'host',
+        'robots',
+        'robots_cached_at',
+    ];
+
+    protected $dates = [
+        'created_at',
+        'updated_at',
+        'robots_cached_at',
+    ];
+}

app/DeactivatedUser.php

@@ -0,0 +1,18 @@
+<?php
+
+namespace App;
+
+use Illuminate\Database\Eloquent\Model;
+
+/**
+ * 削除済Userのユーザー名履歴
+ */
+class DeactivatedUser extends Model
+{
+    public $incrementing = false;
+    protected $keyType = 'string';
+
+    protected $fillable = [
+        'name'
+    ];
+}

app/Ejaculation.php

@@ -2,16 +2,25 @@
 
 namespace App;
 
+use Illuminate\Database\Eloquent\Builder;
 use Illuminate\Database\Eloquent\Model;
+use Illuminate\Support\Facades\Auth;
+use Illuminate\Support\Facades\DB;
+use Staudenmeir\EloquentEagerLimit\HasEagerLimit;
 
 class Ejaculation extends Model
 {
-    //
+    use HasEagerLimit;
+
+    const SOURCE_WEB = 'web';
+    const SOURCE_CSV = 'csv';
+    const SOURCE_WEBHOOK = 'webhook';
 
     protected $fillable = [
         'user_id', 'ejaculated_date',
-        'note', 'geo_latitude', 'geo_longitude', 'link',
-        'is_private'
+        'note', 'geo_latitude', 'geo_longitude', 'link', 'source',
+        'is_private', 'is_too_sensitive', 'discard_elapsed_time',
+        'checkin_webhook_id'
     ];
 
     protected $dates = [
@@ -34,4 +43,95 @@ class Ejaculation extends Model
             return $v->name;
         })->all());
     }
+
+    public function likes()
+    {
+        return $this->hasMany(Like::class);
+    }
+
+    public function scopeVisibleToTimeline(Builder $query)
+    {
+        return $query->whereIn('ejaculations.source', [Ejaculation::SOURCE_WEB, Ejaculation::SOURCE_WEBHOOK]);
+    }
+
+    public function scopeWithLikes(Builder $query)
+    {
+        if (Auth::check()) {
+            // TODO - このスコープを使うことでlikesが常に直近10件で絞られるのは汚染されすぎ感がある。別名を付与できないか？
+            //      - (ejaculation_id, user_id) でユニークなわけですが、is_liked はサブクエリ発行させるのとLeft JoinしてNULLかどうかで結果を見るのどっちがいいんでしょうね
+            return $query
+                ->with([
+                    'likes' => function ($query) {
+                        $query->latest()->take(10);
+                    },
+                    'likes.user' => function ($query) {
+                        $query->where('is_protected', false)
+                            ->where('private_likes', false)
+                            ->orWhere('id', Auth::id());
+                    }
+                ])
+                ->withCount([
+                    'likes',
+                    'likes as is_liked' => function ($query) {
+                        $query->where('user_id', Auth::id());
+                    }
+                ]);
+        } else {
+            return $query
+                ->with([
+                    'likes' => function ($query) {
+                        $query->latest()->take(10);
+                    },
+                    'likes.user' => function ($query) {
+                        $query->where('is_protected', false)
+                            ->where('private_likes', false);
+                    }
+                ])
+                ->withCount('likes')
+                ->addSelect(DB::raw('0 as is_liked'));
+        }
+    }
+
+    /**
+     * このチェックインと同じ情報を流用してチェックインするためのURLを生成
+     * @return string
+     */
+    public function makeCheckinURL(): string
+    {
+        return route('checkin', [
+            'link' => $this->link,
+            'tags' => $this->textTags(),
+            'is_private' => $this->is_private,
+            'is_too_sensitive' => $this->is_too_sensitive,
+        ]);
+    }
+
+    public function ejaculatedSpan(): string
+    {
+        if (array_key_exists('ejaculated_span', $this->attributes)) {
+            if ($this->ejaculated_span === null) {
+                return '精通';
+            }
+            if ($this->discard_elapsed_time) {
+                return '0日 0時間 0分'; // TODO: 気の効いたフレーズにする
+            }
+
+            return $this->ejaculated_span;
+        } else {
+            $previous = Ejaculation::select('ejaculated_date')
+                ->where('user_id', $this->user_id)
+                ->where('ejaculated_date', '<', $this->ejaculated_date)
+                ->orderByDesc('ejaculated_date')
+                ->first();
+
+            if ($previous === null) {
+                return '精通';
+            }
+            if ($this->discard_elapsed_time) {
+                return '0日 0時間 0分';
+            }
+
+            return $this->ejaculated_date->diff($previous->ejaculated_date)->format('%a日 %h時間 %i分');
+        }
+    }
 }

app/Exceptions/CsvImportException.php

@@ -0,0 +1,30 @@
+<?php
+
+namespace App\Exceptions;
+
+use Illuminate\Support\Arr;
+use Throwable;
+
+class CsvImportException extends \RuntimeException
+{
+    /** @var string[] */
+    private $errors;
+
+    /**
+     * CsvImportException constructor.
+     * @param string[] $errors
+     */
+    public function __construct(...$errors)
+    {
+        parent::__construct(Arr::first($errors));
+        $this->errors = $errors;
+    }
+
+    /**
+     * @return string[]
+     */
+    public function getErrors(): array
+    {
+        return $this->errors;
+    }
+}

app/Exceptions/Handler.php

@@ -4,7 +4,10 @@ namespace App\Exceptions;
 
 use Exception;
 use Illuminate\Auth\AuthenticationException;
+use Illuminate\Database\Eloquent\ModelNotFoundException;
 use Illuminate\Foundation\Exceptions\Handler as ExceptionHandler;
+use Illuminate\Http\JsonResponse;
+use Symfony\Component\HttpKernel\Exception\NotFoundHttpException;
 
 class Handler extends ExceptionHandler
 {
@@ -20,18 +23,23 @@ class Handler extends ExceptionHandler
         \Illuminate\Database\Eloquent\ModelNotFoundException::class,
         \Illuminate\Session\TokenMismatchException::class,
         \Illuminate\Validation\ValidationException::class,
+        \App\MetadataResolver\ResolverCircuitBreakException::class,
     ];
 
     /**
      * Report or log an exception.
      *
-     * This is a great spot to send exceptions to Sentry, Bugsnag, etc.
-     *
      * @param  \Exception  $exception
      * @return void
+     *
+     * @throws \Exception
      */
     public function report(Exception $exception)
     {
+        if (app()->bound('sentry') && $this->shouldReport($exception)) {
+            app('sentry')->captureException($exception);
+        }
+
         parent::report($exception);
     }
 
@@ -40,7 +48,9 @@ class Handler extends ExceptionHandler
      *
      * @param  \Illuminate\Http\Request  $request
      * @param  \Exception  $exception
-     * @return \Illuminate\Http\Response
+     * @return \Symfony\Component\HttpFoundation\Response
+     *
+     * @throws \Exception
      */
     public function render($request, Exception $exception)
     {
@@ -62,4 +72,28 @@ class Handler extends ExceptionHandler
 
         return redirect()->guest(route('login'));
     }
+
+    protected function prepareException(Exception $e)
+    {
+        if (!config('app.debug') && $e instanceof ModelNotFoundException) {
+            return new NotFoundHttpException('Resource not found.', $e);
+        }
+
+        return parent::prepareException($e);
+    }
+
+    protected function prepareJsonResponse($request, Exception $e)
+    {
+        $status = $this->isHttpException($e) ? $e->getStatusCode() : 500;
+
+        return new JsonResponse(
+            [
+                'status' => $status,
+                'error' => $this->convertExceptionToArray($e),
+            ],
+            $status,
+            $this->isHttpException($e) ? $e->getHeaders() : [],
+            JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES
+        );
+    }
 }

app/Http/Controllers/Admin/DashboardController.php

@@ -0,0 +1,14 @@
+<?php
+
+namespace App\Http\Controllers\Admin;
+
+use App\Http\Controllers\Controller;
+use Illuminate\Http\Request;
+
+class DashboardController extends Controller
+{
+    public function index()
+    {
+        return view('admin.dashboard');
+    }
+}

app/Http/Controllers/Admin/InfoController.php

@@ -0,0 +1,73 @@
+<?php
+
+namespace App\Http\Controllers\Admin;
+
+use App\Http\Controllers\Controller;
+use App\Http\Requests\AdminInfoStoreRequest;
+use App\Information;
+use Illuminate\Http\Request;
+use Illuminate\Support\Facades\Validator;
+use Illuminate\Validation\Rule;
+
+class InfoController extends Controller
+{
+    public function index()
+    {
+        $informations = Information::query()
+            ->select('id', 'category', 'pinned', 'title', 'created_at')
+            ->orderByDesc('pinned')
+            ->orderByDesc('created_at')
+            ->paginate(20);
+
+        return view('admin.info.index')->with([
+            'informations' => $informations,
+            'categories' => Information::CATEGORIES
+        ]);
+    }
+
+    public function create()
+    {
+        return view('admin.info.create')->with([
+            'categories' => Information::CATEGORIES
+        ]);
+    }
+
+    public function store(AdminInfoStoreRequest $request)
+    {
+        $inputs = $request->all();
+        if (!$request->has('pinned')) {
+            $inputs['pinned'] = false;
+        }
+
+        $info = Information::create($inputs);
+
+        return redirect()->route('admin.info.edit', ['info' => $info])->with('status', 'お知らせを更新しました。');
+    }
+
+    public function edit(Information $info)
+    {
+        return view('admin.info.edit')->with([
+            'info' => $info,
+            'categories' => Information::CATEGORIES
+        ]);
+    }
+
+    public function update(AdminInfoStoreRequest $request, Information $info)
+    {
+        $inputs = $request->all();
+        if (!$request->has('pinned')) {
+            $inputs['pinned'] = false;
+        }
+
+        $info->fill($inputs)->save();
+
+        return redirect()->route('admin.info.edit', ['info' => $info])->with('status', 'お知らせを更新しました。');
+    }
+
+    public function destroy(Information $info)
+    {
+        $info->delete();
+
+        return redirect()->route('admin.info')->with('status', 'お知らせを削除しました。');
+    }
+}

app/Http/Controllers/Api/CardController.php

@@ -0,0 +1,31 @@
+<?php
+
+namespace App\Http\Controllers\Api;
+
+use App\MetadataResolver\DeniedHostException;
+use App\Services\MetadataResolveService;
+use Illuminate\Http\Request;
+
+class CardController
+{
+    public function show(Request $request, MetadataResolveService $service)
+    {
+        $request->validate([
+            'url:required|url'
+        ]);
+
+        try {
+            $metadata = $service->execute($request->input('url'));
+        } catch (DeniedHostException $e) {
+            abort(403, $e->getMessage());
+        }
+        $metadata->load('tags');
+
+        $response = response($metadata);
+        if (!config('app.debug')) {
+            $response = $response->setCache(['public' => true, 'max_age' => 86400]);
+        }
+
+        return $response;
+    }
+}

app/Http/Controllers/Api/LikeController.php

@@ -0,0 +1,73 @@
+<?php
+
+namespace App\Http\Controllers\Api;
+
+use App\Ejaculation;
+use App\Http\Controllers\Controller;
+use App\Like;
+use Illuminate\Http\Request;
+use Illuminate\Support\Facades\Auth;
+use Illuminate\Support\Facades\Validator;
+
+class LikeController extends Controller
+{
+    public function store(Request $request)
+    {
+        $request->validate([
+            'id' => 'required|integer|exists:ejaculations'
+        ]);
+
+        $keys = [
+            'user_id' => Auth::id(),
+            'ejaculation_id' => $request->input('id')
+        ];
+
+        $like = Like::query()->where($keys)->first();
+        if ($like) {
+            $data = [
+                'errors' => [
+                    ['message' => 'このチェックインはすでにいいね済です。']
+                ],
+                'ejaculation' => $like->ejaculation
+            ];
+
+            return response()->json($data, 409);
+        }
+
+        $like = Like::create($keys);
+
+        return [
+            'ejaculation' => $like->ejaculation
+        ];
+    }
+
+    public function destroy($id)
+    {
+        Validator::make(compact('id'), [
+            'id' => 'required|integer'
+        ])->validate();
+
+        $like = Like::query()->where([
+            'user_id' => Auth::id(),
+            'ejaculation_id' => $id
+        ])->first();
+        if ($like === null) {
+            $ejaculation = Ejaculation::find($id);
+
+            $data = [
+                'errors' => [
+                    ['message' => 'このチェックインはいいねされていません。']
+                ],
+                'ejaculation' => $ejaculation
+            ];
+
+            return response()->json($data, 404);
+        }
+
+        $like->delete();
+
+        return [
+            'ejaculation' => $like->ejaculation
+        ];
+    }
+}

app/Http/Controllers/Api/WebhookController.php

@@ -0,0 +1,105 @@
+<?php
+
+namespace App\Http\Controllers\Api;
+
+use App\CheckinWebhook;
+use App\Ejaculation;
+use App\Events\LinkDiscovered;
+use App\Http\Controllers\Controller;
+use App\Http\Resources\EjaculationResource;
+use App\Tag;
+use Carbon\Carbon;
+use Illuminate\Http\Request;
+use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Facades\Validator;
+use Illuminate\Validation\ValidationException;
+
+class WebhookController extends Controller
+{
+    public function checkin(CheckinWebhook $webhook, Request $request)
+    {
+        if (!$webhook->isAvailable()) {
+            return response()->json([
+                'status' => 404,
+                'error' => [
+                    'message' => 'The webhook is unavailable'
+                ]
+            ], 404);
+        }
+
+        $validator = Validator::make($request->all(), [
+            'checked_in_at' => 'nullable|date|after_or_equal:2000-01-01 00:00:00|before_or_equal:2099-12-31 23:59:59',
+            'note' => 'nullable|string|max:500',
+            'link' => 'nullable|url|max:2000',
+            'tags' => 'nullable|array',
+            'tags.*' => ['string', 'not_regex:/[\s\r\n]/u', 'max:255'],
+            'is_private' => 'nullable|boolean',
+            'is_too_sensitive' => 'nullable|boolean',
+            'discard_elapsed_time' => 'nullable|boolean',
+        ], [
+            'tags.*.not_regex' => 'The :attribute cannot contain spaces, tabs and newlines.'
+        ]);
+
+        try {
+            $inputs = $validator->validate();
+        } catch (ValidationException $e) {
+            return response()->json([
+                'status' => 422,
+                'error' => [
+                    'message' => 'Validation failed',
+                    'violations' => $validator->errors()->all(),
+                ]
+            ], 422);
+        }
+
+        $ejaculatedDate = empty($inputs['checked_in_at']) ? now() : new Carbon($inputs['checked_in_at']);
+        $ejaculatedDate = $ejaculatedDate->setTimezone(date_default_timezone_get())->startOfMinute();
+        if (Ejaculation::where(['user_id' => $webhook->user_id, 'ejaculated_date' => $ejaculatedDate])->count()) {
+            return response()->json([
+                'status' => 422,
+                'error' => [
+                    'message' => 'Checkin already exists in this time',
+                ]
+            ], 422);
+        }
+
+        $ejaculation = DB::transaction(function () use ($inputs, $webhook, $ejaculatedDate) {
+            $ejaculation = Ejaculation::create([
+                'user_id' => $webhook->user_id,
+                'ejaculated_date' => $ejaculatedDate,
+                'note' => $inputs['note'] ?? '',
+                'link' => $inputs['link'] ?? '',
+                'source' => Ejaculation::SOURCE_WEBHOOK,
+                'is_private' => (bool)($inputs['is_private'] ?? false),
+                'is_too_sensitive' => (bool)($inputs['is_too_sensitive'] ?? false),
+                'discard_elapsed_time' => (bool)($inputs['discard_elapsed_time'] ?? false),
+                'checkin_webhook_id' => $webhook->id
+            ]);
+
+            $tagIds = [];
+            if (!empty($inputs['tags'])) {
+                foreach ($inputs['tags'] as $tag) {
+                    $tag = trim($tag);
+                    if ($tag === '') {
+                        continue;
+                    }
+
+                    $tag = Tag::firstOrCreate(['name' => $tag]);
+                    $tagIds[] = $tag->id;
+                }
+            }
+            $ejaculation->tags()->sync($tagIds);
+
+            return $ejaculation;
+        });
+
+        if (!empty($ejaculation->link)) {
+            event(new LinkDiscovered($ejaculation->link));
+        }
+
+        return response()->json([
+            'status' => 200,
+            'checkin' => new EjaculationResource($ejaculation)
+        ]);
+    }
+}

app/Http/Controllers/Auth/RegisterController.php

@@ -5,6 +5,7 @@ namespace App\Http\Controllers\Auth;
 use App\Http\Controllers\Controller;
 use App\User;
 use Illuminate\Foundation\Auth\RegistersUsers;
+use Illuminate\Support\Facades\Hash;
 use Illuminate\Support\Facades\Validator;
 
 class RegisterController extends Controller
@@ -48,9 +49,9 @@ class RegisterController extends Controller
     protected function validator(array $data)
     {
         $rules = [
-            'name' => 'required|string|regex:/^[a-zA-Z0-9_-]+$/u|max:15|unique:users',
+            'name' => 'required|string|regex:/^[a-zA-Z0-9_-]+$/u|max:15|unique:users|unique:deactivated_users',
             'email' => 'required|string|email|max:255|unique:users',
-            'password' => 'required|string|min:6|confirmed'
+            'password' => 'required|string|min:8|confirmed'
         ];
 
         // reCAPTCHAのキーが設定されている場合、判定を有効化
@@ -78,7 +79,7 @@ class RegisterController extends Controller
             'name' => $data['name'],
             'display_name' => $data['name'],
             'email' => $data['email'],
-            'password' => bcrypt($data['password']),
+            'password' => Hash::make($data['password']),
             'is_protected' => $data['is_protected'] ?? false,
             'accept_analytics' => $data['accept_analytics'] ?? false
         ]);

app/Http/Controllers/Auth/VerificationController.php

@@ -0,0 +1,41 @@
+<?php
+
+namespace App\Http\Controllers\Auth;
+
+use App\Http\Controllers\Controller;
+use Illuminate\Foundation\Auth\VerifiesEmails;
+
+class VerificationController extends Controller
+{
+    /*
+    |--------------------------------------------------------------------------
+    | Email Verification Controller
+    |--------------------------------------------------------------------------
+    |
+    | This controller is responsible for handling email verification for any
+    | user that recently registered with the application. Emails may also
+    | be re-sent if the user didn't receive the original email message.
+    |
+    */
+
+    use VerifiesEmails;
+
+    /**
+     * Where to redirect users after verification.
+     *
+     * @var string
+     */
+    protected $redirectTo = '/home';
+
+    /**
+     * Create a new controller instance.
+     *
+     * @return void
+     */
+    public function __construct()
+    {
+        $this->middleware('auth');
+        $this->middleware('signed')->only('verify');
+        $this->middleware('throttle:6,1')->only('verify', 'resend');
+    }
+}

app/Http/Controllers/EjaculationController.php

@@ -9,41 +9,57 @@ use App\User;
 use Carbon\Carbon;
 use Illuminate\Http\Request;
 use Illuminate\Support\Facades\Auth;
+use Illuminate\Support\Facades\DB;
 use Validator;
 
 class EjaculationController extends Controller
 {
     public function create(Request $request)
     {
-        $defaults = [
-            'date' => $request->input('date', date('Y/m/d')),
-            'time' => $request->input('time', date('H:i')),
-            'link' => $request->input('link', ''),
-            'tags' => $request->input('tags', ''),
-            'note' => $request->input('note', ''),
-            'is_private' => $request->input('is_private', 0) == 1
+        $tags = old('tags') ?? $request->input('tags', '');
+        if (!empty($tags)) {
+            $tags = explode(' ', $tags);
+        }
+
+        $errors = $request->session()->get('errors');
+        $initialState = [
+            'mode' => 'create',
+            'fields' => [
+                'date' => old('date') ?? $request->input('date', date('Y/m/d')),
+                'time' => old('time') ?? $request->input('time', date('H:i')),
+                'link' => old('link') ?? $request->input('link', ''),
+                'tags' => $tags,
+                'note' => old('note') ?? $request->input('note', ''),
+                'is_private' => old('is_private') ?? $request->input('is_private', 0) == 1,
+                'is_too_sensitive' => old('is_too_sensitive') ?? $request->input('is_too_sensitive', 0) == 1,
+                'is_realtime' => old('is_realtime', true),
+                'discard_elapsed_time' => old('discard_elapsed_time') ?? $request->input('discard_elapsed_time') == 1,
+            ],
+            'errors' => isset($errors) ? $errors->getMessages() : null
         ];
 
-        return view('ejaculation.checkin')->with('defaults', $defaults);
+        return view('ejaculation.checkin')->with('initialState', $initialState);
     }
 
     public function store(Request $request)
     {
         $inputs = $request->all();
-        if ($request->has('note')) {
-            $inputs['note'] = str_replace(["\r\n", "\r"], "\n", $inputs['note']);
-        }
 
         $validator = Validator::make($inputs, [
-            'date' => 'required|date_format:Y/m/d',
-            'time' => 'required|date_format:H:i',
+            'date' => 'required_without:is_realtime|date_format:Y/m/d',
+            'time' => 'required_without:is_realtime|date_format:H:i',
             'note' => 'nullable|string|max:500',
             'link' => 'nullable|url|max:2000',
             'tags' => 'nullable|string',
         ])->after(function ($validator) use ($request, $inputs) {
             // 日時の重複チェック
             if (!$validator->errors()->hasAny(['date', 'time'])) {
-                $dt = $inputs['date'] . ' ' . $inputs['time'];
+                if (isset($inputs['date']) && isset($inputs['time'])) {
+                    $dt = Carbon::createFromFormat('Y/m/d H:i', $inputs['date'] . ' ' . $inputs['time']);
+                } else {
+                    $dt = now();
+                }
+                $dt = $dt->startOfMinute();
                 if (Ejaculation::where(['user_id' => Auth::id(), 'ejaculated_date' => $dt])->count()) {
                     $validator->errors()->add('datetime', '既にこの日時にチェックインしているため、登録できません。');
                 }
@@ -51,26 +67,45 @@ class EjaculationController extends Controller
         });
 
         if ($validator->fails()) {
-            return redirect()->route('checkin')->withErrors($validator)->withInput();
+            return redirect()->route('checkin')
+                ->withErrors($validator)
+                ->withInput(array_merge(['is_realtime' => false], $request->input()));
         }
 
-        $ejaculation = Ejaculation::create([
-            'user_id' => Auth::id(),
-            'ejaculated_date' => Carbon::createFromFormat('Y/m/d H:i', $inputs['date'] . ' ' . $inputs['time']),
-            'note' => $inputs['note'] ?? '',
-            'link' => $inputs['link'] ?? '',
-            'is_private' => $request->has('is_private') ?? false
-        ]);
-
-        $tagIds = [];
-        if (!empty($inputs['tags'])) {
-            $tags = explode(' ', $inputs['tags']);
-            foreach ($tags as $tag) {
-                $tag = Tag::firstOrCreate(['name' => $tag]);
-                $tagIds[] = $tag->id;
+        $ejaculation = DB::transaction(function () use ($request, $inputs) {
+            if (isset($inputs['date']) && isset($inputs['time'])) {
+                $ejaculatedDate = Carbon::createFromFormat('Y/m/d H:i', $inputs['date'] . ' ' . $inputs['time']);
+            } else {
+                $ejaculatedDate = now();
             }
-        }
-        $ejaculation->tags()->sync($tagIds);
+            $ejaculatedDate = $ejaculatedDate->startOfMinute();
+            $ejaculation = Ejaculation::create([
+                'user_id' => Auth::id(),
+                'ejaculated_date' => $ejaculatedDate,
+                'note' => $inputs['note'] ?? '',
+                'link' => $inputs['link'] ?? '',
+                'source' => Ejaculation::SOURCE_WEB,
+                'is_private' => $request->has('is_private') ?? false,
+                'is_too_sensitive' => $request->has('is_too_sensitive') ?? false,
+                'discard_elapsed_time' => $request->has('discard_elapsed_time') ?? false,
+            ]);
+
+            $tagIds = [];
+            if (!empty($inputs['tags'])) {
+                $tags = explode(' ', $inputs['tags']);
+                foreach ($tags as $tag) {
+                    if ($tag === '') {
+                        continue;
+                    }
+
+                    $tag = Tag::firstOrCreate(['name' => $tag]);
+                    $tagIds[] = $tag->id;
+                }
+            }
+            $ejaculation->tags()->sync($tagIds);
+
+            return $ejaculation;
+        });
 
         if (!empty($ejaculation->link)) {
             event(new LinkDiscovered($ejaculation->link));
@@ -81,41 +116,55 @@ class EjaculationController extends Controller
 
     public function show($id)
     {
-        $ejaculation = Ejaculation::findOrFail($id);
+        $ejaculation = Ejaculation::where('id', $id)
+            ->withLikes()
+            ->firstOrFail();
         $user = User::findOrFail($ejaculation->user_id);
 
-        // 1つ前のチェックインからの経過時間を求める
-        $previousEjaculation = Ejaculation::select('ejaculated_date')
-            ->where('user_id', $ejaculation->user_id)
-            ->where('ejaculated_date', '<', $ejaculation->ejaculated_date)
-            ->orderByDesc('ejaculated_date')
-            ->first();
-        if (!empty($previousEjaculation)) {
-            $ejaculatedSpan = $ejaculation->ejaculated_date
-                ->diff($previousEjaculation->ejaculated_date)
-                ->format('%a日 %h時間 %i分');
-        } else {
-            $ejaculatedSpan = null;
-        }
-
-        return view('ejaculation.show')->with(compact('user', 'ejaculation', 'ejaculatedSpan'));
+        return view('ejaculation.show')->with(compact('user', 'ejaculation'));
     }
 
-    public function edit($id)
+    public function edit(Request $request, $id)
     {
         $ejaculation = Ejaculation::findOrFail($id);
 
-        return view('ejaculation.edit')->with(compact('ejaculation'));
+        $this->authorize('edit', $ejaculation);
+
+        if (old('tags') === null) {
+            $tags = $ejaculation->tags->pluck('name');
+        } else {
+            $tags = old('tags');
+            if (!empty($tags)) {
+                $tags = explode(' ', $tags);
+            }
+        }
+
+        $errors = $request->session()->get('errors');
+        $initialState = [
+            'mode' => 'update',
+            'fields' => [
+                'date' => old('date') ?? $ejaculation->ejaculated_date->format('Y/m/d'),
+                'time' => old('time') ?? $ejaculation->ejaculated_date->format('H:i'),
+                'link' => old('link') ?? $ejaculation->link,
+                'tags' => $tags,
+                'note' => old('note') ?? $ejaculation->note,
+                'is_private' => is_bool(old('is_private')) ? old('is_private') : $ejaculation->is_private,
+                'is_too_sensitive' => is_bool(old('is_too_sensitive')) ? old('is_too_sensitive') : $ejaculation->is_too_sensitive,
+                'discard_elapsed_time' => is_bool(old('discard_elapsed_time')) ? old('discard_elapsed_time') : $ejaculation->discard_elapsed_time,
+            ],
+            'errors' => isset($errors) ? $errors->getMessages() : null
+        ];
+
+        return view('ejaculation.edit')->with(compact('ejaculation', 'initialState'));
     }
 
     public function update(Request $request, $id)
     {
         $ejaculation = Ejaculation::findOrFail($id);
 
+        $this->authorize('edit', $ejaculation);
+
         $inputs = $request->all();
-        if ($request->has('note')) {
-            $inputs['note'] = str_replace(["\r\n", "\r"], "\n", $inputs['note']);
-        }
 
         $validator = Validator::make($inputs, [
             'date' => 'required|date_format:Y/m/d',
@@ -137,22 +186,30 @@ class EjaculationController extends Controller
             return redirect()->route('checkin.edit', ['id' => $id])->withErrors($validator)->withInput();
         }
 
-        $ejaculation->fill([
-            'ejaculated_date' => Carbon::createFromFormat('Y/m/d H:i', $inputs['date'] . ' ' . $inputs['time']),
-            'note' => $inputs['note'] ?? '',
-            'link' => $inputs['link'] ?? '',
-            'is_private' => $request->has('is_private') ?? false
-        ])->save();
+        DB::transaction(function () use ($ejaculation, $request, $inputs) {
+            $ejaculation->fill([
+                'ejaculated_date' => Carbon::createFromFormat('Y/m/d H:i', $inputs['date'] . ' ' . $inputs['time']),
+                'note' => $inputs['note'] ?? '',
+                'link' => $inputs['link'] ?? '',
+                'is_private' => $request->has('is_private') ?? false,
+                'is_too_sensitive' => $request->has('is_too_sensitive') ?? false,
+                'discard_elapsed_time' => $request->has('discard_elapsed_time') ?? false,
+            ])->save();
 
-        $tagIds = [];
-        if (!empty($inputs['tags'])) {
-            $tags = explode(' ', $inputs['tags']);
-            foreach ($tags as $tag) {
-                $tag = Tag::firstOrCreate(['name' => $tag]);
-                $tagIds[] = $tag->id;
+            $tagIds = [];
+            if (!empty($inputs['tags'])) {
+                $tags = explode(' ', $inputs['tags']);
+                foreach ($tags as $tag) {
+                    if ($tag === '') {
+                        continue;
+                    }
+
+                    $tag = Tag::firstOrCreate(['name' => $tag]);
+                    $tagIds[] = $tag->id;
+                }
             }
-        }
-        $ejaculation->tags()->sync($tagIds);
+            $ejaculation->tags()->sync($tagIds);
+        });
 
         if (!empty($ejaculation->link)) {
             event(new LinkDiscovered($ejaculation->link));
@@ -164,10 +221,21 @@ class EjaculationController extends Controller
     public function destroy($id)
     {
         $ejaculation = Ejaculation::findOrFail($id);
+
+        $this->authorize('edit', $ejaculation);
+
         $user = User::findOrFail($ejaculation->user_id);
-        $ejaculation->tags()->detach();
-        $ejaculation->delete();
+
+        DB::transaction(function () use ($ejaculation) {
+            $ejaculation->tags()->detach();
+            $ejaculation->delete();
+        });
 
         return redirect()->route('user.profile', ['name' => $user->name])->with('status', '削除しました。');
     }
+
+    public function tools()
+    {
+        return view('ejaculation.tools');
+    }
 }

app/Http/Controllers/HomeController.php

@@ -66,10 +66,13 @@ SQL
                 ->where('users.is_protected', false)
                 ->where('ejaculations.is_private', false)
                 ->where('ejaculations.link', '<>', '')
+                ->where('ejaculations.ejaculated_date', '<=', Carbon::now())
                 ->orderBy('ejaculations.ejaculated_date', 'desc')
                 ->select('ejaculations.*')
                 ->with('user', 'tags')
-                ->take(10)
+                ->withLikes()
+                ->visibleToTimeline()
+                ->take(21)
                 ->get();
 
             return view('home')->with(compact('informations', 'categories', 'globalEjaculationCounts', 'publicLinkedEjaculations'));

app/Http/Controllers/SearchController.php

@@ -4,23 +4,41 @@ namespace App\Http\Controllers;
 
 use App\Ejaculation;
 use App\Tag;
+use App\Utilities\Formatter;
 use Illuminate\Http\Request;
+use Illuminate\Support\Facades\Auth;
 
 class SearchController extends Controller
 {
+    /** @var Formatter */
+    private $formatter;
+
+    public function __construct(Formatter $formatter)
+    {
+        $this->formatter = $formatter;
+    }
+
     public function index(Request $request)
     {
         $inputs = $request->validate([
             'q' => 'required'
         ]);
 
+        $q = $this->normalizeQuery($inputs['q']);
         $results = Ejaculation::query()
-            ->whereHas('tags', function ($query) use ($inputs) {
-                $query->where('name', 'like', "%{$inputs['q']}%");
+            ->whereHas('tags', function ($query) use ($q) {
+                $query->where('normalized_name', 'like', "%{$q}%");
+            })
+            ->whereHas('user', function ($query) {
+                $query->where('is_protected', false);
+                if (Auth::check()) {
+                    $query->orWhere('id', Auth::id());
+                }
             })
             ->where('is_private', false)
             ->orderBy('ejaculated_date', 'desc')
             ->with(['user', 'tags'])
+            ->withLikes()
             ->paginate(20)
             ->appends($inputs);
 
@@ -33,11 +51,17 @@ class SearchController extends Controller
             'q' => 'required'
         ]);
 
+        $q = $this->normalizeQuery($inputs['q']);
         $results = Tag::query()
-            ->where('name', 'like', "%{$inputs['q']}%")
+            ->where('normalized_name', 'like', "%{$q}%")
             ->paginate(50)
             ->appends($inputs);
 
         return view('search.relatedTag')->with(compact('inputs', 'results'));
     }
+
+    private function normalizeQuery(string $query): string
+    {
+        return $this->formatter->normalizeTagName($query);
+    }
 }

app/Http/Controllers/SettingController.php

@@ -2,9 +2,19 @@
 
 namespace App\Http\Controllers;
 
+use App\CheckinWebhook;
+use App\DeactivatedUser;
+use App\Ejaculation;
+use App\Exceptions\CsvImportException;
+use App\Services\CheckinCsvExporter;
+use App\Services\CheckinCsvImporter;
 use Illuminate\Http\Request;
 use Illuminate\Support\Facades\Auth;
+use Illuminate\Support\Facades\DB;
+use Illuminate\Support\Facades\Hash;
 use Illuminate\Support\Facades\Validator;
+use Illuminate\Validation\Rule;
+use Illuminate\Validation\ValidationException;
 
 class SettingController extends Controller
 {
@@ -18,10 +28,18 @@ class SettingController extends Controller
         $inputs = $request->all();
         $validator = Validator::make($inputs, [
             'display_name' => 'required|string|max:20',
+            'email' => [
+                'required',
+                'string',
+                'email',
+                'max:255',
+                Rule::unique('users')->ignore(Auth::user()->email, 'email')
+            ],
             'bio' => 'nullable|string|max:160',
             'url' => 'nullable|url|max:2000'
         ], [], [
             'display_name' => '名前',
+            'email' => 'メールアドレス',
             'bio' => '自己紹介',
             'url' => 'URL'
         ]);
@@ -32,6 +50,7 @@ class SettingController extends Controller
 
         $user = Auth::user();
         $user->display_name = $inputs['display_name'];
+        $user->email = $inputs['email'];
         $user->bio = $inputs['bio'] ?? '';
         $user->url = $inputs['url'] ?? '';
         $user->save();
@@ -46,16 +65,175 @@ class SettingController extends Controller
 
     public function updatePrivacy(Request $request)
     {
-        $inputs = $request->all(['is_protected', 'accept_analytics']);
+        $inputs = $request->all(['is_protected', 'accept_analytics', 'private_likes']);
 
         $user = Auth::user();
         $user->is_protected = $inputs['is_protected'] ?? false;
         $user->accept_analytics = $inputs['accept_analytics'] ?? false;
+        $user->private_likes = $inputs['private_likes'] ?? false;
         $user->save();
 
         return redirect()->route('setting.privacy')->with('status', 'プライバシー設定を更新しました。');
     }
 
+    public function webhooks()
+    {
+        $webhooks = Auth::user()->checkinWebhooks;
+        $webhooksLimit = CheckinWebhook::PER_USER_LIMIT;
+
+        return view('setting.webhooks')->with(compact('webhooks', 'webhooksLimit'));
+    }
+
+    public function storeWebhooks(Request $request)
+    {
+        $validated = $request->validate([
+            'name' => [
+                'required',
+                'string',
+                'max:255',
+                Rule::unique('checkin_webhooks', 'name')->where(function ($query) {
+                    return $query->where('user_id', Auth::id());
+                })
+            ]
+        ], [], [
+            'name' => '名前'
+        ]);
+
+        if (Auth::user()->checkinWebhooks()->count() >= CheckinWebhook::PER_USER_LIMIT) {
+            return redirect()->route('setting.webhooks')
+                ->with('status', CheckinWebhook::PER_USER_LIMIT . '件以上のWebhookを作成することはできません。');
+        }
+
+        Auth::user()->checkinWebhooks()->create($validated);
+
+        return redirect()->route('setting.webhooks')->with('status', '作成しました。');
+    }
+
+    public function destroyWebhooks(CheckinWebhook $webhook)
+    {
+        $webhook->delete();
+
+        return redirect()->route('setting.webhooks')->with('status', '削除しました。');
+    }
+
+    public function import()
+    {
+        return view('setting.import');
+    }
+
+    public function storeImport(Request $request)
+    {
+        $validated = $request->validate([
+            'file' => 'required|file'
+        ], [], [
+            'file' => 'ファイル'
+        ]);
+
+        $file = $request->file('file');
+        if (!$file->isValid()) {
+            return redirect()->route('setting.import')->withErrors(['file' => 'ファイルのアップロードに失敗しました。']);
+        }
+
+        try {
+            set_time_limit(0);
+
+            $importer = new CheckinCsvImporter(Auth::user(), $file->path());
+            $imported = $importer->execute();
+
+            return redirect()->route('setting.import')->with('status', "{$imported}件のインポートに性交しました。");
+        } catch (CsvImportException $e) {
+            return redirect()->route('setting.import')->with('import_errors', $e->getErrors());
+        }
+    }
+
+    public function destroyImport()
+    {
+        Auth::user()
+            ->ejaculations()
+            ->where('ejaculations.source', Ejaculation::SOURCE_CSV)
+            ->delete();
+
+        return redirect()->route('setting.import')->with('status', '削除が完了しました。');
+    }
+
+    public function export()
+    {
+        return view('setting.export');
+    }
+
+    public function exportToCsv(Request $request)
+    {
+        $validated = $request->validate([
+            'charset' => ['required', Rule::in(['utf8', 'sjis'])]
+        ]);
+
+        $charsets = [
+            'utf8' => 'UTF-8',
+            'sjis' => 'SJIS-win'
+        ];
+
+        $filename = tempnam(sys_get_temp_dir(), 'tissue_export_tmp_');
+        try {
+            // 気休め
+            set_time_limit(0);
+
+            $exporter = new CheckinCsvExporter(Auth::user(), $filename, $charsets[$validated['charset']]);
+            $exporter->execute();
+        } catch (\Throwable $e) {
+            unlink($filename);
+            throw $e;
+        }
+
+        return response()
+            ->download($filename, 'TissueCheckin_' . date('Y-m-d_H-i-s') . '.csv')
+            ->deleteFileAfterSend(true);
+    }
+
+    public function deactivate()
+    {
+        return view('setting.deactivate');
+    }
+
+    public function destroyUser(Request $request)
+    {
+        // パスワードチェック
+        $validated = $request->validate([
+            'password' => 'required|string'
+        ]);
+
+        if (!Hash::check($validated['password'], Auth::user()->getAuthPassword())) {
+            throw ValidationException::withMessages([
+                'password' => 'パスワードが正しくありません。'
+            ]);
+        }
+
+        // データの削除
+        set_time_limit(0);
+        DB::transaction(function () {
+            $user = Auth::user();
+
+            // 関連レコードの削除
+            // TODO: 別にDELETE文相当のクエリを一発発行するだけでもいい？
+            foreach ($user->ejaculations as $ejaculation) {
+                $ejaculation->delete();
+            }
+            foreach ($user->likes as $like) {
+                $like->delete();
+            }
+
+            // 先にログアウトしないとユーザーは消せない
+            Auth::logout();
+
+            // ユーザーの削除
+            $user->delete();
+
+            // ユーザー名履歴に追記
+            DeactivatedUser::create(['name' => $user->name]);
+        });
+
+        return view('setting.deactivated');
+    }
+
     // ( ◠‿◠ )☛ここに気づいたか・・・消えてもらう ▂▅▇█▓▒░(’ω’)░▒▓█▇▅▂うわあああああああ
 //    public function password()
 //    {

app/Http/Controllers/TagController.php

@@ -0,0 +1,37 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use App\Tag;
+use Illuminate\Http\Request;
+use Illuminate\Support\Facades\Auth;
+use Illuminate\Support\Facades\DB;
+
+class TagController extends Controller
+{
+    public function index()
+    {
+        $tags = Tag::select(DB::raw(
+            <<<'SQL'
+tags.name,
+count(*) AS "checkins_count"
+SQL
+        ))
+            ->join('ejaculation_tag', 'tags.id', '=', 'ejaculation_tag.tag_id')
+            ->join('ejaculations', 'ejaculations.id', '=', 'ejaculation_tag.ejaculation_id')
+            ->join('users', 'users.id', '=', 'ejaculations.user_id')
+            ->where('ejaculations.is_private', false)
+            ->where(function ($query) {
+                $query->where('users.is_protected', false);
+                if (Auth::check()) {
+                    $query->orWhere('users.id', Auth::id());
+                }
+            })
+            ->groupBy('tags.name')
+            ->orderByDesc('checkins_count')
+            ->orderBy('tags.name')
+            ->paginate(100);
+
+        return view('tag.index', compact('tags'));
+    }
+}

app/Http/Controllers/TimelineController.php

@@ -0,0 +1,27 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use App\Ejaculation;
+use Illuminate\Http\Request;
+use Illuminate\Support\Carbon;
+
+class TimelineController extends Controller
+{
+    public function showPublic()
+    {
+        $ejaculations = Ejaculation::join('users', 'users.id', '=', 'ejaculations.user_id')
+            ->where('users.is_protected', false)
+            ->where('ejaculations.is_private', false)
+            ->where('ejaculations.link', '<>', '')
+            ->where('ejaculations.ejaculated_date', '<=', Carbon::now())
+            ->orderBy('ejaculations.ejaculated_date', 'desc')
+            ->select('ejaculations.*')
+            ->with('user', 'tags')
+            ->withLikes()
+            ->visibleToTimeline()
+            ->paginate(21);
+
+        return view('timeline.public')->with(compact('ejaculations'));
+    }
+}

app/Http/Controllers/UserController.php

@@ -5,13 +5,18 @@ namespace App\Http\Controllers;
 use App\Ejaculation;
 use App\User;
 use Carbon\Carbon;
+use Carbon\CarbonInterface;
 use Illuminate\Http\Request;
 use Illuminate\Support\Facades\Auth;
 use Illuminate\Support\Facades\DB;
+use Validator;
 
 class UserController extends Controller
 {
-    //
+    public function redirectMypage()
+    {
+        return redirect()->route('user.profile', ['name' => auth()->user()->name]);
+    }
 
     public function profile($name)
     {
@@ -23,21 +28,26 @@ class UserController extends Controller
         // チェックインの取得
         $query = Ejaculation::select(DB::raw(
             <<<'SQL'
-id,
+ejaculations.id,
 ejaculated_date,
 note,
 is_private,
+is_too_sensitive,
 link,
-to_char(lead(ejaculated_date, 1, NULL) OVER (ORDER BY ejaculated_date DESC), 'YYYY/MM/DD HH24:MI') AS before_date,
-to_char(ejaculated_date - (lead(ejaculated_date, 1, NULL) OVER (ORDER BY ejaculated_date DESC)), 'FMDDD日 FMHH24時間 FMMI分') AS ejaculated_span
+source,
+discard_elapsed_time,
+to_char(before_dates.before_date, 'YYYY/MM/DD HH24:MI') AS before_date,
+to_char(ejaculated_date - before_dates.before_date, 'FMDDD日 FMHH24時間 FMMI分') AS ejaculated_span
 SQL
         ))
+            ->joinSub($this->queryBeforeEjaculatedDates(), 'before_dates', 'before_dates.id', '=', 'ejaculations.id')
             ->where('user_id', $user->id);
         if (!Auth::check() || $user->id !== Auth::id()) {
             $query = $query->where('is_private', false);
         }
         $ejaculations = $query->orderBy('ejaculated_date', 'desc')
             ->with('tags')
+            ->withLikes()
             ->paginate(20);
 
         // よく使っているタグ
@@ -54,7 +64,18 @@ SQL
             ->limit(10)
             ->get();
 
-        return view('user.profile')->with(compact('user', 'ejaculations', 'tags'));
+        // シコ草
+        $countByDayQuery = $this->countEjaculationByDay($user)
+            ->where('ejaculated_date', '>=', now()->startOfMonth()->subMonths(9))
+            ->where('ejaculated_date', '<', now()->addMonth()->startOfMonth())
+            ->get();
+        $countByDay = [];
+        foreach ($countByDayQuery as $data) {
+            $date = Carbon::createFromFormat('Y/m/d', $data->date);
+            $countByDay[$date->timestamp] = $data->count;
+        }
+
+        return view('user.profile')->with(compact('user', 'ejaculations', 'tags', 'countByDay'));
     }
 
     public function stats($name)
@@ -64,15 +85,161 @@ SQL
             abort(404);
         }
 
-        $groupByDay = Ejaculation::select(DB::raw(
+        $availableMonths = $this->makeStatsAvailableMonths($user);
+        $graphData = $this->makeGraphData($user);
+
+        return view('user.stats.index')->with(compact('user', 'graphData', 'availableMonths'));
+    }
+
+    public function statsYearly($name, $year)
+    {
+        $user = User::where('name', $name)->first();
+        if (empty($user)) {
+            abort(404);
+        }
+
+        $validator = Validator::make(compact('year'), [
+            'year' => 'required|date_format:Y'
+        ]);
+        if ($validator->fails()) {
+            return redirect()->route('user.stats', compact('name'));
+        }
+
+        $availableMonths = $this->makeStatsAvailableMonths($user);
+        if (!isset($availableMonths[$year])) {
+            return redirect()->route('user.stats', compact('name'));
+        }
+
+        $graphData = $this->makeGraphData(
+            $user,
+            Carbon::createFromDate($year, 1, 1, config('app.timezone'))->startOfDay(),
+            Carbon::createFromDate($year, 1, 1, config('app.timezone'))->addYear()->startOfDay()
+        );
+
+        return view('user.stats.yearly')
+            ->with(compact('user', 'graphData', 'availableMonths'))
+            ->with('currentYear', (int) $year);
+    }
+
+    public function statsMonthly($name, $year, $month)
+    {
+        $user = User::where('name', $name)->first();
+        if (empty($user)) {
+            abort(404);
+        }
+
+        $validator = Validator::make(compact('year', 'month'), [
+            'year' => 'required|date_format:Y',
+            'month' => 'required|date_format:m'
+        ]);
+        if ($validator->fails()) {
+            return redirect()->route('user.stats.yearly', compact('name', 'year'));
+        }
+
+        $availableMonths = $this->makeStatsAvailableMonths($user);
+        if (!isset($availableMonths[$year]) || !in_array($month, $availableMonths[$year], false)) {
+            return redirect()->route('user.stats.yearly', compact('name', 'year'));
+        }
+
+        $graphData = $this->makeGraphData(
+            $user,
+            Carbon::createFromDate($year, $month, 1, config('app.timezone'))->startOfDay(),
+            Carbon::createFromDate($year, $month, 1, config('app.timezone'))->addMonth()->startOfDay()
+        );
+
+        return view('user.stats.monthly')
+            ->with(compact('user', 'graphData', 'availableMonths'))
+            ->with('currentYear', (int) $year)
+            ->with('currentMonth', (int) $month);
+    }
+
+    public function okazu($name)
+    {
+        $user = User::where('name', $name)->first();
+        if (empty($user)) {
+            abort(404);
+        }
+
+        // チェックインの取得
+        $query = Ejaculation::select(DB::raw(
             <<<'SQL'
-to_char(ejaculated_date, 'YYYY/MM/DD') AS "date",
-count(*) AS "count"
+ejaculations.id,
+ejaculated_date,
+note,
+is_private,
+is_too_sensitive,
+link,
+source,
+discard_elapsed_time,
+to_char(before_dates.before_date, 'YYYY/MM/DD HH24:MI') AS before_date,
+to_char(ejaculated_date - before_dates.before_date, 'FMDDD日 FMHH24時間 FMMI分') AS ejaculated_span
 SQL
         ))
+            ->joinSub($this->queryBeforeEjaculatedDates(), 'before_dates', 'before_dates.id', '=', 'ejaculations.id')
             ->where('user_id', $user->id)
-            ->groupBy(DB::raw("to_char(ejaculated_date, 'YYYY/MM/DD')"))
-            ->orderBy(DB::raw("to_char(ejaculated_date, 'YYYY/MM/DD')"))
+            ->where('link', '<>', '');
+        if (!Auth::check() || $user->id !== Auth::id()) {
+            $query = $query->where('is_private', false);
+        }
+        $ejaculations = $query->orderBy('ejaculated_date', 'desc')
+            ->with('tags')
+            ->withLikes()
+            ->paginate(20);
+
+        return view('user.profile')->with(compact('user', 'ejaculations'));
+    }
+
+    public function likes($name)
+    {
+        $user = User::where('name', $name)->first();
+        if (empty($user)) {
+            abort(404);
+        }
+
+        $likes = $user->likes()
+            ->orderBy('created_at', 'desc')
+            ->with('ejaculation.user', 'ejaculation.tags')
+            ->whereHas('ejaculation', function ($query) {
+                $query->where('user_id', Auth::id())
+                    ->orWhere('is_private', false);
+            })
+            ->paginate(20);
+
+        return view('user.likes')->with(compact('user', 'likes'));
+    }
+
+    private function makeStatsAvailableMonths(User $user): array
+    {
+        $availableMonths = [];
+        $oldest = $user->ejaculations()->orderBy('ejaculated_date')->first();
+        if (isset($oldest)) {
+            $oldestMonth = $oldest->ejaculated_date->startOfMonth();
+            $currentMonth = now()->startOfMonth();
+            for ($month = $currentMonth; $oldestMonth <= $currentMonth; $month = $month->subMonth()) {
+                if (!isset($availableMonths[$month->year])) {
+                    $availableMonths[$month->year] = [];
+                }
+                $availableMonths[$month->year][] = $month->month;
+            }
+        }
+
+        return $availableMonths;
+    }
+
+    private function makeGraphData(User $user, CarbonInterface $dateSince = null, CarbonInterface $dateUntil = null): array
+    {
+        if ($dateUntil === null) {
+            $dateUntil = now()->addMonth()->startOfMonth();
+        }
+        $dateCondition = [
+            ['ejaculated_date', '<', $dateUntil],
+        ];
+        if ($dateSince !== null) {
+            $dateCondition[] = ['ejaculated_date', '>=', $dateSince];
+        }
+
+        $groupByDay = $this->countEjaculationByDay($user)
+            ->where($dateCondition)
             ->get();
 
         $groupByHour = Ejaculation::select(DB::raw(
@@ -82,6 +249,7 @@ count(*) AS "count"
 SQL
         ))
             ->where('user_id', $user->id)
+            ->where($dateCondition)
             ->groupBy(DB::raw("to_char(ejaculated_date, 'HH24')"))
             ->orderBy(DB::raw('1'))
             ->get();
@@ -101,13 +269,6 @@ SQL
             }
         }
 
-        // 月間グラフ用の配列初期化
-        $month = Carbon::now()->firstOfMonth()->subMonth(11); // 直近12ヶ月
-        for ($i = 0; $i < 12; $i++) {
-            $monthlySum[$month->format('Y/m')] = 0;
-            $month->addMonth();
-        }
-
         foreach ($groupByDay as $data) {
             $date = Carbon::createFromFormat('Y/m/d', $data->date);
             $yearAndMonth = $date->format('Y/m');
@@ -115,9 +276,7 @@ SQL
             $dailySum[$date->timestamp] = $data->count;
             $yearlySum[$date->year] += $data->count;
             $dowSum[$date->dayOfWeek] += $data->count;
-            if (isset($monthlySum[$yearAndMonth])) {
-                $monthlySum[$yearAndMonth] += $data->count;
-            }
+            $monthlySum[$yearAndMonth] = ($monthlySum[$yearAndMonth] ?? 0) + $data->count;
         }
 
         foreach ($groupByHour as $data) {
@@ -125,37 +284,37 @@ SQL
             $hourlySum[$hour] += $data->count;
         }
 
-        return view('user.stats')->with(compact('user', 'dailySum', 'monthlySum', 'yearlySum', 'dowSum', 'hourlySum'));
+        return [
+            'dailySum' => $dailySum,
+            'dowSum' => $dowSum,
+            'monthlySum' => $monthlySum,
+            'yearlyKey' => array_keys($yearlySum),
+            'yearlySum' => array_values($yearlySum),
+            'hourlyKey' => array_keys($hourlySum),
+            'hourlySum' => array_values($hourlySum),
+        ];
     }
 
-    public function okazu($name)
+    private function countEjaculationByDay(User $user)
     {
-        $user = User::where('name', $name)->first();
-        if (empty($user)) {
-            abort(404);
-        }
-
-        // チェックインの取得
-        $query = Ejaculation::select(DB::raw(
+        return Ejaculation::select(DB::raw(
             <<<'SQL'
-id,
-ejaculated_date,
-note,
-is_private,
-link,
-to_char(lead(ejaculated_date, 1, NULL) OVER (ORDER BY ejaculated_date DESC), 'YYYY/MM/DD HH24:MI') AS before_date,
-to_char(ejaculated_date - (lead(ejaculated_date, 1, NULL) OVER (ORDER BY ejaculated_date DESC)), 'FMDDD日 FMHH24時間 FMMI分') AS ejaculated_span
+to_char(ejaculated_date, 'YYYY/MM/DD') AS "date",
+count(*) AS "count"
 SQL
         ))
             ->where('user_id', $user->id)
-            ->where('link', '<>', '');
-        if (!Auth::check() || $user->id !== Auth::id()) {
-            $query = $query->where('is_private', false);
-        }
-        $ejaculations = $query->orderBy('ejaculated_date', 'desc')
-            ->with('tags')
-            ->paginate(20);
+            ->groupBy(DB::raw("to_char(ejaculated_date, 'YYYY/MM/DD')"))
+            ->orderBy(DB::raw("to_char(ejaculated_date, 'YYYY/MM/DD')"));
+    }
 
-        return view('user.profile')->with(compact('user', 'ejaculations'));
+    private function queryBeforeEjaculatedDates()
+    {
+        return DB::table('ejaculations')->selectRaw(
+            <<<'SQL'
+id,
+(select ejaculated_date from ejaculations e2 where e2.ejaculated_date < ejaculations.ejaculated_date and e2.user_id = ejaculations.user_id order by e2.ejaculated_date desc limit 1) AS before_date
+SQL
+        );
     }
 }

app/Http/Kernel.php

@@ -14,7 +14,8 @@ class Kernel extends HttpKernel
      * @var array
      */
     protected $middleware = [
-        \Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode::class,
+        \App\Http\Middleware\TrustProxies::class,
+        \App\Http\Middleware\CheckForMaintenanceMode::class,
         \Illuminate\Foundation\Http\Middleware\ValidatePostSize::class,
         \App\Http\Middleware\TrimStrings::class,
         \Illuminate\Foundation\Http\Middleware\ConvertEmptyStringsToNull::class,
@@ -34,12 +35,20 @@ class Kernel extends HttpKernel
             \Illuminate\View\Middleware\ShareErrorsFromSession::class,
             \App\Http\Middleware\VerifyCsrfToken::class,
             \Illuminate\Routing\Middleware\SubstituteBindings::class,
+            \App\Http\Middleware\NormalizeLineEnding::class,
         ],
 
         'api' => [
-            'throttle:60,1',
-            'bindings',
+            \App\Http\Middleware\EnforceJson::class,
+            \Illuminate\Routing\Middleware\SubstituteBindings::class,
         ],
+
+        'stateful' => [
+            \App\Http\Middleware\EncryptCookies::class,
+            \Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse::class,
+            \Illuminate\Session\Middleware\StartSession::class,
+            \App\Http\Middleware\VerifyCsrfToken::class,
+        ]
     ];
 
     /**
@@ -50,11 +59,33 @@ class Kernel extends HttpKernel
      * @var array
      */
     protected $routeMiddleware = [
-        'auth' => \Illuminate\Auth\Middleware\Authenticate::class,
+        'auth' => \App\Http\Middleware\Authenticate::class,
         'auth.basic' => \Illuminate\Auth\Middleware\AuthenticateWithBasicAuth::class,
         'bindings' => \Illuminate\Routing\Middleware\SubstituteBindings::class,
+        'cache.headers' => \Illuminate\Http\Middleware\SetCacheHeaders::class,
         'can' => \Illuminate\Auth\Middleware\Authorize::class,
         'guest' => \App\Http\Middleware\RedirectIfAuthenticated::class,
+        'password.confirm' => \Illuminate\Auth\Middleware\RequirePassword::class,
+        'signed' => \Illuminate\Routing\Middleware\ValidateSignature::class,
         'throttle' => \Illuminate\Routing\Middleware\ThrottleRequests::class,
+        'verified' => \Illuminate\Auth\Middleware\EnsureEmailIsVerified::class,
+    ];
+
+    /**
+     * The priority-sorted list of middleware.
+     *
+     * This forces non-global middleware to always be in the given order.
+     *
+     * @var array
+     */
+    protected $middlewarePriority = [
+        \App\Http\Middleware\EncryptCookies::class,
+        \Illuminate\Session\Middleware\StartSession::class,
+        \Illuminate\View\Middleware\ShareErrorsFromSession::class,
+        \App\Http\Middleware\Authenticate::class,
+        \Illuminate\Routing\Middleware\ThrottleRequests::class,
+        \Illuminate\Session\Middleware\AuthenticateSession::class,
+        \Illuminate\Routing\Middleware\SubstituteBindings::class,
+        \Illuminate\Auth\Middleware\Authorize::class,
     ];
 }

app/Http/Middleware/Authenticate.php

@@ -0,0 +1,21 @@
+<?php
+
+namespace App\Http\Middleware;
+
+use Illuminate\Auth\Middleware\Authenticate as Middleware;
+
+class Authenticate extends Middleware
+{
+    /**
+     * Get the path the user should be redirected to when they are not authenticated.
+     *
+     * @param  \Illuminate\Http\Request  $request
+     * @return string|null
+     */
+    protected function redirectTo($request)
+    {
+        if (! $request->expectsJson()) {
+            return route('login');
+        }
+    }
+}

app/Http/Middleware/CheckForMaintenanceMode.php

@@ -0,0 +1,17 @@
+<?php
+
+namespace App\Http\Middleware;
+
+use Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode as Middleware;
+
+class CheckForMaintenanceMode extends Middleware
+{
+    /**
+     * The URIs that should be reachable while maintenance mode is enabled.
+     *
+     * @var array
+     */
+    protected $except = [
+        //
+    ];
+}

app/Http/Middleware/EnforceJson.php

@@ -0,0 +1,25 @@
+<?php
+
+namespace App\Http\Middleware;
+
+use Closure;
+
+/**
+ * Request headerに Accept: application/json を上書きする。APIエンドポイント用。
+ */
+class EnforceJson
+{
+    /**
+     * Handle an incoming request.
+     *
+     * @param  \Illuminate\Http\Request  $request
+     * @param  \Closure  $next
+     * @return mixed
+     */
+    public function handle($request, Closure $next)
+    {
+        $request->headers->set('Accept', 'application/json');
+
+        return $next($request);
+    }
+}

app/Http/Middleware/NormalizeLineEnding.php

@@ -0,0 +1,30 @@
+<?php
+
+namespace App\Http\Middleware;
+
+use Closure;
+
+/**
+ * リクエスト内の改行コードを正規化する。
+ * @package App\Http\Middleware
+ */
+class NormalizeLineEnding
+{
+    /**
+     * Handle an incoming request.
+     *
+     * @param  \Illuminate\Http\Request  $request
+     * @param  \Closure  $next
+     * @return mixed
+     */
+    public function handle($request, Closure $next)
+    {
+        $newInput = [];
+        foreach ($request->input() as $key => $value) {
+            $newInput[$key] = str_replace(["\r\n", "\r"], "\n", $value);
+        }
+        $request->replace($newInput);
+
+        return $next($request);
+    }
+}

app/Http/Middleware/TrustProxies.php

@@ -0,0 +1,23 @@
+<?php
+
+namespace App\Http\Middleware;
+
+use Fideloper\Proxy\TrustProxies as Middleware;
+use Illuminate\Http\Request;
+
+class TrustProxies extends Middleware
+{
+    /**
+     * The trusted proxies for this application.
+     *
+     * @var array|string
+     */
+    protected $proxies = '**';
+
+    /**
+     * The headers that should be used to detect proxies.
+     *
+     * @var int
+     */
+    protected $headers = Request::HEADER_X_FORWARDED_ALL;
+}

app/Http/Middleware/VerifyCsrfToken.php

@@ -6,6 +6,13 @@ use Illuminate\Foundation\Http\Middleware\VerifyCsrfToken as BaseVerifier;
 
 class VerifyCsrfToken extends BaseVerifier
 {
+    /**
+     * Indicates whether the XSRF-TOKEN cookie should be set on the response.
+     *
+     * @var bool
+     */
+    protected $addHttpCookie = true;
+
     /**
      * The URIs that should be excluded from CSRF verification.
      *

app/Http/Requests/AdminInfoStoreRequest.php

@@ -0,0 +1,35 @@
+<?php
+
+namespace App\Http\Requests;
+
+use App\Information;
+use Illuminate\Foundation\Http\FormRequest;
+use Illuminate\Validation\Rule;
+
+class AdminInfoStoreRequest extends FormRequest
+{
+    /**
+     * Determine if the user is authorized to make this request.
+     *
+     * @return bool
+     */
+    public function authorize()
+    {
+        return true;
+    }
+
+    /**
+     * Get the validation rules that apply to the request.
+     *
+     * @return array
+     */
+    public function rules()
+    {
+        return [
+            'category' => ['required', Rule::in(array_keys(Information::CATEGORIES))],
+            'pinned' => 'nullable|boolean',
+            'title' => 'required|string|max:255',
+            'content' => 'required|string|max:10000'
+        ];
+    }
+}

app/Http/Resources/EjaculationResource.php

@@ -0,0 +1,28 @@
+<?php
+
+namespace App\Http\Resources;
+
+use Illuminate\Http\Resources\Json\JsonResource;
+
+class EjaculationResource extends JsonResource
+{
+    /**
+     * Transform the resource into an array.
+     *
+     * @param  \Illuminate\Http\Request  $request
+     * @return array
+     */
+    public function toArray($request)
+    {
+        return [
+            'id' => $this->id,
+            'checked_in_at' => $this->ejaculated_date->format(\DateTime::ATOM),
+            'note' => $this->note,
+            'link' => $this->link,
+            'tags' => $this->tags->pluck('name'),
+            'source' => $this->source,
+            'is_private' => $this->is_private,
+            'is_too_sensitive' => $this->is_too_sensitive,
+        ];
+    }
+}

app/Http/ViewComposers/ProfileStatsComposer.php

@@ -19,6 +19,7 @@ class ProfileStatsComposer
         if (!$view->offsetExists('user')) {
             throw new \LogicException('View data "user" was not exist.');
         }
+        /** @var \App\User $user */
         $user = $view->offsetGet('user');
 
         // 現在のオナ禁セッションの経過時間
@@ -35,17 +36,44 @@ class ProfileStatsComposer
         }
 
         // 概況欄のデータ取得
+        $average = 0;
+        $divisor = 0;
+        $averageSources = DB::select(<<<'SQL'
+SELECT
+  extract(epoch from ejaculated_date - lead(ejaculated_date, 1, NULL) OVER (ORDER BY ejaculated_date DESC)) AS span,
+  discard_elapsed_time
+FROM
+  ejaculations
+WHERE
+  user_id = :user_id
+ORDER BY
+  ejaculated_date DESC
+LIMIT
+  30
+SQL
+            , ['user_id' => $user->id]);
+        foreach ($averageSources as $item) {
+            // 経過時間記録対象外のレコードがあったら、それより古いデータは平均の計算に加えない
+            if ($item->discard_elapsed_time) {
+                break;
+            }
+            $average += $item->span;
+            $divisor++;
+        }
+        if ($divisor > 0) {
+            $average /= $divisor;
+        }
+
         $summary = DB::select(<<<'SQL'
 SELECT
-  avg(span) AS average,
   max(span) AS longest,
   min(span) AS shortest,
-  sum(span) AS total_times,
-  count(*) AS total_checkins
+  sum(span) AS total_times
 FROM
   (
     SELECT
-      extract(epoch from ejaculated_date - lead(ejaculated_date, 1, NULL) OVER (ORDER BY ejaculated_date DESC)) AS span
+      extract(epoch from ejaculated_date - lead(ejaculated_date, 1, NULL) OVER (ORDER BY ejaculated_date DESC)) AS span,
+      discard_elapsed_time
     FROM
       ejaculations
     WHERE
@@ -53,9 +81,13 @@ FROM
     ORDER BY
       ejaculated_date DESC
   ) AS temp
+WHERE
+  discard_elapsed_time = FALSE
 SQL
             , ['user_id' => $user->id]);
 
-        $view->with(compact('latestEjaculation', 'currentSession', 'summary'));
+        $total = $user->ejaculations()->count();
+
+        $view->with(compact('latestEjaculation', 'currentSession', 'average', 'summary', 'total'));
     }
 }

app/Information.php

@@ -16,5 +16,9 @@ class Information extends Model
         3 => ['label' => 'メンテナンス', 'class' => 'badge-warning']
     ];
 
+    protected $fillable = [
+        'category', 'pinned', 'title', 'content'
+    ];
+
     protected $dates = ['deleted_at'];
 }

app/Like.php

@@ -0,0 +1,23 @@
+<?php
+
+namespace App;
+
+use Illuminate\Database\Eloquent\Model;
+use Staudenmeir\EloquentEagerLimit\HasEagerLimit;
+
+class Like extends Model
+{
+    use HasEagerLimit;
+
+    protected $fillable = ['user_id', 'ejaculation_id'];
+
+    public function user()
+    {
+        return $this->belongsTo(User::class);
+    }
+
+    public function ejaculation()
+    {
+        return $this->belongsTo(Ejaculation::class)->withLikes();
+    }
+}

app/Listeners/LinkCollector.php

@@ -3,31 +3,24 @@
 namespace App\Listeners;
 
 use App\Events\LinkDiscovered;
-use App\Metadata;
-use App\MetadataResolver\MetadataResolver;
-use App\Utilities\Formatter;
-use GuzzleHttp\Exception\TransferException;
+use App\MetadataResolver\DeniedHostException;
+use App\Services\MetadataResolveService;
 use Illuminate\Contracts\Queue\ShouldQueue;
 use Illuminate\Queue\InteractsWithQueue;
-use Illuminate\Support\Facades\Log;
 
 class LinkCollector
 {
-    /** @var Formatter */
-    private $formatter;
-    /** @var MetadataResolver */
-    private $metadataResolver;
+    /** @var MetadataResolveService */
+    private $metadataResolveService;
 
     /**
      * Create the event listener.
      *
-     * @param Formatter $formatter
-     * @param MetadataResolver $metadataResolver
+     * @param MetadataResolveService $metadataResolveService
      */
-    public function __construct(Formatter $formatter, MetadataResolver $metadataResolver)
+    public function __construct(MetadataResolveService $metadataResolveService)
     {
-        $this->formatter = $formatter;
-        $this->metadataResolver = $metadataResolver;
+        $this->metadataResolveService = $metadataResolveService;
     }
 
     /**
@@ -38,26 +31,13 @@ class LinkCollector
      */
     public function handle(LinkDiscovered $event)
     {
-        // URLの正規化
-        $url = $this->formatter->normalizeUrl($event->url);
-
-        // 無かったら取得
-        // TODO: ある程度古かったら再取得とかありだと思う
-        $metadata = Metadata::find($url);
-        if ($metadata == null || ($metadata->expires_at !== null && $metadata->expires_at < now())) {
-            try {
-                $resolved = $this->metadataResolver->resolve($url);
-                Metadata::updateOrCreate(['url' => $url], [
-                    'title' => $resolved->title,
-                    'description' => $resolved->description,
-                    'image' => $resolved->image,
-                    'expires_at' => $resolved->expires_at
-                ]);
-            } catch (TransferException $e) {
-                // 何らかの通信エラーによってメタデータの取得に失敗した時、とりあえずエラーログにURLを残す
-                Log::error(self::class . ': メタデータの取得に失敗 URL=' . $url);
-                report($e);
-            }
+        try {
+            $this->metadataResolveService->execute($event->url);
+        } catch (DeniedHostException $e) {
+            // ignored
+        } catch (\Exception $e) {
+            // 今のところこのイベントは同期実行されるので、上流をクラッシュさせないために雑catchする
+            report($e);
         }
     }
 }

app/Metadata.php

@@ -2,6 +2,8 @@
 
 namespace App;
 
+use Carbon\CarbonInterface;
+use GuzzleHttp\Exception\RequestException;
 use Illuminate\Database\Eloquent\Model;
 
 class Metadata extends Model
@@ -11,7 +13,68 @@ class Metadata extends Model
     protected $keyType = 'string';
 
     protected $fillable = ['url', 'title', 'description', 'image', 'expires_at'];
-    protected $visible = ['url', 'title', 'description', 'image', 'expires_at'];
+    protected $visible = ['url', 'title', 'description', 'image', 'expires_at', 'tags'];
 
-    protected $dates = ['created_at', 'updated_at', 'expires_at'];
+    protected $dates = ['created_at', 'updated_at', 'expires_at', 'error_at'];
+
+    public function tags()
+    {
+        return $this->belongsToMany(Tag::class)->withTimestamps();
+    }
+
+    public function needRefresh(): bool
+    {
+        return $this->isExpired() || $this->error_at !== null;
+    }
+
+    public function isExpired(): bool
+    {
+        return $this->expires_at !== null && $this->expires_at < now();
+    }
+
+    public function storeException(CarbonInterface $error_at, \Exception $exception): self