import sqlalchemy as sa
from sqlalchemy.orm import declarative_base, Session

engine = sa.create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql_199")

Base = declarative_base()


class User(Base):
    __tablename__ = "user"

    user_id = sa.Column(sa.Integer, primary_key=True)
    full_name = sa.Column(sa.String(255), nullable=False)
    type = sa.Column(sa.String(50))

    __mapper_args__ = {
        "polymorphic_identity": "user",
        "polymorphic_on": type,
    }


class Employee(User):
    __tablename__ = User.__tablename__
    __table_args__ = {"schema": "test_schema"}

    id = sa.Column(sa.Integer, sa.ForeignKey("user.user_id"), primary_key=True)
    company_id = sa.Column(sa.Integer)

    __mapper_args__ = {
        "polymorphic_identity": "employee",
    }


Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)

with engine.connect() as conn:
    print(
        f'default schema: {conn.exec_driver_sql("SELECT SCHEMA_NAME()").scalar()}'
    )
    # default schema: dbo

with Session(engine) as sess:
    sess.add(Employee(full_name="Gord Thompson", company_id=123))
    sess.commit()

with engine.connect() as conn:
    print(conn.exec_driver_sql("SELECT * FROM dbo.[user]").all())
    # [(1, 'Gord Thompson', 'employee')]
    print(conn.exec_driver_sql("SELECT * FROM test_schema.[user]").all())
    # [(1, 123)]