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

# define Interface
##PostGIS spatial processing (QGIS 2.x)=group
##Point on surface (PostGIS)=name
##Input_layer=vector
##Output_relation_type=selection Table;View;Materialized View
##Output_schema=string public
##Output_layer=string pg_pointonsurface
##Output_primary_key=string gid
##Output_geometry_column=string geom
##Attributes_to_keep_(comma_separated_list)_[non_working_dummy]=string None
##Add_output_layer_to_map=boolean true

from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4 import *

from qgis.core import *
from qgis.gui import *
from qgis.utils import *

import psycopg2

# get input parameters from GUI
inlayer = processing.getObject(Input_layer)
inlayer_dp = inlayer.dataProvider().dataSourceUri()

# get database parameters (input layer)
db_name = QgsDataSourceURI(inlayer_dp).database()
db_host = QgsDataSourceURI(inlayer_dp).host()
db_port = QgsDataSourceURI(inlayer_dp).port()
db_user = QgsDataSourceURI(inlayer_dp).username()
db_password = QgsDataSourceURI(inlayer_dp).password()

# get input layer parameters
inlayer_schema = QgsDataSourceURI(inlayer_dp).schema()
inlayer_table = QgsDataSourceURI(inlayer_dp).table()
inlayer_uic = QgsDataSourceURI(inlayer_dp).keyColumn
inlayer_geom = QgsDataSourceURI(inlayer_dp).geometryColumn()
inlayer_crs = inlayer.crs().postgisSrid()

# get output relation parameters
outrelationtype = Output_relation_type
if outrelationtype == 0:
    outrelationtype = 'TABLE'
elif outrelationtype == 1:
    outrelationtype = 'VIEW'
elif outrelationtype == 2:
    outrelationtype = 'MATERIALIZED VIEW'

outschema = Output_schema
outlayer = Output_layer
outpk = Output_primary_key
outgeom = Output_geometry_column
outadd = Add_output_layer_to_map

# check input layer
provider = inlayer.dataProvider()
if provider.name() != 'postgres':
        raise RuntimeError('Input layer '' + inlayer.name() + '' is not a PostgreSQL/PostGIS relation :(')

# connect to database
conn = psycopg2.connect( "dbname={0} host={1} port={2} user={3} password={4}".format( db_name, db_host, db_port, db_user, db_password ) )

# create and run query
with conn.cursor() as curs:
    sql = """CREATE {0} "{1}"."{2}" AS SELECT
    row_number() over() AS "{3}",
    ST_PointOnSurface("{4}")::geometry(Point, {5}) AS "{6}"
FROM "{7}"."{8}";
    """.format( outrelationtype, outschema, outlayer, outpk, inlayer_geom, inlayer_crs, outgeom, inlayer_schema, inlayer_table )

    progress.setInfo('Executing SQL statement ... n')
    progress.setInfo(sql)
    curs.execute(sql)
    progress.setInfo('... done. n')

    # create primary key, sequence and spatial index for relation type 'TABLE'
    if outrelationtype == 'TABLE':
        sql_pk = """ALTER TABLE "{0}"."{1}" ADD CONSTRAINT "{1}_pkey" PRIMARY KEY ("{2}");""".format( outschema, outlayer, outpk )
        curs.execute(sql_pk)

        sql_seq = """CREATE SEQUENCE "{0}"."{1}_{2}_seq" OWNED BY "{0}"."{1}"."{2}";
        SELECT SETVAL('"{0}"."{1}_{2}_seq"', (SELECT MAX("{2}") FROM "{0}"."{1}"));
        ALTER TABLE "{0}"."{1}"
        ALTER COLUMN "{2}" SET DEFAULT nextval('"{0}"."{1}_{2}_seq"'::regclass);
        """.format ( outschema, outlayer, outpk )
        curs.execute(sql_seq)

        sql_sidx = """CREATE INDEX "sidx_{1}_{2}" ON "{0}"."{1}" USING GIST ("{2}");""".format( outschema, outlayer, outgeom )
        curs.execute(sql_sidx)

    # commit changes if everything went OK
    conn.commit()

# load layer
if outadd:
    uri = QgsDataSourceURI()
    uri.setConnection( db_host, db_port, db_name, db_user, db_password )
    uri.setDataSource( outschema, outlayer, outgeom, None, outpk )
    vlayer = QgsVectorLayer( uri.uri(), outlayer, "postgres" )
    QgsMapLayerRegistry.instance().addMapLayer(vlayer)