DROP TRIGGER IF EXISTS validate_device_update;
DROP TRIGGER IF EXISTS validate_device_insert;
DROP TRIGGER IF EXISTS defuser;
DROP TRIGGER IF EXISTS insert_date;
DROP TRIGGER IF EXISTS kamailio_registration_to_devices;
DROP TRIGGER IF EXISTS kamailio_unregistration_to_devices;
DROP TRIGGER IF EXISTS set_user_changes_present;
DROP TRIGGER IF EXISTS device_changes_present_update;
DROP TRIGGER IF EXISTS dc_group_changes_present_update;
DROP TRIGGER IF EXISTS create_user_blocked_action;
DROP TRIGGER IF EXISTS users_update_trigger;

#CREATE TRIGGER `insert_date` BEFORE INSERT ON `calls` FOR EACH ROW SET NEW.date = LEFT(NEW.calldate, 10);

DELIMITER //

CREATE TRIGGER kamailio_registration_to_devices
AFTER INSERT
   ON location FOR EACH ROW
BEGIN

  DECLARE var_username varchar(64);
  DECLARE var_ipaddr varchar(64);
  DECLARE var_port varchar(64);

  SET var_username = NEW.username;
  SET var_ipaddr = SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.received, ':', -2), ':', 1);
  SET var_port = SUBSTRING_INDEX(SUBSTRING_INDEX(NEW.received, ':', -2), ':', -1);

  -- Update user details in devices tables
  UPDATE devices
     SET ipaddr = var_ipaddr,
         ipaddr_range_start = INET_ATON(var_ipaddr),
         ipaddr_range_end = INET_ATON(var_ipaddr),
         host = var_ipaddr,
         port = var_port,
         reg_status = 'REGISTERED'
   WHERE op_username = var_username
     AND dynamic = 'yes'
     AND hidden_device = 0
     AND LENGTH(var_username) > 0
     AND LENGTH(var_ipaddr) > 0
     AND LENGTH(var_port) > 0;
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER kamailio_unregistration_to_devices
AFTER DELETE
   ON location FOR EACH ROW
BEGIN

  DECLARE var_username varchar(64);

  SET var_username = OLD.username;

  -- Update reg_status in devices tables
  UPDATE devices
     SET reg_status = 'UNREGISTERED'
   WHERE op_username = var_username
     AND LENGTH(var_username) > 0;
END //

DELIMITER ;

DELIMITER //

CREATE TRIGGER users_update_trigger
BEFORE UPDATE
   ON users FOR EACH ROW
BEGIN

  -- set changes present
  IF (OLD.balance = NEW.balance) AND NOT (OLD.changes_present = 1 AND NEW.changes_present = 0) THEN
    SET NEW.changes_present = 1;
  END IF;

  -- create action when user is blocked
  IF (OLD.blocked != NEW.blocked) THEN
    IF (NEW.blocked = 1) THEN
        INSERT INTO actions (user_id, `date`, action, target_type, target_id) VALUES (0, NOW(), 'user_blocked', 'user', NEW.id);
    END IF;

    IF (NEW.blocked = 0) THEN
        INSERT INTO actions (user_id, `date`, action, target_type, target_id) VALUES (0, NOW(), 'user_unblocked', 'user', NEW.id);
    END IF;
  END IF;

END //

DELIMITER ;


-- Trigger to set changes_present_kam = 1 when device table changes. Used in Kamailio m4_cache_control module
DELIMITER //

CREATE TRIGGER device_changes_present_update
BEFORE UPDATE
    ON devices FOR EACH ROW
BEGIN
    IF (NEW.changes_present = 1) AND NOT (OLD.changes_present_kam = 1) THEN
        SET NEW.changes_present_kam = 1;
    END IF;
END //

DELIMITER ;


-- Trigger to set changes_present_kam = 1 when dc_groups table changes. Used in Kamailio m4_cache_control module
DELIMITER //

CREATE TRIGGER dc_group_changes_present_update
BEFORE UPDATE
    ON dc_groups FOR EACH ROW
BEGIN
    IF (NEW.changes_present = 1) AND (OLD.changes_present_kam = 0) THEN
        SET NEW.changes_present_kam = 1;
    END IF;
END //

DELIMITER ;