#!/usr/bin/env python

import gadfly
import sys
import pickle
import os

def compile(associated_state_post_office_code, pieces):
	if associated_state_post_office_code is None:
		return

	if associated_state_post_office_code == '':
		associated_state_post_office_code = 'XX'

	print 'compiling %s (%d pieces)' % (associated_state_post_office_code, len(pieces))
	compiler = os.popen('./compile_pdf.jy plates_by_state/%s.pdf' % (associated_state_post_office_code), 'w')
	#compiler = os.popen('cat >/tmp/foo', 'w')
	compiler.write(pickle.dumps(pieces))
	compiler.close()


faa_db = gadfly.gadfly("FAA", "Gadfly_databases/FAA")
faa_cursor = faa_db.cursor()

faa_cursor.execute("""
	select
		airports.associated_state_post_office_code,
		airports.associated_city_name,
		airports.location_identifier,
		airports.facility_name,
		approaches.chart_name,
		approaches.pdf_name
	from 
		airports,
		approaches
	where
		airports.location_identifier = approaches.location_identifier
	order by
		associated_state_post_office_code,
		associated_city_name,
		facility_name
""")


associated_state_post_office_code_prev = associated_city_name_prev = location_identifier_prev = None
pieces = []
visited_filenames = {}

for record in faa_cursor.fetchall():
	(
		associated_state_post_office_code,
		associated_city_name,
		location_identifier,
		facility_name,
		chart_name,
		pdf_name
	) = record


	if pdf_name == '':
		continue

	if not associated_state_post_office_code_prev == associated_state_post_office_code:
		compile(associated_state_post_office_code_prev, pieces)

		associated_state_post_office_code_prev = associated_state_post_office_code
		pieces = [{	
			'depth': 1,
			'title': associated_state_post_office_code,
			'open': True,
		}]
		visited_filenames = {}

	if not associated_city_name_prev == associated_city_name:
		associated_city_name_prev = associated_city_name

		pieces.append({
			'depth': 2,
			'title': associated_city_name,
			'open': False,
		})

	if not location_identifier_prev == location_identifier:
		location_identifier_prev = location_identifier

		pieces.append({
			'depth': 3,
			'title': '%s: %s' % (location_identifier, facility_name),
			'open': False,
		})


	if visited_filenames.has_key(pdf_name):
		page = visited_filenames[pdf_name]

		if not page is None:
			common_piece = pieces[page]
			common_piece['depth'] = 2
			new_pieces = [pieces[0], common_piece]
			if page < len(pieces):
				new_pieces += pieces[1:page]
			if page + 1 < len(pieces):
				new_pieces += pieces[page+1:]
			pieces = new_pieces
			visited_filenames[pdf_name] = None
	else:
		visited_filenames[pdf_name] = len(pieces)

		pieces.append({
			'depth': 4,
			'title': chart_name,
			'filename': '../raw_data/FAA/plates/current/d-TPP/Published_pdfs/%s' % (pdf_name),
			'open': False,
		})

compile(associated_state_post_office_code_prev, pieces)

sys.exit(0)

faa_cursor.execute("""
	select
		*
	from approaches 
""")

#for record in faa_cursor.fetchall():
#	print record