use PHPBB3;

create procedure PHPBB3..log_error (in state any, in msg any)
{
  if (state = 100)
    {
      state := '100';
      msg := 'Not found';
    }
  log_text (sprintf ('phpBB3 [%s] %s', state, msg));
}
;

CREATE TRIGGER NEWS_SERVERS_PHPBB_AI AFTER INSERT ON DB.DBA.NEWS_SERVERS REFERENCING NEW AS SRV
{
  declare lid, rid, fid int;

  SET TRIGGERS OFF;
  declare exit handler for sqlstate '*' {
    PHPBB3..log_error (__SQL_STATE, __SQL_MESSAGE);
    SET TRIGGERS ON;
    return;
  };
  lid := coalesce ((select max (left_id) from phpBB3..phpbb_forums), 1);
  lid := lid + 1;
  rid := lid + 1;
  INSERT INTO phpBB3..phpbb_forums (virt_id, parent_id, left_id, right_id, forum_name)
      values (SRV.NS_ID,
       0,
       lid,
       rid,
       case when SRV.NS_USER = '' or isnull (SRV.NS_USER) then '' else SRV.NS_USER||'@' end ||
       SRV.NS_SERVER ||
       case when SRV.NS_PORT <> 119 then concat (':',cast (SRV.NS_PORT as varchar)) else '' end);
  fid := identity_value ();
  phpBB3..init_acl (fid);
  insert into phpBB3..phpbb_acl_users  (user_id, forum_id, auth_option_id, auth_setting, auth_role_id)
        values (1, fid, 0, 0, 17);
  SET TRIGGERS ON;
}
;

CREATE TRIGGER NEWS_SERVERS_PHPBB_AD AFTER DELETE ON DB.DBA.NEWS_SERVERS REFERENCING OLD AS SRV
{
  declare exit handler for sqlstate '*'{
    PHPBB3..log_error (__SQL_STATE, __SQL_MESSAGE);
    SET TRIGGERS ON;
    return;
  };
  SET TRIGGERS OFF;
  DELETE FROM phpBB3..phpbb_acl_groups WHERE forum_id = phpBB3..virt2forum_id (coalesce(SRV.NS_ID, -1), 0);
  DELETE FROM phpBB3..phpbb_forums WHERE virt_id = SRV.NS_ID;
  SET TRIGGERS ON;
}
;

CREATE TRIGGER NEWS_GROUPS_PHPBB_AI AFTER INSERT ON DB.DBA.NEWS_GROUPS REFERENCING NEW AS GRP
{
  declare lid, rid, pid, fid int;
  declare exit handler for sqlstate '*', not found {
    PHPBB3..log_error (__SQL_STATE, __SQL_MESSAGE);
    SET TRIGGERS ON;
    return;
  };
  SET TRIGGERS OFF;


  select forum_id, right_id into pid, rid from phpBB3..phpbb_forums where virt_id = coalesce (GRP.NG_SERVER,-1);
  update phpBB3..phpbb_forums set right_id = right_id + 2  where right_id >= rid;
  update phpBB3..phpbb_forums set left_id = left_id + 2  where left_id > rid;
  lid := rid;
  rid := rid + 1;
  INSERT INTO phpBB3..phpbb_forums (virt_id, parent_id, left_id, right_id, forum_name, forum_desc, forum_status,
                                    forum_posts, forum_topics, forum_last_post_id,
                                    prune_next, enable_prune, forum_type)
      values (GRP.NG_GROUP, -- forum_id
       pid, -- parent_id
       lid,
       rid,
       GRP.NG_NAME, -- forum_name,
       '', -- forum_desc,
       0,  -- forum_status,
       0,  --forum_posts,
       0,  --forum_topics,
       0,  --forum_last_post_id,
       0,  --prune_next,
       0,  --prune_enable,
       1);
  fid := identity_value ();
  phpBB3..init_acl (fid);
  insert into phpBB3..phpbb_acl_users  (user_id, forum_id, auth_option_id, auth_setting, auth_role_id)
        values (1, fid, 0, 0, 17);
  SET TRIGGERS ON;
}
;

