from sqlalchemy import Column, ForeignKey, String, create_engine, Text, Integer, DateTime, Table
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import datetime

engine = create_engine('mysql+pymysql://riotdorque:*******@****:3306/patternHelperTests', echo=True)

Base = declarative_base()
metadata = Base.metadata

Session = sessionmaker(bind=engine)
session = Session()


pattern_posts_table = Table('pattern_posts', Base.metadata,
                           Column('pattern_id', Integer, ForeignKey('patterns.id')),
                           Column('post_id', Integer, ForeignKey('pending_posts.id')))


class Pattern(Base):
    __tablename__ = 'patterns'

    id = Column(Integer, primary_key=True)
    patid = Column(String(255), unique=True)
    company = Column(String(255))
    patname = Column(String(255))
    details = Column(Text)
    price = Column(String(50))
    skills = Column(String(255))
    fabric = Column(Text)
    notions = Column(String(255))
    sizing = Column(String(255))
    url = Column(String(255))
    processed = Column(Integer, default=0)

    posts = relationship('PendingPost', secondary=pattern_posts_table, back_populates='posts')

    def __repr__(self):
        return "<Pattern(id='%s', patid='%s', patname='%s')>" % (
            self.id, self.patid, self.patname)


class ImageSet(Base):
    __tablename__ = 'imageurls'

    id = Column(Integer, primary_key=True)
    pid = Column(Integer, ForeignKey('patterns.id'))
    url = Column(String(255))
    pattern = relationship("Pattern", back_populates="imageurls")

    def __repr__(self):
        return "<Image(imgurl='%s')>" % (self.url)


Pattern.imageurls = relationship("ImageSet", order_by=ImageSet.id, back_populates="pattern")


class PendingPost(Base):
    __tablename__ = 'pending_posts'

    id = Column(Integer, primary_key=True)
    created = Column(DateTime, nullable=False)
    idstr = Column(String(25), nullable=False, unique=True)
    author = Column(String(120))
    title = Column(Text(collation='utf8mb4_unicode_ci'))
    link = Column(Text(collation='utf8mb4_unicode_ci'))
    flair = Column(String(150, 'utf8mb4_unicode_ci'))
    processed = Column(Integer)
    comidstr = Column(String(120, 'utf8mb4_unicode_ci'))
    body = Column(Text(collation='utf8mb4_unicode_ci'))

    patterns = relationship('Pattern', secondary=pattern_posts_table, back_populates='patterns')

    def __repr__(self):
        return "<Post(idstr='%s', author='%s', title='%s')>" % (
                                  self.idstr, self.author, self.title)

    
# After importing everything from this file, and running: Base.metadata.create_all(engine) and bringing in my input data I try:

#