import os
import shutil
import sqlite3

import tempfile
import urlparse
import urllib2
import subprocess

import logging

import argparse

import csv
import sys

this_dir = os.path.realpath(os.path.dirname(__file__))
sys.path.append(os.path.realpath(os.path.join(os.pardir, os.pardir)))

from geodata.encoding import safe_decode
from geodata.geonames.paths import *

from geodata.file_utils import *
from geodata.log import *

from itertools import islice, chain

log_to_file(sys.stderr)
logger = logging.getLogger('geonames.sqlite')

BASE_URL = 'http://download.geonames.org/export/'

DUMP_URL = urlparse.urljoin(BASE_URL, 'dump/')
ALL_COUNTRIES_ZIP_FILE = 'allCountries.zip'
HIERARCHY_ZIP_FILE = 'hierarchy.zip'
ALTERNATE_NAMES_ZIP_FILE = 'alternateNames.zip'

ZIP_URL = urlparse.urljoin(BASE_URL, 'zip/')

GEONAMES_DUMP_FILES = (ALL_COUNTRIES_ZIP_FILE,
                       HIERARCHY_ZIP_FILE,
                       ALTERNATE_NAMES_ZIP_FILE)

# base_url, local_dir, is_gzipped, local_filename


GEONAMES_FILES = [(DUMP_URL, '', True, ALL_COUNTRIES_ZIP_FILE),
                  (DUMP_URL, '', True, HIERARCHY_ZIP_FILE),
                  (DUMP_URL, '', True, ALTERNATE_NAMES_ZIP_FILE),
                  (ZIP_URL, 'zip', True, ALL_COUNTRIES_ZIP_FILE),
                  ]


def download_file(url, dest):
    logger.info('Downloading file from {}'.format(url))
    subprocess.check_call(['wget', url, '-O', dest])


def admin_ddl(admin_level):
    columns = ['country_code TEXT'] + \
              ['admin{}_code TEXT'.format(i)
               for i in xrange(1, admin_level)]

    create = '''
    CREATE TABLE admin{level}_codes (
    geonames_id INT,
    code TEXT,
    name TEXT,
    {fields}
    )'''.format(level=admin_level,
                fields=''',
    '''.join(columns))

    indices = (
        '''CREATE INDEX admin{}_code_index ON
        admin{}_codes (code)'''.format(admin_level, admin_level),
        '''CREATE INDEX admin{}_gn_id_index ON
        admin{}_codes (geonames_id)'''.format(admin_level, admin_level),
    )

    return (create, ) + indices

geonames_ddl = {
    'geonames': (
        '''CREATE TABLE geonames (
        geonames_id INT PRIMARY KEY,
        name TEXT,
        ascii_name TEXT,
        alternate_names TEXT,
        latitude DOUBLE,
        longitude DOUBLE,
        feature_class TEXT,
        feature_code TEXT,
        country_code TEXT,
        cc2 TEXT,
        admin1_code TEXT,
        admin2_code TEXT,
        admin3_code TEXT,
        admin4_code TEXT,
        population LONG DEFAULT 0,
        elevation INT,
        dem INT,
        timezone TEXT,
        modification_date TEXT)''',
        '''CREATE INDEX feature_code ON
        geonames (feature_code)''',
        '''CREATE INDEX country_code ON
        geonames (country_code)''',
        '''CREATE INDEX admin_codes ON
        geonames (country_code, admin1_code, admin2_code, admin3_code, admin4_code)''',
    ),

    'alternate_names': (
        '''CREATE TABLE alternate_names (
        alternate_name_id INT PRIMARY KEY,
        geonames_id INT,
        iso_language TEXT,
        alternate_name TEXT,
        is_preferred_name BOOLEAN DEFAULT 0,
        is_short_name BOOLEAN DEFAULT 0,
        is_colloquial BOOLEAN DEFAULT 0,
        is_historic BOOLEAN DEFAULT 0)''',
        '''CREATE INDEX geonames_id_index ON
        alternate_names (geonames_id)''',
        '''CREATE INDEX geonames_id_alt_name_index ON
        alternate_names(geonames_id, alternate_name)''',
    ),

    'hierarchy': (
        '''CREATE TABLE hierarchy (
        parent_id INT,
        child_id INT,
        type TEXT
        );''',
        '''CREATE INDEX parent_child_index ON
        hierarchy (parent_id, child_id)''',
        '''CREATE INDEX child_parent_index ON
        hierarchy (child_id, parent_id)''',
    ),

    'postal_codes': (
        '''CREATE TABLE postal_codes (
        country_code TEXT,
        postal_code TEXT,
        place_name TEXT,
        admin1 TEXT,
        admin1_code TEXT,
        admin2 TEXT,
        admin2_code TEXT,
        admin3 TEXT,
        admin3_code TEXT,
        latitude DOUBLE,
        longitude DOUBLE,
        accuracy INT
        )''',
        '''CREATE INDEX post_code_index ON
        postal_codes (country_code, postal_code)''',
        '''CREATE INDEX postal_code_admins ON
        postal_codes (country_code, admin1_code, admin2_code, admin3_code)''',
    ),
    'admin1_codes': admin_ddl(1),
    'admin2_codes': admin_ddl(2),
    'admin3_codes': admin_ddl(3),
    'admin4_codes': admin_ddl(4),

}

geonames_file_table_map = {
    ('', ALL_COUNTRIES_ZIP_FILE): 'geonames',
    ('', ALTERNATE_NAMES_ZIP_FILE): 'alternate_names',
    ('', HIERARCHY_ZIP_FILE): 'hierarchy',
    ('zip', ALL_COUNTRIES_ZIP_FILE): 'postal_codes',
}


country_codes_create_table = (
    'drop table if exists country_codes',
    '''
    create table country_codes as
    select distinct country_code from geonames
    where feature_code in ('PCL', 'PCLI', 'PCLIX', 'PCLD', 'PCLF', 'PCLS', 'TERR')
    ''',
)

proper_countries_create_table = (
    'drop table if exists proper_countries',
    '''
    create table proper_countries as
    select * from geonames
    where feature_code in ('PCL', 'PCLI', 'PCLIX', 'PCLD', 'PCLF', 'PCLS')
    and country_code in (select country_code from country_codes)
    ''',
)

territories_create_table = (
    'drop table if exists territories',
    '''
    create table territories as
    select * from geonames where feature_code = 'TERR'
    and country_code not in (select country_code from proper_countries);
    ''',
)

countries_create_table = (
    'drop table if exists countries',
    '''
    create table countries as
    select * from proper_countries
    union
    select * from territories;
    ''',
    'create index country_geonames_id on countries (geonames_id)',
    'create index conntry_country_code on countries (country_code)',
)

country_alises_create_table = (
    'drop table if exists country_aliases',
    '''
    create table country_aliases as
    select name, country_code
    from countries
    union
    select alternate_name, country_code
    from alternate_names an
    join countries c