-- Fix order_items table schema to match API expectations
USE superpos;

-- Drop and recreate order_items with correct columns
DROP TABLE IF EXISTS `order_items`;

CREATE TABLE `order_items` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `product_name` VARCHAR(200) NOT NULL,
    `quantity` DECIMAL(10, 2) NOT NULL,
    `unit` VARCHAR(20) DEFAULT 'Pcs',
    `price_at_moment` DECIMAL(15, 2) NOT NULL COMMENT 'Selling price at time of sale',
    `cogs_at_moment` DECIMAL(15, 2) DEFAULT 0 COMMENT 'Cost of goods sold at time of sale',
    `discount_per_item` DECIMAL(15, 2) DEFAULT 0,
    `subtotal` DECIMAL(15, 2) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
    INDEX idx_order (`order_id`),
    INDEX idx_product (`product_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

SELECT 'order_items table fixed!' as Status;