package com.usoftchina.bi.server.dao.dataSource;

import com.usoftchina.bi.server.model.po.BaseConfig;
import com.usoftchina.bi.server.model.po.DataConnector;
import com.usoftchina.bi.server.model.po.DataConnectorList;
import com.usoftchina.bi.server.model.po.Databases;
import com.usoftchina.bi.core.base.TestPage;
import com.usoftchina.bi.server.model.vo.configVo.DatabasesInfo;
import com.usoftchina.bi.server.model.vo.configVo.GroupInfo;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface DataConnectorMapper {

    /*
    查询数据源创建人ID
     */
    @Select("select c.create_id from bi_data_connectors c,bi_charts cs where cs.id = #{id} and cs.bd_data_id = c.id")
    int getCreateId(int id);

    /*
    查询数据源ID
     */
    @Select("select c.id from bi_data_connectors c,bi_charts cs where cs.id = #{id} and cs.bd_data_id = c.id")
    int getBaseId(int id);

    /*
    查询单个数据源
     */
    @Select("select ID as dataId, DATA_NAME as dataName, DATA_NOTE as note, DATA_TAG as dataTag, COLUMNS_CONFIG as columnConfig, LOAD_OBJECT as loadObject," +
            "CREATE_BY as createBy, create_id as createId, USED_NUMBER as usedNumber, CREATE_DATE as createDate,DB_CONFIG as dbConId,DB_CONFIG as dbConfig, BD_group as connectorGroup," +
            "CON_TYPE as type, demo from bi_data_connectors where id = #{id}")
    @Results(id = "DataConnectorResultMap", value = {
            @Result(column = "dataId", property = "dataId", jdbcType = JdbcType.BIGINT),
            @Result(column = "dataName", property = "dataName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "note", property = "note", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dataTag", property = "dataTag", jdbcType = JdbcType.VARCHAR),
            @Result(column = "columnConfig", property = "columnConfig", jdbcType = JdbcType.VARCHAR),
            @Result(column = "loadObject", property = "loadObject", jdbcType = JdbcType.VARCHAR),
            @Result(column = "createBy", property = "createBy", jdbcType = JdbcType.VARCHAR),
            @Result(column = "createId", property = "createId", jdbcType = JdbcType.INTEGER),
            @Result(column = "usedNumber", property = "usedNumber", jdbcType = JdbcType.INTEGER),
            @Result(column = "createDate", property = "createDate", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dbConId", property = "dbConId", jdbcType = JdbcType.VARCHAR),
            @Result(column = "connectorGroup", property = "connectorGroup", jdbcType = JdbcType.INTEGER),
            @Result(column = "dbConfig", property = "dbConfig", one = @One(select = "getDataBaseConfig")),
            @Result(column = "demo", property = "demo", jdbcType = JdbcType.BOOLEAN),
            @Result(column = "type", property = "type", jdbcType = JdbcType.VARCHAR)
    })
    DataConnector getOneData(int id);

    /**
     * 获取BaseConfig
     * @param id
     * @return
     */
    @Select("select id, bases_name as name, note, addrass, data_name as dataName, DATABASE_TYPE as databaseType," +
            " PASS_WORD as passWord, USER_NAME as userName, PORT, CREATE_DATE as createDate, demo from bi_databases where id = #{id}")
    BaseConfig getDataBaseConfig(int id);

    /*
    查询自己创建的,权限分配的数据源列表,已经启用
     */
    @Select("select id as dataId, con_type as type, data_name as dataName, data_tag as dataTag, data_note as note, " +
            "bc.create_by as createBy, bc.create_date as createDate," +
            "used_number as usedNumber, db_config as dbConfig ,BD_group as connectorGroup, " +
            "create_id as createId, demo from bi_data_connectors bc where CREATE_ID = #{id} or ( id in ( select distinct bs_db_id from BI_DB_STRATEGYS where bs_id in (" +
            "  select bo_st_id from bi_db_object  where " +
            "  (BO_TYPE='1' and bo_ob_id = #{id} ) or " +
            "  ( bo_type='0' and bo_ob_id in " +
            "  ( select br_user_group from bi_user_rel_groups where br_user_id = #{id} ) " +
            "   )" +
            ")and is_open = '1')) and is_open = '1' ORDER BY dataId DESC")
    @Results(id = "DataConnectorListResultMap", value = {
            @Result(column = "dataId", property = "dataId", jdbcType = JdbcType.BIGINT),
            @Result(column = "dataName", property = "dataName", jdbcType = JdbcType.VARCHAR),
            @Result(column = "note", property = "note", jdbcType = JdbcType.VARCHAR),
            @Result(column = "dataTag", property = "dataTag", jdbcType = JdbcType.VARCHAR),
            @Result(column = "createBy", property = "createBy", jdbcType = JdbcType.VARCHAR),
            @Result(column = "createId", property = "createId", jdbcType = JdbcType.INTEGER),
            @Result(column = "usedNumber", property = "usedNumber", jdbcType = JdbcType.INTEGER),
            @Result(column = "createDate", property = "createDate", jdbcType = JdbcType.VARCHAR),
            @Result(column = "connectorGroup", property = "connectorGroup", jdbcType = JdbcType.INTEGER),
            @Result(column = "dbConfig", property = "dbConfig", one = @One(select = "getDataBaseConfig")),
            @Result(column = "type", property = "type", jdbcType = JdbcType.VARCHAR),
            @Result(column = "demo", property = "demo", jdbcType = JdbcType.BOOLEAN)
    })
    List<DataConnectorList> getDataConnectorList(@Param("id") int id, TestPage testPage);


    /**
     * 查询已经启用的数据源列表
     * @param id
     * @param testPage
     * @return
     */
    @Select("select id as dataId, con_type as type, data_name as dataName, data_tag as dataTag, data_note as note, " +
            "bc.create_by as createBy, bc.create_date as createDate," +
            "used_number as usedNumber, db_config as dbConfig ,BD_group as connectorGroup, " +
            "create_id as createId, demo from bi_data_connectors bc where is_open = '1' ORDER BY dataId DESC")
    @ResultMap("DataConnectorListResultMap")
    List<DataConnectorList> getDataConnectorListWithoutLimit(@Param("id") int id, TestPage testPage);

    /*
    转交数据源
     */
    @Update("update bi_data_connectors set create_by = #{name}, create_id = #{createId} where id = #{baseId}")
    void updataOrder(@Param("name") String name, @Param("createId") int userId, @Param("baseId") int baseId);

    /*
    插入数据源配置
     */
    @Insert("INSERT INTO bi_data_connectors(id,data_name,data_note,data_tag,con_type,LOAD_OBJECT,DB_CONFIG,columns_config,used_number,create_by,create_date, table_name, BD_GROUP, create_id) " +
            "VALUES (#{dataId,jdbcType=INTEGER},#{dataName,jdbcType=VARCHAR}, #{note,jdbcType=VARCHAR}, #{dataTag,jdbcType=VARCHAR}, #{type,jdbcType=VARCHAR}, #{loadObject,jdbcType=VARCHAR}, " +
            "#{dbConId,jdbcType=INTEGER},#{columnConfig,jdbcType=VARCHAR}, #{usedNumber,jdbcType=INTEGER},#{createBy,jdbcType=VARCHAR}, to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'), " +
            "#{tableName,jdbcType=VARCHAR}, #{connectorGroup,jdbcType=VARCHAR},#{createId,jdbcType=INTEGER})")
    @SelectKey(before=true,keyProperty="dataId",resultType=int.class,statement="SELECT bi_data_connectors_sequence.nextval from dual",keyColumn = "id")
    void insertDataConnector(DataConnector dataConnector);

    /*
    更新数据源配置
     */
    @Update("<script>" +
            "UPDATE bi_data_connectors set "  +
            "data_name = #{dataName}" +
            "<if test=\"dataTag != null\"> , data_tag = #{dataTag} </if>" +
            "<if test=\"loadObject != null\"> , LOAD_OBJECT = #{loadObject} </if>" +
            "<if test=\"columnConfig != null\"> , columns_config = #{columnConfig} </if>" +
            "<if test=\"dbConId != null\"> , DB_CONFIG = #{dbConId} </if>" +
            "<if test=\"usedNumber != null\"> , used_number = #{usedNumber} </if>" +
            "<if test=\"createBy != null\"> , create_by = #{createBy} </if>" +
            "<if test=\"type != null\"> , con_TYPE = #{type} </if>" +
            "<if test=\"note != null\"> , DATA_NOTE = #{note} </if>" +
            "<if test=\"tableName != null\"> , table_name = #{tableName} </if>" +
            "<if test=\"connectorGroup != null\"> , BD_GROUP = #{connectorGroup} </if>"+
            "<if test=\"updateDate != null\"> , UPDATE_DATE = to_date(#{updateDate},'YYYY-MM-DD hh24:mi:ss') </if>" +
            "where id = #{dataId}" +
            "</script>")
    void updateData(DataConnector dataConnector);

    /*
    更新数据源配置的分组
     */
    @Update("update bi_data_connectors set BD_GROUP = #{connectGroup} where id = #{dataId}")
    void updateConfigGroup(@Param("dataId") int dataId, @Param("connectGroup") int connectGroup);

    @Select("SELECT COUNT(*) FROM BI_CHARTS WHERE BD_DATA_ID = #{id}")
    int countChartsById(Long id);

    /**
     * 是否存在数据连接名
     * @param name
     * @return
     */
    @Select("SELECT COUNT(*) FROM BI_DATABASES WHERE BASES_NAME = #{name,jdbcType=VARCHAR}")
    int countDataBasesName(@Param("name") String name);

    /**
     * 是否存在数据连接
     * @param databases
     * @return
     */
    @Select("SELECT COUNT(*) FROM BI_DATABASES WHERE ADDRASS = #{addrass,jdbcType=VARCHAR} AND PORT = #{port,jdbcType=VARCHAR} AND USER_NAME = #{userName,jdbcType=VARCHAR}")
    int countDataBases(Databases databases);

    /*
    删除数据源
     */
    @Delete("DELETE FROM BI_DATA_CONNECTORS WHERE ID = #{id}")
    void deleteData(Long id);

    /*
    保存数据库连接
     */
    @Insert("insert into BI_DATABASES(id,ADDRASS, CREATE_DATE, DATABASE_TYPE, DATA_NAME, bases_NAME, PASS_WORD, PORT, USER_NAME, note)" +
            "values(#{id},#{addrass}, to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'), #{databaseType}, #{dataName}, #{name}, #{passWord}, #{port}, #{userName}, #{note})")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT BI_DATABASES_SEQUENCE.nextval from dual",keyColumn = "id")
    void inputDataBases(Databases databases);

    /*
    查询单个数据库连接密码
     */
    @Select("select pass_word from BI_DATABASES where  id= #{id}")
    String getBasesById(int id);

    /*
    查询单个数据库连接
     */
    @Select("select id, bases_name as name, note, addrass, data_name as dataName, DATABASE_TYPE as databaseType," +
            " PASS_WORD as passWord, USER_NAME as userName, PORT, CREATE_DATE as createDate from bi_databases where id = #{id}")
    DatabasesInfo getDatabases(int id);

    /*
    查询数据库配置列表
     */
    @Select("select id, bases_name as name, note, addrass, data_name as dataName, DATABASE_TYPE as databaseType," +
            " PASS_WORD as passWord, USER_NAME as userName, PORT, CREATE_DATE as createDate, demo from bi_databases ORDER BY ID DESC")
    List<Databases> getDatabasesList(TestPage testPage);

    /*
    修改数据库配置列表
     */
    @Update("<script>"+
            "UPDATE BI_DATABASES set "  +
            "<if test=\"name != null\">BASES_NAME = #{name, jdbcType=VARCHAR},</if>" +
            "<if test=\"addrass != null\"> ADDRASS = #{addrass, jdbcType=VARCHAR}, </if>" +
            "<if test=\"port != null\"> PORT = #{port, jdbcType=INTEGER}, </if>" +
            "<if test=\"databaseType != null\"> DATABASE_TYPE = #{databaseType, jdbcType=VARCHAR}, </if>" +
            "<if test=\"dataName != null\"> DATA_NAME = #{dataName, jdbcType=VARCHAR}, </if>" +
            "<if test=\"userName != null\"> USER_NAME = #{userName, jdbcType=VARCHAR}, </if>" +
            "<if test=\"passWord != null\"> PASS_WORD = #{passWord, jdbcType=VARCHAR}, </if>" +
            "NOTE = #{note, jdbcType=VARCHAR}" +
            "where id = #{id, jdbcType=INTEGER}" +
            "</script>")
    void updatabases(Databases databases);

    @Select("SELECT COUNT(*) FROM BI_DATA_CONNECTORS WHERE DB_CONFIG = #{id}")
    int countDataConnectorById(Long id);

    /*
    删除数据库配置
     */
    @Delete("DELETE FROM BI_DATABASES WHERE ID = #{id}")
    void deleteDatabases(Long id);

    /*
    查询数据源列数据
     */
    @Select("select columns_Config as columnConfig from bi_data_connectors " +
            " where id =#{id}")
    DataConnector getColumnData(int id);

    /*
    查询数据库配置
     */
    @Select("select db_config from bi_data_connectors where id = #{id}")
    String getDbConfig(int id);

    /*
    创建数据源分组
     */
    @Insert("insert into bi_base_group_by(bb_id, bb_group_name, bb_index, bb_father_id, create_date)" +
            "values(#{id}, #{groupName}, #{groupIndex}, #{fatherId}, to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'))")
    @SelectKey(before=true,keyProperty="id",resultType=int.class,statement="SELECT BI_BASE_GROUP_BY_SEQUENCE.nextval from dual",keyColumn = "bb_id")
    void setConnectorGroup(GroupInfo group);

    /*
    更新数据源分组
     */
    @Update("<script>"+
            "UPDATE bi_base_group_by set "+
            "bb_group_name = #{groupName}" +
            "<if test=\"groupIndex != null\"> , bb_index = #{groupIndex} </if>" +
            "<if test=\"fatherId != null\"> , bb_father_id = #{fatherId} </if>" +
            ",updata_date = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') "+
            " where bb_id = #{id}" +
            "</script>")
    void updataConnectorGroup(GroupInfo groupInfo);

    /**
     * 删除分组时,将数据源分组更新成默认值 -1
     * @param id  数据源分组ID
     */
    @Update("UPDATE BI_DATA_CONNECTORS SET BD_GROUP = -1 WHERE BD_GROUP = #{id,jdbcType=INTEGER}")
    void updateGroupIdToDefault(Long id);

    /**
     * 删除分组时,将数据源分组更新成默认值 -1
     * @param groupInfoList  数据源分组信息
     */
    @Update("<script>"
            + "UPDATE BI_DATA_CONNECTORS SET BD_GROUP = -1 WHERE BD_GROUP IN "
            + "<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" close=\")\" separator=\",\">"
            +   "#{item.id, jdbcType=INTEGER}"
            + "</foreach>"
            + "</script>"
           )
    void batchUpdateGroupIdToDefault(List<GroupInfo> groupInfoList);

    /*
    删除分组
     */
    @Delete("delete from bi_base_group_by where bb_id = #{id}")
    void delConnectorGroup(Long id);

    @Insert("<script>"
            + "INSERT INTO BI_BASE_GROUP_BY(BB_ID, BB_GROUP_NAME, BB_INDEX, BB_FATHER_ID, CREATE_DATE)"
            + "SELECT BI_BASE_GROUP_BY_SEQUENCE.NEXTVAL,M.* FROM ("
            + "<foreach collection=\"list\" item=\"item\" index=\"index\" separator=\"union all\">"
            +   "SELECT "
            +   "#{item.groupName, jdbcType=VARCHAR},"
            +   "#{item.groupIndex, jdbcType=INTEGER},"
            +   "#{item.fatherId, jdbcType=INTEGER},"
            +   "SYSDATE "
            +   "FROM DUAL"
            + "</foreach>"
            + ") M"
            + "</script>")
    void batchInsertDataConnector(@Param("list") List<GroupInfo> groupInfoList);

    @Update(" <script>"
            + "BEGIN"
            +   "<foreach collection=\"list\" item=\"item\" index=\"index\" separator=\";\">"
            +       "UPDATE BI_BASE_GROUP_BY "
            +       "<set>"
            +       "<if test=\"item.groupName != null\">"
            +           "BB_GROUP_NAME = #{item.groupName, jdbcType=VARCHAR},"
            +       "</if>"
            +       "<if test=\"item.groupIndex != null\">"
            +           "BB_INDEX = #{item.groupIndex, jdbcType=VARCHAR},"
            +       "</if>"
            +       "<if test=\"item.fatherId != null\">"
            +           "BB_FATHER_ID = #{item.fatherId, jdbcType=INTEGER},"
            +       "</if>"
            +       "UPDATA_DATE = SYSDATE"
            +       "</set>"
            +       "WHERE BB_ID = #{item.id, jdbcType=INTEGER}"
            +   "</foreach>"
            + ";END;"
            + "</script>")
    void batchUpdateDataConnector(@Param("list") List<GroupInfo> groupInfoList);

    @Delete(" <script>"
            + "DELETE FROM BI_BASE_GROUP_BY WHERE BB_ID IN"
            + "<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" close=\")\" separator=\",\">"
            +   "#{item.id, jdbcType=INTEGER}"
            + "</foreach>"
            + "</script>")
    void batchDeleteDataConnector(@Param("list") List<GroupInfo> groupInfoList);
    /*
    查询分组是否有子分组
     */
    @Select("select BB_GROUP_NAME from bi_base_group_by where bb_father_id = #{id}")
    List<String> getFatherId(int id);

    /*
    查询是否有数据源正在使用
     */
    @Select("select data_name from bi_data_connectors where bd_group = #{id}")
    List<String> getConName(int id);

    /*
    查询分组
     */
    @Select("select bb_id as id, bb_group_name as groupName, bb_index as groupIndex, bb_father_id as fatherId, create_by as createBy," +
            "create_date as createDate from bi_base_group_by")
    List<GroupInfo> getConnectorGroup();

    /**
     * 通过分组Id查询数据源分组
     * @param id  分组ID
     * @return
     */
    @Select("select bb_id as id, bb_group_name as groupName, bb_index as groupIndex, bb_father_id as fatherId, create_by as createBy," +
            "create_date as createDate from bi_base_group_by WHERE BB_ID = #{id}")
    GroupInfo getConnectorGroupById(int id);

    /*
   查询数据源是否关联图表
    */
    @Select("select chart_name from bi_charts where bd_data_ID = #{id}")
    List<String> getChartsName(int id);

    /*
    查询数据源创建人ID
     */
    @Select("select create_id from bi_data_connectors where id = #{id}")
    int getCreateIdById(@Param("id") int id);

    /*
    获取数据（根据权限）
     */
    @Select("${sql}")
    List<Map<String, Object>> getValues(@Param("sql") String sql, TestPage testPage);

    /*
    通过数据源ID取sql
     */
    @Select("select table_name from bi_data_connectors where id = #{id}")
    String getSqlByid(int id);

    @Select("SELECT * FROM (${table}) ${condition}")
    List<Map<String, Object>> getExportData(@Param("table") String table, @Param("condition") String condition);

}