U
    gn=                    @   s   d dl Z d dlZd dlmZ d dlT d dlmZ d dlZd dl	Z	d dl
Z
d dlZd dlZd dlZd dlmZ e ZG dd dejjjZdS )    N)
namedtuple)*)SQLAlchemyDataDriver)BeautifulSoupc                   @   s  e Zd ZdZdZddddddd	d
dddddddddddddddgZdZdddgZee	ddd Z
ed!d" Zed#d$ Zed%d& Zed'd( Zed)d* Zed+d, Zeded/d0Zed1d2 Zed3d4 Zeejjd5d6d7Zedfd9d:Zeedd;d<d= Zed>d? Zeeddd@dAdBdCdD ZedEdF ZedGdHdIdJdKZedgdLdMZ edNgd-dOdPd8gd-d-fdQdRZ!ed-d-dNgd-dOdPd8gfdSdTdUdVZ"edWdXdYdZd[Z#edWd\d]d^Z$edhd_d`Z%edidadbZ&eedcdd Z'd-S )jCmfFullSearchuC   
    Сервис полнотекстового поиска.
    russiannameZtext_rendertextZ
text_drafttags	parent_idtree_parent_id
project_idcmf_created_atcmf_modified_atcodeZcmf_deletedZcmf_archivedZloginZemailZphoneZphone_internalZphone_mobileZphone_2Zphone_assistant
ip_addressZemail_2ZbirthdayTfulltext_searchrun_force_reindexindex_statsr	   c                 C   sR   | rNt | dd} t| dkrNtdt|  d |  d d jdd} | S )	Nhtml.parser
i  z)CmfFullSearch.clean_text: trunc text len z to 1mbi  ignoreerrors)r   get_textlengdebugencodedecoder    r!   ./cmf/models/cmf_full_search.py
clean_text0   s    zCmfFullSearch.clean_textc                 C   s   t |ddjdd S )Nr   r   r   r   )r   r   r   r    )clsZtext_with_htmlr!   r!   r"   
strip_html@   s    zCmfFullSearch.strip_htmlc                    s@   dd t k rgS  fddtdt D }|S )Ni d   c                    s    g | ]}||    qS r!   r!   ).0iZoverlapr	   Ztext_len_maxr!   r"   
<listcomp>T   s     z-CmfFullSearch._strip_size.<locals>.<listcomp>r   )r   range)r$   r	   resr!   r)   r"   _strip_sizeD   s    "zCmfFullSearch._strip_sizec                 C   s   d dd td|D S )N c                 S   s   g | ]}t |d k r|qS )2   )r   r'   wr!   r!   r"   r*   Y   s      z+CmfFullSearch._strip_50.<locals>.<listcomp>z\sjoinresplitr$   r	   r!   r!   r"   	_strip_50W   s    zCmfFullSearch._strip_50c                 C   s   d dd td|D S )Nr.   c                 S   s   g | ]}|qS r!   r!   r0   r!   r!   r"   r*   `   s     z1CmfFullSearch._strip_not_word.<locals>.<listcomp>z[ \s<>|&\/%@!`+=;.]r2   r6   r!   r!   r"   _strip_not_word[   s    zCmfFullSearch._strip_not_wordc                 C   s  t d|  |stddd l}| jj}|| }|j}| }|	|j
jg|j
j|k|j
jdk }|| }|r|d }	| |j
j|	kjd|j d}
||
 n@t d|  |  }	| j|	|d|j dd}|| d S )Nzcmf_full_search: mark_dirty empty obj_idr   Tis_dirtydirty_atz#cmf_full_search: mark_dirty insert )idobj_idr;   r<   part_no)r   r   
ValueError
sqlalchemydpdata_driverdp_model_cls	__table__Sessionselectcr=   wherer>   r?   with_for_updateexecutefirstupdatevaluesfuncnowgen_idinsert)r$   r>   saddsa_modeltablesget_stmtget_resid_update_stmtinsert_stmtr!   r!   r"   
mark_dirtyb   sH    




