if(not exists (select * 
				from sys.columns c
				join sys.tables t on t.object_id = c.object_id
				where t.name = 'T_RECIPE' and c.name = 'year'))
begin
    ALTER TABLE [AKUZ].[T_RECIPE]
    ADD [Year] INT NULL;

    UPDATE r
    SET r.Year = YEAR(m.PostingDate)
    FROM [AKUZ].[T_RECIPE] r
    JOIN [AKUZ].[T_MEDICAL_RECORD] m ON r.MedicalRecordID = m.MedicalRecordID;

    ALTER TABLE [AKUZ].[T_RECIPE]
    ALTER COLUMN [Year] INT NOT NULL;
end
go

if(exists (select *
				from sys.indexes i
				join sys.tables t on t.object_id = i.object_id
				where t.name = 'T_RECIPE' and i.name = 'UQ_T_RECIPE_SerialNumberDeleted'))
begin
	DROP INDEX [UQ_T_RECIPE_SerialNumberDeleted] ON [AKUZ].[T_RECIPE]
end

if(exists (select *
				from sys.indexes i
				join sys.tables t on t.object_id = i.object_id
				where t.name = 'T_RECIPE' and i.name = 'UQ_T_RECIPE_SerialNumberDeleted_recipeForm'))
begin
	DROP INDEX [UQ_T_RECIPE_SerialNumberDeleted_recipeForm] ON [AKUZ].[T_RECIPE]
end
go

declare @type_ int = (select top (1) EntityID from vclib.T_ENTITIES where EntName = 'recipe_form' and [schema] = 'akuz')
declare @sql nvarchar(1000) 
set @sql = N'
CREATE UNIQUE INDEX [UQ_T_RECIPE_SerialNumberDeleted_recipeForm] ON [AKUZ].[T_RECIPE]
(
[Serial] ASC,
[Number] ASC,
[Year]
)
where Deleted = 0 and MedicalRecordType = ' + cast(@type_ as nvarchar(11))

exec sp_executesql @sql;

set @sql = N'CREATE UNIQUE INDEX [UQ_T_RECIPE_SerialNumberDeleted] ON [AKUZ].[T_RECIPE]
(
[Serial] ASC,
[Number] ASC
)
where Deleted = 0 and MedicalRecordType <>' + cast(@type_ as nvarchar(11))

exec sp_executesql @sql;

if(exists (select * 
				from sys.columns c
				join sys.tables t on t.object_id = c.object_id
				where t.name = 'T_RECIPE' and c.name = 'SerialNumberDeleted_UQ'))
begin
	ALTER TABLE AKUZ.T_RECIPE DROP COLUMN SerialNumberDeleted_UQ
end