import logging
import appconfig as config

from sqlalchemy import Table, Column, Integer, BLOB,String, MetaData, ForeignKey
from sqlalchemy.dialects.mysql import LONGBLOB
from sqlalchemy.engine import create_engine
from sqlalchemy.engine.reflection import Inspector
from sqlalchemy.engine.url import URL
from sqlalchemy.orm import mapper, relationship
from sqlalchemy.orm.session import sessionmaker
from sqlalchemy.types import Date, FLOAT, DateTime, REAL
from sqlalchemy.orm.mapper import configure_mappers
from sqlalchemy.schema import Index

from orangescape.model.server.db.DbError import DbError
from orangescape.model.server.sql.SqlUtil import PK_COL_LEN, TBL_PREFIX, SYS_PREFIX, TMP_PREFIX, getValidColumnName,getValidTableName,getValidSchemaName
from orangescape.model.server.AbstractSession import AbstractSession
from orangescape.model.server.sql.Data import PrimaryData
from orangescape.model.server.sql.Data import Attachment
from orangescape.model.server.wrapper.Model import DynamicModel

from orangescape.model.server.sql.usertypes.EngineDateType import EngineDateType

if config.pickle:
    import os
    from pickleUtil import unpickle
    application_path=os.path.join(os.path.dirname(__file__).split('model')[0],'application')
    idx_def = unpickle(application_path, 'rdbms_indexes.pck')
else:
    from orangescape.application.rdbms_indexes import rdbms_indexes as idx_def
#    from orangescape.tests.functional.server.test.rdbms_indexes import rdbms_indexes as idx_def

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(cell.getTextLength() or 255)
    elif cell.getValue('FinalCategory') is None:
        logging.warn('unable to find data type')
    return REAL()

