USE engine;

DELIMITER //

CREATE PROCEDURE IF NOT EXISTS sp_process_payment(
    IN p_artifact_id    BIGINT,
    IN p_payment_data   JSON,
    OUT p_result        JSON
)
SQL SECURITY INVOKER
BEGIN
    DECLARE v_amount DECIMAL(10,2);
    DECLARE v_currency CHAR(3);
    DECLARE v_email VARCHAR(255);
    DECLARE v_email_hash VARCHAR(64);
    DECLARE v_customer_hash VARCHAR(64);
    DECLARE v_stripe_id VARCHAR(64);
    DECLARE v_method VARCHAR(20);
    DECLARE v_payment_id BIGINT;
    DECLARE v_fraud_score DECIMAL(3,2) DEFAULT 0.00;
    DECLARE v_card_fingerprint VARCHAR(64);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = JSON_OBJECT('success', FALSE, 'error', 'Transaction failed', 'artifact_id', p_artifact_id);
    END;

    START TRANSACTION;

    SET v_amount = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_payment_data, '$.amount')) AS DECIMAL(10,2));
    SET v_currency = UPPER(JSON_UNQUOTE(JSON_EXTRACT(p_payment_data, '$.currency')));
    SET v_email = JSON_UNQUOTE(JSON_EXTRACT(p_payment_data, '$.email'));
    SET v_stripe_id = JSON_UNQUOTE(JSON_EXTRACT(p_payment_data, '$.stripe_payment_intent_id'));
    SET v_method = JSON_UNQUOTE(JSON_EXTRACT(p_payment_data, '$.method'));
    SET v_card_fingerprint = JSON_UNQUOTE(JSON_EXTRACT(p_payment_data, '$.card_fingerprint'));
    SET v_fraud_score = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_payment_data, '$.fraud_score')) AS DECIMAL(3,2));

    IF v_currency IS NULL OR v_currency NOT IN ('CAD','USD','EUR','GBP') THEN
        SET v_currency = 'CAD';
    END IF;

    IF v_amount IS NULL OR v_amount <= 0 THEN
        SET p_result = JSON_OBJECT('success', FALSE, 'error', 'Invalid amount', 'artifact_id', p_artifact_id);
        ROLLBACK;
    END IF;

    IF v_email IS NOT NULL AND LENGTH(v_email) > 0 THEN
        SET v_email_hash = LOWER(SHA2(v_email, 256));
        SET v_customer_hash = LOWER(SHA2(v_email, 256));
    ELSE
        SET v_email_hash = NULL;
        SET v_customer_hash = NULL;
    END IF;

    IF v_fraud_score IS NULL OR v_fraud_score < 0 THEN SET v_fraud_score = 0.00; END IF;
    IF v_fraud_score > 1 THEN SET v_fraud_score = 1.00; END IF;

    INSERT INTO payments (artifact_id, stripe_payment_intent_id, amount, currency, status, card_fingerprint, fraud_score, email_hash, customer_hash)
    VALUES (p_artifact_id, v_stripe_id, v_amount, v_currency, 'pending', v_card_fingerprint, v_fraud_score, v_email_hash, v_customer_hash);

    SET v_payment_id = LAST_INSERT_ID();

    IF v_method IS NOT NULL THEN
        INSERT INTO payment_methods (payment_id, method, method_data)
        VALUES (v_payment_id, v_method, JSON_EXTRACT(p_payment_data, '$.method_data'));
    END IF;

    UPDATE artifacts SET status = 'processing', processed_at = NOW(),
        result = JSON_OBJECT('payment_id', v_payment_id, 'step', 'payment_inserted')
    WHERE id = p_artifact_id;

    COMMIT;

    SET p_result = JSON_OBJECT('success', TRUE, 'payment_id', v_payment_id, 'artifact_id', p_artifact_id,
        'amount', v_amount, 'currency', v_currency, 'customer_hash', v_customer_hash,
        'fraud_score', v_fraud_score, 'status', 'pending');
END //

