-- clean:
create or alter procedure split_into_words as begin end;
create or alter procedure prod_by_patterns as begin end;
recreate table prod(id int);
recreate table prod_w(id int);
recreate table pwords(id int);
commit;

set term ^;
execute block as
begin
  begin execute statement 'drop sequence g'; when any do begin end  end
  begin execute statement 'drop domain dm_name_ci'; when any do begin end  end
  begin execute statement 'drop collation name_ci'; when any do begin end  end
end
^
set term ;^
commit;
--------------------------------------------------------------------------------
-- creating:
create sequence g;
create collation name_ci for utf8 from unicode case insensitive;
create domain dm_name_ci as varchar(80) character set utf8 collate name_ci;
commit;

recreate table prod(id int primary key, name dm_name_ci);
recreate table prod_w(prod_id int, word_id int, constraint prod_w_pk primary key (prod_id, word_id) );
recreate table pwords(id int primary key, name dm_name_ci unique);
commit;

--------------------------------------------------------------------------------
-- adding test data:
insert into prod values( gen_id(g,1), 'сливочное масло "Вологодское"' );
insert into prod values( gen_id(g,1), 'сливочное масло "Крестьянское"' );
insert into prod values( gen_id(g,1), 'масло сливочное "Президент"' );
insert into prod values( gen_id(g,1), 'масло сливочное "Парламент"');
insert into prod values( gen_id(g,1), 'заменитель сливочного масла "Европейский"');
insert into prod values( gen_id(g,1), 'сливочный масложир "Сибирский"');
insert into prod values( gen_id(g,1), 'сливово-масляное сливочное масло "СливоМасло"' );
insert into prod values( gen_id(g,1), 'маслянисто-сливочное масло "Сливовое"' );
insert into prod values( gen_id(g,1), 'сливово-масляное масло, с масляно-сливочной сливой' );
insert into prod values( gen_id(g,1), 'масляно-сливовая слива, со сливово-масляным маслом' );
insert into prod values( gen_id(g,1), 'масляно-сливовое масло, со сливово-масляным маслицем' );
insert into prod values( gen_id(g,1), 'масло растительное "Олейна"' );
insert into prod values( gen_id(g,1), 'сливки "Ополье"' );
insert into prod values( gen_id(g,1), 'масло масляное "Маслоцарство"');
insert into prod values( gen_id(g,1), 'сливки сливочные "Сливочное Королевство"');
insert into prod values( gen_id(g,1), 'масло шоколадное "Красный Октябрь"' );
insert into prod values( gen_id(g,1), 'сливки взбитые "Рот-Фронт"' );

insert into prod values( gen_id(g,1), '   МАСЛО    ' );
insert into prod values( gen_id(g,1), '   СЛИВКИ   ' );
commit;

set term ^;

--------------------------------------------------------------------------------
-- auxiliary proc for split strings into words and fill table pwords:
create or alter procedure split_into_words (
    a_text dm_name_ci,
    a_dels varchar(50) default ',.<>/?;:''"[]{}`~!@#$%^&*()-_=+\|/',
    a_special char(1) default ' ')
returns (
    word dm_name_ci,
    i int
)
AS
begin
for
    with recursive
    j as( -- loop #1: transform list of delimeters to rows
        select s, 1 i, substring(s from 1 for 1) del
        from(
          select replace(:a_dels,:a_special,'') s
          from rdb$database
        )
        
        UNION ALL
        
        select s, i+1, substring(s from i+1 for 1)
        from j
        where substring(s from i+1 for 1)<>''
    )

    ,d as(
        select :a_text s, :a_special sp from rdb$database
    )
    ,e as( -- loop #2: perform replacing each delimeter to `space`
        select d.s, replace(d.s, j.del, :a_special) s1, j.i, j.del
        from d join j on j.i=1

        UNION ALL

        select e.s, replace(e.s1, j.del, :a_special) s1, j.i, j.del
        from e
        join j on j.i = e.i + 1
    )
    ,f as(
        select s1 from e order by i desc rows 1
    )
    
    ,r as ( -- loop #3: perform split text into single words
        select iif(t.k>0, substring(t.s from t.k+1 ), t.s) s,
             iif(t.k>0,position( del, substring(t.s from t.k+1 )),-1) k,
             t.i,
             t.del,
             iif(t.k>0,left(t.s, t.k-1),t.s) word
        from(
          select f.s1 s, d.sp del, position(d.sp, s1) k, 0 i from f cross join d
        )t

        UNION ALL

        select iif(r.k>0, substring(r.s from r.k+1 ), r.s) s,
             iif(r.k>0,position(r.del, substring(r.s from r.k+1 )),-1) k,
             r.i+1,
             r.del,
             iif(r.k>0,left(r.s, r.k-1),r.s) word
        from r
        where r.k>=0
    )
    select word, i from r where word>''
    into
        word, i
do
    suspend;
end

^
--------------------------------------------------------------------------------
-- fill tables pwords and prod_w:
execute block as
    declare prod_id int;
    declare word dm_name_ci;
    declare word_id int = 0;
