=============================================================================================================================
CREATE UNIQUE INDEX unique_item_uom ON item_uom(item_id, uom_id);

=============================================================================================================================

CREATE OR REPLACE PROCEDURE public.update_and_insert_stock_take(
    IN json_input jsonb,
    INOUT result jsonb)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock_take_id INTEGER;  
    v_company_id INTEGER;      
    v_created_by INTEGER;      
    v_highest_version INTEGER;  
BEGIN
    -- Extract the input values from the JSON
    v_stock_take_id := (json_input ->> 'stock_take_id')::INTEGER;
    v_company_id := (json_input ->> 'company_id')::INTEGER;
    v_created_by := (json_input ->> 'created_by')::INTEGER;

    RAISE NOTICE 'Received stock_take_id: %, company_id: %, created_by: %', v_stock_take_id, v_company_id, v_created_by;

    -- Initialize the result
    result := jsonb_build_object('status', 'success', 'details', '[]');

    -- Fetch the highest version number for the given stock_take_id
    SELECT MAX(version_number) INTO v_highest_version
    FROM stock_audit_entry_header
    WHERE stock_take_id = v_stock_take_id;

    RAISE NOTICE 'Highest version number found: %', v_highest_version;

    IF v_highest_version IS NOT NULL THEN
        -- Update the record with the highest version number
        UPDATE stock_audit_entry_header
        SET status_id = 2,
            updated_at = CURRENT_TIMESTAMP,
            updated_by = v_created_by
        WHERE stock_take_id = v_stock_take_id
        AND version_number = v_highest_version;

        RAISE NOTICE 'Record updated for stock_take_id: %', v_stock_take_id;

        -- Insert new record with incremented version number
        INSERT INTO stock_audit_entry_header (stock_take_id, version_number, status_id, company_id, created_by)
        VALUES (
            v_stock_take_id,
            v_highest_version + 1,
            1, 
            v_company_id,
            v_created_by
        );

        RAISE NOTICE 'New entry inserted for stock_take_id: %', v_stock_take_id;

        -- Add result details for the operation
        result := jsonb_set(
            result,
            '{details}',
            (result->'details') || jsonb_build_object('stock_take_id', v_stock_take_id, 'status', 'processed')
        );

    ELSE
        result := jsonb_build_object('status', 'failure', 'error_message', 'stock_take_id not found');
    END IF;

    RAISE NOTICE 'Result: %', result;
    RETURN;
END;
$$;
====================================================================================================================

CREATE UNIQUE INDEX unique_stock_audit_entry_line_items_companyid_and_uuid ON stock_audit_entry_line_items(company_id, uuid);

====================================================================================================================

CREATE UNIQUE INDEX unique_onhand_stocks_stocktakeid_and_itemid_and_uomid ON onhand_stocks(stock_take_id, item_id, uom_id);

====================================================================================================================

CREATE OR REPLACE PROCEDURE consolidate_stock_take(
    IN json_input JSONB,
    INOUT result JSONB
)
LANGUAGE plpgsql
AS $$
DECLARE
    stock_record JSONB;
    rec_company_id INTEGER;
    rec_item_id INTEGER;
    rec_uom_id INTEGER;
    rec_item_status_id INTEGER;
    rec_onhand_quantity INTEGER;
    rec_identified_quantity INTEGER;
    rec_is_audit_completed BOOLEAN;
    rec_version_number INTEGER;
    input_type TEXT;
    input_stock_take_id INTEGER;
    input_user_id INTEGER;
    input_stock_take_status_id INTEGER;
    input_company_id INTEGER;
