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

import com.usoftchina.bi.core.base.TestPage;
import com.usoftchina.bi.server.model.bo.ChartsColumnConfig;
import com.usoftchina.bi.server.model.bo.ColumnConfigAndSql;
import com.usoftchina.bi.server.model.po.ChartConfig;
import com.usoftchina.bi.server.model.po.ChartConfigList;
import com.usoftchina.bi.server.model.po.ChartsConfigToDash;
import com.usoftchina.bi.server.model.vo.configVo.GroupInfo;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

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

@Mapper
@Repository
@CacheNamespace
public interface ChartsConfigMapper {

    @Insert("insert into bi_charts(id,CHART_NAME,CHART_TYPE,BD_DATA_ID,CHART_CONFIG,GROUP_BY,ACCESS_AUTHORITY,UPDATE_AUTHORITY,BC_CHARTS_GROUP,CHART_DESCRIBES,BC_CHART_STYLE," +
            "create_by,create_date, bc_charts_option, create_id, BC_FETCHCONFIG, from_id, from_data_id) " +
            "VALUES (#{chartId,jdbcType=INTEGER},#{chartName,jdbcType=VARCHAR},#{chartType,jdbcType=VARCHAR}, #{dataId,jdbcType=INTEGER}, #{chartConfig,jdbcType=VARCHAR}, #{groupBy,jdbcType=VARCHAR}, #{accessAuthority,jdbcType=VARCHAR}, #{updateAuthority,jdbcType=VARCHAR}, #{chartsGroup,jdbcType=INTEGER}," +
            "#{describes,jdbcType=VARCHAR},#{style,jdbcType=VARCHAR}, #{createBy,jdbcType=VARCHAR},to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'),  #{chartOption,jdbcType=VARCHAR}, #{createId,jdbcType=INTEGER}, #{fetchConfig,jdbcType=VARCHAR},#{from_id,jdbcType=INTEGER},#{from_data_id,jdbcType=INTEGER})" )
//    @Options(useGeneratedKeys=false, keyProperty = "chartId",keyColumn = "id")
    @SelectKey(before=true,keyProperty="chartId",resultType=Integer.class,statement="SELECT bi_charts_sequence.nextval from dual",keyColumn = "id")
    void insertCharts(ChartConfig chartConfig);

    @Select("SELECT COUNT(*) FROM BI_DASHBOARDS_CHART WHERE CHART_ID = #{id}")
    int countDashboardsById(Long id);

    @Delete("delete from bi_charts where id = #{id}")
    void deleteCharts(Long id);

    @Select("select bc.id as chartId, chart_name as chartName, BC_FETCHCONFIG as fetchConfig, chart_type as chartType, bc.create_by as createBy, bc.create_date as createDate, bc_charts_group as chartsGroup " +
            ",bc.bc_filters as filters, bc.BD_DATA_ID as dataId, bc_table_name as tableName, CHART_CONFIG as chartConfig, bc.GROUP_BY as groupBy, bc_charts_option as chartOption, " +
            "bc.ACCESS_AUTHORITY as accessAuthority, UPDATE_AUTHORITY as updateAuthority, CHART_DESCRIBES as describes, BC_CHART_STYLE as style, BC_FILTERS as filters " +
            ",bc.create_id as createId, bdc.DATA_NAME as dataName, bc.demo, BD.BASES_NAME as dataConnectionName, BD.ID as dataConnectionId  from bi_charts bc left join BI_DATA_CONNECTORS bdc on bc.BD_DATA_ID = bdc.id left join BI_DATABASES bd on BD.ID = BDC.DB_CONFIG " +
            "where bc.id = #{id, jdbcType=INTEGER}")
    ChartConfig getOneChart(int id);

    @Select("select DATA_NAME as name from BI_DATA_CONNECTORS where id = #{id}")
    String getDataNameById(int id);

    @Select("select distinct BD_DATA_ID from BI_CHARTS where FROM_DATA_ID = #{bd_data_id}")
    Integer getDataConnectId(@Param("bd_data_id") int id);

