KTV/app/Jobs/ExportSqliteSongJob.php

<?php

namespace App\Jobs;

use App\Models\Song;
use App\Models\Artist;
use App\Models\User;
use App\Services\SqliteExportService;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;


class ExportSqliteSongJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    public $timeout = 600; // 可依資料量調整 timeout 秒數

    protected ?int $branchId;

    public function __construct(?int $branchId = null)
    {
        $this->branchId = $branchId;
    }

    public function handle()
    {
        $sqlitePath = storage_path('app/database/tempSong.sqlite');

        // 確保資料夾存在
        if (!file_exists(dirname($sqlitePath))) {
            mkdir(dirname($sqlitePath), 0755, true);
        }

        // 如果檔案不存在就建立空檔案
        if (!file_exists($sqlitePath)) {
            file_put_contents($sqlitePath, '');
        }
        $connectionName = 'tempsqlite_' . md5($sqlitePath . microtime());
        config(["database.connections.{$connectionName}" => [
            'driver' => 'sqlite',
            'database' => $sqlitePath,
            'prefix' => '',
        ]]);

        Schema::connection($connectionName)->dropIfExists('song_library_cache');
        Schema::connection($connectionName)->create('song_library_cache', function (Blueprint $table) {
            $table->bigIncrements('song_id')->comment('歌曲編號'); 
            $table->string('song_name')->nullable()->index()->comment('歌曲檔名');
            $table->string('song_simplified')->nullable()->index()->comment('歌曲簡體');
            $table->string('phonetic_abbr')->nullable()->index()->comment('歌曲注音');
            $table->string('pinyin_abbr')->nullable()->index()->comment('歌曲拼音');
            $table->integer('strokes_abbr')->default(0)->index()->comment('歌曲筆劃');
            $table->integer('song_number')->default(0)->index()->comment('歌曲字數');
            $table->string('artistA')->nullable()->index()->comment('歌星名稱A');
            $table->string('artistB')->nullable()->index()->comment('歌星名稱B');
            $table->string('artistA_simplified')->nullable()->index()->comment('歌星簡體名稱A');
            $table->string('artistB_simplified')->nullable()->index()->comment('歌星簡體名稱B');
            $table->string('artistA_category')->nullable()->default('未定義')->index()->comment('歌星類別A');
            $table->string('artistB_category')->nullable()->default('未定義')->index()->comment('歌星類別B');
            $table->string('artist_category')->nullable()->default('未定義')->index()->comment('歌星類別');
            $table->string('song_filename')->nullable()->comment('歌曲檔名');
            $table->string('song_category')->nullable()->comment('歌曲分類');
            $table->string('language_name')->nullable()->default('未定義')->index()->comment('語別');
            $table->date('add_date')->nullable()->index()->comment('新增日期');
            $table->string('situation')->nullable()->default('未定義')->index()->comment('情境');
            $table->tinyInteger('vocal')->default(0)->index()->comment('人聲'); // 0,1
            $table->integer('db_change')->default(0)->index()->comment('DB加減');
            $table->integer('song_counts')->default(0)->index()->comment('點播次數');
            $table->dateTime('updated_at')->nullable();
        });

        $totalInserted = 0;

        Song::with(['artists', 'categories'])->where('enable',1)->chunk(500, function ($songs) use (&$totalInserted, $connectionName)
        {
            $rows = [];

            foreach ($songs as $song) {
                $sortedArtists = $song->artists->sortBy('id')->values();
                $artistA = $sortedArtists->get(0);
                $artistB = $sortedArtists->get(1);

                $rows[] = [
                    'song_id' => $song->id,
                    'song_name' => $song->name,
                    'song_simplified' => $song->simplified ,
                    'phonetic_abbr' => $song->phonetic_abbr ?? '',
                    'pinyin_abbr' => $song->pinyin_abbr ?? '',
                    'strokes_abbr' => $song->strokes_abbr ?? 0,
                    'song_number' => $song->song_number ?? 0,
                    'artistA' => $artistA?->name,
                    'artistB' => $artistB?->name,
                    'artistA_simplified' => $artistA?->simplified,
                    'artistB_simplified' => $artistB?->simplified,
                    'artistA_category' => $artistA?->category?->value ?? '未定義',
                    'artistB_category' => $artistB?->category?->value ?? '未定義',
                    'artist_category' => in_array(\App\Enums\ArtistCategory::Group->value, [
                            $artistA?->category?->value,
                            $artistB?->category?->value,
                        ]) ? '團' : '未定義',
                    'song_filename' => $song->filename,
                    'song_category'=>$song->categories->pluck('code')->unique()->sort()->implode(', '),
                    'language_name' => $song->language_type ?? '未定義',
                    'add_date' => $song->adddate,
                    'situation' => $song->situation?->value ?? '未定義',
                    'vocal' => $song->vocal,
                    'db_change' => $song->db_change,
                    'song_counts' => $song->song_counts ?? 0,
                    'updated_at' => now(),
                ];
            }

            collect($rows)->chunk(1000)->each(function ($chunk) use (&$totalInserted, $connectionName) {
                DB::connection($connectionName)->table('song_library_cache')->insert($chunk->toArray());
                $totalInserted += $chunk->count();
            });
        });

        $exporter = new SqliteExportService($connectionName);
        $exporter->exportMultiple([
            'artists' => [
                'modelClass' => Artist::class,
                'tableSchema' => function (Blueprint $table) {
                    $table->bigIncrements('id');
                    $table->string('category')->default('未定義')->index();
                    $table->string('name')->unique();
                    $table->string('simplified')->index();
                    $table->string('phonetic_abbr')->index();
                    $table->string('pinyin_abbr')->index();
                    $table->integer('strokes_abbr')->index();
                    $table->tinyInteger('enable')->default(1);
                    $table->dateTime('updated_at')->nullable();
                },
                'transformer' => fn (Artist $artist) => [
                    'id' => $artist->id,
                    'category' => $artist->category?->value ?? '未定義',
                    'name' => $artist->name,
                    'simplified' => $artist->simplified,
                    'phonetic_abbr' => $artist->phonetic_abbr ?? '',
                    'pinyin_abbr' => $artist->pinyin_abbr ?? '',
                    'strokes_abbr' => $artist->strokes_abbr ?? 0,
                    'enable' => $artist->enable,
                    'updated_at' => now(),
                ],
            ],
        ]);
        DB::purge($connectionName);
        SendSqliteFileJob::dispatch($sqlitePath, $this->branchId);
    }
}