CREATE TRIGGER NEWS_GROUPS_PHPBB_AD AFTER DELETE ON DB.DBA.NEWS_GROUPS REFERENCING OLD AS GRP
{
  declare exit handler for sqlstate '*'{
    PHPBB3..log_error (__SQL_STATE, __SQL_MESSAGE);
    SET TRIGGERS ON;
    return;
  };
  SET TRIGGERS OFF;
  DELETE FROM phpBB3..phpbb_acl_groups WHERE forum_id = phpBB3..virt2forum_id (coalesce(GRP.NG_GROUP, -1), 1);
  DELETE FROM phpBB3..phpbb_forums WHERE virt_id = GRP.NG_GROUP;
  SET TRIGGERS ON;
}
;

CREATE TRIGGER NEWS_MULTI_MSG_PHPBB_AI AFTER INSERT ON DB.DBA.NEWS_MULTI_MSG REFERENCING NEW AS MULTI
{
  declare _forum_id, _post_id, _poster_id, _topic_id integer;
  declare _head, _head_des, _body any;
  declare _email, _from varchar;
  declare _new_topic integer;

  declare exit handler for sqlstate '*'{
    PHPBB3..log_error (__SQL_STATE, __SQL_MESSAGE);
    SET TRIGGERS ON;
    return;
  };


  SET TRIGGERS OFF;
  _forum_id := phpBB3..virt2forum_id (MULTI.NM_GROUP, 1);
  if (_forum_id is null) -- No such group exposed as forum
    {
      SET TRIGGERS ON;
      return;
    }

  _post_id := connection_get ('post_id');
  _topic_id := connection_get ('topic_id');

  if (_topic_id is not null)
    {
      update phpBB3..phpbb_topics set topic_virt_id = MULTI.NM_KEY_ID where topic_id = _topic_id;
    }
  if (_post_id is not null)
    {
      update phpBB3..phpbb_posts set post_virt_id = MULTI.NM_KEY_ID where post_id = _post_id;
      if (row_count ())
	{
	  connection_set ('post_id', null);
	  connection_set ('topic_id', null);
	  SET TRIGGERS ON;
	  return;
	}
    }


  _new_topic := 0;

  SELECT MSG.NM_HEAD, MSG.NM_BODY INTO _head, _body FROM DB.DBA.NEWS_MSG MSG WHERE MSG.NM_ID = MULTI.NM_KEY_ID;

  _topic_id := phpBB3..get_topic_id (MULTI.NM_GROUP, _head, MULTI.NM_KEY_ID);
  _head_des := deserialize (_head)[0];

  _from := phpBB3..decode_subj (coalesce (get_keyword_ucase ('From', _head_des), ''));
  _poster_id := phpBB3..get_poster_id (_from);


    if (_topic_id is null)
    {
      INSERT INTO phpBB3..phpbb_topics (topic_virt_id, forum_id, topic_title, topic_poster, topic_time, topic_views,
                                          topic_replies, topic_status, topic_type, topic_first_post_id,
                                          topic_last_post_id, topic_moved_id)
      VALUES (MULTI.NM_KEY_ID,
	     _forum_id, --forum_id
             phpBB3..decode_subj (get_keyword_ucase ('Subject',_head_des)),   --topic_title
             _poster_id,  --topic_poster
             phpBB3..get_post_date (_head), --topic_time
             0, --topic_views
             0, --topic_replies
             0, --topic_status
             0, --topic_type
             0, --topic_first_post_id
             0, --topic_last_post_id
             0
             );
      _topic_id := identity_value ();
      insert into phpBB3..phpbb_topics_posted (user_id, topic_id, topic_posted) values (_poster_id, _topic_id, 1);
      _new_topic := 1;

    };

    INSERT INTO phpBB3..phpbb_posts (post_virt_id, topic_id, forum_id, poster_id, post_time, poster_ip, post_username,
                                       enable_bbcode, enable_smilies, enable_sig, post_edit_time, post_edit_count,
				       post_subject, post_text)
    VALUES (
	   MULTI.NM_KEY_ID,
	   _topic_id,
           _forum_id,
           _poster_id,
           phpBB3..get_post_date (_head), -- post_time
           http_client_ip (), --poster_ip
           sprintf ('%V', _from),
           0,          --enable_bbcode
           1,          --enable_smilies
           0,          --enable_sig
           0,          --post_edit_time
           0,          --post_edit_count
	   phpBB3..decode_subj (get_keyword_ucase ('Subject',_head_des)),
	   phpBB3..decode_body (_body)
           );

    _post_id := identity_value ();

    if (not _new_topic)
      {
        UPDATE phpBB3..phpbb_topics SET topic_replies = topic_replies + 1 , topic_last_post_id = _post_id WHERE topic_id = _topic_id;
      }
    else
      {
        UPDATE phpBB3..phpbb_topics SET topic_last_post_id = _post_id WHERE topic_id = _topic_id;
      }

    UPDATE phpBB3..phpbb_forums
       SET forum_posts = forum_posts + 1,
           forum_last_post_id = _post_id,
           forum_topics = forum_topics + _new_topic
     WHERE forum_id = _forum_id;
  SET TRIGGERS ON;
};

