-- Table: customer_products
Exists in source, missing in target

-- Table: product_group
Exists in source, missing in target

-- Table: orders
Exists in source, missing in target

-- Table: roles
Exists in source, missing in target

-- Table: product_tax_category_details
Exists in source, missing in target

-- Table: schema_versions
Exists in source, missing in target

-- Table: products
Exists in source, missing in target

-- Table: companies
Exists in source, missing in target

-- Table: company_categories
Exists in source, missing in target

-- Table: order_details
Exists in source, missing in target

-- Table: units
Exists in source, missing in target

-- Table: organizations
Exists in source, missing in target

-- Table: product_tax_categories
Exists in source, missing in target

-- Table: product_warehouse_quantities
Exists in source, missing in target

-- Table: company_products
Exists in source, missing in target

-- Table: gate_pass_logs
Exists in source, missing in target

-- Table: __EFMigrationsHistory
Exists in source, missing in target

-- Table: users
Exists in source, missing in target

-- Table: categories
Exists in source, missing in target

-- Table: default_apartment_products
Exists in source, missing in target

-- Table: vendor_products
Exists in source, missing in target

-- Table: stock_adjustment_headers
Exists in source, missing in target

-- Table: stock_adjustment_details
Exists in source, missing in target

-- Table: stock_adjustment_statuses
Exists in source, missing in target

-- Table: stock_ledgers
Exists in source, missing in target

-- Table: stock_movement_types
Exists in source, missing in target

-- Table: stock_reservation_statuses
Exists in source, missing in target

-- Table: stock_reservations
Exists in source, missing in target

-- Table: stock_transfer_details
Exists in source, missing in target

-- Table: stock_transfer_headers
Exists in source, missing in target

-- Table: stock_transfer_statuses
Exists in source, missing in target

