# -*- coding: utf-8 -*-
from ppms.migration import SQLPacket, PacketCategory, MinVersionDependency, \
                           MaxVersionDependency, VersionType
from ppms.constants import DBMS_MSSQL
from ppms import ppms

class LicenseProcessIDs(SQLPacket):
    """Adds the license as prefix to all auto-ids used in the process model.
    This is applied to the standard process models, and the object processes
    which use them, only - custom process models remain untouched."""

    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 14224

    sql_script = 'WI14224_add_license_to_process_ids.sql'

    def get_dependencies(self):
        return (MinVersionDependency(version='39.5.7',
                                     version_type=VersionType.CUSTOMIZING),
                MaxVersionDependency(version='39.5.7',
                                     version_type=VersionType.CUSTOMIZING))

    def fix(self, packet_runner):

        index_statements = [
            ["""drop index RECURSION_PARENT_ID on DT499""",
                """CREATE NONCLUSTERED INDEX [RECURSION_PARENT_ID] ON [DT499]
                ([DI057823] ASC)
                INCLUDE ([DI041138],[DI041139],[DI041353],[DI057817],
                         [DI057819],[DI057847])"""],
            ["""drop index FK_OBJECT_ID on DT499""",
                """CREATE NONCLUSTERED INDEX [FK_OBJECT_ID] ON [DT499]
                ([DI057817] ASC)
                INCLUDE ([DI041138],[DI041139],[DI041353],[DI057819],
                         [DI057823],[DI057847])"""],
            ["""drop index FK_PR_ID on DT499""",
                """CREATE NONCLUSTERED INDEX [FK_PR_ID] ON [DT499]
                    ([DI041139] ASC)
                    INCLUDE ([DI041138],[DI041353],[DI057817],[DI057819],
                             [DI057823],[DI057847])"""],
            ["""drop index FK_TASK_ID on DT499""",
                """CREATE NONCLUSTERED INDEX [FK_TASK_ID] ON [DT499]
                    ([DI041139] ASC,
                     [DI057847] ASC)
                    INCLUDE ([DI041138],[DI041353],[DI057817],[DI057819],
                             [DI057823])"""],
            ["""drop index FK_RES_ID on DT499""",
             """CREATE NONCLUSTERED INDEX [FK_RES_ID] ON [DT499]
                 ([DI041353] ASC)
                 INCLUDE ([DI041138],[DI041139],[DI057817],[DI057819],
                          [DI057823],[DI057847])"""]]

        pk_statements = [
            ["""alter table DT499 drop constraint PID499DT499""",
                """alter table DT499 ADD CONSTRAINT PID499DT499
                   PRIMARY KEY CLUSTERED (DI041138 ASC)"""],
            ["""alter table dt499 drop constraint DT499_PK""",
                """ALTER TABLE[dbo].[DT499] ADD  CONSTRAINT[DT499_PK]
                    PRIMARY KEY CLUSTERED ([DI041138] ASC)"""],
            ["""alter table DT877 drop constraint PID877DT877""",
                """ALTER TABLE [DT877] ADD  CONSTRAINT [PID877DT877]
                    PRIMARY KEY CLUSTERED ([DI057895] ASC,[DI057896] ASC)"""],
            ["""alter table dt489 drop constraint PID489DT489""",
                """ALTER TABLE [DT489] ADD  CONSTRAINT [PID489DT489]
                    PRIMARY KEY CLUSTERED ([DI041217] ASC)"""],
            ["""alter table DT496 drop constraint PID496DT496""",
                """ALTER TABLE [DT496] ADD CONSTRAINT [PID496DT496]
                   PRIMARY KEY CLUSTERED ([DI041096] ASC)"""],
            ["""alter table DT497 drop constraint PID497DT497""",
                """ALTER TABLE [DT497] ADD  CONSTRAINT [PID497DT497]
                    PRIMARY KEY CLUSTERED ([DI041110] ASC)"""],
            ["""alter table DT498 drop constraint PID498DT498""",
                """ALTER TABLE [DT498] ADD  CONSTRAINT [PID498DT498]
                    PRIMARY KEY CLUSTERED ([DI041123] ASC)"""],
            ["""alter table DT876 drop constraint PID876DT876""",
                """ALTER TABLE [DT876] ADD  CONSTRAINT [PID876DT876]
                    PRIMARY KEY CLUSTERED (DI057880 ASC)"""]]
        
        fk_statements = [
            ["""alter table DT498 drop constraint FKDT498ID497""",
                """ALTER TABLE [DT498]  WITH CHECK ADD CONSTRAINT [FKDT498ID497]
                 FOREIGN KEY([DI041124]) REFERENCES [DT497] ([DI041110])"""],
            ["""alter table DT497 drop constraint FKDT497ID496""",
                """ALTER TABLE [DT497] WITH CHECK ADD CONSTRAINT [FKDT497ID496]
                FOREIGN KEY([DI041109]) REFERENCES [DT496] ([DI041096])"""],
            ["""alter table DT497 drop constraint SDT497ID496""",
                 """ALTER TABLE DT497 ADD CONSTRAINT SDT497ID496
                    FOREIGN KEY (DI041109) REFERENCES DT496 (DI041096)
                    ON DELETE CASCADE"""],
            ["""alter table DT498 drop constraint SDT498ID497""",
                """ALTER TABLE DT498 ADD CONSTRAINT SDT498ID497
                FOREIGN KEY (DI041124) REFERENCES DT497 (DI041110)
                ON DELETE CASCADE"""],
            ["""alter table dt498 drop constraint SDT498ID498S""",
             """ALTER TABLE DT498 ADD CONSTRAINT SDT498ID498S
             FOREIGN KEY (DI057818) REFERENCES DT498 (DI041123)"""],
            ["""alter table dt499 drop constraint SDT499ID499S""",
                """ALTER TABLE DT499 ADD CONSTRAINT SDT499ID499S
                FOREIGN KEY(DI057823) REFERENCES DT499(DI041138)"""],
            ["""drop index PK_covering_query on DT499""",
             """CREATE UNIQUE NONCLUSTERED INDEX [PK_covering_query]
             ON [DT499] ([DI041138] ASC )
             INCLUDE ([DI041139],[DI041353],[DI057817],[DI057819],
                      [DI057823],[DI057847])"""]]

        lic_statements = ["""update dt439 set DI000002=';;;$$$$$$', DI000062=1
                          where DI000388 = '{sys_lic}' and DI000361='041217'""",
                          """update dt439 set DI000002=';;;$$$$$$', DI000062=1
                          where DI000388 = '{sys_lic}' and DI000361='041096'""",
                          """update dt439 set DI000002=';;;$$$$$$', DI000062=1
                          where DI000388 = '{sys_lic}' and DI000361='041110'""",
                          """update dt439 set DI000002=';;;$$$$$$', DI000062=1
                          where DI000388 = '{sys_lic}' and DI000361='041123'""",
                          """update dt439 set DI000002=';;;$$$$$$$$', DI000062=1
                          where DI000388 = '{sys_lic}' and DI000361='041138'""",
                          """update dt439 set DI000002=';;;$$$$$$$$$$$$$$$$$'
                          where DI000388 = '{sys_lic}' and DI000361='057880'"""]

        if self.DBMS == DBMS_MSSQL:
            # drop all indexes, foreign keys, primary keys which avoid changing
            # the column length
            create_indexes = self.execute_drop_statements(index_statements)
            create_fks = self.execute_drop_statements(fk_statements)
            create_pks = self.execute_drop_statements(pk_statements)

        # change columns lengths and add lic to standard process model records
        self.apply_dml_file(self.sql_planta_de_dbms, self.sql_script)

        if self.DBMS == DBMS_MSSQL:
            # recreate all objects which were dropped before
            for create_stmt in create_indexes:
                self.modify_and_log(create_stmt)

            for create_stmt in create_pks:
                self.modify_and_log(create_stmt)

            for create_stmt in create_fks:
                self.modify_and_log(create_stmt)

        # set new creation rule for auto numbers
        sys_lic = ppms.uvar_get('@3')
        for statement in lic_statements:
            self.modify_and_log(statement.format(**locals()))

        return self.success()

    def execute_drop_statements(self, sql_statements):
        create_stmts = []
        for statements in sql_statements:
            drop_statement = statements[0]
            try:
                self.modify_and_log(drop_statement)
            except ppms.DatabaseError:
                # if updating from VoE some indexes won't exist and
                # some constraints can't be dropped
                continue

            create_stmts.append(statements[1])

        return create_stmts