-------------------------------------------------Денислав - 24.06.2024г-------------------------------------------------------------
--------------------Добавяне на нова колона  [rows_group] в sys_cache_tables_registry, поради новите номенклатури. Трябва да се знае, кой ред към коя група принадлежи-------------------------

IF
(
		NOT EXISTS (
			SELECT 1
			FROM INFORMATION_SCHEMA.COLUMNS c
			WHERE c.TABLE_NAME = 'sys_cache_tables_registry'
				AND c.COLUMN_NAME = 'rows_group'
			)
		)
BEGIN
	-- Add the created_at and updated_at columns
	ALTER TABLE sys_cache_tables_registry
	ADD rows_group INT DEFAULT(NULL)
END

----------------------------------------------------------Край 24.06.2024г------------------------------------------------------------------

-------------------------------------------------Денислав - 15.09.2024г-------------------------------------------------------------
IF
(
		NOT EXISTS (
			SELECT 1
			FROM INFORMATION_SCHEMA.COLUMNS c
			WHERE c.TABLE_NAME = 'sys_acl_resource_role_rel'
				AND c.COLUMN_NAME = 'objectId'
			)
		)
BEGIN
	-- Add the created_at and updated_at columns
	ALTER TABLE sys_acl_resource_role_rel
	ADD objectId INT DEFAULT(NULL)
END

IF
(
		NOT EXISTS (
			SELECT 1
			FROM INFORMATION_SCHEMA.COLUMNS c
			WHERE c.TABLE_NAME = 'sys_acl_resource_role_rel'
				AND c.COLUMN_NAME = 'objectType'
			)
		)
BEGIN
	-- Add the created_at and updated_at columns
	ALTER TABLE sys_acl_resource_role_rel
	ADD objectType INT DEFAULT(NULL)
END
IF
(
		NOT EXISTS (
			SELECT 1
			FROM INFORMATION_SCHEMA.COLUMNS c
			WHERE c.TABLE_NAME = 'sys_acl_resource_user_rel'
				AND c.COLUMN_NAME = 'objectId'
			)
		)
BEGIN
	-- Add the created_at and updated_at columns
	ALTER TABLE sys_acl_resource_user_rel
	ADD objectId INT DEFAULT(NULL)
END

IF
(
		NOT EXISTS (
			SELECT 1
			FROM INFORMATION_SCHEMA.COLUMNS c
			WHERE c.TABLE_NAME = 'sys_acl_resource_user_rel'
				AND c.COLUMN_NAME = 'objectType'
			)
		)
BEGIN
	-- Add the created_at and updated_at columns
	ALTER TABLE sys_acl_resource_user_rel
	ADD objectType INT DEFAULT(NULL)
END

----------------------------------------------------------Край 15.09.2024г------------------------------------------------------------------

-------------------------------------------------Денислав - 20.01.2025г-------------------------------------------------------------

IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'sys_acl_operations'
      AND COLUMN_NAME = 'priority'
)
BEGIN

    TRUNCATE TABLE sys_acl_operations

	
		ALTER TABLE sys_acl_operations
        ADD [priority] NVARCHAR(50) NULL

declare @sql nvarchar(max)='
    INSERT INTO sys_acl_operations ([name], [description], [priority])
    VALUES
        (''Read/Update'', ''Everything is allowed'', ''4''),
        (''ReadOnly'', ''No Edit allowed'', ''3''),
        (''Hidden'', ''Sent to the client, but hidden(accessible in the page source)'', ''2''),
        (''Forbidden'', ''Not sent to the client'', ''1''),
        (''Create only'', ''Fillable in create document only'', ''0''),
        (''Delete'', ''Update to empty'', ''0'')
		'
	exec (@sql)


    ALTER TABLE sys_acl_resource_role_rel
        ALTER COLUMN operation NVARCHAR(50) NULL
		update r set  r.operation=o.name
		from [dbo].[sys_acl_resource_role_rel] r 
		join [dbo].[sys_acl_operations] o on o.id=isnull(TRY_CONVERT(int,r.operation),is_allowed)

    ALTER TABLE sys_acl_resource_user_rel
        ALTER COLUMN [operation] NVARCHAR(50) NULL
		update r set  r.operation=o.name
		from [dbo].[sys_acl_resource_user_rel] r 
		join [dbo].[sys_acl_operations] o on o.id=isnull(TRY_CONVERT(int,r.operation),is_allowed)



END

----------------------------------------------------------Край 20.01.2025г------------------------------------------------------------------

