-- 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 $BODY$
DECLARE
    v_shipment_id           int;
    v_company_id            int;        -- optional; (history currently commented)
    v_new_state_slug        text;
    v_new_state_id          int;
    v_changed_by            int;        -- optional (from JSON)
    v_now                   timestamptz := now();
    v_reason                text;
    v_mobile_build_version  text;

    v_old_state_id          int;
    v_old_state_slug        text;
    v_vehicle_id            int;

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

BEGIN
    -- Initialize result early
    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
        )
    );

    -- ========= Parse + Validate input =========
    IF NULLIF(trim(coalesce(json_input->>'shipment_id','')), '') IS NULL THEN
        result := jsonb_build_object('status','failure','error','shipment_id is required');
        RETURN;
    END IF;

    BEGIN
        v_shipment_id := (json_input->>'shipment_id')::int;
    EXCEPTION WHEN others THEN
        result := jsonb_build_object('status','failure','error','shipment_id must be an integer');
        RETURN;
    END;

    -- company_id is optional; avoid crashing on non-numeric input
    BEGIN
        v_company_id := NULLIF(trim(coalesce(json_input->>'company_id','')), '')::int;
    EXCEPTION WHEN others THEN
        v_company_id := NULL;
    END;

    v_new_state_slug := NULLIF(trim(coalesce(json_input->>'shipment_status','')), '');
    IF v_new_state_slug IS NULL THEN
        result := jsonb_build_object('status','failure','error','shipment_status (slug) is required');
        RETURN;
    END IF;

    v_changed_by := COALESCE(
        NULLIF(trim(coalesce(json_input->>'changed_by','')), '')::int,
        NULLIF(trim(coalesce(json_input->>'updated_by','')), '')::int,
        NULL
    );

    v_reason := NULLIF(json_input->>'reason','');
    v_mobile_build_version := NULLIF(json_input->>'mobile_build_version','');

    -- ========= Resolve new status id from 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',
            'shipment_status', v_new_state_slug
        );
        RETURN;
    END IF;

    -- ========= Lock shipment row + load old state =========
	SELECT sh.shipment_status_id, sh.vehicle_id
	INTO v_old_state_id, v_vehicle_id
	FROM shipments sh
	WHERE sh.id = v_shipment_id
	FOR UPDATE;
	
	SELECT ss.slug
	INTO v_old_state_slug
	FROM shipment_status ss
	WHERE ss.id = v_old_state_id;

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

    -- ========= Business rule: loaded only from scheduled =========
    IF v_new_state_slug = 'loaded'
       AND v_old_state_slug IS DISTINCT FROM 'scheduled' THEN
        result := jsonb_build_object(
            'status','failure',
            'error', format(
                'Shipment status cannot be changed from "%s" to "%s"',
                COALESCE(v_old_state_slug, 'unknown'),
                v_new_state_slug
            ),
            'from', COALESCE(v_old_state_slug, 'unknown'),
            'to',   v_new_state_slug
        );
        RETURN;
    END IF;

    -- ========= Concurrency protection for "in_transit" =========
    IF v_new_state_slug = 'in_transit' THEN
        IF v_changed_by IS NOT NULL THEN
            PERFORM pg_advisory_xact_lock(1001, v_changed_by); -- driver namespace
        END IF;

        IF v_vehicle_id IS NOT NULL THEN
            PERFORM pg_advisory_xact_lock(1002, v_vehicle_id); -- vehicle namespace
        END IF;
    END IF;

    -- ========= DRIVER ACTIVE SHIPMENT CHECK =========
    IF v_changed_by IS NOT NULL
       AND v_new_state_slug = 'in_transit'
       AND EXISTS (
            SELECT 1
              FROM shipments s
             WHERE s.driver_id = v_changed_by
               AND s.shipment_status_id = v_new_state_id
               AND s.id <> v_shipment_id
       )
    THEN
        result := jsonb_build_object(
            'status', 'failure',
            'error', 'Driver already has a shipment with in_transit status',
            'driver_id', v_changed_by
        );
        RETURN;
    END IF;

    -- ========= VEHICLE ACTIVE SHIPMENT CHECK =========
    IF v_new_state_slug = 'in_transit'
       AND v_vehicle_id IS NOT NULL
       AND EXISTS (
            SELECT 1
              FROM shipments s
             WHERE s.vehicle_id = v_vehicle_id
               AND s.shipment_status_id = v_new_state_id
               AND s.id <> v_shipment_id
       )
    THEN
        result := jsonb_build_object(
            'status', 'failure',
            'error', 'Vehicle already has a shipment with in_transit status',
            'vehicle_id', v_vehicle_id
        );
        RETURN;
    END IF;

    -- ========= Update SHIPMENTS (history commented) =========
    IF v_old_state_id IS DISTINCT FROM v_new_state_id THEN
        -- 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_id, v_new_state_id, v_changed_by, v_now, 'shipments', v_shipment_id);

        UPDATE shipments
           SET shipment_status_id    = v_new_state_id,
               updated_by            = v_changed_by,
               updated_at            = v_now,
               reason                = COALESCE(v_reason, reason),
               mobile_build_version  = COALESCE(v_mobile_build_version, mobile_build_version)
         WHERE id = v_shipment_id;

        GET DIAGNOSTICS cnt_shipments = ROW_COUNT;
    END IF;

    -- ========= SHIPMENT_CUSTOMERS (history commented) =========
    -- INSERT INTO shipment_status_history(company_id, from_state, to_state, changed_by, changed_at, table_name, ref_id)
    -- SELECT v_company_id, sc.shipment_status_id, v_new_state_id, v_changed_by, v_now, 'shipment_customers', sc.id
    -- FROM shipment_customers sc
    -- WHERE sc.shipment_id = v_shipment_id AND sc.shipment_status_id IS DISTINCT FROM v_new_state_id;

    UPDATE shipment_customers
       SET shipment_status_id = v_new_state_id,
           updated_by         = v_changed_by,
           updated_at         = v_now
     WHERE shipment_id = v_shipment_id
       AND shipment_status_id IS DISTINCT FROM v_new_state_id;

    GET DIAGNOSTICS cnt_shipment_customers = ROW_COUNT;

    -- ========= SHIPMENT_LOCATION (history commented) =========
    -- INSERT INTO shipment_status_history(company_id, from_state, to_state, changed_by, changed_at, table_name, ref_id)
    -- SELECT v_company_id, sl.shipment_status_id, v_new_state_id, v_changed_by, v_now, 'shipment_location', sl.id
    -- FROM shipment_location sl
    -- WHERE sl.shipment_id = v_shipment_id AND sl.shipment_status_id IS DISTINCT FROM v_new_state_id;

    UPDATE shipment_location
       SET shipment_status_id = v_new_state_id,
           updated_by         = v_changed_by,
           updated_at         = v_now
     WHERE shipment_id = v_shipment_id
       AND shipment_status_id IS DISTINCT FROM v_new_state_id;

    GET DIAGNOSTICS cnt_shipment_location = ROW_COUNT;

    -- ========= SHIPMENT_DELIVERY_NOTES (history commented) =========
    -- INSERT INTO shipment_status_history(company_id, from_state, to_state, changed_by, changed_at, table_name, ref_id)
    -- SELECT v_company_id, sdn.shipment_status_id, v_new_state_id, v_changed_by, v_now, 'shipment_delivery_notes', sdn.id
    -- FROM shipment_delivery_notes sdn
    -- WHERE sdn.shipment_id = v_shipment_id AND sdn.shipment_status_id IS DISTINCT FROM v_new_state_id;

    UPDATE shipment_delivery_notes
       SET shipment_status_id = v_new_state_id,
           updated_by         = v_changed_by,
           updated_at         = v_now
     WHERE shipment_id = v_shipment_id
       AND shipment_status_id IS DISTINCT FROM v_new_state_id;

    GET DIAGNOSTICS cnt_shipment_delivery = ROW_COUNT;

    -- ========= Final result =========
    result := jsonb_build_object(
        'status', 'success',
        'shipment_id', v_shipment_id,
        'old_state', jsonb_build_object('id', v_old_state_id, 'slug', v_old_state_slug),
        'new_state', jsonb_build_object('id', v_new_state_id, 'slug', v_new_state_slug),
        '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 unique_violation THEN
        result := jsonb_build_object(
            'status','failure',
            'error','Unique constraint violation',
            'detail', SQLERRM
        );
        RETURN;

    WHEN others THEN
        result := jsonb_build_object(
            'status','failure',
            'error', SQLERRM
        );
        RETURN;
END;
$BODY$;
ALTER PROCEDURE public.update_shipment_status_and_log(jsonb, jsonb)
    OWNER TO postgres;
