from .._compat import basestring
from ..adapters.mssql import MSSQL, MSSQLN, MSSQL3, MSSQL4, MSSQL3N, MSSQL4N, \
    Vertica, Sybase
from ..helpers.methods import varquote_aux
from ..objects import Expression
from .base import SQLDialect
from . import dialects, sqltype_for


@dialects.register_for(MSSQL)
class MSSQLDialect(SQLDialect):
    true = 1
    false = 0
    true_exp = '1=1'
    false_exp = '1=0'
    dt_sep = "T"

    @sqltype_for('boolean')
    def type_boolean(self):
        return 'BIT'

    @sqltype_for('blob')
    def type_blob(self):
        return 'IMAGE'

    @sqltype_for('integer')
    def type_integer(self):
        return 'INT'

    @sqltype_for('bigint')
    def type_bigint(self):
        return 'BIGINT'

    @sqltype_for('double')
    def type_double(self):
        return 'FLOAT'

    @sqltype_for('date')
    def type_date(self):
        return 'DATE'

    @sqltype_for('time')
    def type_time(self):
        return 'CHAR(8)'

    @sqltype_for('datetime')
    def type_datetime(self):
        return 'DATETIME'

    @sqltype_for('id')
    def type_id(self):
        return 'INT IDENTITY PRIMARY KEY'

    @sqltype_for('reference')
    def type_reference(self):
        return 'INT%(null)s%(unique)s, CONSTRAINT %(constraint_name)s ' + \
            'FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ON ' + \
            'DELETE %(on_delete_action)s'

    @sqltype_for('big-id')
    def type_big_id(self):
        return 'BIGINT IDENTITY PRIMARY KEY'

    @sqltype_for('big-reference')
    def type_big_reference(self):
        return 'BIGINT%(null)s%(unique)s, CONSTRAINT %(constraint_name)s' + \
            ' FOREIGN KEY (%(field_name)s) REFERENCES %(foreign_key)s ' + \
            'ON DELETE %(on_delete_action)s'

    @sqltype_for('reference FK')
    def type_reference_fk(self):
        return ', CONSTRAINT FK_%(constraint_name)s FOREIGN KEY ' + \
            '(%(field_name)s) REFERENCES %(foreign_key)s ON DELETE ' + \
            '%(on_delete_action)s'

    @sqltype_for('reference TFK')
    def type_reference_tfk(self):
        return ' CONSTRAINT FK_%(constraint_name)s_PK FOREIGN KEY ' + \
            '(%(field_name)s) REFERENCES %(foreign_table)s ' + \
            '(%(foreign_key)s) ON DELETE %(on_delete_action)s'

    @sqltype_for('geometry')
    def type_geometry(self):
        return 'geometry'

    @sqltype_for('geography')
    def type_geography(self):
        return 'geography'

    def varquote(self, val):
        return varquote_aux(val, '[%s]')

    def update(self, table, values, where=None):
        tablename = self.writing_alias(table)
        whr = ''
        if where:
            whr = ' %s' % self.where(where)
        return 'UPDATE %s SET %s FROM %s%s;' % (
            table.sql_shortref, values, tablename, whr)

    def delete(self, table, where=None):
        tablename = self.writing_alias(table)
        whr = ''
        if where:
            whr = ' %s' % self.where(where)
        return 'DELETE %s FROM %s%s;' % (table.sql_shortref, tablename, whr)

    def select(self, fields, tables, where=None, groupby=None, having=None,
               orderby=None, limitby=None, distinct=False, for_update=False):
        dst, whr, grp, order, limit, upd = '', '', '', '', '', ''
        if distinct is True:
            dst = ' DISTINCT'
        elif distinct:
            dst = ' DISTINCT ON (%s)' % distinct
        if where:
            whr = ' %s' % self.where(where)
        if groupby:
            grp = ' GROUP BY %s' % groupby
            if having:
                grp += ' HAVING %s' % having
        if orderby:
            order = ' ORDER BY %s' % orderby
        if limitby:
            (lmin, lmax) = limitby
            limit = ' TOP %i' % lmax
        if for_update:
            upd = ' FOR UPDATE'
        return 'SELECT%s%s %s FROM %s%s%s%s%s;' % (
            dst, limit, fields, tables, whr, grp, order, upd)

    def left_join(self, val, query_env={}):
        # Left join must always have an ON clause
        if not isinstance(val, basestring):
            val = self.expand(val, query_env=query_env)
        return 'LEFT OUTER JOIN %s' % val

    def random(self):
        return 'NEWID()'

    def cast(self, first, second, query_env={}):
        # apparently no cast necessary in MSSQL
        return first

    def _mssql_like_normalizer(self, term):
        term = term.replace('[', '[[]')
        return term

    def _like_escaper_default(self, term):
        if isinstance(term, Expression):
            return term
        return self._mssql_like_normalizer(
            super(MSSQLDialect, self)._like_escaper_default(term))

    def concat(self, *items, **kwargs):
        query_env = kwargs.get('query_env', {})
        tmp = (self.expand(x, 'string', query_env=query_env) for x in items)
        return '(%s)' % ' + '.join(tmp)

    def regexp(self, first, second, query_env={}):
        second = self.expand(second, 'string', query_env=query_env)
        second = second.replace('\\', '\\\\')
        second = second.replace('%', '\%').replace('*', '%').replace('.', '_')
        return "(%s LIKE %s ESCAPE '\\')" % (
            self.expand(first, query_env=query_env), second)

    def extract(self, first, what, query_env={}):
        return "DATEPART(%s,%s)" % (what,
            self.expand(first, query_env=query_env))

    def epoch(self, val, query_env={}):
        return "DATEDIFF(second, '1970-01-01 00:00:00', %s)" % \
            self.expand(val, query_env=query_env)

    def length(self, val, query_env={}):
        return "LEN(%s)" % self.expand(val, query_env=query_env)

    def aggregate(self, first, what, query_env={}):
        if what == 'LENGTH':
            what = 'LEN'
        return super(MSSQLDialect, self).aggregate(first, what, query_env)

    @property
    def allow_null(self):
        return ' NULL'

    def substring(self, field, parameters, query_env={}):
        return 'SUBSTRING(%s,%s,%s)' % (
            self.expand(field, query_env=query_env), parameters[0],
            parameters[1])

    def primary_key(self, key):
        return 'PRIMARY KEY CLUSTERED (%s)' % key

    def concat_add(self, tablename):
        return '; ALTER TABLE %s ADD ' % tablename

    def drop_index(self, name, table):
        return 'DROP INDEX %s ON %s;' % (self.quote(name), table._rname)

    def st_astext(self, first, query_env={}):
        return '%s.STAsText()' % self.expand(first, query_env=query_env)

    def st_contains(self, first, second, query_env={}):
        return '%s.STContains(%s)=1' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def st_distance(self, first, second, query_env={}):
        return '%s.STDistance(%s)' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def st_equals(self, first, second, query_env={}):
        return '%s.STEquals(%s)=1' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def st_intersects(self, first, second, query_env={}):
        return '%s.STIntersects(%s)=1' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def st_overlaps(self, first, second, query_env={}):
        return '%s.STOverlaps(%s)=1' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def st_touches(self, first, second, query_env={}):
        return '%s.STTouches(%s)=1' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))

    def st_within(self, first, second, query_env={}):
        return '%s.STWithin(%s)=1' % (
            self.expand(first, query_env=query_env),
            self.expand(second, first.type, query_env=query_env))


