33 lines
1.3 KiB
PHP
33 lines
1.3 KiB
PHP
<?php
|
||
|
||
use Illuminate\Database\Migrations\Migration;
|
||
use Illuminate\Database\Schema\Blueprint;
|
||
use Illuminate\Support\Facades\Schema;
|
||
|
||
return new class extends Migration
|
||
{
|
||
public function up(): void
|
||
{
|
||
Schema::create('searches', function (Blueprint $table): void {
|
||
$table->bigIncrements('id');
|
||
$table->decimal('lat_bucket', 5, 2)->comment('Latitude rounded to 2dp (~1km precision) for privacy');
|
||
$table->decimal('lng_bucket', 5, 2)->comment('Longitude rounded to 2dp for privacy');
|
||
$table->string('fuel_type', 20);
|
||
$table->unsignedSmallInteger('results_count');
|
||
$table->unsignedSmallInteger('lowest_pence')->nullable()->comment('Cheapest price found in pence × 100');
|
||
$table->unsignedSmallInteger('highest_pence')->nullable()->comment('Most expensive price found in pence × 100');
|
||
$table->decimal('avg_pence', 8, 2)->nullable()->comment('Mean price across results in pence × 100');
|
||
$table->dateTime('searched_at');
|
||
$table->string('ip_hash', 64)->comment('SHA-256 of requester IP — non-reversible, for unique count only');
|
||
|
||
$table->index(['searched_at', 'fuel_type']);
|
||
$table->index('ip_hash');
|
||
});
|
||
}
|
||
|
||
public function down(): void
|
||
{
|
||
Schema::dropIfExists('searches');
|
||
}
|
||
};
|