CREATE TRIGGER NEWS_MULTI_MSG_PHPBB_AD AFTER DELETE ON DB.DBA.NEWS_MULTI_MSG REFERENCING OLD AS MULTI
{
  declare _post_id, _topic_id, _topic_exists, _last_id, _first_id, _forum_id integer;
--  dbg_obj_print (current_proc_name ());

  declare exit handler for sqlstate '*', not found {
    PHPBB3..log_error (__SQL_STATE, __SQL_MESSAGE);
    SET TRIGGERS ON;
    return;
  };
  SET TRIGGERS OFF;

  _topic_exists := 0;

  _topic_id := (SELECT topic_id FROM phpBB3..phpbb_posts WHERE post_virt_id = MULTI.NM_KEY_ID);

  DELETE FROM phpBB3..phpbb_posts WHERE post_virt_id = MULTI.NM_KEY_ID;

  if ((SELECT count (topic_id) FROM phpBB3..phpbb_posts WHERE topic_id = _topic_id) = 0)
  {
    DELETE FROM phpBB3..phpbb_topics WHERE topic_id = _topic_id;
    DELETE FROM phpBB3..phpbb_topics_posted WHERE topic_id = _topic_id;
  }
  else
  {
    _topic_exists := 1;
    UPDATE phpBB3..phpbb_topics SET topic_replies = topic_replies - 1 WHERE topic_id = _topic_id;
  }

  _forum_id := phpBB3..virt2forum_id (MULTI.NM_GROUP, 1);
  _last_id := (SELECT TOP 1 post_id FROM phpBB3..phpbb_posts WHERE forum_id = _forum_id ORDER BY post_time);
  UPDATE phpBB3..phpbb_forums SET forum_posts = forum_posts - 1, forum_last_post_id = coalesce (_last_id, 0)
	  WHERE forum_id = _forum_id;

  if (_topic_exists)
    {
      _last_id := (SELECT TOP 1 post_id FROM phpBB3..phpbb_posts PS1  WHERE PS1.topic_id = _topic_id ORDER BY post_time);
      _first_id := (SELECT TOP 1 post_id FROM phpBB3..phpbb_posts PS2  WHERE PS2.topic_id = _topic_id ORDER BY post_time DESC);

      if (isnull (_last_id))
	_last_id := 0;
      if  (isnull (_first_id))
        _first_id := 0;

      UPDATE phpBB3..phpbb_topics SET topic_first_post_id = _last_id, topic_last_post_id = _first_id WHERE topic_id = _topic_id;
    }
  SET TRIGGERS ON;
};


CREATE TRIGGER phpbb_topics_IOI AFTER INSERT ON phpBB3..phpbb_topics
{
  connection_set ('topic_id', topic_id);
};

CREATE TRIGGER phpbb_posts_BI BEFORE UPDATE ON phpBB3..phpbb_posts
{
  if (post_virt_id is not null)
    signal ('phpB3','This post is a news message, you are not permited to do this via phpBB3');
};

CREATE TRIGGER phpbb_posts_BD BEFORE DELETE ON phpBB3..phpbb_posts
{
  if (post_virt_id is not null)
    signal ('phpB3','This post is a news message, you are not permited to do this via phpBB3');
};