@dialects.register_for(MSSQLN)
class MSSQLNDialect(MSSQLDialect):
    @sqltype_for('string')
    def type_string(self):
        return 'NVARCHAR(%(length)s)'

    @sqltype_for('text')
    def type_text(self):
        return 'NTEXT'

    def ilike(self, first, second, escape=None, query_env={}):
        if isinstance(second, Expression):
            second = self.expand(second, 'string', query_env=query_env)
        else:
            second = self.expand(second, 'string', query_env=query_env).lower()
            if escape is None:
                escape = '\\'
                second = second.replace(escape, escape * 2)
        if second.startswith("n'"):
            second = "N'" + second[2:]
        return "(%s LIKE %s ESCAPE '%s')" % (
            self.lower(first, query_env), second, escape)


@dialects.register_for(MSSQL3)
class MSSQL3Dialect(MSSQLDialect):
    @sqltype_for('text')
    def type_text(self):
        return 'VARCHAR(MAX)'

    @sqltype_for('time')
    def type_time(self):
        return 'TIME(7)'

    def _rebuild_select_for_limit(self, fields, tables, dst, whr, grp, order,
                                  lmin, lmax):
        f_outer = ['f_%s' % i for i in range(len(fields.split(',')))]
        f_inner = [field for field in fields.split(', ')]
        f_iproxy = ', '.join([
            self._as(o, n) for (o, n) in zip(f_inner, f_outer)])
        f_oproxy = ', '.join(f_outer)
        interp = 'SELECT%s %s FROM (' + \
            'SELECT%s ROW_NUMBER() OVER (%s) AS w_row, %s FROM %s%s%s)' + \
            ' TMP WHERE w_row BETWEEN %i and %i;'
        return interp % (
                dst, f_oproxy, dst, order, f_iproxy, tables, whr, grp,
                lmin, lmax
            )

    def select(self, fields, tables, where=None, groupby=None, having=None,
               orderby=None, limitby=None, distinct=False, for_update=False):
        dst, whr, grp, order, limit, offset, upd = '', '', '', '', '', '', ''
        if distinct is True:
            dst = ' DISTINCT'
        elif distinct:
            dst = ' DISTINCT ON (%s)' % distinct
        if where:
            whr = ' %s' % self.where(where)
        if groupby:
            grp = ' GROUP BY %s' % groupby
            if having:
                grp += ' HAVING %s' % having
        if orderby:
            order = ' ORDER BY %s' % orderby
        if limitby:
            (lmin, lmax) = limitby
            if lmin == 0:
                dst += ' TOP %i' % lmax
            else:
                return self._rebuild_select_for_limit(
                    fields, tables, dst, whr, grp, order, lmin, lmax
                )
        if for_update:
            upd = ' FOR UPDATE'
        return 'SELECT%s %s FROM %s%s%s%s%s%s%s;' % (
            dst, fields, tables, whr, grp, order, limit, offset, upd)


