from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import relationship, backref
import csv
import re

ENGINE = None
Session = None

Base = declarative_base()

### Class declarations go here
class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key = True)
    email = Column(String(64), nullable = True)
    password = Column(String(64), nullable=True)
    age = Column(Integer, nullable=True)
    zipcode = Column(String(15), nullable=True)

    def __init__(self, id= None, email= None,  password = None, age=None, zipcode=None):
        self.id = id
        self.email = email
        self.password = password
        self.age = age
        self.zipcode = zipcode

    @classmethod
    def rowify(cls, row_instance):
         return cls(*row_instance)

class Movie(Base):
    __tablename__ = "movies"
    id = Column(Integer, primary_key = True)
    title = Column(String(64), nullable = True)
    release_date = Column(String(32), nullable = True)
    video_date = Column(String(32), nullable= True)
    imdb_url = Column(String(1000), nullable = True)

    def __init__(self, id = None, title= None, release_date = None, video_date= None, imdb_url= None):
        self.id = id
        self.title = title.decode('latin-1')
        self.release_date = release_date
        self.video_date = video_date
        self.imdb_url = imdb_url

    @classmethod
    def rowify(cls, row_instance):
        movie_title_and_release = row_instance[1]
        # rel = re.search("(\w* \((\d\d\d\d)\))", movie_title_and_release)
        title = re.search("(.*)\s\((\d{4})\)+", movie_title_and_release)
        movie_title = movie_title_and_release[0]
        if title:
            return cls(row_instance[0], title.group (1), title.group(2), row_instance[2], row_instance[4])
        # elif title.group(1) and not title.group(2):
        #     return cls(row_instance[0], title.group(1), None, row_instance[2], row_instance[4])
        else: 
            return cls(row_instance[0], 'UNKNOWN', None, row_instance[2], row_instance[4])
            
class Rating(Base):
    __tablename__ = "ratings"
    id = Column(Integer, primary_key = True)
    movie_id = Column(Integer, ForeignKey('movies.id'))
    user_id = Column(Integer, ForeignKey('users.id'))
    rating = Column(Integer, nullable=True)
    timestamp = Column(String(32), nullable= True)
    user = relationship("User", backref=backref("ratings", order_by=id))
    movie = relationship("Movie", backref=backref("ratings", order_by=id))
    def __init__(self, movie_id= None, user_id= None, rating = None, timestamp = None):
        self.movie_id = movie_id
        self.user_id = user_id
        self.rating = rating
        self.timestamp = timestamp

    @classmethod
    def rowify(cls, row_instance):
         return cls(*row_instance)

### End class declarations

def connect():
    global ENGINE
    global Session 
    ENGINE = create_engine("sqlite:///ratings.db", echo=True)
    Session = sessionmaker(bind=ENGINE)
    Base.metadata.create_all(ENGINE)
    return Session()
#open a file, read a line, parse a line, create an object add to session commit, repeat
def read_row(filename, table_cls):
    session = connect()
    with open(filename, 'rb') as f:
        if table_cls == Rating:
            reader = csv.reader(f, delimiter='\t')
        else:
            reader = csv.reader(f, delimiter='|')
        for row in reader:
            instance_row = table_cls.rowify(row)
            # table_cls(*row_instance)
            # instance_row = table_cls(row[0].split('|'))
            session.add(instance_row)
        session.commit()

    



def main():
    """In case we need this for something"""
    pass
if __name__ == "__main__":
    main()