zCmfFullSearch.mark_dirtyc                 C   s   |st ddd l}| jj}|| }|j}| }||jj	g
|jj|k
|jjdk }|| }|r|d }	| 
|jj	|	kjd|j d}
||
 | j  d S )Nr9   r   Fr:   )r@   rA   rB   rC   rD   rE   rF   rG   rH   r=   rI   r>   r?   rJ   rK   rL   rM   rN   rO   rP   commit)r$   r>   rS   rT   rU   rV   rW   rX   rY   rZ   r[   r!   r!   r"   
mark_clean   s2    




zCmfFullSearch.mark_cleanNFc           &   #   C   s  |}|  |}| |}|r@| |}| |}|  |d }n
d g}d }|rh|}|  |}| |}nd }d }|r|}|  |} | | } nd }d } |r| |}!|!d d }!|  |!}"nd }!d }"|r| |d d }|  |}#nd }#| jd|||||||||	||||||
||||d ||||||| |!|"||#|d  t|dkrt|dd  dD ]^\}$}%|  |%}| j|$|||||||||	||||||
||||%||d d d d d d d ||#|d  qX| j|t|d | j  d S )Nr   i ) r?   r>   	obj_modelobj_parent_idobj_tree_parent_idobj_project_idobj_created_atobj_modified_atobj_codeobj_deletedobj_archivedobj_owner_nameobj_author_nameobj_modified_by_nameobj_responsible_namesobj_hrefobj_logic_type_codeobj_activity_codeobj_status_typeobj_texttext_for_vecobj_namename_for_vecobj_tagstags_for_vecobj_result_textresult_text_for_vecobj_commentscomments_for_vecobj_addon_fieldscf_text_for_vecobj_user_rating   )delete_from_partno)	r8   r7   r-   _text_search_sql_insertr   	enumerate_text_search_sql_delete_partnorB   r^   )&r$   
model_namer>   rf   r   r	   rw   Zcomments_textrh   rg   rm   ra   rn   ro   ru   ri   rj   rk   rl   rb   rc   rd   re   rp   r{   r}   rs   rt   rq   Zobj_text_listrr   rv   rx   Zobj_comments_textZcomments_text_for_vecr|   r?   Zobj_text_partr!   r!   r"   index_object   s    







                

                
zCmfFullSearch.index_objectc           	      C   sh   |st ddd l}| jj}|| }|j}| }|||j	j
|k|j	j|k}|| d S )Nr9   r   )r@   rA   rB   rC   rD   rE   rF   deleterI   rH   r>   r?   rK   )	r$   r>   r   rS   rT   rU   rV   rW   Zdel_stmtr!   r!   r"   r     s    



z,CmfFullSearch._text_search_sql_delete_partnoc!           +   $   C   sB  t d|  |stddd l}!| jj}"|"| }#|#j}$|" }%|!	|$j
jg|$j
j|k|$j
j|k }&t|%|&}'|'rvt|'dkrtd| d| ddd	 |'d d }(|$ |$j
j|(kj|d
|||||||||||||!jj| j||!jj| j||!jj| j||!jj| j||!jj| j||!jj| j||	|
|||||||||| d })|%|) nt d|  |  }(|$ j|(d
|||||||||||||||!jj| j||!jj| j||!jj| j||!jj| j||!jj| j||!jj| j||	|
|||||||||| d"}*|%|* |(S )Nz)cmf_full_search: _text_search_sql_insert r9   r   r~   u   Для объекта z	(part_no=u   ) задублировались записи в поиске. Обратитесь в техническую поддержку!TabortF) r>   r;   r`   rs   rq   ru   rw   ry   r{   ra   rb   rc   rd   re   name_tsvectortext_tsvectortags_tsvectorresult_text_tsvectorcomments_tsvectoraddon_fields_tsvectorrf   rg   rh   ri   rj   rk   rl   rm   rn   ro   rp   r}   z0cmf_full_search: _text_search_sql_insert insert )"r=   r;   r?   r>   r`   rs   rq   ru   rw   ry   r{   ra   rb   rc   rd   re   r   r   r   r   r   r   rf   rg   rh   ri   rj   rk   rl   rm   rn   ro   rp   r}   )r   r   r@   rA   rB   rC   rD   rE   rF   rG   rH   r=   rI   r>   r?   rJ   listrK   r   	cmf_alertrM   rN   sqlrO   Zto_tsvector_fts_configrQ   rR   )+r$   r?   r>   r`   ra   rb   rc   rd   re   rf   rg   rh   ri   rj   rk   rl   rm   rn   ro   rp   rq   rr   rs   rt   ru   rv   rw   rx   ry   rz   r{   r|   r}   rS   rT   rU   rV   rW   rX   rY   rZ   r[   r\   r!   r!   r"   r   .  s    



&'
z%CmfFullSearch._text_search_sql_insert)objc                 C   s>   |j s
d S | jD ](}|dkrq||jkr|| jr dS qd S )Nr   T)full_searchrequired_fieldsfieldsZ
is_changed)r$   r   
field_namer!   r!   r"   is_obj_need_reindex  s    
z!CmfFullSearch.is_obj_need_reindexr&   c              
   C   s   t jj D ]}|jsq|j}|r,||kr,qd}t|}|j}|rJdg}t		 }	|j
|||| gdgddd}
|
svq|
D ]"}|rtj|jj qz|  qz|t|
7 }| j  td| d| dt		 |	 d	d
 qJqd S )Nr   r=   r   T)r   sliceorder_byinclude_archivedinclude_deletedzCmfFullSearch.reindex_models: :, z0.3z sec)cmfmodels	CmfEntityiter_subclassesr   
