require 'sequel'
require 'sequel/extensions/migration'
require 'csv'
require 'tempfile'

module LedgerWeb
  class Database

    def self.connect
      @@db = Sequel.connect(LedgerWeb::Config.instance.get(:database_url))
      self.run_migrations()
    end

    def self.close
      @@db.disconnect
    end

    def self.handle
      @@db
    end

    def self.run_migrations
      Sequel::Migrator.apply(@@db, File.join(File.dirname(__FILE__), "db/migrate"))

      home_migrations = File.join(ENV['HOME'], '.ledger_web', 'migrate')
      if LedgerWeb::Config.should_load_user_config && File.directory?(home_migrations)
        Sequel::Migrator.run(@@db, home_migrations, :table => "user_schema_changes")
      end
    end

    def self.dump_ledger_to_csv
      ledger_bin_path = LedgerWeb::Config.instance.get :ledger_bin_path
      ledger_file = LedgerWeb::Config.instance.get :ledger_file
      ledger_format = LedgerWeb::Config.instance.get :ledger_format
      
      print "    dumping ledger to file...."
      file = Tempfile.new('ledger')
      system "#{ledger_bin_path} -f #{ledger_file} --format='#{ledger_format}' reg > #{file.path}"
      replaced_file = Tempfile.new('ledger')
      replaced_file.write(file.read.gsub('\"', '""'))
      replaced_file.flush

      puts "done"
      return replaced_file
    end
      
    def self.load_database(file)
      counter = 0
      @@db.transaction do
    
        LedgerWeb::Config.instance.run_hooks(:before_load, @@db)
    
        print "    clearing ledger table...."
        @@db["DELETE FROM ledger"].delete
        puts "done"
    
        print "    loading into database...."

        CSV.foreach(file.path) do |row|
          counter += 1
          row = Hash[*[
            :xtn_id,
            :xtn_date,
            :note,
            :account,
            :commodity, 
            :amount,
            :cleared,
            :virtual,
            :tags,
            :cost
          ].zip(row).flatten]

          xtn_date = Date.strptime(row[:xtn_date], '%Y/%m/%d')
    
          row[:xtn_month] = xtn_date.strftime('%Y/%m/01')
          row[:xtn_year]  = xtn_date.strftime('%Y/01/01')
          row[:cost] = row[:cost].gsub(/[^\d\.-]/, '')
    
          row = LedgerWeb::Config.instance.run_hooks(:before_insert_row, row)
          @@db[:ledger].insert(row)
          LedgerWeb::Config.instance.run_hooks(:after_insert_row, row)
        end
    
        puts "    Running after_load hooks"
        LedgerWeb::Config.instance.run_hooks(:after_load, @@db)
      end
      puts "    analyzing ledger table"
      @@db.fetch('VACUUM ANALYZE ledger').all
      puts "done"
      counter
    end
    
    def self.load_prices
      query = <<HERE
        select
          commodity,
          min_date,
          case
            when amount = 0 then max_date
            else now()::date
          end as max_date
        from (
          select
            commodity,
            min(xtn_date) as min_date,
            max(xtn_date) as max_date,
            sum(amount) as amount
          from
            ledger
          group by
            commodity
        ) x
HERE
    
      puts "Deleting prices"
      @@db["DELETE FROM prices"].delete
    
      rows = @@db.fetch(query)
      proc = LedgerWeb::Config.instance.get :price_function
      skip = LedgerWeb::Config.instance.get :price_lookup_skip_symbols
    
      puts "Loading prices"
      rows.each do |row|
        if skip.include?(row[:commodity])
          next
        end
    
        prices = proc.call(row[:commodity], row[:min_date], row[:max_date])
        prices.each do |price|
          @@db[:prices].insert(:commodity => row[:commodity], :price_date => price[0], :price => price[1])
        end
      end
      @@db.fetch("analyze prices").all
      puts "Done loading prices"
    end
  end
end