-------------------------------------------------Lora - 27.01.2024г-------------------------------------------------------------
--------------------Added new column [calendar_color] in sys_users for calendar customization-------------------------

IF
(
		NOT EXISTS (
			SELECT 1
			FROM INFORMATION_SCHEMA.COLUMNS c
			WHERE c.TABLE_NAME = 'sys_users'
				AND c.COLUMN_NAME = 'calendar_color'
			)
		)
BEGIN
	ALTER TABLE sys_users
	ADD calendar_color NVARCHAR(50) DEFAULT(NULL)
END

----------------------------------------------------------Lora 27.01.2024г------------------------------------------------------------------


-------------------------------------------------Денислав - 29.01.2025г-------------------------------------------------------------

IF
(
		NOT EXISTS (
			SELECT 1
			FROM INFORMATION_SCHEMA.COLUMNS c
			WHERE c.TABLE_NAME = 'sys_element'
				AND c.COLUMN_NAME = 'nmcl_optionsGroupField'
			)
		)
BEGIN
	-- Add the created_at and updated_at columns
	ALTER TABLE sys_element
	ADD nmcl_optionsGroupField INT DEFAULT(NULL)
END

----------------------------------------------------- Край 29.01.2025г------------------------------------------------------------------

-------------------------------------------------Паско- 02.07.2025г-------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'sys_files_imported')
BEGIN

    CREATE TABLE sys_files_imported (
        id INT PRIMARY KEY IDENTITY(1,1),
        file_name NVARCHAR(250),
        file_extension NVARCHAR(10),
        file_size NVARCHAR(50),
        origin NVARCHAR(50),
        sys_file_name NVARCHAR(250),
        id_user INT,
        file_upload_date DATETIME
    );

END;
------------------------------------------------- Край 02.07.2025г-------------------------------------------------------------

-------------------------------------------------Lora - 20.02.2025г-------------------------------------------------------------

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'sys_users_hierarchy')
BEGIN

    CREATE TABLE sys_users_hierarchy (
        id INT PRIMARY KEY IDENTITY(1,1),
        id_manager INT,
        id_employee INT
    );

END;
------------------------------------------------- Край 20.02.2025г-------------------------------------------------------------

------------------------------------------------- Паско - 20.02.2025г-------------------------------------------------------------

IF
NOT EXISTS (
		SELECT 1
		FROM INFORMATION_SCHEMA.TABLES t
		WHERE t.TABLE_NAME = 'external_polycomp_products'
		)