class_namecmfutilget_model_by_namefull_search_fieldstimer   r   r]   r=   valuefull_search_indexr   rB   r^   r   r   )r$   Zmodels_listZcommit_everyZlazyZ	model_clsr   offsetmodelZ
field_listtsobj_listr   r!   r!   r"   reindex_models  s8    



zCmfFullSearch.reindex_models)	only_oncec                  C   sf   t d tj  tj} | jj}|| }|j	}|
 }| |jjdkjdd}|| d S )NzRun run_force_reindexr   Tr;   )r   r   r   CmfAccessListcheck_admin_moder   rB   rC   rD   rE   rF   rM   rI   rH   r?   rN   rK   )r$   rT   rU   rV   rW   r[   r!   r!   r"   r     s    


zCmfFullSearch.run_force_reindexc                 C   s(   t j  |  }| jdd}||dS )NTr   )totaldirty_count)r   r   r   count)r$   r   r   r!   r!   r"   r     s    
zCmfFullSearch.index_statsz	@minutely   )r   Z
system_jobZschedulepriorityc            	      C   s  t d tj r"t d d S td t } d}tjj	dgdddgddd	gd
ddt
j
 t
jdd gddd gggddgd}|sqi }|D ]0}t|j}||krg ||< || |j q| D ]\}}t d| dt|  t| |D ]b}|j|jdd|gd	d}|d7 }|sFt d|  tj| qt d|j  |  qqt |  dkrt d| d qt d| d td q8t dt |    d S )NzStart cron_index_dirtyuJ   Не индексируем поскольку запущен импорт   r   r>   r?   =r;   TORr<   <   )Zminutes   )r   filterr   zcron_index_dirty process r.   r=   )r   r   r   r~   u.   Объект не найдет в бд obj_id=zobj_id=   ua   cron_index_dirty превышен лимит времени 20 секунд обработано u    объектовu&   cron_index_dirty обработано u    объектов, sleep 1zEnd cron_index_dirty at )r   r   r   Zenable_import_modeZimport_is_runningr   sleepr   r   slistdatetimerP   Z	timedeltaZget_model_by_idr>   appenditemsr   printgetr   r_   r=   r   )	stnZobj_id_listZobj_ids_by_modelr>   r   Zid_listrZ   r   r!   r!   r"   cron_index_dirty  sV    





zCmfFullSearch.cron_index_dirtyc                 C   sN   |S ]:}|sq|d dkr4t|dks|d dkr4q|| qd|}|S )u   
        Подчистка оригинального квери, который ввел пользователь:
        - удаление стоп-слов
        r.   r   -r~   )r5   r   r   r3   )r$   search_queryZclean_search_query_listr1   Zclean_search_queryr!   r!   r"   _clean_search_query,  s     
z!CmfFullSearch._clean_search_querystrztuple[str, set[str]])r   returnc                 C   s   t  }| d}t|dkr$| |fS d} |dd D ]Z}|dkrBq4td|d}|d dkrj||d  t|dkr4|  |d  |d  } q4|  } | |fS )	zExtrats tags from the given search_query and returns its reminder and a set of extracted tags

        Args:
            search_query (str)

        Returns:
            tuple[str, list[str]]: search_query reminder and a set of extractd tags
        #r~    Nz(\W)r      r   )setr5   r   r4   addstrip)r   r
   Zsharp_splittedtokenZ
sub_tokensr!   r!   r"   _extract_tags@  s    

zCmfFullSearch._extract_tagsc	           J      K   s  t |dkrtddd |d kr$d}d|	kr4d|	d< d|	krDd|	d< |sPd	d
g}tjjdd}
|
dkrttd|
  dt_|}t }|		d}|r|
