#!/usr/bin/python

import os
import sys
import stat
import types
import string
import pyfits

from pyPgSQL import PgSQL
from conf import *

__version__ = "0.2.2"

def napoveda():
  print """\
  
Commands:
  install
  uninstall
  tbcreate /path/file.fit /path/table_structure.sql
  tbdrop name_table
  tbcopy name_table1 name_table2
  import name_table /path/table_structure.sql /path/fits
  list
  changepath name_table new_path
  export name_table
  sql
  run name_table function
  
Help:
  COMMAND ?
  COMMAND h
  COMMAND help\
  
  """

def connect(name_db=database):
  try:
    return PgSQL.connect(host=server, database=name_db, user=user, password=password)
  except PgSQL.Error, msg:
    print "Connection to database '%s' failed" % database
    print msg
    sys.exit(1)

def sql_execute(kurzor, prikaz, vysledek=None):
  try:
    kurzor.execute(prikaz)
  except PgSQL.Error, msg:
    print "ERROR in '%s'" % prikaz
    print msg
    sys.exit(1)

  try:
    if (vysledek == "name"):
      colnames = [t[0] for t in kurzor.description] 
      rows = [dict(zip(colnames, tup)) for tup in kursor.fetchall()]
    elif (vysledek == "num"):
      return kurzor.fetchall()
    elif (vysledek == "colnames"):
      return [t[0] for t in kurzor.description] 
    else:
      return vysledek
  except StandardError, msg:
      print "Fetch of all instanaces failed"
      print msg
      sys.exit()

def seznam_souboru(adr, path):
  seznam = []
  adresar = path + adr
  
  for soubor in os.listdir(adresar): 
    typ = os.stat(adresar + soubor)[stat.ST_MODE]
    if stat.S_ISDIR(typ):
      seznam += seznam_souboru(adr + soubor + "/", path)
    elif ((soubor[-3:].upper() == "AT1") or (soubor[-3:].upper() == "FIT")):
      seznam += [adresar + soubor]

  return seznam

def h_install():
  print """\
  
Vytvori v PostgreSQL databazi %s a v ni tabulku admin.\
      
  """ % database

def p_install():
  sql = """

CREATE TABLE admin (
  name varchar(20) NOT NULL,
  path varchar(50) NOT NULL,
  PRIMARY KEY (name)
);

  """
  
  db = connect("test")
  db.autocommit = 1
  kurzor = db.cursor()
  sql_execute(kurzor, "CREATE DATABASE %s" % (database))
  kurzor.close()
  db.close()

  db = connect()
  kurzor = db.cursor()
  sql_execute(kurzor, sql)
  kurzor.close()
  db.commit()
  print "install\nFinish..."

def h_uninstall():
  print """\

Smaze databazi %s.\
      
  """ % database
  
def p_uninstall():
  odpoved = raw_input("Run uninstall? (y/[n]) ")
  
  if (odpoved.lower() in ["y", "yes"]):
    db = connect("test")
    db.autocommit = 1
    kurzor = db.cursor()
    sql_execute(kurzor, "DROP DATABASE %s" % (database))
    kurzor.close()
    print "uninstall\nFinish..."
  else:
    print "uninstall\nAbort..."
  
def h_tbcreate():
  print """\

Jako prvni argument prijima cestu k FIT souboru, ze ktereho
nacte seznam klicovych slov, a na zaklade techto klicovych slov
ulozi do souboru, definovaneho druhym argumentem, sql prikaz
pro vytvoreni tabulky.

Ve vygenerovanem souboru s sql prikazem muzete libovolne menit
ci mazat klicova slova zacinajici na key_.\
      
  """
  
def p_tbcreate():
  fits = sys.argv[2]
  tabulka = sys.argv[3]
  
  try:
    fimg = pyfits.open(fits)
  except IOError, chyba:
    print chyba
    sys.exit(1)
    
  items = fimg[0].header.items()
  
  install = """CREATE TABLE jmeno_tabulky (
  ID SERIAL,
  soubor text NOT NULL,
  stav varchar(1) NULL,
"""
  
  for polozka in items:
    if (polozka[1].__class__ != pyfits.Boolean):
      install += "  key_" + str(polozka[0]).replace("-", "__").lower()
      
      if (polozka[1].__class__ == int): 
        install += " int"
      elif (polozka[1].__class__ == float): 
        install += " float"
      else:
        install += " varchar(72)"
      
      install += " NULL,\n"
  
  install += "  PRIMARY KEY (ID)\n);"
  
  fimg.close()
  
  try:
    soubor = open("%s" % (tabulka), "w")
  except IOError, chyba:
    print chyba
    sys.exit(1)
  
  soubor.write(install)
  soubor.close()
  print "tbcreate %s %s\nFinish..." % (fits, tabulka)
  