    /*
    查看图表列表
     */
    @Select("select '1' as authority,case when bc.BD_DATA_ID not in(select id from bi_data_connectors) then '1' else '0' end dbStatus, bc.id as chartId, bc.chart_name as chartName, bc.chart_type as chartType, bc.create_by as createBy, bc.create_date as createDate"
            + ",bc.bc_filters as filters, bc.CHART_DESCRIBES as describes, bc.bc_charts_group as chartsGroup, bc.bc_charts_option as chartOption ,bc.bd_data_id as dataId,bd.data_name as dataName, bc.create_id as createId, bc.demo, bds.BASES_NAME as dataConnectionName, bds.id as dataConnectionId "
            + "from bi_charts bc left join bi_data_connectors bd on bc.bd_data_id=bd.id left join BI_DATABASES bds on bds.id = BD.DB_CONFIG")
    List<ChartConfigList> getListCharts();

    /*
    权限查看图表列表
     */
    @Select("  select case " +
            "    when bc.CREATE_ID = #{id} and bc.BD_DATA_ID in (select id from bi_data_connectors where create_id=#{id}) then '1'" +
            "    when bc.CREATE_ID = #{id} and bc.BD_DATA_ID not in (select id from bi_data_connectors where create_id=#{id}) and (" +
            "      select count(1) from BI_DB_STRATEGYS bs1 " +
            "        where bs1.IS_OPEN='1' and bs1.BS_ID in (" +
            "          select bo1.BO_ST_ID from bi_db_object bo1 " +
            "            where bo1.BO_TYPE = '0' and bo1.BO_OB_ID in ( select BR_USER_GROUP from BI_USER_REL_GROUPS where BR_USER_ID=#{id} ) " +
            "            or bo1.bo_type = '1' and bo1.bo_ob_id = #{id}" +
            "        ) and bs1.BS_DB_ID = bc.BD_DATA_ID" +
            "    ) > 0 then '1'" +
            "    when bc.CREATE_ID = #{id} and bc.BD_DATA_ID not in (select id from bi_data_connectors where create_id=#{id}) and (" +
            "      select count(1) from BI_DB_STRATEGYS bs2 " +
            "        where bs2.IS_OPEN='1' and bs2.BS_ID in (" +
            "          select bo2.BO_ST_ID from bi_db_object bo2 " +
            "            where bo2.BO_TYPE = '0' and bo2.BO_OB_ID in ( select BR_USER_GROUP from BI_USER_REL_GROUPS where BR_USER_ID=#{id} ) " +
            "            or bo2.bo_type = '1' and bo2.bo_ob_id = #{id}" +
            "        ) and bs2.BS_DB_ID = bc.BD_DATA_ID" +
            "    ) = 0 then '0' " +
            "    when bc.CREATE_ID != #{id} and bc.BD_DATA_ID in (select bdc1.id " +
            " from bi_data_connectors bdc1 where bdc1.create_id=bc.CREATE_ID) then '1'" +

            "    when bc.CREATE_ID != #{id} and bc.BD_DATA_ID not in (select bdc2.id " +
            "" +
            " from bi_data_connectors bdc2 where bdc2.create_id=bc.CREATE_ID) and (" +
            "      select count(1) from BI_DB_STRATEGYS bs2" +
            "        where bs2.IS_OPEN='1' and bs2.BS_ID in (" +
            "          select bo2.BO_ST_ID from bi_db_object bo2 " +
            "            where bo2.BO_TYPE = '0' and bo2.BO_OB_ID in ( select BR_USER_GROUP from BI_USER_REL_GROUPS where BR_USER_ID=bc.CREATE_ID ) " +
            "            or bo2.bo_type = '1' and bo2.bo_ob_id = bc.CREATE_ID" +
            "        ) and bs2.BS_DB_ID = bc.BD_DATA_ID" +
            "    ) > 0 then '1'" +
            "    when bc.CREATE_ID != #{id} and bc.BD_DATA_ID not in (select bdc3.id " +
            " from bi_data_connectors bdc3 where bdc3.create_id=bc.CREATE_ID) and (" +
            "      select count(1) from BI_DB_STRATEGYS bs2" +
            "        where bs2.IS_OPEN='1' and bs2.BS_ID in (" +
            "          select bo2.BO_ST_ID from bi_db_object bo2 " +
            "            where bo2.BO_TYPE = '0' and bo2.BO_OB_ID in ( select BR_USER_GROUP from BI_USER_REL_GROUPS where BR_USER_ID=bc.CREATE_ID ) " +
            "            or bo2.bo_type = '1' and bo2.bo_ob_id = bc.CREATE_ID" +
            "        ) and bs2.BS_DB_ID = bc.BD_DATA_ID" +
            "    ) = 0 then '0'" +
            "    else null end authority, case when bc.BD_DATA_ID not in(select id from bi_data_connectors) then '1' else '0' end dbStatus, bc.id as chartId, bc.chart_name as chartName, bc.chart_type as chartType, bc.create_by as createBy, bc.create_date as createDate" +
            "    ,bc.bc_filters as filters, bc.CHART_DESCRIBES as describes, bc.bc_charts_group as chartsGroup, bc.bc_charts_option as chartOption ,bc.bd_data_id as dataId,bd.data_name as dataName, bc.create_id as createId, bc.demo, bds.BASES_NAME as dataConnectionName, bds.id as dataConnectionId " +
            "     from bi_charts bc left join bi_data_connectors bd on bc.bd_data_id=bd.id left join BI_DATABASES bds on bds.id = BD.DB_CONFIG where (bc.create_id = #{id} or bc.id in (" +
            "      select bc_chart_id from bi_chart_strategys where BC_ID in (" +
            "        select BO_ST_ID from BI_CHART_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' " +
            "    ))")
    List<ChartConfigList> getListByUser(@Param("id") int id, TestPage testPage);

