#!/usr/bin/env python

import json
import os
import sqlite3

json_path = 'www.submarinecablemap.com/public/api/v2/cable/'
json_files = [pos_json for pos_json in os.listdir(json_path) if
        pos_json.endswith('.json')]
json_files.remove('all.json')
json_files.remove('cable-geo.json')

sqlite3_conn = sqlite3.connect('cables.db')
sqlite3_cur = sqlite3_conn.cursor()
sqlite3_cur.execute('''DROP TABLE IF EXISTS cables''')
sqlite3_cur.execute('''CREATE TABLE cables (company varchar unique, count
integer, total_length float, cables varchar)''')

for i in json_files:
    with open(json_path + i, 'r') as json_file:
        json_text = json.load(json_file)
        cable_name = json_text['name']
        cable_owners = json_text['owners']
        cable_owners = [x.strip() for x in cable_owners.split(',')]

        if json_text['length'] == 'n.a.':
            cable_length = float(0)
        else:
            cable_length = float(json_text['length'].replace(' km',
                '').replace(',', ''))

        print('{} ({} km): {}'.format(cable_name, cable_length,
            sorted(cable_owners)))

        for owner in cable_owners:
            sqlite3_cur.execute('''SELECT count FROM cables WHERE company =
                ?''', (owner,))
            count = sqlite3_cur.fetchone()
            if count is None:
                sqlite3_cur.execute('''INSERT INTO cables VALUES (?, ?, ?,
                    ?)''', (owner, 1, cable_length, cable_name))
            else:
                cable_name_comma = ', ' + cable_name
                sqlite3_cur.execute('''
                    UPDATE cables
                    SET
                        count = count + 1,
                        total_length = total_length + ?,
                        cables = cables || ?
                    WHERE company = ?''',
                    (cable_length,cable_name_comma,owner)
                )

        json_file.close()

sqlite3_conn.commit()
sqlite3_conn.close()