BEGIN
CREATE TABLE [dbo].[external_polycomp_products]
(
	[articlemainkind] [nvarchar](100) NULL,
	[articlemainkinduid] [nvarchar](50) NULL,
	[articlekind] [nvarchar](100) NULL,
	[articlekinduid] [nvarchar](50) NULL,
	[available] [nvarchar](50) NULL,
	[catalog_num] [nvarchar](50) NULL,
	[code] [nvarchar](50) NULL,
	[currency] [nvarchar](50) NULL,
	[description] [nvarchar](4000) NULL,
	[ean_barcode] [nvarchar](50) NULL,
	[characteristics] [nvarchar](max) NULL,
	[id] [nvarchar](50) NULL,
	[kkprice] [nvarchar](50) NULL,
	[model] [nvarchar](4000) NULL,
	[name] [nvarchar](100) NULL,
	[picture_name] [nvarchar](1000) NULL,
	[price] [nvarchar](50) NULL,
	[price_dds] [nvarchar](50) NULL,
	[realprice_bgl] [nvarchar](50) NULL,
	[realprice_bgl_dds] [nvarchar](50) NULL,
	[kkprice_bgl] [nvarchar](50) NULL,
	[kkprice_bgl_dds] [nvarchar](50) NULL,
	[realprice] [nvarchar](50) NULL,
	[small_picture] [nvarchar](500) NULL,
	[vendorname] [nvarchar](100) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END

IF
EXISTS (
		SELECT 1
		FROM INFORMATION_SCHEMA.TABLES t
		WHERE t.TABLE_NAME = 'external_polycomp_products'
		)
BEGIN
	ALTER TABLE external_polycomp_products
	ALTER COLUMN picture_name NVARCHAR(1000) NULL
END

---------------Край 20/02/2025----------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------Lora - 27.02.2025г-------------------------------------------------------------

--------------------Added new column [help_html] in system object tables for "Help" Button-----------------------------------

--------------------Table sys_nmcl-----------------------------------

IF
(
	NOT EXISTS (
		SELECT 1
		FROM INFORMATION_SCHEMA.COLUMNS c
		WHERE c.TABLE_NAME = 'sys_nmcl'
			AND c.COLUMN_NAME = 'help_html'
		)
	)
BEGIN
	ALTER TABLE sys_nmcl
	ADD help_html NVARCHAR(MAX) DEFAULT(NULL)
END


--------------------Table sys_doctype-----------------------------------
IF
(
	NOT EXISTS (
		SELECT 1
		FROM INFORMATION_SCHEMA.COLUMNS c
		WHERE c.TABLE_NAME = 'sys_doctype'
			AND c.COLUMN_NAME = 'help_html'
		)
	)
BEGIN
	ALTER TABLE sys_doctype
	ADD help_html NVARCHAR(MAX) DEFAULT(NULL)
END


--------------------Table sys_reports-----------------------------------
IF
(
	NOT EXISTS (
		SELECT 1
		FROM INFORMATION_SCHEMA.COLUMNS c
		WHERE c.TABLE_NAME = 'sys_reports'
			AND c.COLUMN_NAME = 'help_html'
		)
	)
BEGIN
	ALTER TABLE sys_reports
	ADD help_html NVARCHAR(MAX) DEFAULT(NULL)
END
------------------------------------------------- Край 27.02.2025г-------------------------------------------------------------

-------------------------------------------------Денислав - 5.03.2025г-------------------------------------------------------------

IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'sys_grid_dependency'
      AND COLUMN_NAME = 'custom_sql'
)
BEGIN
    ALTER TABLE sys_grid_dependency
    ADD dependency_type    NVARCHAR(50)  NULL,
        custom_sql         NVARCHAR(MAX) NULL,
        [comment]          NVARCHAR(100) NULL,
        created_at         DATETIME      NULL,
        updated_at         DATETIME      NULL,
        onCreate           BIT           NULL,
        onEdit             BIT           NULL,
        id_objectType      INT           NULL,
        id_object          INT           NULL;
END

----------------------------------------------------- Край 5.03.2025г------------------------------------------------------------------

------------------------------------------------- Паско - 09.03.2025г-------------------------------------------------------------
MERGE INTO [sys_translations] AS target
USING (
    -- Hardcoded source data
    SELECT * FROM (
        VALUES
        ('ui', 'selectProviderPolycomp', 'web', '1', 'bg', N'Импорт API Поликомп', GETDATE(), GETDATE(), 0, 0),
		('ui', 'selectProviderPolycomp', 'web', '1', 'en', N'Polycomp API Import', GETDATE(), GETDATE(), 0, 0),
		('ui', 'ChoosePolycompVendor', 'web', '1', 'bg', N'Избор на производител', GETDATE(), GETDATE(), 0, 1),
		('ui', 'ChoosePolycompVendor', 'web', '1', 'en', N'Select Vendor', GETDATE(), GETDATE(), 0, 1),
        ('ui', 'import-vendors_fetch_success', 'web', '1', 'bg', N'Успешно зареждане на производители!', GETDATE(), GETDATE(), 0, 1),
        ('ui', 'import-vendors_fetch_success', 'web', '1', 'en', N'Vendor list successfully loaded', GETDATE(), GETDATE(), 0, 1),
		('ui', 'import-groups_fetch_success', 'web', '1', 'bg', N'Успешно зареждане на групи!', GETDATE(), GETDATE(), 0, 1),
		('ui', 'import-groups_fetch_success', 'web', '1', 'en', N'Group list successfully loaded', GETDATE(), GETDATE(), 0, 1),
		('ui', 'ChoosePolycompVendorGroup', 'web', '1', 'bg', N'Избор на група (Празно за всички групи)', GETDATE(), GETDATE(), 0, 1),
		('ui', 'ChoosePolycompVendorGroup', 'web', '1', 'en', N'Select Group (Empty for ALL groups)', GETDATE(), GETDATE(), 0, 1),
		('ui', 'uploadPolycompXML', 'web', '1', 'bg', N'Импорт на данни от Поликомп', GETDATE(), GETDATE(), 0, 1),
		('ui', 'uploadPolycompXML', 'web', '1', 'en', N'Import Data from Polycomp', GETDATE(), GETDATE(), 0, 1),
		('ui', 'deletePolycomp', 'web', '1', 'bg', N'Изтриване на данни от Поликомп', GETDATE(), GETDATE(), 0, 1),
		('ui', 'deletePolycomp', 'web', '1', 'en', N'Delete Data from Polycomp', GETDATE(), GETDATE(), 0, 1),
		('ui', 'import-delete-success', 'web', '1', 'bg', N'Успешно изтриване на данните!', GETDATE(), GETDATE(), 0, 1),
		('ui', 'import-delete-success', 'web', '1', 'en', N'Data Deleted Succesfully!', GETDATE(), GETDATE(), 0, 1),
		('ui', 'import-delete-fail', 'web', '1', 'bg', N'Неуспешно изтриване на данните!', GETDATE(), GETDATE(), 0, 1),
		('ui', 'import-delete-fail', 'web', '1', 'en', N'Failed to Delete Data!', GETDATE(), GETDATE(), 0, 1)
		
    ) AS source ([filename], [label], [device], [status], [locale], [value], [created_at], [updated_at], [flag], [server_insert])
) AS source
ON target.[filename] = source.[filename] AND target.[label] = source.[label] AND target.[locale] = source.[locale] -- Match based on filename, label and language