| | |\}}t||}||	d< |}| }|}t|}|dkrd}t|}d	|d	< |dr|d d }g }g }g }g }g }g }g }g }g }g }g }g }g }g }g } g }!g }"g }#g }$g }%g }&g }'g }(g })g }*g }+|dkrdd tjj D },n8t|tjkrd}d}dd tjj D },n|g},|		d}-| |-|	d< |		dd}.| j|ddd}/| j|dd}0| j|dd}1| j|ddd}2|1t_tdd|0}3|3t_tdd|0}4|dksl|d kr<|dkr|.s<| j|,fttjj d!d"gdd#|d$|	}%| j|,fd!d"gdd#|d%|	}&nt| j!d&|,|0fd!d"g|d'|/|d(|	}|.s<| j!d|,|1f|d!d"gd)|d*|	}| j!d+|,|0f|d!d"gd)|d*|	}|dksP|d!kr|dkr|.s| jd!gfttjj |d,|d-|	}'| jd!gf|d,|d.|	}(nN|dkr| j!d&d!g|0f|d'|/|d/|	}| j!d&d!g|1f|d0|/d1|	}|.r|d&kr| j!|d!g|0f|d2d3|	}| j!|d!g|3fd4|d5|	}| j!|d!g|4fd6d7i|	}| j!|d!g|1f|d)d3|	}| j!d+d!g|0f|d)d3|	}!t"t |t |t |t |t |t |t |!fd8k r| j!|d!g|2f|d)d3|	d	d9 }#|dks|d"kr|dkr^|.s| jd"gfttjj |d,|d-|	})| jd"gf|d,|d.|	}*nN|dkr| j!d&d"g|0f|d'|/|d/|	}| j!d&d"g|1f|d0|/d1|	}|.r|d&kr| j!|d"g|0f|d2d3|	}| j!|d"g|3fd4|d5|	}| j!|d"g|4fd6d7i|	}| j!|d"g|1f|d)d3|	} | j!d+d"g|0f|d)d3|	}"t"t |t |t |t |t |t | t |"fd8k r| j!|d!g|2f|d)d3|	d	d9 }$g }5d:|# krDt$d;d<}6tjj%d=d>d?d@dAgdBd>dC|# gd=dC|# ggdDD ]F}7|7j&r|7j&d d nd}8|5'|6|7j(|7j)|7j*|7j+|8dEd
d	dF qg }9t }:dGdH };d}<t,dI |s |s |s |%s |&s |s |s |s |s |s |s |s |s |s |s |s | s |!s |"s |#s |$s |'s |)s |(s |*s |5	r|<rF|;|5|9|: |;||9|: |;||9|: |;||9|: |;||9|: d}<|;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;||9|: |;| |9|: |;|!|9|: |;|"|9|: |;|#|9|: |;|$|9|: |;||9|: |;|'|9|: |;|(|9|: |;|)|9|: |;|*|9|: |;|%|9|: |;|&|9|: qdqd|9|d	 |dJ  }9|sni }=|
rJ|		dd}>|		dd}?i }@|9D ]}A|@-|Aj)g '|Aj( 	q|@. D ]>}t|j|dKdL|@| g|>|?dM}B|BD ]}C|C|=|Cj/< 
q2
qntdN g }D|9D ]}A|Aj(|Aj0|Aj*|Aj1 dO|Aj2 d:|Aj3dPd:|Aj4dQdRtj 	|Aj2|Aj3|Aj4dS}E|=	|Aj(}C|C
rZz`|
s|C5  n||C
st6|D ].}F|F7dTd	 }Ft8|=|Aj( |Fd }G|G|E|F< 
q|D'|E W n t6k
r0   Y nX 
qZt,dU |sjt9tjj:|t;|Dd d8 dVdW |DS i }Hg }I|9D ]b}A|I'|Aj( |Aj(|Aj0|Aj*|Aj1 dO|Aj2 d:|Aj3dPd:|Aj4dQdRtj 	|Aj2|Aj3|Aj4dS|H|Aj(< qz|Ht_<t,dU |IS )XNi   uY   Превышена максимально допустимая длина запроса!Tr   r   archivedFdeletedr   r&   r      u   Идет процесс индексации, могут быть доступны не все результаты поиска. Осталось объектов: tag_nameANYr	   ZModelc                 S   s   g | ]}|j r|jqS r!   r   r   r'   mr!   r!   r"   r*     s      z1CmfFullSearch.fulltext_search.<locals>.<listcomp>commentsc                 S   s   g | ]}|j r|jqS r!   r   r   r!   r!   r"   r*     s      r   tree_parent_filtertitles_only)synonyms
stop_wordsr   )r   
first_wordz([&] )([^!])z<1> \2z<2> \2)CmfTaskCmfDocumentr   r   ZFS)related_usermodel_name_not_inr   labelr   )r   r   r   r   r   N)r   r   r   text_stop_words
like_queryS)r   r   r   r   addon_fieldsZFA)r   r   r   r   )r   r   r   )r   r   r   r   ZNs)r   r   r   A)r   r   ZH1)r   r   r   ZH2r      r.   SearchResultTuplez<obj_id,obj_model,obj_code,title,headline,label,rank,age_daysr>   rf   rs   r`   rq   r   r   )r   r   ZCODE)r>   r`   rf   titleheadliner   rankage_daysc                 S   s<   | r8|  d}|d |kr || ||d  dS q dS )Nr   TF)popr   r   )r   resultskip_idsrr!   r!   r"   add_if_exists  s    

z4CmfFullSearch.fulltext_search.<locals>.add_if_existszfulltext_search Start mixingr~   r=   IN)r   r   r   r   uY   DEV: FATAL. Укажите в запросе поиска список полей fields=z ||| z.6fz.0fz words=)r=   r   r   r   r   r   r   .zfulltext_search END)r   obj_dict)kwargs)=r   r   r   r   r   Zcmf_noter   FSTr   r   r   r   r   unionlowerr   Zninjaendswithr   r   r   r   Z
CmfComment_get_all_branchesprepare_search_queryFSTQr4   subZFSTQHfilter_oncer   current_userr   search_oncesumr   r   r   rq   r   r>   r`   rf   rs   r   
setdefaultkeysr=   r   r   r   r   r   Z_acl_check_readZCmfPermissionErrorr5   getattrZschedule_deferred_job_do_calc_statisticsZ
dumps_dictfulltext_search_headlines)Jr$   r   r   r   Zonly_idsr   r   Zno_analitycsZcheck_accessr   r   Zorig_field_namer
   r   Zextracted_tagsZorig_search_queryZlike_search_queryZfullsearch_sliceZother_res_name_foundZother_res_text_foundZother_res_cf_foundZtask_res_name_foundZdocument_res_name_foundZtask_res_name_syn_foundZdocument_res_name_syn_foundZtask_res_all_foundZdocument_res_all_foundZtask_res_strong_position_h1Zdocument_res_strong_position_h1Ztask_res_strong_position_h2Zdocument_res_strong_position_h2Ztask_res_syn_foundZdocument_res_syn_foundZtask_res_cf_foundZdocument_res_cf_foundZtask_res_first_wordZdocument_res_first_wordZother_filter_rel_foundZother_filter_foundZtask_filter_rel_foundZtask_filter_foundZdocument_filter_rel_foundZdocument_filter_foundZaddon_res_foundZother_model_namesr   r   r   Ztsquery_without_synZtsquery_with_synZtsquery_first_wordZtsquery_strong_position_h1Ztsquery_strong_position_h2Zres_code_foundr   Zany_res_code_foundr   r   r   r   Zdo_topZobjectsZis_archivedZ
