###################################################################
#           Copyright (c) 2016 by TAOS Technologies, Inc.
#                     All rights reserved.
#
#  This file is proprietary and confidential to TAOS Technologies.
#  No part of this file may be reproduced, stored, transmitted,
#  disclosed or used in any form or by any means other than as
#  expressly provided by the written permission from Jianhui Tao
#
###################################################################

# -*- coding: utf-8 -*-

import sys
import taos
import numpy as np
from util.log import tdLog
from util.cases import tdCases
from util.sql import tdSql
from util.dnodes import tdDnodes


class TDTestCase:
    def init(self, conn, logSql):
        tdLog.debug("start to execute %s" % __file__)
        tdSql.init(conn.cursor(), logSql)

        self.ts = 1593548685000                  

    def run(self):
        tdSql.prepare()

        tdSql.execute("create table st (ts timestamp, voltage int) tags (loc nchar(30))")
        tdSql.execute("insert into t0 using st tags('beijing') values(%d, 220) (%d, 221) (%d, 225) (%d, 228) (%d, 222)" 
                        % (self.ts, self.ts + 1000000000, self.ts + 2000000000, self.ts + 3000000000, self.ts + 6000000000))
        tdSql.execute("insert into t1 using st tags('shanghai') values(%d, 220) (%d, 221) (%d, 225) (%d, 228) (%d, 222)" 
                        % (self.ts, self.ts + 2000000000, self.ts + 4000000000, self.ts + 5000000000, self.ts + 7000000000))             
                

        tdSql.query("select avg(voltage) from st interval(1n)")
        tdSql.checkRows(3)        
        tdSql.checkData(0, 0, "2020-07-01 00:00:00")
        tdSql.checkData(0, 1, 221.4)        
        tdSql.checkData(1, 0, "2020-08-01 00:00:00")
        tdSql.checkData(1, 1, 227.0)        
        tdSql.checkData(2, 0, "2020-09-01 00:00:00")
        tdSql.checkData(2, 1, 222.0)
        
        tdSql.query("select avg(voltage) from st interval(1n, 15d)")
        tdSql.checkRows(4)
        tdSql.checkData(0, 0, "2020-06-16 00:00:00")
        tdSql.checkData(0, 1, 220.333333)
        tdSql.checkData(1, 0, "2020-07-16 00:00:00")
        tdSql.checkData(1, 1, 224.666666)
        tdSql.checkData(2, 0, "2020-08-16 00:00:00")
        tdSql.checkData(2, 1, 225.0)
        tdSql.checkData(3, 0, "2020-09-16 00:00:00")
        tdSql.checkData(3, 1, 222.0)

        tdSql.query("select avg(voltage) from st interval(1n, 15d) group by loc")
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, "2020-06-16 00:00:00")
        tdSql.checkData(0, 1, 220.5)
        tdSql.checkData(1, 0, "2020-07-16 00:00:00")
        tdSql.checkData(1, 1, 226.5)
        tdSql.checkData(2, 0, "2020-08-16 00:00:00")
        tdSql.checkData(2, 1, 222.0)
        tdSql.checkData(3, 0, "2020-06-16 00:00:00")
        tdSql.checkData(3, 1, 220.0)
        tdSql.checkData(4, 0, "2020-07-16 00:00:00")
        tdSql.checkData(4, 1, 221.0)
        tdSql.checkData(5, 0, "2020-08-16 00:00:00")
        tdSql.checkData(5, 1, 226.5)
        tdSql.checkData(6, 0, "2020-09-16 00:00:00")
        tdSql.checkData(6, 1, 222.0)

        # test case for https://jira.taosdata.com:18080/browse/TD-5338
        tdSql.query("select loc,max(voltage) from st  interval(1m);")
        tdSql.checkRows(8)
        tdSql.checkData(0, 0, "2020-07-01 04:24:00.000")
        tdSql.checkData(0, 1, "beijing")
        tdSql.checkData(0, 2, 220)
        tdSql.checkData(1, 0, "2020-07-12 18:11:00.000")
        tdSql.checkData(1, 1, "beijing")
        tdSql.checkData(1, 2, 221)
        tdSql.checkData(2, 0, "2020-07-24 07:58:00.000")
        tdSql.checkData(2, 1, "beijing")
        tdSql.checkData(2, 2, 225)
        tdSql.checkData(3, 0, "2020-08-04 21:44:00.000")
        tdSql.checkData(2, 1, "beijing")
        tdSql.checkData(3, 2, 228)
        tdSql.checkData(4, 0, "2020-08-16 11:31:00.000")
        tdSql.checkData(4, 1, "shanghai")
        tdSql.checkData(4, 2, 225)
        tdSql.checkData(5, 0, "2020-08-28 01:18:00.000")
        tdSql.checkData(5, 1, "shanghai")
        tdSql.checkData(5, 2, 228)
        tdSql.checkData(6, 0, "2020-09-08 15:04:00.000")
        tdSql.checkData(6, 1, "beijing")
        tdSql.checkData(6, 2, 222)
        tdSql.checkData(7, 0, "2020-09-20 04:51:00.000")
        tdSql.checkData(7, 1, "shanghai")
        tdSql.checkData(7, 2, 222)
        tdSql.query("select loc,max(voltage) from t0  interval(1m);")
        tdSql.checkRows(5)
        tdSql.checkData(0, 0, "2020-07-01 04:24:00.000")
        tdSql.checkData(0, 1, "beijing")
        tdSql.checkData(0, 2, 220)
        tdSql.checkData(1, 0, "2020-07-12 18:11:00.000")
        tdSql.checkData(1, 1, "beijing")
        tdSql.checkData(1, 2, 221)
        tdSql.checkData(2, 0, "2020-07-24 07:58:00.000")
        tdSql.checkData(2, 1, "beijing")
        tdSql.checkData(2, 2, 225)
        tdSql.checkData(3, 0, "2020-08-04 21:44:00.000")
        tdSql.checkData(2, 1, "beijing")
        tdSql.checkData(3, 2, 228)       
        tdSql.checkData(4, 0, "2020-09-08 15:04:00.000")
        tdSql.checkData(4, 1, "beijing")
        tdSql.checkData(4, 2, 222)
    

        # test case for https://jira.taosdata.com:18080/browse/TD-2298
        tdSql.execute("create database test keep 36500")
        tdSql.execute("use test")
        tdSql.execute("create table t (ts timestamp, voltage int)")
        for i in range(10000):
            tdSql.execute("insert into t values(%d, 0)" % (1000000 + i * 6000))
        
        tdDnodes.stop(1)
        tdDnodes.start(1)
        tdSql.query("select last(*) from t interval(1s)")
        tdSql.checkRows(10000)

        # test case for https://jira.taosdata.com:18080/browse/TD-2601
        newTs = 1601481600000

        tdSql.execute("create database test2")
        tdSql.execute("use test2")
        tdSql.execute("create table t (ts timestamp, voltage int)")
        for i in range(100):
            tdSql.execute("insert into t values(%d, %d)" % (newTs + i * 10000000, i))
        
        tdSql.query("select sum(voltage) from t where ts >='2020-10-01 00:00:00' and ts <='2020-12-01 00:00:00' interval(1n) fill(NULL)")
        tdSql.checkRows(3)
        tdSql.checkData(0, 1, 4950)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(2, 1, None)

         # test case for https://jira.taosdata.com:18080/browse/TD-2659， https://jira.taosdata.com:18080/browse/TD-2660
        tdSql.execute("create database test3")
        tdSql.execute("use test3")
        tdSql.execute("create table tb(ts timestamp, c int)")
        tdSql.execute("insert into tb values('2020-10-30 18:11:56.680', -111)")
        tdSql.execute("insert into tb values('2020-11-19 18:11:45.773', null)")
        tdSql.execute("insert into tb values('2020-12-09 18:11:17.098', null)")
        tdSql.execute("insert into tb values('2020-12-29 11:00:49.412', 1)")
        tdSql.execute("insert into tb values('2020-12-29 11:00:50.412', 2)")
        tdSql.execute("insert into tb values('2020-12-29 11:00:52.412', 3)")

        tdSql.query("select first(ts),twa(c) from tb interval(14a)")
        tdSql.checkRows(6)

        tdSql.error("select twa(c) from tb group by c")

        # TD-14678
        tdSql.execute("create database test4")
        tdSql.execute("use test4")
        tdSql.execute("create table stb(ts timestamp, c int) tags(t1 int)")
        ts = 1630000000000
        sql = "insert into t1 using stb tags(1) values"
        for i in range(100):
            sql += "(%d, %d)" % (ts + i * 1000, i % 100)
        tdSql.execute(sql)

        tdSql.query("select COUNT(*) from stb interval(13m) sliding(3m) group by tbname order by ts desc")
        tdSql.checkData(0, 1, 20)
        tdSql.checkData(1, 1, 100)
        tdSql.checkData(2, 1, 100)
        tdSql.checkData(3, 1, 100)
        tdSql.checkData(4, 1, 100)

        tdSql.query("select COUNT(*) from stb interval(13m) sliding(3m) group by tbname order by ts")
        tdSql.checkData(0, 1, 100)
        tdSql.checkData(1, 1, 100)
        tdSql.checkData(2, 1, 100)
        tdSql.checkData(3, 1, 100)
        tdSql.checkData(4, 1, 20)

        # TD-14698
        tdSql.query("select SPREAD(_c0) from (select * from stb) where ts between 1630000001000 and 1630100001000 interval(12h) Fill(NULL) order by ts")
        matrix = np.array(tdSql.queryResult)
        list = matrix[:, 0]

        if all(sorted(list) == list):
            tdLog.info("sql:%s, column : ts is sorted in accending order as expected" % (tdSql.sql))
        else:
            tdLog.exit("sql:%s, column : ts is not sorted in accending order as expected" % (tdSql.sql))

        # TS-1582
        tdSql.execute("create table stb1(ts timestamp, c1 int) tags(t1 int)")
        tdSql.execute("insert into tb1 using stb1 tags(1) values(%d, 1)(%d, 5)(%d, 10)" % (self.ts, self.ts + 1000, self.ts + 2000))
        tdSql.execute("insert into tb2 using stb1 tags(2) values(%d, 1)(%d, 5)(%d, 10)" % (self.ts, self.ts + 1000, self.ts + 2000))

        tdSql.query("select t1, last(c1) from stb1 where ts between 1593548685000 and 1593548688000 interval(2s) fill(NULL) group by tbname")
        tdSql.checkRows(6)
        tdSql.checkData(2, 2, None)
        tdSql.checkData(2, 3, "tb1")
        tdSql.checkData(5, 2, None)
        tdSql.checkData(5, 3, "tb2")

        tdSql.query("select t1, last(c1) - 1 from stb1 where ts between 1593548685000 and 1593548688000 interval(2s) fill(NULL) group by tbname")
        tdSql.checkRows(6)
        tdSql.checkData(2, 2, None)
        tdSql.checkData(2, 3, "tb1")
        tdSql.checkData(5, 2, None)
        tdSql.checkData(5, 3, "tb2")

    def stop(self):
        tdSql.close()
        tdLog.success("%s successfully executed" % __file__)


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