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


class FixUUIDsInPSPReportTables(SQLPacket):
    """Adds real UUIDs to the PSP report tables and renames the old ones"""
    run_on_startup = True
    done_after_success = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 21235

    sql_script = 'WI21235_fix_psp_report_table.sql'

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

    def fix(self, runner):
        self.remove_necessary_constraints()
        self.apply_dml_file(self.sql_planta_de_dbms, self.sql_script)
        return self.success()

    def remove_necessary_constraints(self):
        get_foreign_key_constraints = {DBMS_ORACLE: """select table_name, constraint_name 
                                            from user_constraints 
                                            where r_constraint_name in
                                            (select constraint_name from user_constraints
                                            where table_name='{table}')""",
                                       DBMS_MSSQL: """select distinct OBJECT_NAME(f.parent_object_id), f.name
                                            from sys.foreign_keys as f, sys.foreign_key_columns as fc, sys.tables as t 
                                            where f.OBJECT_ID = fc.constraint_object_id
                                            and t.OBJECT_ID = fc.referenced_object_id
                                            and OBJECT_NAME(t.object_id) = '{table}'"""}

        drop_constraint = "alter table {table} drop constraint {constraint}"

        tables = ['DT974', 'DT976']
        for table in tables:
            for foreign_table, constraint in planta.db_select(
                    get_foreign_key_constraints[self.DBMS].format(table=table)):
                self.modify_and_log(drop_constraint.format(table=foreign_table, constraint=constraint))
            constraints = self.get_constraints_of_column_ported(table, 'UUID')
            for constraint in constraints:
                try:
                    self.modify_and_log(drop_constraint.format(table=table, constraint=constraint))
                except planta.DatabaseError:
                    self.log("Could not drop {constraint}. Trying to rename old column anyways.".format(
                        constraint=constraint))

    @staticmethod
    def get_constraints_of_column_ported(table_name, column_name):
        """Get all constraints on a certain column

        Returns a list of constraint names
        """
        dbms = planta.db_get_dbms_name()

        if dbms == DBMS_ORACLE:
            query = """SELECT constraint_name FROM user_cons_columns
                        WHERE TABLE_NAME = '{table_name}' AND  COLUMN_NAME = '{column_name}'"""
        elif dbms == DBMS_MSSQL:
            query = """SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
                       WHERE TABLE_NAME = '{table_name}' AND COLUMN_NAME = '{column_name}'"""
        else:
            raise NotImplementedError(dbms)

        return [constraint[0] for constraint in
                planta.db_select(query.format(table_name=table_name, column_name=column_name))]
