require 'rubygems'
require 'mysql'
require 'csv'
require 'uuid'

Uuid = UUID.new
DBh = nil

DBh = Mysql.real_connect("localhost", "root", "", "openbravopos")

# Remove the existing products in the database
res = DBh.query("delete from stockcurrent");
res = DBh.query("delete from products_cat");
res = DBh.query("delete from products");
res = DBh.query("delete from categories");

# Recursive for the case when insertion fails do to similar name.
def insert_products(row, val)
	reference = code = row[0]
	cur_val = val == 1 ? '' : ' ' + val.to_s
	name = "#{row[1]}#{cur_val}"
	pricebuy = row[2]
	pricesell = row[3]
	stockvolume = row[4]
	category = row[5]
	taxcat = '001'
	iscom = ''
	isscale = ''
	id = Uuid.generate

	begin
		DBh.query("insert into products (reference, code, name, pricebuy, pricesell, stockvolume, category, taxcat, iscom, isscale, id) values ('#{reference}', '#{code}', '#{name}', '#{pricebuy}', '#{pricesell}', '#{stockvolume}', '#{category}', '#{taxcat}', '#{iscom}', '#{isscale}', '#{id}')")
		#DBh.query("insert into products_cat (product) values ('#{id}')")
		#DBh.query("insert into stockcurrent (product, units, location) values ('#{id}', #{stockvolume}, 0)")
	rescue Mysql::Error => e
		insert_products(row, val + 1)
	ensure
	end
end

# Insert categories
CSV::Reader.parse(File.open('categories.csv', 'rb')) do |row|
	DBh.query("insert into categories (id, name) values ('#{row[0]}', '#{row[1]}')")
end

# Insert the products
CSV::Reader.parse(File.open('products.csv', 'rb')) do |row|
	insert_products(row, 1)
end