from elixir import *
from sqlalchemy import *
from sqlalchemy.exceptions import FlushError
import sys

class Member(Entity):
    has_field('gid', Integer)
    has_field('sid', Integer)
    using_table_options(PrimaryKeyConstraint('sid', 'gid')) 

class Times(Entity):
    has_field('time', Integer, primary_key=True)

class Schedule(Entity):
    has_field('sid', Integer)
    has_field('busy_hour', Integer)
    #belongs_to('times', colname='busy_hour', of_kind='Times', required=True)

    using_table_options(PrimaryKeyConstraint('sid', 'busy_hour'),
                        ForeignKeyConstraint(['busy_hour'], [Times.table.c.time]))


def run(stmt):
    rs = stmt.execute()
    for row in rs:
        print row

#print dir(Times.c)
metadata.connect("sqlite:///schedules.sqlite")
create_all()

Times.table.delete().execute()
Schedule.table.delete().execute()
Member.table.delete().execute()
for i in range(0, 24):
    Times(time=i)

objectstore.flush()

#print Times.select()
#Schedule(sid=1,busy_hour=5)
#objectstore.flush()
a= select([Schedule.table.c.sid, Schedule.table.c.busy_hour])
print run(a)

print Times.table.c.time

while(True):
    line = raw_input('> ')
    parts = line.split(' ')
    cmd = parts[0]
    if(cmd == 'when'):
        gid = parts[1]
#        'select time from times left join schedule on '
#        'times.time = schedule.busy_hour where busy_hour is null'
#        students_in_group = select([Group.table.c.sid], Group.table.c.gid == gid).select()
        grouptimes_j = join(Schedule.table, Member.table, and_(Schedule.table.c.sid == Member.table.c.sid, Member.table.c.gid == gid)).select()
        
        joined = outerjoin(Times.table, grouptimes_j, Times.table.c.time == grouptimes_j.c.busy_hour)
#        print joined.select()
        result = select([Times.table.c.time], joined.c.busy_hour == None, from_obj=[joined] )
        result.distinct = True
#        print result
        run(result)
    elif(cmd == 'member'):
        try:
            gid = parts[1]
            sid = parts[2]
            Member(gid=gid, sid=sid)
            objectstore.flush()
        except FlushError:
            print 'duplicate entry already exists in database'
            objectstore.clear()
    elif(cmd == 'busy'):
        try:
            sid = parts[1]
            hour = parts[2]
            Schedule(sid=sid, busy_hour=hour)
            objectstore.flush()
        except FlushError:
            print 'duplicate entry already exists in database'
            objectstore.clear()
#        run(select([Schedule.table.c.sid, Schedule.table.c.busy_hour]))
    elif(cmd in ['quit', 'bye']):
        print 'bye'
        sys.exit()