def h_import():
  print """\
  
Jako prvni argument prijima nazev tabulky, ktera se vytvori
na zaklade sql prikazu ulozenem v souboru definovanym druhym
argumentem. Do vytvorene tabulky se importuji hlavicky FIT
souboru ulozene v adresari definovanem tretim argumentem.\
      
  """
  
def p_import():
  table = sys.argv[2]
  sql = sys.argv[3]
  path = sys.argv[4]

  if (path[-1] != "/"):
    path = "%s/" % (path)

  soubor = open(sql, "r")
  sql = soubor.read()
  sql = sql.replace("jmeno_tabulky", table)
  soubor.close()

  db = connect()
  kurzor = db.cursor()
  sql_execute(kurzor, sql)
  sql_execute(kurzor, "INSERT INTO admin VALUES ('%s', '%s')" % (table, path))
  data = sql_execute(kurzor, "SELECT * FROM %s " % table, "colnames")
      
  sloupce = ''
  for radek in data:
    sloupce += radek + ","
  
  for soubor in seznam_souboru('', path):
    try:
      print "Importuji " + soubor + "..."
      fimg = pyfits.open(soubor)
    except IOError:
      print "Soubor se nepodarilo otevrit"
      sys.exit(1)
   
    try:
      items = fimg[0].header.items()
    except ValueError, chyba:
      print chyba
    
    soubor = soubor[len(path):]
    names = "soubor, stav"
    values = "'%s', 'P'" % soubor

    for polozka in items:
      nazev = "key_" + str(polozka[0]).replace('-', '__').lower()
      if ((polozka[1].__class__ != pyfits.Boolean) and (sloupce.find(nazev + ',') != -1)):
        if (type(polozka[1]) != type(types.IntType()) and
            type(polozka[1]) != type(types.FloatType()) and
            type(polozka[1]) != type(types.StringType())):
          names += ", %s" % nazev
          values += ", NULL"
        else:
          names += ", %s" % nazev
          values += ", '%s'" % str(polozka[1])
          
    sql_prikaz = "INSERT INTO %s (%s) VALUES (%s)" % (table, names, values)
    sql_execute(kurzor, sql_prikaz)

  kurzor.close()
  db.commit()
  print "import %s %s %s\nFinish..." % (table, sql, path)
      
def h_list():
  print """\
  
Vypise seznam tabulek s FIT hlavickami a cestu k FIT souborum.\
      
  """
  
def p_list():
  db = connect()
  kurzor = db.cursor()
  data = sql_execute(kurzor, "SELECT * FROM admin", "num")

  cara = "+" + 22 * "-" + "+" + 52 * "-" + "+"
  print cara
  print "| %20s | %50s |" % ("name", "path")
  print cara
  for radek in data:
    print "| %20s | %50s |" % (radek[0], radek[1])
  print cara
        
  kurzor.close()
  db.commit()

def h_sql():
  print """\

Spusti psql klienta, ktery umoznuje komunikovat z PostgreSQL
databazovym servrem.\
  
  """
  
def p_sql():
  os.system("psql -W --host=%s %s %s" % (server, database, user))

def h_run():
  print """\

Prvni argument je nazev tabulky na, kterou se ma aplikovat jedna z
nasledujicich funkci, ktera je druhym argumentem:

  repair_date_obs   opravi date_obs v pripade, ze utmiddle presahlo pulnoc
  irepair_date_obs  pouze ukaze jake opravy by provedla predchozi funkce\
  
  """

def p_run():
  tabulka = sys.argv[2]
  funkce = sys.argv[3]
  
  if (funkce == "repair_date_obs"):
    prikaz = "SELECT * FROM repair_date_obs('%s', 'run');" % tabulka
    os.system("""psql -W -c "%s" --host=%s %s %s""" % (prikaz, server, database, user))
  elif (funkce == "irepair_date_obs"):
    prikaz = "SELECT * FROM repair_date_obs('%s', '');" % tabulka
    os.system("""psql -W -c "%s" --host=%s %s %s""" % (prikaz, server, database, user))
  
def h_changepath():
  print """\
  
Zmeni cestu k FIT souborum pro tabulku definovanou prvnim
argumentem.\
  
  """
  
