-- AlterTable
ALTER TABLE `products` ADD COLUMN `logistics_route_id` VARCHAR(191) NULL;

-- AlterTable
ALTER TABLE `shops` ADD COLUMN `default_logistics_route_id` VARCHAR(191) NULL;

-- CreateTable
CREATE TABLE `logistics_routes` (
    `id` VARCHAR(191) NOT NULL,
    `shop_id` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `description` VARCHAR(191) NULL,
    `is_default` BOOLEAN NOT NULL DEFAULT false,
    `active` BOOLEAN NOT NULL DEFAULT true,
    `revision` INTEGER NOT NULL DEFAULT 1,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updated_at` DATETIME(3) NOT NULL,

    INDEX `logistics_routes_shop_id_active_idx`(`shop_id`, `active`),
    INDEX `logistics_routes_shop_id_is_default_idx`(`shop_id`, `is_default`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `logistics_route_steps` (
    `id` VARCHAR(191) NOT NULL,
    `route_id` VARCHAR(191) NOT NULL,
    `station_id` VARCHAR(191) NOT NULL,
    `step_type` ENUM('DEPARTURE', 'PRODUCTION', 'TRANSPORT', 'ARRIVAL') NOT NULL,
    `position` INTEGER NOT NULL,
    `label` VARCHAR(191) NULL,
    `handler_mode` ENUM('RUNNER', 'SELF_CASHIER', 'SOURCE_STATION') NULL,
    `assigned_runner_id` VARCHAR(191) NULL,
    `estimated_minutes` INTEGER NULL,
    `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    INDEX `logistics_route_steps_route_id_idx`(`route_id`),
    INDEX `logistics_route_steps_station_id_idx`(`station_id`),
    INDEX `logistics_route_steps_assigned_runner_id_idx`(`assigned_runner_id`),
    UNIQUE INDEX `logistics_route_steps_route_id_position_key`(`route_id`, `position`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateIndex
CREATE INDEX `products_logistics_route_id_idx` ON `products`(`logistics_route_id`);

-- AddForeignKey
ALTER TABLE `shops` ADD CONSTRAINT `shops_default_logistics_route_id_fkey` FOREIGN KEY (`default_logistics_route_id`) REFERENCES `logistics_routes`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `products` ADD CONSTRAINT `products_logistics_route_id_fkey` FOREIGN KEY (`logistics_route_id`) REFERENCES `logistics_routes`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `logistics_routes` ADD CONSTRAINT `logistics_routes_shop_id_fkey` FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `logistics_route_steps` ADD CONSTRAINT `logistics_route_steps_route_id_fkey` FOREIGN KEY (`route_id`) REFERENCES `logistics_routes`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `logistics_route_steps` ADD CONSTRAINT `logistics_route_steps_station_id_fkey` FOREIGN KEY (`station_id`) REFERENCES `stations`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE `logistics_route_steps` ADD CONSTRAINT `logistics_route_steps_assigned_runner_id_fkey` FOREIGN KEY (`assigned_runner_id`) REFERENCES `shop_users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
