#!coding: utf-8

from sqlalchemy import CheckConstraint
from sqlalchemy import Column
from sqlalchemy import column
from sqlalchemy import ForeignKey
from sqlalchemy import Index
from sqlalchemy import inspect
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import schema
from sqlalchemy import select
from sqlalchemy import sql
from sqlalchemy import Table
from sqlalchemy import testing
from sqlalchemy import util
from sqlalchemy.engine import default
from sqlalchemy.sql import compiler
from sqlalchemy.sql import LABEL_STYLE_TABLENAME_PLUS_COL
from sqlalchemy.sql.elements import _anonymous_label
from sqlalchemy.sql.elements import quoted_name
from sqlalchemy.testing import AssertsCompiledSQL
from sqlalchemy.testing import eq_
from sqlalchemy.testing import fixtures
from sqlalchemy.testing import is_
from sqlalchemy.testing.util import picklers


class QuoteExecTest(fixtures.TablesTest):
    __backend__ = True

    @classmethod
    def define_tables(cls, metadata):
        Table(
            "WorstCase1",
            metadata,
            Column("lowercase", Integer, primary_key=True),
            Column("UPPERCASE", Integer),
            Column("MixedCase", Integer),
            Column("ASC", Integer, key="a123"),
        )
        Table(
            "WorstCase2",
            metadata,
            Column("desc", Integer, primary_key=True, key="d123"),
            Column("Union", Integer, key="u123"),
            Column("MixedCase", Integer),
        )

    def test_reflect(self):
        meta2 = MetaData()
        t2 = Table("WorstCase1", meta2, autoload_with=testing.db, quote=True)
        assert "lowercase" in t2.c

        # indicates the DB returns unquoted names as
        # UPPERCASE, which we then assume are unquoted and go to
        # lower case.  So we cannot accurately reflect quoted UPPERCASE
        # names from a "name normalize" backend, as they cannot be
        # distinguished from case-insensitive/unquoted names.
        if testing.db.dialect.requires_name_normalize:
            assert "uppercase" in t2.c
        else:
            assert "UPPERCASE" in t2.c

        # ASC OTOH is a reserved word, which is always quoted, so
        # with that name we keep the quotes on and it stays uppercase
        # regardless.  Seems a little weird, though.
        assert "ASC" in t2.c

        assert "MixedCase" in t2.c

    @testing.provide_metadata
    def test_has_table_case_sensitive(self, connection):
        preparer = testing.db.dialect.identifier_preparer
        conn = connection
        if conn.dialect.requires_name_normalize:
            conn.exec_driver_sql("CREATE TABLE TAB1 (id INTEGER)")
        else:
            conn.exec_driver_sql("CREATE TABLE tab1 (id INTEGER)")
        conn.exec_driver_sql(
            "CREATE TABLE %s (id INTEGER)" % preparer.quote_identifier("tab2")
        )
        conn.exec_driver_sql(
            "CREATE TABLE %s (id INTEGER)" % preparer.quote_identifier("TAB3")
        )
        conn.exec_driver_sql(
            "CREATE TABLE %s (id INTEGER)" % preparer.quote_identifier("TAB4")
        )

        t1 = Table(
            "tab1", self.metadata, Column("id", Integer, primary_key=True)
        )
        t2 = Table(
            "tab2",
            self.metadata,
            Column("id", Integer, primary_key=True),
            quote=True,
        )
        t3 = Table(
            "TAB3", self.metadata, Column("id", Integer, primary_key=True)
        )
        t4 = Table(
            "TAB4",
            self.metadata,
            Column("id", Integer, primary_key=True),
            quote=True,
        )

        insp = inspect(connection)
        assert insp.has_table(t1.name)
        eq_([c["name"] for c in insp.get_columns(t1.name)], ["id"])

        assert insp.has_table(t2.name)
        eq_([c["name"] for c in insp.get_columns(t2.name)], ["id"])

        assert insp.has_table(t3.name)
        eq_([c["name"] for c in insp.get_columns(t3.name)], ["id"])

        assert insp.has_table(t4.name)
        eq_([c["name"] for c in insp.get_columns(t4.name)], ["id"])

    def test_basic(self, connection):
        table1, table2 = self.tables("WorstCase1", "WorstCase2")

        connection.execute(
            table1.insert(),
            [
                {"lowercase": 1, "UPPERCASE": 2, "MixedCase": 3, "a123": 4},
                {"lowercase": 2, "UPPERCASE": 2, "MixedCase": 3, "a123": 4},
                {"lowercase": 4, "UPPERCASE": 3, "MixedCase": 2, "a123": 1},
            ],
        )
        connection.execute(
            table2.insert(),
            [
                {"d123": 1, "u123": 2, "MixedCase": 3},
                {"d123": 2, "u123": 2, "MixedCase": 3},
                {"d123": 4, "u123": 3, "MixedCase": 2},
            ],
        )

        columns = [
            table1.c.lowercase,
            table1.c.UPPERCASE,
            table1.c.MixedCase,
            table1.c.a123,
        ]
        result = connection.execute(select(*columns)).all()
        assert result == [(1, 2, 3, 4), (2, 2, 3, 4), (4, 3, 2, 1)]

        columns = [table2.c.d123, table2.c.u123, table2.c.MixedCase]
        result = con
