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


class FixMSSQLColumnTypes(SQLPacket):
    """Fix incorrect column types in MSSQL VoE systems

    Affected:
        * DT391 - DI040927
        * DT401 - DI061538
        * DT801 - DI040791
    """

    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 14265

    CONSTRAINTS = [('DT391', 'DI040927'),
                   ('DT401', 'DI061538'),
                   ('DT801', 'DI040791')]
    STATEMENTS = ["""ALTER TABLE DT391 ALTER COLUMN DI040927 FLOAT NULL""",
                  """ALTER TABLE DT401 ALTER COLUMN DI061538 NUMERIC(5,0) NOT NULL""",
                  """ALTER TABLE DT801 ALTER COLUMN DI040791 FLOAT NULL"""]

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

    def fix(self, runner):
        if not self.DBMS == DBMS_MSSQL:
            self.log("Only required for systems running on MS SQL Server.")
            return self.success()
        for (dt, di) in self.CONSTRAINTS:
            constraint = ppms.db_select("""SELECT
    default_constraints.name
FROM 
    sys.all_columns

        INNER JOIN
    sys.tables
        ON all_columns.object_id = tables.object_id

        INNER JOIN 
    sys.schemas
        ON tables.schema_id = schemas.schema_id

        INNER JOIN
    sys.default_constraints
        ON all_columns.default_object_id = default_constraints.object_id

WHERE 
        schemas.name = 'dbo'
    AND tables.name = '{dt}'
    AND all_columns.name = '{di}'""".format(dt=dt, di=di))
            if constraint:
                self.modify_and_log("""alter table {dt} drop constraint {constraint}""".format(dt=dt, constraint=constraint[0][0]))
        for statement in self.STATEMENTS:
            self.modify_and_log(statement)

        return self.success()