#!/usr/bin/python
# -*- encoding: utf-8 -*-

# © 2012, David Paleino <d.paleino@gmail.com>
#
# Permission is hereby granted, free of charge, to any person
# obtaining a copy of this software and associated documentation
# files (the "Software"), to deal in the Software without
# restriction, including without limitation the rights to use,
# copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the
# Software is furnished to do so, subject to the following
# conditions:
#
# The above copyright notice and this permission notice shall be
# included in all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
# EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
# OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
# NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
# HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
# WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
# FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
# OTHER DEALINGS IN THE SOFTWARE.

from sqlalchemy import *
from sqlalchemy.exc import *
import logging
import sys

user = ''
psw = ''
host = 'localhost'
port = '3306'
dbname = 'magento'

###
# Don't edit after here, unless you know what you're doing.
###

def run(statement):
    """Executes a sqlalchemy statement"""
    return statement.execute()

logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
logging.getLogger('sqlalchemy.orm.unitofwork').setLevel(logging.DEBUG)

db = create_engine('mysql://%s:%s@%s:%s/%s' % (user, psw, host, port, dbname))
#db = create_engine('sqlite:///test.sqlite', echo=True)
meta = MetaData(db)

#table = Table('directory_country_region', meta,
#    Column('region_id', Integer, nullable=False, primary_key=True, autoincrement=True),
#    Column('country_id', String(3), nullable=False),
#    Column('code', String(3), nullable=False),
#    Column('default_name', String(60), nullable=False),
#)
#table.create()
#table = Table('directory_country_region_name', meta,
#    Column('locale', Integer, nullable=False, primary_key=True),
#    Column('region_id', String(60), nullable=False, primary_key=True),
#    Column('name', String(60), nullable=False),
#)
#table.create()

regions = Table('directory_country_region', meta, autoload=True)
names = Table('directory_country_region_name', meta, autoload=True)

for region in open(sys.argv[1]):
    name, prov = region.strip().split('\t')
    run(regions.insert(dict(country_id='IT', code=prov, default_name=name.decode('utf-8'))))

    for row in run(regions.select(regions.c.default_name == name.decode('utf-8'))):
        run(names.insert(dict(locale='en_US', region_id=row[0], name=name.decode('utf-8'))))