CREATE TRIGGER phpbb_posts_IOI AFTER INSERT ON phpBB3..phpbb_posts
{
   declare new_body, old_hdr, new_subj, nfrom, new_ref,_email,_poster_id any;
   declare _old_ref, _groups, _old_id any;
   declare new_mess any;
   declare temp_forum_id, temp_topic_id, _nm_id, virt_group any;
   declare _id varchar;
   declare _pos, poster_uid integer;

--  dbg_obj_print (current_proc_name ());
  declare exit handler for sqlstate '*', not found {
    PHPBB3..log_error (__SQL_STATE, __SQL_MESSAGE);
    return;
  };

   new_body := phpBB3..normalize_text (post_text);
   new_subj := trim (phpBB3..normalize_text (post_subject));

   nfrom := post_username;
   temp_forum_id := forum_id;
   _poster_id := poster_id;
   temp_topic_id := topic_id;
   virt_group := (select virt_id from phpBB3..phpbb_forums where forum_id = temp_forum_id);

   if (virt_group is null)
     return;

   _email := null;
   poster_uid := 'nobody';
   {
     whenever not found goto no_user;
     SELECT case when U_FULL_NAME is not null and trim (U_FULL_NAME) <> ''
                     then '"' || U_FULL_NAME || '" <' || U_E_MAIL || '>'
                     else U_E_MAIL end, U_NAME
		     into _email, poster_uid
                FROM DB.DBA.SYS_USERS, phpBB3..phpbb_users WHERE U_NAME = username and user_id = _poster_id;
      no_user:;
   }
   if (length (_email))
     nfrom := _email;

   if (0 = length (nfrom))
     nfrom := 'noreply@noreply';

   new_ref := '';

   if (exists (SELECT 1 FROM phpBB3..phpbb_topics WHERE topic_id = temp_topic_id))
   {
     _nm_id := (SELECT topic_virt_id FROM phpBB3..phpbb_topics WHERE topic_id = temp_topic_id);
     old_hdr := (SELECT deserialize (NM_HEAD)[0] FROM DB.DBA.NEWS_MSG WHERE NM_ID = _nm_id);
   }

   if (isarray (old_hdr))
     {
	_old_ref := get_keyword_ucase ('References', old_hdr , NULL);
	_old_id := get_keyword_ucase ('Message-ID', old_hdr , '');
	_groups := get_keyword_ucase ('Newsgroups', old_hdr , '');

	if (_old_ref is NULL)
	  new_ref := _old_id;
	else
	  new_ref := _old_ref || ' ' || _old_id;
     }
   else
     {
	_groups := (SELECT NG_NAME FROM DB.DBA.NEWS_GROUPS WHERE NG_GROUP = virt_group);
     }
  -- Message header
  if (not length (new_subj))
    new_subj := '~no subject~';

  new_mess := 'From: ' || nfrom || '\r\n';
  new_mess := new_mess || 'Subject: ' || new_subj || '\r\n';
  new_mess := new_mess || 'Newsgroups: ' || _groups || '\r\n';
  if (new_ref <> '')
    new_mess := new_mess || 'References: ' || new_ref || '\r\n';
  new_mess := new_mess || 'Date: ' || DB.DBA.date_rfc1123 (now ()) || '\r\n';
  new_mess := new_mess || 'X-Newsreader: ' || 'phpBB3 integrated on Virtuoso' || '\r\n';
  _id := MD5 (concat (new_mess,'\r\n',new_body, cast (now () as varchar)));

  _pos := position('<',nfrom);
  if (_pos and position ('>', nfrom))
    nfrom := substring (nfrom, _pos + 1, position ('>', nfrom, _pos) - _pos - 1);
  _id := concat ('<', _id, '@', replace (nfrom,'@','.at.'), '>');
  new_mess := new_mess || 'Message-ID: ' || _id || '\r\n';
  -- delimiter
  new_mess := new_mess || '\r\n';

  -- Message body
  new_mess := new_mess || new_body || '\r\n\r\n.\r\n';

  -- export postid & user which doing operation
  connection_set ('nntp_uid', poster_uid);
  connection_set ('post_id', post_id);

  -- call NNTP procedure to make news message
  DB.DBA.ns_post (new_mess);

  -- clear global variables
  connection_set ('nntp_uid', NULL);
  connection_set ('topic_id', NULL);
  connection_set ('post_id', NULL);
};


CREATE TRIGGER phpbb_forums_BI BEFORE INSERT ON phpBB3..phpbb_forums referencing new as N
{
  if (N.virt_id is not null)
    signal ('phpB3','Forums are news groups, you are not permited to do this via phpBB3');
};

CREATE TRIGGER phpbb_forums_BD BEFORE DELETE ON phpBB3..phpbb_forums referencing old as O
{
  if (O.virt_id is not null)
    signal ('phpB3','Forums are news groups, you are not permited to do this via phpBB3');
};