require 'rubygems'
require 'postgres'

module ServerSide
  module SQL
    COMMA_SEPARATOR = ', '.freeze
  
    module Schema
      COLUMN_DEF = '%s %s'.freeze
      UNIQUE = ' UNIQUE'.freeze
      NOT_NULL = ' NOT NULL'.freeze
      DEFAULT = ' DEFAULT %s'.freeze
      
      TYPES = Hash.new {|h, k| k}
      TYPES[:double] = 'double precision'
      
      def column_definition(column)
        c = COLUMN_DEF % [column[:name], TYPES[column[:type]]]
        c << UNIQUE if column[:unique]
        c << NOT_NULL if column[:null] == false
        c << DEFAULT % PGconn.quote(column[:default]) if column[:default]
        c
      end
    
      def create_table_column_list(columns)
        columns.map {|c| column_definition(c)}.join(COMMA_SEPARATOR)
      end
      
      CREATE_INDEX = 'CREATE INDEX %s ON %s (%s);'.freeze
      CREATE_UNIQUE_INDEX = 'CREATE UNIQUE INDEX %s ON %s (%s);'.freeze
      INDEX_NAME = '%s_%s_index'.freeze
      UNDERSCORE = '_'.freeze
      
      def index_definition(table_name, index)
        fields = index[:columns].join(COMMA_SEPARATOR)
        index_name = index[:name] || INDEX_NAME %
          [table_name, index[:columns].join(UNDERSCORE)]
        (index[:unique] ? CREATE_UNIQUE_INDEX : CREATE_INDEX) %
          [index_name, table_name, fields]
      end
      
      def create_indexes_sql(table_name, indexes)
        indexes.map {|i| index_definition(table_name, i)}.join
      end
    
      CREATE_TABLE = "CREATE TABLE %s (%s);".freeze
      
      def create_table_sql(name, columns, indexes = nil)
        sql = CREATE_TABLE % [name, create_table_column_list(columns)]
        sql << create_indexes_sql(name, indexes) if indexes && !indexes.empty?
        sql
      end
      
      DROP_TABLE = "DROP TABLE %s;".freeze
      
      def delete_table_sql(name)
        DROP_TABLE % name
      end
      
      class Generator
        :attr_reader :table_name, :primary_key
      
        def initialize(table_name, &block)
          @table_name = table_name
          @primary_key = {:name => :id, :type => :serial}
          @columns = []
          @indexes = []
          instance_eval(&block)
        end
        
        def primary_key(name, type, opts)
          @primary_key = {:name => name, :type => type}.merge(opts)
        end
        
        def column(name, type, opts)
          @columns << {:name => name, :type => type}.merge(opts)
        end
        
        def has_column?(name)
          @columns.each {|c| return true if c[:name] == name}
          false
        end
        
        def index(columns, opts)
          columns = [columns] unless columns.is_a?(Array)
          @indexes << {:columns => columns}.merge(opts)
        end
        
        def to_s
          
          if @primary_key && !has_column?(@primary_key[:name])
            @columns.unshift(@primary_key)
          end
          create_table_sql(@table_name, @columns, @indexes)
        end
      end
    end
  end
end

columns = [
  {:name => :id, :type => :serial},
  {:name => :name, :type => :text, :null => false},
  {:name => :path, :type => :text, :null => false},
  {:name => :kind, :type => :integer},
  {:name => :parent_id, :type => :integer},
  {:name => :producer_id, :type => :integer},
  {:name => :state_quality, :type => :smallint, :null => false, :default => 0},
  {:name => :state_stamp, :type => :double},
  {:name => :state_value, :type => :text},
  {:name => :state_datatype, :type => :smallint}
]

indexes = [
  {:columns => [:path], :unique => true},
  {:columns => [:parent_id]},
  {:columns => [:kind]}
]

include ServerSide::SQL::Schema

puts create_table_sql(:nodes, columns, indexes)

__END__

class Node < ServerSide::Model
  schema :nodes do # this also calls set_table_name with the table name
    primary_key :id # implied if no such declaration exists, and calls set_primary_key
    column :name, :text, :null => false
    column :path, :text, :null => false
    column :kind, :integer
    column :parent_id, :integer
    column :producer_id, :integer
    column :state_quality, :smallint, :null => false, :default => 0
    column :state_stamp, :double
    column :state_value, :text
    column :state_datatype, :smallint
    
    index :path, :unique => true
    index :parent_id
    index :kind
  end
end

DB = Postgres::Database.new

columns = [
  {:name => :name, :type => :text, :null => false},
  {:name => :path, :type => :text, :null => false},
  {:name => :kind, :type => :integer},
  {:name => :parent_id, :type => integer},
  {:name => :producer_id, :type => :integer},
  {:name => :state_quality, :type => :smallint, :null => false, :default => 0},
  {:name => :state_stamp, :type => :double},
  {:name => :state_value, :type => :text},
  {:name => :state_datatype, :type => :smallint}
]

indexes = [
  {:columns => [:path], :unique => true},
  {:columns => [:parent_id]},
  {:columns => [:kind]}
]

DB.create_table(:nodes, columns, indexes)

DB.delete_table(:nodes)

DB.table?(:nodes)