-- Function: grant_full_schema_access
CREATE OR REPLACE FUNCTION public.grant_full_schema_access(p_schema text, p_user text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    obj RECORD;
BEGIN
    -- Grant on tables
    FOR obj IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = p_schema
          AND table_type = 'BASE TABLE'
    LOOP
        EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE %I.%I TO %I;', p_schema, obj.table_name, p_user);
        RAISE NOTICE 'GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE %.% TO %;', p_schema, obj.table_name, p_user;
    END LOOP;

    -- Grant on sequences (USAGE + SELECT + UPDATE: full coverage)
    FOR obj IN
        SELECT c.relname AS sequence_name
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'S'
          AND n.nspname = p_schema
    LOOP
        EXECUTE format('GRANT USAGE, SELECT, UPDATE ON SEQUENCE %I.%I TO %I;', p_schema, obj.sequence_name, p_user);
        RAISE NOTICE 'GRANT USAGE, SELECT, UPDATE ON SEQUENCE %.% TO %;', p_schema, obj.sequence_name, p_user;
    END LOOP;

    -- Grant on all functions (handles all argument types)
    FOR obj IN
        SELECT
            p.proname AS function_name,
            pg_get_function_identity_arguments(p.oid) AS args
        FROM
            pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE
            n.nspname = p_schema
            AND p.prokind = 'f' -- f = function
    LOOP
        EXECUTE format(
            'GRANT EXECUTE ON FUNCTION %I.%I(%s) TO %I;',
            p_schema, obj.function_name, obj.args, p_user
        );
        RAISE NOTICE 'GRANT EXECUTE ON FUNCTION %.%(%) TO %;', p_schema, obj.function_name, obj.args, p_user;
    END LOOP;

    -- Grant on all procedures (Postgres 11+)
    FOR obj IN
        SELECT
            p.proname AS procedure_name,
            pg_get_function_identity_arguments(p.oid) AS args
        FROM
            pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE
            n.nspname = p_schema
            AND p.prokind = 'p' -- p = procedure
    LOOP
        EXECUTE format(
            'GRANT EXECUTE ON PROCEDURE %I.%I(%s) TO %I;',
            p_schema, obj.procedure_name, obj.args, p_user
        );
        RAISE NOTICE 'GRANT EXECUTE ON PROCEDURE %.%(%) TO %;', p_schema, obj.procedure_name, obj.args, p_user;
    END LOOP;

END;
$function$

-- Function: pg_get_tabledef
CREATE OR REPLACE FUNCTION public.pg_get_tabledef(p_table_name text)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
                            DECLARE
                                col RECORD;
                                col_defs TEXT := '';
                                pk_cols TEXT := '';
                                result TEXT;
                            BEGIN
                                FOR col IN
                                    SELECT 
                                        column_name,
                                        data_type,
                                        character_maximum_length,
                                        numeric_precision,
                                        numeric_scale,
                                        is_nullable,
                                        column_default
                                    FROM information_schema.columns
                                    WHERE table_schema = 'public' AND table_name = p_table_name
                                    ORDER BY ordinal_position
                                LOOP
                                    col_defs := col_defs || 
                                        format('"%s" %s%s%s%s, ',
                                            col.column_name,
                                            CASE 
                                                WHEN col.data_type = 'character varying' THEN format('varchar(%s)', col.character_maximum_length)
                                                WHEN col.data_type = 'numeric' THEN format('numeric(%s,%s)', col.numeric_precision, col.numeric_scale)
                                                ELSE col.data_type
                                            END,
                                            CASE WHEN col.column_default IS NOT NULL THEN ' DEFAULT ' || col.column_default ELSE '' END,
                                            CASE WHEN col.is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END,
                                            ''
                                        );
                                END LOOP;

                                -- Get primary key columns
                                SELECT string_agg(format('"%s"', kcu.column_name), ', ')
                                INTO pk_cols
                                FROM information_schema.table_constraints tc
                                JOIN information_schema.key_column_usage kcu 
                                  ON tc.constraint_name = kcu.constraint_name
                                WHERE tc.table_schema = 'public' 
                                  AND tc.table_name = p_table_name 
                                  AND tc.constraint_type = 'PRIMARY KEY';

                                IF pk_cols IS NOT NULL THEN
                                    col_defs := col_defs || format('PRIMARY KEY (%s), ', pk_cols);
                                END IF;

                                col_defs := left(col_defs, length(col_defs) - 2);
                                result := format('CREATE TABLE "%s" (%s);', p_table_name, col_defs);
                                RETURN result;
                            END;
                            $function$

-- Function: get_all_products
CREATE OR REPLACE FUNCTION public.get_all_products(p_company_id uuid, p_product_group_id integer)
 RETURNS TABLE(product_id uuid, product_group_id integer, product_group_name text, product_name text, unit_id uuid, unit_name text, category_id uuid, category_name text, description text, hsn_code text, sac text, purchase_price numeric, selling_price numeric, opening_stock numeric, minimum_stock numeric, vendor_id uuid, vendor_name text, tax_category_id integer, tax_category_name text, maximum_stock numeric, cgst_rate numeric, sgst_rate numeric, igst_rate numeric)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT
        p.id AS product_id,
        p.product_group_id,
        pg.name::TEXT AS product_group_name,
        p.product_name::TEXT,
        p.unit_id,
        COALESCE(u.unit_name, '')::TEXT AS unit_name,
        p.category_id,
        COALESCE(c.category_name, '')::TEXT AS category_name,
        p.description::TEXT,
        p.hsn_code::TEXT,
        p.sac::TEXT,
        p.purchase_price,
        p.selling_price,
        p.opening_stock,
        p.minimum_stock,
        p.vendor_id,
        NULL::TEXT AS vendor_name, -- Replace with actual vendor join if needed
        p.tax_category_id,
        COALESCE(ptc.name, '')::TEXT AS tax_category_name,
        p.maximum_stock,
        ptcd.c_gst_rate,
        ptcd.s_gst_rate,
        ptcd.i_gst_rate
    FROM products p
    INNER JOIN company_products cp ON p.id = cp.product_id
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN product_tax_categories ptc ON p.tax_category_id = ptc.id
    LEFT JOIN product_tax_category_details ptcd ON ptc.id = ptcd.producttaxcategory_id
    INNER JOIN product_group pg ON p.product_group_id = pg.id
    LEFT JOIN units u ON p.unit_id = u.id
    WHERE cp.company_id = p_company_id
      AND p.product_group_id = p_product_group_id
      AND (cp.is_deleted IS FALSE OR cp.is_deleted IS NULL)
      AND (p.is_deleted IS FALSE OR p.is_deleted IS NULL);
END;
$function$

-- Function: get_products_by_company_and_group
CREATE OR REPLACE FUNCTION public.get_products_by_company_and_group(in_company_id uuid, in_product_group_id integer)
 RETURNS TABLE(id uuid, product_group_id integer, product_name text, unit_id uuid, unit_name text, category_id uuid, category_name text, description text, hsn_code text, sac text, purchase_price numeric, selling_price numeric, opening_stock numeric, minimum_stock numeric, vendor_id uuid, tax_category_id uuid, maximum_stock numeric, cgst_rate numeric, sgst_rate numeric, igst_rate numeric)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT 
        p.id,
        p.product_group_id,
        p.product_name::TEXT,
        p.unit_id,
        u.unit_name::TEXT,
        p.category_id,
        c.category_name::TEXT,
        p.description::TEXT,
        p.hsn_code::TEXT,
        p.sac::TEXT,
        p.purchase_price,
        p.selling_price,
        p.opening_stock,
        p.minimum_stock,
        p.vendor_id,
        p.tax_category_id,
        p.maximum_stock,
        ptcd.c_gst_rate,
        ptcd.s_gst_rate,
        ptcd.i_gst_rate
    FROM products p
    INNER JOIN company_products cp ON p.id = cp.product_id
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN product_tax_categories ptc ON p.tax_category_id = ptc.id
    LEFT JOIN product_tax_category_details ptcd ON ptc.id = ptcd.producttaxcategory_id
    INNER JOIN product_group pg ON p.product_group_id = pg.id
    LEFT JOIN units u ON u.id = p.unit_id
    WHERE cp.company_id = in_company_id
      AND p.product_group_id = in_product_group_id;
END;
$function$

-- Function: get_all_stock_adjustments_from_span
CREATE OR REPLACE FUNCTION public.get_all_stock_adjustments_from_span(p_company_id uuid, p_user_id uuid, p_warehouse_id uuid DEFAULT NULL::uuid, p_start_date timestamp without time zone DEFAULT NULL::timestamp without time zone, p_end_date timestamp without time zone DEFAULT NULL::timestamp without time zone)
 RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, warehouse_id uuid, stock_adjustment_status text, stock_adjustment_status_id integer, reason_code text, stock_adjustment_number text, remarks text, adjustment_date date, created_by uuid, created_on_utc timestamp without time zone, modified_by uuid, modified_on_utc timestamp without time zone, created_by_name text, modified_by_name text, line_count integer, total_difference numeric)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_start_date date := COALESCE(p_start_date::date, '1970-01-01'::date);
    v_end_date   date := COALESCE(p_end_date::date,   '9999-12-31'::date);