class SchemaGen():
    def __init__(self, application, engine = None,schema = None):
        self.application = application
        self.schema = schema
        self.engine = engine
        self.__orm_metadata__ = MetaData()
        self.refCellsDict = {}
        self.use_alter = engine is not None

    def generate(self):
        sheetMetadataList = self.application.getSheetMetadataList()
        for sheetId in sheetMetadataList:
            sheetMetadata = self.application.getSheetMetadataById(sheetId)
            self.generateTableMetadata(sheetMetadata)
        self.createAttachmentsTable()
        self.createVersionTable()
        self.createModelVersionTable()

    def generateTableIfNotExist(self):
        metadataList =[]
        sheetMetadataList = self.application.getSheetMetadataList()
        for sheetId in sheetMetadataList:
            sheetMetadata = self.application.getSheetMetadataById(sheetId)
            table = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema) +'.'+ getValidTableName(TBL_PREFIX+sheetMetadata.getSheetName()))
            if table is None:
                metadataList.extend(self.generateTableMetadata(sheetMetadata))
        if metadataList:
            self.__orm_metadata__.create_all(self.engine, metadataList)

    def updateSchemaMetadata(self):
        self.__orm_metadata__ = MetaData(bind = self.engine)
        self.__orm_metadata__.reflect(bind = self.engine, schema = self.schema)
        sheetMetadataList = self.application.getSheetMetadataList()
        for sheetId in sheetMetadataList:
            sheetMetadata = self.application.getSheetMetadataById(sheetId)
            tblMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema) + '.' + getValidTableName(TBL_PREFIX+sheetMetadata.getSheetName()))
            if tblMetadata is not None:
                self.updateTableMetadata(tblMetadata, sheetMetadata)

    def createAttachmentsTable(self):
        attachmentTbl = Table(getValidTableName('Attachments'), self.__orm_metadata__,Column('id', String(PK_COL_LEN), primary_key=True),Column('content', BLOB))

    def createVersionTable(self):
        versionTbl = Table(getValidTableName('Version'), self.__orm_metadata__,Column('Version_Id', String(PK_COL_LEN), primary_key=True),
                           Column('Version_Number', Integer),
                           Column('ModelVersion', REAL()),
                           Column('Global_Ref', String(PK_COL_LEN),ForeignKey(getValidTableName('tblGlobal')+'.Global_Id',use_alter=self.use_alter,name='Global')))

    def createModelVersionTable(self):
        modelVersionTbl = Table(getValidTableName('ModelVersion'), self.__orm_metadata__,
                                Column('ModelVersion_Id', String(PK_COL_LEN), primary_key=True),
                                Column('IsLatestVersion', String(10)),
                                Column('Content', LONGBLOB),
                                Column('Model_Id', String(PK_COL_LEN)),
                                Column('Version_Date', EngineDateType),
                                Column('Version_Ref', String(PK_COL_LEN),ForeignKey(getValidTableName('Version')+'.Version_Id',use_alter=self.use_alter,name='Version')),
                                Column('Metadata_Ref', String(PK_COL_LEN),ForeignKey(getValidTableName('tblMetadata')+'.Metadata_Id',use_alter=self.use_alter,name='Metadata'))
                                )

    def updateTableMetadata(self, tblMetadata, sheetMetadata):
        import migrate.changeset
        tblColumns = tblMetadata._columns
        tblColumns = [column.name for column in tblColumns]

        sysMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema) +'.'+ getValidTableName(SYS_PREFIX+sheetMetadata.getSheetName()))
        sysColumns = sysMetadata._columns
        sysColumns = [column.name for column in sysColumns]

        tmpMetadata = self.__orm_metadata__.tables.get(getValidSchemaName(self.schema) +'.'+ getValidTableName(TMP_PREFIX+sheetMetadata.getSheetName()))
        tmpColumns = tmpMetadata._columns
        tmpColumns = [column.name for column in tmpColumns]
        for row in sheetMetadata.getRowMetadataList():
            for cell in row.getCellMetadataList():
                if cell:
                    if cell.isSystemCell():
                        if not sysColumns.__contains__(cell.getCellName()):
                            if cell.isReferenceCell():
                                refColumn = Column(cell.getCellName(), String(PK_COL_LEN),ForeignKey(getValidSchemaName(self.schema) +'.'+ getValidTableName(SYS_PREFIX+cell.getReferredSheetMetadata().getSheetName()) +'.'+ cell.getReferredSheetMetadata().getSheetName()+'_Id', use_alter=self.use_alter, name=getValidColumnName(cell.getSheetId())), key = cell.getSheetId())
                                sysMetadata.append_column(refColumn)
                                sysMetadata.create_column(refColumn)
                            else:
                                sysMetadata.create_column(Column(getValidColumnName(cell.getCellName()), getDataType(cell), key=cell.getSheetId()))
                    elif cell.isNamedCell():
                        if not tblColumns.__contains__(cell.getCellName()):
                            if cell.isReferenceCell():
                                tblRefColumn = Column(cell.getCellName(), String(PK_COL_LEN),ForeignKey(getValidSchemaName(self.schema) +'.'+ getValidTableName(TBL_PREFIX+cell.getReferredSheetMetadata().getSheetName()) +'.'+ cell.getReferredSheetMetadata().getSheetName()+'_Id', use_alter=self.use_alter, name=getValidColumnName(cell.getSheetId())), key = cell.getSheetId())
                                tblMetadata.append_column(tblRefColumn)
                                tblMetadata.create_column(tblRefColumn)
                            else:
                                tblMetadata.create_column(Column(cell.getCellName(), getDataType(cell), key=cell.getSheetId()))
                    else:
                        if not tmpColumns.__contains__(getValidColumnName(cell.getSheetId())):
                            tmpMetadata.create_column(Column(getValidColumnName(cell.getSheetId()), getDataType(cell), key=cell.getSheetId()))

    def generateTableMetadata(self, sheetMetadata, update=None):
        tblCols, sysCols, refCols, tmpCols = [], [], [], []
        tblCols.append(Column(sheetMetadata.getSheetName()+'_Id', String(PK_COL_LEN), primary_key=True))
        sysCols.append(Column(sheetMetadata.getSheetName()+'_Id', String(PK_COL_LEN), ForeignKey(getValidTableName(TBL_PREFIX+sheetMetadata.getSheetName())+'.'+sheetMetadata.getSheetName()+'_Id'), primary_key=True))
        tmpCols.append(Column(sheetMetadata.getSheetName()+'_Id', String(PK_COL_LEN), ForeignKey(getValidTableName(SYS_PREFIX+sheetMetadata.getSheetName())+'.'+sheetMetadata.getSheetName()+'_Id'), primary_key=True))

        model_idx = idx_def.get(sheetMetadata.getSheetName())
        for row in sheetMetadata.getRowMetadataList():
            for cell in row.getCellMetadataList():
                if cell:
                    if cell.isSystemCell():
                        if cell.isReferenceCell():
                            if update is True:
                                sysCols.append(Column(getValidColumnName(cell.getCellName()), String(PK_COL_LEN), ForeignKey(getValidSchemaName(self.schema) +'.'+ getValidTableName(SYS_PREFIX+cell.getReferredSheetMetadata().getSheetName())+'.'+cell.getReferredSheetMetadata().getSheetName()+'_Id', use_alter=self.use_alter, name=getValidColumnName(cell.getSheetId())), key=cell.getSheetId()))
                            else:
                                sysCols.append(Column(getValidColumnName(cell.getCellName()), String(PK_COL_LEN), ForeignKey(getValidTableName(SYS_PREFIX+cell.getReferredSheetMetadata().getSheetName())+'.'+cell.getReferredSheetMetadata().getSheetName()+'_Id', use_alter=self.use_alter, name=getValidColumnName(cell.getSheetId())), key=cell.getSheetId()))
                        else:
                            sysCols.append(Column(getValidColumnName(cell.getCellName()), getDataType(cell), key=cell.getSheetId()))
                            if cell.getCellName() == 'SheetName' or (model_idx and cell.getSheetId() in model_idx):
                                sysCols.append(Index(getValidColumnName(cell.getSheetId()), sysCols[-1]))
                    elif cell.isNamedCell():
                        if cell.isReferenceCell():
                            if update is True:
                                tblCols.append(Column(cell.getCellName(), String(PK_COL_LEN), ForeignKey(getValidSchemaName(self.schema) +'.'+ getValidTableName(TBL_PREFIX+cell.getReferredSheetMetadata().getSheetName())+'.'+cell.getReferredSheetMetadata().getSheetName()+'_Id', use_alter=self.use_alter, name=getValidColumnName(cell.getSheetId())), key=cell.getSheetId()))
                            else:
                                tblCols.append(Column(cell.getCellName(), String(PK_COL_LEN), ForeignKey(getValidTableName(TBL_PREFIX+cell.getReferredSheetMetadata().getSheetName())+'.'+cell.getReferredSheetMetadata().getSheetName()+'_Id', use_alter=self.use_alter, name=getValidColumnName(cell.getSheetId())), key=cell.getSheetId()))
                        else:
                            tblCols.append(Column(cell.getCellName(), getDataType(cell), key=cell.getSheetId()))
                            if model_idx and cell.getSheetId() in model_idx:
                                tblCols.append(Index(getValidColumnName(cell.getSheetId()), tblCols[-1]))
                    else:
                        tmpCols.append(Column(getValidColumnName(cell.getSheetId()), getDataType(cell), key=cell.getSheetId()))
        tblCols.append(Column('type_', String(100), nullable=False))
        sysCols.append(Column('type_', String(100), nullable=False))

        tbl = Table(getValidTableName(TBL_PREFIX+sheetMetadata.getSheetName()), self.__orm_metadata__, *tuple(tblCols))
        sys = Table(getValidTableName(SYS_PREFIX+sheetMetadata.getSheetName()), self.__orm_metadata__, *tuple(sysCols))
        tmp = Table(getValidTableName(TMP_PREFIX+sheetMetadata.getSheetName()), self.__orm_metadata__, *tuple(tmpCols))

        return [tbl,sys,tmp]

    def create_tables(self, engine):
        self.__orm_metadata__.create_all(bind=engine)

    def drop_tables(self, engine):
        self.__orm_metadata__.drop_all(bind=engine)

    def getORMMetadata(self):
        return self.__orm_metadata__
