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

from distutils.version import StrictVersion
import os

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


def table_get_id(table):
    return table.id


class BaseNullLicenseFixer():
    generated_batch_size = 128

    fix_data_schema_ids = ['Q1B', 'Q2B']
    fix_data_table_ids = [500, 501, 502, 503,
                          545,
                          560, 561, 562, 565,
                          489, 496, 497, 498]

    set_constraint_schema_ids = ['Q1B', 'Q2B', 'Q3B', 'Q5B']


    def __init__(self):
        data_dictionary = ppms.get_data_dictionary()

        ### prepare lists of tables to operate on
        self.fix_data_tables = list()
        for schema_id in self.fix_data_schema_ids:
            for table in data_dictionary.db_schemas[schema_id].data_tables:
                self.fix_data_tables.append(table)
        for table_id in self.fix_data_table_ids:
            try:
                self.fix_data_tables.append(data_dictionary.data_tables[table_id])
            except KeyError as e:
                pass  # DT from Venus not found in Data Dictionary of VoE
        self.fix_data_tables.sort(key=table_get_id, reverse=False)

        self.set_constraint_tables = list()
        for schema_id in self.set_constraint_schema_ids:
            for table in data_dictionary.db_schemas[schema_id].data_tables:
                self.set_constraint_tables.append(table)
        self.set_constraint_tables.sort(key=table_get_id, reverse=False)


        ### derive output file name from CU version
        cu_version = None
        if MinVersionDependency.get_customizing_version() == StrictVersion('39.4'):
            cu_version = 'voe'
        else:
            cu_version = 'venus'
        self.sql_file = '''wi13560_fix_null_license_in_{v}_standard_cu_objects.sql'''.format(v=cu_version)


    def table_get_license_di(self, table):
        """
        for di in table.data_items:
            if di.python_id in ['own_lic', 'owner_license']:
                return di
        """
        if table.acr_dis and len(table.acr_dis) == 10:
            return table.acr_dis[5]
        else:
            return None


    def process_license_columns(self, work_func):
        result = True

        for table in self.fix_data_tables:
            license_di = self.table_get_license_di(table)

            if license_di and not work_func(table, license_di):
                result = False

        return result





class FixNullLicense(SQLPacket, BaseNullLicenseFixer):
    """Apply a value to the owner license in all customizing tables if it is null

    - Standard objects identified by their primary key receive a PLANTA-internal license
    - All remaining objects receive the running system's active license
    """

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

    active_license = ppms.uvar_get(SYS_VAR_LICENSE)

    ddl_statements = {DBMS_ORACLE: [
        """alter table "{table}" modify ("{column}" varchar2(4 char) default '{license}' constraint "{constraint}"
        not null)"""],
                      DBMS_MSSQL: ["""alter table [{table}] alter column [{column}] varchar(4)""",
                                   """alter table [{table}] add constraint [{constraint}_DEFAULT] default '{license}'
                                   for [{column}]""",
                                   """alter table [{table}] add constraint [{constraint}_NOT_NULL] check ([{column}]
                                   is not null)""", ]}
    ddl_stmt = ddl_statements[ppms.db_get_dbms_name()]


    def __init__(self, *args, **kwargs):
        super(FixNullLicense, self).__init__(*args, **kwargs)
        BaseNullLicenseFixer.__init__(self)

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

    def fix(self, runner):
        prev = ppms.db_set_auto_commit(False)
        try:
            self.fix_standard_cu_objects()
            self.fix_customer_cu_objects()
            return self.ensure_all_objects_fixed() and self.add_not_null_constraints()
        finally:
            ppms.db_set_auto_commit(prev)


    def fix_standard_cu_objects(self):
        """
        apply PLANTA-internal license to known PLANTA-owned objects
        """
        self.apply_dml_file(self.sql_planta_de_ansi, self.sql_file)


    def fix_customer_cu_objects(self):
        """
        apply customer license to all remaining objects
        """

        if (int(self.active_license) > 100):  # only do this on real customer licenses
            self.process_license_columns(self.fix_customer_cu_in_table)


    def fix_customer_cu_in_table(self, table, license_di):
        modified = ppms.db_modify("""update {table} set {column} = '{license}' where {column} is null""".format(
            table=table.sql_name, column=license_di.sql_name, license=self.active_license))

        if modified:
            self.log("""DT {table} / {sql_table}: set license to {license} in {num} records""".format(
                table=table.id, sql_table=table.sql_name, license=self.active_license, num=modified))

        return True


    def ensure_all_objects_fixed(self):
        if self.process_license_columns(self.ensure_table_fixed):
            ppms.db_commit()
            return True
        else:
            ppms.db_rollback()
            return self.fail('null values remaining in license columns')


    def ensure_table_fixed(self, table, license_di):
        count = int(ppms.db_select("""select count(*) from {table} where {column} is null""".format(
            table=table.sql_name, column=license_di.sql_name))[0][0])
        if count == 0:
            return True
        else:
            self.log("""DT {tid} / {tsql}: still found null values in license column in {num} records""".format(
                tid=table.id, tsql=table.sql_name, num=count))
            return False


    def add_not_null_constraints(self):
        if self.process_license_columns(self.add_not_null_constraint):
            return self.success();
        else:
            return self.fail('unable to establish not-null-constraint on license columns')


    def add_not_null_constraint(self, table, license_di):
        try:
            for ddl_stmt in self.ddl_stmt:
                stmt = ddl_stmt.format(table=table.sql_name, column=license_di.sql_name, license=self.active_license,
                                       constraint='LICENSE_{table}'.format(table=table.sql_name)[:31])
                self.modify_and_log(stmt)
            return True
        except ppms.DatabaseError as e:
            self.log(e)
            return False