package jnpf.dmsMapper;

import jnpf.dmsEntity.search.model.DmsSearchIndexSource;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.UUID;
import java.util.List;

@Mapper
public interface DmsSearchIndexSourceMapper {
    @Select("<script>SELECT CAST(file_version_id AS TEXT) FROM public.dms_version_states "
            + "WHERE tenant_id=#{tenantId} AND lifecycle_status='EFFECTIVE' "
            + "AND effective_from &lt;= statement_timestamp() "
            + "AND (effective_to IS NULL OR effective_to &gt; statement_timestamp()) "
            + "AND CAST(file_version_id AS TEXT) IN "
            + "<foreach collection='ids' item='id' open='(' separator=',' close=')'>#{id}</foreach>"
            + "</script>")
    List<String> currentVersionIds(@Param("tenantId") String tenantId, @Param("ids") List<String> ids);

    @Select("WITH RECURSIVE folder_chain AS ("
            + "SELECT f.id, f.parent_id, f.name, 0 AS depth FROM public.dms_folders f "
            + "JOIN public.dms_documents source_document "
            + "ON source_document.tenant_id = f.tenant_id AND source_document.folder_id = f.id "
            + "WHERE source_document.tenant_id = #{tenantId} "
            + "AND source_document.id = #{documentId} AND f.f_delete_mark IS NULL "
            + "UNION ALL SELECT parent.id, parent.parent_id, parent.name, child.depth + 1 "
            + "FROM public.dms_folders parent JOIN folder_chain child "
            + "ON child.parent_id = parent.id WHERE parent.tenant_id = #{tenantId} "
            + "AND parent.f_delete_mark IS NULL) "
            + "SELECT d.tenant_id, d.id AS document_id, v.id AS file_version_id, "
            + "d.folder_id, (SELECT string_agg(name, '/' ORDER BY depth DESC) FROM folder_chain) "
            + "AS folder_path, d.document_no, d.name AS document_name, d.document_type, "
            + "d.responsible_dept_id, v.version_label, v.file_name, v.mime_type, "
            + "COALESCE(s.lifecycle_status, 'UNKNOWN') AS lifecycle_status, "
            + "COALESCE(s.revision, 0) AS state_revision, v.created_at "
            + "FROM public.dms_documents d JOIN public.dms_file_versions v "
            + "ON v.tenant_id = d.tenant_id AND v.document_id = d.id "
            + "JOIN public.dms_version_states s ON s.tenant_id = v.tenant_id "
            + "AND s.document_id = v.document_id AND s.file_version_id = v.id "
            + "WHERE d.tenant_id = #{tenantId} AND d.id = #{documentId} "
            + "AND v.id = #{fileVersionId} AND s.lifecycle_status = 'EFFECTIVE' "
            + "AND s.effective_from <= CURRENT_TIMESTAMP "
            + "AND (s.effective_to IS NULL OR CURRENT_TIMESTAMP < s.effective_to) LIMIT 1")
    DmsSearchIndexSource selectSource(@Param("tenantId") String tenantId,
                                      @Param("documentId") UUID documentId,
                                      @Param("fileVersionId") UUID fileVersionId);
}