package test

import (
	"fmt"
	"github.com/pingcap/enterprise-toolkit/tispector/config"
	"github.com/pingcap/enterprise-toolkit/tispector/controller"
	"github.com/pingcap/enterprise-toolkit/tispector/dao"
	"github.com/pingcap/enterprise-toolkit/tispector/injection"
	"github.com/pingcap/enterprise-toolkit/tispector/util/logger"
	"testing"
)

func checkerSQL(sql string, items []string) {
	config.InitConfig("/Users/weiwei/goprojects/dbinjection/config/config.yml")
	logger.InitLog(config.Conf.Server.LogDir, "tispector.log", config.Conf.Server.LogLevel)
	injection.Injection()
	dao.InitDB()

	cluster := controller.TestCluster{
		User: "root",
		Pwd:  "sa123456",
		Addr: "172.16.4.81:5000",
	}
	dbName := "wwei"
	res, err := controller.CheckSQLByRules(sql, items, cluster, dbName)
	fmt.Println(res, err)
}

func TestRule_RuleDmlLimitOversized(t *testing.T) {

	sql := "select ur.username from user_role ur ,(select u.userid from user u where u.stats =1 limit 2000 ) ul where ur.userid = ul.userid limit  10 "
	items := []string{"DML.010"}

	checkerSQL(sql, items)
}

func TestRule_RuleCreateTableWithOutDate(t *testing.T) {

	sql := "\nCREATE TABLE IF NOT EXISTS `store` (\n    `store_id` varchar(20) NOT NULL,\n  `type` tinyint(3) unsigned NOT NULL COMMENT '店铺类型',\n     `create_user_id` bigint(20) unsigned NOT NULL,\n      PRIMARY KEY (`store_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='店铺信息';"
	items := []string{"Create.020"}

	checkerSQL(sql, items)
}

func TestRule_RuleCreateTooManyColumn(t *testing.T) {

	sql := "\nCREATE TABLE IF NOT EXISTS `store` (\n    `store_id` varchar(20) NOT NULL,\n  `type` tinyint(3) unsigned NOT NULL COMMENT '店铺类型',\n     `create_user_id` bigint(20) unsigned NOT NULL,\n      PRIMARY KEY (`store_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='店铺信息';"
	items := []string{"Create.021"}

	checkerSQL(sql, items)
}

func TestRule_RuleDmlHavingClause(t *testing.T) {

	sql := "select\n    ur.user_id,\n    (select count(a.id) from a where a.status = 1),\n    ur.state,\n    (select count(b.id) from\n                          b,\n                          (select * from c left join e on c.id = e.id where e.name is null and c.status = 2 having c.id >2 ) ce\n    where\n          b.tx in (select d.tx from d where d.status =3 )\n      and b.id = ce.id\n      and b.status = 4)\nfrom user_role ur,\n    (select * from f where f.status = 8 and f.tx in (select d.tx from d where d.status =7 )) f\nwhere ur.role_id = '1'\n  and exists(select 1 from q left join w on q.a = w.a where w.id is null and q.tx in (select d.tx from d where d.status =9))"

	items := []string{"DML.011"}
	checkerSQL(sql, items)
}

func TestRule_RuleDmlProhibitCrossJoin(t *testing.T) {

	sql := "select * from user_role ur cross join user u "

	items := []string{"DML.014"}
	checkerSQL(sql, items)
}

func TestRule_RuleDmlOrderByFieldsExceedsLimit(t *testing.T) {

	sql := "select userid,roleid from user_role order by userid,roleid "

	items := []string{"DML.015"}
	checkerSQL(sql, items)
}

func TestRule_RuleDmlSubqueriesWithNotInOrNotLike(t *testing.T) {

	sql := "select\n    ur.user_id,\n    (select count(a.id) from a where a.status = 1),\n    ur.state,\n    (select count(b.id) from\n                          b,\n                          (select * from c left join e on c.id = e.id where e.name is null and c.status = 2 having c.id >2 ) ce\n    where\n          b.tx in (select d.tx from d where d.status =3 )\n      and b.id = ce.id\n      and b.status = 4)\nfrom user_role ur,\n    (select * from f where f.status = 8 and f.tx in (select ur.userid from user_role ur where ur.userid not in (1,2,3,4) and ur.username not like 'tidb%' )) f \nwhere ur.role_id = '1'\n  and exists(select 1 from q left join w on q.a = w.a where w.id is null and q.tx in (select d.tx from d where d.status =9)) "

	items := []string{"DML.016"}
	checkerSQL(sql, items)
}

