#!/usr/bin/env python
"""Generate SQL functions from custom YAML definitions
Usage:
  generate_sql.py class <yaml-source>
  generate_sql.py changed_tiles <yaml-source>
  generate_sql.py tables <yaml-source>
  generate_sql.py (-h | --help)
Options:
  -h --help                 Show this screen.
  --version                 Show version.
"""
from collections import namedtuple
from docopt import docopt
import yaml


Class = namedtuple('Class', ['name', 'values'])


def generate_sql_class(source, func_suffix='class'):
    def generate_when_statement(class_name, mapping_values):
        in_statements = ["'{}'".format(value) for value in mapping_values]
        return " " * 12 + "WHEN type IN ({0}) THEN '{1}'".format(
            ','.join(in_statements),
            class_name
        )

    system_name = source['system']['name']
    classes = find_classes(source)

    when_statements = [generate_when_statement(cl, val) for cl, val in classes]

    return """CREATE OR REPLACE FUNCTION {0}_{1}(type VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    RETURN CASE
{2}
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
    """.format(system_name, func_suffix, "\n".join(when_statements))


def find_classes(config):
    for cl_name, mapped_values in config['system']['classes'].items():
        yield Class(cl_name, mapped_values)


Table = namedtuple('Table', ['name', 'buffer', 'min_zoom', 'max_zoom'])


def generate_changed_tiles(
        tables,
        func_name='changed_tiles',
        func_changed_tiles_query='changed_tiles_table'
    ):

    indent = 8 * " "

    def gen_select_stmt(table):
        return "SELECT * FROM {0}('{1}', ts, {2}, {3}, {4})".format(
            func_changed_tiles_query,
            table.name,
            table.buffer,
            table.min_zoom,
            table.max_zoom,
        )

    stmts = [indent + gen_select_stmt(t) for t in tables]
    separator = '\n' + indent + 'UNION\n'
    return """CREATE OR REPLACE FUNCTION {0}(ts timestamp)
RETURNS TABLE (x INTEGER, y INTEGER, z INTEGER) AS $$
BEGIN
    RETURN QUERY (
{1}
    );
END;
$$ language plpgsql;
    """.format(func_name, separator.join(stmts))


def generate_static_table_view(tables, view_name='osm_tables'):

    def gen_select_stmt(table):
        return ("SELECT '{0}' AS table_name,"
        '{1} AS buffer_size,'
        '{2} AS min_zoom,'
        '{3} AS max_zoom').format(table.name, table.buffer,
                                  table.min_zoom, table.max_zoom)

    indent = 4 * " "
    stmts = [indent + gen_select_stmt(t) for t in tables]
    sep = '\n' + indent + 'UNION\n'
    return 'CREATE OR REPLACE VIEW {0} AS (\n{1}\n);'.format(view_name,
                                                             sep.join(stmts))


def find_delete_tables(config, delete_suffix='delete'):
    for src_table in find_tables(config):
        yield Table(src_table.name + '_' + delete_suffix,
                    src_table.buffer,
                    src_table.min_zoom, src_table.max_zoom)


def find_tables_with_deletes(config):
    for src_table in find_tables(config):
        yield src_table

    for delete_table in find_delete_tables(config):
        yield delete_table


def find_tables(config, schema_prefix='osm'):
    for table_name, config_values in config['tables'].items():
        yield Table(schema_prefix + '_' + table_name,config_values['buffer'],
                    config_values['min_zoom'], config_values['max_zoom'])


if __name__ == '__main__':
    args = docopt(__doc__)

    with open(args['<yaml-source>'], 'r') as f:
        source = yaml.load(f)
        if args['class']:
            print(generate_sql_class(source))

        if args['changed_tiles']:
            print(generate_changed_tiles(find_tables_with_deletes(source)))
        if args['tables']:
            print(generate_static_table_view(find_tables(source)))

@@ -408,16 +408,37 @@
  yaml%0A%0A%0A
+SQL_INDENT = 8 * %22 %22%0A
 Class = 
@@ -543,29 +543,24 @@
  def gen
-erate
 _when_st
 atement(
@@ -547,30 +547,25 @@
  gen_when_st
-atemen
+m
 t(class_name
@@ -904,21 +904,16 @@
 %5Bgen
-erate
 _when_st
 atem
@@ -908,22 +908,17 @@
 _when_st
-atemen
+m
 t(cl, va
@@ -1535,29 +1535,8 @@
 ):%0A%0A
-    indent = 8 * %22 %22%0A
 %0A   
@@ -1793,38 +1793,46 @@
 )%0A%0A    stmts = %5B
-indent
+2 * SQL_INDENT
  + gen_select_st
@@ -1869,38 +1869,46 @@
 arator = '%5Cn' + 
-indent
+2 * SQL_INDENT
  + 'UNION%5Cn'%0A   
@@ -2461,29 +2461,8 @@
 m)%0A%0A
-    indent = 4 * %22 %22%0A
     
@@ -2470,22 +2470,26 @@
 tmts = %5B
-indent
+SQL_INDENT
  + gen_s
@@ -2540,14 +2540,18 @@
 ' + 
-indent
+SQL_INDENT
  + '
@@ -3571,24 +3571,24 @@
 s%5B'class'%5D:%0A
+
             
@@ -3621,17 +3621,16 @@
 ource))%0A
-%0A
         

Improve Python code generation