KCI-CAMPAIGN-TRACKER/database/migrations/20260511_000008_create_job_table.php

<?php

declare(strict_types=1);

use Core\Database;
use Core\Migration;

return new class extends Migration
{
    public function up(Database $database): void
    {
        $tableExists = $database->first(
            "SELECT 1 AS tbl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'job'"
        );

        if ($tableExists) {
            return;
        }

        $database->execute(
            'CREATE TABLE job (
                id               INT IDENTITY(1,1) NOT NULL,
                campaign_id      INT           NOT NULL,
                job_type_id      INT           NOT NULL,
                attribute_values NVARCHAR(MAX) NULL,
                created_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
                CONSTRAINT PK_job          PRIMARY KEY (id),
                CONSTRAINT FK_job_campaign FOREIGN KEY (campaign_id)
                    REFERENCES campaign (id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                CONSTRAINT FK_job_job_type FOREIGN KEY (job_type_id)
                    REFERENCES job_type (id) ON UPDATE NO ACTION ON DELETE NO ACTION
            )'
        );

        $database->execute('CREATE INDEX IX_job_campaign_id ON job (campaign_id)');
    }

    public function down(Database $database): void
    {
        $database->execute('DROP TABLE IF EXISTS job');
    }
};