SET NOCOUNT ON

DECLARE
 @iTemp INT,
 @sql NVARCHAR(3000),
 @idTab INT,
 @sysTabName NVARCHAR(128),
 @hvwName NVARCHAR(128),
 @idCol INT,
 @trigger NVARCHAR(max)


SET @sysTabName = '_TabVyroba_RamcovyPlan'
SET @hvwName = 'hvw_Vyroba_RamcovyPlan'


SET @iTemp = ISNULL( (SELECT MAX(ID) FROM dbo.TabDefTabUzivTabulka), 1)
DBCC CHECKIDENT (TabDefTabUzivTabulka, RESEED, @iTemp)
SET @iTemp = ISNULL( (SELECT MAX(ID) FROM dbo.TabDefTabUzivAtributy), 1)
DBCC CHECKIDENT (TabDefTabUzivAtributy, RESEED, @iTemp)
SET @iTemp = ISNULL( (SELECT MAX(ID) FROM dbo.TabDefTabUzivConstraint), 1)
DBCC CHECKIDENT (TabDefTabUzivConstraint, RESEED, @iTemp)



-- tabulka
SET @idTab = (SELECT ID FROM dbo.TabDefTabUzivTabulka WHERE NazevTabulky=@sysTabName)
IF (@idTab IS NULL)
 BEGIN
  INSERT dbo.TabDefTabUzivTabulka (Skupina, Popis, NazevTabulky, NazevPrehledu, NastaveniAkceNovy, NastaveniAkceOprava, NastaveniAkceZrusit)
   SELECT N'', 'Rámcový plán', @sysTabName, @hvwName, 1, 1, 1
  SET @idTab = SCOPE_IDENTITY()
 END







-- sloupce
IF (@idTab IS NOT NULL)
 BEGIN
