-- Create triggers to auto-sync Inventory.quantity with InventoryItem counts
-- This ensures ACID compliance by automatically maintaining consistency

DELIMITER //

-- Trigger: After INSERT on inventory_items
-- Increment Inventory.quantity when new IN_STOCK item is created
CREATE TRIGGER trg_inventory_item_insert
AFTER INSERT ON inventory_items
FOR EACH ROW
BEGIN
  IF NEW.status = 'IN_STOCK' THEN
    INSERT INTO inventories (product_id, variant_id, quantity, reorder_level, created_at, updated_at)
    VALUES (NEW.product_id, NEW.variant_id, 1, 0, NOW(), NOW())
    ON DUPLICATE KEY UPDATE
      quantity = quantity + 1,
      updated_at = NOW();
  END IF;
END//

-- Trigger: After UPDATE on inventory_items
-- Update Inventory.quantity when status changes
CREATE TRIGGER trg_inventory_item_update
AFTER UPDATE ON inventory_items
FOR EACH ROW
BEGIN
  -- Handle status changes
  IF OLD.status = 'IN_STOCK' AND NEW.status != 'IN_STOCK' THEN
    -- Item left IN_STOCK (SOLD, RESERVED, DAMAGED, etc.)
    UPDATE inventories
    SET quantity = GREATEST(0, quantity - 1),
        updated_at = NOW()
    WHERE product_id = NEW.product_id
      AND (variant_id = NEW.variant_id OR (variant_id IS NULL AND NEW.variant_id IS NULL));
  END IF;
  
  IF OLD.status != 'IN_STOCK' AND NEW.status = 'IN_STOCK' THEN
    -- Item returned to IN_STOCK (RETURNED, etc.)
    INSERT INTO inventories (product_id, variant_id, quantity, reorder_level, created_at, updated_at)
    VALUES (NEW.product_id, NEW.variant_id, 1, 0, NOW(), NOW())
    ON DUPLICATE KEY UPDATE
      quantity = quantity + 1,
      updated_at = NOW();
  END IF;
  
  -- Handle product_id or variant_id changes (rare but possible)
  IF (OLD.product_id != NEW.product_id OR OLD.variant_id != NEW.variant_id) THEN
    -- Decrement old inventory
    IF OLD.status = 'IN_STOCK' THEN
      UPDATE inventories
      SET quantity = GREATEST(0, quantity - 1),
          updated_at = NOW()
      WHERE product_id = OLD.product_id
        AND (variant_id = OLD.variant_id OR (variant_id IS NULL AND OLD.variant_id IS NULL));
    END IF;
    
    -- Increment new inventory
    IF NEW.status = 'IN_STOCK' THEN
      INSERT INTO inventories (product_id, variant_id, quantity, reorder_level, created_at, updated_at)
      VALUES (NEW.product_id, NEW.variant_id, 1, 0, NOW(), NOW())
      ON DUPLICATE KEY UPDATE
        quantity = quantity + 1,
        updated_at = NOW();
    END IF;
  END IF;
END//

-- Trigger: After DELETE on inventory_items
-- Decrement Inventory.quantity when item is deleted
CREATE TRIGGER trg_inventory_item_delete
AFTER DELETE ON inventory_items
FOR EACH ROW
BEGIN
  IF OLD.status = 'IN_STOCK' THEN
    UPDATE inventories
    SET quantity = GREATEST(0, quantity - 1),
        updated_at = NOW()
    WHERE product_id = OLD.product_id
      AND (variant_id = OLD.variant_id OR (variant_id IS NULL AND OLD.variant_id IS NULL));
  END IF;
END//

DELIMITER ;

-- Add index on inventory_items for better trigger performance
CREATE INDEX IF NOT EXISTS idx_inventory_items_sync 
ON inventory_items(product_id, variant_id, status);

