# -*- coding: utf-8 -*-

import abc
import os
import pickle

from ppms.migration import SQLPacket, PacketCategory, MaxVersionDependency
from ppms.constants import DBMS_ORACLE, DBMS_MSSQL
from ppms import ppms


def table_get_id(table):
    return table.id


class PkUuidTransfer():
    pickle_file = os.path.join('.', 'migration', 'server', 'S_39_5_13', 'z90_wi13689_create_pk_uuids.pickle')


    def read_dict_from_file(self):
        with open(self.pickle_file, 'rb') as f:
            return pickle.load(f)


    def write_dict_to_file(self, uuid_dis_by_table_id):
        with open(self.pickle_file, 'wb') as f:
            pickle.dump(uuid_dis_by_table_id, f)


class PkUuidGenerator(PkUuidTransfer):
    def to_file(self):
        self.write_dict_to_file(self.collect_pk_uuid_dis_by_table())


    def collect_pk_uuid_dis_by_table(self):
        data_dictionary = ppms.get_data_dictionary()
        uuid_dis_by_table_id = dict()

        for table in data_dictionary.data_tables.values():
            uuid_di = table.uuid_di

            if uuid_di:
                title_inum = int(
                    ppms.db_select("""select di001591 from dt412 where di000238='{di:06d}'""".format(di=uuid_di.id))[0][
                        0])
                uuid_dis_by_table_id[table.id] = (uuid_di, title_inum)

        return uuid_dis_by_table_id


class BasePkUuidPacket(SQLPacket, PkUuidTransfer, metaclass=abc.ABCMeta):
    ddl_statements = {
        DBMS_ORACLE: """begin p_execute_ignoring_exception('alter table {table}
        add ("UUID" raw(16) default SYS_GUID() constraint "{constraint}" not
        null)'); end;""",
        DBMS_MSSQL: """exec p_execute_ignoring_exception 'alter table [{table}]
                                       add [UUID] uniqueidentifier
                                       default newid()
                                       constraint [{constraint}] not null'"""}
    ddl_stmt = ddl_statements[ppms.db_get_dbms_name()]


    def select_or_log(self, stmt):
        try:
            return ppms.db_select(stmt)
        except Exception as e:
            self.log('Exception while trying to perform update statement\n{s}'.format(s=stmt))
            self.log(e)
            raise


    def modify_or_log(self, stmt):
        try:
            ppms.db_modify(stmt)
        except Exception as e:
            self.log('Exception while trying to perform update statement\n{s}'.format(s=stmt))
            self.log(e)
            raise


    def normalize_uuid_dis(self):
        # ancient UUID fields used to have data field type 'A' - fix them
        stmt = """
update dt412 set
    di003141='UUID',
    di000244='I',
    di000246=36,
    di000254=16,
    di027172=1,
    di041246=321
where di000253='ID'"""
        self.modify_or_log(stmt)

        # Non-virtual UUID DI with DB pos. 0 is supposed to be the data table's PK UUID DI - set proper column and
        # python name
        stmt = """
update dt412 set
    di000215='UUID',
    di041035='uuid'
where di000253='ID'
and   di000241 = 0
and   di000275 = 0"""
        self.modify_or_log(stmt)


    # Non-virtual UUID DI with DB pos. 0 is supposed to be the data table's PK UUID DI
    def collect_pk_uuid_di(self, table_id, verbose=False):
        stmt = """
select di000238 from dt412
where di000239 = '{dt:03d}'
and   di000253 = 'ID'
and   di000241 = 0
and   di000275 = 0""".format(dt=table_id)
        uuid_id_dis = self.select_or_log(stmt)
        length = len(uuid_id_dis)
        if length == 0:
            if verbose:
                self.log(
                    '''Could not find any non-virtual UUID DI with DB pos. 0, hence cannot set DT {dt:03d}'s PK UUID
                    DI!'''.format(
                        dt=table_id))
            return None
        elif length == 1:
            uuid_di_id = int(uuid_id_dis[0][0])
            self.log('Found PK UUID DI {di:06d}, writing into DT {dt:03d}'
                     .format(di=uuid_di_id, dt=table_id))
            self.update_table_record(table_id, uuid_di_id)
            return uuid_di_id
        else:
            self.log(
                '''ERROR: Found {l} non-virtual UUID DIs with DB pos. 0, cannot decide which one is supposed to be DT
                {dt:03d}'s PK UUID DI!'''.format(
                    l=length, dt=table_id))
            return None


    def update_table_record(self, table_id, uuid_di_id):
        self.modify_or_log(
            """update dt415 set uuid_di='{di:06d}' where di000265='{t:03d}'""".format(di=uuid_di_id, t=table_id))


