-- Migration: Add Purchase Order Items table and GRN processed_at column
-- Date: 2026-01-05
-- Description: 
--   1. Creates purchase_order_items table for storing PO items
--   2. Adds processed_at column to grns table

-- ============================================
-- 1. Create purchase_order_items table
-- ============================================
CREATE TABLE IF NOT EXISTS `purchase_order_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `purchase_order_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `variant_id` BIGINT UNSIGNED NULL,
  `quantity` DECIMAL(12, 3) NOT NULL,
  `unit_cost` DECIMAL(12, 2) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_purchase_order_id` (`purchase_order_id`),
  INDEX `idx_product_id` (`product_id`),
  INDEX `idx_variant_id` (`variant_id`),
  CONSTRAINT `fk_po_items_po` 
    FOREIGN KEY (`purchase_order_id`) 
    REFERENCES `purchase_orders` (`id`) 
    ON UPDATE CASCADE 
    ON DELETE CASCADE,
  CONSTRAINT `fk_po_items_product` 
    FOREIGN KEY (`product_id`) 
    REFERENCES `products` (`id`) 
    ON UPDATE CASCADE,
  CONSTRAINT `fk_po_items_variant` 
    FOREIGN KEY (`variant_id`) 
    REFERENCES `product_variants` (`id`) 
    ON UPDATE CASCADE,
  CHECK (`quantity` >= 0),
  CHECK (`unit_cost` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- 2. Add processed_at column to grns table
-- ============================================
-- Check if column doesn't exist before adding (MySQL 8.0+ compatible)
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'grns'
    AND COLUMN_NAME = 'processed_at'
);

SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `grns` ADD COLUMN `processed_at` DATETIME NULL AFTER `received_at`',
  'SELECT "Column processed_at already exists in grns table" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- Migration complete
-- ============================================

