CREATE OR REPLACE FUNCTION public.get_sequence_no(
    module_name_arg text,
    update_and_get boolean DEFAULT false,
    check_current_financial_year boolean DEFAULT true
)
RETURNS text
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    seq_record RECORD;
    financial_year_record RECORD;
    formatted_sequence TEXT;
    new_sequence TEXT;
    var_company_id INT;
BEGIN
    -- Get the current company_id from the session (or handle it based on your logic)
    var_company_id := NULLIF(get_current_data('app.lcp_company_id'), '')::INT;

    -- Get the financial year if check_current_financial_year is true
    IF check_current_financial_year THEN
        SELECT * INTO financial_year_record
        FROM financial_years
        WHERE company_id = var_company_id 
        AND status_id = 1
        AND CURRENT_DATE BETWEEN start_date AND end_date;

        -- If no financial year is found or current date is not within the range, raise an exception
        IF NOT FOUND THEN
            RAISE EXCEPTION 'No active financial year found or current date is outside the range';
        END IF;
    END IF;

    -- Update the sequence if update_and_get is true, otherwise fetch the current sequence
    IF update_and_get THEN
        IF check_current_financial_year THEN
            UPDATE document_sequences 
            SET current_sequence_no = current_sequence_no + 1
            WHERE module_name = module_name_arg 
            AND financial_year_id = financial_year_record.id
            RETURNING * INTO seq_record;
        ELSE
            UPDATE document_sequences 
            SET current_sequence_no = current_sequence_no + 1
            WHERE module_name = module_name_arg
            RETURNING * INTO seq_record;
        END IF;
    ELSE
        IF check_current_financial_year THEN
            SELECT * INTO seq_record
            FROM document_sequences
            WHERE module_name = module_name_arg 
            AND financial_year_id = financial_year_record.id
            ORDER BY id DESC LIMIT 1;
        ELSE
            SELECT * INTO seq_record
            FROM document_sequences
            WHERE module_name = module_name_arg
            ORDER BY id DESC LIMIT 1;
        END IF;
    END IF;

    -- If no record is found for the module name, raise an exception
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Module name not found: %', module_name_arg;
    END IF;

    -- Get the current_sequence padded with leading zeros
    IF update_and_get THEN
        IF seq_record.end_sequence_no != 0 AND (seq_record.current_sequence_no - 1) = seq_record.end_sequence_no THEN
            RAISE EXCEPTION 'Sequence end has reached: %', seq_record.end_sequence_no;
        END IF;
        new_sequence := lpad(seq_record.current_sequence_no::text, seq_record.sequence_length, '0');
    ELSE
        IF seq_record.end_sequence_no != 0 AND seq_record.current_sequence_no = seq_record.end_sequence_no THEN
            RAISE EXCEPTION 'Sequence end has reached: %', seq_record.end_sequence_no;
        END IF;
        new_sequence := lpad((seq_record.current_sequence_no + 1)::text, seq_record.sequence_length, '0');
    END IF;

    -- Replace placeholders dynamically in code_format
    formatted_sequence := seq_record.code_format;
    formatted_sequence := replace(formatted_sequence, '{{current_sequence}}', new_sequence);

    -- Return the formatted sequence
    RETURN formatted_sequence;
END;
$BODY$;

ALTER FUNCTION public.get_sequence_no(text, boolean, boolean)
    OWNER TO postgres;