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

Base = declarative_base()

class Calendar(Base):
    __tablename__ = 'calendars'

    uid = Column(String(255), primary_key=True)
    url = Column(String(255))
    name = Column(String(255), index=True)
    website = Column(String(255))
    visible = Column(Boolean, index=True)

    _category = Column(
        Integer,
        ForeignKey(
            'categories.id',
            onupdate='CASCADE',
            ondelete='SET NULL'))
    category = relationship(
        'Category',
        back_populates='calendars',
        order_by='Calendar.name')
    
    entries = relationship(
        'Entry',
        back_populates='calendar',
        cascade="all",
        passive_deletes=True,
        order_by='Entry.begin_date')
    
    @staticmethod
    def fetch(uid, session, name=None, url=None):
        calendar = session.query(Calendar).filter(Calendar.uid == uid).first()
        if not calendar:
            calendar = Calendar()
            calendar.uid = uid
            session.add(calendar)
        if name:
            calendar.name = name
        if url:
            calendar.url = url
        return calendar

class Entry(Base):
    __tablename__ = 'entries'

    id = Column(Integer, primary_key=True)
    uid = Column(String(255), index=True, unique=True)
    begin_date = Column(Date, index=True)
    end_date = Column(Date)
    all_day = Column(Boolean)
    name = Column(String(255))
    location = Column(String(255))

    _calendar = Column(
        String(255),
        ForeignKey(
            'calendars.uid',
            onupdate='CASCADE',
            ondelete='CASCADE'))
    calendar = relationship(
        'Calendar',
        back_populates='entries',
        order_by='Entry.begin_date')
    
    @staticmethod
    def fetch(uid, session):
        entry = session.query(Entry).filter(Entry.uid == uid).first()
        if not entry:
            entry = Entry()
            session.add(entry)
        return entry

class Category(Base):
    __tablename__ = 'categories'

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

    calendars = relationship(
        'Calendar',
        back_populates='category',
        cascade="all",
        passive_deletes=True,
        order_by='Calendar.name')
    
__all__ = ['Calendar', 'Entry', 'Category']