package jnpf.dmsMapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import jnpf.dmsEntity.permission.entity.DmsFolderEntity;
import jnpf.dmsEntity.permission.model.DmsFolderAncestorRow;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

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

@Mapper
public interface DmsFolderMapper extends BaseMapper<DmsFolderEntity> {
    String COLUMNS = "id, tenant_id, parent_id, name, created_by, created_at, sort_code, revision, "
            + "updated_by, updated_at, f_delete_mark, deleted_by, deleted_at";

    @Select("SELECT " + COLUMNS + " FROM public.dms_folders "
            + "WHERE tenant_id = #{tenantId} AND id = #{id} AND f_delete_mark IS NULL")
    DmsFolderEntity selectByTenantAndId(@Param("tenantId") String tenantId, @Param("id") UUID id);

    @Select("SELECT " + COLUMNS + " FROM public.dms_folders "
            + "WHERE tenant_id = #{tenantId} AND id = #{id} AND f_delete_mark IS NULL FOR UPDATE")
    DmsFolderEntity selectByTenantAndIdForUpdate(@Param("tenantId") String tenantId,
                                                  @Param("id") UUID id);

    @Select({"<script>",
            "SELECT " + COLUMNS,
            "FROM public.dms_folders WHERE tenant_id = #{tenantId} AND f_delete_mark IS NULL AND id IN",
            "<foreach collection='ids' item='id' open='(' separator=',' close=')'>#{id}</foreach>",
            "</script>"})
    List<DmsFolderEntity> selectByTenantAndIds(@Param("tenantId") String tenantId,
                                                @Param("ids") List<UUID> ids);

    @Select("WITH RECURSIVE folder_path(id, parent_id, name, depth) AS ("
            + "SELECT id, parent_id, name, 0 FROM public.dms_folders WHERE tenant_id = #{tenantId} "
            + "AND id = #{id} AND f_delete_mark IS NULL UNION ALL "
            + "SELECT parent.id, parent.parent_id, parent.name, child.depth + 1 FROM folder_path child "
            + "JOIN public.dms_folders parent ON parent.tenant_id = #{tenantId} "
            + "AND parent.id = child.parent_id AND parent.f_delete_mark IS NULL) "
            + "SELECT '/' || string_agg(name, '/' ORDER BY depth DESC) FROM folder_path")
    String selectPath(@Param("tenantId") String tenantId, @Param("id") UUID id);

    @Select({"<script>",
            "WITH RECURSIVE ancestors(start_folder_id, ancestor_folder_id, parent_id, depth) AS (",
            "SELECT f.id, f.id, f.parent_id, 0 FROM public.dms_folders f",
            "WHERE f.tenant_id = #{tenantId} AND f.f_delete_mark IS NULL AND f.id IN",
            "<foreach collection='folderIds' item='id' open='(' separator=',' close=')'>#{id}</foreach>",
            "UNION ALL",
            "SELECT a.start_folder_id, p.id, p.parent_id, a.depth + 1",
            "FROM ancestors a JOIN public.dms_folders p",
            "ON p.tenant_id = #{tenantId} AND p.id = a.parent_id AND p.f_delete_mark IS NULL",
            ")",
            "SELECT start_folder_id, ancestor_folder_id, depth FROM ancestors",
            "ORDER BY start_folder_id, depth",
            "</script>"})
    List<DmsFolderAncestorRow> selectAncestorRows(@Param("tenantId") String tenantId,
                                                   @Param("folderIds") List<UUID> folderIds);

    @Select("SELECT 1 FROM (SELECT pg_advisory_xact_lock(hashtextextended(#{tenantId} "
            + "|| ':folder-siblings:' || COALESCE(CAST(#{parentId} AS TEXT), 'root'), 0))) sibling_lock")
    Integer lockSiblingSet(@Param("tenantId") String tenantId, @Param("parentId") UUID parentId);

    @Select("SELECT COALESCE(MAX(sort_code), 0) + 1024 FROM public.dms_folders "
            + "WHERE tenant_id = #{tenantId} AND parent_id IS NOT DISTINCT FROM #{parentId} "
            + "AND f_delete_mark IS NULL")
    long nextSortCode(@Param("tenantId") String tenantId, @Param("parentId") UUID parentId);

    @Select({"<script>",
            "SELECT EXISTS (SELECT 1 FROM public.dms_folders",
            "WHERE tenant_id = #{tenantId} AND parent_id IS NOT DISTINCT FROM #{parentId}",
            "AND f_delete_mark IS NULL AND lower(name) = lower(#{name})",
            "<if test='excludeId != null'>AND id != #{excludeId}</if>)",
            "</script>"})
    boolean siblingNameExists(@Param("tenantId") String tenantId,
                              @Param("parentId") UUID parentId,
                              @Param("name") String name,
                              @Param("excludeId") UUID excludeId);

    @Select("SELECT " + COLUMNS + " FROM public.dms_folders "
            + "WHERE tenant_id = #{tenantId} AND parent_id IS NOT DISTINCT FROM #{parentId} "
            + "AND f_delete_mark IS NULL AND (sort_code, id) < (#{sortCode}, #{id}) "
            + "ORDER BY sort_code DESC, id DESC LIMIT 1 FOR UPDATE")
    DmsFolderEntity selectPreviousForUpdate(@Param("tenantId") String tenantId,
                                             @Param("parentId") UUID parentId,
                                             @Param("sortCode") long sortCode,
                                             @Param("id") UUID id);

