2024_12_17_111223_create_ledger_table.php

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {

        DB::statement(
            <<<'SQL'
CREATE VIEW ledgers AS
SELECT ROW_NUMBER() OVER (ORDER BY combined_data.created_at) as id,
        user_id,
        amount,
        source_id,
        source,
        created_at,
        notes
FROM (
     SELECT eu.user_id, 
             eu.winnings as amount, 
             eu.event_id as source_id,
             CONVERT('\\App\\Models\\Event' USING utf8) as source,
             eu.registered_at as created_at,
             CONVERT(CONCAT(
                CASE 
                    WHEN eu.place % 10 = 1 AND eu.place != 11 THEN CONCAT(eu.place, 'st')
                    WHEN eu.place % 10 = 2 AND eu.place != 12 THEN CONCAT(eu.place, 'nd')
                    WHEN eu.place % 10 = 3 AND eu.place != 13 THEN CONCAT(eu.place, 'rd')
                    ELSE CONCAT(eu.place, 'th')
                END,
                ' place in ',
                e.name
             ) USING utf8) as notes
     FROM event_user eu
     JOIN events e ON e.id = eu.event_id
     WHERE eu.winnings > 0
     UNION ALL
     SELECT tu.user_id,
             tu.winnings as amount,
             tu.tournament_id as source_id,
             CONVERT('\\App\\Models\\Tournament' USING utf8) as source,
             tu.registered_at as created_at,
             CONVERT(CONCAT(
                CASE 
                    WHEN tu.place % 10 = 1 AND tu.place != 11 THEN CONCAT(tu.place, 'st')
                    WHEN tu.place % 10 = 2 AND tu.place != 12 THEN CONCAT(tu.place, 'nd')
                    WHEN tu.place % 10 = 3 AND tu.place != 13 THEN CONCAT(tu.place, 'rd')
                    ELSE CONCAT(tu.place, 'th')
                END,
                ' place in ',
                t.name
             ) USING utf8) as notes
     FROM tournament_user tu
     JOIN tournaments t ON t.id = tu.tournament_id
     WHERE tu.winnings > 0
     UNION ALL
     SELECT user_id,
             -amount as amount,
             id as source_id,
             CONVERT('\\App\\Models\\Withdrawals' USING utf8) as source,
             created_at,
             CONVERT(CONCAT('Withdrawal: Paid on ', DATE_FORMAT(paid_at, '%m/%d/%y')) USING utf8) as notes
     FROM withdrawals
     WHERE amount > 0
) as combined_data
ORDER BY combined_data.created_at;
SQL
        );
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::statement('DROP VIEW ledgers;');
    }
};