Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

Exists in source, missing in target

-- CreateScript: MissingInSource
CREATE TABLE "user_paid_modules" ("user_id" uuid NOT NULL, "role_name" text NOT NULL, PRIMARY KEY ("user_id", "role_name"));

-- PrimaryKeys: MissingInSource
ALTER TABLE "user_paid_modules" ADD CONSTRAINT "pk_user_paid_modules" PRIMARY KEY (user_id, role_name);

-- Columns: MissingInSource
ALTER TABLE "user_paid_modules" ADD COLUMN "user_id" uuid NOT NULL;

-- Columns: MissingInSource
ALTER TABLE "user_paid_modules" ADD COLUMN "role_name" text NOT NULL;

-- ForeignKeys: MissingInSource
ALTER TABLE "user_paid_modules" ADD CONSTRAINT "fk_user_paid_modules_user" FOREIGN KEY (user_id) REFERENCES users(id);

-- Indexes: MissingInSource
CREATE UNIQUE INDEX pk_user_paid_modules ON public.user_paid_modules USING btree (user_id, role_name)

-- CreateScript: MissingInSource
CREATE TABLE "user_roles" ("user_id" uuid NOT NULL, "paid_modules" bigint NOT NULL, PRIMARY KEY ("user_id"));

-- PrimaryKeys: MissingInSource
ALTER TABLE "user_roles" ADD CONSTRAINT "pk_user_roles" PRIMARY KEY (user_id);

-- Columns: MissingInSource
ALTER TABLE "user_roles" ADD COLUMN "user_id" uuid NOT NULL;

-- Columns: MissingInSource
ALTER TABLE "user_roles" ADD COLUMN "paid_modules" bigint NOT NULL;

-- ForeignKeys: MissingInSource
ALTER TABLE "user_roles" ADD CONSTRAINT "fk_user_roles_user" FOREIGN KEY (user_id) REFERENCES users(id);

-- Indexes: MissingInSource
CREATE UNIQUE INDEX pk_user_roles ON public.user_roles USING btree (user_id)

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$

CREATE OR REPLACE FUNCTION public.get_goods_receipts(p_company_id uuid)
 RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, receipt_no character varying, receipt_type character varying, warehouse_id uuid, warehouse_name character varying, vendor_id uuid, vendor_name text, source_document_type character varying, source_document_id uuid, vendor_invoice_no character varying, receipt_date timestamp without time zone, status character varying, remarks text, posted_on_utc timestamp without time zone, total_items integer, total_received_qty numeric, total_accepted_qty numeric, total_rejected_qty numeric, total_amount numeric)
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT
        grh.id,
        grh.organization_id,
        grh.company_id,
        grh.receipt_no,
        grh.receipt_type,
        grh.warehouse_id,
        w.name AS warehouse_name,
        grh.vendor_id,
		NULL::text AS vendor_name,
        grh.source_document_type,
        grh.source_document_id,
        grh.vendor_invoice_no,
        grh.receipt_date,
        grh.status,
        grh.remarks,
        grh.posted_on_utc,
        COUNT(grd.id)::int AS total_items,
        COALESCE(SUM(grd.received_qty), 0) AS total_received_qty,
        COALESCE(SUM(grd.accepted_qty), 0) AS total_accepted_qty,
        COALESCE(SUM(grd.rejected_qty), 0) AS total_rejected_qty,
        COALESCE(SUM(grd.line_amount), 0) AS total_amount
    FROM public.goods_receipt_headers grh
    INNER JOIN public.warehouses w
        ON w.id = grh.warehouse_id
    --LEFT JOIN public.vendors v
    --    ON v.id = grh.vendor_id
    LEFT JOIN public.goods_receipt_details grd
        ON grd.goods_receipt_header_id = grh.id
    WHERE grh.company_id = p_company_id
      AND grh.is_deleted = false
    GROUP BY
        grh.id,
        grh.organization_id,
        grh.company_id,
        grh.receipt_no,
        grh.receipt_type,
        grh.warehouse_id,
        w.name,
        grh.vendor_id,
        --v.vendor_name,
        grh.source_document_type,
        grh.source_document_id,
        grh.vendor_invoice_no,
        grh.receipt_date,
        grh.status,
        grh.remarks,
        grh.posted_on_utc
    ORDER BY grh.receipt_date DESC, grh.receipt_no DESC;
