#!/usr/bin/perl
# osm2textdsf -- Create text files suitable for convertion with dsftool from a database populated with osm2pgsql
# Script by Knut Arne Bjorndal, based on CSV2TEXT-DSF by Matteo Hausner
use warnings;
use strict;

use DBI;
use Error qw(:try);
use File::Path;
use POSIX;

my $dbname = 'gis';
my $packagename = 'test';

my %objects = (
	       'amenity' => {
		   'place_of_worship' => { id => 0, obj => 'opensceneryx/objects/buildings/religious/churches/medieval/small.obj' }
		   },
	       'power_source' => {
		   'wind' => { id => 1, obj => 'opensceneryx/objects/buildings/industrial/wind_turbine.obj' },
		   'nuclear' => { id => 2, obj => 'opensceneryx/objects/buildings/industrial/cooling_towers/1.obj' },
		   'coal' => { id => 3, obj => 'opensceneryx/objects/buildings/industrial/power_stations/1.obj' },
		   'fossil' => { id => 4, obj => 'opensceneryx/objects/buildings/industrial/power_stations/1.obj' }
		   },
	       'landuse' => {
		   'industrial' => { id => 5, obj => 'opensceneryx/objects/buildings/industrial/factories/victorian.obj' }
		   },
	       'man_made' => {
		   'water_tower' => { id => 6, obj => 'opensceneryx/objects/buildings/industrial/water_towers/conical/camouflaged.obj' },
		   'gasometer' => { id => 7, obj => 'opensceneryx/objects/buildings/industrial/gasometers/1.obj' },
		   'tower' => { id => 8, obj => '/lib/global8/us/feat_RadioTower_10_10_650r50.obj' },
		   'windmill' => { id => 9, obj => 'opensceneryx/objects/buildings/commercial/windmills/round/2.obj' },
		   'crane' => { id => 10, obj => 'opensceneryx/objects/buildings/industrial/cranes/1.obj' }
	       }
	       );

########## End of configuration ##########

# Connect to database
our $dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "", {RaiseError => 1});

if( defined($ARGV[0]) && defined($ARGV[1]) ){
    my $south = $ARGV[0];
    my $west = $ARGV[1];
    fetch_point_objects_for_tile( $south, $west );
} elsif( defined($ARGV[0]) && $ARGV[0] eq 'all' ){
    my @tilelist = list_tiles_for_database_extent();
    foreach my $tile ( @tilelist ){
	try {
	    fetch_point_objects_for_tile( $tile->{south}, $tile->{west} );
	} otherwise {
	    my $e = shift;
	    # Catch topologyexceptions and make them warnings.
	    if( $e =~ /GEOS pointonsurface\(\) threw an error!/ ){
		warn "Caught geometry exception during tile run, tile skipped.\n$e";
	    } else {
		$e->throw(); # Re-throw
	    }
	};
    }
} else {
    die usage();
}

sub list_tiles_for_database_extent {
    print "Fetching extent of data loaded into database...\n";
    my $sth = $dbh->prepare('SELECT ST_XMin(extent), ST_XMax(extent), ST_YMin(extent), ST_YMax(extent) FROM ( SELECT ST_extent(way) AS extent FROM planet_osm_point ) AS e');
    $sth->execute();

    my $row = $sth->fetchrow_arrayref();
    my ( $xmin, $xmax, $ymin, $ymax ) = @{ $row };

    # Round towards the middle of the extent. Move away from 0,0 to make the math easier
    print "xmin = $xmin xmax = $xmax ymin = $ymin ymax = $ymax\n";
    $xmin += 180;
    $xmax += 180;
    $ymin += 90;
    $ymax += 90;

    $xmin = ceil($xmin);
    $xmax = floor($xmax)-1;
    $ymin = ceil($ymin);
    $ymax = floor($ymax)-1;

    $xmin -= 180;
    $xmax -= 180;
    $ymin -= 90;
    $ymax -= 90;
    print "xmin = $xmin xmax = $xmax ymin = $ymin ymax = $ymax\n";

    my @list;
    foreach my $x ( $xmin .. $xmax ){
	foreach my $y ( $ymin .. $ymax ){
	    push( @list, { west => $x, south => $y } );
	}
    }

    return @list;
}