class NormalizeUuidDiDefinitions(BasePkUuidPacket):
    """Corrects the definitions of UUID DIs in DT412"""

    version = 1
    run_on_startup = False
    done_after_success = True
    category = PacketCategory.CATEGORY_HELPER
    wi_number = 13689

    def fix(self, runner):
        prev = ppms.db_set_auto_commit(False)
        try:
            self.normalize_uuid_dis()
            ppms.db_commit()
            return self.success()

        except BaseException as e:
            self.log(e)
            ppms.db_rollback()
            return self.fail(e)

        finally:
            ppms.db_set_auto_commit(prev)


class DeclarePkUuidInDataTable(BasePkUuidPacket):
    """Fill DI061211 of DT415 with the DI ID of UUID DI
    """

    version = 1
    run_on_startup = False
    done_after_success = True
    category = PacketCategory.CATEGORY_HELPER
    wi_number = 13689

    def fix(self, runner):
        data_dictionary = ppms.get_data_dictionary()
        prev = ppms.db_set_auto_commit(False)
        try:
            for table in data_dictionary.data_tables.values():
                if not table.uuid_di:
                    self.collect_pk_uuid_di(table.id, verbose=True)
            ppms.db_commit()
            return self.success()

        except BaseException as e:
            self.log(e)
            ppms.db_rollback()
            return self.fail(e)

        finally:
            ppms.db_set_auto_commit(prev)