END;
$function$

CREATE OR REPLACE FUNCTION public.get_goods_receipt_by_id(p_id uuid)
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_result jsonb;
BEGIN
    SELECT jsonb_build_object(
        'id', grh.id,
        'organizationId', grh.organization_id,
        'companyId', grh.company_id,
        'receiptNo', grh.receipt_no,
        'receiptType', grh.receipt_type,
        'warehouseId', grh.warehouse_id,
        'warehouseName', w.name,
        'vendorId', grh.vendor_id,
        'vendorName', NULL,
        'sourceDocumentType', grh.source_document_type,
        'sourceDocumentId', grh.source_document_id,
        'vendorInvoiceNo', grh.vendor_invoice_no,
        'receiptDate', grh.receipt_date,
        'status', grh.status,
        'remarks', grh.remarks,
        'postedOnUtc', grh.posted_on_utc,
        'postedBy', grh.posted_by,
        'details', COALESCE(
            (
                SELECT jsonb_agg(
                    jsonb_build_object(
                        'id', grd.id,
                        'goodsReceiptHeaderId', grd.goods_receipt_header_id,
                        'lineNumber', grd.line_number,
                        'productId', grd.product_id,
                        'productName', p.product_name,
                        'unitId', grd.unit_id,
                        'unitName', u.unit_name,
                        'orderedQty', grd.ordered_qty,
                        'receivedQty', grd.received_qty,
                        'acceptedQty', grd.accepted_qty,
                        'rejectedQty', grd.rejected_qty,
                        'unitRate', grd.unit_rate,
                        'lineAmount', grd.line_amount,
                        'warehouseLocationId', grd.warehouse_location_id,
                        'warehouseLocationName', wl.name,
                        'remarks', grd.remarks
                    )
                    ORDER BY grd.line_number
                )
                FROM public.goods_receipt_details grd
                INNER JOIN public.products p
                    ON p.id = grd.product_id
                INNER JOIN public.units u
                    ON u.id = grd.unit_id
                LEFT JOIN public.warehouse_locations wl
                    ON wl.id = grd.warehouse_location_id
                WHERE grd.goods_receipt_header_id = grh.id
            ),
            '[]'::jsonb
        )
    )
    INTO v_result
    FROM public.goods_receipt_headers grh
    INNER JOIN public.warehouses w
        ON w.id = grh.warehouse_id
    WHERE grh.id = p_id
      AND grh.is_deleted = false;

    RETURN v_result;
END;
$function$

CREATE OR REPLACE FUNCTION public.update_material_request_next_status_main(p_company_id uuid, p_material_request_ids uuid[], p_modified_by uuid)
 RETURNS TABLE(material_request_id uuid, status_name text, error_msg text)
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_request_id uuid;
    v_current_status_name varchar(100);
    v_current_status_id int4;
    v_next_status_id int4;
    v_next_status_name varchar(100);
    v_approval_level int4;
    v_user_allowed boolean;
    v_has_partial_issue boolean;
