#!/usr/local/bin/python
# -*- coding: utf8 -*-

import cgi, sys, json, re
import psycopg2
import psycopg2.extras
import db_config

output={}

def gettree():
  getname = getvalues.getfirst('name','')
  getlang = getvalues.getfirst('lang','')
  if (getlang==''):
    output['error']='incorrect variables in action=gettree'
    return
  if (re.search('(?![\w-]).',getname) is not None):
    output['error']='name only (a-z A-Z 0-9 - _), action=gettree'
    return

  if (getname==''):
    where = 'poi_catalog.parent is null'
  else:
    where = "'%(getname)s' = any(poi_catalog.parent)" % {"getname":getname}

  cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
  cur.execute("""
    SELECT poi_catalog.id as id, poi_catalog.name as name, poi_dict.name as langname
    FROM
      poi_catalog
      LEFT JOIN poi_dict
      ON poi_catalog.name=poi_dict.item
        AND poi_dict.type = 'o'
        AND poi_dict.lang = %(getlang)s
    WHERE """+where+"""
    ORDER BY
      poi_dict.name
    ;""", {'getlang': getlang})
  data = []
  for record in cur:
    data.append(record)
  output['data']=data


def getcatalog():
  getname = getvalues.getfirst('name','')
  getlang = getvalues.getfirst('lang','')
  if (getname=='' and getlang==''):
    output['error']='incorrect variables in action=getcatalog'
    return

  cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
  cur.execute("""
    SELECT poi_catalog.id as id, poi_catalog.name as name, tparent.parent, tmore.moretags,
      array[akeys(poi_catalog.tags),avals(poi_catalog.tags)] as tags, poi_catalog.poi,
      poi_dict.name as langname, poi_dict.description as langdescription, poi_dict.link as langlink, poi_dict.keywords as langkeywords
    FROM
      poi_catalog
      LEFT JOIN poi_dict
      ON poi_catalog.name=poi_dict.item
        AND poi_dict.type = 'o'
        AND poi_dict.lang = %(getlang)s
      ,
      (SELECT
        Array[array_agg(t5.parent), array_agg(poi_dict.name)] as parent
      FROM
        (SELECT unnest(poi_catalog.parent) as parent
        FROM
          poi_catalog
        WHERE
          poi_catalog.name=%(getname)s) as t5
        LEFT JOIN poi_dict
        ON t5.parent=poi_dict.item
          AND poi_dict.type = 'o'
          AND poi_dict.lang = %(getlang)s) as tparent
      ,
      (SELECT
        Array[array_agg(t6.moretags), array_agg(poi_dict.name)] as moretags
      FROM
        (SELECT unnest(poi_catalog.moretags) as moretags
        FROM
          poi_catalog
        WHERE
          poi_catalog.name=%(getname)s) as t6
        LEFT JOIN poi_dict
        ON t6.moretags=poi_dict.item
          AND poi_dict.type = 'm'
          AND poi_dict.lang = %(getlang)s) as tmore
    WHERE
      poi_catalog.name=%(getname)s
    ;""", {'getname': getname, 'getlang': getlang})
  output['data']=cur.fetchone()

def setcatalog():
  getjsondata = getvalues.getfirst('jsondata','')
  if (getjsondata == ''):
    output['error']='incorrect variable jsondata'
    return
  
  data=json.loads(getjsondata)
  
  output['lang']=data["lang"]
  #gettt = getvalues.getlist('tt[]')
  #output['q']=getvalues



def getmore():
  getlang = getvalues.getfirst('lang','')
  if (getlang==''):
    output['error']='incorrect variables in action=getmore'
    print json.dumps(output)
    return

  cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
  cur.execute("""
    SELECT poi_moretags.id as id, poi_moretags.name as name, poi_dict.name as langname, poi_moretags.type as type,
        poi_moretags.class as class, poi_moretags.tag as tag,
      poi_dict.description as description, poi_dict.link as link, poi_dict.keywords as keywords
    FROM
      poi_moretags
      LEFT JOIN poi_dict
      ON poi_moretags.name=poi_dict.item
        AND poi_dict.type = 'm'
        AND poi_dict.lang = %(getlang)s
  ;""", {'getlang': getlang})
  data = {}
  data['rows']=[]
  for record in cur:
    data['rows'].append(record)
  #data['rows']=rows
  print json.dumps(data)

