#!/usr/bin/env python3
# -*- coding: utf-8 -*-

# grab data to generate summary for surveys of a cave network

import sqlite3
import pandas as pd
import numpy as np

conn = sqlite3.connect(':memory:')
conn.executescript(open('cave.sql').read())
stats = open('survey_stats.dat','w')

# sql to panda
surv = pd.read_sql_query('select ID, NAME from SURVEY', conn)
surv = surv[surv.NAME  != ''] # drop empty survey names
ctli = pd.read_sql_query('select ID, SURVEY_ID, TOPO_DATE, LENGTH from CENTRELINE', conn)
ctli = ctli.dropna() # drop empty date centerlines
topo = pd.read_sql_query('select PERSON_ID, CENTRELINE_ID from TOPO', conn)
team = pd.read_sql_query('select ID, NAME, SURNAME from PERSON', conn)
team = team.drop_duplicates()
team = team.to_numpy()

#iterate over surveys
for index, s in surv.iterrows():
    print(s.NAME)
    stats.write('%s\n'%(s.NAME))
        
    for index, c in ctli.iterrows():
    	if (c.SURVEY_ID == s.ID) :
    		print('      date   ',c.TOPO_DATE)
    		stats.write('      date   %s\n'%(c.TOPO_DATE))
    		print('      length ',c.LENGTH)
    		stats.write('      length %s\n'%(c.LENGTH))
    		for index, t in topo.iterrows():
    			if (t.CENTRELINE_ID == c.ID) :
    				name = team[team[:,0] == t.PERSON_ID]
    				print ('                 {} {}'.format(name[0,1],name[0,2]))
    				stats.write('                 {} {}\n'.format(name[0,1],name[0,2]))
    
stats.close()