    /*
    通过Id查找表名
    */
    @Select("select dc.table_name from bi_data_connectors dc left join bi_charts c on dc.id =c.bd_data_id where c.id=#{chartId}")
    String getTableName(@Param("chartId") int id);


    /*
    通过ID查找数据源配置
     */
    @Select("select dc.db_config from bi_data_connectors dc left join bi_charts c on dc.id =c.bd_data_id where c.id=#{chartId}")
    String getBaseConifg(@Param("chartId") int id);

    /*
    通过ID查找数据源配置
     */
    @Select("select bd_data_id from bi_charts where id=#{chartId}")
    int getBaseId(@Param("chartId") int id);

    /*
    通过ID查找数据库sql
     */
    @Select("select table_name from bi_data_connectors where id = #{id}")
    String getTableNameByBase(@Param("id") int id);

    /*
    更新图表配置
     */
    @Update("<script>" +
            "UPDATE bi_charts set "  +
            "CHART_NAME = #{chartName}" +
            "<if test=\"chartType != null\"> , CHART_TYPE = #{chartType} </if>" +
            "<if test=\"createBy != null\"> , CREATE_BY = #{createBy} </if>" +
            "<if test=\"groupBy != null\"> , GROUP_BY = #{groupBy} </if>" +
            "<if test=\"chartConfig != null\"> , CHART_CONFIG = #{chartConfig} </if>" +
            "<if test=\"describes != null\"> , CHART_DESCRIBES = #{describes} </if>" +
            "<if test=\"filters != null\"> , BC_FILTERS = #{filters} </if>" +
            "<if test=\"accessAuthority != null\"> , ACCESS_AUTHORITY = #{accessAuthority} </if>" +
            "<if test=\"updateAuthority != null\"> , UPDATE_AUTHORITY = #{updateAuthority} </if>" +
            "<if test=\"style != null\"> , BC_CHART_STYLE = #{style} </if>" +
            "<if test=\"chartsGroup !=null\">, BC_CHARTS_GROUP = #{chartsGroup}</if>"+
            "<if test=\"chartOption !=null\">, bc_charts_option = #{chartOption}</if>"+
            "<if test=\"fetchConfig !=null\">, BC_FETCHCONFIG = #{fetchConfig}</if>"+
            "<if test=\"updateDate != null\"> , UPDATE_DATE = to_date(#{updateDate},'YYYY-MM-DD hh24:mi:ss') </if>" +
            "where id = #{chartId}" +
            "</script>")
    void updateChartConfig(ChartConfig chartConfig);