WHEN MATCHED THEN 
    UPDATE SET 
        target.[device] = source.[device],
        target.[status] = source.[status],
        target.[locale] = source.[locale],
        target.[value] = source.[value],
        target.[updated_at] = source.[updated_at],
        target.[flag] = source.[flag],
        target.[server_insert] = source.[server_insert]

WHEN NOT MATCHED THEN 
    INSERT ([filename], [label], [device], [status], [locale], [value], [created_at], [updated_at], [flag], [server_insert])
    VALUES (source.[filename], source.[label], source.[device], source.[status], source.[locale], source.[value], source.[created_at], source.[updated_at], source.[flag], source.[server_insert]);
----------------------------------------------------- Край 09.03.2025г------------------------------------------------------------------

------------------------------------------------- Lora - 28.03.2025г-------------------------------------------------------------
MERGE INTO [sys_translations] AS target
USING (
    -- Hardcoded source data
    SELECT * FROM (
        VALUES
		-- object guidelines page
		('ui', 'object_guide', 'web', '1', 'bg', N'Информация за обект', GETDATE(), GETDATE(), 0, 1),
		('ui', 'object_guide', 'web', '1', 'en', N'Object guide', GETDATE(), GETDATE(), 0, 1),
		('ui', 'object_guidelines', 'web', '1', 'bg', N'Информация за обект', GETDATE(), GETDATE(), 0, 1),
		('ui', 'object_guidelines', 'web', '1', 'en', N'Object guidelines', GETDATE(), GETDATE(), 0, 1),
		('ui', 'add_object_guidelines', 'web', '1', 'bg', N'Добавяне на информация', GETDATE(), GETDATE(), 0, 1),
		('ui', 'add_object_guidelines', 'web', '1', 'en', N'Add object guidelines', GETDATE(), GETDATE(), 0, 1),
		('ui', 'id', 'web', '1', 'bg', N'ID', GETDATE(), GETDATE(), 0, 1),
		('ui', 'id', 'web', '1', 'en', N'ID', GETDATE(), GETDATE(), 0, 1),
		('ui', 'object_name', 'web', '1', 'bg', N'Име на обект', GETDATE(), GETDATE(), 0, 1),
		('ui', 'object_name', 'web', '1', 'en', N'Object name', GETDATE(), GETDATE(), 0, 1),
		('ui', 'object_group', 'web', '1', 'bg', N'Група на обект', GETDATE(), GETDATE(), 0, 1),
		('ui', 'object_group', 'web', '1', 'en', N'Object group', GETDATE(), GETDATE(), 0, 1),
		('ui', 'object_guidelines_missing', 'web', '1', 'bg', N'Няма добавена информация за обекта.', GETDATE(), GETDATE(), 0, 1),
		('ui', 'object_guidelines_missing', 'web', '1', 'en', N'Object guidelines are missing.', GETDATE(), GETDATE(), 0, 1),

		-- delete filters button
		('labels', 'delete-filters', 'web', '1', 'bg', N'Изчисти филтри', GETDATE(), GETDATE(), 0, 1),
		('labels', 'delete-filters', 'web', '1', 'en', N'Delete filters', GETDATE(), GETDATE(), 0, 1),

		-- 2fa settings
		('labels', '2fa-settings', 'web', '1', 'bg', N'Настройки на двуфакторна автентикация', GETDATE(), GETDATE(), 0, 1),
		('labels', '2fa-settings', 'web', '1', 'en', N'Two-factor authentication settings', GETDATE(), GETDATE(), 0, 1),
		('labels', '2fa-enabled', 'web', '1', 'bg', N'Активирано', GETDATE(), GETDATE(), 0, 1),
		('labels', '2fa-enabled', 'web', '1', 'en', N'Enabled', GETDATE(), GETDATE(), 0, 1),
		('labels', '2fa-frequency', 'web', '1', 'bg', N'Честота на автентикацията', GETDATE(), GETDATE(), 0, 1),
		('labels', '2fa-frequency', 'web', '1', 'en', N'Frequency of authentication', GETDATE(), GETDATE(), 0, 1),

		-- calendar settings
		('labels', 'user-calendar-settings', 'web', '1', 'bg', N'Настройки на календар', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-settings', 'web', '1', 'en', N'Calendar settings', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-color', 'web', '1', 'bg', N'Цвят на календара', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-color', 'web', '1', 'en', N'Calendar color', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-view', 'web', '1', 'bg', N'Изглед на календара', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-view', 'web', '1', 'en', N'Calendar view', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-month', 'web', '1', 'bg', N'Месец', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-month', 'web', '1', 'en', N'Month', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-week', 'web', '1', 'bg', N'Седмица', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-week', 'web', '1', 'en', N'Week', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-day', 'web', '1', 'bg', N'Ден', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-day', 'web', '1', 'en', N'Day', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-list', 'web', '1', 'bg', N'График', GETDATE(), GETDATE(), 0, 1),
		('labels', 'user-calendar-list', 'web', '1', 'en', N'List', GETDATE(), GETDATE(), 0, 1),

		-- user requests
		('ui', 'user_requests', 'web', '1', 'bg', N'Заявки за регистрация', GETDATE(), GETDATE(), 0, 1),
		('ui', 'user_requests', 'web', '1', 'en', N'Registration requests', GETDATE(), GETDATE(), 0, 1),
		('ui', 'verified', 'web', '1', 'bg', N'Одобрен', GETDATE(), GETDATE(), 0, 1),
		('ui', 'verified', 'web', '1', 'en', N'Verified', GETDATE(), GETDATE(), 0, 1),
		('ui', 'rejected', 'web', '1', 'bg', N'Отхвърлен', GETDATE(), GETDATE(), 0, 1),
		('ui', 'rejected', 'web', '1', 'en', N'Rejected', GETDATE(), GETDATE(), 0, 1),
		('ui', 'verify', 'web', '1', 'bg', N'Одобри', GETDATE(), GETDATE(), 0, 1),
		('ui', 'verify', 'web', '1', 'en', N'Verify', GETDATE(), GETDATE(), 0, 1),
		('ui', 'reject', 'web', '1', 'bg', N'Отхвърли', GETDATE(), GETDATE(), 0, 1),
		('ui', 'reject', 'web', '1', 'en', N'Reject', GETDATE(), GETDATE(), 0, 1)
		
    ) AS source ([filename], [label], [device], [status], [locale], [value], [created_at], [updated_at], [flag], [server_insert])
) AS source
ON target.[filename] = source.[filename] AND target.[label] = source.[label] AND target.[locale] = source.[locale] -- Match based on filename, label and language

