﻿--Migration
DECLARE @sql nvarchar(max) = ''

SELECT @sql +=
    'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '].[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT [' + name + ']
    '
FROM sys.foreign_keys
WHERE object_name(referenced_object_id) IN (
 'T_DKT_DIC_MINDENUZENETKOZPONTIFALONTIPUS'
,'T_DKT_DIC_MUNKATERTIPUS'
,'T_DKT_DIC_TANULOFELADATBEADASSTATUSZ'
,'T_DKT_DIC_UZENETFORRASTIPUS'
,'T_DKT_DIC_UZENETLATHATOSAG'
,'T_DKT_DIC_UZENOFALTIPUS'
,'T_DKT_EGYEDIUZENOFALFELHASZNALO'
,'T_DKT_TANULOBEALLITAS'
,'T_DKT_TANULOFELADATBEADAS'
,'T_DKT_TANULOFELADATBEADASFILE'
,'T_DKT_UZENETALKALMAZOTTTANULOPRIVATUZENOFAL'
,'T_DKT_UZENETALKALMAZOTTUZENOFAL'
,'T_DKT_UZENETMUNKATERUZENOFAL'
,'T_DKT_UZENOFAL'
)

EXEC sp_executesql @sql
GO

DECLARE @sql nvarchar(max) = ''

SELECT @sql += 
    'ALTER TABLE [' +  OBJECT_SCHEMA_NAME(parent_object_id) +
    '].[' + OBJECT_NAME(parent_object_id) + 
    '] DROP CONSTRAINT [' + name + ']
    '
FROM sys.default_constraints x  
WHERE object_name(parent_object_id) IN (
 'T_DKT_DIC_MINDENUZENETKOZPONTIFALONTIPUS'
,'T_DKT_DIC_MUNKATERTIPUS'
,'T_DKT_DIC_TANULOFELADATBEADASSTATUSZ'
,'T_DKT_DIC_UZENETFORRASTIPUS'
,'T_DKT_DIC_UZENETLATHATOSAG'
,'T_DKT_DIC_UZENOFALTIPUS'
,'T_DKT_EGYEDIUZENOFALFELHASZNALO'
,'T_DKT_TANULOBEALLITAS'
,'T_DKT_TANULOFELADATBEADAS'
,'T_DKT_TANULOFELADATBEADASFILE'
,'T_DKT_UZENETALKALMAZOTTTANULOPRIVATUZENOFAL'
,'T_DKT_UZENETALKALMAZOTTUZENOFAL'
,'T_DKT_UZENETMUNKATERUZENOFAL'
,'T_DKT_UZENOFAL'
)

EXEC sp_executesql @sql
GO

DROP TABLE IF EXISTS T_DKT_DIC_MINDENUZENETKOZPONTIFALONTIPUS
DROP TABLE IF EXISTS T_DKT_DIC_MUNKATERTIPUS
DROP TABLE IF EXISTS T_DKT_DIC_TANULOFELADATBEADASSTATUSZ
DROP TABLE IF EXISTS T_DKT_DIC_UZENETFORRASTIPUS
DROP TABLE IF EXISTS T_DKT_DIC_UZENETLATHATOSAG
DROP TABLE IF EXISTS T_DKT_DIC_UZENOFALTIPUS
DROP TABLE IF EXISTS T_DKT_EGYEDIUZENOFALFELHASZNALO
DROP TABLE IF EXISTS T_DKT_TANULOBEALLITAS
DROP TABLE IF EXISTS T_DKT_TANULOFELADATBEADAS
DROP TABLE IF EXISTS T_DKT_TANULOFELADATBEADASFILE
DROP TABLE IF EXISTS T_DKT_UZENETALKALMAZOTTTANULOPRIVATUZENOFAL
DROP TABLE IF EXISTS T_DKT_UZENETALKALMAZOTTUZENOFAL
DROP TABLE IF EXISTS T_DKT_UZENETMUNKATERUZENOFAL
DROP TABLE IF EXISTS T_DKT_UZENOFAL

GO
PRINT N'Altering [dbo].[T_DKT_FELADAT]...';


GO
ALTER TABLE [dbo].[T_DKT_FELADAT]
    ADD [C_GROUPID]       UNIQUEIDENTIFIER NULL,
        [C_IDOTARTAMPERC] INT              NULL,
        [C_KESZ]          CHAR (1)         DEFAULT ('F') NOT NULL;


GO
PRINT N'Altering [dbo].[T_DKT_FILE]...';


GO
ALTER TABLE [dbo].[T_DKT_FILE]
    ADD [C_VEGLEGESITESDATUM] DATETIME NULL;


GO

ALTER TABLE T_DKT_ALKALMAZOTTBEALLITAS 
DROP CONSTRAINT IF EXISTS FK_DKT_AlkalmazottBeallitas_HfErteklesTipusId_IntezmenyId_TanevId_REF_DKT_DIC_FeladatErtekelesTipus
GO

ALTER TABLE T_DKT_ALKALMAZOTTBEALLITAS 
DROP CONSTRAINT IF EXISTS FK_DKT_AlkalmazottBeallitas_OfErtekelesTipusId_IntezmenyId_TanevId_REF_DKT_DIC_FeladatErtekelesTipus
GO

EXEC dev.uspDropDefaultConstraint @pTableName = 'T_DKT_ALKALMAZOTTBEALLITAS', @pColumnName = 'C_HFERTEKLESTIPUSID'
GO

DROP INDEX NCI_DKT_AlkalmazottBeallitas_IntezmenyId_TanevId_HfErteklesTipusId ON T_DKT_ALKALMAZOTTBEALLITAS
GO

ALTER TABLE T_DKT_ALKALMAZOTTBEALLITAS 
DROP COLUMN IF EXISTS C_HFERTEKLESTIPUSID
GO

ALTER TABLE T_DKT_ALKALMAZOTTBEALLITAS
ADD C_HFERTEKELESTIPUSID int DEFAULT 0 NOT NULL
GO

CREATE INDEX NCI_DKT_AlkalmazottBeallitas_IntezmenyId_TanevId_HfErtekelesTipusId 
ON T_DKT_ALKALMAZOTTBEALLITAS (C_INTEZMENYID, C_TANEVID, C_HFERTEKELESTIPUSID)
GO

ALTER TABLE [DBO].T_DKT_ALKALMAZOTTBEALLITAS 
  ADD CONSTRAINT FK_DKT_AlkalmazottBeallitas_HfErtekelesTipusId_IntezmenyId_TanevId_REF_DKT_DIC_FeladatErtekelesTipus 
  FOREIGN KEY (C_HFERTEKELESTIPUSID, C_INTEZMENYID, C_TANEVID) 
  REFERENCES [dbo].T_DKT_DIC_FELADATERTEKELESTIPUS (ID, C_INTEZMENYID, C_TANEVID)
GO

ALTER TABLE [DBO].T_DKT_ALKALMAZOTTBEALLITAS 
  ADD CONSTRAINT FK_DKT_AlkalmazottBeallitas_OfErtekelesTipusId_IntezmenyId_TanevId_REF_DKT_DIC_FeladatErtekelesTipus 
  FOREIGN KEY (C_OFERTEKELESTIPUSID, C_INTEZMENYID, C_TANEVID) 
  REFERENCES [dbo].T_DKT_DIC_FELADATERTEKELESTIPUS (ID, C_INTEZMENYID, C_TANEVID)
GO


EXEC dev.uspCreateSchemaViews 'T_DKT_FILE, T_DKT_FELADAT, T_DKT_ALKALMAZOTTBEALLITAS'
GO

EXEC dev.sp_Global_GenerateAsyncAuditTriggerAll
GO

ALTER QUEUE auditLog.[AuditQueue]
WITH ACTIVATION (
  STATUS = ON,
  PROCEDURE_NAME = auditLog.usp_AuditProcessing,
  MAX_QUEUE_READERS = 10,
  EXECUTE AS 'Kreta_tech_user'
);
GO

