use lego;

delete from t_atic_sysconfig where sys_var='scpFeedbackServerIP';
delete from t_atic_sysconfig where sys_var='scpFeedbackServerPort';
INSERT INTO `t_atic_sysconfig`(`sys_var`,`sys_value`,`remark`) VALUES ('scpFeedbackServerIP','117.78.61.235','');
INSERT INTO `t_atic_sysconfig`(`sys_var`,`sys_value`,`remark`) VALUES ('scpFeedbackServerPort','8446','');


DROP PROCEDURE IF EXISTS add_device_esn;
DELIMITER //
CREATE PROCEDURE add_device_esn() BEGIN
    IF NOT EXISTS(SELECT * FROM information_schema.columns WHERE table_name = 't_atic_device' AND column_name ='device_esn')
    THEN
        ALTER TABLE t_atic_device ADD COLUMN device_esn varchar(40) DEFAULT null;
    END IF;
END;
//
DELIMITER ;
CALL add_device_esn();

DROP PROCEDURE IF EXISTS add_device_esn;


DROP PROCEDURE IF EXISTS add_ipreputation_type;
DELIMITER //
CREATE PROCEDURE add_ipreputation_type() BEGIN
    IF NOT EXISTS(SELECT * FROM information_schema.columns WHERE table_name = 't_vsm_zone_ddos_policy' AND column_name ='ipreputation_service_type')
    THEN
        ALTER TABLE t_vsm_zone_ddos_policy ADD COLUMN ipreputation_service_type INT (11) DEFAULT NULL;
    END IF;
END;
//
DELIMITER ;
CALL add_ipreputation_type();

DROP PROCEDURE IF EXISTS add_ipreputation_type;


DROP PROCEDURE IF EXISTS add_ipreputation_enable;
DELIMITER //
CREATE PROCEDURE add_ipreputation_enable() BEGIN
    IF NOT EXISTS(SELECT * FROM information_schema.columns WHERE table_name = 't_vsm_neconf_abnormaldfn' AND column_name ='ip_repuation_enable')
    THEN
        ALTER TABLE t_vsm_neconf_abnormaldfn ADD COLUMN ip_repuation_enable BIT (1) DEFAULT NULL ;
        ALTER TABLE t_vsm_neconf_abnormaldfn ADD COLUMN gray_threshold INT(11) DEFAULT null;
        ALTER TABLE t_vsm_neconf_abnormaldfn ADD COLUMN black_threshold INT(11) DEFAULT null;
        ALTER TABLE t_vsm_neconf_abnormaldfn ADD COLUMN statistics_feedback BIT (11) DEFAULT null;
        ALTER TABLE t_vsm_neconf_abnormaldfn ADD COLUMN sample_rate INT(11) DEFAULT null;
    END IF;
END;
//
DELIMITER ;
CALL add_ipreputation_enable();

DROP PROCEDURE IF EXISTS add_ipreputation_enable;