"""Exercises for eager loading.

Derived from mailing list-reported problems and issue tracker issues.

These are generally very old 0.1-era tests and at some point should
be cleaned up and modernized.

"""
import datetime

import sqlalchemy as sa
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import testing
from sqlalchemy import text
from sqlalchemy.orm import backref
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.testing import eq_
from sqlalchemy.testing import fixtures
from sqlalchemy.testing.fixtures import fixture_session
from sqlalchemy.testing.schema import Column
from sqlalchemy.testing.schema import Table


class EagerTest(fixtures.MappedTest):
    run_deletes = None
    run_inserts = "once"
    run_setup_mappers = "once"

    @classmethod
    def define_tables(cls, metadata):

        Table(
            "owners",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column("data", String(30)),
        )

        Table(
            "categories",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column("name", String(20)),
        )

        Table(
            "tests",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column(
                "owner_id", Integer, ForeignKey("owners.id"), nullable=False
            ),
            Column(
                "category_id",
                Integer,
                ForeignKey("categories.id"),
                nullable=False,
            ),
        )

        Table(
            "options",
            metadata,
            Column(
                "test_id", Integer, ForeignKey("tests.id"), primary_key=True
            ),
            Column(
                "owner_id", Integer, ForeignKey("owners.id"), primary_key=True
            ),
            Column(
                "someoption",
                sa.Boolean,
                server_default=sa.false(),
                nullable=False,
            ),
        )

    @classmethod
    def setup_classes(cls):
        class Owner(cls.Basic):
            pass

        class Category(cls.Basic):
            pass

        class Thing(cls.Basic):
            pass

        class Option(cls.Basic):
            pass

    @classmethod
    def setup_mappers(cls):
        Category, owners, Option, tests, Thing, Owner, options, categories = (
            cls.classes.Category,
            cls.tables.owners,
            cls.classes.Option,
            cls.tables.tests,
            cls.classes.Thing,
            cls.classes.Owner,
            cls.tables.options,
            cls.tables.categories,
        )

        cls.mapper_registry.map_imperatively(Owner, owners)

        cls.mapper_registry.map_imperatively(Category, categories)

        cls.mapper_registry.map_imperatively(
            Option,
            options,
            properties=dict(
                owner=relationship(Owner, viewonly=True),
                test=relationship(Thing, viewonly=True),
            ),
        )

        cls.mapper_registry.map_imperatively(
            Thing,
            tests,
            properties=dict(
                owner=relationship(Owner, backref="tests"),
                category=relationship(Category),
                owner_option=relationship(
                    Option,
                    primaryjoin=sa.and_(
                        tests.c.id == options.c.test_id,
                        tests.c.owner_id == options.c.owner_id,
                    ),
                    foreign_keys=[options.c.test_id, options.c.owner_id],
                    uselist=False,
                ),
            ),
        )

    @classmethod
    def insert_data(cls, connection):
        Owner, Category, Option, Thing = (
            cls.classes.Owner,
            cls.classes.Category,
            cls.classes.Option,
            cls.classes.Thing,
        )

        session = Session(connection)

        o = Owner()
        c = Category(name="Some Category")
        session.add_all(
            (
                Thing(owner=o, category=c),
                Thing(
                    owner=o, category=c, owner_option=Option(someoption=True)
                ),
                Thing(owner=o, category=c, owner_option=Option()),
            )
        )

        session.flush()

    def test_noorm(self, connection):
        """test the control case"""

        tests, options, categories = (
            self.tables.tests,
            self.tables.options,
            self.tables.categories,
        )

        # I want to display a list of tests owned by owner 1
        # if someoption is false or they haven't specified it yet (null)
        # but not if they set it to true (example someoption is for hiding)

        # desired output for owner 1
        # test_id, cat_name
        # 1 'Some Category'
        # 3  "

        # not orm style correct query
        print("Obtaining correct results without orm")
        result = connection.execute(
            sa.select(tests.c.id, categories.c.name)
            .where(
                sa.and_(
                    tests.c.owner_id == 1,
                    sa.or_(
                        options.c.someoption == None,  # noqa
                        options.c.someoption == False,
                    ),
                )
            )
            .order_by(tests.c.id)
            .select_from(
                tests.join(categories).outerjoin(
                    options,
                    sa.and_(
                        tests.c.id == options.c.test_id,
                        tests.c.owner_id == options.c.owner_id,
                    ),
                )
            )
        ).fetchall()
        eq_(result, [(1, "Some Category"), (3, "Some Category")])

    def test_withoutjoinedload(self):
        Thing, tests, options = (
            self.classes.Thing,
            self.tables.tests,
            self.tables.options,
        )

        s = fixture_session()
        result = (
            s.query(Thing)
            .select_from(
                tests.outerjoin(
                    options,
                    sa.and_(
                        tests.c.id == options.c.test_id,
                        tests.c.owner_id == options.c.owner_id,
                    ),
                )
            )
            .filter(
                sa.and_(
                    tests.c.owner_id == 1,
                    sa.or_(
                        options.c.someoption == None,  # noqa
                        options.c.someoption == False,
                    ),
                )
            )
        )

        result_str = ["%d %s" % (t.id, t.category.name) for t in result]
        eq_(result_str, ["1 Some Category", "3 Some Category"])

    def test_withjoinedload(self):
        """
        Test that an joinedload locates the correct "from" clause with which to
        attach to, when presented with a query that already has a complicated
        from clause.

        """

        Thing, tests, options = (
            self.classes.Thing,
            self.tables.tests,
            self.tables.options,
        )

        s = fixture_session()
        q = s.query(Thing).options(sa.orm.joinedload("category"))

        result = q.select_from(
            tests.outerjoin(
                options,
                sa.and_(
                    tests.c.id == options.c.test_id,
                    tests.c.owner_id == options.c.owner_id,
                ),
            )
        ).filter(
            sa.and_(
                tests.c.owner_id == 1,
                sa.or_(
                    options.c.someoption == None,
                    options.c.someoption == False,  # noqa
                ),
            )
        )

        result_str = ["%d %s" % (t.id, t.category.name) for t in result]
        eq_(result_str, ["1 Some Category", "3 Some Category"])

    def test_dslish(self):
        """test the same as withjoinedload except using generative"""

        Thing, tests, options = (
            self.classes.Thing,
            self.tables.tests,
            self.tables.options,
        )

        s = fixture_session()
        q = s.query(Thing).options(sa.orm.joinedload("category"))
        result = q.filter(
            sa.and_(
                tests.c.owner_id == 1,
                sa.or_(
                    options.c.someoption == None,
                    options.c.someoption == False,  # noqa