package affectversions

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	. "github.com/onsi/ginkgo"
	. "github.com/onsi/gomega"
	"github.com/pingcap/test-infra/sdk/resource"
	"go.uber.org/zap"

	_ "github.com/pingcap/endless/pkg/client/tidb"
	. "github.com/pingcap/endless/pkg/matcher"
	"github.com/pingcap/log"
)

var _ = Describe("Planner", func() {
	var db *sql.DB
	var tc resource.TiDBCluster
	Context("in expression", func() {
		BeforeEach(func() {
			tc = suiteTestCtx.Resource("tidb").(resource.TiDBCluster)
			db, _ = tc.GetDB("test")
		})
		It("should not handle collation if it contains a row function #TIBUG-1631#", func() {
			Try(db.Exec("drop table if exists t;"))
			Try(db.Exec("create table t(`COL2` tinyint(16) DEFAULT NULL);"))
			Try(db.Exec("insert into t values(0);"))

			rows := db.QueryRow("select * from t where col2 in (0xfc);")
			var a int = 100
			err := rows.Scan(&a)
			Expect(err).Should(Equal(sql.ErrNoRows))
			Expect(a).Should(Equal(100))
		})
	})

	Context("enable plan cache", func() {
		BeforeEach(func() {
			tc = suiteTestCtx.Resource("tidb").(resource.TiDBCluster)
			db, _ = tc.GetDB("test")
		})
		It("should not panic for prepare arguments type changed #TIBUG-1768#", func() {
			initSql := `
	drop table if exists t;
	create table t (a int, b int, c int,primary key k_a(a),unique key k_b(b));
	insert into t values (1, 1, 1);
	insert into t values (2, 2, 2);
	insert into t values (3, 3, 3);
	`
			InitData(db, initSql)
			stmt1, err := db.Prepare("select * from t where a = ?")
			if err != nil {
				log.Fatal("prepare failed", zap.Error(err))
			}

			var a int
			if err := stmt1.QueryRow(0).Scan(&a); err != nil {
				Expect(err).To(Equal(sql.ErrNoRows))
			}
			if err := stmt1.QueryRow(nil).Err(); err != nil {
				Expect(err).Should(BeNil())
			}
		})
	})
})

var _ = Describe("sql-infra", func() {
	var (
		db *sql.DB
		tc resource.TiDBCluster
	)

	Context("enable expression index", func() {
		BeforeEach(func() {
			tc = suiteTestCtx.Resource("tidb").(resource.TiDBCluster)
			db, _ = tc.GetDB("test")
		})

		It("should return the correct result while using expression index #TIBUG-1621#", func() {
			initSql := `
		drop table if exists t02;
		create table t02 (a varchar(20));
		insert into t02 values ('a'), ('b'), ('c');
		create index eidx on t02 ((lower(a)));
		`
			InitData(db, initSql)
			rows, err := db.Query(" select * from t02 use index(eidx) where lower(a) < 'c';")
			Expect(err).Should(BeNil())
			var res []string
			var a string
			for rows.Next() {
				err = rows.Scan(&a)
				Expect(err).Should(BeNil())
				res = append(res, a)
			}

			Expect(res).To(Equal([]string{"a", "b"}))
		})
	})
})

