from datetime import datetime

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, MetaData, join, ForeignKey
from sqlalchemy import Column, Integer, String
from sqlalchemy import Table, Column, MetaData, join, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.orm import column_property
from sqlalchemy.orm import mapper, object_mapper
from sqlalchemy.orm import validates
from sqlalchemy import ForeignKey    

from PLC.Storage.Alchemy import Base, engine, dbsession
from PLC.Storage.Record import Record
from PLC.Timestamp import Timestamp
from PLC.Logger import logger   
 
class AlchemyObj(Record):

    def __init__(self, api=None, fields = {}, object=None):
        Record.__init__(self, dict=fields, object=object)
        self.api=api

    def __iter__(self):
        self._i = iter(object_mapper(self).columns)
        return self

    def next(self):
        n = self._i.next().name
        return n, getattr(self, n)

    def get_table(self):
        metadata = MetaData(engine)
        table = Table(self.tablename, metadata)
        for field in self.fields:
            param = self.fields[field]
            # skip params joined from other tables 
            if param.joined:
                continue
            type = String
            if param.type == int:
                type = Integer
            elif param.type == datetime:
                type = DateTime
            column = Column(field, type, 
                            nullable = param.nullok,
                            indexed = param.indexed,
                            primary_key=param.primary_key)
            table.append_column(column)
        if not table.exists():
            table.create()
        return table

    def create(self):
        table = self.get_table()
        table.create()    

    def insert(self, values):
        table = self.get_table()
        result = dbsession.execute(table.insert().values(values))
        dbsession.commit()
        return result
    
    def updatedb(self, filter, values):
        class Cls(object): pass
        table = self.get_table()
        clsmapper = mapper(Cls, table)
        result = dbsession.query(clsmapper).filter_by(**filter).update(values)
        dbsession.commit()
        return result

    def delete(self, filter={}):
        class Cls(object): pass
        table = self.get_table()
        clsmapper = mapper(Cls, table)
        result = dbsession.query(clsmapper).filter_by(**filter).delete()
        dbsession.commit()         
        return result
 
    def select(self, filter={}):
        class Cls(object): pass
        table = self.get_table()
        clsmapper = mapper(Cls, table) 
        return dbsession.query(clsmapper).filter_by(**filter)