package com.lemon.cases;

import com.alibaba.fastjson.JSONPath;
import com.lemon.constants.Constants;
import com.lemon.pojo.API;
import com.lemon.pojo.Case;
import com.lemon.utils.AuthorizationUtils;
import com.lemon.utils.ExcelUtils;
import com.lemon.utils.SqlUtils;
import io.qameta.allure.Description;
import io.qameta.allure.Step;
import org.apache.commons.lang3.StringUtils;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;
import org.testng.Assert;

import java.util.Date;
import java.math.BigDecimal;
import java.text.DecimalFormat;

public class ServerCase extends BaseCase {
    @Test(dataProvider = "data1", description = "1A：A段和B段sql完全匹配")   // DTO数据传输
    @Description("1A：A段和B段sql完全匹配")
    public void testManager1A(API api, Case cas) throws Exception {
        //0、执行前休眠30秒
        Thread.sleep(1000 * 10);
        //1、参数化替换
        String params = replace(cas.getParams());
        String sql = replace(cas.getCheckSQL());
        String Bsql = replace(cas.getCheckBSQL());
        cas.setParams(params);
        cas.setCheckSQL(sql);
        cas.setCheckBSQL(Bsql);
        //2、数据库前置查询结果（断言必须在接口执行前后都查询）
        Object beforeASqlResult = SqlUtils.querySingle(cas.getCheckSQL());
        Object beforeBSqlResult = SqlUtils.querySingle(cas.getCheckBSQL());
        //3、调用接口
        String body = call(api, cas, false);
        // 3.1 将登录的token存储（仅限于登录接口特有）
        AuthorizationUtils.storeToken(body);
//        System.out.println(AuthorizationUtils.env);
        //4、断言响应结果，Excel中预期响应数据与实际响应数据进行对比
        boolean assertResponseFlag = assertResponse(cas, body);
        //5、添加接口响应回写excel内容
        addWBD(Integer.parseInt(cas.getId()), Constants.ACTURL_WRITER_BACK_CELL_NUM, body);
        //6、数据库后置查询结果
        System.out.println("A-sql: " + cas.getCheckSQL());
        Object afterASqlResult = SqlUtils.querySingle(cas.getCheckSQL());
        System.out.println("B-sql: " + cas.getCheckBSQL());
        Thread.sleep(1000 * 30);  //验证B段SQL的休眠时间60秒
        Object afterBSqlResult = SqlUtils.querySingle(cas.getCheckBSQL());

        //7-1、A段数据库断言
        boolean sqlAFlag = assertSql(cas.getaExpectValue(), beforeASqlResult == null ? null : beforeASqlResult.toString(), afterASqlResult == null ? null : afterASqlResult.toString());
        String assertSqlAFlag = (sqlAFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.A_ASSERT_RESULT_CELL_NUM, assertSqlAFlag);
        System.out.println("数据库A-sql断言结果：" + sqlAFlag);
        //7-2、B段数据库断言
        boolean sqlBFlag = assertSql(cas.getbExpectValue(), beforeBSqlResult == null ? null : beforeBSqlResult.toString(), afterBSqlResult == null ? null : afterBSqlResult.toString());
        String assertSqlBFlag = (sqlBFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.B_ASSERT_RESULT_CELL_NUM, assertSqlBFlag);
        System.out.println("数据库B-sql断言结果：" + sqlBFlag);
        //8、添加断言回写内容
        String assertContent = (assertResponseFlag && sqlAFlag && sqlBFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.ASSERT_RESULT_CELL_NUM, assertContent);
        Assert.assertEquals(assertContent, "Pass");
    }

    @DataProvider(name = "data1")
    public Object[][] data1() {
        Object[][] data = ExcelUtils.getAPIAndCaseByApiId("1");  // 传参apiID
        return data;
    }