nection.execute(select(*columns)).all()
        assert result == [(1, 2, 3), (2, 2, 3), (4, 3, 2)]

    def test_use_labels(self, connection):
        table1, table2 = self.tables("WorstCase1", "WorstCase2")
        connection.execute(
            table1.insert(),
            [
                {"lowercase": 1, "UPPERCASE": 2, "MixedCase": 3, "a123": 4},
                {"lowercase": 2, "UPPERCASE": 2, 
"MixedCase": 3, "a123": 4},
                {"lowercase": 4, "UPPERCASE": 3, "MixedCase": 2, "a123": 1},
            ],
        )
        connection.execute(
            table2.insert(),
            [
                {"d123": 1, "u123": 2, "MixedCase": 3},
                {"d123": 2, "u123": 2, "MixedCase": 3},
                {"d123": 4, "u123": 3, "MixedCase": 2},
            ],
        )

        columns = [
            table1.c.lowercase,
            table1.c.UPPERCASE,
            table1.c.MixedCase,
            table1.c.a123,
        ]
        result = connection.execute(
            select(*columns).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
        ).fetchall()
        assert result == [(1, 2, 3, 4), (2, 2, 3, 4), (4, 3, 2, 1)]

        columns = [table2.c.d123, table2.c.u123, table2.c.MixedCase]
        result = connection.execute(
            select(*columns).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
        ).all()
        assert result == [(1, 2, 3), (2, 2, 3), (4, 3, 2)]


class QuoteTest(fixtures.TestBase, AssertsCompiledSQL):
    __dialect__ = "default"

    @testing.crashes("oracle", "FIXME: unknown, verify not fails_on")
    @testing.requires.subqueries
    def test_labels(self):
        """test the quoting of labels.

        If labels aren't quoted, a query in postgresql in particular will
        fail since it produces::

            SELECT
                LaLa.lowercase, LaLa."UPPERCASE", LaLa."MixedCase", LaLa."ASC"
            FROM (
                SELECT DISTINCT
                    "WorstCase1".lowercase AS lowercase,
                    "WorstCase1"."UPPERCASE" AS UPPERCASE,
                    "WorstCase1"."MixedCase" AS MixedCase,
                    "WorstCase1"."ASC" AS ASC
                FROM "WorstCase1"
            ) AS LaLa

        where the "UPPERCASE" column of "LaLa" doesn't exist.
        """

        metadata = MetaData()
        table1 = Table(
            "WorstCase1",
            metadata,
            Column("lowercase", Integer, primary_key=True),
            Column("UPPERCASE", Integer),
            Column("MixedCase", Integer),
            Column("ASC", Integer, key="a123"),
        )
        Table(
            "WorstCase2",
            metadata,
            Column("desc", Integer, primary_key=True, key="d123"),
            Column("Union", Integer, key="u123"),
            Column("MixedCase", Integer),
        )

        self.assert_compile(
            table1.select().distinct().alias("LaLa").select(),
            "SELECT "
            '"LaLa".lowercase, '
            '"LaLa"."UPPERCASE", '
  