package com.upsfj.sfjprojectbackend.mapper;

import com.upsfj.sfjprojectbackend.entity.Algorithm;
import com.upsfj.sfjprojectbackend.entity.Field;
import com.upsfj.sfjprojectbackend.entity.UseCase;
import org.apache.ibatis.annotations.*;
import org.springframework.data.relational.core.sql.In;

import java.util.List;

@Mapper
public interface AlgorithmMapper {
//    查询算法的所有相关信息
    @Select("SELECT \n" +
            "    a.algorithm_id, \n" +
            "    a.algorithm_name, \n" +
            "    a.algorithm_description, \n" +
            "    a.algorithm_img, \n" +
            "    a.algorithm_coder, \n" +
            "    GROUP_CONCAT(DISTINCT i.field_name) AS algorithmFields, \n" +
            "    GROUP_CONCAT(DISTINCT uc.usecase_name) AS algorithmUserCases\n" +
            "FROM \n" +
            "    sfj_algorithms a \n" +
            "LEFT JOIN \n" +
            "    sfj_algorithm_field ai ON a.algorithm_id = ai.algorithm_id \n" +
            "LEFT JOIN \n" +
            "    sfj_field i ON ai.field_id = i.field_id \n" +
            "LEFT JOIN \n" +
            "    sfj_algorithm_usecases auc ON a.algorithm_id = auc.algorithm_id \n" +
            "LEFT JOIN \n" +
            "    sfj_usecase uc ON auc.usecase_id = uc.usecase_id\n" +
            "GROUP BY \n" +
            "    a.algorithm_id;")
    @Results({
            @Result(property = "algorithm_id", column = "algorithm_id"),
            @Result(property = "algorithm_name", column = "algorithm_name"),
            @Result(property = "algorithm_description", column = "algorithm_description"),
            @Result(property = "algorithm_img", column = "algorithm_img"),
            @Result(property = "algorithm_coder", column = "algorithm_coder"),
            @Result(property = "fieldStr", column = "algorithmFields"),
            @Result(property = "userStr", column = "algorithmUserCases")
    })
    List<Algorithm> findAllAlgorithm();


//

    @Select("select * from sfj_field")
    List<Field> findAllField();


//   @Select("select ")




    @Select("select * from sfj_usecase")
    List<UseCase> findAllUseCase();


    @Update("UPDATE sfj_algorithms SET algorithm_name=#{algorithm_name},algorithm_description=#{algorithm_description} ,algorithm_coder=#{algorithm_coder}WHERE algorithm_id=#{algorithm_id}")
    void updateAlgorithmById(Algorithm algorithm);

    @Insert("INSERT INTO sfj_algorithms(algorithm_id,algorithm_name,algorithm_description,algorithm_img,algorithm_coder) VALUES(#{algorithm_id},#{algorithm_name},#{algorithm_description},#{algorithm_img},#{algorithm_coder})")
    void addAlgorithm(Algorithm algorithm);

    @Delete("DELETE FROM sfj_algorithms WHERE algorithm_id=#{algorithm_id}")
    void deleteAlgorithm(Algorithm algorithm);



    @Delete("DELETE FROM sfj_algorithm_field WHERE algorithm_id=#{algorithm_id} AND field_id=#{field_id}")
    void deleteFieldForAlgorithm(@Param("algorithm_id") int algorithmId,@Param("field_id") int fieldId);

    @Insert("INSERT INTO sfj_algorithm_field(algorithm_id, field_id) VALUES(#{algorithm_id}, #{field_id})")
    void addFieldForAlgorithm(@Param("algorithm_id") int algorithmId,@Param("field_id") int fieldId);


    @Select("SELECT field_id FROM sfj_algorithm_field WHERE algorithm_id=#{algorithm_id}")
    List<Integer> getFieldIdsForAlgorithm(int algorithmId);


    @Select("select field_id from sfj_field where field_name=#{name}")
    Integer findFieldIdByName(String name);

    @Select("SELECT COUNT(*) FROM sfj_algorithms")
    Integer getAlgorithmCount();


    @Delete("DELETE FROM sfj_algorithm_usecases WHERE algorithm_id=#{algorithm_id} AND usecase_id=#{use_id}")
    void deleteUSeForAlgorithm(@Param("algorithm_id") int algorithmId,@Param("use_id") int usecaseId);


    @Insert("INSERT INTO sfj_algorithm_usecases(algorithm_id, usecase_id) VALUES(#{algorithm_id}, #{use_id})")
    void addUseForAlgorithm(@Param("algorithm_id") int algorithmId,@Param("use_id") int usecaseId);

    @Select("SELECT usecase_id FROM sfj_algorithm_usecases WHERE algorithm_id=#{algorithmId}")
    List<Integer> getUseIdForAlgorithm(int algorithmId);

    @Select("select usecase_id from sfj_usecase where usecase_name=#{name}")
    Integer findUseIdByName(String name);

//
//    @Insert("INSERT INTO sfj_algorithms(algorithm_img) VALUES(#{algorithm_img})")
//    void addAlgorithmImg(String algorithm_img);
//
//
    @Update("UPDATE sfj_algorithms SET algorithm_img=#{algorithm_img} WHERE algorithm_id=#{algorithm_id}" )
    void updateAlgorithmImg(String algorithm_img,Integer algorithm_id);




}
