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


class PerformanceReplanning(SQLPacket):
    """Creates db objects needed to speed-up the cost aggregation after scheduling/replanning."""
    run_on_startup = True
    done_after_success = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 20208

    sql_script = 'WI20208_performance_replanning.sql'
    
    def get_dependencies(self):
        return MaxVersionDependency(version='39.5.10')

    def fix(self, packet_runner):
        # create table, indexes, triggers, procedure
        if self.DBMS == DBMS_MSSQL:
            self.apply_dml_file(self.sql_planta_de_dbms, self.sql_script, split=True, separator='GO')
        else:
            self.apply_dml_file(self.sql_planta_de_dbms, self.sql_script, split=True, separator=';/')

        """ Fill table project_structure with all parent to (recursive-)child pairs including the starting project
        itself. So for example the structure of three projects:
          project1 -> project2 (sub project of 1) -> project3 (sub project of 2)
        leads to this data:
         -project1,project1 ; project1,project2 ; project1,project3
         -project2,project2 ; project2,project3
         -project3,project3

        This is needed for a fast structure aggregation on every project level without the need to calculate the
        children for every cost/budget calculation statement running on a sub-table of DT461 (eg. DT282)."""
        projects = [pr_id[0] for pr_id in ppms.db_select("select DI001001 from DT461")]
        
        structure_qry_oracle = """insert into project_structure (
                                     select '{pr_id}', DI001001
                                     from DT461
                                     start with di001003='{pr_id}'
                                     connect by prior di001001=di001003
                                     group by nvl(DI001003, DI001001), DI001001
                                    union
                                     select '{pr_id}', '{pr_id}'
                                     from dual
                                    )"""
        
        structure_qry_mssql = """with n(di001001, di001003) AS
                                    (select di001001, di001003
                                     from dt461
                                     where di001003='{pr_id}'
                                        union all
                                     select parent.di001001, parent.di001003
                                     from dt461 as parent, n
                                     where n.di001001 = parent.di001003)
                                insert into project_structure
                                    select '{pr_id}', di001001 from n
                                       union all
                                    select '{pr_id}', '{pr_id}'"""
        for pr_id in projects:
            # insert structure per project
            if self.DBMS == DBMS_ORACLE:
                structure_qry = structure_qry_oracle.format(pr_id=pr_id)
            elif self.DBMS == DBMS_MSSQL:
                structure_qry = structure_qry_mssql.format(pr_id=pr_id)
                
            self.modify_and_log(structure_qry)

        return self.success()