WHEN MATCHED THEN 
    UPDATE SET 
        target.[device] = source.[device],
        target.[status] = source.[status],
        target.[locale] = source.[locale],
        target.[value] = source.[value],
        target.[updated_at] = source.[updated_at],
        target.[flag] = source.[flag],
        target.[server_insert] = source.[server_insert]

WHEN NOT MATCHED THEN 
    INSERT ([filename], [label], [device], [status], [locale], [value], [created_at], [updated_at], [flag], [server_insert])
    VALUES (source.[filename], source.[label], source.[device], source.[status], source.[locale], source.[value], source.[created_at], source.[updated_at], source.[flag], source.[server_insert]);
----------------------------------------------------- Край 28.03.2025г------------------------------------------------------------------

------------------------------------------------- Lora - 31.03.2025г-------------------------------------------------------------
IF
(
		NOT EXISTS (
			SELECT 1
			FROM INFORMATION_SCHEMA.COLUMNS c
			WHERE c.TABLE_NAME = 'sys_users'
				AND c.COLUMN_NAME = 'calendar_view'
			)
		)
BEGIN
	ALTER TABLE sys_users
	ADD calendar_view NVARCHAR(50) DEFAULT(NULL)
END
----------------------------------------------------- Край 31.03.2025г------------------------------------------------------------------

