ALTER TABLE orders
    MODIFY COLUMN status ENUM('draft', 'submitted', 'validated', 'paid', 'in_bat', 'bat_approved', 'in_production', 'shipped', 'fulfilled', 'cancelled') NOT NULL DEFAULT 'draft',
    ADD COLUMN bat_sent_at DATETIME NULL AFTER paid_at,
    ADD COLUMN bat_approved_at DATETIME NULL AFTER bat_sent_at;

CREATE TABLE order_bat_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    order_item_id BIGINT UNSIGNED NULL,
    title VARCHAR(190) NOT NULL,
    message TEXT NULL,
    asset_type ENUM('mockup', 'pdf_preview', 'source_pdf', 'custom') NOT NULL DEFAULT 'mockup',
    asset_path VARCHAR(255) NOT NULL,
    status ENUM('sent', 'commented', 'changes_requested', 'approved', 'superseded') NOT NULL DEFAULT 'sent',
    sent_by_user_id BIGINT UNSIGNED NULL,
    sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    responded_at DATETIME NULL,
    approved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_order_bat_requests_order (order_id),
    INDEX idx_order_bat_requests_status (status),
    CONSTRAINT fk_order_bat_requests_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_order_bat_requests_item FOREIGN KEY (order_item_id) REFERENCES order_items(id),
    CONSTRAINT fk_order_bat_requests_sender FOREIGN KEY (sent_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_bat_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bat_request_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    created_by_user_id BIGINT UNSIGNED NULL,
    author_scope ENUM('admin', 'user') NOT NULL,
    action_type ENUM('comment', 'approve', 'changes_requested') NOT NULL DEFAULT 'comment',
    comment TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_bat_comments_request (bat_request_id),
    INDEX idx_order_bat_comments_order (order_id),
    CONSTRAINT fk_order_bat_comments_request FOREIGN KEY (bat_request_id) REFERENCES order_bat_requests(id),
    CONSTRAINT fk_order_bat_comments_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_order_bat_comments_user FOREIGN KEY (created_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE product_packs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    network_id BIGINT UNSIGNED NULL,
    code VARCHAR(120) NOT NULL UNIQUE,
    name VARCHAR(190) NOT NULL,
    description TEXT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product_packs_network (network_id),
    CONSTRAINT fk_product_packs_network FOREIGN KEY (network_id) REFERENCES networks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE product_pack_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pack_id BIGINT UNSIGNED NOT NULL,
    source_product_id BIGINT UNSIGNED NOT NULL,
    shop_id BIGINT UNSIGNED NULL,
    quantity INT NOT NULL DEFAULT 1,
    item_order INT NOT NULL DEFAULT 0,
    title_override VARCHAR(190) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_product_pack_items_pack (pack_id),
    CONSTRAINT fk_product_pack_items_pack FOREIGN KEY (pack_id) REFERENCES product_packs(id),
    CONSTRAINT fk_product_pack_items_product FOREIGN KEY (source_product_id) REFERENCES products(id),
    CONSTRAINT fk_product_pack_items_shop FOREIGN KEY (shop_id) REFERENCES shops(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_pack_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    pack_id BIGINT UNSIGNED NOT NULL,
    generated_order_id BIGINT UNSIGNED NULL,
    status ENUM('pending', 'generated', 'cancelled') NOT NULL DEFAULT 'pending',
    assigned_by_user_id BIGINT UNSIGNED NULL,
    note VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    generated_at DATETIME NULL,
    INDEX idx_user_pack_assignments_user (user_id),
    INDEX idx_user_pack_assignments_status (status),
    CONSTRAINT fk_user_pack_assignments_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_user_pack_assignments_pack FOREIGN KEY (pack_id) REFERENCES product_packs(id),
    CONSTRAINT fk_user_pack_assignments_order FOREIGN KEY (generated_order_id) REFERENCES orders(id),
    CONSTRAINT fk_user_pack_assignments_assigned_by FOREIGN KEY (assigned_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