func TestRule_RuleDmlWithUnion(t *testing.T) {

	sql := "select userid,roleid from user_role where userid not in (1,2,3,4) union select userid,roleid from user_role where userid not like '1%'"

	items := []string{"DML.017"}
	checkerSQL(sql, items)
}

func TestRule_RuleDmlSubqueriesInWhereClause(t *testing.T) {
	sql := "select\n    ur.user_id,\n    (select count(a.id) from a where a.status = 1),\n    ur.state,\n    (select count(b.id) from\n                          b,\n                          (select * from c left join e on c.id = e.id where e.name is null and c.status = 2 having c.id >2 ) ce\n    where\n          b.tx in (select d.tx from d where d.status =3 )\n      and b.id = ce.id\n      and b.status = 4)\nfrom user_role ur,\n    (select * from f where f.status = 8 and f.tx in (select ur.userid from user_role ur where ur.userid not in (1,2,3,4) and ur.username not like 'tidb%' )) f \nwhere ur.role_id = '1'\n  "

	items := []string{"DML.018"}
	checkerSQL(sql, items)
}

func TestRule_RuleDmlSubqueriesInFromClause(t *testing.T) {
	//sql := "select\n    ur.user_id,\n    ur.state\nfrom\n     user_role ur\nwhere\n    ur.user_id in (select b.id from b, (select c.tx from c left join e on c.id = e.id ) ce where b.tx = ce.tx)"
	sql := "select * from a"

	items := []string{"DML.019"}
	checkerSQL(sql, items)
}

func TestRule_RuleDmlDeletDataFromMultipleTables(t *testing.T) {
	sql := "DELETE a ,b ,c FROM table1 a LEFT JOIN table2 b ON a.id = b.aid LEFT JOIN table3 c ON a.id = c.aid WHERE a.id = 1"

	items := []string{"DML.020"}
	checkerSQL(sql, items)
}

func TestRule_RuleDmlUpdateFromMultipleTables(t *testing.T) {
	sql := "UPDATE student s , class c SET s.class_name='test00',c.stu_name='test00' WHERE s.class_id = c.id"

	items := []string{"DML.021"}
	checkerSQL(sql, items)
}

func TestRule_RuleCreateTableNotUseForeignKey(t *testing.T) {
	sql := "create table user_test\n(\n    id     int     not null     auto_increment,\n    rID     int     null,\n    primary key (ID),\n    -- 方式二：指定外键名称为(FK_Name)\n    constraint FK_Name foreign key (rID) references user(user_id) on delete cascade on update cascade\n)"

	items := []string{"Create.022"}
	checkerSQL(sql, items)
}

func TestRule_RuleCreateTableUsePartitioning(t *testing.T) {
	sql := "CREATE TABLE employees (\n    id INT NOT NULL,\n    fname VARCHAR(30),\n    lname VARCHAR(30),\n    hired DATE NOT NULL DEFAULT '1970-01-01',\n    separated DATE DEFAULT '9999-12-31',\n    job_code INT,\n    store_id INT NOT NULL\n)\n\nPARTITION BY RANGE (job_code) (\n    PARTITION p0 VALUES LESS THAN (100),\n    PARTITION p1 VALUES LESS THAN (1000),\n    PARTITION p2 VALUES LESS THAN (10000)\n);"

	items := []string{"Create.023"}
	checkerSQL(sql, items)
}

func TestRule_RuleCreateSequenceNoCache(t *testing.T) {
	sql := "CREATE SEQUENCE seq2 start 3 increment 2 minvalue 1 maxvalue 10 cache 3;"

	items := []string{"Create.024"}
	checkerSQL(sql, items)
}

func TestRule_RuleAlterUseForeignKey(t *testing.T) {
	sql := "ALTER TABLE t1 ADD FOREIGN KEY (Id) REFERENCES t2(t_Id)"

	items := []string{"Alter.006"}
	checkerSQL(sql, items)
}

