from sqlalchemy import create_engine,ForeignKey,Column,Date,Integer,String,Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship,backref,sessionmaker

# Course has many sections
# Section belongs to many courses
# Section has a label
# Label belongs to many sections
# Label belogns to many courses
# Section has a label
# Course has a label
# Course has a single video
# Video belongs to a single course

class Database():
	def __init__(self, uri):
		self.uri = f'sqlite:///{uri}'
		self.engine = create_engine(self.uri,echo=True)
		self.session = sessionmaker(bind=self.engine)()
		self.Base = declarative_base()
	
	def add(self,obj):
		self.session.add(obj)
	
	def commit(self):
		self.session.commit()
	
	def create_all(self):
		self.Base.metadata.create_all(self.engine)

db = Database('courses.db')

class CourseSections(db.Base):
    __tablename__ = 'courses_sections'
    course_id = Column(ForeignKey('courses.id'), primary_key=True)
    section_id = Column(ForeignKey('sections.id'), primary_key=True)
    extra_data = Column(String(50))
    course = relationship("Course", back_populates="sections")
    section = relationship("Section", back_populates="courses")

class Course(db.Base):
    __tablename__ = 'courses'
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False, unique=True)
    description = Column(String(250), nullable=False)
    status_id = Column(Integer, ForeignKey('statuses.id')) 
    status = relationship('Status', back_populates='courses')
    gradeable = Column(Boolean, default=False, nullable=False)
    # MANY TO MANY
    sections = relationship("CourseSections", back_populates="course")
    # ONE TO MANY
    course_label_id = Column(Integer, ForeignKey('course_labels.id'))
    course_label = relationship("CourseLabel", back_populates="courses") # remove to make it non-bi-directional
    # ONE TO ONE
    video_id = Column(Integer, ForeignKey('videos.id'))
    video = relationship("Video", back_populates="course") # remove to make it non-bi-directional

class Section(db.Base):
    __tablename__ = 'sections'
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
    description = Column(String(250), nullable=False)
    courses = relationship("CourseSections", back_populates="section")
    section_label_id = Column(Integer, ForeignKey('section_labels.id'))
    section_label = relationship("SectionLabel", back_populates="sections")

class CourseLabel(db.Base):
    __tablename__ = "course_labels"
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
    courses = relationship("Course")

class SectionLabel(db.Base):
    __tablename__ = "section_labels"
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)
    sections = relationship("Section")

class Video(db.Base):
    __tablename__ = "videos"
    id = Column(Integer, primary_key=True)
    url = Column(String(255), nullable=False)
    course = relationship("Course", back_populates="video", uselist=False)

class Status(db.Base):
    __tablename__ = 'statuses'
    id = Column(Integer, primary_key=True)
    label = Column(String(255), nullable=False)
    courses = relationship('Course', back_populates='status')


course = Course(name='Course 1', description="Initial course")
db.add(course)

section = Section(name='Section 1', description="Initial hogwart section")
db.add(section)

course_labels = ["Math","Science","English"]
for cl in course_labels:
	db.add(CourseLabel(name=cl))

section_labels = ["History","Big Ideas","Conclusion"]
for sl in section_labels:
	db.add(SectionLabel(name=sl))

video_urls = ["https://www.youtube.com/watch?v=TMubSggUOVE","https://www.youtube.com/watch?v=grnP3mduZkM","https://www.youtube.com/watch?v=NybHckSEQBI"]
for url in video_urls:
    db.add(Video(url=url))

# Create courses
courses = ["Algebra Course","Physics Course","Poetry Courses"]
for c in courses:
	db.add(Course(name=c, description=f"A description of {c}"))

# Create sections
sections = [
    {"name": "Section 2", "description":"Initial section"},
    {"name":'Section 3', "description":"Another section"}
]
for s in sections:
    sec = Section(name=s['name'], description=f"A description of {s['description']}")
    db.add(sec)

# Join Table
a = CourseSections(extra_data="some data")
a.section = section
course.sections.append(a)

db.create_all()
db.commit()

# QUERIES
all_courses = db.session.query(Course.name).all()
all_sections = db.session.query(Section.name).all()
first_course = db.session.query(Course).first()
first_section = db.session.query(Section).first()

print(first_course)
print(all_sections[0].name)
print(first_section.courses)
print(first_course.sections)
print(all_courses)
print(all_sections)
# print([value for value, in all_courses])

for course_section in first_course.sections:
    print(course_section.extra_data)
    print(course_section.section.name)
    print(course_section.section.description)

#first_course.sections.remove(first_section)
#https://docs.sqlalchemy.org/en/14/orm/relationships.html