BEGIN
    FOREACH v_request_id IN ARRAY p_material_request_ids
    LOOP
        v_current_status_name := NULL;
        v_current_status_id := NULL;
        v_next_status_id := NULL;
        v_next_status_name := NULL;
        v_approval_level := 0;
        v_user_allowed := false;
        v_has_partial_issue := false;

        -- 1. Validate request exists
        SELECT mrh.status
        INTO v_current_status_name
        FROM public.material_request_headers mrh
        WHERE mrh.id = v_request_id
          AND mrh.company_id = p_company_id
          AND COALESCE(mrh.is_deleted, false) = false;

        IF v_current_status_name IS NULL THEN
            material_request_id := v_request_id;
            status_name := NULL;
            error_msg := 'Material request not found';
            RETURN NEXT;
            CONTINUE;
        END IF;

        -- 2. Map current status name -> status id
        SELECT mrs.id
        INTO v_current_status_id
        FROM public.material_request_statuses mrs
        WHERE lower(mrs.name) = lower(v_current_status_name)
          AND COALESCE(mrs.is_deleted, false) = false
        LIMIT 1;

        IF v_current_status_id IS NULL THEN
            material_request_id := v_request_id;
            status_name := v_current_status_name;
            error_msg := 'Current status is invalid';
            RETURN NEXT;
            CONTINUE;
        END IF;

        -- 3. Terminal statuses cannot move next
        IF lower(v_current_status_name) IN ('rejected', 'closed', 'cancelled') THEN
            material_request_id := v_request_id;
            status_name := v_current_status_name;
            error_msg := 'Next status update is not allowed for current status';
            RETURN NEXT;
            CONTINUE;
        END IF;

        /*
            4. Special business rule:
               Approved -> Closed OR Partially Issued
               Based on detail quantities
        */
        IF lower(v_current_status_name) = 'approved' THEN
            SELECT EXISTS (
                SELECT 1
                FROM public.material_request_details mrd
                WHERE mrd.material_request_header_id = v_request_id
                  AND COALESCE(mrd.is_deleted, false) = false
                  AND COALESCE(mrd.issued_qty, 0) < COALESCE(mrd.approved_qty, mrd.requested_qty)
            )
            INTO v_has_partial_issue;

            IF v_has_partial_issue THEN
                SELECT mrs.id, mrs.name
                INTO v_next_status_id, v_next_status_name
                FROM public.material_request_statuses mrs
                WHERE lower(mrs.name) = lower('Partially Issued')
                  AND COALESCE(mrs.is_deleted, false) = false
                LIMIT 1;
            ELSE
                SELECT mrs.id, mrs.name
                INTO v_next_status_id, v_next_status_name
                FROM public.material_request_statuses mrs
                WHERE lower(mrs.name) = lower('Closed')
                  AND COALESCE(mrs.is_deleted, false) = false
                LIMIT 1;
            END IF;

            IF v_next_status_id IS NULL THEN
                material_request_id := v_request_id;
                status_name := v_current_status_name;
                error_msg := 'Next status master not found';
                RETURN NEXT;
                CONTINUE;
            END IF;

            UPDATE public.material_request_headers
            SET status = v_next_status_name,
                modified_by = p_modified_by,
                modified_on_utc = now()
            WHERE id = v_request_id;

            INSERT INTO public.material_request_approval_logs(
                material_request_id,
                status_id,
                approved_by,
                approved_on,
                comment,
                created_on_utc,
                created_by,
                approval_level
            )
            VALUES (
                v_request_id,
                v_next_status_id,
                p_modified_by,
                now(),
                'Moved to next status from ' || v_current_status_name || ' to ' || v_next_status_name,
                now(),
                p_modified_by,
                0
            );

            INSERT INTO public.material_request_approval_issue_logs(
                material_request_id,
                issue,
                created_on_utc,
                created_by
            )
            VALUES (
                v_request_id,
                'Moved to status ' || v_next_status_name,
                now(),
                p_modified_by
            );

            material_request_id := v_request_id;
            status_name := v_next_status_name;
            error_msg := NULL;
            RETURN NEXT;
            CONTINUE;
        END IF;

        -- 5. Workflow-driven next status for all other non-terminal statuses
        SELECT mrw.next_status, COALESCE(mrw.approval_level, 0)
        INTO v_next_status_id, v_approval_level
        FROM public.material_request_workflows mrw
        WHERE mrw.company_id = p_company_id
          AND mrw.status = v_current_status_id
          AND COALESCE(mrw.is_deleted, false) = false
        LIMIT 1;

        IF v_next_status_id IS NULL THEN
            material_request_id := v_request_id;
            status_name := v_current_status_name;
            error_msg := 'Next status is not configured';
            RETURN NEXT;
            CONTINUE;
        END IF;

        -- 6. Validate user authorization when approval level applies
        IF COALESCE(v_approval_level, 0) > 0 THEN
            SELECT EXISTS (
                SELECT 1
                FROM public.material_request_approval_user_company mrauc
                WHERE mrauc.company_id = p_company_id
                  AND mrauc.status_id = v_current_status_id
                  AND mrauc.approval_level = v_approval_level
                  AND mrauc.user_id = p_modified_by
                  AND COALESCE(mrauc.is_deleted, false) = false
            )
            INTO v_user_allowed;

            IF NOT COALESCE(v_user_allowed, false) THEN
                material_request_id := v_request_id;
                status_name := v_current_status_name;
                error_msg := 'User is not authorized to move this status';
                RETURN NEXT;
                CONTINUE;
            END IF;
        END IF;

        -- 7. Resolve next status name
        SELECT mrs.name
        INTO v_next_status_name
        FROM public.material_request_statuses mrs
        WHERE mrs.id = v_next_status_id
          AND COALESCE(mrs.is_deleted, false) = false
        LIMIT 1;

        IF v_next_status_name IS NULL THEN
            material_request_id := v_request_id;
            status_name := v_current_status_name;
            error_msg := 'Next status master not found';
            RETURN NEXT;
            CONTINUE;
        END IF;

        -- 8. Update header
        UPDATE public.material_request_headers
        SET status = v_next_status_name,
            modified_by = p_modified_by,
            modified_on_utc = now()
        WHERE id = v_request_id;

        -- 9. Insert approval log
        INSERT INTO public.material_request_approval_logs(
            material_request_id,
            status_id,
            approved_by,
            approved_on,
            comment,
            created_on_utc,
            created_by,
            approval_level
        )
        VALUES (
            v_request_id,
            v_next_status_id,
            p_modified_by,
            now(),
            'Moved to next status from ' || v_current_status_name || ' to ' || v_next_status_name,
            now(),
            p_modified_by,
            v_approval_level
        );

        -- 10. Issue log only for issue statuses
        IF lower(v_next_status_name) IN ('partially issued', 'issued', 'closed') THEN
            INSERT INTO public.material_request_approval_issue_logs(
                material_request_id,
                issue,
                created_on_utc,
                created_by
            )
            VALUES (
                v_request_id,
                'Moved to status ' || v_next_status_name,
                now(),
                p_modified_by
            );
        END IF;

        material_request_id := v_request_id;
        status_name := v_next_status_name;
        error_msg := NULL;
        RETURN NEXT;
    END LOOP;

    RETURN;