func TestRule_RulePlnIdxRangeSan(t *testing.T) {

	sql := "select * from user u, user_role ur where  u.user_id = '1' and  ur.user_id = '2';"

	items := []string{"PLN.001"}
	checkerSQL(sql, items)
}

func TestRule_RulePlnIdxFullSan(t *testing.T) {
	sql := "SELECT id, name, email, phone,taxrate FROM users WHERE id=1 ;"

	items := []string{"PLN.002"}
	checkerSQL(sql, items)
}

func TestRule_RulePushdownNotSupportedPredicates(t *testing.T) {
	//sql := "select id from t where substring('123', a, 1) = '1';"
	sql := "select t.* from user_role t where t.user_id=JSON_TYPE(user_id)\n"

	items := []string{"PUSHDOWN.001"}
	checkerSQL(sql, items)
}

func TestRule_RulePushdownToJoinOperator(t *testing.T) {
	//sql := "select ur.role_id from user_role ur left join user_info ui on ur.user_id = ui.Id left join role r on ur.role_id = r.role_id  where r.role_name is null and ui.password is null and ur.state = 1"

	//sql := "select ur.role_id from user_role ur , (select s.id from t right join s on t.a = s.a where t.id is null) tmp where tmp.id = ur.user_id  and ur.state = 1"
	//sql := "select s.id from t right join s on t.a = s.a where t.id is null"

	//sql := "select ur.role_id from user_role ur where ur.userid in (select s.id from t right join s on t.a = s.a where t.id is null) and  exists (select 1 from q left join w on q.a = w.a where w.id is null ) and ur.status = (select c.id from c where c.id = 12)"

	//sql := "select ur.user_id,(select count(ur.role_id) from user_role ur right join user_info ui on ur.user_id = ui.Id  where ur.role_id is null),ur.state,(select count(ur.role_id) from user_role ur right join user_info ui on ur.user_id = ui.Id  where ur.role_id is null) from user_role ur where ur.role_id = '1';"

	//sql := "select\n    ur.user_id,\n    (select count(a.id) from a where a.status = 1),\n    ur.state,\n    (select count(b.id) from\n                          b,\n                          (select * from c left join e on c.id = e.id where e.name is null and c.status = 2) ce\n    where\n          b.tx in (select d.tx from d where d.status =3 )\n      and b.id = ce.id\n      and b.status = 4)\nfrom user_role ur,\n    (select * from f where f.status = 8 and f.tx in (select d.tx from d where d.status =7 )) f\nwhere ur.role_id = '1'\n  and exists(select 1 from q left join w on q.a = w.a where w.id is null and q.tx in (select d.tx from d where d.status =9))\n"

	//sql := "select id from view1 where id >ANY(select id from view1123)"

	//sql := "insert into projectfile (id, projectid) select projectfile.id ,projectfile2.projectid from projectfile ;"
	//sql := "Update Table1 set c = (select c from Table2 where a = Table1.a) where c is null and (workdate, cino) in (select workdate, cino from tab2)"
	//sql := "Update tab1 t set val1 = 1 where (workdate, cino) in (select workdate, cino from tab2) ;"
	//sql := "UPDATE test.tb_vobile a\njoin\n(select max(id) id from test.tb_vobile) b\non a.id = b.id\nset a.name = '123 ';"
	//sql := "UPDATE test.tb_vobile a ,(select max(id) id from test.tb_vobile) b\nset a.name = '321 '\nWHERE\na.id = b.id ;"
	//sql := "delete from  t_book  where fcategoryid in\n(\nselect fcategoryid from t_book t group by t.fcategoryid having count(*)>3\n)"
	sql := "select 1"

	items := []string{"PUSHDOWN.002"}
	checkerSQL(sql, items)
}

func TestRule_RulePushdownWithUserVaribales(t *testing.T) {
	//sql := "select id  from t where a = @a;"
	sql := "select t.* from user_role t where t.user_id=JSON_TYPE(user_id)\n"

	items := []string{"PUSHDOWN.003"}
	checkerSQL(sql, items)
}