BEGIN
    -- Initialize result if it is null
    IF result IS NULL THEN
        result := jsonb_build_object('status', 'success', 'details', '[]');
    END IF;

    -- Extract the "type" and other input values from the input JSON
    input_type := json_input ->> 'type';
    input_stock_take_id := (json_input ->> 'stock_take_id')::INTEGER;
    input_user_id := (json_input ->> 'user_id')::INTEGER;
    input_company_id := (json_input ->> 'company_id')::INTEGER;
    input_stock_take_status_id := (json_input ->> 'stock_take_status_id')::INTEGER;
    
    -- Update stock_take status if the type is "stock_audit_close"
    IF input_type = 'stock_audit_close' THEN
        UPDATE stock_take
        SET stock_take_status_id = input_stock_take_status_id,
            updated_by = input_user_id,
            updated_at = CURRENT_TIMESTAMP
        WHERE id = input_stock_take_id AND company_id = input_company_id;
    END IF;

    -- Process each record in the "records" array
    FOR stock_record IN SELECT * FROM jsonb_array_elements(json_input -> 'records')
    LOOP
        -- Extract values from each JSON object
        rec_company_id := (stock_record ->> 'company_id')::INTEGER;
        rec_item_id := (stock_record ->> 'item_id')::INTEGER;
        rec_uom_id := (stock_record ->> 'uom_id')::INTEGER;
        rec_item_status_id := (stock_record ->> 'item_status_id')::INTEGER;
        rec_onhand_quantity := (stock_record ->> 'onhand_quantity')::INTEGER;
        rec_identified_quantity := (stock_record ->> 'identified_quantity')::INTEGER;
        rec_is_audit_completed := (stock_record ->> 'is_audit_completed')::BOOLEAN;
        rec_version_number := (stock_record ->> 'version_number')::INTEGER;

        -- Insert or update based on conflict
        INSERT INTO onhand_stocks (
            company_id, stock_take_id, item_id, uom_id, item_status_id, 
            onhand_quantity, identified_quantity, is_audit_completed, version_number, created_by
        )
        VALUES (
            rec_company_id, input_stock_take_id, rec_item_id, rec_uom_id, rec_item_status_id,
            rec_onhand_quantity, rec_identified_quantity, rec_is_audit_completed, rec_version_number, input_user_id
        )
        ON CONFLICT (company_id, stock_take_id, item_id, uom_id, item_status_id)
        DO UPDATE SET
            identified_quantity = EXCLUDED.identified_quantity,
            version_number = EXCLUDED.version_number,
            is_audit_completed = EXCLUDED.is_audit_completed
        WHERE onhand_stocks.is_audit_completed = false;

        -- Append details to the result
        result := jsonb_set(
            result,
            '{details}',
            (result->'details') || jsonb_build_object('company_id', rec_company_id, 'item_id', rec_item_id, 'status', 'processed')
        );
    END LOOP;

    -- Completion message
    RAISE NOTICE 'Procedure executed successfully.';

EXCEPTION
    -- Handle errors and update the result with error information
    WHEN OTHERS THEN
        result := jsonb_build_object('status', 'failure', 'error_message', SQLERRM);
        RAISE NOTICE 'An error occurred: %', SQLERRM;
END;
$$;

==========================================================================================================================

CREATE OR REPLACE PROCEDURE update_and_insert_stock_take(
    IN stock_take_data JSON
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_stock_take_id INTEGER;
    v_company_id INTEGER;
    v_created_by INTEGER;    
    highest_version INTEGER;
    result JSONB;  -- Local variable to hold the result
BEGIN
    -- Extract stock_take_id, company_id, and created_by from the JSON input
    v_stock_take_id := (stock_take_data ->> 'stock_take_id')::INTEGER;
    v_company_id := (stock_take_data ->> 'company_id')::INTEGER;
    v_created_by := (stock_take_data ->> 'created_by')::INTEGER;

    -- Initialize the result as an empty JSON object
    result := '{}'::JSONB;

    -- Step 1: Find the highest version_number for the given stock_take_id
    SELECT MAX(version_number) INTO highest_version
    FROM stock_audit_entry_header
    WHERE stock_take_id = v_stock_take_id;

    -- Step 2: Check if stock_take_id exists and if the highest version is found
    IF highest_version IS NOT NULL THEN
        -- Step 3: Update the status of the highest version to 2
        UPDATE stock_audit_entry_header
        SET status_id = 2
        WHERE stock_take_id = v_stock_take_id
        AND version_number = highest_version;

        -- Step 4: Insert a new entry with version_number = highest_version + 1, status = 1
        INSERT INTO stock_audit_entry_header (
            stock_take_id, 
            version_number, 
            status_id, 
            company_id, 
            created_by 
        )
        VALUES (
            v_stock_take_id,
            highest_version + 1,
            1,                -- New status
            v_company_id,
            v_created_by 
        );

        -- Prepare the output JSON with the new entry details
        result := jsonb_build_object(
            'stock_take_id', v_stock_take_id,
            'new_version', highest_version + 1,
            'status', 1
        );
    ELSE
        RAISE NOTICE 'stock_take_id % not found in stock_audit_entry_header', v_stock_take_id;
        result := jsonb_build_object('error', 'stock_take_id not found');
    END IF;

    -- Output result as a notice
    RAISE NOTICE 'Result: %', result::TEXT;
END;
$$;

==========================================================================================================================

-- ALTER TABLE onhand_stocks
-- ADD CONSTRAINT unique_onhand_stock UNIQUE (company_id, stock_take_id, item_id, uom_id, item_status_id);

========================================================================================================

================== VIEW QUERY =========================================================================

CREATE VIEW max_version_stock_audit as  WITH cte AS (
         SELECT a.stock_take_id,
            a.version_number,
            b.item_id,
            b.uom_id,
            sum(b.quantity) AS totalquantity,
            rank() OVER (PARTITION BY a.stock_take_id, b.item_id, b.uom_id ORDER BY a.version_number DESC) AS rnk
           FROM stock_audit_entry_header a
             JOIN stock_audit_entry_line_items b ON b.stock_audit_entry_id = a.id
          GROUP BY a.stock_take_id, a.version_number, b.item_id, b.uom_id
        )
 SELECT stock_take_id,
    version_number,
    item_id,
    uom_id,
    totalquantity
   FROM cte
  WHERE rnk = 1;


========================================================================================================