END;
$function$

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$

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$

CREATE OR REPLACE FUNCTION public.get_all_products_by_company_and_group(p_company_id uuid, p_product_group_id integer)
 RETURNS TABLE(id uuid, product_group_id integer, product_group_name text, product_name character varying, unit_id uuid, unit 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 numeric, sgst numeric, igst numeric)
 LANGUAGE sql
 STABLE
AS $function$
    SELECT
        p.id,
        p.product_group_id,
        ''::text AS product_group_name,
        p.product_name,
        p.unit_id,
        COALESCE(un.unit_name, '')::text AS unit,
        p.category_id,
        COALESCE(cat.category_name, '')::text AS category_name,
        p.description,
        p.hsn_code,
        p.sac,
        p.purchase_price,
        p.selling_price,
        p.opening_stock,
        p.minimum_stock,
        p.vendor_id,
        NULL::text AS vendor_name,
        p.tax_category_id,
        ''::text AS tax_category_name,
        p.maximum_stock,
        ptcd.c_gst_rate AS cgst,
        ptcd.s_gst_rate AS sgst,
        ptcd.i_gst_rate AS igst
    FROM public.products p
    INNER JOIN public.company_products cp
        ON p.id = cp.product_id
    LEFT JOIN public.categories cat
        ON p.category_id = cat.id
    LEFT JOIN public.product_tax_categories ptc
        ON p.tax_category_id = ptc.id
    LEFT JOIN public.product_tax_category_details ptcd
        ON ptc.id = ptcd.producttaxcategory_id
    INNER JOIN public.product_group pg
        ON p.product_group_id = pg.id
    LEFT JOIN public.units un
        ON un.id = p.unit_id
    WHERE cp.company_id = p_company_id
      AND p.product_group_id = p_product_group_id;