BEGIN
    RETURN QUERY
    WITH headers AS (
        SELECT h.*
        FROM public.stock_adjustment_headers h
        WHERE h.company_id = p_company_id
          AND h.is_deleted = false
          AND (p_warehouse_id IS NULL OR p_warehouse_id = '00000000-0000-0000-0000-000000000000'::uuid OR h.warehouse_id = p_warehouse_id)
          AND (h.adjustment_date_utc::date BETWEEN v_start_date AND v_end_date)
    ), line_agg AS (
        SELECT
            sd.stock_adjustment_header_id,
            COUNT(*)::int AS line_count,
            SUM(sd.difference_quantity)::numeric AS total_difference
        FROM public.stock_adjustment_details sd
        WHERE sd.is_deleted = false
        GROUP BY sd.stock_adjustment_header_id
    )
    SELECT
        h.id,
        h.organization_id,
        h.company_id,
        h.warehouse_id,
        COALESCE(s.name, '') AS stock_adjustment_status,
        h.stock_adjustment_status_id,
        h.reason_code,
        h.stock_adjustment_number,
        h.remarks,
        h.adjustment_date_utc::date AS adjustment_date,
        h.created_by,
        h.created_on_utc,
        h.modified_by,
        h.modified_on_utc,
        CONCAT(cu.first_name, ' ', cu.last_name)::text AS created_by_name,
        CONCAT(mu.first_name, ' ', mu.last_name)::text AS modified_by_name,
        COALESCE(la.line_count, 0) AS line_count,
        COALESCE(la.total_difference, 0.0) AS total_difference
    FROM headers h
    LEFT JOIN public.stock_adjustment_statuses s ON s.id = h.stock_adjustment_status_id
    LEFT JOIN public.users cu ON cu.id = h.created_by
    LEFT JOIN public.users mu ON mu.id = h.modified_by
    LEFT JOIN line_agg la ON la.stock_adjustment_header_id = h.id;
