# -*- coding: utf-8 -*-
import os

from planta_logging.logging import *
from ppms import ppms
from ppms.constants import *
from ppms.migration import BasePacket, ChangeDTPPacket, SQLPacket, MinVersionDependency, MaxVersionDependency, PacketCategory


class PortPeriodSchemaChanges(ChangeDTPPacket, SQLPacket):
    """Port period schema changes from Venus"""

    helper_packet_path = 'migration.server.special.aggregate_resource_periods.AggregateResourcePeriods'
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 17042

    procedure_sql_file = "WI11721_change_language_P_CALC_PERIODS.sql"
    function_sql_file = "WI11721_change_language_PLANTA_F_CALC_PERIOD_FACTOR.sql"

    NEW_FILENAMES = {DBMS_ORACLE: ('aggregation_oracle_new.sql', 'summarize_oracle_new.sql'),
                     DBMS_MSSQL: ('aggregation_mssql_new.sql', 'summarize_mssql_new.sql')}

    OLD_FILENAMES = {DBMS_ORACLE: ('aggregation_oracle_old.sql', 'summarize_oracle_old.sql'),
                     DBMS_MSSQL: ('aggregation_mssql_old.sql', 'summarize_mssql_old.sql')}

    QUERIES = ('000682', '000691')

    TABLE_SQL = {DBMS_ORACLE: ("""DROP TABLE DT468_TEMP""",
                               """CREATE TABLE DT468_TEMP (
                               "RES" VARCHAR2(20 CHAR),
                               "PERIOD" DATE,
                               "WORK_P" NUMBER,
                               "FACTOR_AMOUNT" NUMBER,
                               "HOLIDAY" NUMBER,
                               "ABSENCE" NUMBER,
                               "BASE_LOAD" NUMBER,
                               "FUNCTIONAL" NUMBER,
                               "MISC" NUMBER,
                               "UNITS_PERIOD" NUMBER,
                               "AVAIL_CAP" NUMBER,
                               "RSC" VARCHAR2(50 BYTE),
                               "COMMENTS" NUMBER,
                               "FAC_3" NUMBER,
                               "FAC_4" NUMBER,
                               "FAC_5" NUMBER )""",
                               """ALTER TABLE "DT468_TEMP" MODIFY ("RES" NOT NULL ENABLE)"""),
                 DBMS_MSSQL: ("""DROP TABLE DT468_TEMP""",
                              """DROP PROCEDURE P_CALC_PERIODS""",
                              """DROP FUNCTION PLANTA_F_CALC_PERIOD_FACTOR""",
                              """CREATE TABLE DT468_TEMP (
                                  "RES" VARCHAR(20) NOT NULL,
                                  "PERIOD" DATE,
                                  "WORK_P"      FLOAT,
                                  "FACTOR_AMOUNT"   FLOAT,
                                  "HOLIDAY"      FLOAT,
                                  "ABSENCE" FLOAT,
                                  "BASE_LOAD"   FLOAT,
                                  "FUNCTIONAL"    FLOAT,
                                  "MISC"   FLOAT,
                                  "UNITS_PERIOD"    FLOAT,
                                  "AVAIL_CAP"    FLOAT,
                                  "RSC"         VARCHAR(50),
                                  "COMMENTS"   FLOAT,
                                  "FAC_3"       FLOAT,
                                  "FAC_4"       FLOAT,
                                  "FAC_5"       FLOAT)""")}

    def get_dependencies(self):
        return (MinVersionDependency(version='39.4'),
                MaxVersionDependency(version='39.4'))

    def fix(self, runner):
        if self.port_new_queries() and self.port_name_changes():
            self.reset_cleanup_helper()
            return self.success()

        return self.fail('Could not port changes')


    def port_new_queries(self):
        dbms = ppms.db_get_dbms_name()

        old_filenames = self.OLD_FILENAMES.get(dbms, None)
        new_filenames = self.NEW_FILENAMES.get(dbms, None)

        if old_filenames is None or new_filenames is None:
            self.log('Unknown dbms!!')
            return False

        for old_filename, new_filename, query in zip(old_filenames, new_filenames, self.QUERIES):
            old_file_path = os.path.join(os.path.dirname(__file__), old_filename)
            new_file_path = os.path.join(os.path.dirname(__file__), new_filename)

            self.log('Updating period query from file: "%s"' % new_file_path)
            with open(new_file_path, 'r') as f:
                new_statement = f.read()

            with open(old_file_path, 'r') as f:
                old_statement = f.read()

            record = self.get_record(343, [dbms, query], ['source_text_clob'])
            if not self.change_value(record, 'source_text_clob', old_statement, new_statement):
                self.log('Changing the update query failed')
                return False

        return True


    def port_name_changes(self):
        if ppms.db_get_dbms_name() == DBMS_ORACLE:
            for statement in self.TABLE_SQL[DBMS_ORACLE]:
                self.modify_and_log(statement)

        if ppms.db_get_dbms_name() == DBMS_MSSQL:
            for statement in self.TABLE_SQL[DBMS_MSSQL]:
                self.modify_and_log(statement)

        self.apply_fix_ddl_file(self.sql_planta_de_dbms, self.function_sql_file, False)
        self.apply_fix_ddl_file(self.sql_planta_de_dbms, self.procedure_sql_file, False)

        return True


    def reset_cleanup_helper(self):
        self.mark_packet_as_undone(self.helper_packet_path)


    def mark_packet_as_undone(self, packet_path):
        future_packet = BasePacket.from_packet_path(packet_path)
        if future_packet is None:
            self.log('No packet with path "%s" found - might be a problem!' % packet_path)
            return False

        self.log('Marking the packet with path "%s" as undone' % packet_path)
        future_packet.log('Packet marked as undone by "%s"' % str(self))
        future_packet.mark_as_undone()
        future_packet.save_history()

        return True
