use phpBB3;

SET TRIGGERS OFF;

create procedure phpBB3..init_acl (in id int)
{
  insert into phpBB3..phpbb_acl_groups (group_id, forum_id, auth_option_id, auth_role_id, auth_setting)
      values (1, id, 0, 17, 0);
  insert into phpBB3..phpbb_acl_groups (group_id, forum_id, auth_option_id, auth_role_id, auth_setting)
      values (2, id, 0, 15, 0);
  insert into phpBB3..phpbb_acl_groups (group_id, forum_id, auth_option_id, auth_role_id, auth_setting)
      values (3, id, 0, 15, 0);
  insert into phpBB3..phpbb_acl_groups (group_id, forum_id, auth_option_id, auth_role_id, auth_setting)
      values (4, id, 0, 21, 0);
  insert into phpBB3..phpbb_acl_groups (group_id, forum_id, auth_option_id, auth_role_id, auth_setting)
      values (5, id, 0, 14, 0);
  insert into phpBB3..phpbb_acl_groups (group_id, forum_id, auth_option_id, auth_role_id, auth_setting)
      values (6, id, 0, 19, 0);
};

create procedure phpBB3..init_db_tree_rec (in fid int, in lid int)
{
  declare rid, id int;
  rid := lid + 1;
  id := lid;
  for select forum_id from phpBB3..phpbb_forums where parent_id = fid order by forum_id do
    {
       rid := phpBB3..init_db_tree_rec (forum_id, rid);
    }
  update phpBB3..phpbb_forums set left_id = lid, right_id = rid where forum_id = fid;
  return rid + 1;
}
;