def p_changepath():
  name = sys.argv[2]
  path = sys.argv[3]
  
  if (path[-1] != "/"):
    path = "%s/" % (path)

  db = connect()
  kurzor = db.cursor()
  sql_execute(kurzor, "UPDATE admin SET path = '%s' WHERE name like '%s'" % (path, name))
  kurzor.close()
  db.commit()
  print "changepath %s %s\nFinish..." % (name, path)
    
def h_tbdrop():
  print """\

Smaze tabulku definovanou prvnim argumentem.\
      
  """
  
def p_tbdrop():
  tb = sys.argv[2]
  odpoved = raw_input("Run tbdrop %s? (y/[n]) " % tb)
  
  if (odpoved.lower() in ["y", "yes"]):
    db = connect()
    kurzor = db.cursor()
    sql_execute(kurzor, "DELETE FROM admin WHERE name like '%s'" % (tb))
    sql_execute(kurzor, "DROP TABLE %s" % (tb))
    kurzor.close()
    db.commit()
    print "tbdrop %s\nFinish..." % tb
  else:
    print "tbdrop %s\nAbort..." % tb

def h_tbcopy():
  print """\
  
Zkopiruje tabulku definovanou prvnim argumentem do
tabulky definovanou druhym argumentem.\
  
  """
  
def p_tbcopy():
  tb1 = sys.argv[2]
  tb2 = sys.argv[3]
  
  db = connect()
  kurzor = db.cursor()
  sql_execute(kurzor, "CREATE TABLE %s AS SELECT * FROM %s" % (tb2, tb1))
  data = sql_execute(kurzor, "SELECT * FROM admin WHERE name like '%s'" % (tb1), "num")
  sql_execute(kurzor, "INSERT INTO admin VALUES ('%s', '%s')" % (tb2, data[0][1]))
  kurzor.close()
  db.commit()
  print "tbcopy %s %s\nFinish..." % (tb1, tb2)
    
def h_export():
  print """\

Ulozi zmeny z tabulky definovane prvnim argumentem zpet
do FIT souboru.\
      
  """
  
def p_export():
  table = sys.argv[2]
  
  db = connect()
  kurzor = db.cursor()
  data = sql_execute(kurzor, "SELECT * FROM admin WHERE name like '%s'" % (table), "num")
  path = data[0][1]
  
  key = sql_execute(kurzor, "SELECT * FROM %s " % table, "colnames")
  data = sql_execute(kurzor, "SELECT * FROM %s WHERE stav like 'Z'" % table, "num")
  
  for radek in data:
    try:
      fimg = pyfits.open("%s%s" % (path, radek[1]), mode = 'update')
      print "Update file %s%s..." % (path, radek[1])
    except IOError:
      print "Soubor '%s' se nepodarilo otevrit" % (radek[1])
      sys.exit(1)
  
    i = 3
    for radek_key in key:
      if ((radek_key.lower() != 'id') and (radek_key.lower() != 'soubor') and (radek_key.lower() != 'stav')):
        klic = radek_key[4:].replace('__', '-').upper() 
        if ((radek[i] != '$') and (radek[i] != None)):
          try:
            fimg[0].header.update(klic, radek[i])
          except ValueError, chyba:
            print chyba
        else:
          del fimg[0].header[klic]
        i += 1
  
    sql_execute(kurzor, "UPDATE %s SET stav='U' WHERE ID=%s" % (table, str(radek[0])))
    db.commit()

    fimg.flush(output_verify="warn")
    fimg.close(output_verify="ignore")

  kurzor.close()
  print "export %s\nFinish..." % table

def main():
  run = { "tbcreate": [p_tbcreate, 2, h_tbcreate],
          "tbdrop": [p_tbdrop, 1, h_tbdrop],
          "tbcopy": [p_tbcopy, 2, h_tbcopy],
          "import": [p_import, 3, h_import],
          "install": [p_install, 0, h_install],
          "uninstall": [p_uninstall, 0, h_uninstall],
          "list": [p_list, 0, h_list],
          "sql": [p_sql, 0, h_sql],
          "run": [p_run, 2, h_run],
          "changepath": [p_changepath, 2, h_changepath],
          "export": [p_export, 1, h_export] } 

  pocet = len(sys.argv)
  if (pocet == 1):
    napoveda()
    sys.exit(0)
    
  pocet -= 2
  prikaz = sys.argv[1]
      
  if ((run.has_key(prikaz)) and (pocet == 1) and (sys.argv[2] in ["help", "h", "?"])):
    run[prikaz][2]()
  elif ((run.has_key(prikaz)) and (run[prikaz][1] == pocet)):
    run[prikaz][0]()
  else:
    napoveda()
  
if __name__ == '__main__':
    main()