##!/usr/bin/env python
from elixir import * # A BadThing(tm) to do :)
from datetime import datetime

class Person(Entity):
  with_fields(
      name = Field(Unicode(32))
      )
  has_many('free_time', of_kind='TimeAvailable')
  has_and_belongs_to_many('groups', of_kind='Group', tablename="groups_people")
  using_options(tablename="people")

class Group(Entity):
  with_fields (
      name = Field(Unicode(32), unique=True)
      )
  has_and_belongs_to_many('people', of_kind='Person', tablename="groups_people")
  using_options(tablename="groups")

  def find_meeting_times(self):
    try:
      if not self.id:
        1/0
    except NameError, ZeroDivisionError:
      raise Exception("Group does not have a id yet")

    secondary_table = Person.mapper.properties["groups"].secondary # There must be a better way to do this...
    query = select([TimeAvailable.c.time, func.count()],
              and_(TimeAvailable.c.person_id == Person.c.id,
                   secondary_table.c.people_id == Person.c.id,
                   secondary_table.c.groups_id == self.id),
              group_by=TimeAvailable.c.time)
    free_times = query.execute().fetchall()
    """
    free_times = {}
    for person in self.people:
      for time in person.free_time:
        try:
          free_times[time.time] += 1
        except KeyError:
          free_times[time.time] = 1
    free_times = free_times.items()
    """
    free_times.sort(lambda a, b: cmp(b[1], a[1]))
    return free_times

class TimeAvailable(Entity):
  # hhmm This would be a lot cleaner if I could extend datetime
  # Too bad I don't know how and the docs don't tell me how
  with_fields (
     time = Field(DateTime)
     )
  belongs_to('person', of_kind='Person')
  using_options(tablename="times")

from sqlalchemy import *
#metadata.connect("sqlite:///./gm.sqlite3")
metadata.connect("sqlite://in_memory")

create_all()
a = Person(name="David")
b = Person(name="Alex")
a.free_time.append(TimeAvailable(time=datetime(2007, 01, 01, 10)))
a.free_time.append(TimeAvailable(time=datetime(2007, 01, 02, 12)))
b.free_time.append(TimeAvailable(time=datetime(2007, 01, 01, 10)))
b.free_time.append(TimeAvailable(time=datetime(2007, 01, 01, 8)))
b.free_time.append(TimeAvailable(time=datetime(2007, 01, 02, 12)))
g = Group(name = "DrProject")
g.people.append(a)
g.people.append(b)
objectstore.flush()

print g.find_meeting_times()