# -*- coding: latin-1 -*-
from ppms import ppms
from ppms.migration import SQLPacket, PacketCategory, MinVersionDependency, MaxVersionDependency
from ppms.constants import *
from ppms.database_integrity import db_consistence_test
import re
import io
import traceback
from collections import namedtuple


class ConsistencyCheckWorkArounder(io.StringIO):
    """Workaround for the consistency check closing files"""

    def __enter__(self):
        self.original_close = self.close
        self.close = lambda: True

        self.result = """"""

        return self

    def __exit__(self, *args):
        self.original_close()

    def write(self, string):
        # not the best solution but not possible to do differently without
        # changing the relation check
        if "SELECT * FROM" in string.upper():
            self.result += string + "\n"


def find_inconsistencies_in_schemas(schemas=None):
    """Finds inconsistent data in schemas and returns the sql statements to find them,
    already formated for a log entry.
    schemas must be a list with schema names e.g. [Q1B, Q2B]"""
    inconsistencies_sql_searches = ""
    if not schemas:
        schemas = ['Q1B', 'Q2B', 'Q3B', 'Q4B', 'Q5B', 'Q6B']
    relation_consistence_test = db_consistence_test.RelationCheck()

    for schema in schemas:
        with ConsistencyCheckWorkArounder() as dummy_log:
            relation_consistence_test.start(schema, file=dummy_log, write_sql=True)
            if dummy_log.result:
                inconsistencies_sql_searches += "\nTo find the inconsistencies in schema" + schema + ": \n"
                inconsistencies_sql_searches += dummy_log.result + "\n"

    return inconsistencies_sql_searches


Index = namedtuple('Index', ('table_name', 'index_name', 'columns', 'oracle_statement', 'mssql_statement'))

