from sqlalchemy import String, Integer, ForeignKey, select
from sqlalchemy.orm import mapper, Session

from sqlalchemy import testing

from sqlalchemy.testing import fixtures, eq_
from sqlalchemy.testing.schema import Table, Column


class InheritingSelectablesTest(fixtures.MappedTest):
    @classmethod
    def define_tables(cls, metadata):
        foo = Table('foo', metadata,
                    Column('a', String(30), primary_key=1),
                    Column('b', String(30), nullable=0))

        cls.tables.bar = foo.select(foo.c.b == 'bar').alias('bar')
        cls.tables.baz = foo.select(foo.c.b == 'baz').alias('baz')

    def test_load(self):
        foo, bar, baz = self.tables.foo, self.tables.bar, self.tables.baz
        # TODO: add persistence test also
        testing.db.execute(foo.insert(), a='not bar', b='baz')
        testing.db.execute(foo.insert(), a='also not bar', b='baz')
        testing.db.execute(foo.insert(), a='i am bar', b='bar')
        testing.db.execute(foo.insert(), a='also bar', b='bar')

        class Foo(fixtures.ComparableEntity):
            pass

        class Bar(Foo):
            pass

        class Baz(Foo):
            pass

        mapper(Foo, foo, polymorphic_on=foo.c.b)

        mapper(Baz, baz,
               with_polymorphic=('*',
                                 foo.join(baz, foo.c.b == 'baz').alias('baz')),
               inherits=Foo, inherit_condition=(foo.c.a == baz.c.a),
               inherit_foreign_keys=[baz.c.a],
               polymorphic_identity='baz')

        mapper(Bar, bar,
               with_polymorphic=('*',
                                 foo.join(bar, foo.c.b == 'bar').alias('bar')),
               inherits=Foo, inherit_condition=(foo.c.a == bar.c.a),
               inherit_foreign_keys=[bar.c.a],
               polymorphic_identity='bar')

        s = Session()

        assert [Baz(), Baz(), Bar(), Bar()] == s.query(
            Foo).order_by(Foo.b.desc()).all()
        assert [Bar(), Bar()] == s.query(Bar).all()


class JoinFromSelectPersistenceTest(fixtures.MappedTest):
    """test for [ticket:2885]"""

    @classmethod
    def define_tables(cls, metadata):
        Table('base', metadata,
              Column('id', Integer, primary_key=True,
                     test_needs_autoincrement=True),
              Column('type', String(50)))
        Table('child', metadata,
              # 1. name of column must be different, so that we rely on
              # mapper._table_to_equated to link the two cols
              Column('child_id', Integer, ForeignKey(
                  'base.id'), primary_key=True),
              Column('name', String(50)))

    @classmethod
    def setup_classes(cls):
        class Base(cls.Comparable):
            pass

        class Child(Base):
            pass

    def test_map_to_select(self):
        Base, Child = self.classes.Base, self.classes.Child
        base, child = self.tables.base, self.tables.child

        base_select = select([base]).alias()
        mapper(Base, base_select, polymorphic_on=base_select.c.type,
               polymorphic_identity='base')
        mapper(Child, child, inherits=Base,
               polymorphic_identity='child')

        sess = Session()

        # 2. use an id other than "1" here so can't rely on
        # the two inserts having the same id
        c1 = Child(id=12, name='c1')
        sess.add(c1)

        sess.commit()
        sess.close()

        c1 = sess.query(Child).one()
        eq_(c1.name, 'c1')