--============== **************** Functions ****************** =======================

--======****** 1. Title: Function to Retrieve the Current Setting Value by Key ***** ========

CREATE FUNCTION dbo.get_current_data (@data_key NVARCHAR(255))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @value NVARCHAR(MAX);

    -- Retrieve the value from the session context
    SELECT @value = CONVERT(NVARCHAR(MAX), SESSION_CONTEXT(@data_key));

    -- Return the value (or NULL if the key does not exist)
    RETURN @value;
END;

EXEC sp_set_session_context @key = N'app.lcp_user_id', @value = 123;
EXEC sp_set_session_context @key = N'app.lcp_company_id', @value = 456;

SELECT dbo.get_current_data(N'app.lcp_user_id') AS lcp_user_id,
       dbo.get_current_data(N'app.lcp_company_id') AS lcp_company_id;

--=======================================================================================================

--======****** 2. Title: Audit Trigger Function for Tracking Changes in Database Tables ***** ========


CREATE OR ALTER PROCEDURE audit_trigger
    @operation_type NVARCHAR(10),
    @table_name NVARCHAR(128),
    @old_data NVARCHAR(MAX) = NULL,
    @new_data NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @user_id INT;
    DECLARE @company_id INT;
    DECLARE @entity_id INT;
    DECLARE @field_name NVARCHAR(128);
    DECLARE @old_value NVARCHAR(MAX);
    DECLARE @new_value NVARCHAR(MAX);
    DECLARE @sql NVARCHAR(MAX);

    -- Fetch user ID and company ID from session context
    SET @user_id = TRY_CAST(dbo.get_current_data('app.lcp_user_id') AS INT);
    SET @company_id = TRY_CAST(dbo.get_current_data('app.lcp_company_id') AS INT);

    -- Get the entity ID (primary key) dynamically
    SET @entity_id = JSON_VALUE(@old_data, '$[0].id'); -- Extract ID from JSON if available

    -- Ensure @entity_id is assigned
    IF @entity_id IS NULL
        SET @entity_id = JSON_VALUE(@new_data, '$[0].id');

    -- Loop through each column and log changes field by field
    DECLARE column_cursor CURSOR FOR
    SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(@table_name);

    OPEN column_cursor;
    FETCH NEXT FROM column_cursor INTO @field_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Extract old and new values dynamically for each field
        SET @old_value = JSON_VALUE(@old_data, '$[0].' + @field_name);
        SET @new_value = JSON_VALUE(@new_data, '$[0].' + @field_name);

        -- Only log the changes where values are different
        IF @old_value IS DISTINCT FROM @new_value
        BEGIN
            INSERT INTO audit_logs (table_name, entity_id, field_name, old_value, new_value, created_at, user_id, operation_type, company_id)
            VALUES (@table_name, @entity_id, @field_name, @old_value, @new_value, GETDATE(), @user_id, @operation_type, @company_id);
        END

        FETCH NEXT FROM column_cursor INTO @field_name;
    END

    CLOSE column_cursor;
    DEALLOCATE column_cursor;
END;

--=======================================================================================================

---============== *********** Anonymus Block **************** ===================================

--================ ******* 1. Title: Audit Trigger Management for Multiple Tables ******** ===============

--============DELETE TRIGGERS====================
-- Ensure the cursor does not exist before declaring it
IF CURSOR_STATUS('global', 'trigger_cursor') >= -1
BEGIN
    CLOSE trigger_cursor;
    DEALLOCATE trigger_cursor;
END

DECLARE @trigger_name NVARCHAR(128) = 'trigger_audit_log';
DECLARE @table_name NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);

-- Declare a cursor to loop through all tables containing the trigger
DECLARE trigger_cursor CURSOR FOR
SELECT t.name
FROM sys.triggers tr
JOIN sys.tables t ON tr.parent_id = t.object_id
WHERE tr.name = @trigger_name + '_' + t.name;

OPEN trigger_cursor;
FETCH NEXT FROM trigger_cursor INTO @table_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct and execute the dynamic SQL to drop the trigger
    SET @sql = 'DROP TRIGGER IF EXISTS ' + QUOTENAME(@trigger_name + '_' + @table_name) + ';';
	print @sql;
    EXEC sp_executesql @sql;

    FETCH NEXT FROM trigger_cursor INTO @table_name;
