package com.fly.test;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;

import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterUtils;
import org.springframework.test.context.junit4.SpringRunner;

import com.fly.RunApplication;
import com.fly.common.DataBaseInit;
import com.mool.xsqlbuilder.SafeSqlProcesser;
import com.mool.xsqlbuilder.SafeSqlProcesserFactory;
import com.mool.xsqlbuilder.XsqlBuilder;
import com.mool.xsqlbuilder.XsqlBuilder.XsqlFilterResult;

import lombok.extern.slf4j.Slf4j;

/**
 * XsqlBuilderTest
 * 
 * @author 00fly
 * @version [版本号, 2018年11月13日]
 * @see [相关类/方法]
 * @since [产品/模块版本]
 */
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(classes = RunApplication.class)
public class XsqlBuilderTest
{
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    private XsqlBuilder builder;
    
    @PostConstruct
    private void init()
    {
        // 开启Sql安全过滤
        SafeSqlProcesser safeSqlProcesser = SafeSqlProcesserFactory.getMysql();
        builder = new XsqlBuilder(safeSqlProcesser);
        log.info("XsqlBuilder = {}", builder);
    }
    
    /**
     * 执行数据库表初始化
     */
    @BeforeClass
    public static void initDB()
    {
        try
        {
            DataBaseInit.initUseSQL("/sql/init.sql");
        }
        catch (IOException e)
        {
            log.error(e.getMessage(), e);
        }
    }
    
    /**
     * DynSQL ---> SQL (不能带IN条件且参数不可出现多次,否则SQL非法,此处理方式不建议使用)
     */
    @Test
    @Deprecated
    public void testGenerateSql()
    {
        // 动态sql语句 /~ ~/为一个语法块,{key}替换为sql的?或是hql的:key标记 [key]将直接替换为key对应的value
        StringBuilder sql = new StringBuilder("select * from user where 1=1");
        sql.append("/~ and name like {name} ~/");
        // sql.append("/~ and name like {name} ~/"); 条件参数出现多次，会造成?数目大于paramArr长度，SQL非法
        sql.append("/~ and age > {age} ~/");
        
        // 参数
        Map<String, Object> filters = new LinkedHashMap<>();
        filters.put("name", "name%");
        filters.put("age", 18);
        
        // SQL语句
        XsqlFilterResult result = builder.generateSql(sql.toString(), filters);
        Object[] paramArr = result.getAcceptedFilters().values().toArray();
        List<Map<String, Object>> list = jdbcTemplate.queryForList(result.getXsql(), paramArr);
        log.info("★★★★ before: paramMap = {}", result.getAcceptedFilters());
        log.info("★★★★ execute: realSql  = {}", result.getXsql());
        log.info("★★★★ execute: paramArr = {}", Arrays.asList(paramArr));
        log.info("★★★★ execute: result   = {}", list);
    }
    
    /**
     * DynSQL ---> namedParamSQL ---> SQL
     */
    @Test
    public void testGenerateHql()
    {
        // 动态sql语句 /~ ~/为一个语法块,{key}替换为sql的?或是hql的:key标记 [key]将直接替换为key对应的value
        StringBuilder sql = new StringBuilder("select * from user where 1=1");
        sql.append("/~ and name like {name} ~/");
        sql.append("/~ and name like '[name]' ~/");
        sql.append("/~ and age > {age} ~/");
        sql.append("/~ and age > [age] ~/");
        
        // 参数
        Map<String, Object> filters = new LinkedHashMap<>();
        filters.put("name", "name%");
        filters.put("age", 18);
        
        // HQL语句
        XsqlFilterResult result = builder.generateHql(sql.toString(), filters);
        Map<String, Object> paramMap = result.getAcceptedFilters();
        String namedParamSQL = result.getXsql();
        String realSql = buildRealSQL(namedParamSQL, paramMap);
        Object[] paramArr = buildValueArray(namedParamSQL, paramMap);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(realSql, paramArr);
        
        log.info("★★★★ before: namedParamSQL  = {}", namedParamSQL);
        log.info("★★★★ before: paramMap = {}", paramMap);
        log.info("★★★★ execute: realSql  = {}", realSql);
        log.info("★★★★ execute: paramArr = {}", Arrays.asList(paramArr));
        log.info("★★★★ execute: result   = {}", list);
    }
    
    /**
     * DynSQL ---> namedParamSQL ---> SQL
     */
    @Test
    public void testGenerateHql2()
    {
        // 有IN条件
        StringBuilder sql = new StringBuilder("select id, name from student where 1=1");
        sql.append("/~ and id= {id} ~/");
        sql.append("/~ or id in ({ids}) ~/");
        Map<String, Object> filters = new LinkedHashMap<>();
        filters.put("id", 1);
        filters.put("ids", Arrays.asList(1, 2, 3, 4, 5));
        
        // HQL语句
        XsqlFilterResult result = builder.generateHql(sql.toString(), filters);
        Map<String, Object> paramMap = result.getAcceptedFilters();
        String namedParamSQL = result.getXsql();
        String realSql = buildRealSQL(namedParamSQL, paramMap);
        Object[] paramArr = buildValueArray(namedParamSQL, paramMap);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(realSql, paramArr);
        
        log.info("★★★★ before: namedParamSQL  = {}", namedParamSQL);
        log.info("★★★★ before: paramMap = {}", paramMap);
        log.info("★★★★ execute: realSql  = {}", realSql);
        log.info("★★★★ execute: paramArr = {}", Arrays.asList(paramArr));
        log.info("★★★★ execute: result   = {}", list);
    }
    
    /**
     * 将namedParamSQL转换为带?的传统SQL
     * 
     * @param namedParamSQL 命名参数SQL
     * @param paramMap Map参数
     * @return
     * @see [类、类#方法、类#成员]
     */
    private String buildRealSQL(String namedParamSQL, Map<String, Object> paramMap)
    {
        return NamedParameterUtils.substituteNamedParameters(NamedParameterUtils.parseSqlStatement(namedParamSQL), new MapSqlParameterSource(paramMap));
    }
    
    /**
     * 滤除无效参数列表后，以Object[]返回
     * 
     * @param namedParamSQL 命名参数SQL
     * @param paramMap Map参数
     * @return
     * @see [类、类#方法、类#成员]
     */
    private Object[] buildValueArray(String namedParamSQL, Map<String, Object> paramMap)
    {
        Object[] params = NamedParameterUtils.buildValueArray(namedParamSQL, paramMap);
        List<Object> paramList = new ArrayList<>();
        for (Object obj : params)
        {
            if (List.class.isInstance(obj))
            {
                paramList.addAll((List<?>)obj);
            }
            else
            {
                paramList.add(obj);
            }
        }
        return paramList.toArray();
    }
}