-- ============================================================================================
-- Schema view-kat generál a paraméterekben megadott táblákhoz és schemákhoz,
-- illetve, ha léteznek akkor frissíti őket.
--
-- Ha a @pTables vagy a @pSchemas paraméter értéke NULL, akkor az összes
-- táblára/schemára megcsinálja a view-kat

-- @pTables: tábla nevek vesszővel elválasztva, pl.: 'T_TANEV, T_UJTABLA,T_TANULO'
-- @pSchemas: schema nevek vesszővel elválasztva, pl.: 'KR_asd_Schema,dbo, KR_dobokata_Schema'
-- ============================================================================================
DROP PROCEDURE IF EXISTS dev.uspCreateSchemaViews
GO

CREATE PROCEDURE dev.uspCreateSchemaViews
   @pTables nvarchar(max)
  ,@pSchemas nvarchar(max) = NULL
  ,@pDebugMode bit = 0
AS
SET NOCOUNT ON;

DECLARE 
   @tableName sysname
  ,@schemaName sysname
  ,@intezmenyId int
  ,@tanevId int
  ,@notExistingObject sysname
  ,@tanevColumn sysname
  ,@intezmenyColumn sysname
  ,@toroltColumn sysname
  ,@dbName sysname = DB_NAME()
  ,@sql nvarchar(max)

SELECT TOP 1 @notExistingObject = s.value
FROM STRING_SPLIT(@pTables, ',') s 
WHERE NOT EXISTS (
    SELECT 1 FROM sys.tables t 
    WHERE s.value = t.name
    AND t.name LIKE 'T[_]%'
    AND t.schema_id = 1
)

IF @notExistingObject IS NOT NULL BEGIN
    RAISERROR('A megadott tábla nem létezik a %s adatbázisban: %s', 16, 1, @dbName, @notExistingObject)
    RETURN
END

SELECT TOP 1 @notExistingObject = s.value
FROM STRING_SPLIT(@pSchemas, ',') s 
WHERE NOT EXISTS (
    SELECT 1 FROM sys.schemas t
    WHERE s.value = t.name
)

IF @notExistingObject IS NOT NULL BEGIN
    RAISERROR('A megadott schema nem létezik a %s adatbázisban: %s', 16, 1, @dbName, @notExistingObject)
    RETURN
END

DECLARE @ViewCursor CURSOR

SET @ViewCursor = CURSOR FOR
    SELECT 
       tbl.name AS TableName
      ,sch.name AS SchemaName
      ,i.ID AS IntezmenyId
      ,tv.ID AS TanevId
      ,ctv.name AS TanevColumn
      ,cin.name AS IntezmenyColumn
      ,ctr.name AS ToroltColumn
    FROM sys.tables tbl
      LEFT JOIN sys.columns cid ON tbl.object_id = cid.object_id AND cid.name IN ('ID')
      LEFT JOIN sys.columns cin ON tbl.object_id = cin.object_id AND cin.name IN ('C_INTEZMENYID', 'C_ALINTEZMENYID')
      LEFT JOIN sys.columns ctv ON tbl.object_id = ctv.object_id AND ctv.name IN ('C_TANEVID', 'C_ALTANEVID')
      LEFT JOIN sys.columns ctr ON tbl.object_id = ctr.object_id AND ctr.name IN ('TOROLT')
      CROSS JOIN sys.schemas sch 
      INNER JOIN STRING_SPLIT(ISNULL(@pTables, ''),  ',') t ON (@pTables IS NULL OR t.value = tbl.name)
      INNER JOIN STRING_SPLIT(ISNULL(@pSchemas, ''), ',') s ON (@pSchemas IS NULL OR sch.name = s.value)
      LEFT JOIN T_INTEZMENY i ON 'KR_' + i.C_AZONOSITO + '_Schema' = sch.name AND i.TOROLT = 'F'
      LEFT JOIN T_TANEV tv ON tv.C_INTEZMENYID = i.ID AND tv.C_AKTIV = 'T' AND tv.TOROLT = 'F'
    WHERE (sch.name like 'KR[_]%[_]Schema' or sch.name = 'dbo')
    AND tbl.schema_id = 1
    AND tbl.name like 'T[_]%'
    AND (tbl.name not like 'T[_]DKT[_]%' OR tbl.name IN ('T_DKT_FILE', 'T_DKT_FELADAT', 'T_DKT_FELADATFILE'))
    AND tbl.name NOT IN ('T_ENTITYATTRIBUTEHISTORY', 'T_ENTITYHISTORY', 'T_ENTITYATTRIBUTEHISTORY_ARCHIVE', 'T_ENTITYBLOBVALUES', 'T_ENTITYBLOBVALUES_ARCHIVE')
    ORDER BY sch.name, tbl.name

OPEN @ViewCursor

FETCH NEXT FROM @ViewCursor
INTO @tableName, @schemaName, @intezmenyId, @tanevId, @tanevColumn, @intezmenyColumn, @toroltColumn
 
WHILE @@FETCH_STATUS = 0
    BEGIN

    -- _OSSZES view-ok
    SET @sql = '
CREATE OR ALTER VIEW [' + @schemaName + '].[' +  @tableName + '_OSSZES] AS 
SELECT * 
FROM dbo.' + @tableName + CHAR(13)
+ CASE WHEN @intezmenyColumn > '' AND @intezmenyId > 0 THEN CONCAT('WHERE ', @intezmenyColumn, ' = ', @intezmenyId, CHAR(13)) ELSE '' END

    EXEC sp_executesql @sql
    IF @pDebugMode = 1 OR @@ERROR <> 0 PRINT @schemaName + '.' +  @tableName + '_OSSZES (osszes)'
    IF @pDebugMode = 1 PRINT @sql

    -- normál view-ok
    SET @sql = '
CREATE OR ALTER VIEW [' + @schemaName + '].[' +  @tableName + '] AS 
SELECT * 
FROM dbo.' + @tableName + CHAR(13)
+ 'WHERE '
+ CASE WHEN @intezmenyColumn > '' AND @intezmenyId > 0 THEN CONCAT(@intezmenyColumn, ' = ', @intezmenyId, CHAR(13), 'AND ') ELSE '' END
+ CASE WHEN @tanevColumn > '' AND @tanevId > 0 THEN CONCAT(@tanevColumn + ' = ', @tanevId, CHAR(13), 'AND ') ELSE '' END
+ CASE WHEN @toroltColumn > '' THEN 'TOROLT = ''F''' + CHAR(13) + 'AND ' ELSE '' END

    IF RIGHT(@sql, 4) = 'AND ' SET @sql = LEFT(@sql, LEN(@sql) - 4)
    IF RIGHT(@sql, 6) = 'WHERE ' SET @sql = LEFT(@sql, LEN(@sql) - 6)
    IF @schemaName = 'dbo' SET @sql = null

    EXEC sp_executesql @sql
    IF @pDebugMode = 1 OR @@ERROR <> 0 PRINT @schemaName + '.' +  @tableName + ' (normal)'
    IF @pDebugMode = 1 PRINT @sql

    FETCH NEXT FROM @ViewCursor
    INTO @tableName, @schemaName, @intezmenyId, @tanevId, @tanevColumn, @intezmenyColumn, @toroltColumn

    END

CLOSE @ViewCursor

GO