is_deletedZids_by_modelr   r   r   r,   r   Zfieldattrr  Z	result_idr!   r!   r"   r   b  s   






		  




     	
 	



    	
 




		


,	



,
zCmfFullSearch.fulltext_searchZnull000r   r   c
           &      K   s,  |
 dd }|
 d}|
 dd }|
 dd }|
 dd }|
 d}|
 d}|
 dd }|
 d	}|
 d
}|
 dd }|
 d|}|p|}|
 d}|rdd|nd}|
 dpg }| |\}}|dkrd }|	dkrd }	d }|	rtd|	s
td|	r|	 }|dkr.td| dd d| }d| }|dkrPd}| d}|d }|d |d  } d}!|rt|}d| d}!t	j
jj d  d}"|d!krd"}"d#jf ||||!||d$}#t	j
jj |#||| t|t||||	|"|||||||||||||d%|}$t|$}%|%S )&Nr   modified_by_nameresponsible_namelogic_type_codestatus_typemodified_at_beforemodified_at_afterr   r   user_rating
owner_nameauthor_namer    AND obj_tree_parent_id IN ('{}')','r   r   z^[a-zA-Z]+-[0-9]+$z^[0-9]+$)r	   r   r
   r   r   6   Недопустимое значение field_name: Tr   obj_r   rq   Z	_tsvectorr   r~   $obj_modified_at > now() - interval '
 days' ANDz"set gin_fuzzy_search_limit=100000;r	   @B u\  
            SELECT 
                subs.*, 
                ts_headline(
                    'russian', 
                    substring(subs.{search_field} FROM 0 FOR 100000), 
                    :tsquery, 
                    'MaxFragments=1,MaxWords=25,MinWords=24'
                ) AS headline 
            FROM (
                (    
                    SELECT
                        obj_id,
                        obj_code,
                        obj_model,
                        obj_name as title,
                        :label || (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 365 then 'R' else '' end) as label,
                        ts_rank_cd({tsvector_field}, :tsquery, 2|8)
                        * (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 365 then
                                    (365-EXTRACT(days from (now()-obj_modified_at)))/30+1 else 1 end) as rank,
                        EXTRACT(days from (now()-obj_modified_at)) as age_days,
                        {search_field} as {search_field}
                    FROM cmf_full_search
                    WHERE
                        {age_days_subquery}
                        {tsvector_field} @@ :tsquery
                        and obj_model IN :model_name_in and obj_model NOT IN :model_name_not_in
                        and (obj_text is null or obj_text = '' or :text_stop_words is null or text_tsvector @@ to_tsquery('russian', :text_stop_words))
                        and part_no <= :max_partno
                        and ( :parent_id is null or obj_parent_id = :parent_id )
                        AND ( :owner_name IS NULL OR obj_owner_name = :owner_name )
                        AND ( :author_name IS NULL OR obj_author_name = :author_name )
                        AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name)
                        and ( :responsible_name is null or obj_responsible_names ILIKE '%' || :responsible_name || '%' )
                        {tags_filter}
                        {tree_parent_filter}
                        and ( :logic_type_code is null or obj_logic_type_code = :logic_type_code )
                        and ( :status_type is null or obj_status_type = :status_type )
                        AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before )
                        AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after )
                        and ( :archived is null or obj_archived = :archived )
                        AND ( :deleted IS NULL OR obj_deleted = :deleted )
                        and ( :user_rating is null or obj_user_rating >= :user_rating)
                    ORDER BY rank desc
                    LIMIT :slice_for OFFSET :slice_from
                )
            UNION
                /* Дополнительно проверяем полное совпадение по ilike с высоким рангом */
                (    
                    SELECT
                        obj_id,
                        obj_code,
                        obj_model,
                        obj_name as title,
                        :label || (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 365 then 'R' else '' end) as label,
                        100
                        * (CASE WHEN EXTRACT(days from (now()-obj_modified_at)) < 365 then
                                    (365-EXTRACT(days from (now()-obj_modified_at)))/30+1 else 1 end) as rank,
                        EXTRACT(days from (now()-obj_modified_at)) as age_days,
                        {search_field} as {search_field}
                    FROM cmf_full_search
                    WHERE
                        {age_days_subquery}
                        /* Дополнительно проверяем полное совпадение по ilike с высоким рангом */
                        ( {search_field} ILIKE '%' || :like_query || '%'
                            or (:obj_code is not null and obj_code ILIKE '%' || :obj_code)
                            or obj_id = :like_query
                        )
                        and obj_model IN :model_name_in and obj_model NOT IN :model_name_not_in
                        and part_no <= :max_partno
                        and ( :parent_id is null or obj_parent_id = :parent_id )
                        AND ( :owner_name IS NULL OR obj_owner_name = :owner_name )
                        AND ( :author_name IS NULL OR obj_author_name = :author_name )
                        AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name)
                        and ( :responsible_name is null or obj_responsible_names ILIKE '%' || :responsible_name || '%' )
                        {tags_filter}
                        {tree_parent_filter}
                        and ( :logic_type_code is null or obj_logic_type_code = :logic_type_code )
                        and ( :status_type is null or obj_status_type = :status_type )
                        AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before )
                        AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after )
                        and ( :archived is null or obj_archived = :archived )
                        AND ( :deleted IS NULL OR obj_deleted = :deleted )
                        and ( :user_rating is null or obj_user_rating >= :user_rating)

                        /*and (obj_text is null or obj_text = '' or :text_stop_words is null or text_tsvector @@ to_tsquery('russian', :text_stop_words))*/
                    /*ORDER BY obj_id desc -- не будем делать, т.к. тормозит*/
                    LIMIT :slice_for/10 
                    OFFSET :slice_from/10
                )
            ) as subs
            ORDER BY subs.rank desc
            LIMIT :slice_for 
            OFFSET :slice_from;
        )r   search_fieldtsvector_fieldage_days_subqueryheadline_fieldtags_filter)tsquery