sub fetch_point_objects_for_tile {
    my $south = shift;
    my $west = shift;

    # Build SQL id CASE and WHERE clauses from %objects
    my $sql_tags_case = 'CASE ';
    my $sql_tags_where = '';
    foreach my $key ( keys( %objects ) ){
	foreach my $value ( keys( %{$objects{$key}} ) ){
	    my $id = $objects{$key}{$value}{id};
	    $sql_tags_case .= "WHEN $key='$value' THEN $id ";
	    $sql_tags_where .= ($sql_tags_where ? ' OR' : '') . " $key='$value'";
	}
    }
    $sql_tags_case .= "END";
    
    # Create postgis box for our current tile
    my $north = $south + 1;
    my $east = $west + 1;
    my $sql_bbox = "SetSRID('BOX($west $south, $east $north)'::box2d, 4326)";

    # SQL CASE for direction.
    # Use random() between 0 and 360 except for windmills, lock them to 270
    my $sql_direction = "CASE WHEN power_source='wind' THEN 270 WHEN man_made='windmill' THEN 270 ELSE random()*360 END";

    # Put together the SQL query
    my $sql =
	"SELECT ST_AsText(way), $sql_tags_case AS id, $sql_direction AS direction " .
	"FROM planet_osm_point " .
	"WHERE ST_CoveredBy(way, $sql_bbox) AND ( $sql_tags_where ) " .
	"UNION " .
	"SELECT ST_AsText(ST_PointOnSurface(ST_Intersection(way, $sql_bbox))) AS way, $sql_tags_case AS id, $sql_direction AS direction " .
	"FROM planet_osm_polygon " .
	"WHERE ST_Intersects(way, $sql_bbox) AND ( $sql_tags_where ) "
	;

    #DEBUG print $sql."\n";

    # Run query against database
    my $sth = $dbh->prepare($sql);
    $sth->execute();

    my @output_objects;
    while( my $row = $sth->fetchrow_arrayref() ){
	# row is a pointer to an array: [way, id from case, direction from case]

	# Parse point into lon/lat
	$row->[0] =~ /^POINT\((-?\d+(?:\.\d+)) (-?\d+(?:\.\d+))\)$/ or die "Invalid point $row->[0]";
	my $lon = $1;
	my $lat = $2;

	push(@output_objects, "OBJECT $row->[1] $lon $lat $row->[2]\n");
    }
    print scalar(@output_objects) . " objects in tile $south,$west.";
    
    # Write to file, but only if there is something to write
    if( @output_objects > 0 ){
	# Create folder hierarchy
	my $south_rounded = floor( $south/10 ) * 10;
	my $west_rounded = floor( $west/10 ) * 10;

	my $path = sprintf("output/Custom Scenery/$packagename/Earth nav data/%+03d%+04d", $south_rounded, $west_rounded);
	mkpath( $path );

	my $filename = sprintf("$path/%+03d%+04d.txt", $south, $west);
	print " Writing to $filename.\n";
	open( OUTPUT, ">$filename" ) or die "Error opening $filename for writing: $1\n";

	# Write file header
	print OUTPUT "I\n";
	print OUTPUT "800\n";
	print OUTPUT "DSF2TEXT\n";
	print OUTPUT "\n";
	print OUTPUT "#$filename\n";
	print OUTPUT "\n";
	print OUTPUT "PROPERTY sim/planet earth\n";
	print OUTPUT "PROPERTY sim/overlay 1\n";
	print OUTPUT "PROPERTY sim/require_object 1/0\n";
	print OUTPUT "PROPERTY sim/require_facade 1/0\n";
	print OUTPUT "PROPERTY sim/creation_agent osm2textdsf\n";
	print OUTPUT "PROPERTY sim/west $west\n";
	print OUTPUT "PROPERTY sim/east $east\n";
	print OUTPUT "PROPERTY sim/north $north\n";
	print OUTPUT "PROPERTY sim/south $south\n";

	# Write object list to file
	my @objectlist;
	foreach my $key ( keys( %objects ) ){
	    foreach my $value ( keys( %{ $objects{$key} } ) ){
		$objectlist[ $objects{$key}{$value}{id} ] = $objects{$key}{$value}{obj};
	    }
	}
	foreach my $object ( @objectlist ){
	    print OUTPUT "OBJECT_DEF $object\n";
	}

	# Write the actual object lines
	print OUTPUT @output_objects;

	close( OUTPUT ) or die "I/O error while writing $filename: $!\n";
    } else {
	print " Not writing empty file\n";
    }
}

sub usage {
    return "$0: { south west | all }\n" .
	"\tsouth\tSouthernmost edge of tile to build\n" .
	"\twest\tWesternmost edge of tile to build\n" .
	"\tall\tPassing all as a single argument makes the script process all tiles within the extent loaded in the database\n";
}