CREATE PROCEDURE IF NOT EXISTS sp_send_email(
    IN p_artifact_id    BIGINT,
    IN p_email_data     JSON,
    OUT p_result        JSON
)
SQL SECURITY INVOKER
BEGIN
    DECLARE v_recipient VARCHAR(255);
    DECLARE v_recipient_hash VARCHAR(64);
    DECLARE v_subject VARCHAR(500);
    DECLARE v_body TEXT;
    DECLARE v_dkim_domain VARCHAR(100);
    DECLARE v_dkim_selector VARCHAR(50) DEFAULT 'default';
    DECLARE v_canary_id VARCHAR(32);
    DECLARE v_email_id BIGINT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_result = JSON_OBJECT('success', FALSE, 'error', 'Email queue failed', 'artifact_id', p_artifact_id);
    END;

    SET v_recipient = JSON_UNQUOTE(JSON_EXTRACT(p_email_data, '$.recipient'));
    SET v_subject = JSON_UNQUOTE(JSON_EXTRACT(p_email_data, '$.subject'));
    SET v_body = JSON_UNQUOTE(JSON_EXTRACT(p_email_data, '$.body'));
    SET v_dkim_domain = JSON_UNQUOTE(JSON_EXTRACT(p_email_data, '$.dkim_domain'));

    IF v_recipient IS NULL OR LENGTH(v_recipient) = 0 THEN
        SET p_result = JSON_OBJECT('success', FALSE, 'error', 'Recipient required', 'artifact_id', p_artifact_id);
    END IF;

    IF v_dkim_domain IS NULL OR LENGTH(v_dkim_domain) = 0 THEN
        SET v_dkim_domain = 'cybernetic-engine.com';
    END IF;

    SET v_recipient_hash = LOWER(SHA2(v_recipient, 256));
    SET v_canary_id = LOWER(LEFT(UUID(), 8));

    INSERT INTO email_queue (artifact_id, email_hash, subject, body, dkim_selector, dkim_domain, status, canary_id)
    VALUES (p_artifact_id, v_recipient_hash, v_subject, v_body, v_dkim_selector, v_dkim_domain, 'queued', v_canary_id);

    SET v_email_id = LAST_INSERT_ID();

    UPDATE artifacts SET status = 'processing',
        result = JSON_OBJECT('email_id', v_email_id, 'step', 'email_queued')
    WHERE id = p_artifact_id;

    SET p_result = JSON_OBJECT('success', TRUE, 'email_id', v_email_id, 'artifact_id', p_artifact_id,
        'status', 'queued', 'canary_id', v_canary_id);
END //

CREATE PROCEDURE IF NOT EXISTS sp_start_fulfillment(
    IN p_payment_id     BIGINT,
    IN p_shipping_data  JSON,
    OUT p_order_ref     VARCHAR(32)
)
SQL SECURITY INVOKER
BEGIN
    DECLARE v_shipping_method VARCHAR(30);
    DECLARE v_shipping_address JSON;
    DECLARE v_fulfillment_id BIGINT;
    DECLARE v_customer_hash VARCHAR(64);
    DECLARE v_amount DECIMAL(10,2);
    DECLARE v_currency CHAR(3);
    DECLARE v_payment_status VARCHAR(20);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_order_ref = NULL;
        RESIGNAL;
    END;

    SELECT status, customer_hash INTO v_payment_status, v_customer_hash
    FROM payments WHERE id = p_payment_id;

    IF v_payment_status IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Payment not found';
    END IF;

    IF v_payment_status != 'completed' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Payment not completed';
    END IF;

    IF EXISTS (SELECT 1 FROM fulfillment WHERE payment_id = p_payment_id) THEN
        SELECT order_ref INTO p_order_ref FROM fulfillment WHERE payment_id = p_payment_id;
    ELSE
        SET p_order_ref = CONCAT('CE-', DATE_FORMAT(CURDATE(), '%Y%m%d'), '-', UPPER(SUBSTRING(MD5(RAND()), 1, 4)));

        WHILE EXISTS (SELECT 1 FROM fulfillment WHERE order_ref = p_order_ref) DO
            SET p_order_ref = CONCAT('CE-', DATE_FORMAT(CURDATE(), '%Y%m%d'), '-', UPPER(SUBSTRING(MD5(RAND()), 1, 4)));
        END WHILE;

        SET v_shipping_method = JSON_UNQUOTE(JSON_EXTRACT(p_shipping_data, '$.shipping_method'));
        SET v_shipping_address = JSON_EXTRACT(p_shipping_data, '$.shipping_address');

        IF v_shipping_method IS NULL OR v_shipping_method NOT IN ('canada_post_express','canada_post_xpresspost','canada_post_regular') THEN
            SET v_shipping_method = 'canada_post_regular';
        END IF;

        INSERT INTO fulfillment (payment_id, order_ref, status, shipping_method, tracking_number, shipping_address, estimated_delivery)
        VALUES (p_payment_id, p_order_ref, 'pending', v_shipping_method, NULL, v_shipping_address, DATE_ADD(CURDATE(), INTERVAL 7 DAY));

        SET v_fulfillment_id = LAST_INSERT_ID();

        SELECT amount, currency INTO v_amount, v_currency FROM payments WHERE id = p_payment_id;

        INSERT INTO unified_orders (engine_payment_id, order_ref, customer_hash, total_amount, currency, status, payment_status, fulfillment_status, shipping_address, source_system)
        VALUES (p_payment_id, p_order_ref, COALESCE(v_customer_hash, 'unknown'), v_amount, v_currency, 'pending', 'completed', 'pending', v_shipping_address, 'engine');
    END IF;