    /*
    移交图表
     */
    @Update("update bi_charts set create_by = #{name}, create_id = #{createId} where id = #{baseId}")
    void changeChartOrder(@Param("name") String name, @Param("createId") int userId, @Param("baseId") int baseId);

    /*
    更新FetchConfig
     */
    @Update("update bi_charts set BC_FETCHCONFIG = #{fetch} where id = #{id}")
    void updateChartFetchConfig(@Param("fetch") String fetch, @Param("id") int id);

    /*
    更新图表分组
     */
    @Update("update bi_charts set BC_CHARTS_GROUP = #{chartsGroup} where id = #{chartId}")
    void updateConfigGroup(@Param("chartId") int chartId, @Param("chartsGroup") int chartsGroup);

    /*
   创建图表分组
    */
    @Insert("insert into bi_charts_group(bc_id, bc_group_name, bc_index, bc_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_CHARTS_GROUP_SEQUENCE.nextval from dual",keyColumn = "bc_id")
    void setChartsGroup(GroupInfo group);

    /*
    更新图表分组
     */
    @Update("<script>"+
            "UPDATE bi_charts_group set "+
            "bc_group_name = #{groupName}" +
            "<if test=\"groupIndex != null\"> , bc_index = #{groupIndex} </if>" +
            "<if test=\"fatherId != null\"> , bc_father_id = #{fatherId} </if>" +
            "<if test=\"createBy != null\"> , create_by = #{createBy} </if>" +
            "，updata_date = to_date(to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') "+
            " where bc_id = #{id}" +
            "</script>")
    void updataChartsGroup(GroupInfo groupInfo);

    /*
    删除分组
     */
    @Delete("<script>" +
            "delete from bi_charts_group where bc_id in" +
            "(" +
            "<foreach collection=\"list\" index=\"index\" item=\"item\"  separator=\",\">" +
            "#{item, jdbcType = NUMERIC}" +
            " </foreach>" +
            ")" +
            "</script>")
    void delChartsGroup(List<Integer> isList);

