from fastapi import Depends, FastAPI
from sqlalchemy import Column, ForeignKey, Integer, create_engine, String, Integer, Table
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

SQLALCHEMY_DATABASE_URL = "sqlite:///./sql_app.db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


counterparties_documents = Table(
    "counterparties_documents",
    Base.metadata,
    Column("counterparty_id", ForeignKey(
        "counterparties.id"), primary_key=True),
    Column("document_id", ForeignKey(
        "documents.id"), primary_key=True)
)


class Document(Base):
    __tablename__ = "documents"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)

    counterparties = relationship(
        "Counterparty", secondary=counterparties_documents, back_populates="documents")


class Counterparty(Base):
    __tablename__ = "counterparties"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)

    documents = relationship(
        "Document", secondary=counterparties_documents, back_populates="counterparties")


app = FastAPI()

Base.metadata.create_all(bind=engine)

# Run route "/" twice and get:
# ----------------------------------------

# sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) UNIQUE constraint failed: counterparties.id
# [SQL: INSERT INTO counterparties (id, name) VALUES (?, ?)]
# [parameters: (1, 'Someone Limited')]
# (Background on this error at: https://sqlalche.me/e/14/gkpj)


@app.get("/")
async def root(db: SessionLocal = Depends(get_db)):

    db_document = Document(name="Document name", counterparties=[
        Counterparty(id=1, name="Someone Limited")])

    db.add(db_document)
    db.commit()
    db.refresh(db_document)
    return db_document