from util.log import *
from util.sql import *
from util.cases import *

class TDTestCase:
    
    def init(self, conn, logSql):
        tdLog.debug(f"start to excute {__file__}")
        tdSql.init(conn.cursor())

    def run(self):  # sourcery skip: extract-duplicate-method
        tdSql.prepare()
        tdLog.printNoPrefix("==========step1:create tables==========")
        tdSql.execute(
            '''create table if not exists ntb
            (ts timestamp, c1 int, c2 float,c3 double,c4 timestamp)
            '''
        )
        tdSql.execute(
            '''create table if not exists stb
            (ts timestamp, c1 int, c2 float,c3 double,c4 timestamp) tags(t0 int)
            '''
        )
        tdSql.execute(
            '''create table if not exists stb_1 using stb tags(100)
            '''
        )
        tdLog.printNoPrefix("==========step2:insert data into ntb==========")

        # RFC3339:2020-01-01T00:00:00+8:00
        # ISO8601:2020-01-01T00:00:00.000+0800
        tdSql.execute(
            'insert into ntb values(now,1,1.55,100.555555,today())("2020-1-1 00:00:00",10,11.11,99.999999,now())(today(),3,3.333,333.333333,now())')
        tdSql.execute(
            'insert into stb_1 values(now,1,1.55,100.555555,today())("2020-1-1 00:00:00",10,11.11,99.999999,now())(today(),3,3.333,333.333333,now())')
        
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00') from ntb")
        tdSql.checkRows(3)
        tdSql.query("select timediff(1,0,1d) from ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff(1,0,1d) from db.ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff(1,0,1s) from ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,1)
        tdSql.query("select timediff(1,0,1s) from db.ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,1)
        tdSql.query("select timediff(1,0,1w) from ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff(1,0,1w) from db.ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff(1,0,1h) from ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff(1,0,1h) from db.ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff(1,0,1m) from ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff(1,0,1m) from db.ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff(1,0,1a) from ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,1000)
        tdSql.query("select timediff(1,0,1a) from db.ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,1000)
        tdSql.query("select timediff(1,0,1u) from ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,1000000)
        tdSql.query("select timediff(1,0,1u) from db.ntb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,1000000)

        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00') from stb")
        tdSql.checkRows(3)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00') from db.stb")
        tdSql.checkRows(3)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1d) from stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,1)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1d) from db.stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,1)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1h) from stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,24)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1h) from db.stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,24)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1w) from stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1m) from stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,1440)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1m) from db.stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,1440)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1s) from stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,86400)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1s) from db.stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,86400)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1a) from stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,86400000)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1a) from db.stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,86400000)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1u) from stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,86400000000)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-2 00:00:00',1u) from db.stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,86400000000)


        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00') from stb_1")
        tdSql.checkRows(3)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00') from db.stb_1")
        tdSql.checkRows(3)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1w) from stb_1 ")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1w) from db.stb_1 ")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1d) from stb_1 ")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1d) from db.stb_1 ")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,0)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1h) from stb_1 ")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,12)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1h) from db.stb_1 ")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,12)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1m) from stb_1" )
        tdSql.checkRows(3)
        tdSql.checkData(0,0,720)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1m) from db.stb_1" )
        tdSql.checkRows(3)
        tdSql.checkData(0,0,720)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1s) from stb_1")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,43200)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1s) from db.stb_1")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,43200)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1a) from stb_1")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,43200000)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1a) from db.stb_1")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,43200000)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1u) from stb_1")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,43200000000)
        tdSql.query("select timediff('2020-1-1 00:00:00','2020-1-1 12:00:00',1u) from db.stb_1")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,43200000000)

        tdSql.query("select timediff('a','b') from stb")
        tdSql.checkRows(3)
        tdSql.checkData(0,0,None)
        tdSql.checkData(1,0,None)
        tdSql.checkData(2,0,None)
        tdSql.error("select timediff(1.5,1.5) from stb")
        tdSql.error("select timediff(1) from stb")
        tdSql.error("select timediff(10,1,1.5) from stb")
        # tdSql.error("select timediff(10,1,2s) from stb")
        # tdSql.error("select timedifff(10,1,c1) from stb")
        tdSql.error("select timediff(1.5,1.5) from stb_1")
        tdSql.error("select timediff(1) from stb_1")
        tdSql.error("select timediff(10,1,1.5) from stb_1")
        # tdSql.error("select timediff(10,1,2s) from stb_1")
        # tdSql.error("select timedifff(10,1,c1) from stb_1")
        tdSql.error("select timediff(1.5,1.5) from ntb")
        tdSql.error("select timediff(1) from ntb")
        tdSql.error("select timediff(10,1,1.5) from ntb")
        # tdSql.error("select timediff(10,1,2s) from ntb")
        # tdSql.error("select timedifff(10,1,c1) from ntb")






    def stop(self):
        tdSql.close()
        tdLog.success(f"{__file__} successfully executed")

tdCases.addLinux(__file__, TDTestCase())
tdCases.addWindows(__file__, TDTestCase())