$function$

CREATE OR REPLACE FUNCTION public.get_all_material_requests(p_company_id uuid)
 RETURNS TABLE(id uuid, organization_id uuid, company_id uuid, request_no character varying, request_type character varying, requested_by uuid, requesting_department character varying, warehouse_id uuid, warehouse_name character varying, required_date date, status character varying, remarks text, created_on_utc timestamp without time zone, modified_on_utc timestamp without time zone, deleted_on_utc timestamp without time zone, is_deleted boolean, created_by uuid, modified_by uuid, created_by_name character varying, modified_by_name character varying)
 LANGUAGE sql
 STABLE
AS $function$
    SELECT
        mrh.id,
        mrh.organization_id,
        mrh.company_id,
        mrh.request_no,
        mrh.request_type,
        mrh.requested_by,
        mrh.requesting_department,
        mrh.warehouse_id,
        w.name AS warehouse_name,
        mrh.required_date,
        mrh.status,
        mrh.remarks,
        mrh.created_on_utc,
        mrh.modified_on_utc,
        mrh.deleted_on_utc,
        mrh.is_deleted,
        mrh.created_by,
        mrh.modified_by,
        CAST(TRIM(CONCAT(cu.first_name, ' ', cu.last_name)) AS varchar) AS created_by_name,
        CAST(TRIM(CONCAT(mu.first_name, ' ', mu.last_name)) AS varchar) AS modified_by_name
    FROM public.material_request_headers mrh
    LEFT JOIN public.warehouses w
        ON mrh.warehouse_id = w.id
       AND w.is_deleted = false
    LEFT JOIN public.users cu
        ON cu.id = mrh.created_by
    LEFT JOIN public.users mu
        ON mu.id = mrh.modified_by
    WHERE mrh.company_id = p_company_id
      AND mrh.is_deleted = false
    ORDER BY mrh.created_on_utc DESC;
$function$

CREATE OR REPLACE FUNCTION public.get_new_material_request_number(p_company_id uuid, p_date date)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_finance_year integer;
    v_new_id integer;
    v_prefix varchar;
    v_length integer;
    v_material_request_no varchar;
BEGIN
    -- 1. Determine Financial Year (April–March)
    IF EXTRACT(MONTH FROM p_date) >= 4 THEN
        v_finance_year := EXTRACT(YEAR FROM p_date);
    ELSE
        v_finance_year := EXTRACT(YEAR FROM p_date) - 1;
    END IF;

    -- 2. Try UPDATE (Atomic Increment)
    WITH updated AS (
        UPDATE public.material_request_headers_ids
        SET last_material_request_id = last_material_request_id + 1
        WHERE company_id = p_company_id
          AND fin_year = v_finance_year
        RETURNING 
            last_material_request_id,
            material_request_prefix,
            material_request_length
    ),
    inserted AS (
        -- 3. If not exists → INSERT default
        INSERT INTO public.material_request_headers_ids (
            company_id,
            fin_year,
            material_request_prefix,
            material_request_length,
            last_material_request_id
        )
        SELECT 
            p_company_id,
            v_finance_year,
            'MR',       -- Default Prefix
            6,          -- Default Length (can change later)
            1
        WHERE NOT EXISTS (SELECT 1 FROM updated)
        RETURNING 
            last_material_request_id,
            material_request_prefix,
            material_request_length
    )

    -- 4. Get values
    SELECT 
        COALESCE((SELECT last_material_request_id FROM updated LIMIT 1),
                 (SELECT last_material_request_id FROM inserted LIMIT 1)),
        COALESCE((SELECT material_request_prefix FROM updated LIMIT 1),
                 (SELECT material_request_prefix FROM inserted LIMIT 1)),
        COALESCE((SELECT material_request_length FROM updated LIMIT 1),
                 (SELECT material_request_length FROM inserted LIMIT 1))
    INTO v_new_id, v_prefix, v_length;

    -- 5. Generate Material Request Number
    v_material_request_no := v_prefix || LPAD(v_new_id::text, v_length, '0');

    RETURN v_material_request_no;