def setmore():
  getval={}
  getval['oper'] = getvalues.getfirst('oper','')
  getval['name'] = getvalues.getfirst('name','')
  if (re.search('(?![\w-]).',getval['name']) is not None):
    output['error']="Name only a-z A-Z 0-9 - _"
    return
  getval['lang'] = getvalues.getfirst('lang','')
  getval['langname'] = getvalues.getfirst('langname','')
  getval['type'] = getvalues.getfirst('type','')
  getval['class'] = getvalues.getfirst('class','')
  getval['tag'] = getvalues.getfirst('tag','')
  for name in ['name','lang','type','class','tag']:
    if (getval[name] == ''):
      output['error']=("%(name)s is not null"%{'name':name})
      return
  getval['description'] = getvalues.getfirst('description','')
  getval['link'] = getvalues.getfirst('link','')
  getval['keywords'] = getvalues.getfirst('keywords','')
  getval['id'] = getvalues.getfirst('id','')
  
  cur = conn.cursor()
  try:
    if ( getval['oper'] == 'add' ):
      cur.execute("""
        INSERT INTO poi_moretags("name", "class", tag, "type")
          VALUES (%(name)s, %(class)s, %(tag)s, %(type)s);
        INSERT INTO poi_dict(lang, item, "name", description, link, keywords, "type")
          VALUES (%(lang)s, %(name)s, %(langname)s, %(description)s, %(link)s, %(keywords)s, 'm');
      ;""", {'lang': getval['lang'], 'name': getval['name'], 'langname': getval['langname'], 'type': getval['type'], 'class': getval['class'], 'tag': getval['tag'], 'description': getval['description'], 'link': getval['link'], 'keywords': getval['keywords'] })
    elif ( getval['oper'] == 'edit' ):
      for name in ['langname','id']:
        if (getval[name] == ''):
          output['error']=("%(name)s is not null"%{'name':name})
          return
      cur.execute("""
        UPDATE poi_dict
          SET item=%(name)s, "name"=%(langname)s, description=%(description)s, link=%(link)s, keywords=%(keywords)s
          FROM
            poi_moretags
          WHERE
            poi_moretags.id=%(id)s
            AND poi_moretags.name=poi_dict.item
            AND poi_dict.type='m'
            AND poi_dict.lang=%(lang)s;
        UPDATE poi_moretags
            SET "name"=%(name)s, "class"=%(class)s, tag=%(tag)s, "type"=%(type)s
          WHERE 
            id=%(id)s;            
      ;""", {'lang': getval['lang'], 'name': getval['name'], 'langname': getval['langname'], 'type': getval['type'], 'class': getval['class'], 'tag': getval['tag'], 'description': getval['description'], 'link': getval['link'], 'keywords': getval['keywords'], 'id': getval['id'] })      
    
    conn.commit()
  except Exception, e:
    output['error'] = str(e)
  
  

def getmoreval():
  getclass = getvalues.getfirst('class','')
  getlang = getvalues.getfirst('lang','')
  if (getclass=='' and getlang==''):
    output['error']='incorrect variables in action=getmoreval'
    print json.dumps(output)
    return

  cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
  cur.execute("""
    SELECT poi_class.value, poi_dict.name as name, poi_dict.id as id_dict
    FROM
      poi_class
      LEFT JOIN poi_dict
      ON poi_class.name=poi_dict.item
        AND poi_class.value=poi_dict.value
        AND poi_dict.type = 'c'
        AND poi_dict.lang = %(getlang)s
    WHERE poi_class.name = %(getclass)s
    ORDER BY poi_class.value ASC
  ;""", {'getclass': getclass, 'getlang': getlang})
  data = {}
  data['rows']=[]
  for record in cur:
    data['rows'].append(record)
  #data['rows']=rows
  print json.dumps(data)

def setmoreval():
  getval={}
  getval['oper'] = getvalues.getfirst('oper','')
  getval['lang'] = getvalues.getfirst('lang','')
  getval['class'] = getvalues.getfirst('class','')
  getval['value'] = getvalues.getfirst('value','')
  for name in ['lang','class','value']:
    if (getval[name] == ''):
      output['error']=("%(name)s is not null"%{'name':name})
      return
  getval['name'] = getvalues.getfirst('name','')
  getval['id'] = getvalues.getfirst('id','')

  cur = conn.cursor()
  try:
    if ( getval['oper'] == 'add' ):
      cur.execute("""
        INSERT INTO poi_class(name, value)
          VALUES ( %(class)s, %(value)s );
        INSERT INTO poi_dict(lang, item, name, type, value)
          VALUES ( %(lang)s, %(class)s, %(name)s, 'c', %(value)s );
      ;""", {'lang': getval['lang'], 'class': getval['class'], 'value': getval['value'], 'name': getval['name'] })
    elif ( getval['oper'] == 'edit' ):
      for name in ['name','id']:
        if (getval[name] == ''):
          output['error']=("%(name)s is not null"%{'name':name})
          return
      cur.execute("""
        UPDATE poi_class
           SET "value"=%(value)s
          FROM poi_dict
          WHERE poi_class.name=%(class)s
           AND poi_dict.id=%(id)s
           AND poi_dict.value=poi_class.value;
        UPDATE poi_dict
           SET "value"=%(value)s, "name"=%(name)s 
          WHERE id=%(id)s;           
      ;""", {'lang': getval['lang'], 'class': getval['class'], 'value': getval['value'], 'name': getval['name'], 'id': getval['id'] })
      
    conn.commit()
  except Exception, e:
    output['error'] = str(e) #str(sys.exc_info()[1])
    #raise
  #output['data']=getval['oper']


#print "Content-type: text/javascript; Charset=Utf-8\nAccess-Control-Allow-Origin: *\n" # debug
print "Content-type: application/json; Charset=Utf-8\nAccess-Control-Allow-Origin: *\n" # production

conn = psycopg2.connect(host=db_config.site_host, database=db_config.site_database, user=db_config.site_user, password=db_config.site_password)
#psycopg2.extras.register_hstore(conn)

getvalues=cgi.FieldStorage()
getaction = getvalues.getfirst('action','')
output['action']=getaction
if getaction == 'gettree':
  gettree()
elif getaction == 'getcatalog':
  getcatalog()
elif getaction == 'setcatalog':
  setcatalog()
elif getaction == 'getmore':
  getmore()
elif getaction == 'setmore':
  setmore()
elif getaction == 'getmoreval':
  getmoreval()
elif getaction == 'setmoreval':
  setmoreval()
else:
  output['error']='wrong action'

if (getaction <> 'getmore' and getaction <> 'getmoreval'):
  print json.dumps(output)