create procedure phpBB3..init_db ()
{
  declare "RESULT" varchar;
  result_names("RESULT");

  declare exit handler for sqlstate '*'
    {
      __atomic (0);
      resignal;
    };

  __atomic(1);

  delete from phpBB3..phpbb_topics_posted where topic_id in
      (select t.topic_id from phpBB3..phpbb_topics t, phpBB3..phpbb_forums f where t.forum_id = f.forum_id and f.virt_id is not null);
  delete from phpBB3..phpbb_topics where forum_id in (select f.forum_id from phpBB3..phpbb_forums f where f.virt_id is not null);
  delete from phpBB3..phpbb_posts where post_virt_id is not null;
  delete from phpBB3..phpbb_forums where virt_id is not null;

  result('Tables cleared!');
  commit work;

  INSERT INTO phpBB3..phpbb_forums (virt_id, parent_id, left_id, right_id, forum_name)
      values (-1, 0, 0, 0, 'Virtuoso Local Server');

  INSERT INTO phpBB3..phpbb_forums (virt_id, parent_id, left_id, right_id, forum_name)
      SELECT SRV.NS_ID,
	     0,
	     0,
	     0,
     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
	 FROM DB.DBA.NEWS_SERVERS SRV;
  result('All categories done.');

  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)
      SELECT GRP.NG_GROUP, -- forum_id
      phpBB3..virt2forum_id (coalesce(GRP.NG_SERVER,-1), 0), -- parent_id
      0,
      0,
      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
      FROM DB.DBA.NEWS_GROUPS GRP
	  ;

  phpBB3..init_db_tree_rec (0, 1);
  for select forum_id id from phpBB3..phpbb_forums do
    {
      phpBB3..init_acl (id);
    }

  -- ACL for forums
  insert into phpBB3..phpbb_acl_users  (user_id, forum_id, auth_option_id, auth_setting, auth_role_id)
      select 1, forum_id, 0, 0, 17 from phpBB3..phpbb_forums;

  result('All forums done.');
  {
      declare tt_cr dynamic cursor for SELECT NM_KEY_ID, NM_GROUP FROM DB.DBA.NEWS_MULTI_MSG;
      declare tt_nm_key_id, tt_nm_group any;
      declare tt_bm any;
      declare tt_inx integer;

      tt_inx := 1;
      whenever not found goto tt_done;
      open tt_cr;
      while (1)
	{
	  fetch tt_cr into tt_nm_key_id, tt_nm_group;

	  if (exists (SELECT 1 FROM DB.DBA.NEWS_MSG MSG WHERE MSG.NM_ID = tt_nm_key_id))
	    {
	      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)
	      SELECT
		  MULTI.NM_KEY_ID as _topic_virt_id,
		  phpBB3..virt2forum_id (MULTI.NM_GROUP, 1) as _forum_id, --forum_id
		  phpBB3..get_field (MSG.NM_HEAD, 'Subject') as _topic_title,   --topic_title
		  0 as _topic_poster, --topic_poster
		  phpBB3..get_post_date(MSG.NM_HEAD) as _topic_time, --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
		  FROM DB.DBA.NEWS_MSG MSG
		  INNER JOIN DB.DBA.NEWS_MULTI_MSG MULTI on MSG.NM_ID = MULTI.NM_KEY_ID
		  WHERE MULTI.NM_KEY_ID = tt_nm_key_id AND MULTI.NM_GROUP = tt_nm_group and MSG.NM_REF is null
		  ;

	      insert replacing phpBB3..phpbb_topics_posted
		  (user_id, topic_id, topic_posted)
		  select 0, topic_id, 1 from
		  phpBB3..phpbb_topics, DB.DBA.NEWS_MULTI_MSG
		  where topic_virt_id = NM_KEY_ID and NM_GROUP = tt_nm_group;

	      if (mod(tt_inx,100) = 0)
		{
		  result(cast(tt_inx as varchar) || ' topics done.');

		};

		tt_inx := tt_inx + 1;
	    }
	}
	tt_done:;
       close tt_cr;
       result('All ' || cast(tt_inx as varchar) || ' topics done.');
    }

    {
      declare pp_cr dynamic cursor for SELECT NM_KEY_ID, NM_GROUP FROM DB.DBA.NEWS_MULTI_MSG MULTI;
      declare pp_nm_key_id, pp_nm_group any;
      declare pp_bm any;
      declare pp_inx integer;
      pp_inx := 1;
      whenever not found goto pp_done;

      open pp_cr;
      while (1)
	{
	  fetch pp_cr into pp_nm_key_id, pp_nm_group;

	  for  SELECT
	    MULTI.NM_KEY_ID as _post_virt_id,
		phpBB3..get_topic_id (MULTI.NM_GROUP, MSG.NM_HEAD, MSG.NM_ID) as _topic_id,
		phpBB3..virt2forum_id (MULTI.NM_GROUP, 1) as _forum_id,
		phpBB3..get_poster_id (phpBB3..get_field_raw (MSG.NM_HEAD, 'From')) as _poster_id,
		phpBB3..get_post_date (MSG.NM_HEAD) as _post_time,
		phpBB3..get_field (MSG.NM_HEAD, 'From') as _post_username,
		phpBB3..get_field (MSG.NM_HEAD, 'Subject') as _post_subject,
		phpBB3..decode_body(MSG.NM_BODY) as _post_text
	      FROM DB.DBA.NEWS_MSG MSG INNER JOIN DB.DBA.NEWS_MULTI_MSG MULTI on MSG.NM_ID = MULTI.NM_KEY_ID
	      WHERE MULTI.NM_KEY_ID = pp_nm_key_id AND MULTI.NM_GROUP = pp_nm_group
	      do
		{
		  if (_topic_id is null)
		    {
		      INSERT INTO phpBB3..phpbb_topics (topic_virt_id, forum_id, topic_title, topic_poster, topic_time)
			  values (_post_virt_id, _forum_id, _post_subject, _poster_id, _post_time);
		      _topic_id := identity_value ();
		      insert replacing phpBB3..phpbb_topics_posted (user_id, topic_id, topic_posted)
			  values (_poster_id, _topic_id, 1);
		    }
		  INSERT INTO phpBB3..phpbb_posts
		      (post_virt_id, topic_id, forum_id, poster_id, post_time, poster_ip, post_username, post_subject,
		      enable_bbcode, enable_smilies, enable_sig, post_edit_time, post_edit_count, post_text)
		      values
		      (_post_virt_id, _topic_id, _forum_id, _poster_id, _post_time, '127.0.0.1', _post_username, _post_subject,
		       0, 1, 0, 0, 0, _post_text);
		}

	  if (mod(pp_inx,100) = 0)
	    {
	      result (cast(pp_inx as varchar) || ' posts done.');
	    }
	  pp_inx := pp_inx + 1;
	}

      pp_done:;
      close pp_cr;
      result('All ' || cast(pp_inx as varchar) || ' posts done.');
    }
    {
      declare _last_id, _first_id, _topic_replies integer;
      declare tid,fid integer;
      declare cr cursor for SELECT topic_id as tid, forum_id as fid FROM phpBB3..phpbb_topics;
      whenever not found goto done;
      open cr;
      while (1)
	{
	  fetch cr into tid,fid;
	  _first_id := 0;
	  _last_id := 0;

	  _first_id := (SELECT TOP 1 post_id FROM phpBB3..phpbb_posts PS1  WHERE PS1.topic_id = tid ORDER BY post_time);
	  _last_id := (SELECT TOP 1 post_id FROM phpBB3..phpbb_posts PS2  WHERE PS2.topic_id = tid ORDER BY post_time DESC);
	  _topic_replies := (SELECT count(post_id) FROM phpBB3..phpbb_posts WHERE topic_id = tid);

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

	  if (isnull(_topic_replies) or _topic_replies < 2)
	    _topic_replies := 0;
	  else
	    _topic_replies := _topic_replies - 1;

	   UPDATE phpBB3..phpbb_topics
	       SET topic_first_post_id = _first_id,
		   topic_last_post_id = _last_id,
		   topic_replies = _topic_replies
	   WHERE CURRENT OF cr;

	   UPDATE phpBB3..phpbb_forums
	       SET forum_last_post_id = _last_id
	       WHERE forum_id = fid;
	}
      done:;
    }
  __atomic(1);
};

