<?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 cards (
                id             INTEGER PRIMARY KEY AUTOINCREMENT,
                board_id       INTEGER NOT NULL,
                column_id      INTEGER NOT NULL,
                swim_lane_id   INTEGER NOT NULL,
                job_number     VARCHAR(255),
                job_name       VARCHAR(255),
                customer_name  VARCHAR(255),
                delivery_date  DATE,
                quantity       VARCHAR(50),
                notes          TEXT,
                full_note      TEXT,
                position       INTEGER NOT NULL DEFAULT 0,
                created_at     DATETIME,
                created_by     VARCHAR(255),
                updated_at     DATETIME,
                updated_by     VARCHAR(255),
                FOREIGN KEY (board_id)     REFERENCES boards(id),
                FOREIGN KEY (column_id)    REFERENCES board_columns(id),
                FOREIGN KEY (swim_lane_id) REFERENCES swim_lanes(id)
            )'
        );

        $database->execute('CREATE INDEX IF NOT EXISTS idx_cards_board_id     ON cards (board_id)');
        $database->execute('CREATE INDEX IF NOT EXISTS idx_cards_column_id    ON cards (column_id)');
        $database->execute('CREATE INDEX IF NOT EXISTS idx_cards_swim_lane_id ON cards (swim_lane_id)');
    }

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