INDEXES = [Index(table_name='DT324', index_name='I_DT324_LOG_TIMESTAMP',
                 columns=('DI004367', 'DI004356', 'log_timestamp'),
                 oracle_statement="""CREATE INDEX I_DT324_log_timestamp
   ON DT324 (DI004367, DI004356, log_timestamp)""",
                 mssql_statement=None),
           Index(table_name='DT333', index_name='SID_PY_ID',
                 columns=('DI0051831',),
                 oracle_statement="""CREATE INDEX SID_PY_ID ON DT333 (DI051831)""",
                 mssql_statement=None),
           Index(table_name='DT404', index_name='IDX_DI028100',
                 columns=('DI028100',),
                 oracle_statement="""CREATE INDEX IDX_DI028100 ON DT404(DI028100)""",
                 mssql_statement=None),
           Index(table_name='DT406', index_name='IDX_DI000970',
                 columns=('DI000970',),
                 oracle_statement="""CREATE INDEX IDX_DI000970 ON DT406(DI000970)""",
                 mssql_statement=None),
           Index(table_name='DT406', index_name='IDX_DI000969',
                 columns=('DI000969',),
                 oracle_statement="""CREATE INDEX IDX_DI000969 ON DT406(DI000969)""",
                 mssql_statement=None),
           Index(table_name='DT411', index_name='DT411_LB_MOD_ID',
                 columns=('DI000111',),
                 oracle_statement="""CREATE INDEX DT411_LB_MOD_ID ON DT411 (DI000111 ASC)""",
                 mssql_statement=None),
           Index(table_name='DT411', index_name='DT411_ACTION_ID',
                 columns=('DI027481',),
                 oracle_statement="""CREATE INDEX DT411_ACTION_ID ON DT411 (DI027481 ASC)""",
                 mssql_statement=None),
           Index(table_name='DT411', index_name='IDX_DI000306',
                 columns=('DI000306',),
                 oracle_statement="""CREATE INDEX IDX_DI000306 ON DT411(DI000306)""",
                 mssql_statement=None),
           Index(table_name='DT412', index_name='DT412_DATAITEM_ENABLED',
                 columns=('DI000238', 'DI000239', 'DI000252', 'ENABLED', 'UUID'),
                 oracle_statement="""CREATE INDEX DT412_DATAITEM_ENABLED ON DT412
  (
    DI000238,
    DI000239,
    DI000252,
    ENABLED,
    UUID ASC
  )""",
                 mssql_statement=None),
           Index(table_name='DT415', index_name='DT415_TABLE_ENABLED',
                 columns=('DI000265', 'ENABLED', 'UUID'),
                 oracle_statement="""CREATE INDEX DT415_TABLE_ENABLED ON DT415
  (
    DI000265,
    ENABLED,
    UUID ASC
  )""",
                 mssql_statement=None),
           Index(table_name='DT415', index_name='UUID_DI_UNIQUE',
                 columns=('UUID_DI',),
                 oracle_statement="""CREATE UNIQUE INDEX UUID_DI_UNIQUE ON DT415 (UUID_DI)""",
                 mssql_statement="""CREATE UNIQUE INDEX UUID_DI_UNIQUE ON DT415 (UUID_DI) WHERE UUID_DI IS NOT NULL"""),
           Index(table_name='DT420', index_name='DT420_SCHEMA_ENABLED',
                 columns=('ENABLED',),
                 oracle_statement="""CREATE INDEX DT420_SCHEMA_ENABLED ON DT420 (ENABLED ASC)""",
                 mssql_statement=None),
           Index(table_name='DT324', index_name='I_DT324_LOG_TIMESTAMP',
                 columns=('DI004367', 'DI004356', 'log_timestamp'),
                 oracle_statement="""CREATE INDEX I_DT324_log_timestamp
   ON DT324 (DI004367, DI004356, log_timestamp)""",
                 mssql_statement=None),
           Index(table_name='DT461', index_name='MAIN_ID461DT461',
                 columns=('DI001052',),
                 oracle_statement="""CREATE INDEX MAIN_ID461DT461 ON DT461 (DI001052)""",
                 mssql_statement=None),
           Index(table_name='DT461', index_name='I_DT461_DI000264_PSP_1',
                 columns=('DI001001', 'DI001003', 'DI001052'),
                 oracle_statement="""CREATE INDEX I_DT461_DI000264_PSP_1
   ON DT461 (DI001001, DI001003, DI001052)""",
                 mssql_statement=None),
           Index(table_name='DT461', index_name='I_DT461_DI000264_PSP_2',
                 columns=('DI001003', 'DI001001', 'DI001052'),
                 oracle_statement="""CREATE INDEX I_DT461_DI000264_PSP_2
   ON DT461 (DI001003, DI001001, DI001052)""",
                 mssql_statement=None),
           Index(table_name='DT467', index_name='IDX_DT467_EVA_RES_TYPE',
                 columns=('DI001218', 'DI003414', 'DI001242'),
                 oracle_statement="""CREATE UNIQUE INDEX IDX_DT467_EVA_RES_TYPE ON DT467
  (
    DI001218 ASC, DI003414 ASC, DI001242 ASC
  )
  NOLOGGING""",
                 mssql_statement="""CREATE UNIQUE INDEX IDX_DT467_EVA_RES_TYPE ON DT467
  (
    DI001218 ASC, DI003414 ASC, DI001242 ASC
  )"""),
           Index(table_name='DT472', index_name='I_DT472_MOD0099AN',
                 columns=('DI000602', 'DI001395', 'DI001400', 'DI001413', 'DI001419', 'DI001426', 'DI001438',
                          'DI001491', 'DI001500', 'DI001501', 'DI001502', 'DI001503', 'DI001504', 'DI001505',
                          'DI001506', 'DI001507', 'DI001508', 'DI001509', 'DI001510', 'DI001511', 'DI001516',
                          'DI001517', 'DI001518', 'DI001519', 'DI057881'),
                 oracle_statement="""CREATE INDEX I_DT472_MOD0099AN
   ON dt472 ("DI000602",
             "DI001395",
             "DI001400",
             "DI001413",
             "DI001419",
             "DI001426",
             "DI001438",
             "DI001491",
             "DI001500",
             "DI001501",
             "DI001502",
             "DI001503",
             "DI001504",
             "DI001505",
             "DI001506",
             "DI001507",
             "DI001508",
             "DI001509",
             "DI001510",
             "DI001511",
             "DI001516",
             "DI001517",
             "DI001518",
             "DI001519",
             "DI057881")""",
                 mssql_statement=None),
           Index(table_name='DT499', index_name='SID499DT461',
                 columns=('DI057817',),
                 oracle_statement="""CREATE INDEX SID499DT461 ON DT499 (DI057817)""",
                 mssql_statement=None),
           Index(table_name='DT512', index_name='DT512_FK',
                 columns=('DI010333',),
                 oracle_statement="""CREATE INDEX DT512_FK ON DT512 (DI010333 ASC)""",
                 mssql_statement=None),
           Index(table_name='DT513', index_name='DT513_FK',
                 columns=('DI010407',),
                 oracle_statement="""CREATE INDEX DT513_FK ON DT513 (DI010407 ASC)""",
                 mssql_statement=None),
           Index(table_name='DT514', index_name='DT514_FK',
                 columns=('DI010338',),
                 oracle_statement="""CREATE INDEX DT514_FK ON DT514 (DI010338 ASC)""",
                 mssql_statement=None),
           Index(table_name='DT515', index_name='DT515_FK',
                 columns=('DI010325',),
                 oracle_statement="""CREATE INDEX DT515_FK ON DT515 (DI010325 ASC)""",
                 mssql_statement=None),
           Index(table_name='DT827', index_name='DI008517',
                 columns=('DI008517',),
                 oracle_statement="""CREATE INDEX DI008517 ON DT827 (DI008517)""",
                 mssql_statement=None),
           Index(table_name='DT827', index_name='DI008666',
                 columns=('DI008666',),
                 oracle_statement="""CREATE INDEX DI008666 ON DT827 (DI008666)""",
                 mssql_statement=None),
           Index(table_name='DT827', index_name='I_DT827_COSTS',
                 columns=('DI008516', 'DI008535', 'DI008518', 'DI008523', 'DI008660', 'DI008661',
                          'DI008663', 'DI008664', 'DI008665', 'DI008662'),
                 oracle_statement="""CREATE INDEX I_DT827_COSTS ON DT827
  (
    DI008516 ASC,
    DI008535 ASC,
    DI008518 ASC,
    DI008523 ASC,
    DI008660 ASC,
    DI008661 ASC,
    DI008663 ASC,
    DI008664 ASC,
    DI008665 ASC,
    DI008662 ASC
  )
  NOLOGGING""",
                 mssql_statement="""CREATE INDEX I_DT827_COSTS ON DT827
  (
    DI008516 ASC,
    DI008535 ASC,
    DI008518 ASC,
    DI008523 ASC,
    DI008660 ASC,
    DI008661 ASC,
    DI008663 ASC,
    DI008664 ASC,
    DI008665 ASC,
    DI008662 ASC
  )"""),
           Index(table_name='DT886', index_name='DT886_DI041508',
                 columns=('DI041508',),
                 oracle_statement="""CREATE INDEX DT886_DI041508 ON DT886 (DI041508 ASC)""",
                 mssql_statement=None),
           ]