    @Select("SELECT " + COLUMNS + " FROM public.dms_folders "
            + "WHERE tenant_id = #{tenantId} AND parent_id IS NOT DISTINCT FROM #{parentId} "
            + "AND f_delete_mark IS NULL AND (sort_code, id) > (#{sortCode}, #{id}) "
            + "ORDER BY sort_code, id LIMIT 1 FOR UPDATE")
    DmsFolderEntity selectNextForUpdate(@Param("tenantId") String tenantId,
                                         @Param("parentId") UUID parentId,
                                         @Param("sortCode") long sortCode,
                                         @Param("id") UUID id);

    @Update("UPDATE public.dms_folders SET name = #{name}, revision = revision + 1, "
            + "updated_by = #{actorId}, updated_at = CURRENT_TIMESTAMP "
            + "WHERE tenant_id = #{tenantId} AND id = #{id} AND revision = #{revision} AND f_delete_mark IS NULL")
    int rename(@Param("tenantId") String tenantId, @Param("id") UUID id,
               @Param("name") String name, @Param("revision") long revision,
               @Param("actorId") String actorId);

    @Update("UPDATE public.dms_folders SET sort_code = #{sortCode}, revision = revision + 1, "
            + "updated_by = #{actorId}, updated_at = CURRENT_TIMESTAMP "
            + "WHERE tenant_id = #{tenantId} AND id = #{id} AND revision = #{revision} AND f_delete_mark IS NULL")
    int reorder(@Param("tenantId") String tenantId, @Param("id") UUID id,
                @Param("sortCode") long sortCode, @Param("revision") long revision,
                @Param("actorId") String actorId);

    @Update("UPDATE public.dms_folders SET parent_id = #{parentId}, sort_code = #{sortCode}, "
            + "revision = revision + 1, updated_by = #{actorId}, updated_at = CURRENT_TIMESTAMP "
            + "WHERE tenant_id = #{tenantId} AND id = #{id} AND revision = #{revision} AND f_delete_mark IS NULL")
    int move(@Param("tenantId") String tenantId, @Param("id") UUID id,
             @Param("parentId") UUID parentId, @Param("sortCode") long sortCode,
             @Param("revision") long revision, @Param("actorId") String actorId);

    @Update("UPDATE public.dms_folders SET f_delete_mark = 1, deleted_by = #{actorId}, "
            + "deleted_at = CURRENT_TIMESTAMP, revision = revision + 1, "
            + "updated_by = #{actorId}, updated_at = CURRENT_TIMESTAMP "
            + "WHERE tenant_id = #{tenantId} AND id = #{id} AND revision = #{revision} AND f_delete_mark IS NULL")
    int softDelete(@Param("tenantId") String tenantId, @Param("id") UUID id,
                   @Param("revision") long revision, @Param("actorId") String actorId);

    @Select("SELECT EXISTS (SELECT 1 FROM public.dms_folders child "
            + "WHERE child.tenant_id = #{tenantId} AND child.parent_id = #{id} AND child.f_delete_mark IS NULL) "
            + "OR EXISTS (SELECT 1 FROM public.dms_documents document "
            + "WHERE document.tenant_id = #{tenantId} AND document.folder_id = #{id})")
    boolean hasContents(@Param("tenantId") String tenantId, @Param("id") UUID id);

    @Select("WITH RECURSIVE ancestors(id, parent_id) AS ("
            + "SELECT id, parent_id FROM public.dms_folders WHERE tenant_id = #{tenantId} "
            + "AND id = #{targetId} AND f_delete_mark IS NULL UNION ALL "
            + "SELECT parent.id, parent.parent_id FROM ancestors child "
            + "JOIN public.dms_folders parent ON parent.tenant_id = #{tenantId} "
            + "AND parent.id = child.parent_id AND parent.f_delete_mark IS NULL) "
            + "SELECT EXISTS (SELECT 1 FROM ancestors WHERE id = #{sourceId})")
    boolean targetIsSelfOrDescendant(@Param("tenantId") String tenantId,
                                     @Param("sourceId") UUID sourceId,
                                     @Param("targetId") UUID targetId);

    @Select("WITH RECURSIVE subtree AS ("
            + "SELECT f.*, 0 AS depth FROM public.dms_folders f WHERE f.tenant_id = #{tenantId} "
            + "AND f.id = #{id} AND f.f_delete_mark IS NULL UNION ALL "
            + "SELECT child.*, parent.depth + 1 FROM subtree parent "
            + "JOIN public.dms_folders child ON child.tenant_id = #{tenantId} "
            + "AND child.parent_id = parent.id AND child.f_delete_mark IS NULL) "
            + "SELECT " + COLUMNS + " FROM subtree ORDER BY depth, sort_code, id")
    List<DmsFolderEntity> selectSubtree(@Param("tenantId") String tenantId, @Param("id") UUID id);
}