begin
     insert into pwords(id, name)
     select gen_id(g,1), word
     from (
        select distinct w.word
        from prod p
        left join split_into_words(p.name) w on 1=1
     );

    insert into prod_w(prod_id, word_id)
    select p.id, x.id
    from prod p
    left join split_into_words(p.name) w on 1=1
    left join pwords x on w.word = x.name
    group by 1,2;

end

^
--------------------------------------------------------------------------------
-- main SP:
create or alter procedure prod_by_patterns( a_pattern dm_name_ci )
returns( id int, name dm_name_ci )
as
begin
    for
        with
        inp_arg as(
          select p.word as pattern, p.i
          from split_into_words( :a_pattern ) p
          --from split_into_words('масл слив') p
          --from split_into_words('масл слив масл') p
          --from split_into_words('масл масл') p
          --from split_into_words('масл масл масл') p
          --from split_into_words('слив слив слив') p
          --from split_into_words('слив слив масл') p
          --from split_into_words('слив слив масл масл') p
        )
        ,inp_pat  as(
          select
            i.pattern, i.i
            ,(select count(*) from inp_arg) cnt_all
            ,(select count(distinct pattern) from inp_arg) cnt_unq
            ,(select count(*) from inp_arg x where x.pattern=i.pattern ) cnt_occ
          from inp_arg i
        )
        --select * from inp_pat i
        ,prod_pat as(
            select --pp.prod_id, i.pattern, i.i, i.cnt_all, i.cnt_occ, pw.id pw_id, pw.name pw_word, ps.name
            pp.prod_id
            ,i.pattern
            ,i.cnt_all --, pw.id
            ,count(distinct pw.id) pw_unq
            ,max(i.cnt_unq) cnt_unq
            ,max(i.cnt_occ) cnt_occ
            from inp_pat i
            left join pwords pw on pw.name starting with i.pattern
            left join prod_w pp on pw.id = pp.word_id
            group by pp.prod_id, i.pattern, i.cnt_all --, pw.id
            having count(distinct pw.id) >= max(i.cnt_occ)
        )
        --select * from  prod_pat
        ,prod_fin as (
            select pp.prod_id
            from prod_pat pp
            group by pp.prod_id
            having count(*) = max(cnt_unq)
        )
        select pf.prod_id, ps.name
        from prod_fin pf
        join prod ps on pf.prod_id = ps.id
        into id, name
    do
        suspend;
end

^
set term ;^
commit;

SQL> set width name 70;
SQL> select * from prod_by_patterns('масл');

          ID NAME
============ ======================================================================
           1 сливочное масло "Вологодское"
           2 сливочное масло "Крестьянское"
           3 масло сливочное "Президент"
           4 масло сливочное "Парламент"
           5 заменитель сливочного масла "Европейс
           6 сливочный масложир "Сибирский"
           7 сливово-масляное сливочное масло "Сли▒
           8 маслянисто-сливочное масло "Сливовое"
           9 сливово-масляное масло, с масляно-слив
          10 масляно-сливовая слива, со сливово-мас
          11 масло растительное "Олейна"
          13 масло масляное "Маслоцарство"
          15 масло шоколадное "Красный Октябрь"
          17    МАСЛО

SQL> select * from prod_by_patterns('масл масл');

          ID NAME
============ ======================================================================
           7 сливово-масляное сливочное масло "Сли▒
           8 маслянисто-сливочное масло "Сливовое"
           9 сливово-масляное масло, с масляно-слив
          10 масляно-сливовая слива, со сливово-мас
          13 масло масляное "Маслоцарство"

SQL> select * from prod_by_patterns('масл слив слив');

          ID NAME
============ ======================================================================
           7 сливово-масляное сливочное масло "Сли▒
           8 маслянисто-сливочное масло "Сливовое"
           9 сливово-масляное масло, с масляно-слив
          10 масляно-сливовая слива, со сливово-мас

SQL> select * from prod_by_patterns('масл масл масл');

          ID NAME
============ ======================================================================
           9 сливово-масляное масло, с масляно-слив
          10 масляно-сливовая слива, со сливово-мас
          13 масло масляное "Маслоцарство"

SQL> select * from prod_by_patterns('слив слив слив');

          ID NAME
============ ======================================================================
           7 сливово-масляное сливочное масло "Сли▒
           9 сливово-масляное масло, с масляно-слив
          10 масляно-сливовая слива, со сливово-мас
          14 сливки сливочные "Сливочное Королевст

SQL>  select * from prod_by_patterns('слив слив масл масл');

          ID NAME
============ ======================================================================
           7 сливово-масляное сливочное масло "Сли▒
           8 маслянисто-сливочное масло "Сливовое"
           9 сливово-масляное масло, с масляно-слив
          10 масляно-сливовая слива, со сливово-мас

SQL> select * from prod_by_patterns('МАСЛ МАСЛ МАСЛ МАСЛ');

          ID NAME
============ ======================================================================
          11 масляно-сливовое масло, со сливово-мас