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


class AddPortfolioChanges(SQLPacket):
    """Adds the real columns required for the new portfolio."""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 19923

    sql_script = 'WI19923_add_portfolio_changes.sql'

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

    def fix(self, packet_runner):
        self.apply_dml_file(self.sql_planta_de_dbms, self.sql_script)
        self.log("Schema changes done! Updating old portfolios now.")
        self.create_ctg_assignments_for_old_portfolios()
        self.fix_filters_with_non_exitent_portfolios()
        self.log("Done updating old portfolios!")
        return self.success()

    def create_ctg_assignments_for_old_portfolios(self):
        insert = """INSERT INTO {table} ({columns}) VALUES ({values})"""
        os_columns = 'OS_CREATED_ON, OS_MODIFIED_ON, OS_RWDU, OS_CREATED_BY, OS_MODIFIED_BY, OS_OWN_LIC, OS_OPC, OS_OWNER, OS_GROUP, OS_ANY'
        live_columns = 'COST_TYPE_GROUP_ID, PORTFOLIO_ID, COST_TYPE_GROUP_ACTIVE, ' + os_columns
        report_columns = 'COST_TYPE_GROUP_ID, PORTFOLIO_ID, STATUSREPORT_ID, COST_TYPE_GROUP_ACTIVE, ' + os_columns

        owner_license = planta.uvar_get('@3').zfill(3)
        user = planta.uvar_get('@1')

        if self.DBMS == DBMS_ORACLE:
            os_data = "CURRENT_DATE, CURRENT_DATE, 7, '{owner}', '{owner}', '{owner_license}', 7, 7, 7, 7".format(
                owner=user, owner_license=owner_license)
        elif self.DBMS == DBMS_MSSQL:
            os_data = "CAST(GETDATE() AS DATE), CAST(GETDATE() AS DATE), 7, '{owner}', '{owner}', '{owner_license}', 7, 7, 7, 7".format(
                owner=user, owner_license=owner_license)
        else:
            raise NotImplementedError('This DBMS is not implemented!')

        get_portfolios = """select DI028667 from DT870"""

        get_portfolio_sbs = """select STATUSREPORT_ID from DT090 where PORTFOLIO_ID = '{pf_id}'"""

        get_ctgs = """select DI060121, DI060135 from DT280"""

        pf_ids = [pf_id[0] for pf_id in planta.db_select(get_portfolios)]
        ctg_ids = planta.db_select(get_ctgs)

        for pf_id in pf_ids:
            for ctg_id, active in ctg_ids:
                values = "'" + ctg_id + "', " + "'" + pf_id + "', " + str(int(active)) + ", " + os_data
                query = insert.format(table='PORTFOLIO_CTG', columns=live_columns, values=values)
                self.modify_and_log(query)
            reports = [report[0] for report in planta.db_select(get_portfolio_sbs.format(pf_id=pf_id))]
            for report in reports:
                for ctg_id, active in ctg_ids:
                    values = "'" + ctg_id + "', '" + pf_id + "', '" + report + "', " + str(int(active)) + ", " + os_data
                    query = insert.format(table='PF_REPORT_CTG', columns=report_columns, values=values)
                    self.modify_and_log(query)

    def fix_filters_with_non_exitent_portfolios(self):
        statement = """UPDATE DT818 SET DT818.DI034392 = NULL WHERE NOT EXISTS
                            (SELECT 1 FROM DT870 WHERE DT818.DI034392 = DT870.DI028667) AND DT818.DI034392 IS NOT NULL"""

        self.modify_and_log(statement)