slice_from	slice_formodel_name_inr   r   r   r   
max_partnor   r  r  r  r  r  r  r  r  r   r   rf   r  )r   formatr3   _build_tags_filterr4   matchupperr   intr   r   rB   rC   rF   rK   tupler   )&r$   r   r*  Ztsquery_strr   r   r   r   r   r   r   r   r  r  r  r  r  r  r   r   r  r  r  r   
tags_namesr&  tags_paramsrf   r"  r%  r#  r(  r)  r$  r+  r   
found_objsall_objsr!   r!   r"   r
  r  s    





"





_gzCmfFullSearch.search_oncez
str | None)r   c           "      K   s  | d}	| d}
| d}| d}| d}| d}| d}| d}| d	}| d
}| dd }| d|}|p|}| d}|rdd|nd}| dpg }| |\}}d}|r| }}d}|dkrtd| dd d| }|d }|d |d  }d}|r0t|}d| d}|dkr>dnd}d| d | d!| d"| d#| d$| d$| d%}tjjj	
 |||t|t||||	|||
||||||d&|} t| }!|!S )'Nr   r  r  r  r  r  r  r   r   r  r  r  r   r  r  r   r   z
            AND ( :responsible_name IS NULL OR obj_responsible_names ILIKE '%' || :responsible_name || '%' )
            AND ( :owner_name IS NULL OR obj_owner_name = :owner_name )
        z
            AND (
                :responsible_name IS NULL 
                OR obj_responsible_names ILIKE '%' || :responsible_name || '%' 
                OR obj_owner_name = :owner_name
            )
        )r	   r   r
   r  Tr   r  r   r~   r  r   r	   r!  a  
            SELECT 
                obj_id,
                obj_code,
                obj_model,
                obj_name as title,
                :label || (CASE WHEN EXTRACT(DAYS FROM (NOW() - obj_modified_at)) < 365 then 'R' else '' end) as label,
                100 / (EXTRACT(DAYS FROM (NOW() - obj_modified_at)) + 1) as rank,
                EXTRACT(DAYS FROM (NOW() - obj_modified_at)) as age_days,
                z as z,
                substring(z` FROM 0 FOR 240) as headline
            FROM cmf_full_search
            WHERE
                a  
                obj_model IN :model_name_in AND obj_model NOT IN :model_name_not_in
                AND part_no <= :max_partno
                AND ( :parent_id IS NULL OR obj_parent_id = :parent_id )
                AND ( :author_name IS NULL OR obj_author_name = :author_name )
                AND ( :modified_by_name IS NULL OR obj_modified_by_name = :modified_by_name)
                z
                a  
                AND ( :logic_type_code IS NULL OR obj_logic_type_code = :logic_type_code )
                AND ( :status_type IS NULL OR obj_status_type = :status_type )
                AND ( :modified_at_before IS NULL OR obj_modified_at <= :modified_at_before )
                AND ( :modified_at_after IS NULL OR obj_modified_at >= :modified_at_after )
                AND ( :archived IS NULL OR obj_archived = :archived )
                AND ( :deleted IS NULL OR obj_deleted = :deleted )
                and ( :user_rating is null or obj_user_rating >= :user_rating)
            ORDER BY rank desc
            LIMIT :slice_for OFFSET :slice_from;
        )r(  r)  r*  r   r   r+  r   r  r  r  r  r  r  r   r   r  )r   r,  r3   r-  r   r0  r   r   rB   rC   rF   rK   r1  r   )"r$   r*  r   r   r   r   r   r   r   r   r  r  r  r  r  r  r   r   r  r  r  r   r2  r&  r3  Zowner_and_responsible_filterr"  r(  r)  r$  r+  r   r4  r5  r!   r!   r"   r  /  s    











			!zCmfFullSearch.filter_oncez	list[str]ztuple[str, dict[str, str]])r2  r   c                 C   sH   i }d}t | D ].\}}d|d  }|||< | d| d}q||fS )Nr   tagr~   z AND obj_tags ILIKE '%' || :z || '%')r   )r2  r3  r&  r   r6  Z	tag_paramr!   r!   r"   r-    s    z CmfFullSearch._build_tags_filter)r   c                 C   s0   | sg S t jjj dd| i}dd |D S )Na/  
                WITH tree_parents AS (
                    WITH RECURSIVE r AS (
                        SELECT obj_id, obj_code, obj_tree_parent_id
                        FROM cmf_full_search
                        WHERE obj_tree_parent_id = :tree_parent_id

                        UNION

                        SELECT cfs.obj_id, cfs.obj_code, cfs.obj_tree_parent_id
                        FROM cmf_full_search AS cfs
                        JOIN r ON cfs.obj_tree_parent_id = r.obj_id
                    )
                    SELECT obj_id FROM r
                    WHERE r.obj_id IN (SELECT obj_tree_parent_id FROM r)
                    
                    UNION
                    
                    SELECT :tree_parent_id
                )
                SELECT * FROM tree_parents;
            r   c                 S   s   g | ]}|d  qS )r   r!   )r'   r   r!   r!   r"   r*     s     z3CmfFullSearch._get_all_branches.<locals>.<listcomp>)r   r   rB   rC   rF   rK   )r   Zrecordsr!   r!   r"   r    s    zCmfFullSearch._get_all_branchesc              
   C   sp  dt _|dd}tdd|}td|}d}d}d}	|D ]}
t|
dkrPq<t|
dkr|
dkrfq<|
d	kr~|	d|
 7 }	q<|
dkrq<|
d
kr|	d7 }	q<|
dkr|	d7 }	q<t|
dkrq<|
d dkrt|
dkrq<|	d|
dd   7 }	|d|
dd   7 }q<|rq<|
dd}
|d7 }|dkr.d}|	rB|	d dkrJ|	d7 }	t|
dkr| j|
|d}t|dkr|	|d  7 }	n|	dd| d 7 }	|r|dkr q|dkr< qq<q<|rt|dkr|d dkr|dd  }|S |	dddddd d!d 	 }	|	r<|	d dkr<|	dd  }	|	r\|	d dkr\|	dd  }	|	r||	d dkr||	dd  }	|	r|	dd  dkr|	d d }	z.t
jjj d"d#|	i}t|d d }	W n tjjk
rX } zft
jjj  td$|  td%|	 d| dt j  t
jjj d&d#|i}t|d d }	W 5 d }~X Y nX t|d'|	 |	t _|	S )(Nr   zwww.u   [^-A-Za-zА-Яа-я0-9()|&!' ]r.   z(,| |&|\||\(|\))r   r~   )r   !z()&|)oru   или|z |)u   иand&z &r   z& !r7  r      F)r9  r;  z& r   z( z | z )
   )r;  r9  z OR z or z AND z & z and z!select to_tsquery('russian', :q);qu;   Ошибочный синтаксиса в запросе: u@   DEV: Ошибочный синтаксиса в запросе: z+select websearch_to_tsquery('russian', :q);z->)r   r   replacer4   r  r5   r   prepare_wordr3   r   r   
CmfSynonymrB   rC   rF   rK   r   rA   excZProgrammingErrorZrollbackr   r   r  )r$   r   r   r   r   Zsearch_query_allowed_symbtokensZ
word_countZstopsr?  tZ	sug_wordsr'  er!   r!   r"   r    s    

( "z"CmfFullSearch.prepare_search_queryc                 C   s  t  jd| d7  _|d tjkr0d}tj}n
d}tj}g }t|s||}g }t	|}|rt  jd| d7  _|
| t  jd7  _d}	|D ]}
|	d	kr qHt|
d	krq|
d |d kr|
d
 |d
 krqd|
kr|
dd}
t  jd|
 d7  _|
|
 |	d
7 }	qt  jd|
 d7  _|
|
 |	d
7 }	qg }tjjj dd|i}d}	|D ]\}}|	d
kr qt|d	krqn|d |d ks|d
 |d
 krn|dd}t  jd| d7  _|
| |	d
7 }	qnt|t|B |hB }n|h}t }|D ]D}
t|
d d	 D ]*}||j t  jd|j d7  _q2q||B }t }|rtjjddt||hB gddgdgdd
gd}|D ]\}|jr|jjdd d D ]6}| dd}t  jd| d7  _|| qʐq||B t|B }t|S )Nz|w:z: r   enruzaddNinjaRevers r   zspellError, r   r~   r.   z<->z	addSpell z
            SELECT
                name, similarity(:word, name) as sim
            FROM cmf_synonym
            WHERE
                :word % name
            ORDER BY "sim" desc
            LIMIT 5;
             wordzaddSpellTrgm z
normalize r   r   r	   Zorderno)r   r   r   r   ,r   zsynAdd )r   r   stringascii_lettersr   Zdictionary_enZdictionary_ruZdictionary_checkZsuggestZninja_reversr   r   r@  r   rB  rB   rC   rF   rK   r   morphparser   Znormal_formr   r	   r   r5   r   )r$   rI  r   langZ
dictionaryZfiltered_suggestions3ZsuggestionsZfiltered_suggestionsZnwr(   r1   Zfiltered_suggestions2Zsuggestions2_listZsugg_Zall_suggestionsZnormalized_wordsZsynonym_wordsZsynonym_listZsynonymrW   r!   r!   r"   rA  9  s    


 



	 
$
 zCmfFullSearch.prepare_wordc              	   C   s   t | dk rd S g }|d d D ]b}|d dsB|d dsBq i }|d |d< |dd rp|d d |d< nd |d< || q t }||_d	|_| |_t	j
j|_d
|_t  |  W 5 Q R X d S )Nr   r   r=   zCmfDocument:zCmfTask:r>   parentr   searchF)r   
startswithr   r   r   ZCmfSearchStatr   actionr   r   r	  r=   Z	person_idZ
aggregatedr   Zdisable_aclZsave)r   r   Zst_dictZrecZst_recstatr!   r!   r"   r    s(    

z!CmfFullSearch._do_calc_statistics)NNNFFNNNNNNNNNNNNNNNN)Nr&   F)FNNFN)TFF)T)(__name__
__module____qualname____doc__r   r   Z	api_allowZapi_methodsstaticmethodr   r#   classmethodr%   r-   r7   r8   r]   r_   r   r   r   r   r   Z	BaseModelr   r   Zcmf_deferred_jobr   r   r   r   r   r   r
  r  r-  r  r  rA  r  r!   r!   r!   r"   r      s                     




+
                     n

{!
6
!         =u"dnr   )r   r   collectionsr   Zcmf.includeZcmf.data_providers.sqlalchemyr   Zcmf.fields.cmf_full_searchr   ZenchantrK  Z	pymorphy3r4   rA   Zbs4r   ZMorphAnalyzerrM  r   Zcmf_full_searchr   r!   r!   r!   r"   <module>   s   
