-- Shipments
CREATE OR REPLACE FUNCTION log_shipments_status_change()
RETURNS TRIGGER AS $$
DECLARE
    v_vehicle_id INT;
    v_status_slug TEXT;
BEGIN
    -- Only log if shipment_status_id actually changes
    IF NEW.shipment_status_id IS DISTINCT FROM OLD.shipment_status_id THEN
        -- Fetch the vehicle ID for this shipment
        SELECT vehicle_id INTO v_vehicle_id
        FROM shipments
        WHERE id = NEW.id
        LIMIT 1;

        -- Fetch the slug for the new shipment status
        SELECT slug INTO v_status_slug
        FROM shipment_status
        WHERE id = NEW.shipment_status_id
        LIMIT 1;

        -- Update vehicle assignment based on shipment status slug
        IF v_vehicle_id IS NOT NULL THEN
            IF v_status_slug IN ('scheduled', 'loaded', 'in_transit') THEN
                UPDATE vehicles
                SET is_assigned = TRUE
                WHERE id = v_vehicle_id;
            ELSE
                UPDATE vehicles
                SET is_assigned = FALSE
                WHERE id = v_vehicle_id;
            END IF;
        END IF;

        INSERT INTO shipment_status_history (
            ref_id,
            table_name,
            from_state,
            to_state,
            changed_by,
            company_id,
            status_id
        ) VALUES (
            NEW.id,
            'shipments',
            OLD.shipment_status_id,       
            NEW.shipment_status_id,      
            COALESCE(NEW.updated_by, NEW.created_by),              
            NEW.company_id,              
            1                             
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS log_shipments_status_change_trigger ON shipments;

CREATE TRIGGER log_shipments_status_change_trigger
AFTER UPDATE OF shipment_status_id
ON shipments
FOR EACH ROW
EXECUTE FUNCTION log_shipments_status_change();