create procedure phpBB3..sync_stats ()
{
  declare stat, tmp, tmp1 any;

  UPDATE phpBB3..phpbb_forums
      SET forum_posts = (SELECT COUNT(post_id) from phpBB3..phpbb_posts pp where pp.forum_id = phpBB3..phpbb_forums.forum_id),
	  forum_topics = (SELECT COUNT(topic_id) from phpBB3..phpbb_topics pt where pt.forum_id = phpBB3..phpbb_forums.forum_id);

  SELECT COUNT(post_id) into stat FROM phpbb_posts WHERE post_approved = 1;
  UPDATE phpbb_config SET config_value = stat WHERE config_name = 'num_posts';

  SELECT COUNT(topic_id) into stat FROM phpbb_topics WHERE topic_approved = 1;
  UPDATE phpbb_config SET config_value = stat WHERE config_name = 'num_topics';

  SELECT COUNT(user_id) into stat FROM phpbb_users WHERE user_type IN (0,3);
  UPDATE phpbb_config SET config_value = stat WHERE config_name = 'num_users';

  SELECT COUNT(attach_id) into stat FROM phpbb_attachments WHERE is_orphan = 0;
  UPDATE phpbb_config SET config_value = stat WHERE config_name = 'num_files';

  SELECT SUM(filesize) into stat FROM phpbb_attachments WHERE is_orphan = 0;
  UPDATE phpbb_config SET config_value = '0' WHERE config_name = 'upload_dir_size';

  SELECT TOP 1  user_id, username, user_colour into stat, tmp, tmp1 FROM phpbb_users WHERE user_type IN (0, 3) ORDER BY user_id DESC;
  UPDATE phpbb_config SET config_value = stat WHERE config_name = 'newest_user_id';
  UPDATE phpbb_config SET config_value = tmp WHERE config_name = 'newest_username';
  UPDATE phpbb_config SET config_value = tmp1 WHERE config_name = 'newest_user_colour';
  for SELECT COUNT(p.post_id) AS num_posts, u.user_id as uid
    FROM phpbb_users u LEFT JOIN  phpbb_posts p ON (u.user_id = p.poster_id AND p.post_postcount = 1) GROUP BY u.user_id do
      {
	UPDATE phpbb_users SET user_posts = num_posts WHERE user_id = uid;
      }
}
;

phpBB3..init_db ();
phpBB3..sync_stats ();
checkpoint;
phpBB3..cache_clear ();

SET TRIGGERS ON;