import requests
import pymysql
import json
from urllib.parse import quote

# Подключение к MySQL DB
import time

import sys

connection = pymysql.connect(host='localhost',
                             user='testuser',
                             password='something',
                             db='mapdb',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
results_count = 100

#Название колонок в таблице
tablename = 'places'
name_column = 'name'
location_lat_column = 'lat'
location_lng_column = 'lng'
address_column = 'address'
#Название колонок для расшифрованного адреса, подробнее см. https://developers.google.com/maps/documentation/geocoding/intro#Types
addressEncode_columns = {
        'street_address': 'street_address',
        'route': 'route',
        'intersection': 'intersection',
        'political': 'political',
        'country': 'country',
        'administrative_area_level_1': 'administrative_area_level_1',
        'administrative_area_level_2': 'administrative_area_level_2',
        'administrative_area_level_3': 'administrative_area_level_3',
        'administrative_area_level_4': 'administrative_area_level_4',
        'administrative_area_level_5': 'administrative_area_level_5',
        'locality': 'locality',
        'ward': 'ward',
        'sublocality': 'sublocality',
        'premise': 'premise',
        'subpremise': 'subpremise',
        'natural_feature': 'natural_feature',
        'airport': 'airport',
        'park': 'park',
    }

def getAllAddressColumnString():
    list = []
    for k in addressEncode_columns.values():
        list.append('`' + k + '`')
    return ', '.join(list)

def getJson(url):
    req = requests.get(url)
    out = req.content.decode()
    return json.loads(out)

text = sys.argv[1]
encoded_text = quote(text)
key = "AIzaSyCMjAD1sCZUOXoAleXVEaULAf1q1M5mJgE"
url = f'https://maps.googleapis.com/maps/api/place/textsearch/json?query={encoded_text}&key={key}'
res = getJson(url)

pagetoken = res.get('next_page_token')

while pagetoken is not None and len(res['results']) <= results_count - 20:
    time.sleep(0.1)
    u = f'https://maps.googleapis.com/maps/api/place/textsearch/json?pagetoken={pagetoken}&key={key}'
    j = getJson(u)
    if j['status'] == 'INVALID_REQUEST':
        continue
    res['results'].extend(j['results'])
    pagetoken = j.get('next_page_token')

allAddressColumnString = getAllAddressColumnString()
VERY_NEEDED_VARIABLE = ', '.join(['%s'] * len(addressEncode_columns))
try:
    with connection.cursor() as cursor:
        for o in res['results']:
            u = f"https://maps.googleapis.com/maps/api/geocode/json?address={quote(o['formatted_address'])}&key={key}"
            a = getJson(u)
            address = {}
            if len(a['results']) >= 1:
                for c in a['results'][0]['address_components']:
                    for t in c['types']:
                        address[t] = c['long_name']

            sql = f"INSERT INTO `{tablename}` (`{name_column}`, `{address_column}`, `{location_lat_column}`, " \
                  f"`{location_lng_column}`, {allAddressColumnString}) VALUES (%s, %s, %s, %s, {VERY_NEEDED_VARIABLE}) "

            cursor.execute(sql, (o['name'], o['formatted_address'],
                                 o['geometry']['location']['lat'],
                                 o['geometry']['location']['lng'],
                                address.get('street_address', ''), address.get('route', ''), address.get('intersection', ''), address.get('political', ''),
                                address.get('country', ''), address.get('administrative_area_level_1', ''), address.get('administrative_area_level_2', ''),
                                address.get('administrative_area_level_3', ''), address.get('administrative_area_level_4', ''),
                                address.get('administrative_area_level_5', ''), address.get('locality', ''), address.get('ward', ''), address.get('sublocality', ''),
                                address.get('premise', ''), address.get('subpremise', ''), address.get('natural_feature', ''), address.get('airport', ''), address.get('park', '')))
    connection.commit()
finally:
    connection.close()

print('Founded ' + str(len(res['results'])))