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


class AddCtgConstraints(SQLPacket):
    """Adds foreign keys from cost type groups tables DT281-DT284 to DT280 to ensure no sub records can exist without
    a corresponding cost type group. The package will fail in case there already exists corrupt data, which was created
    with the customer license. In this case the corruptions have to be resolved manually and the package can be started
    again."""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 22024

    cleanup_corruptions = ["""delete from DT281 where DI060140 not in (select DI060121 from dt280)
                                                and DI060166 <> '{sys_lic}'""",
                           """delete from DT282 where DI060473 not in (select DI060121 from dt280)
                                                and DI060479 <> '{sys_lic}'""",
                           """delete from DT283 where DI060344 not in (select DI060121 from dt280)
                                                and DI060352 <> '{sys_lic}'""",
                           """delete from DT284 where DI060299 not in (select DI060121 from dt280)
                                                and DI060307 <> '{sys_lic}'""",
                           """delete from DT285 where DI060389 not in (select DI060121 from dt280)
                                                and DI060392 <> '{sys_lic}'"""]

    add_constraints = ["""alter table DT281 add constraint FKDT281ID280
                                           foreign key("DI060140") references "DT280"("DI060121")""",
                       """alter table DT282 add constraint FKDT282ID280
                                           foreign key("DI060473") references "DT280"("DI060121")""",
                       """alter table DT283 add constraint FKDT283ID280
                                           foreign key("DI060344") references "DT280"("DI060121")""",
                       """alter table DT284 add constraint FKDT284ID280
                                           foreign key("DI060299") references "DT280"("DI060121")""",
                       """alter table DT285 add constraint FKDT285ID280
                                           foreign key("DI060389") references "DT280"("DI060121")"""]
                    
    def get_dependencies(self):
        return MaxVersionDependency(version='39.5.10', version_type=VersionType.CUSTOMIZING)

    def fix(self, packet_runner):
        # delete all corrupt cost type group data (records which don't belong to an existing cost type group)
        sys_lic = ppms.uvar_get('@3')
        for stmt in self.cleanup_corruptions:
            ppms.ui_message_box('sys lic', stmt.format(sys_lic=sys_lic))
            self.modify_and_log(stmt.format(sys_lic=sys_lic))
        
        # create constraints from all cost type group tables to dt280 "cost type group"
        for stmt in self.add_constraints:
            self.modify_and_log(stmt)
            
        return self.success()
