USE `gateguard`;

DROP PROCEDURE IF EXISTS `sp_createVisitors`;

DELIMITER $$
CREATE PROCEDURE `sp_createVisitors` (
    IN p_FULL_NAME VARCHAR(64),
    IN p_AADHAR_UID VARCHAR(12),
	IN p_MOBILE_NUMBER VARCHAR(15),
    IN p_TYPE_ID INT,	
    IN p_MODIFIED_BY INT,
    OUT v_VISITOR_ID INT,
	OUT v_ERROR_CODE VARCHAR(255),
	OUT v_ERROR_MESSAGE VARCHAR(255)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
    BEGIN
         GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
		SET v_ERROR_MESSAGE = @text;
		SET v_ERROR_CODE = CONCAT(@errno, '|', @sqlstate);
		ROLLBACK;
    END;

    SET AUTOCOMMIT=0;
    START TRANSACTION;

    INSERT INTO visitors (FULL_NAME, AADHAR_UID, MOBILE_NUMBER, TYPE_ID)
    VALUES (p_FULL_NAME, p_AADHAR_UID, p_MOBILE_NUMBER, p_TYPE_ID);

    SET v_VISITOR_ID = LAST_INSERT_ID();

    INSERT INTO visitors_audit (
        MODIFIED_BY, 
        MODIFIED_ON, 
        MODIFICATION, 
        ID, 
        FULL_NAME, 
		AADHAR_UID,
        MOBILE_NUMBER,
		TYPE_ID
    ) VALUES (
        p_MODIFIED_BY,
        SYSDATE(),
        'CREATED',
		v_VISITOR_ID,
        p_FULL_NAME,
		p_AADHAR_UID,
        p_MOBILE_NUMBER,
		p_TYPE_ID
    );

    COMMIT;
END$$
DELIMITER ;
