from cStringIO import StringIO
import appconfig as config

from SqlUtil import PK_COL_LEN, TBL_PREFIX, SYS_PREFIX, TMP_PREFIX, ATTACHMENT_TBL_NAME, VERSION_TBL_NAME, MODEL_VERSION_TBL_NAME
from SqlUtil import getValidColumnName,getValidTableName,getValidSchemaName, getValidIndexName ,getPKName, getOneToManyName, getManyToOneName

INDENT_SIZE = 4
INDENT = ' '*INDENT_SIZE
baseName = 'Base'

def getDataType(cell):
    if cell.getValue('CellName'):
        if cell.getValue('CellName')=='CreatedAt' or cell.getValue('CellName')=='ModifiedAt' or cell.getValue('CellName').count('Date')>0:
            return 'EngineDateType()'
    if cell.getValue('FinalCategory')=="Category005":
        return 'EngineDateType()'
    elif cell.getValue('FinalCategory')=="Category001" or cell.getValue('FinalCategory')=="Category010":
        return 'String(%s)'%(cell.getTextLength() or 255)
    elif cell.getValue('FinalCategory') is None:
        logging.warn('unable to find data type')
    return 'REAL()'

class StringWritter():
    def __init__(self, buff = ''):
        self.buff = StringIO()
        self.write(buff)

    def write_str(self, buff):
        self.buff.write("'")
        self.buff.write(buff)
        self.buff.write("'")

    def write_str_line(self, buff):
        self.write_str(buff)
        self.buff.write('\n')

    def write(self, buff):
        self.buff.write(buff)

    def write_line(self, buff):
        self.write(buff)
        self.buff.write('\n')

    def getvalue(self):
        return self.buff.getvalue()

class ClassGenerator():
    def __init__(self, applicationContext, idx_def):
        self.applicationContext = applicationContext
        self.commonBuff = StringWritter()
        self.classBuff = StringWritter()
        self.relationBuff = StringWritter()
        self.idx_def = idx_def

    def generate(self):
        self.generateCommonCode()
        self.generateAttachmentCode()
        self.generateVersionTable()
        self.generateModelVersionTable()
        for metadataName in self.applicationContext.getSheetMetadataDict():
            metadata = self.applicationContext.getSheetMetadata(metadataName)
            self.generateClassForModel(metadata)

        buff = StringWritter()
        buff.write_line(self.commonBuff.getvalue())
        buff.write_line(self.classBuff.getvalue())
