import pytest

import dbconnection.db as dbconnection


@pytest.fixture
def db():
    database = dbconnection.DB()
    database.connect()
    return database

# fail if it runs on TiDB Serverless
def test_clustered_nonclustered(db: dbconnection.DB):
    clusteredSQL = """
    DROP TABLE IF EXISTS test.auto_increment_t1_clustered;
    CREATE TABLE test.auto_increment_t1_clustered (
        id BIGINT PRIMARY KEY AUTO_INCREMENT,
        name CHAR(255),
        varname VARCHAR(200));
    """
    nonclusteredSQL = """
    DROP TABLE IF EXISTS test.t2_nonclustered;
    CREATE TABLE test.t2_nonclustered (
        id BIGINT PRIMARY KEY NONCLUSTERED,
        name CHAR(255),
        varname VARCHAR(200));
    """
    insert_clustered = """
    INSERT INTO test.auto_increment_t1_clustered (name, varname)
        VALUES ('A', 'V1'),
            ('B', 'V1'),
            ('C', 'V2'),
            ('D', 'V2');
    """
    insert_nonclustered = """
    INSERT INTO test.t2_nonclustered (id, name, varname)
        VALUES (50, 'A', 'V1');
    """
    ## clustered 
    db.query(clusteredSQL)
    db.query(insert_clustered)

    result = db.query("""SELECT COUNT(region_id) 
       FROM information_schema.tikv_region_status
       WHERE table_name = 'auto_increment_t1_clustered';""")
    assert result[0][0] == 1

    ## nonclustered
    db.query(nonclusteredSQL)
    db.query(insert_nonclustered)
    result = db.query("""SELECT COUNT(region_id) 
       FROM information_schema.tikv_region_status
       WHERE table_name = 't2_nonclustered';""")
    assert result[0][0] == 2