-------------------------------------------------Денислав - 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г------------------------------------------------------------------