# -*- coding: latin-1 -*-
from ppms.migration import SQLPacket, PacketCategory
from ppms import ppms
from ppms.constants import *

class CorrectOwnerLicense(SQLPacket):
    ''' Sets all owner license fields which are empty in Q1B and Q2B.

    Sets all owner licenses according to their auto number if available in Q1B and Q2B as well as in DT500.
    '''
    wi_number = 16890
    done_after_success = False
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY

    licenses = ['011', '100', '000']
    schemas = ['Q1B', 'Q2B']
    include_tables = [('500', 'DT500')] # must be a list of tuple with ('<TABLE NUMBER>', '<SQL TABLE IDENTIFIER>')
    skip_tables = ['581', '582', '055', '520', '521', '514']

    always_set_standard = ['407', '432', '441']
    always_set_customer = ['333', '343', '411', '416', '433', '409', '315', '314']

    get_all_tables_with_lic_di = """select DI000265, DI000216 from DT415 where ENABLED = 1 and DI000928 != 0 and DI000266 in {schemas}"""

    set_license_by_schema_sql = """update {table}
                                    set {owner_lic}  =
                                    case
                                        when {schema_di} not like 'P%' and {schema_di} not like 'Q%' then '{custome_license}'
                                        else '011'
                                    end
                                    where {owner_lic} is null"""

    owner_license_of_dt = {DBMS_ORACLE: """select * from(
                                select tab.DI000215, tab.DI000239, ROWNUM rn
                                from
                                (select DT412.*
                                  from DT412
                                   INNER JOIN DT415
                                          ON DT415.DI000265 = DT412.DI000239
                                          where DT415.DI000216 = '{dt}'
                                          and DT412.DI000241 >= DT415.DI000928
                                  order by DT412.DI000241 asc) tab)
                                where rn = 6""",
                           DBMS_MSSQL: """select tab.DI000215, tab.DI000239 from
                                (select DT412.*, ROW_NUMBER() OVER (order by DT412.DI000241 asc) AS rn
                                    from DT412
                                    INNER JOIN DT415
                                            ON DT415.DI000265 = DT412.DI000239
                                            where DT415.DI000216 = '{dt}'
                                            and DT412.DI000241 >= DT415.DI000928) as tab
                                    where tab.rn = 6"""}

    auto_number_structure = """select DI000002, DI000215 from DT439
                                      inner join DT412 on DT439.DI000361 = DT412.DI000238
                                      inner join DT415 on DT415.DI000265 = DT412.DI000239
                                      where DT415.DI000216 = '{dt}'"""

    set_license_from_autonumber = {DBMS_ORACLE: """update {table}
                                    set {owner_lic}  =
                                    case
                                        when substr({auto_num}, {start}, {end}) = {owner_lic} then {owner_lic}
                                        when substr({auto_num}, {start}, {end}) = (select DI000001 from DT401 where DI026548 = 1) then substr({auto_num}, {start}, {end})
                                        else '011'
                                    end
                                    where {owner_lic} not in {licenses}""",
                   DBMS_MSSQL: """update {table}
                                    set {owner_lic}  =
                                    case
                                        when substring(cast({auto_num} as varchar(30)), {start}, {end}) = {owner_lic} then {owner_lic}
                                        when substring(cast({auto_num} as varchar(30)), {start}, {end}) = (select DI000001 from DT401 where DI026548 = 1) then substring(cast({auto_num} as varchar(30)), {start}, {end})
                                        else '011'
                                    end
                                    where {owner_lic} not in {licenses}"""}

    set_license = """update {table} set {owner_lic_di} = {owner_lic} where {owner_lic_di} is null"""

    def fix(self, runner):
        self.customer_license = self.get_customer_license()
        self.log("Starting update of owner licenses for customer license " + self.customer_license)
        for table, table_sql in self.get_all_tables_with_license_di() + self.include_tables:
            if self.find_owner_license_di_of_dt(table_sql) != '':
                self.log('Starting update of table: ' + table)
                owner_di = self.find_owner_license_di_of_dt(table_sql)
                if table in self.skip_tables:
                    self.log("Skipping table " + table + " as defined.")
                elif table.startswith('6') or table.startswith('7') or table in self.always_set_customer:
                    self.set_license_for_all_empty_records(table_sql, owner_di, self.customer_license)
                elif table in self.always_set_standard:
                    self.set_license_for_all_empty_records(table_sql, owner_di, '011')
                elif table == '345':
                    self.set_license_for_all_empty_records(table_sql, owner_di, 'DI024408')
                elif table in ['415', '420']:
                    self.set_license_by_schema(table_sql, owner_di)
                elif table == '435':
                    self.set_records_with_empty_license(table_sql, owner_di, 'DI001538', '0', '3')
                elif table == '037':
                    self.set_records_with_empty_license(table_sql, owner_di, 'DI025487', '0', '3')
                elif table == '519':
                    self.set_records_with_empty_license(table_sql, owner_di, 'DI010685', '0', '3')
                elif table == '348':
                    self.modify_and_log(self.set_license.format(table=table_sql, owner_lic_di=owner_di, owner_lic='DI025409'))
                elif table == '396':
                    self.modify_and_log(self.set_license.format(table=table_sql, owner_lic_di=owner_di, owner_lic='DI008150'))
                elif table == '439':
                    self.modify_and_log(self.set_license.format(table=table_sql, owner_lic_di=owner_di, owner_lic='DI000388'))
                elif self.get_auto_number_info(table_sql) != -1:
                    start, end, auto_num_di = self.get_auto_number_info(table_sql)
                    self.set_records_with_empty_license(table_sql, owner_di, auto_num_di, start, end)
                else:
                    self.log("Skipping table " + table + " because it does not have a auto number DI which contains the license.")
            else:
                self.log("Skipping table " + table + " because it does not have a license DI.")

        return self.success()

    def get_all_tables_with_license_di(self):
        schemas = self.schemas
        if len(schemas) == 1:
            schemas.append(schemas[0])
        tables = ppms.db_select(self.get_all_tables_with_lic_di.format(schemas=str(tuple(schemas))))
        return [(t[0], t[1]) for t in tables]

    def set_license_by_schema(self, table, owner_lic_di):
        if table == 'DT415':
            schema_di = 'DI000266'
        elif table == 'DT420':
            schema_di = 'DI000289'
        self.modify_and_log(self.set_license_by_schema_sql.format(table=table, owner_lic=owner_lic_di, schema_di=schema_di, custome_license=self.customer_license))

    def get_auto_number_info(self, table):
        """
        :param table: table to get the information about the auto number
        :return: -1 if the table does not have a auto number or a tuple with
        (start position of auto number, end position, di of the auto number)
        """
        auto_num = ppms.db_select(self.auto_number_structure.format(dt=table))
        if auto_num != []:
            start = auto_num[0][0].find(';')
            if start != -1:
                end = start + 3
                return start, end, auto_num[0][1]
        return -1

    def find_owner_license_di_of_dt(self, table):
        di_num = ppms.db_select(self.owner_license_of_dt[self.DBMS].format(dt=table))
        if di_num != []:
            return di_num[0][0]
        return ''

    def set_records_with_empty_license(self, table, owner_di, auto_num_di, start, end):
        owner_licenses = []
        owner_licenses += self.licenses
        owner_licenses.append(self.customer_license)
        if len(owner_licenses) == 1:
            owner_licenses.append(owner_licenses[0])
        self.modify_and_log(
            self.set_license_from_autonumber[self.DBMS].format(table=table, owner_lic=owner_di,
                                               auto_num=auto_num_di,
                                               start=start, end=end,
                                               licenses=str(tuple(owner_licenses))))

    def set_license_for_all_empty_records(self, table, license_di, license):
        self.modify_and_log(
            self.set_license.format(table=table, owner_lic_di=license_di,
                                    owner_lic=license))

    def get_customer_license(self):
        customer_license = ppms.db_select("""select DI000001 from DT401 where DI026548 = 1""")[0][0]
        return customer_license
