package cn.lihongjie.coal.netDisk.repository;

import cn.lihongjie.coal.base.dao.BaseRepository;
import cn.lihongjie.coal.netDisk.entity.NetDiskEntity;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface NetDiskRepository extends BaseRepository<NetDiskEntity> {
    NetDiskEntity findBySha256(String sha256);

    List<NetDiskEntity> findAllBySha256(String sha256);

    long countByNameAndParentNull(String name);

    boolean existsByOssKey(String ossKey);

    NetDiskEntity findByParentNull();

    List<NetDiskEntity> findALlByParentNullAndOrganizationId(String organizationId);

    long countByParentNullAndOrganizationIdAndName(String organizationId, String name);

    @Query(
            value =
                    """

with recursive
    tmp as (select t.*, 0 as level
            from t_net_disk t
            where t.id = :id
            union all

            select p.*, t.level + 1 as level
            from t_net_disk p
                     inner join tmp t on p.id = t.parent_id),

    tmp2 as (select * from tmp order by level desc)


select string_agg(name, '/')
from tmp2
""",
            nativeQuery = true)
    String idToPath(@Param("id") String id);



    @Query(
            value =
                    """

with recursive
    tmp as (select t.*, 0 as level
            from t_net_disk t
            where t.id = :id
            union all

            select p.*, t.level + 1 as level
            from t_net_disk p
                     inner join tmp t on p.id = t.parent_id),

    tmp2 as (select * from tmp order by level desc)


select id
from tmp2
""",
            nativeQuery = true)
    List<String> idToPath2(@Param("id") String id);

    @Query(
            value =
                    """

with recursive tmp as (select (regexp_split_to_array(:path, '/'))[2::] as arr)
   , tmp2 as (select tmp.arr, 1 as index, t.id as id, ARRAY_LENGTH(tmp.arr, 1) as len
              from tmp
                       inner join t_net_disk t on t.parent_id is null and t.organization_id = :organizationId and t.name = '/'
              union all

              select tx.arr as arr, tx.index + 1 as index, t.id as id, tx.len
              from t_net_disk t
                       inner join tmp2 tx on t.parent_id = tx.id and t.name = tx.arr[tx.index + 1])

select id
from tmp2  where index = len
""",
            nativeQuery = true)
    String pathToId(@Param("path") String path, @Param("organizationId") String organizationId);

    @Query(value = """

with recursive tmp as (

select 0 as depth, t.id as id from t_net_disk t where t.id = :id
union all 
select tmp.depth + 1 as depth, t.id as id from t_net_disk t 
inner join tmp on t.parent_id = tmp.id
where t.entry_type = '0'
)

select max(depth) from tmp

""", nativeQuery = true)
    Integer dirDepth(@Param("id") String id);

    @Query(value = """

with recursive tmp as (

select 0 as depth, t.id as id from t_net_disk t where t.id = :id
union all 
select tmp.depth + 1 as depth, t.id as id from t_net_disk t 
inner join tmp on t.parent_id = tmp.id
where t.entry_type = '0'
)

select id from tmp

""", nativeQuery = true)
    List<String> findAllSubDir(@Param("id") String id );
}
