require 'mysql'

MYSQL_SERVER="coco.mtld.mobi"
MYSQL_PORT="3306"
MYSQL_USER="user"
MYSQL_PASSWORD="password"
MYSQL_DB="certifications"

def getDataArrayFromFile(file)
    data = []
    buffer_line = ""
    file.each { |line|
        # removes leading spaces
        line.gsub!(/^[\s]+/, "")

        # ignore ending file sign
        next if line =~ /^</

        if line =~ /\\\r\n/
            line.chomp!("\\\r\n")
            buffer_line = buffer_line + line
        else
            if buffer_line.empty?
                data_line = line
            else
                data_line = buffer_line + line
                buffer_line = ""
            end

            next if data_line =~ /^CNMExport/
            data_line_array = data_line.split(">")
            data.push(data_line_array)
        end
    }
    return data
end

def getCompletedTests(data)
    people = []
    data.each { |line|
        if line[0] == "test"
            people.push(line)
        end
    }
    return people
end

def writeResultsIntoDB(people)
    db = Mysql.connect(MYSQL_SERVER, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB, MYSQL_PORT.to_i)
    puts "Server version: " + db.get_server_info
    people.each { |person|
        lastname = db.escape_string(person[1])
        name = db.escape_string(person[2])
        db.query("INSERT INTO certificated_details (lastname, name) VALUES ('" + lastname + "','" + name + "')")
        puts "Added " + name
    }
    db.close if db
end

if ARGV[0]
    f = File.open(ARGV[0], "r")
    data = getDataArrayFromFile(f)
    people = getCompletedTests(data)
    writeResultsIntoDB(people)
else
    puts "pass me a file as argument please"
end