require 'rubygems'
require 'sequel'
require 'time'

DB = Sequel.connect 'sqlite:/'

class Category < Sequel::Model
  set_schema do
    primary_key :id
    text :name
  end

  unless table_exists?
    self.create_table
    self.create :name => "OSS"
    self.create :name => "Proprietary"
  end

  many_to_many :projects, :join_table => :status
end

class Status < Sequel::Model(:status)
  set_schema do
    primary_key :id
    integer :category_id
    integer :project_id
    boolean :status
  end

  unless self.table_exists?
    self.create_table
    self.create :category_id => 1, :project_id => 1, :status => true
    self.create :category_id => 1, :project_id => 2, :status => true
    self.create :category_id => 2, :project_id => 3, :status => true
  end

  before_create do |record|
    record.status = true
  end
end

class Project < Sequel::Model
  set_schema do
    primary_key :id
    text :name
  end

  unless self.table_exists?
    self.create_table
    self.create :name => "Sequel"
    self.create :name => "Ramaze"
    self.create :name => "jQuery"
  end

  many_to_many :categories, :join_table => :status
end

Status.join(:categories, :id=>:category_id).group_and_count(:name).each do |count|
  puts "Category #{count[:'`name`']} has #{count[:count]} active projects"
end

puts

Category.dataset.each do |cat|
  count = Status.filter(:status => true, :category_id => cat.id).count
  puts "Category: #{cat.name} has #{count} active project(s)."
end