import sys

from sqlalchemy import create_engine, and_
from sqlalchemy.orm import sessionmaker

from sfa.util.config import Config
from sfa.util.sfalogging import logger

from sqlalchemy import Column, Integer, String, DateTime
from sqlalchemy import Table, Column, MetaData, join, ForeignKey
import sfa.storage.model as model
from sfa.storage.model import RegSlice
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref


from sqlalchemy.dialects import postgresql

from sqlalchemy import MetaData, Table
from sqlalchemy.exc import NoSuchTableError

from sqlalchemy import String
from sfa.storage.alchemy import dbsession

#Dict holding the columns names of the table as keys
#and their type, used for creation of the table
slice_table = {'record_id_user':'integer PRIMARY KEY references X ON DELETE CASCADE ON UPDATE CASCADE','oar_job_id':'integer DEFAULT -1',  'record_id_slice':'integer', 'slice_hrn':'text NOT NULL'}

#Dict with all the specific senslab tables
tablenames_dict = {'slice_senslab': slice_table}

##############################



SlabBase = declarative_base()




class SliceSenslab (SlabBase):
    __tablename__ = 'slice_senslab' 
    #record_id_user = Column(Integer, primary_key=True)

    slice_hrn = Column(String,primary_key=True)
    peer_authority = Column( String,nullable = True)
    record_id_slice = Column(Integer)    
    record_id_user = Column(Integer) 

    #oar_job_id = Column( Integer,default = -1)
    #node_list = Column(postgresql.ARRAY(String), nullable =True)
    
    def __init__ (self, slice_hrn =None, record_id_slice=None, record_id_user= None,peer_authority=None):
        if record_id_slice: 
            self.record_id_slice = record_id_slice
        if slice_hrn:
            self.slice_hrn = slice_hrn
        if record_id_user: 
            self.record_id_user= record_id_user
        if peer_authority:
            self.peer_authority = peer_authority
            
            
    def __repr__(self):
        result="<Record id user =%s, slice hrn=%s, Record id slice =%s ,peer_authority =%s"% \
                (self.record_id_user, self.slice_hrn, self.record_id_slice, self.peer_authority)
        result += ">"
        return result
          
    def dump_sqlalchemyobj_to_dict(self):
        dict = {'slice_hrn':self.slice_hrn,
        'peer_authority':self.peer_authority,
        'record_id':self.record_id_slice, 
        'record_id_user':self.record_id_user,
        'record_id_slice':self.record_id_slice, }
        return dict 
          
             

#class PeerSenslab(SlabBase):
    #__tablename__ = 'peer_senslab' 
    #peername = Column(String, nullable = False)
    #peerid = Column( Integer,primary_key=True)
    
    #def __init__ (self,peername = None ):
        #if peername:
            #self.peername = peername
            
            
      #def __repr__(self):
        #result="<Peer id  =%s, Peer name =%s" % (self.peerid, self.peername)
        #result += ">"
        #return result
          
class SlabDB:
    def __init__(self,config, debug = False):
        self.sl_base = SlabBase
        dbname="slab_sfa"
        if debug == True :
            l_echo_pool = True
            l_echo=True 
        else :
            l_echo_pool = False
            l_echo = False 
        # will be created lazily on-demand
        self.slab_session = None
        # the former PostgreSQL.py used the psycopg2 directly and was doing
        #self.connection.set_client_encoding("UNICODE")
        # it's unclear how to achieve this in sqlalchemy, nor if it's needed at all
        # http://www.sqlalchemy.org/docs/dialects/postgresql.html#unicode
        # we indeed have /var/lib/pgsql/data/postgresql.conf where
        # this setting is unset, it might be an angle to tweak that if need be
        # try a unix socket first - omitting the hostname does the trick
        unix_url = "postgresql+psycopg2://%s:%s@:%s/%s"%\
            (config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_PORT,dbname)
        print >>sys.stderr, " \r\n \r\n SLAPOSTGRES INIT unix_url %s" %(unix_url)
        # the TCP fallback method
        tcp_url = "postgresql+psycopg2://%s:%s@%s:%s/%s"%\
            (config.SFA_DB_USER,config.SFA_DB_PASSWORD,config.SFA_DB_HOST,config.SFA_DB_PORT,dbname)
        for url in [ unix_url, tcp_url ] :
            try:
                self.slab_engine = create_engine (url,echo_pool = l_echo_pool, echo = l_echo)
                self.check()
                self.url=url
                return
            except:
                pass
        self.slab_engine=None
        raise Exception,"Could not connect to database"
    
    
    
    def check (self):
        self.slab_engine.execute ("select 1").scalar()
        
        
        
    def session (self):
        if self.slab_session is None:
            Session=sessionmaker ()
            self.slab_session=Session(bind=self.slab_engine)
        return self.slab_session
        
        
   
        
    #Close connection to database
    def close(self):
        if self.connection is not None:
            self.connection.close()
            self.connection = None
            
   
        
        
    def exists(self, tablename):
        """
        Checks if the table specified as tablename exists.
    
        """
       
        try:
            metadata = MetaData (bind=self.slab_engine)
            table=Table (tablename, metadata, autoload=True)
           
            return True
        except NoSuchTableError:
            print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES EXISTS NOPE! tablename %s " %(tablename)
            return False
       
    
    def createtable(self, tablename ):
        """
        Creates the specifed table. Uses the global dictionnary holding the tablenames and
        the table schema.
    
        """

        print>>sys.stderr, " \r\n \r\n \t SLABPOSTGRES createtable SlabBase.metadata.sorted_tables %s \r\n engine %s" %(SlabBase.metadata.sorted_tables , slab_engine)
        SlabBase.metadata.create_all(slab_engine)
        return
    
    

       


from sfa.util.config import Config

slab_alchemy= SlabDB(Config())
slab_engine=slab_alchemy.slab_engine
slab_dbsession=slab_alchemy.session()