-- ResellBD Step 24: split a customer order into seller/platform sub-orders.
-- Apply ONLY to the NEW ResellBD database after migrations 001-015.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS seller_suborders (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 suborder_number VARCHAR(40) NOT NULL,
 parent_order_id BIGINT UNSIGNED NOT NULL,
 seller_user_id BIGINT UNSIGNED NULL,
 seller_store_id BIGINT UNSIGNED NULL,
 status ENUM('pending','confirmed','processing','packed','shipped','delivered','cancelled','returned') NOT NULL DEFAULT 'pending',
 items_subtotal_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
 delivery_fee_share_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
 total_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_seller_suborder_number(suborder_number),
 KEY idx_seller_suborder_parent(parent_order_id), KEY idx_seller_suborder_seller(seller_user_id,status,created_at),
 CONSTRAINT fk_seller_suborder_parent FOREIGN KEY(parent_order_id) REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_seller_suborder_user FOREIGN KEY(seller_user_id) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_seller_suborder_store FOREIGN KEY(seller_store_id) REFERENCES seller_stores(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS seller_suborder_items (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 suborder_id BIGINT UNSIGNED NOT NULL,
 order_item_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 variant_id BIGINT UNSIGNED NOT NULL,
 quantity SMALLINT UNSIGNED NOT NULL,
 unit_price_paisa BIGINT UNSIGNED NOT NULL,
 line_total_paisa BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_suborder_order_item(order_item_id),
 KEY idx_suborder_items_suborder(suborder_id),
 CONSTRAINT fk_suborder_item_suborder FOREIGN KEY(suborder_id) REFERENCES seller_suborders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_suborder_item_order_item FOREIGN KEY(order_item_id) REFERENCES order_items(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_suborder_item_product FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_suborder_item_variant FOREIGN KEY(variant_id) REFERENCES product_variants(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT chk_suborder_item_total CHECK(line_total_paisa = unit_price_paisa * quantity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;