-- PROCEDURE: public.update_shipment_status_and_log(jsonb, jsonb)

-- DROP PROCEDURE IF EXISTS public.update_shipment_status_and_log(jsonb, jsonb);

CREATE OR REPLACE PROCEDURE public.update_shipment_status_and_log(
    IN  json_input jsonb,
    INOUT result jsonb
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_shipment_id     int;
    v_company_id      int;        -- kept if you want to validate tenant; not used below unless you add a check
    v_new_state_slug  text;       -- We now accept 'slug' for the shipment status
    v_new_state_id    int;
    v_changed_by      int;        -- optional (from JSON)
    v_now             timestamptz := now();

    v_old_state       int;

    r_customer        record;
    r_location        record;
    r_dnote           record;

    cnt_shipments           int := 0;
    cnt_shipment_customers  int := 0;
    cnt_shipment_location   int := 0;
    cnt_shipment_delivery   int := 0;

BEGIN
    -- Parse input
    v_shipment_id    := (json_input ->> 'shipment_id')::int;
    v_company_id     := NULLIF(json_input ->> 'company_id','')::int;              -- optional
    v_new_state_slug := (json_input ->> 'shipment_status')::text;
    v_changed_by     := COALESCE(
                           NULLIF(json_input ->> 'changed_by','')::int,
                           NULLIF(json_input ->> 'updated_by','')::int,
                           NULL
                       );

    -- Initialize result
    result := jsonb_build_object(
        'status',  'init',
        'input',   json_input,
        'summary', jsonb_build_object(
            'shipments', 0,
            'shipment_customers', 0,
            'shipment_location', 0,
            'shipment_delivery_notes', 0
        )
    );

    -- Fetch shipment status ID based on the provided slug
    SELECT id INTO v_new_state_id
      FROM shipment_status
     WHERE slug = v_new_state_slug
     LIMIT 1;

    IF v_new_state_id IS NULL THEN
        result := jsonb_build_object(
            'status','failure',
            'error','shipment_status slug not found'
        );
        RETURN;
    END IF;

    -- Find shipment by ID
    SELECT shipment_status_id INTO v_old_state
      FROM shipments
     WHERE id = v_shipment_id
     LIMIT 1;

    IF v_old_state IS NULL THEN
        result := jsonb_build_object(
            'status','failure',
            'error','shipment not found for given shipment_id'
        );
        RETURN;
    END IF;

    -- ========== Update SHIPMENTS (single row) ==========
    IF v_old_state IS DISTINCT FROM v_new_state_id THEN
        UPDATE shipments
           SET shipment_status_id = v_new_state_id,
               updated_by         = v_changed_by,
               updated_at         = v_now
         WHERE id = v_shipment_id;

        INSERT INTO shipment_status_history (company_id, from_state, to_state, changed_by, changed_at, table_name, ref_id)
        VALUES (v_company_id, v_old_state, v_new_state_id, v_changed_by, v_now, 'shipments', v_shipment_id);

        cnt_shipments := 1;
    END IF;

    -- ========== Update SHIPMENT_CUSTOMERS (many rows) ==========
    FOR r_customer IN
        SELECT id, shipment_status_id
          FROM shipment_customers
         WHERE shipment_id = v_shipment_id
    LOOP
        IF r_customer.shipment_status_id IS DISTINCT FROM v_new_state_id THEN
            UPDATE shipment_customers
               SET shipment_status_id = v_new_state_id,
                   updated_by         = v_changed_by,
                   updated_at         = v_now
             WHERE id = r_customer.id;

            INSERT INTO shipment_status_history (company_id, from_state, to_state, changed_by, changed_at, table_name, ref_id)
            VALUES (v_company_id, r_customer.shipment_status_id, v_new_state_id, v_changed_by, v_now, 'shipment_customers', r_customer.id);

            cnt_shipment_customers := cnt_shipment_customers + 1;
        END IF;
    END LOOP;

    -- ========== Update SHIPMENT_LOCATION (many rows) ==========
    FOR r_location IN
        SELECT id, shipment_status_id
          FROM shipment_location
         WHERE shipment_id = v_shipment_id
    LOOP
        IF r_location.shipment_status_id IS DISTINCT FROM v_new_state_id THEN
            UPDATE shipment_location
               SET shipment_status_id = v_new_state_id,
                   updated_by         = v_changed_by,
                   updated_at         = v_now
             WHERE id = r_location.id;

            INSERT INTO shipment_status_history (company_id, from_state, to_state, changed_by, changed_at, table_name, ref_id)
            VALUES (v_company_id, r_location.shipment_status_id, v_new_state_id, v_changed_by, v_now, 'shipment_location', r_location.id);

            cnt_shipment_location := cnt_shipment_location + 1;
        END IF;
    END LOOP;

    -- ========== Update SHIPMENT_DELIVERY_NOTES (many rows) ==========
    FOR r_dnote IN
        SELECT id, shipment_status_id
          FROM shipment_delivery_notes
         WHERE shipment_id = v_shipment_id
    LOOP
        IF r_dnote.shipment_status_id IS DISTINCT FROM v_new_state_id THEN
            UPDATE shipment_delivery_notes
               SET shipment_status_id = v_new_state_id,
                   updated_by         = v_changed_by,
                   updated_at         = v_now
             WHERE id = r_dnote.id;

            INSERT INTO shipment_status_history (company_id, from_state, to_state, changed_by, changed_at, table_name, ref_id)
            VALUES (v_company_id, r_dnote.shipment_status_id, v_new_state_id, v_changed_by, v_now, 'shipment_delivery_notes', r_dnote.id);

            cnt_shipment_delivery := cnt_shipment_delivery + 1;
        END IF;
    END LOOP;

    -- Final result
    result := jsonb_build_object(
        'status', 'success',
        'shipment_id', v_shipment_id,
        'new_state', v_new_state_id,
        'changed_by', v_changed_by,
        'changed_at', to_char(v_now, 'YYYY-MM-DD"T"HH24:MI:SSOF'),
        'summary', jsonb_build_object(
            'shipments', cnt_shipments,
            'shipment_customers', cnt_shipment_customers,
            'shipment_location', cnt_shipment_location,
            'shipment_delivery_notes', cnt_shipment_delivery
        )
    );
    RETURN;

EXCEPTION
    WHEN OTHERS THEN
        result := jsonb_build_object(
            'status','failure',
            'error', SQLERRM
        );
        RAISE;
END;
$$;

ALTER PROCEDURE public.update_shipment_status_and_log(jsonb, jsonb) OWNER TO postgres;
