import pytest

import dbconnection.db as dbconnection


@pytest.fixture
def db():
    """ Returns a connected database object. Each test should have new connection."""
    database = dbconnection.DB()
    database.connect()
    # create tables with sql ../tidb-pcta-course-ja-lab/scripts/01-demo-auto-increment-01-setup.sql
    database.query_file('../tidb-pcta-course-ja-lab/scripts/01-demo-auto-increment-01-setup.sql')
    return database

def test_db_test_tables(db: dbconnection.DB):
    db.connect()
    # check if tables are created using information_schema
    result = db.query('SELECT table_name FROM information_schema.tables WHERE table_schema = "test" AND table_name IN ("t1", "t2")')
    assert len(result) == 2

def test_db_auto_increment(db: dbconnection.DB):
    db.connect()
    db.query_file('../tidb-pcta-course-ja-lab/scripts/01-demo-auto-increment-02-show.sql')
    result = db.query('SELECT * FROM t1')
    assert len(result) == 4

    # iterate three times demo increment 02 to check auto increment is working
    for i in range(3):
        db.query_file('../tidb-pcta-course-ja-lab/scripts/01-demo-auto-increment-02-show.sql')
        result = db.query('SELECT max(id) FROM t1')
        assert result[0][0] == 4 + (i + 1) * 4

# fail if it runs on TiDB Serverless
def test_db_auto_increment_gap(db: dbconnection.DB):
    db.connect()
    db.query_file('../tidb-pcta-course-ja-lab/scripts/01-demo-auto-increment-03-show.sql')
    result = db.query('SELECT * FROM t2')
    assert len(result) == 4

    # iterate three times demo increment 03 to check auto increment is working
    for i in range(3):
        db.connect()
        db.query_file('../tidb-pcta-course-ja-lab/scripts/01-demo-auto-increment-03-show.sql')
        result = db.query('SELECT max(id) FROM t2')
        print(result)
        if result[0][0] > 300:
            assert True
            return
    # if max(id) is not greater than 300, then fail the test
    assert False