    @Test(dataProvider = "data2", description = "2B：B段sql部分匹配")
    @Description("2B：B段sql部分匹配")
    public void testManager2B(API api, Case cas) throws Exception {
        Thread.sleep(1000 * 10);
        String params = replace(cas.getParams());
        String Bsql = replace(cas.getCheckBSQL());
        cas.setParams(params);
        cas.setCheckBSQL(Bsql);
        Object beforeBSqlResult = SqlUtils.querySingle(cas.getCheckBSQL());
        String body = call(api, cas, false);
        AuthorizationUtils.storeToken(body);
//        System.out.println(AuthorizationUtils.env);
        boolean assertResponseFlag = assertResponse(cas, body);
        addWBD(Integer.parseInt(cas.getId()), Constants.ACTURL_WRITER_BACK_CELL_NUM, body);
        System.out.println("B-sql: " + cas.getCheckBSQL());
        Thread.sleep(1000 * 30);  //验证B段SQL的休眠时间60秒
        Object afterBSqlResult = SqlUtils.querySingle(cas.getCheckBSQL());
        //7-2、B段数据库断言
        boolean sqlBFlag = assertSqlB(beforeBSqlResult == null ? null : beforeBSqlResult.toString(), afterBSqlResult == null ? null : afterBSqlResult.toString());
        String assertSqlBFlag = (sqlBFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.B_ASSERT_RESULT_CELL_NUM, assertSqlBFlag);
        System.out.println("数据库B-sql断言结果：" + sqlBFlag);
        //8、添加断言回写内容
        String assertContent = (assertResponseFlag && sqlBFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.ASSERT_RESULT_CELL_NUM, assertContent);
        Assert.assertEquals(assertContent, "Pass");
    }

    // 使用Excel文件传输接口信息和请求信息
    @DataProvider(name = "data2")
    public Object[][] data2() {
        Object[][] data = ExcelUtils.getAPIAndCaseByApiId("2");  // 传参apiID
        return data;
    }

    @Test(dataProvider = "data3", description = "3C：A段sql完全匹配")   // DTO数据传输
    @Description("3C：A段sql完全匹配")
    public void testManager3C(API api, Case cas) throws Exception {
        Thread.sleep(1000 * 10);
        String params = replace(cas.getParams());
        String sql = replace(cas.getCheckSQL());
        cas.setParams(params);
        cas.setCheckBSQL(sql);
        Object beforeASqlResult = SqlUtils.querySingle(cas.getCheckSQL());
        String body = call(api, cas, false);
        AuthorizationUtils.storeToken(body);
        boolean assertResponseFlag = assertResponse(cas, body);
        addWBD(Integer.parseInt(cas.getId()), Constants.ACTURL_WRITER_BACK_CELL_NUM, body);
        System.out.println("a-sql: " + cas.getCheckSQL());
        Thread.sleep(1000 * 30);  //A段SQL的休眠时间60秒
        Object afterASqlResult = SqlUtils.querySingle(cas.getCheckSQL());
        boolean sqlAFlag = assertSql(cas.getaExpectValue(), beforeASqlResult == null ? null : beforeASqlResult.toString(), afterASqlResult == null ? null : afterASqlResult.toString());
        String assertSqlAFlag = (sqlAFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.A_ASSERT_RESULT_CELL_NUM, assertSqlAFlag);
        System.out.println("数据库A-sql断言结果：" + sqlAFlag);
        String assertContent = (assertResponseFlag && sqlAFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.ASSERT_RESULT_CELL_NUM, assertContent);
        Assert.assertEquals(assertContent, "Pass");
    }

    @DataProvider(name = "data3")
    public Object[][] data3() {
        Object[][] data = ExcelUtils.getAPIAndCaseByApiId("3");  // 传参apiID
        return data;
    }


    @Test(dataProvider = "data4", description = "4D：A段sql完全匹配，B段sql部分匹配")   // DTO数据传输
    @Description("4D：A段sql完全匹配，B段sql部分匹配")
    public void testManager4D(API api, Case cas) throws Exception {
        //0、执行前休眠30秒
        //1、参数化替换
        String params = replace(cas.getParams());
        String sql = replace(cas.getCheckSQL());
        String Bsql = replace(cas.getCheckBSQL());
        cas.setParams(params);
        cas.setCheckSQL(sql);
        cas.setCheckBSQL(Bsql);
        //2、数据库前置查询结果（断言必须在接口执行前后都查询）
        Thread.sleep(1000 * 30);
        Object beforeASqlResult = SqlUtils.querySingle(cas.getCheckSQL());
        Thread.sleep(1000 * 30);
        Object beforeBSqlResult = SqlUtils.querySingle(cas.getCheckBSQL());
        //3、调用接口
        String body = call(api, cas, false);
        // 3.1 将登录的token存储（仅限于登录接口特有）
        AuthorizationUtils.storeToken(body);
//        System.out.println(AuthorizationUtils.env);
        //4、断言响应结果，Excel中预期响应数据与实际响应数据进行对比
        boolean assertResponseFlag = assertResponse(cas, body);
        //5、添加接口响应回写excel内容
        addWBD(Integer.parseInt(cas.getId()), Constants.ACTURL_WRITER_BACK_CELL_NUM, body);
        //6、数据库后置查询结果
        System.out.println("A-sql: " + cas.getCheckSQL());
        Thread.sleep(1000 * 30);
        Object afterASqlResult = SqlUtils.querySingle(cas.getCheckSQL());
        System.out.println("B-sql: " + cas.getCheckBSQL());
        Thread.sleep(1000 * 30);  //验证B段SQL的休眠时间60秒
        Object afterBSqlResult = SqlUtils.querySingle(cas.getCheckBSQL());
        //7-1、A段数据库断言
        boolean sqlAFlag = assertSql(cas.getaExpectValue(), beforeASqlResult == null ? null : beforeASqlResult.toString(), afterASqlResult == null ? null : afterASqlResult.toString());
        String assertSqlAFlag = (sqlAFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.A_ASSERT_RESULT_CELL_NUM, assertSqlAFlag);
        System.out.println("数据库A-sql断言结果：" + sqlAFlag);
        //7-2、B段数据库断言
        boolean sqlBFlag = assertSqlB(beforeBSqlResult == null ? null : beforeBSqlResult.toString(), afterBSqlResult == null ? null : afterBSqlResult.toString());
        String assertSqlBFlag = (sqlBFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.B_ASSERT_RESULT_CELL_NUM, assertSqlBFlag);
        System.out.println("数据库B-sql断言结果：" + sqlBFlag);
        //8、添加断言回写内容
        String assertContent = (assertResponseFlag && sqlAFlag && sqlBFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.ASSERT_RESULT_CELL_NUM, assertContent);
        Assert.assertEquals(assertContent, "Pass");
    }

    // 使用Excel文件传输接口信息和请求信息
    @DataProvider(name = "data4")
    public Object[][] data4() {
        Object[][] data = ExcelUtils.getAPIAndCaseByApiId("4");  // 传参apiID
        return data;
    }


    @Test(dataProvider = "data5", description = "5E：A段sql完全匹配，B段sql部分匹配，适用于作品应用")
    @Description("5E：A段sql完全匹配，B段sql部分匹配，适用于作品应用")
    public void testManager5E(API api, Case cas) throws Exception {
        //1、参数化替换
        String params = replace(cas.getParams());
        String sql = replace(cas.getCheckSQL());
        String Bsql = replace(cas.getCheckBSQL());
        cas.setParams(params);
        cas.setCheckSQL(sql);
        cas.setCheckBSQL(Bsql);
        //2、数据库前置查询结果（断言必须在接口执行前后都查询）
        Object beforeASqlResult = SqlUtils.querySingle(cas.getCheckSQL());
        Object beforeBSqlResult = SqlUtils.querySingle(cas.getCheckBSQL());
        //3、调用接口
        String body = call(api, cas, false);
        // 3.1 将登录的token存储（仅限于登录接口特有）
        AuthorizationUtils.storeToken(body);
//        System.out.println(AuthorizationUtils.env);
        //4、断言响应结果，Excel中预期响应数据与实际响应数据进行对比
        boolean assertResponseFlag = assertResponse(cas, body);
        //5、添加接口响应回写excel内容
        addWBD(Integer.parseInt(cas.getId()), Constants.ACTURL_WRITER_BACK_CELL_NUM, body);
        //6、数据库后置查询结果
        System.out.println("A-sql: " + cas.getCheckSQL());
        Thread.sleep(1000 * 30);
        Object afterASqlResult = SqlUtils.querySingle(cas.getCheckSQL());
        System.out.println("B-sql: " + cas.getCheckBSQL());
        Thread.sleep(1000 * 30);  //验证B段SQL的休眠时间60秒
        Object afterBSqlResult = SqlUtils.querySingle(cas.getCheckBSQL());
        //7-1、A段数据库断言
        boolean sqlAFlag = assertSql(cas.getaExpectValue(), beforeASqlResult == null ? null : beforeASqlResult.toString(), afterASqlResult == null ? null : afterASqlResult.toString());
        String assertSqlAFlag = (sqlAFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.A_ASSERT_RESULT_CELL_NUM, assertSqlAFlag);
        System.out.println("数据库A-sql断言结果：" + sqlAFlag);
        //7-2、B段数据库断言
        boolean sqlBFlag = assertSqlB(beforeBSqlResult == null ? null : beforeBSqlResult.toString(), afterBSqlResult == null ? null : afterBSqlResult.toString());
        String assertSqlBFlag = (sqlBFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.B_ASSERT_RESULT_CELL_NUM, assertSqlBFlag);
        System.out.println("数据库B-sql断言结果：" + sqlBFlag);
        //8、添加断言回写内容
        String assertContent = (assertResponseFlag && sqlAFlag && sqlBFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.ASSERT_RESULT_CELL_NUM, assertContent);
        Assert.assertEquals(assertContent, "Pass");
    }

