import unittest
import os
import time
import pg8000
import datetime
from .connection_settings import db_connect
from sys import exc_info
from pg8000.six import b, IS_JYTHON
from distutils.version import LooseVersion


# DBAPI compatible interface tests
class Tests(unittest.TestCase):
    def setUp(self):
        self.db = pg8000.connect(**db_connect)
        # Jython 2.5.3 doesn't have a time.tzset() so skip
        if not IS_JYTHON:
            os.environ['TZ'] = "UTC"
            time.tzset()

        try:
            c = self.db.cursor()
            try:
                c = self.db.cursor()
                c.execute("DROP TABLE t1")
            except pg8000.DatabaseError:
                e = exc_info()[1]
                # the only acceptable error is:
                self.assertEqual(e.args[1], '42P01')  # table does not exist
                self.db.rollback()
            c.execute(
                "CREATE TEMPORARY TABLE t1 "
                "(f1 int primary key, f2 int not null, f3 varchar(50) null)")
            c.execute(
                "INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)",
                (1, 1, None))
            c.execute(
                "INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)",
                (2, 10, None))
            c.execute(
                "INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)",
                (3, 100, None))
            c.execute(
                "INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)",
                (4, 1000, None))
            c.execute(
                "INSERT INTO t1 (f1, f2, f3) VALUES (%s, %s, %s)",
                (5, 10000, None))
            self.db.commit()
        finally:
            c.close()

    def tearDown(self):
        self.db.close()

    def testParallelQueries(self):
        try:
            c1 = self.db.cursor()
            c2 = self.db.cursor()

            c1.execute("SELECT f1, f2, f3 FROM t1")
            while 1:
                row = c1.fetchone()
                if row is None:
                    break
                f1, f2, f3 = row
                c2.execute("SELECT f1, f2, f3 FROM t1 WHERE f1 > %s", (f1,))
                while 1:
                    row = c2.fetchone()
                    if row is None:
                        break
                    f1, f2, f3 = row
        finally:
            c1.close()
            c2.close()

        self.db.rollback()

    def testQmark(self):
        orig_paramstyle = pg8000.paramstyle
        try:
            pg8000.paramstyle = "qmark"
            c1 = self.db.cursor()
            c1.execute("SELECT f1, f2, f3 FROM t1 WHERE f1 > ?", (3,))
            while 1:
                row = c1.fetchone()
                if row is None:
                    break
                f1, f2, f3 = row
            self.db.rollback()
        finally:
            pg8000.paramstyle = orig_paramstyle
            c1.close()

    def testNumeric(self):
        orig_paramstyle = pg8000.paramstyle
        try:
            pg8000.paramstyle = "numeric"
            c1 = self.db.cursor()
            c1.execute("SELECT f1, f2, f3 FROM t1 WHERE f1 > :1", (3,))
            while 1:
                row = c1.fetchone()
                if row is None:
                    break
                f1, f2, f3 = row
            self.db.rollback()
        finally:
            pg8000.paramstyle = orig_paramstyle
            c1.close()

    def testNamed(self):
        orig_paramstyle = pg8000.paramstyle
        try:
            pg8000.paramstyle = "named"
            c1 = self.db.cursor()
            c1.execute(
                "SELECT f1, f2, f3 FROM t1 WHERE f1 > :f1", {"f1": 3})
            while 1:
                row = c1.fetchone()
                if row is None:
                    break
                f1, f2, f3 = row
            self.db.rollback()
        finally:
            pg8000.paramstyle = orig_paramstyle
            c1.close()

    def testFormat(self):
        orig_paramstyle = pg8000.paramstyle
        try:
            pg8000.paramstyle = "format"
            c1 = self.db.cursor()
            c1.execute("SELECT f1, f2, f3 FROM t1 WHERE f1 > %s", (3,))
            while 1:
                row = c1.fetchone()
                if row is None:
                    break
                f1, f2, f3 = row
            self.db.commit()
        finally:
            pg8000.paramstyle = orig_paramstyle
            c1.close()

    def testPyformat(self):
        orig_paramstyle = pg8000.paramstyle
        try:
            pg8000.paramstyle = "pyformat"
            c1 = self.db.cursor()
            c1.execute(
                "SELECT f1, f2, f3 FROM t1 WHERE f1 > %(f1)s", {"f1": 3})
            while 1:
                row = c1.fetchone()
                if row is None:
                    break
                f1, f2, f3 = row
            self.db.commit()
        finally:
            pg8000.paramstyle = orig_paramstyle
            c1.close()

    def testArraysize(self):
        try: