USE engine;
DELIMITER //
CREATE TRIGGER IF NOT EXISTS trg_payments_before_insert
BEFORE INSERT ON payments
FOR EACH ROW
BEGIN
IF NEW.currency IS NULL OR NEW.currency NOT IN ('CAD','USD','EUR','GBP') THEN
SET NEW.currency = 'CAD';
END IF;
IF NEW.fraud_score IS NULL THEN
SET NEW.fraud_score = 0.00;
ELSEIF NEW.fraud_score < 0.00 THEN
SET NEW.fraud_score = 0.00;
ELSEIF NEW.fraud_score > 1.00 THEN
SET NEW.fraud_score = 1.00;
END IF;
IF NEW.card_fingerprint IS NOT NULL AND LENGTH(NEW.card_fingerprint) > 64 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'card_fingerprint exceeds max length';
END IF;
IF NEW.amount IS NULL OR NEW.amount <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Payment amount must be greater than 0';
END IF;
IF NEW.email_hash IS NOT NULL AND LENGTH(NEW.email_hash) > 0 AND NEW.customer_hash IS NULL THEN
SET NEW.customer_hash = NEW.email_hash;
END IF;
END //
CREATE TRIGGER IF NOT EXISTS trg_payments_after_insert
AFTER INSERT ON payments
FOR EACH ROW
BEGIN
DECLARE v_shipping_method VARCHAR(30) DEFAULT 'canada_post_regular';
DECLARE v_shipping_address JSON;
DECLARE v_order_ref VARCHAR(32);
IF NEW.status = 'completed' AND NOT EXISTS (SELECT 1 FROM fulfillment WHERE payment_id = NEW.id) THEN
SET v_shipping_address = JSON_OBJECT('name','','address_1','','city','','province','','postal_code','','country','CA');
SET v_order_ref = CONCAT('CE-', DATE_FORMAT(CURDATE(), '%Y%m%d'), '-', UPPER(SUBSTRING(MD5(CONCAT(NEW.id, RAND())), 1, 4)));
WHILE EXISTS (SELECT 1 FROM fulfillment WHERE order_ref = v_order_ref) DO
SET v_order_ref = CONCAT('CE-', DATE_FORMAT(CURDATE(), '%Y%m%d'), '-', UPPER(SUBSTRING(MD5(CONCAT(NEW.id, RAND())), 1, 4)));
END WHILE;
INSERT INTO fulfillment (payment_id, order_ref, status, shipping_method, tracking_number, shipping_address, estimated_delivery)
VALUES (NEW.id, v_order_ref, 'pending', v_shipping_method, NULL, v_shipping_address, DATE_ADD(CURDATE(), INTERVAL 7 DAY));
END IF;
END //
CREATE TRIGGER IF NOT EXISTS trg_fulfillment_after_update
AFTER UPDATE ON fulfillment
FOR EACH ROW
BEGIN
DECLARE v_customer_email_hash VARCHAR(64);
DECLARE v_email_subject VARCHAR(500);
DECLARE v_email_body TEXT;
DECLARE v_artifact_id BIGINT;
IF OLD.status != 'shipped' AND NEW.status = 'shipped' THEN
SELECT email_hash, artifact_id INTO v_customer_email_hash, v_artifact_id
FROM payments WHERE id = NEW.payment_id;
IF v_customer_email_hash IS NOT NULL THEN
SET v_email_subject = CONCAT('Your order ', NEW.order_ref, ' has shipped');
SET v_email_body = CONCAT('<p>Your order <strong>', NEW.order_ref, '</strong> has shipped.</p><p>Tracking: <strong>', COALESCE(NEW.tracking_number, 'N/A'), '</strong></p>');
INSERT INTO email_queue (artifact_id, email_hash, subject, body, dkim_domain, status)
VALUES (v_artifact_id, v_customer_email_hash, v_email_subject, v_email_body, (SELECT setting_value FROM engine.sys_settings WHERE setting_key='dkim_domain' LIMIT 1), 'queued');
END IF;
END IF;
IF OLD.status != 'delivered' AND NEW.status = 'delivered' THEN
SELECT email_hash, artifact_id INTO v_customer_email_hash, v_artifact_id
FROM payments WHERE id = NEW.payment_id;
IF v_customer_email_hash IS NOT NULL THEN
SET v_email_subject = CONCAT('Your order ', NEW.order_ref, ' has been delivered');
SET v_email_body = CONCAT('<p>Your order <strong>', NEW.order_ref, '</strong> has been delivered.</p>');
INSERT INTO email_queue (artifact_id, email_hash, subject, body, dkim_domain, status)
VALUES (v_artifact_id, v_customer_email_hash, v_email_subject, v_email_body, (SELECT setting_value FROM engine.sys_settings WHERE setting_key='dkim_domain' LIMIT 1), 'queued');
END IF;
END IF;
END //
CREATE TRIGGER IF NOT EXISTS trg_sessions_before_insert
BEFORE INSERT ON sessions
FOR EACH ROW
BEGIN
IF NEW.max_lifetime IS NULL THEN
SET NEW.max_lifetime = DATE_ADD(NOW(), INTERVAL 1 HOUR);
END IF;
IF NEW.trust_score IS NULL OR NEW.trust_score < 0.00 THEN
SET NEW.trust_score = 0.10;
ELSEIF NEW.trust_score > 1.00 THEN
SET NEW.trust_score = 1.00;
END IF;
IF NEW.ip LIKE '::ffff:%' THEN
SET NEW.ip = SUBSTRING(NEW.ip, 8);
END IF;
END //
CREATE TRIGGER IF NOT EXISTS trg_strikes_after_insert
AFTER INSERT ON strikes
FOR EACH ROW
BEGIN
DECLARE v_current_rep DECIMAL(3,2);
SELECT reputation_score INTO v_current_rep FROM ip_reputation WHERE ip = NEW.ip;
IF v_current_rep IS NULL THEN
INSERT INTO ip_reputation (ip, reputation_score, request_count, blocked_count)
VALUES (NEW.ip, GREATEST(0.00, 0.50 - (NEW.count * 0.15)), 0, 1);
ELSE
UPDATE ip_reputation SET reputation_score = GREATEST(0.00, v_current_rep - (NEW.count * 0.15)),
blocked_count = blocked_count + 1, last_seen = NOW() WHERE ip = NEW.ip;
END IF;
IF NEW.count >= 2 THEN
INSERT INTO security_events (node_id, event_type, severity, source_ip, description, metadata)
VALUES ('669427', 'blacklist', CASE WHEN NEW.count >= 3 THEN 'critical' ELSE 'high' END, NEW.ip,
CONCAT('Strike escalation: ', NEW.count), JSON_OBJECT('strike_id', NEW.id, 'count', NEW.count, 'action', NEW.action));
END IF;
END //
CREATE TRIGGER IF NOT EXISTS trg_security_events_after_insert
AFTER INSERT ON security_events
FOR EACH ROW
BEGIN
IF NEW.severity = 'critical' THEN
INSERT INTO logs (node_id, level, category, message, metadata)
VALUES (NEW.node_id, 'critical', 'security', CONCAT('CRITICAL: ', NEW.event_type, ' - ', LEFT(NEW.description, 200)),
JSON_OBJECT('event_id', NEW.id, 'event_type', NEW.event_type, 'severity', NEW.severity, 'source_ip', NEW.source_ip));
IF NEW.source_ip IS NOT NULL THEN
INSERT INTO ip_reputation (ip, reputation_score, blocked_count)
VALUES (NEW.source_ip, 0.00, 1)
ON DUPLICATE KEY UPDATE reputation_score = GREATEST(0.00, reputation_score - 0.30),
blocked_count = blocked_count + 1, last_seen = NOW();
END IF;
END IF;
IF NEW.severity = 'high' THEN
INSERT INTO logs (node_id, level, category, message, metadata)
VALUES (NEW.node_id, 'warn', 'security', CONCAT('High: ', NEW.event_type, ' - ', LEFT(NEW.description, 200)),
JSON_OBJECT('event_id', NEW.id, 'event_type', NEW.event_type, 'severity', NEW.severity, 'source_ip', NEW.source_ip));
END IF;
END //
DELIMITER ;