PHP-MVC-TERRITORY/database/migrations/20260517_000001_create_sentinel_tables.php

<?php

declare(strict_types=1);

use Core\Database;
use Core\Migration;

return new class extends Migration
{
    public function up(Database $database): void
    {
        $database->execute('
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                email VARCHAR(255) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                permissions TEXT,
                last_login DATETIME,
                first_name VARCHAR(255),
                last_name VARCHAR(255),
                created_at DATETIME,
                updated_at DATETIME
            )
        ');

        $database->execute('
            CREATE TABLE IF NOT EXISTS roles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                slug VARCHAR(255) NOT NULL UNIQUE,
                name VARCHAR(255) NOT NULL,
                permissions TEXT,
                created_at DATETIME,
                updated_at DATETIME
            )
        ');

        $database->execute('
            CREATE TABLE IF NOT EXISTS role_users (
                user_id INTEGER NOT NULL,
                role_id INTEGER NOT NULL,
                created_at DATETIME,
                updated_at DATETIME,
                PRIMARY KEY (user_id, role_id)
            )
        ');

        $database->execute('
            CREATE TABLE IF NOT EXISTS persistences (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                code VARCHAR(255) NOT NULL UNIQUE,
                created_at DATETIME,
                updated_at DATETIME
            )
        ');

        $database->execute('
            CREATE TABLE IF NOT EXISTS activations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                code VARCHAR(255) NOT NULL,
                completed INTEGER NOT NULL DEFAULT 0,
                completed_at DATETIME,
                created_at DATETIME,
                updated_at DATETIME
            )
        ');

        $database->execute('
            CREATE TABLE IF NOT EXISTS reminders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                code VARCHAR(255) NOT NULL,
                completed INTEGER NOT NULL DEFAULT 0,
                completed_at DATETIME,
                created_at DATETIME,
                updated_at DATETIME
            )
        ');

        $database->execute('
            CREATE TABLE IF NOT EXISTS throttle (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                type VARCHAR(255) NOT NULL,
                ip VARCHAR(255),
                created_at DATETIME,
                updated_at DATETIME
            )
        ');
    }

    public function down(Database $database): void
    {
        foreach (['throttle', 'reminders', 'activations', 'persistences', 'role_users', 'roles', 'users'] as $table) {
            $database->execute("DROP TABLE IF EXISTS {$table}");
        }
    }
};