@dialects.register_for(MSSQL4)
class MSSQL4Dialect(MSSQL3Dialect):
    def select(self, fields, tables, where=None, groupby=None, having=None,
               orderby=None, limitby=None, distinct=False, for_update=False):
        dst, whr, grp, order, limit, offset, upd = '', '', '', '', '', '', ''
        if distinct is True:
            dst = ' DISTINCT'
        elif distinct:
            dst = ' DISTINCT ON (%s)' % distinct
        if where:
            whr = ' %s' % self.where(where)
        if groupby:
            grp = ' GROUP BY %s' % groupby
            if having:
                grp += ' HAVING %s' % having
        if orderby:
            order = ' ORDER BY %s' % orderby
        if limitby:
            (lmin, lmax) = limitby
            if lmin == 0:
                dst += ' TOP %i' % lmax
            else:
                if not order:
                    order = ' ORDER BY %s' % self.random
                offset = ' OFFSET %i ROWS FETCH NEXT %i ROWS ONLY' % (
                    lmin, (lmax - lmin))
        if for_update:
            upd = ' FOR UPDATE'
        return 'SELECT%s %s FROM %s%s%s%s%s%s%s;' % (
            dst, fields, tables, whr, grp, order, limit, offset, upd)


@dialects.register_for(MSSQL3N)
class MSSQL3NDialect(MSSQLNDialect, MSSQL3Dialect):
    @sqltype_for('text')
    def type_text(self):
        return 'NVARCHAR(MAX)'


@dialects.register_for(MSSQL4N)
class MSSQL4NDialect(MSSQLNDialect, MSSQL4Dialect):
    @sqltype_for('text')
    def type_text(self):
        return 'NVARCHAR(MAX)'


@dialects.register_for(Vertica)
class VerticaDialect(MSSQLDialect):
    dt_sep = ' '

    @sqltype_for('boolean')
    def type_boolean(self):
        return 'BOOLEAN'

    @sqltype_for('text')
    def type_text(self):
        return 'BYTEA'

    @sqltype_for('json')
    def type_json(self):
        return self.types['string']

    @sqltype_for('blob')
    def type_blob(self):
        return 'BYTEA'

    @sqltype_for('double')
    def type_double(self):
        return 'DOUBLE PRECISION'

    @sqltype_for('time')
    def type_time(self):
        return 'TIME'

    @sqltype_for('id')
    def type_id(self):
        return 'IDENTITY'

    @sqltype_for('reference')
    def type_reference(self):
        return 'INT REFERENCES %(foreign_key)s ON DELETE %(on_delete_action)s'

    @sqltype_for('big-reference')
    def type_big_reference(self):
        return 'BIGINT REFERENCES %(foreign_key)s ON DELETE' + \
            ' %(on_delete_action)s'

    def extract(self, first, what, query_env={}):
        return "DATE_PART('%s', TIMESTAMP %s)" % (what,
            self.expand(first, query_env=query_env))

    def truncate(self, table, mode=''):
        if mode:
            mode = " %s" % mode
        return ['TRUNCATE %s%s;' % (table._rname, mode)]

    def select(self, *args, **kwargs):
        return SQLDialect.select(self, *args, **kwargs)


@dialects.register_for(Sybase)
class SybaseDialect(MSSQLDialect):
    @sqltype_for('string')
    def type_string(self):
        return 'CHAR VARYING(%(length)s)'

    @sqltype_for('date')
    def type_date(self):
        return 'DATETIME'