END //

CREATE PROCEDURE IF NOT EXISTS sp_update_trust_score(
    IN p_session_id     VARCHAR(64),
    IN p_event_type     VARCHAR(20),
    OUT p_new_score     DECIMAL(3,2)
)
SQL SECURITY INVOKER
BEGIN
    DECLARE v_current_score DECIMAL(3,2);
    DECLARE v_delta DECIMAL(3,2) DEFAULT 0.00;

    SELECT trust_score INTO v_current_score FROM sessions WHERE id = p_session_id;

    IF v_current_score IS NULL THEN
        SET p_new_score = NULL;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Session not found';
    END IF;

    SET v_delta = CASE UPPER(p_event_type)
        WHEN 'EMAIL_VERIFY'   THEN 0.10
        WHEN 'PURCHASE'       THEN 0.20
        WHEN 'FAILED_PAYMENT' THEN -0.10
        WHEN 'SUSPICIOUS'     THEN -0.20
        WHEN 'CHARGEBACK'     THEN -0.50
        ELSE 0.00
    END;

    SET p_new_score = GREATEST(0.00, LEAST(1.00, v_current_score + v_delta));

    UPDATE sessions SET trust_score = p_new_score,
        purchase_count = CASE WHEN UPPER(p_event_type) = 'PURCHASE' THEN purchase_count + 1 ELSE purchase_count END,
        failed_payment_count = CASE WHEN UPPER(p_event_type) = 'FAILED_PAYMENT' THEN failed_payment_count + 1 ELSE failed_payment_count END,
        suspicious_activity_count = CASE WHEN UPPER(p_event_type) = 'SUSPICIOUS' THEN suspicious_activity_count + 1 ELSE suspicious_activity_count END,
        email_verified = CASE WHEN UPPER(p_event_type) = 'EMAIL_VERIFY' THEN TRUE ELSE email_verified END,
        last_activity = NOW()
    WHERE id = p_session_id;
END //

CREATE PROCEDURE IF NOT EXISTS sp_add_strike(
    IN p_ip             VARCHAR(45),
    IN p_fingerprint    VARCHAR(64),
    IN p_reason         VARCHAR(200),
    OUT p_action        VARCHAR(30)
)
SQL SECURITY INVOKER
BEGIN
    DECLARE v_current_count INT DEFAULT 0;
    DECLARE v_new_count INT;

    SELECT count INTO v_current_count FROM strikes
    WHERE ip = p_ip AND (fingerprint = p_fingerprint OR fingerprint IS NULL);

    IF v_current_count > 0 THEN
        SET v_new_count = v_current_count + 1;
        SET p_action = CASE WHEN v_new_count >= 3 THEN 'permanent_blackhole' WHEN v_new_count >= 2 THEN 'blackhole_7d' ELSE 'log_popup' END;

        UPDATE strikes SET count = v_new_count, action = p_action,
            expires_at = CASE p_action WHEN 'permanent_blackhole' THEN DATE_ADD(NOW(), INTERVAL 100 YEAR) WHEN 'blackhole_7d' THEN DATE_ADD(NOW(), INTERVAL 7 DAY) ELSE DATE_ADD(NOW(), INTERVAL 1 DAY) END
        WHERE ip = p_ip AND (fingerprint = p_fingerprint OR fingerprint IS NULL);
    ELSE
        SET v_new_count = 1;
        SET p_action = 'log_popup';
        INSERT INTO strikes (ip, fingerprint, count, action, expires_at)
        VALUES (p_ip, p_fingerprint, 1, 'log_popup', DATE_ADD(NOW(), INTERVAL 1 DAY));
    END IF;

    IF v_new_count >= 2 THEN
        INSERT INTO security_events (node_id, event_type, severity, source_ip, description, metadata)
        VALUES ('669427', 'strike', CASE WHEN v_new_count >= 3 THEN 'critical' ELSE 'high' END, p_ip, p_reason,
            JSON_OBJECT('strike_count', v_new_count, 'action', p_action, 'fingerprint', p_fingerprint));
    END IF;