------------------------------------------------- Денислав - 31.03.2025г-------------------------------------------------------------

MERGE INTO [sys_translations] AS target
USING (
    SELECT * FROM (
        VALUES
		-- преводи за номенклатура
		('ui', 'permissionConfig', 'web', '1', 'bg', N'Конфигурация на правата по обекти', GETDATE(), GETDATE(), 0, 1),
		('ui', 'permissionConfig', 'web', '2', 'en', N'Permission configuration', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_saveNmcl', 'web', '1', 'bg', N'Запис', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_saveNmcl', 'web', '2', 'en', N'Save', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_editNmcl', 'web', '1', 'bg', N'Редакция', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_editNmcl', 'web', '2', 'en', N'Edit', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_hideNmcl', 'web', '1', 'bg', N'Скрий', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_hideNmcl', 'web', '2', 'en', N'Hide', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_deleteNmcl', 'web', '1', 'bg', N'Изтриване на ред', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_deleteNmcl', 'web', '2', 'en', N'Delete row', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_fieldsNmcl', 'web', '1', 'bg', N'Полета', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_fieldsNmcl', 'web', '2', 'en', N'Fields', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_addRowNmcl', 'web', '1', 'bg', N'Добавяне на ред', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_addRowNmcl', 'web', '2', 'en', N'Add row', GETDATE(), GETDATE(), 0, 1),
		-- Преводи за документ
		('ui', 'acl_saveDoc', 'web', '1', 'bg', N'Запис', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_saveDoc', 'web', '2', 'en', N'Save', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_editDoc', 'web', '1', 'bg', N'Редакция', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_editDoc', 'web', '2', 'en', N'Edit', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_hideDoc', 'web', '1', 'bg', N'Скрий', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_hideDoc', 'web', '2', 'en', N'Hide', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_anulDoc', 'web', '1', 'bg', N'Анулиране', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_anulDoc', 'web', '2', 'en', N'Cancellation', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_fieldsDoc', 'web', '1', 'bg', N'Полета', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_fieldsDoc', 'web', '2', 'en', N'Fields', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_inheritDoc', 'web', '1', 'bg', N'Наследвяване', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_inheritDoc', 'web', '2', 'en', N'Inheritance', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_printDoc', 'web', '1', 'bg', N'Печат', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_printDoc', 'web', '2', 'en', N'Print', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_searchDoc', 'web', '1', 'bg', N'Търсене', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_searchDoc', 'web', '2', 'en', N'DocSearch', GETDATE(), GETDATE(), 0, 1),
		-- Преводи за справки
		('ui', 'acl_saveRep', 'web', '1', 'bg', N'Скрий', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_saveRep', 'web', '2', 'en', N'Hide', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_filtersRep', 'web', '1', 'bg', N'Филтри', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_filtersRep', 'web', '2', 'en', N'Filters', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_columnsRep', 'web', '1', 'bg', N'Колони', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_columnsRep', 'web', '2', 'en', N'Columns', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_exportRep', 'web', '1', 'bg', N'Експорт', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_exportRep', 'web', '2', 'en', N'Export', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_viewsRep', 'web', '1', 'bg', N'Изгледи', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_viewsRep', 'web', '2', 'en', N'Views', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_createDocRep', 'web', '1', 'bg', N'Създаване на док', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_createDocRep', 'web', '2', 'en', N'Create document', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_printRep', 'web', '1', 'bg', N'Печат', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_printRep', 'web', '2', 'en', N'Print', GETDATE(), GETDATE(), 0, 1),
		-- Преводи за системни права
		('ui', 'acl_navigation', 'web', '1', 'bg', N'Навигация', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_navigation', 'web', '2', 'en', N'Navigation', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_systemAcl', 'web', '1', 'bg', N'Системни права', GETDATE(), GETDATE(), 0, 1),
		('ui', 'acl_systemAcl', 'web', '2', 'en', N'System permissions', GETDATE(), GETDATE(), 0, 1),

		---------- other labels (Lora)
		('ui', 'close_nmcl_msg', 'web', '1', 'bg', N'Сигурни ли сте, че искате да затворите номенклатурата?', GETDATE(), GETDATE(), 0, 1),
		('ui', 'close_nmcl_msg', 'web', '2', 'en', N'Are you sure you want to close this nomenclature?', GETDATE(), GETDATE(), 0, 1)
		
    ) AS source ([filename], [label], [device], [status], [locale], [value], [created_at], [updated_at], [flag], [server_insert])
) AS source
ON target.[filename] = source.[filename] AND target.[label] = source.[label] AND target.[locale] = source.[locale] -- Match based on filename, label and language

WHEN MATCHED THEN 
    UPDATE SET 
        target.[device] = source.[device],
        target.[status] = source.[status],
        target.[locale] = source.[locale],
        target.[value] = source.[value],
        target.[updated_at] = source.[updated_at],
        target.[flag] = source.[flag],
        target.[server_insert] = source.[server_insert]

WHEN NOT MATCHED THEN 
    INSERT ([filename], [label], [device], [status], [locale], [value], [created_at], [updated_at], [flag], [server_insert])
    VALUES (source.[filename], source.[label], source.[device], source.[status], source.[locale], source.[value], source.[created_at], source.[updated_at], source.[flag], source.[server_insert]);

----------------------------------------------------- Край 31.03.2025г---------------------------------------------------------------

------------------------------------------------- Денислав - 31.03.2025г-------------------------------------------------------------
-- Заявка за alter на стейт таблиците, да позволява NULL като стойност в id_record колоната
declare @sqltext varchar(max)
 
select 
@sqltext = isnull(@sqltext,'')+char(13)+'alter table '+t.name+' alter column id_record ' + y.name +' Null' 
from 
sys.columns c 
join sys.tables t on t.object_id=c.object_id
join sys.types y on y.user_type_id=c.user_type_id
where c.name='id_record'
 
exec (@sqltext)
------------------------------------------------- Край - 31.03.2025г-------------------------------------------------------------
------------------------------------------------- Денислав - 10.04.2025г-------------------------------------------------------------

DECLARE @sqltext_1 VARCHAR(MAX);

SELECT
  @sqltext_1 = ISNULL(@sqltext, '') + CHAR(13) 
             + 'ALTER TABLE ' + QUOTENAME(t.name) + ' ADD sys_updated_at DATETIME NULL'
FROM sys.tables t
LEFT JOIN sys.columns c
  ON t.object_id = c.object_id AND c.name = 'sys_updated_at'
WHERE t.name LIKE 'state_%'
  AND c.name IS NULL;

EXEC(@sqltext_1);



------------------------------------------------- Край - 10.04.2025г-------------------------------------------------------------

------------------------------------------------- Lora - 10.04.2025г-------------------------------------------------------------
MERGE INTO [sys_translations] AS target
USING (
    SELECT * FROM (
        VALUES
		-- calendar task offcanvas
		('ui', 'task_preview', 'web', '1', 'bg', N'Преглед на задача', GETDATE(), GETDATE(), 0, 1),
		('ui', 'task_preview', 'web', '2', 'en', N'Task preview', GETDATE(), GETDATE(), 0, 1),
		('ui', 'task_edit', 'web', '1', 'bg', N'Редактиране на задача', GETDATE(), GETDATE(), 0, 1),
		('ui', 'task_edit', 'web', '2', 'en', N'Edit task', GETDATE(), GETDATE(), 0, 1),
		-- users hierarchy
		('ui', 'users_hierarchy', 'web', '1', 'bg', N'Йерархия на потребители', GETDATE(), GETDATE(), 0, 1),
		('ui', 'users_hierarchy', 'web', '2', 'en', N'Users hierarchy', GETDATE(), GETDATE(), 0, 1),
		('ui', 'add_hierarchy_component', 'web', '1', 'bg', N'Добави настройка за йерархия', GETDATE(), GETDATE(), 0, 1),
		('ui', 'add_hierarchy_component', 'web', '2', 'en', N'Add hierarchy settings', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_components', 'web', '1', 'bg', N'Списък добавени настройки за йерархия', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_components', 'web', '2', 'en', N'List of added settings', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_deletion_success', 'web', '1', 'bg', N'Успешно изтриване на настройка', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_deletion_success', 'web', '2', 'en', N'Successfully deleted settings', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_employee', 'web', '1', 'bg', N'Служител', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_employee', 'web', '2', 'en', N'Employee', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_manager', 'web', '1', 'bg', N'Ръководител', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_manager', 'web', '2', 'en', N'Manager', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_placeholder_search', 'web', '1', 'bg', N'Въведете потребител...', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_placeholder_search', 'web', '2', 'en', N'Search by user...', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_search', 'web', '1', 'bg', N'Търсене по:', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_search', 'web', '2', 'en', N'Search by:', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_store_success', 'web', '1', 'bg', N'Успешно запазени настройки', GETDATE(), GETDATE(), 0, 1),
		('ui', 'hierarchy_store_success', 'web', '2', 'en', N'Successfully saved settings', GETDATE(), GETDATE(), 0, 1)
    ) AS source ([filename], [label], [device], [status], [locale], [value], [created_at], [updated_at], [flag], [server_insert])
) AS source
ON target.[filename] = source.[filename] AND target.[label] = source.[label] AND target.[locale] = source.[locale] -- Match based on filename, label and language

WHEN MATCHED THEN 
    UPDATE SET 
        target.[device] = source.[device],
        target.[status] = source.[status],
        target.[locale] = source.[locale],
        target.[value] = source.[value],
        target.[updated_at] = source.[updated_at],
        target.[flag] = source.[flag],
        target.[server_insert] = source.[server_insert]

WHEN NOT MATCHED THEN 
    INSERT ([filename], [label], [device], [status], [locale], [value], [created_at], [updated_at], [flag], [server_insert])
    VALUES (source.[filename], source.[label], source.[device], source.[status], source.[locale], source.[value], source.[created_at], source.[updated_at], source.[flag], source.[server_insert]);
------------------------------------------------- Край - 10.04.2025г-------------------------------------------------------------

------------------------------------------------- Lora - 29.04.2025г-------------------------------------------------------------
MERGE INTO [sys_translations] AS target
USING (
    SELECT * FROM (
        VALUES
		-- calendar task offcanvas
		('labels', 'filter_by', 'web', '1', 'bg', N'Филтриране по:', GETDATE(), GETDATE(), 0, 1),
		('labels', 'filter_by', 'web', '2', 'en', N'Filter by:', GETDATE(), GETDATE(), 0, 1),
		('labels', 'select_all', 'web', '1', 'bg', N'Всички', GETDATE(), GETDATE(), 0, 1),
		('labels', 'select_all', 'web', '2', 'en', N'Select all', GETDATE(), GETDATE(), 0, 1),
		('labels', 'users', 'web', '1', 'bg', N'Потребители', GETDATE(), GETDATE(), 0, 1),
		('labels', 'users', 'web', '2', 'en', N'Users', GETDATE(), GETDATE(), 0, 1),
		('labels', 'tasktype', 'web', '1', 'bg', N'Вид задача', GETDATE(), GETDATE(), 0, 1),
		('labels', 'tasktype', 'web', '2', 'en', N'Task type', GETDATE(), GETDATE(), 0, 1),
		('labels', 'taskstatus', 'web', '1', 'bg', N'Статус на задача', GETDATE(), GETDATE(), 0, 1),
		('labels', 'taskstatus', 'web', '2', 'en', N'Task status', GETDATE(), GETDATE(), 0, 1),
		('labels', 'clear_filters', 'web', '1', 'bg', N'Изчистване на филтри', GETDATE(), GETDATE(), 0, 1),
		('labels', 'clear_filters', 'web', '2', 'en', N'Clear filters', GETDATE(), GETDATE(), 0, 1),
		('labels', 'filter_calendar', 'web', '1', 'bg', N'Прилагане на филтри', GETDATE(), GETDATE(), 0, 1),
		('labels', 'filter_calendar', 'web', '2', 'en', N'Apply filters', GETDATE(), GETDATE(), 0, 1)
    ) AS source ([filename], [label], [device], [status], [locale], [value], [created_at], [updated_at], [flag], [server_insert])
) AS source
ON target.[filename] = source.[filename] AND target.[label] = source.[label] AND target.[locale] = source.[locale] -- Match based on filename, label and language

WHEN MATCHED THEN 
    UPDATE SET 
        target.[device] = source.[device],
        target.[status] = source.[status],
        target.[locale] = source.[locale],
        target.[value] = source.[value],
        target.[updated_at] = source.[updated_at],
        target.[flag] = source.[flag],
        target.[server_insert] = source.[server_insert]

WHEN NOT MATCHED THEN 
    INSERT ([filename], [label], [device], [status], [locale], [value], [created_at], [updated_at], [flag], [server_insert])
    VALUES (source.[filename], source.[label], source.[device], source.[status], source.[locale], source.[value], source.[created_at], source.[updated_at], source.[flag], source.[server_insert]);
------------------------------------------------- Край - 29.04.2025г-------------------------------------------------------------