class CreatePkUuids(BasePkUuidPacket):
    """
    Create UUID DIs for all active data tables known to standard customizing
    """

    version = 1
    run_on_startup = True
    done_after_success = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 13689

    def get_dependencies(self):
        return (MaxVersionDependency('39.5.6'), )

    def fix(self, runner):
        prev = ppms.db_set_auto_commit(False)
        try:
            self.normalize_uuid_dis()
            tables = self.fix_customizing()
            return self.fix_db_schema(tables)

        except BaseException as e:
            self.log(e)
            return self.fail(e)

        finally:
            ppms.db_set_auto_commit(prev)


    def fix_customizing(self):
        data_dictionary = ppms.get_data_dictionary()
        uuid_dis_by_table_id = self.read_dict_from_file()
        tables = list()

        try:
            for table in data_dictionary.data_tables.values():
                if not table.id in [496, 497, 498, 499]:
                    existing_uuid_di = table.uuid_di

                    if not existing_uuid_di:
                        # try and find an existing PK UUID DI which is not yet referred in the data table
                        di_id = self.collect_pk_uuid_di(table.id)
                        if di_id:
                            try:
                                existing_uuid_di = data_dictionary.data_items[di_id]
                            except KeyError:
                                self.log(
                                    'PK UUID DI {di:06d} found in DB, but not in data dictionary => must have been '
                                    'created by a preceding migration package, skipping.'
                                    .format(di=di_id))
                                continue

                    imported_uuid_di = None
                    title_inum = None

                    try:
                        imported_uuid_di, title_inum = uuid_dis_by_table_id[table.id]
                    except KeyError:
                        self.log('No PK UUID DI imported for DT {dt:03d}, skipping.'
                                 .format(dt=table.id))
                        continue

                    if not existing_uuid_di or existing_uuid_di.id == imported_uuid_di.id:
                        if self.add_uuid_di(table, imported_uuid_di, title_inum):
                            tables.append(table)
                    else:
                        self.fix_uuid_di_id(existing_uuid_di, imported_uuid_di, title_inum)

            ppms.db_commit()
            return tables
        except BaseException as e:
            ppms.db_rollback()
            raise


    def fix_uuid_di_id(self, existing_uuid_di, imported_uuid_di, title_inum):
        self.log('Fixing PK UUID DI {di:06d} for DT {dt:03d}'.format(di=imported_uuid_di.id, dt=imported_uuid_di.table))
        self.update_data_item_record(existing_uuid_di.id, imported_uuid_di.id, title_inum)
        self.update_table_record(imported_uuid_di.table, imported_uuid_di.id)


    def add_uuid_di(self, table, uuid_di, title_inum):
        if self.create_data_item_record(table.id, uuid_di.id, title_inum):
            self.update_table_record(table.id, uuid_di.id)
            return True
        else:
            return False


    def update_data_item_record(self, old_di_id, new_di_id, title_inum):
        stmt = """
update dt412 set
    di001869='011',
    di003141='UUID',
    di000241=0,
	di000244='I',
    di000246=36,
    di000253='ID',
    di000254=16,
    di000215='UUID',
    di027172=1,
    di041035='uuid',
    di041246=321,
    enabled=1,
    di000238='{new:06d}',
    di001591={inum:d}
where di000238='{old:06d}'""".format(new=new_di_id, inum=title_inum, old=old_di_id)
        self.modify_or_log(stmt)

        self.create_title_itext_records(title_inum)


    def create_data_item_record(self, table_id, uuid_di_id, title_inum):
        if self.data_item_record_does_not_exist(table_id, uuid_di_id, title_inum):
            self.log('Adding PK UUID DI {di:06d} for DT {dt:03d}'.format(di=uuid_di_id, dt=table_id))
            stmt = """
insert into dt412 (di001700, di000275, di001869, di003141, di000241, di000244, di000246, di000253, di000254, di000215,
di027172, di041035, di041246, enabled, di000238,     di000239,   di001591)
           values (       0,        0, '011',    'UUID', 0 , 'I',            36, 'ID',           16, 'UUID',
           1, 'uuid',        321,       1, '{di:06d}',   '{dt:03d}', {inum:d})""" \
                .format(di=uuid_di_id, dt=table_id, inum=title_inum)
            self.modify_or_log(stmt)

            self.create_title_itext_records(title_inum)
            return True

        else:
            self.log('Found preexisting PK UUID DI {di:06d} for DT {dt:03d}'.format(di=uuid_di_id, dt=table_id))
            return False


    def create_title_itext_records(self, title_inum):
        for language in ['DE', 'EN', 'FR', 'PT', ]:
            stmt = """
insert into dt435 (di001538, di001550, di001544, di001549)
           values ({inum:d}, '{lang}',    '011',   'UUID')""" \
                .format(inum=title_inum, lang=language)
            self.modify_or_log(stmt)


    def data_item_record_does_not_exist(self, table_id, uuid_di_id, title_inum):
        stmt = """
select di000239, di001869, di003141, di000244, di000246, di000253, di000254, di000215, di027172, di041035, di041246,
enabled, di001591
from dt412 where di000238='{di:06d}'""".format(di=uuid_di_id)
        result_set = self.select_or_log(stmt)
        if 0 == len(result_set):
            return True
        else:
            found = result_set[0]
            expected = ['{dt:03d}'.format(dt=table_id),
                        '011', 'UUID', 'I', float(36), 'ID',
                        float(16), 'UUID', float(1), 'uuid', float(321), float(1),
                        float(title_inum)]
            if found == expected:
                return False
            else:
                raise FileExistsError('''
DI {di:06d} already exists, but it is not the UUID field we are trying to port!
expected: {e}
found:    {f}'''.format(di=uuid_di_id, e=expected, f=found))


    def fix_db_schema(self, tables):
        success = True
        for t in tables:
            try:
                self.create_uuid_column(t)
            except ppms.DatabaseError as exception:
                if 'ORA-01430' not in str(exception):
                    success = False

        if success:
            return self.success()
        else:
            return self.fail("could not create all UUID columns in DB schema!");


    def create_uuid_column(self, table):
        self.log('Adding UUID column to table {t} (DT {dt:03d})'.format(t=table.sql_name, dt=table.id))
        stmt = self.ddl_stmt.format(table=table.sql_name, constraint='UUID_{table}'.format(table=table.sql_name)[:31])
        self.modify_or_log(stmt)