#        buff.write_line(self.relationBuff.getvalue())
        return buff.getvalue()

    def generateAttachmentCode(self):
        self.commonBuff.write_line("")
        self.commonBuff.write_line("class %s(%s):"%(ATTACHMENT_TBL_NAME, baseName))
        self.commonBuff.write_line("%s__tablename__ = '%s'"%(INDENT, getValidTableName(ATTACHMENT_TBL_NAME)))
        self.commonBuff.write_line("%sid = Column('id', String(%s), primary_key=True)"%(INDENT, PK_COL_LEN))
        self.commonBuff.write_line("%scontent = Column('content', BLOB())"%(INDENT))

    def generateVersionTable(self):
        self.commonBuff.write_line("")
        self.commonBuff.write_line("class %s(%s):"%(VERSION_TBL_NAME, baseName))
        self.commonBuff.write_line("%s__tablename__ = '%s'"%(INDENT, getValidTableName(VERSION_TBL_NAME)))
        self.commonBuff.write_line("%sVersion_Id = Column('Version_Id', String(%s), primary_key=True)"%(INDENT, PK_COL_LEN))
        self.commonBuff.write_line("%sVersion_Number = Column('Version_Number', Integer)"%(INDENT))
        self.commonBuff.write_line("%sModelVersion = Column('ModelVersion', REAL())"%(INDENT))
        self.commonBuff.write_line("%sGlobal_Ref = Column('Global_Ref', String(%s), ForeignKey('%s.Global_Id'))"%(INDENT, PK_COL_LEN, getValidTableName('tblGlobal')))

    def generateModelVersionTable(self):
        self.commonBuff.write_line("")
        self.commonBuff.write_line("class %s(%s):"%(MODEL_VERSION_TBL_NAME, baseName))
        self.commonBuff.write_line("%s__tablename__ = '%s'"%(INDENT, getValidTableName(MODEL_VERSION_TBL_NAME)))
        self.commonBuff.write_line("%sModelVersion_Id = Column('ModelVersion_Id', String(%s), primary_key=True)"%(INDENT, PK_COL_LEN))
        self.commonBuff.write_line("%sIsLatestVersion = Column('IsLatestVersion', String(%s))"%(INDENT, 10))
        self.commonBuff.write_line("%sContent = Column('Content', LONGBLOB)"%(INDENT))
        self.commonBuff.write_line("%sModel_Id = Column('Model_Id', String(%s))"%(INDENT, PK_COL_LEN))
        self.commonBuff.write_line("%sVersion_Date = Column('Version_Date', EngineDateType)"%(INDENT))
        self.commonBuff.write_line("%sVersion_Ref = Column('Version_Ref', String(%s), ForeignKey('%s.Version_Id', use_alter=True, name='Version'))"%(INDENT, PK_COL_LEN, getValidTableName('Version')))
        self.commonBuff.write_line("%sMetadata_Ref = Column('Metadata_Ref', String(%s), ForeignKey('%s.Metadata_Id', use_alter=True, name='Metadata'))"%(INDENT, PK_COL_LEN, getValidTableName('tblMetadata')))

    def generateCommonCode(self):
        self.commonBuff.write_line('import logging')
        self.commonBuff.write_line('from sqlalchemy import Table, Column, Integer, BLOB,String, MetaData, ForeignKey, REAL')
        self.commonBuff.write_line('from sqlalchemy.orm import relationship')
        self.commonBuff.write_line('from sqlalchemy.dialects.mysql import LONGBLOB')
        self.commonBuff.write_line('from sqlalchemy.schema import Index')
        self.commonBuff.write_line('from orangescape.model.server.sql.usertypes.EngineDateType import EngineDateType')
        self.commonBuff.write_line('from orangescape.model.server.sql.Data import PrimaryData')
        self.commonBuff.write_line('from sqlalchemy.ext.declarative import declarative_base')
        self.commonBuff.write_line('%s = declarative_base()'%(baseName))

    def getTblClassName(self, metadata):
        return TBL_PREFIX+metadata.getSheetName()

    def getSysClassName(self, metadata):
        return SYS_PREFIX+metadata.getSheetName()

    def getTmpClassName(self, metadata):
        return TMP_PREFIX+metadata.getSheetName()

    def getTblTableName(self, metadata):
        return getValidTableName(self.getTblClassName(metadata))

    def getSysTableName(self, metadata):
        return getValidTableName(self.getSysClassName(metadata))

    def getTmpTableName(self, metadata):
        return getValidTableName(self.getTmpClassName(metadata))

    def generateClassForModel(self, metadata):
        tblBuff = StringWritter()
        sysBuff = StringWritter()
        tmpBuff = StringWritter()

