PHP-MVC-TERRITORY/database/migrations/20260531_000002_create_territory_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 territories (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                name        VARCHAR(255) NOT NULL,
                description TEXT,
                coordinates TEXT,
                created_at  DATETIME,
                updated_at  DATETIME
            )
        ');

        $database->execute('
            CREATE TABLE IF NOT EXISTS households (
                id                    INTEGER PRIMARY KEY AUTOINCREMENT,
                territory_id          INTEGER NOT NULL,
                address               VARCHAR(255) NOT NULL,
                street_number         INTEGER,
                street_name           VARCHAR(255),
                latitude              REAL,
                longitude             REAL,
                is_business           INTEGER NOT NULL DEFAULT 0,
                do_not_call           INTEGER NOT NULL DEFAULT 0,
                do_not_call_date      DATE,
                do_not_call_notes     TEXT,
                do_not_call_private_notes TEXT,
                created_at            DATETIME,
                updated_at            DATETIME,
                FOREIGN KEY (territory_id) REFERENCES territories(id)
            )
        ');

        $database->execute('
            CREATE TABLE IF NOT EXISTS householder_names (
                id               INTEGER PRIMARY KEY AUTOINCREMENT,
                household_id     INTEGER NOT NULL,
                name             VARCHAR(255) NOT NULL,
                letter_returned  INTEGER NOT NULL DEFAULT 0,
                return_date      DATETIME,
                created_at       DATETIME,
                updated_at       DATETIME,
                FOREIGN KEY (household_id) REFERENCES households(id)
            )
        ');
    }

    public function down(Database $database): void
    {
        foreach (['householder_names', 'households', 'territories'] as $table) {
            $database->execute("DROP TABLE IF EXISTS {$table}");
        }
    }
};