END

-- Clean up the cursor properly
CLOSE trigger_cursor;
DEALLOCATE trigger_cursor;

--====================CREATE SINGLE TRIGGER=============================
CREATE OR ALTER TRIGGER trigger_audit_log_roles
ON roles
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @operation NVARCHAR(10);
    DECLARE @old_data NVARCHAR(MAX) = NULL;
    DECLARE @new_data NVARCHAR(MAX) = NULL;

    IF EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)
    BEGIN
        SET @operation = 'UPDATE';
        SET @old_data = (SELECT * FROM DELETED FOR JSON PATH);
        SET @new_data = (SELECT * FROM INSERTED FOR JSON PATH);
    END
    ELSE IF EXISTS (SELECT * FROM INSERTED)
    BEGIN
        SET @operation = 'INSERT';
        SET @new_data = (SELECT * FROM INSERTED FOR JSON PATH);
    END
    ELSE IF EXISTS (SELECT * FROM DELETED)
    BEGIN
        SET @operation = 'DELETE';
        SET @old_data = (SELECT * FROM DELETED FOR JSON PATH);
    END

    -- Call the stored procedure to log changes
    EXEC dbo.audit_trigger @operation, 'roles', @old_data, @new_data;
END;

--====================CREATE TRIGGERS===================================
CREATE OR ALTER PROCEDURE create_auditlog_trigger
    @table_name NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate the table name to prevent SQL injection
    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name)
    BEGIN
        RAISERROR('Table "%s" does not exist.', 16, 1, @table_name);
        RETURN;
    END

    -- Generate the trigger name
    DECLARE @trigger_name NVARCHAR(256) = 'trigger_audit_log_' + @table_name;

    -- Generate the dynamic SQL for the trigger
    DECLARE @sql NVARCHAR(MAX) = '
CREATE OR ALTER TRIGGER ' + QUOTENAME(@trigger_name) + '
ON ' + QUOTENAME(@table_name) + '
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @operation NVARCHAR(10);
    DECLARE @old_data NVARCHAR(MAX) = NULL;
    DECLARE @new_data NVARCHAR(MAX) = NULL;

    IF EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED)
    BEGIN
        SET @operation = ''UPDATE'';
        SET @old_data = (SELECT * FROM DELETED FOR JSON PATH);
        SET @new_data = (SELECT * FROM INSERTED FOR JSON PATH);
    END
    ELSE IF EXISTS (SELECT * FROM INSERTED)
    BEGIN
        SET @operation = ''INSERT'';
        SET @new_data = (SELECT * FROM INSERTED FOR JSON PATH);
    END
    ELSE IF EXISTS (SELECT * FROM DELETED)
    BEGIN
        SET @operation = ''DELETE'';
        SET @old_data = (SELECT * FROM DELETED FOR JSON PATH);
    END

    -- Call the stored procedure to log changes
    EXEC dbo.audit_trigger @operation, ''' + @table_name + ''', @old_data, @new_data;
END;';

    -- Print the generated SQL for debugging
    PRINT @sql;

    -- Execute the dynamic SQL to create the trigger
    EXEC sp_executesql @sql;

    -- PRINT 'Trigger "' + @trigger_name + '" created successfully for table "' + @table_name + '".';
END;


-- EXEC create_auditlog_trigger 'roles';
--=======================================================================================

DECLARE @table_name NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);

-- List of tables for triggers
DECLARE @tables TABLE (table_name NVARCHAR(128));
INSERT INTO @tables (table_name)
VALUES ('users'), ('user_details'), ('designations'), ('departments'), ('permissions'), ('roles'), ('role_permissions'), ('user_roles'), ('user_permissions'), ('language_contents'), ('menu'), ('menu_items'), ('master_entities'), ('master_entity_line_items'), ('email_templates'), ('email_template_assignments');

-- Loop through each table in the list
DECLARE table_cursor CURSOR FOR
SELECT table_name FROM @tables;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @table_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Generate and execute the CREATE TRIGGER statement for each table
    EXEC create_auditlog_trigger @table_name;

    FETCH NEXT FROM table_cursor INTO @table_name;
END

CLOSE table_cursor;
DEALLOCATE table_cursor;


-- Note: If trigger is enanled then OUTPUT INSERTED.* & OUTPUT DELETED.* will not work.
