import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT # <-- ADD THIS LINE
import json
import select
#import pg_channels


def ConectDB(u,h,po,pas):
    a=psycopg2.connect(
        user=u,
        host=h,
        port=po,
        password=pas )
    return a

def CreateDB(con,nombre):
    '''
    con CMD:

    cd C:\Program Files\PostgreSQL\10\bin
    psql -U Postgres -h localfost
    create database <nombre>
    '''
    con.set_isolation_level( ISOLATION_LEVEL_AUTOCOMMIT )  # <-- ADD THIS LINE
    cur = con.cursor()
    cmd = "CREATE DATABASE %s  ;" % nombre
    cur.execute( cmd )

    return 0

def ListBD(con):
    cur = con.cursor()
    cmd = """SELECT datname from pg_database"""
    cur.execute( cmd )

    return cur

def ListTable(nombre,u,h,po,pas):
    con=psycopg2.connect(
        dbname=nombre,
        user=u,
        host=h,
        port=po,
        password=pas
        )
    cur = con.cursor()
    cmd = """SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; """
    cur.execute( cmd )

    return cur

def ListColm(nombre,u,h,po,pas,tabla):
    con=psycopg2.connect(
        dbname=nombre,
        user=u,
        host=h,
        port=po,
        password=pas
        )
    cur = con.cursor()
    cmd = """SELECT column_name 
             FROM information_schema.columns 
             WHERE table_name = %s ; """% ("'"+str(tabla)+"'")
    cur.execute( cmd )

    return cur

def DescrTabl(nombre,u,h,po,pas,tabla):
    con=psycopg2.connect(
        dbname=nombre,
        user=u,
        host=h,
        port=po,
        password=pas
        )
    cur = con.cursor()
    cmd = """SELECT * FROM %s ; """% (str(tabla))
    cur.execute( cmd )

    return cur

def AddColm(nombre,u,h,po,pas,tabla):
    con=psycopg2.connect(
        dbname=nombre,
        user=u,
        host=h,
        port=po,
        password=pas
        )
    cur = con.cursor()
    cur.execute("CREATE EXTENSION postgis;")
    cmd = """ALTER TABLE %s 
             ADD COLUMN the_geom geometry(Point,4326)"""% (str(tabla))
    cur.execute( cmd )
    con.commit()

    return cur

def Rellcolm(nombre,u,h,po,pas,tabla):
    con=psycopg2.connect(
        dbname=nombre,
        user=u,
        host=h,
        port=po,
        password=pas
        )
    cur = con.cursor()
    #cur.execute("CREATE EXTENSION postgis;")
    cmd = """UPDATE %s SET the_geom = ST_GeomFromText("Geom",4326)"""% (str(tabla))
    cur.execute( cmd )
    con.commit()
    return cur

def insert_row(nombre,u,h,po,pas,tabla,colum,data):
    con = psycopg2.connect(
        dbname=nombre,
        user=u,
        host=h,
        port=po,
        password=pas
    )

    cur = con.cursor()
    # cur.execute("CREATE EXTENSION postgis;")
    string="("
    for dat in colum:
        string+=dat+","
    string=string[:-1]
    string+=")"
    cmd = """Insert into %s %s VALUES %s"""% (str(tabla),string,tuple(data))
    cur.execute(cmd)
    con.commit()
    return cur

def ComoGJson(nombre,u,h,po,pas,tabla):

    con=psycopg2.connect(
        dbname=nombre,
        user=u,
        host=h,
        port=po,
        password=pas
        )
    cur = con.cursor()
    #cur.execute("CREATE EXTENSION postgis;")
    cmd = """
                (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f))
                As features FROM 
                (SELECT 
                'Feature' As type, 
                ST_AsGeoJSON(lg.the_geom,15,0)::json As geometry,
                row_to_json(lg) As properties
                FROM %s As lg) As f );"""% (str(tabla))
    cur.execute( cmd )
    con.commit()

    gjson = """{
        "type": "FeatureCollection",
        "features": [
            {""" + str( cur.fetchall() )[22:-5] + """            }
            }
        ]
    }"""
    gjson = json.dumps( gjson )

    return gjson

def ComoGjsonMRE(nombre,u,h,po,pas,tabla):

    con=psycopg2.connect(
        dbname=nombre,
        user=u,
        host=h,
        port=po,
        password=pas
        )
    cur = con.cursor()
    #cur.execute("CREATE EXTENSION postgis;")
    cmd = """
                (SELECT 'FeatureCollection' As type, array_to_json(array_agg(f))
                As features FROM 
                (SELECT 
                'Feature' As type, 
                ST_AsGeoJSON(lg.the_geom,15,0)::json As geometry,
                row_to_json(lg) As properties
                FROM %s  As lg WHERE (ST_Intersects(lg.the_geom::geometry,ST_GeomFromText('POLYGON ((0 0, 0 90, 90 90, 90 0, 0 0))',4326)::geometry))) As f )
                ;"""% (str(tabla))
    cur.execute( cmd )
    con.commit()

    gjson = """{
        "type": "FeatureCollection",
        "features": [
            {""" + str( cur.fetchall() )[22:-5] + """            }
            }
        ]
    }"""
    gjson = json.dumps( gjson )

    return gjson

'''def pg_notify(nombre,u,h,po,pas):

    pgc = pg_channels.connect(
        dbname=nombre,
        user=u,
        host=h,
        port=po,
        password=pas)

    #Sending notification events:
    for i in range(4):
        pgc.notify('new_id','Some data: '+str(i))
        import time
        time.sleep(5)

    # Subscribe (listen) to a specific channel
    pgc.listen('new_id')

    # Iterate over any notification events
    for event in pgc.events():
        print(event.payload)


    return 0'''