UPDATE users usr SET
    usr."ATTACH_ID" = NULL
  WHERE
    usr."ATTACH_ID" IS NOT NULL AND
    NOT EXISTS (
      SELECT
        1
      FROM
        tmp$attachments att
      WHERE
        usr."ATTACH_ID" = att."TMP$ATTACHMENT_ID" AND
        usr."ATTACH_TIME" = att."TMP$TIMESTAMP"
    );

  FOR
    SELECT
      cfg."USR_ID"
    FROM
      configs cfg
    WHERE
      cfg."USR_ID" = 3 AND  /* System Admin */
      cfg."SECTION" = 'GLOBAL' AND
      cfg."IDENT" = :in_ident
    INTO
      :var_usr_id
    AS CURSOR
      cur_cfg
  DO
    UPDATE configs cfg SET
      cfg."INT_VALUE" = :in_int_value,
      cfg."FLOAT_VALUE" = :in_float_value,
      cfg."STR_VALUE" = :in_str_value,
      cfg."BLOB_VALUE" = :in_blob_value
    WHERE
      CURRENT OF cur_cfg;

  UPDATE users usr SET
    usr."ATTACH_ID" = NULL
  WHERE
    usr."ATTACH_ID" IS NOT NULL AND
    NOT EXISTS (
      SELECT
        1
      FROM
        tmp$attachments att
      WHERE
        usr."ATTACH_ID" = att."TMP$ATTACHMENT_ID" AND
        usr."ATTACH_TIME" = att."TMP$TIMESTAMP"
    );

CREATE PROCEDURE sp_e_usr_login (
  in_name VARCHAR(64),
  in_password VARCHAR(16),
  in_trn_id INTEGER)
RETURNS (
  out_id SMALLINT)
AS
  DECLARE VARIABLE var_attach_id INTEGER;
  DECLARE VARIABLE var_attach_time TIMESTAMP;
  DECLARE VARIABLE var_host CHAR(31) CHARACTER SET NONE;
  DECLARE VARIABLE var_msg VARCHAR(32);
BEGIN
  EXECUTE PROCEDURE sp_i_usr_login_garbage;

  EXECUTE PROCEDURE sp_i_trn_attach_info(
    :in_trn_id
  ) RETURNING_VALUES (
    :var_attach_id,
    :var_attach_time,
    :var_host
  );

  SELECT
    usr."ID"
  FROM
    users usr
  WHERE (usr."NAME" = :in_name)
    AND (usr."USER_PASSWORD" = :in_password)
    AND ((usr."ATTACH_ID" IS NULL) OR (usr."LAST_HOST" = :var_host))
  INTO
    :out_id;

  IF (:out_id IS NULL) THEN BEGIN
    SELECT
      usr."ID"
    FROM
      users usr
    WHERE (usr."NAME" = :in_name)
      AND (usr."USER_PASSWORD" = :in_password)
    INTO
      :out_id;
    IF (:out_id IS NULL) THEN
      EXCEPTION ex_usr_not_found;
    ELSE
      EXCEPTION ex_usr_login;
  END ELSE BEGIN
    UPDATE
      users usr
    SET
      usr."ATTACH_ID" = :var_attach_id,
      usr."ATTACH_TIME" = :var_attach_time,
      usr."LAST_HOST" = :var_host
    WHERE
      usr."ID" = :out_id;

    var_msg = CAST(:var_host AS VARCHAR(32));
    EXECUTE PROCEDURE sp_i_usr_ins_log(
      out_id,
      0,  /* ultLogin */
      :var_msg
    );
  END
END

CREATE PROCEDURE sp_i_usr_login_garbage
AS
BEGIN
  UPDATE users usr SET
    usr."ATTACH_ID" = NULL
  WHERE
    usr."ATTACH_ID" IS NOT NULL AND
    NOT EXISTS (
      SELECT
        1
      FROM
        tmp$attachments att
      WHERE
        usr."ATTACH_ID" = att."TMP$ATTACHMENT_ID" AND
        usr."ATTACH_TIME" = att."TMP$TIMESTAMP"
    );
END

CREATE PROCEDURE sp_i_trn_attach_info (
  in_id INTEGER)
RETURNS (
  out_attach_id INTEGER,
  out_attach_time TIMESTAMP,
  out_host CHAR(31) CHARACTER SET none)
AS
BEGIN
  SELECT
    att."TMP$ATTACHMENT_ID",
    att."TMP$TIMESTAMP",
    att."TMP$USER_IP_ADDR"
  FROM
    tmp$transactions trn
    LEFT JOIN tmp$attachments att ON (
      att."TMP$ATTACHMENT_ID" = trn."TMP$ATTACHMENT_ID"
    )
  WHERE
    trn."TMP$TRANSACTION_ID" = :in_id
  INTO
    :out_attach_id,
    :out_attach_time,
    :out_host;
END

CREATE PROCEDURE sp_i_usr_ins_log (
  in_id INTEGER,
  in_log_type SMALLINT,
  in_str VARCHAR(1024))
AS
BEGIN
  INSERT INTO stream_obj_logs(
    "OBJ_ID",
    "OBJ_TYPE",
    "LOG_TYPE",
    "USR_ID",
    "LOG_STR"
  ) VALUES (
    :in_id,
    2, /* itUser = 2 */
    :in_log_type,
    :in_id,
    :in_str
  );
END

CREATE PROCEDURE sp_e_cfg_global_upd (
  in_ident VARCHAR(64),
  in_int_value INTEGER,
  in_float_value DOUBLE PRECISION,
  in_str_value VARCHAR(1024),
  in_blob_value BLOB SUB_TYPE 0 SEGMENT SIZE 80)
AS
  DECLARE VARIABLE var_usr_id SMALLINT;
BEGIN
  var_usr_id = NULL;
  FOR
    SELECT
      cfg."USR_ID"
    FROM
      configs cfg
    WHERE
      cfg."USR_ID" = 3 AND  /* System Admin */
      cfg."SECTION" = 'GLOBAL' AND
      cfg."IDENT" = :in_ident
    INTO
      :var_usr_id
    AS CURSOR
      cur_cfg
  DO
    UPDATE configs cfg SET
      cfg."INT_VALUE" = :in_int_value,
      cfg."FLOAT_VALUE" = :in_float_value,
      cfg."STR_VALUE" = :in_str_value,
      cfg."BLOB_VALUE" = :in_blob_value
    WHERE
      CURRENT OF cur_cfg;

  IF (:var_usr_id IS NULL) THEN
    INSERT INTO configs (
      "USR_ID",
      "SECTION",
      "IDENT",
      "INT_VALUE",
      "FLOAT_VALUE",
      "STR_VALUE",
      "BLOB_VALUE"
    ) VALUES (
      3,  /* System Admin */
      'GLOBAL',
      :in_ident,
      :in_int_value,
      :in_float_value,
      :in_str_value,
      :in_blob_value
    );

    INSERT INTO configs_bk (
      "USR_ID",
      "SECTION",
      "IDENT",
      "TIME",
      "INT_VALUE",
      "FLOAT_VALUE",
      "STR_VALUE",
      "BLOB_VALUE"
    ) VALUES (
      3,  /* System Admin */
      'GLOBAL',
      :in_ident,
      CURRENT_TIMESTAMP,
      :in_int_value,
      :in_float_value,
      :in_str_value,
      :in_blob_value
    );

  POST_EVENT 'EVENT_CFG_GLOBAL';
END