    @Insert("<script>"
            + "INSERT INTO BI_CHARTS_GROUP(BC_ID, BC_GROUP_NAME, BC_INDEX, BC_FATHER_ID, CREATE_DATE) "
            + "SELECT BI_CHARTS_GROUP_SEQ.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 batchInsertCharts(@Param("list") List<GroupInfo> groupInfoList);

    @Update("<script>"
            + "BEGIN"
            +   "<foreach collection=\"list\" item=\"item\" index=\"index\" separator=\";\">"
            +       "UPDATE BI_CHARTS_GROUP"
            +       "<set>"
            +       "<if test=\"item.groupName != null\">"
            +           "BC_GROUP_NAME = #{item.groupName, jdbcType=VARCHAR},"
            +       "</if>"
            +       "<if test=\"item.groupIndex != null\">"
            +           "BC_INDEX = #{item.groupIndex, jdbcType=VARCHAR},"
            +       "</if>"
            +       "<if test=\"item.fatherId != null\">"
            +           "BC_FATHER_ID = #{item.fatherId, jdbcType=INTEGER},"
            +       "</if>"
            +       "UPDATA_DATE = SYSDATE"
            +       "</set>"
            +       "WHERE BC_ID = #{item.id, jdbcType=INTEGER}"
            +   "</foreach>"
            + ";END;"
            +"</script>")
    void batchUpdateCharts(@Param("list") List<GroupInfo> groupInfoList);

    @Delete(" <script>"
            + "DELETE FROM BI_CHARTS_GROUP WHERE BC_ID IN"
            + "<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" close=\")\" separator=\",\">"
            +   "#{item.id, jdbcType=INTEGER}"
            + "</foreach>"
            + "</script>")
    void batchDeleteCharts(@Param("list") List<GroupInfo> groupInfoList);

    /**
     * 更新图表分组为默认分组-1
     * @param groupInfoList
     */
    @Update("<script>"
            + "UPDATE BI_CHARTS SET BC_CHARTS_GROUP = -1 WHERE BC_CHARTS_GROUP IN "
            + "<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" close=\")\" separator=\",\">"
            +   "#{item.id, jdbcType=INTEGER}"
            + "</foreach>"
            + "</script>")
    void batchUpdateGroupToDefault(@Param("list") List<GroupInfo> groupInfoList);

    /*
    查询分组
     */
    @Select("select bc_id as id, bc_group_name as groupName, bc_index as groupIndex, bc_father_id as fatherId, create_by as createBy," +
            "create_date as createDate from bi_charts_group")
    List<GroupInfo> getChartsGroup();

    /*
    查询分组是否有子分组
     */
    @Select("select BC_GROUP_NAME from bi_charts_group where bc_father_id = #{id}")
    List<String> getChartFatherId(int id);

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

    /*
    查询图表创建人ID
     */
    @Select("select create_id from bi_charts where id = #{id}")
    int getCreateId(@Param("id") int id);

    /*
    通过图表id查询数据源创建人ID
     */
    @Select("select bd.create_id from bi_charts bc,bi_data_connectors bd where bc.bd_data_id = bd.id and bc.id = #{id}")
    int getCreateIdBychart(int id);

    /*
    通过图表查询数据源ID
     */
    @Select("select bd_data_id from bi_charts where id = #{id}")
    int getBaseIdByChart(int id);

    /*
    通过用户Id查询数据源ID
     */
    @Select("select bs_db_id from bi_db_strategys where bs_id in (select bo_st_id from " +
            "bi_db_object where (bo_ob_id = #{id} and bo_type = '1') or (bo_ob_id = '1' and 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'")
    int getBaseIdByUser(int id);

    @Select("select table_name as sql, columns_config as columnConfig from bi_data_connectors where id = #{id}")
    ColumnConfigAndSql getConfigAndSql(int id);

    /*
    图表ID查询列配置
     */
    @Select("select columns_config from bi_data_connectors bc, bi_charts bch where bc.id = bch.bd_data_id and bch.id = #{id} ")
    String getConfigByChartId(int id);

    /*
    查询图表数据列信息
     */
    @Select("${sql}")
    List<Map<String, Object>> getChartsData(@Param("sql") String sql, TestPage testPage);

    /*
    通过图表ID获取图表配置
     */
    @Select("select id, chart_type as chartType, BC_FETCHCONFIG as fetchConfig,BC_CHART_STYLE as styleConfig from bi_charts where id = #{id}")
    ChartsConfigToDash getChartConfigToDash(int id);

    /*
    通过图表ID查询图表列配置和style
     */
    @Select("select bc.id as id, bc.chart_config as chartConfig, bc.BC_CHART_STYLE as chartStyle,bc.id, bc.CHART_NAME as chartName," +
            "bc.chart_type as chartType, bc.BD_DATA_ID as dataId, bc.create_id as createId, bc.CREATE_BY as createBy," +
            "bd.data_name as dataName " +
            "from bi_charts bc left join bi_data_connectors bd on bc.bd_data_id = bd.id where bc.id = #{id}")
    ChartsColumnConfig getChartsColumn(int id);

    /*
    通过图表ID查询图表列配置和style
     */
    @Select("select ID,BD_DATA_ID from BI_CHARTS where FROM_ID = #{bc_id} and FROM_DATA_ID = #{bd_data_id}")
    List<Map<String, Object>> checkOldChart(@Param("bc_id") int bc_id,@Param("bd_data_id") int bd_data_id);
}