#        tblRelBuff = StringWritter()
#        sysRelBuff = StringWritter()

        tblIndexBuff = StringWritter()
        sysIndexBuff = StringWritter()

        tblBuff.write_line("class %s(PrimaryData, %s):"%(self.getTblClassName(metadata), baseName))
        tblBuff.write_line("%s__tablename__ = '%s'"%(INDENT, self.getTblTableName(metadata)))
        tblBuff.write_line("%sMetadataName = '%s'"%(INDENT, metadata.getSheetName()))
        tblBuff.write_line("%sSheetMetadataId= '%s'"%(INDENT, metadata.getSheetId()))

        sysBuff.write_line("class %s(%s):"%(self.getSysClassName(metadata), self.getTblClassName(metadata)))
        sysBuff.write_line("%s__tablename__ = '%s'"%(INDENT, self.getSysTableName(metadata)))
        tmpBuff.write_line("class %s(%s):"%(self.getTmpClassName(metadata), self.getSysClassName(metadata)))
        tmpBuff.write_line("%s__tablename__ = '%s'"%(INDENT, self.getTmpTableName(metadata)))

        tblBuff.write_line("%s%s = Column('%s', String(%s), primary_key=True)"%(INDENT, getPKName(metadata), getPKName(metadata), PK_COL_LEN))
        sysBuff.write_line("%s%s = Column('%s', String(%s), ForeignKey('%s.%s'), primary_key=True)"%(INDENT, getPKName(metadata), getPKName(metadata), PK_COL_LEN, self.getTblTableName(metadata), getPKName(metadata)))
        tmpBuff.write_line("%s%s = Column('%s', String(%s), ForeignKey('%s.%s'), primary_key=True)"%(INDENT, getPKName(metadata), getPKName(metadata), PK_COL_LEN, self.getSysTableName(metadata), getPKName(metadata)))

        model_idx = self.idx_def.get(metadata.getSheetName())
        for row in metadata.getRowMetadataList():
            for cell in row.getCellMetadataList():
                if cell:
                    if cell.isSystemCell():
                        sysBuff.write_line("%s%s = Column('%s', %s)"%(INDENT, cell.getSheetId(), getValidColumnName(cell.getCellName()), getDataType(cell)))
                        if cell.isReferenceCell() or (model_idx and cell.getSheetId() in model_idx):
                            sysIndexBuff.write("Index('%s' ,'%s'),"%(getValidIndexName(cell.getSheetId()),cell.getCellName()))
                    elif cell.isNamedCell():
                        tblBuff.write_line("%s%s = Column('%s', %s)"%(INDENT, cell.getSheetId(), getValidColumnName(cell.getCellName()), getDataType(cell)))
                        if cell.isReferenceCell() or (model_idx and cell.getSheetId() in model_idx):
                            tblIndexBuff.write("Index('%s' ,'%s'),"%(getValidIndexName(cell.getSheetId()),cell.getCellName()))
                    elif cell.isFormula() or cell.isReferenceCell() or cell.getNodeList():
                        tmpBuff.write_line("%s%s = Column('%s', %s)"%(INDENT, cell.getSheetId(), getValidColumnName(cell.getSheetId()), getDataType(cell)))
        tblBuff.write_line(INDENT+"discriminator = Column('type_', String(100), nullable=False)")
        tblBuff.write_line(INDENT+"__mapper_args__ = {'polymorphic_on': discriminator}")
        if tblIndexBuff.getvalue():
            tblBuff.write_line("%s__table_args__ = (%s)"%(INDENT, tblIndexBuff.getvalue()))
        if sysIndexBuff.getvalue():
            sysBuff.write_line("%s__table_args__ = (%s)"%(INDENT, sysIndexBuff.getvalue()))

        sysBuff.write_line(INDENT+"discriminator = Column('type_', String(100), nullable=False)")
        sysBuff.write_line(INDENT+"__mapper_args__ = {'polymorphic_on': discriminator, 'polymorphic_identity': '"+metadata.getSheetId()+"'}")
        tmpBuff.write_line(INDENT+"__mapper_args__ = {'polymorphic_identity': '"+metadata.getSheetId()+"'}")

        self.classBuff.write_line(tblBuff.getvalue())
        self.classBuff.write_line(sysBuff.getvalue())
        self.classBuff.write_line(tmpBuff.getvalue())

#        self.relationBuff.write_line(tblRelBuff.getvalue())
#        self.relationBuff.write_line(sysRelBuff.getvalue())
