class InitialSchema < ActiveRecord::Migration

  def self.up
    create_table "users", :force => true do |t|
      t.column :login,            :string, :limit => 40
      t.column :email,            :string, :limit => 100
      t.column :crypted_password, :string, :limit => 40
      t.column :salt,             :string, :limit => 40
      t.column :fullname,         :string, :limit => 60
      # these two added for user activation
      t.column :activation_code,   :string, :limit => 40
      t.column :activated_at,     :datetime
      t.column :created_at,       :datetime
      t.column :updated_at,       :datetime
    end
    
    create_table "roles", :force => true do |t|
      t.column :user_id,          :integer
      t.column :name,             :string, :limit => 40
      t.column :created_at,       :datetime
      t.column :updated_at,       :datetime
    end
   
    # Resource entries
    create_table "resources", :force => true do |t|
      t.column "name",                     :string, :limit => 100
      t.column "byline",                   :string, :limit => 200
      t.column "project_url",              :string, :limit => 200
      t.column "description",              :text
      t.column "contact_name",             :string, :limit => 200
      t.column "contact_email",            :string, :limit => 100
      t.column "contact_other",            :text
      t.column "contact_name_public",      :bool
      t.column "contact_email_public",     :bool
      t.column "contact_other_public",     :bool
      t.column "mailing_list_url",         :string, :limit => 200
      t.column "documentation_url",        :string, :limit => 200
      t.column "auth_key",                 :string, :limit => 18
      t.column "hits",                     :integer, :default => 0
      t.column "outclicks",                :integer, :default => 0
      t.column "year_started",             :date
      t.column "resource_type_id",         :integer
      t.column "viewable",                 :bool, :default => false
      t.column "stage_id",                 :integer
      t.column "userbase",                 :integer
      t.column "developers",               :integer
      t.column "license_id",               :integer
      t.column "os_support_id",            :integer
      t.column "size_scale_id",            :integer
      t.column "support_id",               :integer
      t.column "commercial",               :string, :limit => 255
      t.column "commercial_url",           :string, :limit => 200
      t.column "cost",                     :string, :limit => 255
      t.column "publications",             :integer
      t.column "tech_publications",        :text
      t.column "appl_publications",        :text
      t.column "superceded_id",            :integer      
      t.column "created_at",               :datetime
      t.column "updated_at",               :datetime
    end
    
    # Related = People who use resource A might want to know about these
    #  other resources...
    # Relatedness is a unidirectional association in Simbiome.
    create_table "relatedness", :id=> false, :force => true do |t|
      t.column "resource_id",       :integer
      t.column "related_id",        :integer
    end
    
    # A similar project has similar functionality.
    # Similarity is a unidirectional association in Simbiome
    create_table "similarity",  :id=> false, :force => true do |t|
      t.column "resource_id",       :integer
      t.column "similar_id",        :integer
    end
    
    # Flags let visitors notify the administrators of problems with entries
    # Examples include "Update" and "Remove"
    create_table "flags", :force => true do |t|
      t.column "resource_id",       :integer
      t.column "resource_version",  :integer
      t.column "submit_user_id",    :integer
      t.column "flag_type",         :string
      t.column "comment",           :text      
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    
    create_table "orgs_resources", :id => false, :force => true do |t|
      t.column "org_id",            :integer
      t.column "resource_id",       :integer      
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    
    # Organizations that maintain resources
    create_table "orgs", :force => true do |t|
      t.column "name",              :string, :limit => 250      
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    
    create_table "taggings", :id => false, :force => true do |t|
      t.column "tag_id",            :integer
      t.column "resource_id",       :integer      
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    
    create_table "tags", :force => true do |t|
      t.column "name",              :string, :limit => 100      
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    
    # Categorization of a resource
    # An example is "Software: Algorithm or Reusable Library"
    create_table "resource_types",  :force => true do |t|
      t.column "name",              :string, :limit => 100
      t.column "description",       :text     
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    resource_type_list = [
      "Software: Algorithm or Reusable Library",
      "Software: Simulation Application",
      "Software: Platform, Development Environment",
      "Software: Support Tool",
      "Web / Online Resource",
      "Database or Data Source"
    ]
    resource_type_list.each do |resource_type|
      ResourceType.create( :name => resource_type )
    end
    
    # The stage of development
    create_table "stages", :force => true do |t|
      t.column "name",              :string, :limit => 100
      t.column "description",       :text      
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    stage_list = [
      "0 - Inactive / Retired",
      "1 - Planning",
      "2 - Alpha",
      "3 - Beta",
      "4 - Development with public userbase",
      "5 - Production/Stable",
      "6 - Mature"
    ]
    stage_list.each do |stage|
      Stage.create( :name => stage )
    end
    
    # The degree to which a project or application is supported
    create_table "supports", :force => true do |t|
      t.column "name",              :string, :limit => 100
      t.column "description",       :text    
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    support_list = [
      "0 - None",
      "1 - Informal Support",
      "2 - Organized User base / Mailing lists",
      "3 - Professional Support"
    ]
    support_list.each do |support|
      Support.create( :name => support )
    end
    
    # Describes the physical size on which the project operates
    # e.g., molecular or organism
    create_table "size_scales", :force => true do |t|
      t.column "name",              :string, :limit => 100
      t.column "description",       :text    
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    size_scale_list = [
      "0 - N/A - not size specific",
      "1 - Atomic / Molecular",
      "2 - Functional / Cellular",
      "3 - Organ / Organism"
    ]
    size_scale_list.each do |size_scale|
      SizeScale.create( :name => size_scale )
    end
    
    # The operating systems a project or application is designed to work on
    create_table "os_supports", :force => true do |t|
      t.column "name",              :string, :limit => 100
      t.column "description",       :text     
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    os_support_list = [
      "0 - OS Independent (Written in an interpreted language)",
      "1 - OS Portable (Source code to work with many OS platforms)",
      "2 - All POSIX (Linux/BSD/UNIX-like OSes)",
      "2.1 - Linux",
      "2.2 - Solaris (Sun Solaris (SunOS))",
      "3 - All BSD Platforms (FreeBSD/NetBSD/OpenBSD/Apple Mac OS X)",
      "3.1 - Apple OS X (Apple Mac OS X)",
      "3.2 - FreeBSD",
      "3.3 - NetBSD",
      "3.4 - OpenBSD",
      "4 - All Win32 32-bit MS Windows (95/98/NT/2000/XP)",
      "4.1 -  Win32 32-bit MS Windows (95/98)",
      "4.2 -  Win32 32-bit MS Windows (NT/2000/XP)",
      "4.3 -  Win2K (Microsoft Windows 2000)",
      "4.4 -  WinXP (Microsoft Windows XP)" 
    ]
    os_support_list.each do |os|
      OsSupport.create( :name => os )
    end
    
    # Software license for a resource
    create_table "licenses", :force => true do |t|
      t.column "name",              :string, :limit => 100
      t.column "description",       :text   
      t.column "created_at",        :datetime
      t.column "updated_at",        :datetime
    end
    license_list = [
      "None - no license, public domain",
      "Other - not listed here",
      "Academic Free License (AFL)",
      "Apache License V2.0",
      "Apache Software License",
      "Apple Public Source License",
      "Artistic License",
      "Attribution Assurance License",
      "BSD License",
      "Common Development and Distribution License",
      "Common Public License",
      "Eclipse Public License",
      "Educational Community License",
      "Eiffel Forum License V2.0",
      "Fair License",
      "GNU General Public License (GPL)",
      "GNU Library or Lesser General Public License (LGPL)",
      "IBM Public License",
      "Intel Open Source License",
      "Jabber Open Source License",
      "MIT License",
      "Mozilla Public License 1.0 (MPL)",
      "Mozilla Public License 1.1 (MPL 1.1)",
      "Nethack General Public License",
      "Nokia Open Source License",
      "Open Group Test Suite License",
      "Open Software License",
      "PHP License",
      "Python License (CNRI Python License)",
      "Python Software Foundation License",
      "Qt Public License (QPL)",
      "Reciprocal Public License",
      "Sleepycat License",
      "Sun Industry Standards Source License (SISSL)",
      "Sun Public License",
      "University of Illinois/NCSA Open Source License",
      "W3C License",
      "Zope Public License",
      "wxWindows Library License",
      "zlib/libpng License"
    ]
    license_list.each do |license|
      License.create( :name => license )
    end
    
    # Migrate the monolithic, original resource table to the more normalized set of tables

    begin
      entries = Legacy.find(:all)
    rescue
      puts "\n\n*** No legacy table found ***\n\n"
      entries = nil
    end
    
    if entries 
      puts "\n\n*** Importing from legacy table ***\n\n"
      entries.each do |e|
        r = Resource.new
        r.name = e.name              
        r.byline = e.byline
        r.project_url = e.project_url      
        r.description = e.description
        
        # If resource has an organization, check it against current orgs
        if e.org and not e.org.empty?
          proj_orgs = ( e.org =~ /--/ ) ? e.org.split('--') : [ e.org ]
          proj_orgs.each do |org|
            org.strip!
            matched_org = Org.find_by_name(org)
            if matched_org
              r.orgs << matched_org
            else
              r.orgs << Org.create( :name => org )
            end
          end
        end
             
        r.contact_email = e.contact_email     
        r.contact_name = e.contact_name      
        r.contact_other = e.contact_other
        e.public_settings =~ /(\d):(\d):(\d)/
        r.contact_name_public = ($1 == '0') ? false : true      
        r.contact_email_public = ($2 == '0') ? false : true
        r.contact_other_public = ($3 == '0') ? false : true
      
        r.mailing_list_url = e.mailing_list_url  
        r.documentation_url = e.documentation_url 
        r.auth_key = e.auth          
        r.hits = (e.hits ? e.hits : 0)
        r.outclicks = (e.outclicks ? e.outclicks : 0) 
        r.year_started = Date.new( e.year_started, 1, 1 ) if e.year_started
        r.viewable = (e.entry_disabled != 0) ? false : true
                 
        r.resource_type = ResourceType.find_by_name(e.rtype.strip) if e.rtype
        r.stage = Stage.find_by_name(e.stage.strip) if e.stage
        r.license = License.find_by_name(e.license.strip) if e.license    
        r.os_support = OsSupport.find_by_name(e.os_support.strip) if e.os_support   
        r.size_scale = SizeScale.find_by_name(e.size_scale.strip) if e.size_scale     
        r.support = Support.find_by_name(e.support.strip) if e.support   
      
        r.userbase = e.int_userbase          
        r.developers = e.int_developers        
        r.commercial = e.commercial        
        r.commercial_url = e.commercial_url    
        r.cost = e.cost              
        r.publications = e.int_publications      
        r.tech_publications = e.tech_pubs 
        r.appl_publications = e.appl_pubs
        r.save
      end
    end
    
  end
  
  def self.down
    
    [:users, 
     :roles,
     :resources, 
     :relatedness, 
     :similarity, 
     :flags, 
     :orgs, 
     :orgs_resources, 
     :resource_types, 
     :stages, 
     :supports, 
     :size_scales, 
     :os_supports, 
     :licenses, 
     :tags, 
     :taggings].each do |t|
      begin
        drop_table t
      rescue
        puts "drop table #{t} failed"
      end
    end
  end
end