# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.constants import *
from ppms.migration.packet import *

QUERY = {DBMS_MSSQL: """-- One Update constraint for sub-table 503 if an data field is moved from one data area to another
CREATE TRIGGER USDT503ID411 ON DT411
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
IF UPDATE(DI000306) OR UPDATE(DI000307)
UPDATE DT503 SET DT503.DI024190 = INSERTED.DI000306, DT503.DI024191 = INSERTED.DI000307 FROM INSERTED, DELETED
WHERE INSERTED.UUID = DELETED.UUID AND DT503.DI024190 = DELETED.DI000306 AND DT503.DI024191 = DELETED.DI000307
IF @@ERROR<>0
ROLLBACK TRAN
END""",
        DBMS_ORACLE: """BEGIN
  DECLARE
    SQLCMD VARCHAR(1024);
  BEGIN
    SELECT 'CREATE OR REPLACE TRIGGER USDT503ID411 ' || 
      'AFTER UPDATE ON DT411 ' || 
      'FOR EACH ROW ' || 
      'WHEN (NEW.DI000306 != OLD.DI000306 OR NEW.DI000307 != OLD.DI000307) ' || 
      'BEGIN ' || 
      'UPDATE DT503 SET DI024190 = :NEW.DI000306, DI024191 = :NEW.DI000307 ' || 
      'WHERE DI024190 = :OLD.DI000306 AND DI024191 = :OLD.DI000307; ' || 
      'END;' INTO sqlcmd FROM DUAL;
    EXECUTE IMMEDIATE sqlcmd;
    EXCEPTION
      when others then
        dbms_output.put_line (SQLERRM||'#');
  END;
END;"""}


class EnabledModuleCopyConstraint(SQLPacket):
    '''Add a constraint relevant for module copying'''
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 11294

    def fix(self, runner):
        ppms.db_modify(QUERY[ppms.db_get_dbms_name()])

        return self.success()