END;
$function$

-- Procedure: hard_delete_inventory_data
CREATE OR REPLACE PROCEDURE public.hard_delete_inventory_data(IN p_company_id uuid)
 LANGUAGE plpgsql
AS $procedure$
BEGIN
    -- Delete from dependent tables first (if foreign key constraints exist)

    -- Orders (assuming these are transactional)
    DELETE FROM orders WHERE company_id = p_company_id;

    -- Inventory master data
    DELETE FROM company_products WHERE company_id = p_company_id;
    DELETE FROM company_categories WHERE company_id = p_company_id;
    DELETE FROM product_tax_categories WHERE company_id = p_company_id;

    -- Units and Users
    DELETE FROM units WHERE company_id = p_company_id;
    DELETE FROM users WHERE company_id = p_company_id;

    RAISE NOTICE 'Inventory data for company_id % has been hard deleted.', p_company_id;
END;
$procedure$

-- Procedure: hard_delete_org_inventory
CREATE OR REPLACE PROCEDURE public.hard_delete_org_inventory(IN p_organization_id uuid)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_company_id uuid;
BEGIN
    FOR v_company_id IN SELECT id FROM companies WHERE organization_id = p_organization_id LOOP
        DELETE FROM orders WHERE company_id = v_company_id;
        DELETE FROM company_products WHERE company_id = v_company_id;
        DELETE FROM company_categories WHERE company_id = v_company_id;
        DELETE FROM product_tax_categories WHERE company_id = v_company_id;
        DELETE FROM units WHERE company_id = v_company_id;
        DELETE FROM users WHERE company_id = v_company_id;
        DELETE FROM companies WHERE id = v_company_id;
    END LOOP;

    DELETE FROM organizations WHERE id = p_organization_id;

    RAISE NOTICE 'Deleted inventory data for organization_id: %', p_organization_id;
END;
$procedure$

-- Procedure: initialize_company
CREATE OR REPLACE PROCEDURE public.initialize_company(IN p_company_id uuid, IN p_organization_id uuid, IN p_is_apartment boolean, IN p_name text, IN p_created_by uuid, IN p_default_company_id uuid)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_company_exists boolean;
    v_is_apartment boolean;
    v_products_exist boolean;
    v_categories_exist boolean;