END //

CREATE PROCEDURE IF NOT EXISTS sp_check_session(
    IN p_session_id     VARCHAR(64),
    OUT p_valid         BOOLEAN,
    OUT p_trust         DECIMAL(3,2)
)
SQL SECURITY INVOKER
BEGIN
    DECLARE v_lifetime TIMESTAMP;

    SELECT max_lifetime, trust_score INTO v_lifetime, p_trust FROM sessions WHERE id = p_session_id;

    IF v_lifetime IS NULL THEN
        SET p_valid = FALSE;
        SET p_trust = 0.00;
    ELSEIF v_lifetime < NOW() THEN
        SET p_valid = FALSE;
        SET p_trust = 0.00;
    ELSE
        SET p_valid = TRUE;
        UPDATE sessions SET last_activity = NOW() WHERE id = p_session_id;
    END IF;
END //

CREATE PROCEDURE IF NOT EXISTS sp_process_canada_post_webhook(
    IN p_tracking_number    VARCHAR(64),
    IN p_event_data         JSON
)
SQL SECURITY INVOKER
BEGIN
    DECLARE v_fulfillment_id BIGINT;
    DECLARE v_event_code VARCHAR(20);
    DECLARE v_event_desc VARCHAR(200);
    DECLARE v_location VARCHAR(200);
    DECLARE v_event_ts TIMESTAMP;
    DECLARE v_new_status VARCHAR(20);
    DECLARE v_old_status VARCHAR(20);

    SELECT id, status INTO v_fulfillment_id, v_old_status FROM fulfillment WHERE tracking_number = p_tracking_number;

    IF v_fulfillment_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tracking number not found';
    END IF;

    SET v_event_code = JSON_UNQUOTE(JSON_EXTRACT(p_event_data, '$.event_code'));
    SET v_event_desc = JSON_UNQUOTE(JSON_EXTRACT(p_event_data, '$.event_description'));
    SET v_location = JSON_UNQUOTE(JSON_EXTRACT(p_event_data, '$.location'));
    SET v_event_ts = STR_TO_DATE(JSON_UNQUOTE(JSON_EXTRACT(p_event_data, '$.event_timestamp')), '%Y-%m-%dT%H:%i:%sZ');

    INSERT INTO canada_post_tracking (fulfillment_id, tracking_number, event_code, event_description, location, event_timestamp)
    VALUES (v_fulfillment_id, p_tracking_number, v_event_code, v_event_desc, v_location, COALESCE(v_event_ts, NOW()));

    SET v_new_status = CASE v_event_code
        WHEN '3000' THEN 'shipped'
        WHEN '5000' THEN 'shipped'
        WHEN '7000' THEN 'delivered'
        WHEN '8000' THEN 'delivered'
        ELSE NULL
    END;

    IF v_new_status IS NOT NULL AND v_new_status != v_old_status THEN
        UPDATE fulfillment SET status = v_new_status,
            shipped_at = CASE WHEN v_new_status = 'shipped' AND shipped_at IS NULL THEN NOW() ELSE shipped_at END,
            delivered_at = CASE WHEN v_new_status = 'delivered' THEN NOW() ELSE delivered_at END,
            canada_post_api_response = p_event_data
        WHERE id = v_fulfillment_id;
    END IF;

    INSERT INTO logs (node_id, level, category, message, metadata)
    VALUES ('669427', 'info', 'fulfillment', CONCAT('Canada Post webhook: ', p_tracking_number, ' - ', v_event_desc),
        JSON_OBJECT('tracking_number', p_tracking_number, 'event_code', v_event_code, 'fulfillment_id', v_fulfillment_id));
END //

DELIMITER ;