    @DataProvider(name = "data5")
    public Object[][] data5() {
        Object[][] data = ExcelUtils.getAPIAndCaseByApiId("5");
        return data;
    }

    @Test(dataProvider = "data6", description = "6F：B段sql完全匹配")   // DTO数据传输
    @Description("6F：B段sql完全匹配")
    public void testManager6F(API api, Case cas) throws Exception {
        //0、执行前休眠30秒
        Thread.sleep(1000 * 10);
        //1、参数化替换
        String params = replace(cas.getParams());
        String sql = replace(cas.getCheckSQL());
        String Bsql = replace(cas.getCheckBSQL());
        cas.setParams(params);
        cas.setCheckSQL(sql);
        cas.setCheckBSQL(Bsql);
        //2、数据库前置查询结果（断言必须在接口执行前后都查询）
        Object beforeASqlResult = SqlUtils.querySingle(cas.getCheckSQL());
        Object beforeBSqlResult = SqlUtils.querySingle(cas.getCheckBSQL());
        //3、调用接口
        String body = call(api, cas, false);
        // 3.1 将登录的token存储（仅限于登录接口特有）
        AuthorizationUtils.storeToken(body);
//        System.out.println(AuthorizationUtils.env);
        //4、断言响应结果，Excel中预期响应数据与实际响应数据进行对比
        boolean assertResponseFlag = assertResponse(cas, body);
        //5、添加接口响应回写excel内容
        addWBD(Integer.parseInt(cas.getId()), Constants.ACTURL_WRITER_BACK_CELL_NUM, body);
        //6、数据库后置查询结果
//        System.out.println("A-sql: " + cas.getCheckSQL());
//        Object afterASqlResult = SqlUtils.querySingle(cas.getCheckSQL());
        System.out.println("B-sql: " + cas.getCheckBSQL());
        Thread.sleep(1000 * 30);  //验证B段SQL的休眠时间60秒
        Object afterBSqlResult = SqlUtils.querySingle(cas.getCheckBSQL());

        //7-2、B段数据库断言
        boolean sqlBFlag = assertSql(cas.getbExpectValue(), beforeBSqlResult == null ? null : beforeBSqlResult.toString(), afterBSqlResult == null ? null : afterBSqlResult.toString());
        String assertSqlBFlag = (sqlBFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.B_ASSERT_RESULT_CELL_NUM, assertSqlBFlag);
        System.out.println("数据库B-sql断言结果：" + sqlBFlag);
        //8、添加断言回写内容
        String assertContent = (assertResponseFlag && sqlBFlag) ? "Pass" : "Fail";
        addWBD(Integer.parseInt(cas.getId()), Constants.ASSERT_RESULT_CELL_NUM, assertContent);
        Assert.assertEquals(assertContent, "Pass");
    }

    @DataProvider(name = "data6")
    public Object[][] data6() {
        Object[][] data = ExcelUtils.getAPIAndCaseByApiId("6");  // 传参apiID
        return data;
    }

}