BEGIN
    -- Check if company already exists
    SELECT EXISTS (
        SELECT 1 FROM public.companies 
        WHERE id = p_company_id
    ) INTO v_company_exists;
    
    IF NOT v_company_exists THEN
		  -- Insert into companies table
       INSERT INTO public.companies (
        id, 
        organization_id, 
        is_apartment,
        name,        
        created_on_utc, 
        created_by
      ) VALUES (
        p_company_id, 
        p_organization_id, 
        p_is_apartment,
        p_name,
        NOW(), 
        p_created_by 
      );
      RAISE NOTICE 'Created company % (ID: %) for organization %', 
        p_name, p_company_id, p_organization_id;
   ELSE
        RAISE NOTICE 'Company % (ID: %) already exists for organization %. Skipping initialization.', 
            p_name, p_company_id, p_organization_id;
   END IF;

    -- Check if the company is an apartment
    IF p_is_apartment THEN
        -- Check if products already exist for this company
        SELECT EXISTS (
            SELECT 1 FROM public.company_products 
            WHERE company_id = p_company_id
        ) INTO v_products_exist;
        
        IF NOT v_products_exist THEN
            -- Insert into company_products
            INSERT INTO public.company_products(
                id,
                company_id,
                product_id,
                created_on_utc,
                created_by
            )
            SELECT 
                gen_random_uuid(),
                p_company_id,
                product_id,
                NOW(),
                p_created_by
            FROM public.default_apartment_products;
            
            RAISE NOTICE 'Added default products for apartment company %', p_name;
        ELSE
            RAISE NOTICE 'Products already exist for company %. Skipping product initialization.', p_name;
        END IF;
    END IF;

    -- Check if categories already exist for this company
    SELECT EXISTS (
        SELECT 1 FROM public.company_categories
        WHERE company_id = p_company_id
    ) INTO v_categories_exist;
    
    IF NOT v_categories_exist THEN
        -- Insert into company_categories
        INSERT INTO public.company_categories (
            id,
            company_id,
            category_id,
            created_on_utc,
            created_by
        )
        SELECT 
            nextval('company_categories_seq'),
            p_company_id,
            category_id,
            NOW(),
            p_created_by
        FROM public.company_categories cc
        WHERE cc.company_id = p_default_company_id;
        
        RAISE NOTICE 'Copied categories from default company % to new company %', 
            p_default_company_id, p_company_id;
    ELSE
        RAISE NOTICE 'Categories already exist for company %. Skipping category initialization.', p_name;
    END IF;
END;
$procedure$

-- Procedure: initialize_company_products
CREATE OR REPLACE PROCEDURE public.initialize_company_products(IN old_company_id uuid, IN new_company_id uuid)
 LANGUAGE plpgsql
AS $procedure$
BEGIN
    -- Insert new records for the new company
    INSERT INTO company_products (
        id,
        company_id,
        product_id,
        created_on_utc,        
        created_by
    )
    SELECT
        gen_random_uuid(), -- Generate a new UUID for the id column
        new_company_id, -- Assign the new company ID
        product_id,
        created_on_utc,       
        created_by
    FROM company_products
    WHERE company_id = old_company_id;

    -- Optional: Log the operation
    RAISE NOTICE 'Company products have been copied from company % to company %.', old_company_id, new_company_id;

END;
$procedure$

-- Procedure: hard_delete_organization
CREATE OR REPLACE PROCEDURE public.hard_delete_organization(IN p_organization_id uuid)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    p_company_id UUID; -- Variable to hold company IDs associated with the organization
BEGIN
    -- Get the associated company IDs for the organization
    FOR p_company_id IN
        SELECT c.id FROM public.companies c WHERE c.organization_id = p_organization_id
    LOOP 
		 -- Delete company-specific products
        DELETE FROM public.company_products
        WHERE company_id = p_company_id;

		 -- Delete company-specific categories
        DELETE FROM public.company_categories
        WHERE company_id = p_company_id;

		 -- Delete users associated with the company
		DELETE FROM public.users
		WHERE users.company_id = p_company_id;
	
        -- Delete from companies
        DELETE FROM public.companies
        WHERE id = p_company_id;
    END LOOP;  

    -- Delete the organization itself
    DELETE FROM public.organizations
    WHERE public.organizations.id = p_organization_id;

    -- Log the operation
    RAISE NOTICE 'Organization with ID % and all related data have been hard deleted.', p_organization_id;

END;
$procedure$

-- Procedure: initialize_organization
CREATE OR REPLACE PROCEDURE public.initialize_organization(IN p_id uuid, IN p_name text, IN p_company_ids text, IN p_company_names text, IN p_user_id uuid, IN p_user_first_name character varying, IN p_user_last_name character varying, IN p_phone_number character varying, IN p_email character varying, IN p_created_by uuid, IN p_default_company_id uuid)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_company_id uuid;
    v_company_name text;
    v_company_ids uuid[];
    v_company_names text[];
    i integer;
    v_organization_exists boolean;
    v_user_exists boolean;
    v_company_exists boolean;
    v_existing_user_id uuid;
    v_update_count integer;
