DELIMITER $$

USE `signalmash_prod2`$$

DROP PROCEDURE IF EXISTS `AddTariff`$$
################### added int_day_num INT(11) Start #################
CREATE DEFINER=`root`@`localhost` PROCEDURE `AddTariff`(var_account INT(11), var_type VARCHAR(255), var_name VARCHAR(100), var_description VARCHAR(255), int_day_num INT(11))
################### End #########################################
BEGIN 

	DECLARE tariff_id INT(11);

	
	################ added (day_num) and  (int_day_num)  Start###################
	INSERT INTO `tariff` (`idaccount`,`type`,`name`, `description`, `day_num`) VALUES (var_account, var_type, var_name, var_description, int_day_num); 
	################ added (day_num) and value is (int_day_num) End ###################
	SET @tariff_id=LAST_INSERT_ID();

	

	INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES 

	(var_account, @tariff_id, '1', 'voice', 'voice_api', 0,0,0, 'app_calling' ),

	(var_account, @tariff_id, '2', 'voice', 'voice_api', 0,0,0, 'sip_interface' ),

	(var_account, @tariff_id, '3', 'voice', 'voice_api', 0,0,0, 'secure_media' );

	

	INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES 

	(var_account, @tariff_id, '1', 'voice', 'inteligent', 0, 0,0, 'conference_calls' ),

	(var_account, @tariff_id, '2', 'voice', 'inteligent', 0, 0,0, 'call_recording' ),

	(var_account, @tariff_id, '3', 'voice', 'inteligent', 0, 0,0, 'call_recording_storage' ),

	(var_account, @tariff_id, '4', 'voice', 'inteligent', 0, 0,0, 'answering_machine_detection' ),

	(var_account, @tariff_id, '5', 'voice', 'inteligent', 0, 0,0, 'Speech-to-text_Transcription' );

	

	INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES 

	(var_account, @tariff_id, '1', 'voice', 'number', 0, 0,0, 'local_Number' ),

	(var_account, @tariff_id, '2', 'voice', 'number', 0, 0,0, 'toll_free_number' );

	

	INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES 

	(var_account, @tariff_id, '1', 'voice', 'depth_voice', 0, 0,0, 'united_states_toll_free' ),

	(var_account, @tariff_id, '2', 'voice', 'depth_voice', 0, 0,0, 'united_states_lower 48 (Zone 1)' ),

	(var_account, @tariff_id, '3', 'voice', 'depth_voice', 0, 0,0, 'united_states - Hawaii (Zone 2)808' ),

	(var_account, @tariff_id, '4', 'voice', 'depth_voice', 0, 0,0, 'united_states - Alaska (Zone 3)907' ),
	
	(var_account, @tariff_id, '5', 'voice', 'depth_voice',0, 0,0, 'united_state High Cost (Zone 4)' ),

	(var_account, @tariff_id, '6', 'voice', 'depth_voice',0, 0,0, 'united_state High Cost (Zone 5)' ),
	################### added row for shi_trunking Start #################
	(var_account, @tariff_id, '7', 'voice', 'depth_voice',0, 0,0, 'sip_trunking' );
	################### added row for shi_trunking ENd #################



	INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES 

	(var_account, @tariff_id, '1', 'elastic', 'sip_trunking', 0, 0,0, 'local_calls' ),

	(var_account, @tariff_id, '2', 'elastic', 'sip_trunking', 0, 0,0, 'toll_free_call' );



	INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES 

	(var_account, @tariff_id, '1', 'elastic', 'inteligent', 0, 0,0, 'unlimited_call_concurrency' ),

	(var_account, @tariff_id, '2', 'elastic', 'inteligent', 0, 0,0, 'secure_trunking' ),

	(var_account, @tariff_id, '3', 'elastic', 'inteligent', 0, 0,0, 'emergency_calling' ),

	(var_account, @tariff_id, '4', 'elastic', 'inteligent', 0, 0,0, 'call_recording' ),

	(var_account, @tariff_id, '5', 'elastic', 'inteligent', 0, 0,0, 'call_recording_storage' ),

	(var_account, @tariff_id, '6', 'elastic', 'inteligent', 0, 0,0, 'T.38_fax_support' );



	INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES 

	(var_account, @tariff_id, '1', 'elastic', 'number', 0, 0,0, 'local_Number' ),

	(var_account, @tariff_id, '2', 'elastic', 'number', 0, 0,0, 'toll_free_number' );



	INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES 

	(var_account, @tariff_id, '1', 'elastic', 'depth_voice',0,0,0, 'united_states_toll_free' ),

	(var_account, @tariff_id, '2', 'elastic', 'depth_voice',0,0,0, 'united_states_lower 48 (Zone 1)' ),

	(var_account, @tariff_id, '3', 'elastic', 'depth_voice',0,0,0, 'united_states - Hawaii (Zone 2)' ),

	(var_account, @tariff_id, '4', 'elastic', 'depth_voice',0,0,0, 'united_states - Alaska (Zone 3)' ),
	
	(var_account, @tariff_id, '5', 'elastic', 'depth_voice',0, 0,0, 'united_state High Cost (Zone 4)' ),

	(var_account, @tariff_id, '6', 'elastic', 'depth_voice',0, 0,0, 'united_state High Cost (Zone 5)' ),
	################### added row for shi_trunking Start #################
	(var_account, @tariff_id, '7', 'elastic', 'depth_voice',0, 0,0, 'sip_trunking' );
	################### added row for shi_trunking ENd #################



	INSERT INTO `sms_rate` (`idaccount`, id_tariff, `priority`, `category`, `type`, `MO_rates`, `MT_rates`, `subcategory`, `categoryvalue`) VALUES 

	(var_account, @tariff_id, '1' ,'10_dlc', 'unregistered',0,0, 'SMS', '10 DLC (10 digit long code)'),

	(var_account, @tariff_id, '2' ,'10_dlc', 'unregistered',0,0, 'MMS', '10 DLC (10 digit long code)'),

	(var_account, @tariff_id, '3' ,'toll_free', 'unregistered',0,0, 'SMS', 'Toll Free'),

	(var_account, @tariff_id, '4' ,'toll_free', 'unregistered',0,0, 'MMS', 'Toll Free'),

	(var_account, @tariff_id, '5' ,'short_code', 'unregistered',0,0, 'SMS', 'Short Code'),

	(var_account, @tariff_id, '6' ,'short_code', 'unregistered',0,0, 'MMS', 'Short Code');



	INSERT INTO `voice_rates` (`idaccount`,`idtariff`,`priority`, `type`, `subtype`, `rate`, `mo`, `mt`, `category`) VALUES 

	(var_account, @tariff_id, '1', 'message', 'message_activation',  0,0,0, 'local_number' ),

	(var_account, @tariff_id, '2', 'message', 'message_activation',  0,0,0, 'toll_free_number' ),

	(var_account, @tariff_id, '3', 'message', 'message_activation',  0,0,0, 'short_code_lease' ),

	(var_account, @tariff_id, '4', 'message', 'message_activation',  0,0,0, 'activate_messaging_Phone_Number' );



	SELECT @tariff_id `idtariff`, var_name `name`; 

END$$

DELIMITER ;