DROP_STATEMENT = {DBMS_ORACLE: '''drop index "{index_name}"''',
                  DBMS_MSSQL: '''drop index [{table_name}].[{index_name}]'''}


class A_CreateConstraints(SQLPacket):
    '''Applies the constraints created by the schema generation.

    If the packet fails, inconsistent data has to be corrected. Afterwards, the EnableConstraints packet needs to be started manually.
    '''
    wi_number = 17120
    done_after_success = False
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY

    constraint_errors = ""

    def log_sql_error(self, constraint):
        type, value, trace = sys.exc_info()
        error_text = traceback.format_exception_only(type, value)[0].split("\\n", 1)[0]
        self.constraint_errors += '''\n\n''' + \
                                  error_text + \
                                  constraint

    def fix(self, runner):
        errors = 0
        dialect = ppms.db_get_current_dialect()
        create_constraints = ppms.db_custom_schema_create(dialect, dts_to_export=None,
                                                          get_create_strings=True,
                                                          get_table_strings=False,
                                                          get_constraint_strings=True)

        for constraint in create_constraints:
            try:
                if self.DBMS == DBMS_MSSQL:
                    for query in re.split('GO[^\w\]]', constraint):
                        if not self.change_create_index_to_rename_index(query):
                            self.modify_and_log(query)
                else:
                    for query in re.split(';/[^\w]', constraint):
                        if query.strip() != "":
                            if not self.change_create_index_to_rename_index(query):
                                self.modify_and_log(query)
            except ppms.DatabaseError as e:
                self.log_sql_error(constraint)
                errors += 1
        if errors == 0:
            return self.success()
        self.log("The following errors occurred while creating the constraints: ")
        self.log(self.constraint_errors)
        inconsistencies_sql_searches = find_inconsistencies_in_schemas()
        if inconsistencies_sql_searches:
            self.log(
                "Not all the constraints could be activated. Check the errors above to figure out the constraints concerned. Correct the data in the affected tables in order to activate the foreign keys .\nYou can find inconsistent data using the following SQL statements. Note that there might also be other problems, so make sure to check all the errors above.")
            self.log(inconsistencies_sql_searches)
        return self.fail(
            "%d errors occurred during constraint creation. Your system is most likely still runnable, but the database integrity could be compromised.\nAfter the errors have been corrected, you have to run the EnableConstraints packet to enable the missing constraints." % errors)


class B_AddMissingIndexes(SQLPacket):
    """This migration packet adds several indexes to speed up the system"""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 17637

    def should_index_be_skipped(self, table_name, index_name, column_names):
        """Check if the index already exists or the columns are already indexed"""
        self.log('Checking if index "%s" in table "%s" already exists' % (index_name, table_name))

        if self.index_exists(table_name=table_name, index_name=index_name):
            self.log('Index already exists - so skipping it!')
            return True

        self.log('Doesn\'t seem to be case, so let\'s create it')

        return False

    def fix(self, packet_runner):
        dbms = ppms.db_get_dbms_name()
        failures = []

        for table_name, index_name, columns, oracle_statement, mssql_statement in INDEXES:
            if self.should_index_be_skipped(table_name=table_name, index_name=index_name, column_names=columns):
                continue

            if dbms == DBMS_ORACLE:
                query = oracle_statement
            elif dbms == DBMS_MSSQL:
                if index_name == 'I_DT472_MOD0099AN':
                    self.log('This index can not be created in MSSQL, so we skip it')
                    continue

                if mssql_statement is None:
                    query = oracle_statement
                else:
                    query = mssql_statement
            else:
                raise NotImplementedError(dbms)

            try:
                if not self.change_create_index_to_rename_index(query):
                    self.modify_and_log(query=query, log_rows=False)
            except ppms.DatabaseError:
                failures.append((table_name, index_name, query, traceback.format_exc()))

        if failures:
            self.log_heading('The following indexes could not be created')

            for table_name, index_name, query, exception in failures:
                self.log('Table: %s Index: %s' % (table_name, index_name))
                self.log('Statement: %s' % query)
                self.log('Exception: %s' % exception)

            return self.fail()

        return self.success()