BEGIN
    
    SELECT EXISTS (
        SELECT 1 FROM public.organizations 
        WHERE id = p_id
    ) INTO v_organization_exists;
    
    IF v_organization_exists THEN
        RAISE NOTICE 'Organization with ID %  already exists. Skipping organization creation.', p_id;
    ELSE
		 -- Insert organization if it doesn't exist
        INSERT INTO public.organizations (
          id, name, created_on_utc, created_by, is_deleted
        ) VALUES (
          p_id, p_name, NOW(), p_created_by, false );
        RAISE NOTICE 'Initialized organization: % with ID: %', p_name, p_id;    
    END IF;

    -- Parse company IDs and names
    v_company_ids := string_to_array(p_company_ids, ',');
    v_company_names := string_to_array(p_company_names, ',');

    -- Initialize companies with duplicate checks
    FOR i IN 1..array_length(v_company_ids, 1) LOOP
        v_company_id := v_company_ids[i];
        v_company_name := v_company_names[i];

            CALL public.initialize_company(
                v_company_id, 
                p_id,
                true, -- Indicates it's an apartment
                v_company_name, 
                p_created_by,
                p_default_company_id
            );
    END LOOP;

    -- Assign the first company ID from the array
    v_company_id := v_company_ids[1];

    -- Check if user already exists (by both ID and email)
    SELECT id INTO v_existing_user_id FROM public.users 
    WHERE id = p_user_id OR email = p_email
    LIMIT 1;
    
    v_user_exists := (v_existing_user_id IS NOT NULL);
   
    IF NOT v_user_exists THEN
        INSERT INTO public.users (
            id, first_name, last_name, email,
            created_on_utc, created_by, password_hash,
            phone_number, company_id
        ) VALUES (
            p_user_id, p_user_first_name, p_user_last_name, p_email,
            NOW(), p_created_by, '',
            p_phone_number, v_company_id
        );
        RAISE NOTICE 'New user created with ID: % and email: %', p_user_id, p_email;
    ELSE
        UPDATE public.users 
        SET 
            company_id = v_company_id,
            first_name = COALESCE(p_user_first_name, first_name),
            last_name = COALESCE(p_user_last_name, last_name),
            phone_number = COALESCE(p_phone_number, phone_number),
            email = COALESCE(p_email, email)
        WHERE id = v_existing_user_id;
        
        GET DIAGNOSTICS v_update_count = ROW_COUNT;
        
        IF v_update_count > 0 THEN
            RAISE NOTICE 'Updated existing user % (ID: %) with new company association %', 
                p_email, v_existing_user_id, v_company_id;
        ELSE
            RAISE NOTICE 'User % (ID: %) already associated with company %. No changes made.', 
                p_email, v_existing_user_id, v_company_id;
        END IF;
    END IF;
    
    RAISE NOTICE 'Organization initialization process completed for % (ID: %)', p_name, p_id;
END;
$procedure$

-- Procedure: purge_inventory_organization_data
CREATE OR REPLACE PROCEDURE public.purge_inventory_organization_data(IN p_organization_ids uuid[] DEFAULT NULL::uuid[])
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    -- Retention policy
    c_retention_hours   integer := 24;
    v_cutoff_24h        timestamp := now() - make_interval(hours => GREATEST(c_retention_hours, 1));

    -- Targets
    v_orgs                      uuid[];
    v_companies                 uuid[] := '{}';

    -- Orders
    v_order_ids                 uuid[] := '{}';
    v_order_detail_ids          uuid[] := '{}';

    -- Company-scoped master/config
    v_company_product_ids       uuid[] := '{}';
    v_company_category_ids      int[]  := '{}';
    v_ptc_ids                   int[]  := '{}';   -- product_tax_categories.id
    v_ptcd_ids                  int[]  := '{}';   -- product_tax_category_details.id
    v_unit_ids                  uuid[] := '{}';

    -- Inventory quantities tied to company products
    v_pwq_ids                   int[]  := '{}';   -- product_warehouse_quantities.id
