use phpBB3;

ALTER TABLE phpBB3..phpbb_forums ADD virt_id integer default -1;
ALTER TABLE phpBB3..phpbb_posts ADD post_virt_id varchar;
ALTER TABLE phpBB3..phpbb_topics ADD topic_virt_id varchar;

CREATE INDEX phpbb_forums_v_id ON phpBB3..phpbb_forums (virt_id);
CREATE INDEX phpbb_posts_v_id  ON phpBB3..phpbb_posts (post_virt_id);
CREATE INDEX phpbb_topics_v_id ON phpBB3..phpbb_topics (topic_virt_id);
CREATE INDEX IX_phpbb_posts_1 ON phpbb_posts(post_time);

create procedure phpBB3..virt2forum_id (in id int, in kind int)
{
  return (select forum_id from phpBB3..phpbb_forums where virt_id = id and forum_type = kind);
}
;

create procedure phpBB3..get_post_date(in _head any) returns integer
{
  declare _dt datetime;
  declare _res integer;
  _dt := phpBB3..rfc2822_to_datetime(get_keyword_ucase('date',deserialize(_head)[0]));
  _res := datediff('second', phpBB3..get_start_date(),_dt);
  if (_res > 0)
    return _res;
  return 0;
};

create procedure phpBB3..get_start_date() returns datetime
{
  return dt_set_tz(dateadd('minute',timezone(now()),stringdate('1970-01-01 00:00:00')),0);
};

create procedure phpBB3..get_field (in ser_str any, in fld varchar)
{
  return sprintf('%V',phpBB3..decode_subj(coalesce (get_keyword_ucase (fld, deserialize(ser_str)[0]), '')));
}
;

create procedure phpBB3..get_field_raw (in ser_str any, in fld varchar)
{
  return coalesce (get_keyword_ucase (fld, deserialize(ser_str)[0]), '');
}
;

create procedure phpBB3..get_topic_id (in _group integer,in _head any,in _def integer := null) returns integer
{
  declare _ref,_ref_left varchar;
  declare tid integer;
  declare i, _pos, _cnt integer;

  _ref_left := get_keyword_ucase ('References', deserialize(_head)[0], '');
  tid := null;
  while (tid is null and _ref_left <> '')
    {
      i := 0;
      _pos := 0;
      _cnt := 0;
      while (i < length(_ref_left) and _pos = 0)
	{
	  if (_ref_left[i] = ascii('<'))
	    _cnt := _cnt + 1;
	  else if (_ref_left[i] = ascii('>'))
	    _cnt := _cnt - 1;
	  else if (_ref_left[i] = ascii(' ') and _cnt = 0)
	    _pos := i;
	  i := i + 1;
	}

      if (_pos)
	{
	  _ref := substring(_ref_left,1,_pos);
	  _ref_left := subseq(_ref_left,_pos + 1);
	}
      else
	{
	  _ref := _ref_left;
	  _ref_left := '';
	}
      tid := (select topic_id from phpBB3..phpbb_topics where topic_virt_id = _ref);
    }

  if (tid is null and _def is not null)
    tid := (select topic_id from phpBB3..phpbb_topics where topic_virt_id = _def);

  return tid;

};

----------------------------------------------------------
-- Get mail format "DAY, DD MON YYYY HH:MI:SS {+,-}HHMM"
--                 "DD MON YYYY HH:MI:SS {+,-}HHMM"
--		  and return "DD.MM.YYYY HH:MI:SS" GMT
------------------------------------------------------------
create procedure phpBB3..rfc2822_to_datetime(in _mdate varchar) returns datetime
{
  declare _rs datetime;

  declare exit handler for sqlstate '*'
   {
     _rs := phpBB3..get_start_date(); -- set system date
   };

  if (regexp_match('^[0-9]+ [a-zA-Z]{3} [0-9]{4} [0-9]+:[0-9]+:[0-9]+',_mdate))
    {
      _rs := http_string_date('xxx, ' || _mdate);
    }
  else
    {
      _rs := http_string_date(_mdate);
    };
  return _rs;
};

create procedure phpBB3..decode_subj (in str varchar) returns varchar
{
  declare match varchar;
  declare inx int;

  inx := 50;

  match := regexp_match ('=\\?[^\\?]+\\?[A-Z]\\?[^\\?]+\\?=', str);
  while (match is not null and inx > 0)
    {
      declare enc, ty, dat, tmp, cp, dec any;

      cp := match;
      tmp := regexp_match ('^=\\?[^\\?]+\\?[A-Z]\\?', match);

      match := substring (match, length (tmp)+1, length (match) - length (tmp) - 2);

      enc := regexp_match ('=\\?[^\\?]+\\?', tmp);

      tmp := replace (tmp, enc, '');

      enc := trim (enc, '?=');
      ty := trim (tmp, '?');

      if (ty = 'B')
    	{
    	  dec := decode_base64 (match);
    	}
          else if (ty = 'Q')
    	{
    	  dec := uudecode (match, 12);
    	}
          else
    	{
    	  dec := '';
    	}
          declare exit handler for sqlstate '2C000'
    	{
    	  return str;
    	};
      dec := charset_recode (dec, enc, 'UTF-8');

      str := replace (str, cp, dec);

      --dbg_printf ('encoded=[%s] enc=[%s] type=[%s] decoded=[%s]', match, enc, ty, dec);
      match := regexp_match ('=\\?[^\\?]+\\?[A-Z]\\?[^\\?]+\\?=', str);
      inx := inx - 1;
    }
  return str;
};

