CREATE OR ALTER FUNCTION dbo.get_sequence_no
(
    @module_name_arg NVARCHAR(255),
    @check_current_financial_year BIT = 1
)
RETURNS NVARCHAR(255)
AS
BEGIN
    DECLARE @var_company_id INT;
    DECLARE @formatted_sequence NVARCHAR(255);
    DECLARE @new_sequence NVARCHAR(255);
    DECLARE @current_sequence_no INT;
    DECLARE @end_sequence_no INT;
    DECLARE @sequence_length INT;
    DECLARE @code_format NVARCHAR(255);

    -- Get the current company_id from the session
    SET @var_company_id = TRY_CAST(SESSION_CONTEXT(N'app.lcp_company_id') AS INT);

    -- Get the financial year if @check_current_financial_year is true
    IF @check_current_financial_year = 1
    BEGIN
        IF NOT EXISTS (
            SELECT 1
            FROM financial_years
            WHERE company_id = @var_company_id
            AND status_id = 1
            AND GETDATE() BETWEEN start_date AND end_date
        )
        BEGIN
            RETURN NULL; -- No active financial year found
        END;
    END;

    -- Fetch the current sequence
    SELECT TOP 1
        @current_sequence_no = current_sequence_no,
        @end_sequence_no = end_sequence_no,
        @sequence_length = sequence_length,
        @code_format = code_format
    FROM document_sequences
    WHERE module_name = @module_name_arg
    ORDER BY id DESC;

    -- If no record is found for the module name, return NULL
    IF @current_sequence_no IS NULL
    BEGIN
        RETURN NULL;
    END;

    -- Check if the sequence end has been reached
    IF @end_sequence_no != 0 AND @current_sequence_no = @end_sequence_no
    BEGIN
        RETURN NULL; -- Sequence end has reached
    END;

    -- Generate the new sequence
    SET @new_sequence = RIGHT('0000000000' + CAST((@current_sequence_no + 1) AS NVARCHAR(255)), @sequence_length);

    -- Replace placeholders dynamically in code_format
    SET @formatted_sequence = REPLACE(@code_format, '{{current_sequence}}', @new_sequence);

    -- Return the formatted sequence
    RETURN @formatted_sequence;
END;

-- It is readonly so update the current_sequence_no + 1 manually