var _ = Describe("execution", func() {
	var (
		db *sql.DB
		tc resource.TiDBCluster
	)
	BeforeEach(func() {
		tc = suiteTestCtx.Resource("tidb").(resource.TiDBCluster)
		db, _ = tc.GetDB("test")
	})

	AfterEach(func() {
		Try(db.Exec("drop database if exists test;"))
		Try(db.Exec("create database test;"))
		db.Close()
	})

	It("indexHashJoin should not hang when using limit #TIBUG-2442#", func() {
		Try(db.Exec("create table t1 (a int, b int, index idx(b));"))
		Try(db.Exec("create table t2 (c int, d int, index idx(d));"))
		var values string
		for i := 0; i < 3000; i++ {
			values += fmt.Sprintf("(%d, %d), ", i, i)
		}
		Try(db.Exec("insert into t1 values" + values[:len(values)-2] + ";"))
		Try(db.Exec("insert into t2 values" + values[:len(values)-2] + ";"))
		Try(db.Exec("set tidb_max_chunk_size=32;"))
		Try(db.Exec("set tidb_index_join_batch_size=32;"))
		Try(db.Exec("INSERT INTO mysql.opt_rule_blacklist VALUES('topn_push_down');"))
		Try(db.Exec("admin reload opt_rule_blacklist;"))

		var value int
		ctx1, cancel := context.WithTimeout(suiteTestCtx, time.Second*10)
		defer cancel()
		err := db.QueryRowContext(ctx1, "select /*+ inl_hash_join(t2) */ t1.b from t1 left join t2  on t1.b=t2.d order by t1.b limit 1000,1;").Scan(&value)
		Expect(err).Should(BeNil())
		Expect(value).Should(Equal(1000))

		err = db.QueryRowContext(ctx1, "select /*+ inl_hash_join(t2) */ t1.b from t1 left join t2  on t1.b=t2.d order by t1.b limit 1000,1;").Scan(&value)
		Expect(err).Should(BeNil(), "the second index hash join query should not be hang")
		Expect(value).Should(Equal(1000))

	})

	It("time add should return the correct result #TIBUG-115#", func() {
		var value string
		var nullValue sql.NullTime
		_ = db.QueryRow("select now();").Scan(&value)
		stoday := strings.Split(value, " ")[0]
		today, _ := time.Parse("2006-01-02", stoday)
		log.Info("today is ", zap.String("now", value), zap.Time("today", today))

		_ = db.QueryRow("select time('00:00:00') + interval 3020399000 MICROSECOND;").Scan(&value)
		Expect(value).To(Equal("00:50:20.399000"))

		_ = db.QueryRow("select time('00:00:00') + interval 3020400000 MICROSECOND;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval 3020399 second;").Scan(&value)
		Expect(value).To(Equal("838:59:59"))

		_ = db.QueryRow("select time('00:00:00') + interval 3020400 second;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval 50339 minute;").Scan(&value)
		Expect(value).To(Equal("838:59:00"))

		_ = db.QueryRow("select time('00:00:00') + interval 50340 minute;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval 838 hour;").Scan(&value)
		Expect(value).To(Equal("838:00:00"))

		_ = db.QueryRow("select time('00:00:00') + interval 839 hour;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval 34 day;").Scan(&value)
		expect := today.AddDate(0, 0, 34).Format("2006-01-02 15:04:05")
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval 35 day;").Scan(&value)
		expect = today.AddDate(0, 0, 35).Format("2006-01-02 15:04:05")
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval 4 week;").Scan(&value)
		expect = today.AddDate(0, 0, 4*7).Format("2006-01-02 15:04:05")
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval 5 week;").Scan(&value)
		expect = today.AddDate(0, 0, 5*7).Format("2006-01-02 15:04:05")
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval -2 month;").Scan(&value)
		expect = today.AddDate(0, -2, 0).Format("2006-01-02 15:04:05")
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval 1 month;").Scan(&value)
		expect = today.AddDate(0, 1, 0).Format("2006-01-02 15:04:05")
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval 2 month;").Scan(&value)
		expect = today.AddDate(0, 2, 0).Format("2006-01-02 15:04:05")
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '34 22:59:59' day_second;").Scan(&value)
		expect = today.AddDate(0, 0, 34).Format("2006-01-02") + " 22:59:59"
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '34 23:00:00' day_second;").Scan(&value)
		expect = today.AddDate(0, 0, 34).Format("2006-01-02") + " 23:00:00"
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '34 22:59' day_minute;").Scan(&value)
		expect = today.AddDate(0, 0, 34).Format("2006-01-02") + " 22:59:00"
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '34 23:00' day_minute;").Scan(&value)
		expect = today.AddDate(0, 0, 34).Format("2006-01-02") + " 23:00:00"
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '34 22' DAY_HOUR;").Scan(&value)
		expect = today.AddDate(0, 0, 34).Format("2006-01-02") + " 22:00:00"
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '34 23' DAY_HOUR;").Scan(&value)
		expect = today.AddDate(0, 0, 34).Format("2006-01-02") + " 23:00:00"
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '0-1' YEAR_MONTH;").Scan(&value)
		expect = today.AddDate(0, 1, 0).Format("2006-01-02 15:04:05")
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '0-0' YEAR_MONTH;").Scan(&value)
		expect = today.AddDate(0, 0, 0).Format("2006-01-02 15:04:05")
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '1-0' YEAR_MONTH;").Scan(&value)
		expect = today.AddDate(1, 0, 0).Format("2006-01-02 15:04:05")
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '3020399.0000000' MINUTE_MICROSECOND;").Scan(&value)
		Expect(value).To(Equal("838:59:59.000000"))

		_ = db.QueryRow("select time('00:00:00') + interval '3020399.0000001' MINUTE_MICROSECOND;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval '3020399.0000000' SECOND_MICROSECOND;").Scan(&value)
		Expect(value).To(Equal("838:59:59.000000"))

		_ = db.QueryRow("select time('00:00:00') + interval '3020399.0000001' SECOND_MICROSECOND;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval '0:3020399' MINUTE_SECOND;").Scan(&value)
		Expect(value).To(Equal("838:59:59"))

		_ = db.QueryRow("select time('00:00:00') + interval '0:3020400' MINUTE_SECOND;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval '838:59:59.000000' HOUR_MICROSECOND;").Scan(&value)
		Expect(value).To(Equal("838:59:59.000000"))

		_ = db.QueryRow("select time('00:00:00') + interval '838:59:59.000001' HOUR_MICROSECOND;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval '839:00:00' HOUR_SECOND;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval '838:59:59' HOUR_SECOND;").Scan(&value)
		Expect(value).To(Equal("838:59:59"))

		_ = db.QueryRow("select time('00:00:00') + interval '839:00' HOUR_MINUTE;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval '838:59' HOUR_MINUTE;").Scan(&value)
		Expect(value).To(Equal("838:59:00"))

		_ = db.QueryRow("select time('00:00:00') + interval '838:59:59.000001' DAY_MICROSECOND;").Scan(&nullValue)
		Expect(nullValue.Valid).To(BeFalse())

		_ = db.QueryRow("select time('00:00:00') + interval '838:59:59.00000' DAY_MICROSECOND;").Scan(&value)
		Expect(value).To(Equal("838:59:59.000000"))

		_ = db.QueryRow("select time('00:00:00') + interval '839:00:00' DAY_SECOND;").Scan(&value)
		expect = today.AddDate(0, 0, 34).Format("2006-01-02") + " 23:00:00"
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '838:59:59' DAY_SECOND;").Scan(&value)
		expect = today.AddDate(0, 0, 34).Format("2006-01-02") + " 22:59:59"
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '839:00' DAY_MINUTE;").Scan(&value)
		expect = today.AddDate(0, 0, 34).Format("2006-01-02") + " 23:00:00"
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("select time('00:00:00') + interval '838:59' DAY_MINUTE;").Scan(&value)
		expect = today.AddDate(0, 0, 34).Format("2006-01-02") + " 22:59:00"
		Expect(value).To(Equal(expect))

		_ = db.QueryRow("SELECT time('12:34:56') + INTERVAL 1 SECOND;").Scan(&value)
		Expect(value).To(Equal("12:34:57"))

		_ = db.QueryRow("SELECT time('12:34:56') + INTERVAL 1 HOUR;").Scan(&value)
		Expect(value).To(Equal("13:34:56"))

		_ = db.QueryRow("SELECT time('12:34:56') + INTERVAL 1 DAY;").Scan(&value)
		expect = today.AddDate(0, 0, 1).Format("2006-01-02") + " 12:34:56"
		Expect(value).To(Equal(expect))
	})

})

func InitData(db *sql.DB, inits string) {
	for _, sql := range strings.Split(inits, "\n") {
		if _, err := db.Exec(sql); err != nil {
			log.Fatal("init data failed", zap.Error(err))
		}
	}
}