DROP TRIGGER IF EXISTS trg_bar_duplicate_guard;

DELIMITER $$

CREATE TRIGGER trg_bar_duplicate_guard
BEFORE INSERT ON brand_ampere_rates
FOR EACH ROW
BEGIN
    IF NEW.product_id IS NOT NULL AND NEW.amperage_id IS NOT NULL THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'INVALID RATE: product_id and amperage_id cannot both be set simultaneously.';
    END IF;

    IF NEW.is_current = 1 AND EXISTS (
        SELECT 1 FROM brand_ampere_rates
        WHERE brand_id    = NEW.brand_id
          AND rate_type   = NEW.rate_type
          AND amperage_id <=> NEW.amperage_id
          AND product_id  <=> NEW.product_id
          AND is_current  = 1
    ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'DUPLICATE ACTIVE RATE: A current rate already exists for this combination.';
    END IF;
END$$

DELIMITER ;