/*
   INSERT dbo.TabDefTabUzivAtributy
      (IDDefinice, Poradi, DTUHeliosAttr, DTUAtrJmenoSys, DTUAtrJmenoVerejne, DTUAtrTyp, DTUAtrDelka, DTUAtrNull, DTUAtrServerDefault, DTUAtrDruh,
       DTUAtrPocitany, DTUAtrKonverze, DTUAtrSirkaSloupce, DTUAtrMaska, DTUAtrVerejny, DTUAtrVyzadovany, DTUAtrSumovat, DTUAtrTisk, DTUEditovatelny,
       DTUReadOnly, DTUHromadneZmeny, EdAtrEditor, EdAtrPoradi, EdAtrHlidat, EdAtrPrenosAtr, EdAtrSkrytyPrenos, EdAtrInfoAtr1, EdAtrInfoAtr2, EdAtrPrenosDPSN, EdAtrPrenosIDFiltr, EdAtrPodminka)

DTUAtrTyp
0=INT
1=SMALLINT
2=TINYINT
3=BIGINT
4=GUID
5=BIT
6=NVARCHAR
7=NTEXT
8=VARBINARY(MAX)
9=DATETIME
10=NUMERIC(5,2)
11=NUMERIC(19,6)
12=FLOAT
13=VARCHAR
14=NVARCHAR(MAX)
*/


  SET @idCol = (SELECT ID FROM dbo.TabDefTabUzivAtributy WHERE IDDefinice=@idTab AND DTUAtrJmenoSys=N'IDKmenZbozi')
  IF (@idCol IS NULL)
   INSERT dbo.TabDefTabUzivAtributy
      (IDDefinice, Poradi, DTUAtrJmenoSys, DTUAtrJmenoVerejne, DTUAtrTyp, DTUAtrDelka, DTUAtrNull, DTUAtrServerDefault, DTUAtrDruh,
       DTUAtrPocitany, DTUAtrErrTextStr, DTUAtrKonverze, DTUAtrSirkaSloupce, DTUAtrMaska, DTUAtrVerejny, DTUAtrVyzadovany, DTUAtrSumovat, DTUAtrTisk, DTUEditovatelny,
       DTUReadOnly, DTUHromadneZmeny, EdAtrPrenosDPSN, EdAtrPrenosAtr)
   SELECT @idTab, 1, N'IDKmenZbozi', N'ID kmenové karty', 0, 0, 0, N'', 0,     -- 0 = int
          NULL, NULL, NULL, 20, N'', 0, 1, 0, 1, 1,
          0, 0, N'2', N'ID'
  IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS where table_name=@sysTabName AND column_name=N'IDKmenZbozi')
   BEGIN
    SET @sql = N'ALTER TABLE ' + @sysTabName + ' ADD IDKmenZbozi INT NOT NULL'
    EXEC sp_executesql @sql
   END



  SET @idCol = (SELECT ID FROM dbo.TabDefTabUzivAtributy WHERE IDDefinice=@idTab AND DTUAtrJmenoSys=N'Rok')
  IF (@idCol IS NULL)
   INSERT dbo.TabDefTabUzivAtributy
      (IDDefinice, Poradi, DTUAtrJmenoSys, DTUAtrJmenoVerejne, DTUAtrTyp, DTUAtrDelka, DTUAtrNull, DTUAtrServerDefault, DTUAtrDruh,
       DTUAtrPocitany, DTUAtrErrTextStr, DTUAtrKonverze, DTUAtrSirkaSloupce, DTUAtrMaska, DTUAtrVerejny, DTUAtrVyzadovany, DTUAtrSumovat, DTUAtrTisk, DTUEditovatelny,
       DTUReadOnly, DTUHromadneZmeny, EdAtrPrenosDPSN)
   SELECT @idTab, 2, N'Rok', N'Rok', 1, 0, 0, N'', 0,
          NULL, NULL, NULL, 50, N'', 1, 1, 0, 1, 1,
          0, 0, N''
  IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS where table_name=@sysTabName AND column_name=N'Rok')
   BEGIN
    SET @sql = N'ALTER TABLE ' + @sysTabName + ' ADD Rok SMALLINT NOT NULL'
    EXEC sp_executesql @sql
   END


  SET @idCol = (SELECT ID FROM dbo.TabDefTabUzivAtributy WHERE IDDefinice=@idTab AND DTUAtrJmenoSys=N'Mesic')
  IF (@idCol IS NULL)
   INSERT dbo.TabDefTabUzivAtributy
      (IDDefinice, Poradi, DTUAtrJmenoSys, DTUAtrJmenoVerejne, DTUAtrTyp, DTUAtrDelka, DTUAtrNull, DTUAtrServerDefault, DTUAtrDruh,
       DTUAtrPocitany, DTUAtrErrTextStr, DTUAtrKonverze, DTUAtrSirkaSloupce, DTUAtrMaska, DTUAtrVerejny, DTUAtrVyzadovany, DTUAtrSumovat, DTUAtrTisk, DTUEditovatelny,
       DTUReadOnly, DTUHromadneZmeny, EdAtrPrenosDPSN)
   SELECT @idTab, 3, N'Mesic', N'Měsíc', 2, 0, 0, N'', 0,
          NULL, NULL, NULL, 50, N'', 1, 1, 0, 1, 1,
          0, 0, N''
  IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS where table_name=@sysTabName AND column_name=N'Mesic')
   BEGIN
    SET @sql = N'ALTER TABLE ' + @sysTabName + ' ADD Mesic TINYINT NOT NULL'
    EXEC sp_executesql @sql
   END


  SET @idCol = (SELECT ID FROM dbo.TabDefTabUzivAtributy WHERE IDDefinice=@idTab AND DTUAtrJmenoSys=N'Mnozstvi')
  IF (@idCol IS NULL)
   INSERT dbo.TabDefTabUzivAtributy
      (IDDefinice, Poradi, DTUAtrJmenoSys, DTUAtrJmenoVerejne, DTUAtrTyp, DTUAtrDelka, DTUAtrNull, DTUAtrServerDefault, DTUAtrDruh,
       DTUAtrPocitany, DTUAtrErrTextStr, DTUAtrKonverze, DTUAtrSirkaSloupce, DTUAtrMaska, DTUAtrVerejny, DTUAtrVyzadovany, DTUAtrSumovat, DTUAtrTisk, DTUEditovatelny,
       DTUReadOnly, DTUHromadneZmeny, EdAtrPrenosDPSN)
   SELECT @idTab, 4, N'Mnozstvi', N'Množství', 11, 0, 0, N'0', 0,
          NULL, NULL, NULL, 70, N'#,##0.##', 1, 1, 1, 1, 1,
          0, 0, N''
  IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS where table_name=@sysTabName AND column_name=N'Mnozstvi')
   BEGIN
    SET @sql = N'ALTER TABLE ' + @sysTabName + ' ADD Mnozstvi NUMERIC(19,6) NOT NULL DEFAULT 0.0'
    EXEC sp_executesql @sql
   END


 END -- @idTab IS NOT NULL




-- indexy
IF (@idTab IS NOT NULL)
 IF NOT EXISTS (SELECT 1 FROM dbo.TabDefTabUzivConstraint WHERE IDDefinice=@idTab AND Typ=1 AND JmenoSys=N'PK__' + @sysTabName + N'__ID')
  INSERT dbo.TabDefTabUzivConstraint
     (IDDefinice, JmenoSys, Typ, MojeAtributy,   CiziTabulka, CiziAtributy, IncludedAttr, VazbaNazevMoje, VazbaNazevMojeSys, VazbaNazevCizi, VazbaNazevCiziSys)
   SELECT @idTab, N'PK__' + @sysTabName + N'__ID', 1, N'ID',   N'', N'', N'', N'', N'', N'', N''

IF (@idTab IS NOT NULL) AND EXISTS (SELECT 1 FROM dbo.TabDefTabUzivAtributy WHERE IDDefinice=@idTab AND DTUAtrJmenoSys=N'IDKmenZbozi')
 IF NOT EXISTS (SELECT 1 FROM dbo.TabDefTabUzivConstraint WHERE IDDefinice=@idTab AND Typ=0 AND JmenoSys=N'IX__' + @sysTabName + N'__IDKmenZbozi')
  INSERT dbo.TabDefTabUzivConstraint
     (IDDefinice, JmenoSys, Typ, MojeAtributy,   CiziTabulka, CiziAtributy, IncludedAttr, VazbaNazevMoje, VazbaNazevMojeSys, VazbaNazevCizi, VazbaNazevCiziSys)
   SELECT @idTab, N'IX__' + @sysTabName + N'__IDKmenZbozi', 0, N'IDKmenZbozi',   N'', N'', N'', N'', N'', N'', N''