BEGIN
    START TRANSACTION;

    --------------------------------------------------------------------
    -- 1) Resolve target organizations and companies (uniform block)
    --------------------------------------------------------------------
    IF p_organization_ids IS NULL OR array_length(p_organization_ids, 1) IS NULL THEN
        SELECT array_agg(id)
        INTO v_orgs
        FROM organizations
        WHERE created_on_utc > '2025-05-23'
          AND created_on_utc < (NOW() - interval '24 hours');
    ELSE
        v_orgs := p_organization_ids;
    END IF;

    IF v_orgs IS NULL OR array_length(v_orgs, 1) IS NULL THEN
        RAISE NOTICE 'No organizations found for cleanup.';
        COMMIT;
        RETURN;
    END IF;

    -- Companies by organization
    SELECT COALESCE(array_agg(id), '{}')
    INTO v_companies
    FROM companies
    WHERE organization_id = ANY(v_orgs);

    IF v_companies IS NULL OR array_length(v_companies, 1) IS NULL THEN
        RAISE NOTICE 'No companies resolved for inventory purge. Orgs: %', v_orgs;
        COMMIT;
        RETURN;
    END IF;

    RAISE NOTICE 'Inventory purge targets - Organizations: %; Companies: %', v_orgs, v_companies;

    --------------------------------------------------------------------
    -- 2) Collect IDs (COALESCE to '{}' to avoid NULL-array issues)
    --------------------------------------------------------------------
    -- Orders and details (time-gated)
    SELECT COALESCE(array_agg(id), '{}')
    INTO v_order_ids
    FROM orders
    WHERE company_id = ANY(v_companies)
      AND created_on_utc < v_cutoff_24h;

    SELECT COALESCE(array_agg(id), '{}')
    INTO v_order_detail_ids
    FROM order_details
    WHERE order_id = ANY(v_order_ids);

    -- Company products and categories
    SELECT COALESCE(array_agg(id), '{}')
    INTO v_company_product_ids
    FROM company_products
    WHERE company_id = ANY(v_companies);

    SELECT COALESCE(array_agg(id), '{}')
    INTO v_company_category_ids
    FROM company_categories
    WHERE company_id = ANY(v_companies);

    -- Product tax categories (company-scoped) and their details
    SELECT COALESCE(array_agg(id), '{}')
    INTO v_ptc_ids
    FROM product_tax_categories
    WHERE company_id = ANY(v_companies);

    SELECT COALESCE(array_agg(id), '{}')
    INTO v_ptcd_ids
    FROM product_tax_category_details
    WHERE producttaxcategory_id = ANY(v_ptc_ids);

    -- Units (company-scoped)
    SELECT COALESCE(array_agg(id), '{}')
    INTO v_unit_ids
    FROM units
    WHERE company_id = ANY(v_companies);

    -- Product warehouse quantities for products linked to these companies
    SELECT COALESCE(array_agg(pwq.id), '{}')
    INTO v_pwq_ids
    FROM product_warehouse_quantities pwq
    WHERE pwq.product_id IN (
        SELECT DISTINCT cp.product_id
        FROM company_products cp
        WHERE cp.company_id = ANY(v_companies)
    );

    --------------------------------------------------------------------
    -- 3) Purge in strict child → parent order (avoid FK violations)
    --------------------------------------------------------------------
    -- Orders: details → headers
    DELETE FROM order_details
    WHERE id = ANY(v_order_detail_ids);

    DELETE FROM orders
    WHERE id = ANY(v_order_ids);

    -- Inventory quantities tied to purged company-product assignments
    DELETE FROM product_warehouse_quantities
    WHERE id = ANY(v_pwq_ids);

    -- Product tax category details → categories
    DELETE FROM product_tax_category_details
    WHERE id = ANY(v_ptcd_ids);

    DELETE FROM product_tax_categories
    WHERE id = ANY(v_ptc_ids);

    -- Company-scoped assignments
    DELETE FROM company_products
    WHERE id = ANY(v_company_product_ids);

    DELETE FROM company_categories
    WHERE id = ANY(v_company_category_ids);

    -- Company-scoped units
    DELETE FROM units
    WHERE id = ANY(v_unit_ids);

    RAISE NOTICE 'Inventory purge complete for companies: % (orgs: %).', v_companies, v_orgs;

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'purge_inventory_organization_data failed: %', SQLERRM;
        -- Optionally rethrow
        -- RAISE;
END;
$procedure$