create procedure phpBB3..decode_body (in _body any) returns long varchar
{
  declare parsed_message any;
  declare _print_body varchar;
  declare enc varchar;
  if (_body is null)
    return '';
  if (isblob(_body))
    _body := blob_to_string(_body);

  parsed_message := mime_tree (_body);

  if (parsed_message[2] <> 0)
  {
    declare parts, part, attr, body any;

    body := parsed_message[1];
    parts := parsed_message[2];
    _print_body := '';

    for (declare x any, x := 0; x <  length (parts); x := x + 1)
    {
	    part := parts[x];
	    attr := part[0];
	    body := part[1];

      if (isstring(attr) and  get_keyword_ucase ('content-transfer-encoding', attr, '') <> 'base64')
	    {
	      if (body[1] - body[0] > 0)
	        _print_body := _print_body || substring (_body, body[0] + 1,  body[1] - body[0], get_keyword_ucase ('content-type', attr));
	    }
    }
  } else {

    _print_body := subseq (_body, parsed_message[1][0], parsed_message[1][1]);
    if (length (_print_body) > 3)
      _print_body := subseq (_print_body, 0, (length (_print_body) - 3));

    --_print_body := phpBB3..get_body_parts(_print_body,0)[0];

    --enc := get_keyword_ucase ('charset',parsed_message[0]);
    --if (enc is not null and enc <> '')
    --  _print_body := charset_recode (_print_body, enc, 'UTF-8');
  }
  --dbg_obj_print(substring(_print_body,1,1));
  --if (length(_print_body) > 3000)
    --return subseq(_print_body,1,3000);
  --else
    return _print_body;

};

create procedure phpBB3..get_body_parts (inout _data any, in get_uuparts integer)
{
  declare data, outp, _all any;
  declare line varchar;
  declare in_UU, get_body integer;

  data := string_output (http_strses_memory_size ());
  http (_data, data);
  http ('\n', data);
  _all := vector ();

  outp := string_output (http_strses_memory_size ());

  in_UU := 0;
  get_body := 1;
  while (1 = 1)
  {
    line := ses_read_line (data, 0);
    if (line is null or isstring (line) = 0)
	  {
	    if (_all = vector ())
	    {
	      _all := vector_concat (_all, vector (string_output_string (outp)));
	    }
      return _all;
	  }
--dbg_obj_print(line);
    if (in_UU = 0 and length (line) > 6 and subseq (line, 0, 6) = 'begin ')
    {
      in_UU := 1;
	    if (get_body)
	    {
	      get_body := 0;
	      _all := vector_concat (_all, vector (string_output_string (outp)));
	      http_output_flush (outp);
	    }
	    _all := vector_concat (_all, vector (subseq (line, 10)));
    }
    else if (in_UU = 1  and length (line) > 3 and subseq (line, 0, 3) = 'end')
    {
      in_UU := 0;
	    if (get_uuparts)
	    {
	      _all := vector_concat (_all, vector (string_output_string (outp)));
	       http_output_flush (outp);
	    }
    }
    else if ((get_uuparts and in_UU = 1) or get_body)
    {
      http (line, outp);
      http ('\n', outp);
    }
  }

  return _all;
}
;

create procedure phpBB3..unix_date (in d any)
{
  declare res any;
  res := datediff('second', phpBB3..get_start_date(), coalesce(d,now()));
  if (res > 0)
    return res;
  return 0;
}
;

