<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    /**
     * Phase 9 EXPLAIN pass found `whereMonth(date_of_birth)` and
     * `DATE_FORMAT(date_of_birth, '%m-%d')` (birthday report, dashboard
     * upcoming-birthdays count) wrap the indexed column in a function,
     * defeating the date_of_birth index — a full scan over 293k rows.
     * A generated, indexed 'MM-DD' column lets both queries become a
     * sargable LIKE '06-%' / WHERE ... IN (...) instead.
     */
    public function up(): void
    {
        DB::statement(
            'ALTER TABLE members ADD COLUMN date_of_birth_month_day CHAR(5) '.
            "GENERATED ALWAYS AS (DATE_FORMAT(date_of_birth, '%m-%d')) STORED"
        );
        DB::statement('CREATE INDEX members_branch_id_birth_month_day_index ON members (branch_id, date_of_birth_month_day)');
        DB::statement('CREATE INDEX members_birth_month_day_index ON members (date_of_birth_month_day)');
    }

    public function down(): void
    {
        DB::statement('ALTER TABLE members DROP INDEX members_birth_month_day_index');
        DB::statement('ALTER TABLE members DROP INDEX members_branch_id_birth_month_day_index');
        DB::statement('ALTER TABLE members DROP COLUMN date_of_birth_month_day');
    }
};
