import re
from sqlalchemy import sql, util
from sqlalchemy import types as sqltypes

from sqlalchemy import pool, exc
from sqlalchemy.engine import default, reflection

from sqlalchemy_monetdb.base import MonetExecutionContext, \
    MonetIdentifierPreparer
from sqlalchemy_monetdb.compiler import MonetDDLCompiler, MonetTypeCompiler, \
    MonetCompiler
from sqlalchemy_monetdb.monetdb_types import MONETDB_TYPE_MAP

try:
    import alembic

    class MonetImpl(alembic.ddl.impl.DefaultImpl):
        __dialect__ = 'monetdb'
except ImportError:
    pass


class MonetDialect(default.DefaultDialect):
    supports_statement_cache = False
    name = "monetdb"
    driver = "pymonetdb"

    preexecute_pk_sequences = True
    supports_pk_autoincrement = True
    supports_sequences = True
    sequences_optional = True
    supports_native_decimal = True
    supports_default_values = True
    supports_native_boolean = True
    poolclass = pool.SingletonThreadPool
    supports_unicode_statements = True

    statement_compiler = MonetCompiler
    ddl_compiler = MonetDDLCompiler
    execution_ctx_cls = MonetExecutionContext
    preparer = MonetIdentifierPreparer
    type_compiler = MonetTypeCompiler
    default_paramstyle = 'pyformat'

    def __init__(self, **kwargs):
        default.DefaultDialect.__init__(self, **kwargs)

    @classmethod
    def dbapi(cls):
        return __import__("pymonetdb", fromlist="sql")

    def create_connect_args(self, url):
        opts = url.translate_connect_args()
        return [], opts

    def create_execution_context(self, *args, **kwargs):
        return MonetExecutionContext(self, *args, **kwargs)

    def get_table_names(self, connection, schema=None, **kw):
        """Return a list of table names for `schema`."""

        q = """
            SELECT name
            FROM sys.tables
            WHERE system = false
            AND type = 0
            AND schema_id = %(schema_id)s
        """
        args = {"schema_id": self._schema_id(connection, schema)}
        return [row[0] for row in connection.execute(q, args)]

    @reflection.cache
    def get_temp_table_names(self, connection, **kw):
        # 2097 is tmp schema, 30 is table type LOCAL TEMPORARY
        s = "SELECT tables.name FROM sys.tables WHERE schema_id = 2097 AND type = 30"
        rs = connection.execute(s)
        return [row[0] for row in rs]

    def has_table(self, connection, table_name, schema=None):
        # seems like case gets folded in pg_class...
        if schema is None:
            cursor = connection.execute(
                sql.text(
                    "select name "
                    "from sys.tables "
                    "where system = false "
                    "and type = 0 "
                    "and name=:name",
                ).bindparams(
                    sql.bindparam('name', util.text_type(table_name),
                                  type_=sqltypes.Unicode)
                )
            )
        else:
            cursor = connection.execute(
                sql.text(
                    "SELECT tables.name "
                    "FROM sys.tables, sys.schemas "
                    "WHERE tables.system = FALSE "
                    "AND tables.schema_id = schemas.id "
                    "AND type = 0 "
                    "AND tables.name = :name "
                    "AND schemas.name = :schema",
                    bindparams=[
                        sql.bindparam('name',
                                      util.text_type(table_name),
                                      type_=sqltypes.Unicode),
                        sql.bindparam('schema',
                                      util.text_type(schema),
                                      type_=sqltypes.Unicode)]
                )
            )
        return bool(cursor.first())

    def has_sequence(self, connection, sequence_name, schema=None):
        q = """
            SELECT id
            FROM sys.sequences
            WHERE name = %(name)s
            AND schema_id = %(schema_id)s
        """
        args = {
            "name": sequence_name,
            "schema_id": self._schema_id(connection, schema)
        }
        cursor = connection.execute(q, args)
        return bool(cursor.first())

    @reflection.cache
    def _schema_id(self, connection, schema_name):
        """Fetch the id for schema"""

        if schema_name is None:
            schema_name = connection.execute("SELECT current_schema").scalar()

        query = """
                    SELECT id
                    FROM sys.schemas
                    WHERE name = %(schema_name)s
                """
        args = {"schema_name": schema_name}
        cursor = connection.execute(query, args)
        schema_id = cursor.scalar()
        if schema_id is None:
            raise exc.InvalidRequestError(schema_name)
        return schema_id

    @reflection.cache