create procedure phpBB3..update_user(in _user_id integer)
{
  INSERT REPLACING phpBB3..phpbb_users (
      user_id,
      username,
      username_clean,
      user_type,
      user_regdate,
      user_password,
      user_email,
      user_icq,
      user_website,
      user_occ,
      user_from,
      user_interests,
      user_sig,
      user_allow_viewemail,
      user_style,
      user_aim,
      user_yim,
      user_msnm,
      user_posts,
      user_allow_pm,
      user_notify_pm,
      user_allow_viewonline,
      user_rank,
      user_avatar,
      user_lang,
      user_timezone,
      user_dateformat,
      user_actkey,
      user_newpasswd,
      user_notify
      )

SELECT 	U_ID,
	U_NAME,
	U_NAME,
	case when U_ID < 3 then 3 else 0 end,
	phpBB3..unix_date (WAUI_JOIN_DATE),
	md5(coalesce(pwd_magic_calc (U_NAME, U_PASSWORD, 1),'')),
        U_E_MAIL,
	coalesce (WAUI_ICQ, ''),
	coalesce (WAUI_WEBPAGE, ''),
	coalesce (WAUI_BJOB, ''),
	WAUI_HCITY || case when isnull(WAUI_HCITY) or WAUI_HCITY = '' then '' else ', ' end || WAUI_HCOUNTRY ,
        '',
	coalesce (WAUI_MSIGNATURE, ''),
	1,
	1,
        coalesce (WAUI_AIM, ''),
	coalesce (WAUI_YAHOO, ''),
	coalesce (WAUI_MSN, ''),
	0,
	1,
        1,
	1,
        case when U_ID < 3 then 1 else 0 end,
	'',
	'',
	coalesce (WAUI_HTZONE, 0),
        'd M Y h:i a',
	'',
	'',
	0
    FROM DB.DBA.SYS_USERS
    LEFT JOIN DB.DBA.WA_USER_INFO ON U_ID = WAUI_U_ID
   WHERE U_DAV_ENABLE = 1 AND U_ACCOUNT_DISABLED = 0 AND U_ID=_user_id
  ;

--  INSERT REPLACING phpBB3..phpbb_groups (group_id,group_type,group_name,group_desc)
--  SELECT U_ID,1,'','Personal User'
--    FROM DB.DBA.SYS_USERS
--   WHERE U_ID=U_GROUP AND U_DAV_ENABLE = 1 AND U_ACCOUNT_DISABLED = 0 AND U_ID>2 AND U_ID=_user_id
--  ;


  INSERT REPLACING phpBB3..phpbb_user_group (group_id,user_id,user_pending)
  SELECT case when U_ID < 3 then 5 else 2 end, U_ID, 0
    FROM DB.DBA.SYS_USERS
   WHERE U_DAV_ENABLE = 1 AND U_ACCOUNT_DISABLED = 0 AND U_ID=_user_id
  ;

};

create procedure phpBB3..normalize_text(in _str any){
  _str := blob_to_string(_str);
  _str := replace(_str,'&lt;','<');
  _str := replace(_str,'&gt;','>');
  _str := replace(_str,'&amp;','&');
  _str := replace(_str,'&quot;','"');

  _str:=REGEXP_REPLACE(_str, '([\x5B]/{0,1}b:[a-zA-Z_0-9]{1,10}[\x5D])', '*',1,null);
  _str:=REGEXP_REPLACE(_str, '([\x5B]/{0,1}i:[a-zA-Z_0-9]{1,10}[\x5D])', '/',1,null);
  _str:=REGEXP_REPLACE(_str, '([\x5B]/{0,1}u:[a-zA-Z_0-9]{1,10}[\x5D])', '_',1,null);

--  _str := replace(_str,'[b]','*');
--  _str := replace(_str,'[/b]','*');
--  _str := replace(_str,'[i]','/');
--  _str := replace(_str,'[/i]','/');
--  _str := replace(_str,'[u]','_');
--  _str := replace(_str,'[/u]','_');

  declare match any;
  declare _pattern varchar;
  _pattern := '\\[url=([^\\]]*)\\](.*)[/url]';
  match := regexp_parse(_pattern,_str,0);
  while(match)
  {
     _str := substring(_str,1,match[2] - 5) || ' ' ||
             substring(_str,match[4] + 1,match[5] - match[4] - 4) || ' ' ||
             '(' ||
             substring(_str,match[2] + 1,match[3] - match[2]) ||
             ')' ||
             substring(_str,match[5] + 3,length(_str));
     match := regexp_parse(_pattern,_str,0);
  }
  _str := replace(_str,'[url]','');
  _str := replace(_str,'[/url]','');

  return _str;
};

create procedure phpBB3..get_bbcode_uid (in _str any)
{ declare _bbcode_uid varchar;
  _bbcode_uid:=null;

  if(length(_str)=0) return _bbcode_uid;

  _bbcode_uid:=regexp_match ('([\x5B]/{0,1}[a-zA-Z]+:[a-zA-Z_0-9]{1,10}[\x5D])',_str);

  if(_bbcode_uid is not null)
  { declare sep_pos integer;
    sep_pos:=position (':',_bbcode_uid);

    _bbcode_uid:=subseq(_bbcode_uid,sep_pos,length(_bbcode_uid)-1);
  }

  return _bbcode_uid;
}
;
create procedure phpBB3..get_poster_id(in _from integer)
{
  declare _poster_id integer;
  declare _email varchar;
  if (strchr(_from,'<') and strchr(_from,'>'))
    _email := substring(_from,strchr(_from,'<') + 2,strrchr(_from,'>') - strchr(_from,'<') - 1);
  else
    _email := _from;

  _poster_id := (SELECT USER_ID FROM phpBB3..phpbb_users WHERE user_email = _email);

  if (not(_poster_id) or isnull(_poster_id))
    _poster_id := 1;

  return _poster_id;
};


create procedure phpBB3..cache_clear ()
{
  declare path varchar;
  declare filename varchar;
  declare files any;
  declare len integer;
  declare ctr integer;
  declare err integer;

  path := http_root () || '/vad/vsp/phpBB3/cache/';
  files := file_dirlist (path, 1, err);
  len := length (files);
  ctr := 0;
  while (ctr < len)
    {
      filename := path || aref (files, ctr);
      file_delete (filename, 1);
      ctr := ctr + 1;
    }
}
;