END;
$function$

CREATE OR REPLACE FUNCTION public.get_gate_pass_by_id(p_gate_pass_id uuid)
 RETURNS TABLE(id uuid, gate_pass_no character varying, gate_pass_type character varying, warehouse_id uuid, warehouse_name character varying, party_type character varying, party_id uuid, vehicle_no character varying, driver_name character varying, purpose character varying, reference_document_type character varying, reference_document_id uuid, gate_out_time timestamp without time zone, gate_in_time timestamp without time zone, status character varying, remarks text, created_on_utc timestamp without time zone, modified_on_utc timestamp without time zone, deleted_on_utc timestamp without time zone, is_deleted boolean, created_by uuid, modified_by uuid, created_by_name character varying, modified_by_name character varying, details jsonb, logs jsonb)
 LANGUAGE sql
AS $function$
SELECT
    gh.id,
    gh.gate_pass_no,
    gh.gate_pass_type,
    gh.warehouse_id,
    w."name" AS warehouse_name,
    gh.party_type,
    gh.party_id,
    gh.vehicle_no,
    gh.driver_name,
    gh.purpose,
    gh.reference_document_type,
    gh.reference_document_id,
    gh.gate_out_time,
    gh.gate_in_time,
    gh.status,
    gh.remarks,
    gh.created_on_utc,
    gh.modified_on_utc,
    gh.deleted_on_utc,
    gh.is_deleted,
    gh.created_by,
    gh.modified_by,
    CONCAT(cu.first_name, ' ', cu.last_name) AS created_by_name,
    CONCAT(mu.first_name, ' ', mu.last_name) AS modified_by_name,

    -- ================= DETAILS =================
    (
        SELECT jsonb_agg(
            jsonb_build_object(
                'id', gpd.id,
                'productId', gpd.product_id,
                'productName', p.product_name,
                'unitId', gpd.unit_id,
                'unitName', u.unit_name,
                'quantity', gpd.quantity,
                'remarks', gpd.remarks
            )
        )
        FROM gate_pass_details gpd
        LEFT JOIN products p ON p.id = gpd.product_id
        LEFT JOIN units u ON u.id = gpd.unit_id
        WHERE gpd.gate_pass_header_id = gh.id
          AND gpd.is_deleted = false
    ) AS details,

    -- ================= LOGS =================
    (
        SELECT jsonb_agg(
            jsonb_build_object(
                'id', gl.id,
                'log_message', gl.log_message,
                'created_on_utc', gl.created_on_utc,
                'created_by', gl.created_by,
                'created_by_name',
                CONCAT(u.first_name, ' ', u.last_name)
            )
            ORDER BY gl.created_on_utc DESC
        )
        FROM gate_pass_logs gl
        LEFT JOIN users u ON u.id = gl.created_by
        WHERE gl.gate_pass_header_id = gh.id
    ) AS logs

FROM gate_pass_headers gh
LEFT JOIN warehouses w ON w.id = gh.warehouse_id
LEFT JOIN users cu ON cu.id = gh.created_by
LEFT JOIN users mu ON mu.id = gh.modified_by

WHERE gh.id = p_gate_pass_id
  AND gh.is_deleted = false;
$function$